加快大型數據庫查詢

我正在使用 MySQL 和 InnoDB 執行一個大型本地數據庫。數據大小約為 30GB，密鑰大小約為 6GB。該機器具有 16GB 的 RAM。

桌子看起來像這樣

symbol VARCHAR(10),
iso_datestamp VARCHAR(14),
-- a lot more columns with values with mostly DECIMAL(26,6)s

我在符號和 iso_datesamp 上有 BTREE 索引。

我執行的操作大多是這樣執行的更新：

1. DELETE FROM table WHERE symbol = 'XXX'
2. INSERT INTO table (columns) VALUES (...)

需要這樣做以避免數據不一致，而不是僅僅附加數據。

我也經常執行分析，如 SELECT FROM table WHERE symbol in ('XXX', 'YYY', ...) [AND iso_datestamp > '2015...']

每個符號我有大約 3000 個條目。

性能問題：一旦表開始增長超過 10GB，DELETEandINSERT語句就會從通常不到 0.4 秒到超過 1 秒甚至超過 5 秒甚至更多，因為表不斷增長。RAM 沒有限制（根據top）。

我需要平均 1 秒左右（當然更快也可以）DELETE…INSERT

我現在有兩個想法：

• 使用雜湊索引而不是 btree 索引，因為我只做相等操作
• 使用符號和範圍對錶進行分區

這些是可行的策略嗎？兩者都做，只有一個？我應該做的其他事情來提高速度？

• iso_datestamp到 MySQL TIMESTAMP，以便它可以是 5 個字節而不是 15 個。
• 使用 InnoDB，而不是 MyISAM。（這對以下某些項目至關重要。）
• 使用CHARACTER SET ascii（或latin1），除非您需要多字節 utf8/utf8mb4。
• PRIMARY KEY(symbol, datestamp)
• auto_increment 浪費空間和索引。以上是一個“自然PK” *，*它提供了一個有效的索引。
• PARTITION BY RANGE(TO_DAYS(datestamp))– 這將顯著加快刪除“舊”行的速度。
• 有了那個 PK 和分區，您可能不需要任何二級索引，從而完全消除了您提到的 6GB。（如果您有未充分處理的查詢，請告訴我。）
• 拍攝不超過50個分區；不要打擾子分區。（見下面的連結）
• 用於DROP PARTITION按日期非常有效地刪除。（見下面的連結）如果您要按符號刪除，請告訴我。
• DECIMAL(26,6)佔用 12 個字節。考慮縮小它和/或移動到 8-byte DOUBLE。注意：Double 的 ~16 位有效數字和二進制格式可能會導致舍入問題。
• 考慮使用 3 字節MEDIUMINT INT的 asymbol_id並將實際symbol名稱標準化為另一個（小）表。
• 指標（例如，“alpha”）可能應該是 4-byte FLOATs。它的 7 位有效數字對於這種情況來說太過分了。
• 設置innodb_buffer_pool_size為大約 70% 的可用記憶體（在考慮了應用程序、Web 伺服器等之後）
• 以 100-1000 個為一組插入新數據。這實際上是一次執行速度的 10 倍。

這些更改應該有助於解決您的各種問題。

目前，由於多種原因，性能正在跌落懸崖。上面的提示會使懸崖延遲大約兩倍，並使懸崖不那麼陡峭。在某些情況下，懸崖被消除了。

此外，我的技巧將減少所需的 I/O——通過集群、引用位置、更好的記憶體等。

http://mysql.rjweb.org/doc.php/partitionmaint

DELETE 序列化非常糟糕。您可以通過按符號雜湊對錶進行分區來緩解這種情況。這將允許您在這些 DELETE 上實現更好的並發性。

對於 INSERT，可能有幫助的事情是：

1. 將事務隔離級別設置為已送出讀
2. 確保你有足夠的（innodb_log_file_size x innodb_log_files_in_group）。這些應該乘以浸泡您的峰值寫入時間所需的數量。
3. 根據您的表上是否有 auto_increment 列，innodb_autoinc_lock_mode=2 將有助於高並發。
4. 確保您的 innodb_io_capacity_max 設置為您的儲存堆棧可以通過 16KB IOPS 提供的值。

通過優化調整將MySQL 數據目錄放在ZFS 上將提高您的寫入性能。

此外，請確保您的 innodb_buffer_pool_size 設置正確（經驗法則是 RAM 的 50-80%，具體取決於您擁有多少 RAM）。您最熱門的索引和主鍵應該適合 RAM。在您的情況下，如果它是專用數據庫伺服器，12GB 似乎是一個合理的數量。

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