提升高達 1 億行表的讀/寫性能的常用方法是什麼？

表有 column SEGMENT_ID INT NOT NULL，其中每個段大約有 100.000-1.000.000 行。寫入 -SEGMENT_ID立即插入所有行，之後不再更新SEGMENT_ID。讀取 - 經常，我需要良好的性能SELECT * FROM table WERE SEGMENT_ID = ?。

最明顯的方法是動態創建新表SEGMENT_ID，但動態表意味著使用 ORM 甚至本機 SQL 查詢框架。換句話說，你完成了有異味的程式碼。

你也可以使用分片，對吧？數據庫是否在後台創建新表？

我可以按SEGMENT_ID. 但是，如果我一次插入所有與段相關的數據，我的插入會被聚集嗎？

Postgres 還建議使用分區來處理非常大的表。

也許有某種神奇的索引可以幫助我避免動態創建新表或配置分片？

還有其他選擇嗎？

使用簡單的BRIN索引

那。

這是一個與您描述的完全一樣的表，最壞的情況是 1 億行，每行 100 萬行SEGMENT_ID

explain analyze
CREATE TABLE foo AS
 SELECT (x::int%100)::int AS SEGMENT_ID
 FROM generate_series(1,100e6) AS gs(x);

                                                             QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series gs  (cost=0.00..15.00 rows=1000 width=32) (actual time=21740.904..57589.405 rows=100000000 loops=1)
Planning time: 0.043 ms
Execution time: 96685.350 ms
(3 rows)

這意味著我們在 1.5 分鐘內創建了表格。這裡我們添加一個索引。

CREATE INDEX ON foo
 USING brin (SEGMENT_ID);
VACUUM ANALYZE foo;

然後我們再添加一百萬行。SEGMENT_ID = 142

explain analyze
INSERT INTO foo(SEGMENT_ID)
 SELECT 142
 FROM generate_series(1,1e6) AS gs(x);

                                                            QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
Insert on foo  (cost=0.00..10.00 rows=1000 width=0) (actual time=1489.958..1489.958 rows=0 loops=1)
  ->  Function Scan on generate_series gs  (cost=0.00..10.00 rows=1000 width=0) (actual time=174.690..286.331 rows=1000000 loops=1)
Planning time: 0.043 ms
Execution time: 1499.529 ms
(4 rows)

添加一百萬行需要 1.5 秒。現在我們選擇，

explain analyze
SELECT *
 FROM foo
 WHERE SEGMENT_ID=142;

                                                          QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on foo  (cost=52.00..56.01 rows=1 width=4) (actual time=4.401..140.874 rows=1000000 loops=1)
  Recheck Cond: (segment_id = 142)
  Rows Removed by Index Recheck: 24832
  Heap Blocks: lossy=4535
  ->  Bitmap Index Scan on foo_segment_id_idx  (cost=0.00..52.00 rows=1 width=0) (actual time=1.504..1.504 rows=46080 loops=1)
        Index Cond: (segment_id = 142)
Planning time: 0.059 ms
Execution time: 176.902 ms
(8 rows)

選擇一百萬行需要 176 毫秒。

這是在具有*“Intel(R) Core(TM) i5-3230M CPU @ 2.60GHz”*和單個 SSD 的 5 年曆史 x230 上。你可以花幾百美元買一個並安裝 Xubuntu。也不完全是硬科學。我正在後台編譯 Angular 應用程序。

提升高達 1 億行表的讀/寫性能的常用方法是什麼？

不在手機上執行？我的意思是，在現代中端硬體上，數以百萬計的行數並不是特別大。這意味著 - 嗯，讓我們看看。雙插槽，16 核（我在這裡只使用最低許可 Windows 標準，它與例如 AMD EPYC 的低端匹配），可能 128GB RAM 和全 SSD 設置，至少是一個高度 SSD 記憶體的東西。

我的意思是，我古老的虛擬機（sql server，使用 48gb 記憶體、6 個核心和大約 10 個專用 SSD）在不到一秒的時間內處理了 6400 萬行插入/刪除作業，沒有任何特別之處。

最明顯的方法是為 SEGMENT_ID 創建新表

這是專業數據庫具有稱為分區的東西的一件事。一種穀歌實際上告訴我 postgres 也有它 - https://www.postgresql.org/docs/current/static/ddl-partitioning.html - 你知道嗎？從 waht 我看到它比 SQL Server 優雅得多（似乎在每個分區上創建索引，而不是由數據庫透明地處理）。

它不會使讀取或寫入速度更快，但刪除整個分區可以顯著加快速度。不需要在這裡動態，雖然你可以 - 主要的一點是你永遠不會使用子表，所以 ORM 和查詢保持不變。

你也可以使用分片，對吧？

您可能應該這樣做 - 一旦您達到數千億行。

它確實是分區，但前提是您的插入/刪除方案使其高效。否則答案真的是硬體，特別是因為 1 億並不是很多。分區是幾乎唯一可以與 ORM 完美配合的解決方案。

真的，為什麼是動態的？預生成。哦，還有……

我需要 SELECT * FROM table WERE SEGMENT_ID = 的良好性能？

分區在這裡沒有幫助。好的，這就是問題所在 - 分區可以幫助您搜尋更少的數據，但是使用以 segment_id 作為第一個欄位並按此欄位過濾的索引 - 完全一樣。足夠的 RAM 和 FAST IO 是快速讀取數據的唯一解決方案。分區基本上是“快速刪除一個分區”的東西 - 其他任何東西充其量只是一個小小的收穫。

引用自：https://dba.stackexchange.com/questions/188407