MYSQL：如何提高將超過 1M 行插入索引行超過 100M 的表的性能

我有這個mysql表：

CREATE TABLE `codes` (
 `code` bigint(11) unsigned NOT NULL,
 `allocation` int(11) NOT NULL DEFAULT '0',
 `used` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`code`),
 KEY `allocation` (`allocation`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

當它完全啟動並執行時，它將保存從 1 到 10 萬億之間的數字隨機生成的 1 億到 3 億個程式碼。

要填寫表格，我有這個儲存過程：

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_codes_v4`(
   IN bf_codes_to_generate BIGINT,
   IN bf_lower_limit BIGINT,
   IN bf_upper_limit BIGINT,
   IN bf_allocation_num INT
)
BEGIN

   SET @Codes = bf_codes_to_generate;
   SET @Lower = bf_lower_limit;
   SET @Upper = bf_upper_limit;
   SET @Allocation = bf_allocation_num;

   SET @qry_rand = 'SELECT ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) INTO @Random';
   PREPARE qry_rand_stmt FROM @qry_rand;

   SET @qry_insert = 'INSERT IGNORE INTO `codes` (`code`,`allocation`) VALUES ( @Random, @Allocation )';
   PREPARE qry_insert_stmt FROM @qry_insert;

   START TRANSACTION;

   WHILE @Codes > 0 DO

       EXECUTE qry_rand_stmt;
       EXECUTE qry_insert_stmt;

       SET @Codes = @Codes - ROW_COUNT();

   END WHILE;

   COMMIT;

   DEALLOCATE PREPARE qry_rand_stmt;
   DEALLOCATE PREPARE qry_insert_stmt;

END;;
DELIMITER ;

這樣做是在給定邊界之間選擇一個隨機數並將其插入表中。

我們目前使用此儲存過程一次插入 500K 到 5M 行之間的任何位置。當它工作時，它開始變得非常慢，因為表中已經存在更多行。

一旦我們在表中已經有 10M 行，生成過程就會減慢到每秒大約 1000 行。由於我們最終計劃在此表中儲存 100M 到 300M 程式碼，因此插入過程將需要更長的時間。基本上這張表不能很好地縮放。

有什麼辦法可以使這個過程更好地擴展嗎？

以下是我認為您可能會問的一些問題的答案

問：為什麼要在分配列上建立索引？ A：每次插入一批行時，我們都會給它一個分配號。我們需要能夠快速獲取具有給定分配編號的允許行。

問：為什麼要使用事務？ 答：顯然，這會阻止索引在插入程式碼時不斷刷新到磁碟，並且在我們的測試中顯著加快了插入速度。此外，雖然尚未實現，但我們希望能夠放置一個終止開關，可以在任何時間點取消批量插入。

問：為什麼不將表拆分為多個表，例如 1-1T 進入表一，1T-2T 進入表二等等？ A：我們可能不得不考慮這樣做，但我想看看我們現在擁有的東西是否可以改進。

問：還有什麼我們應該知道的嗎？ A：該表將一直用作查找程式碼以檢查程式碼是否存在以及它們是否已被使用並且將是 SELECT 繁重的。任何解決方案都不能阻止讀取該表，並儘量不要降低其讀取性能。

如果您可以訪問伺服器文件系統，我建議您將數字生成腳本（Perl、PHP、C++ 等）寫入平面文件並執行LOAD DATA INFILE操作。

對於較大的行集，通常LOAD DATA INFILE比重複INSERT語句執行得更快，並且還可以處理IGNORE子句。如果您選擇使用該選項，請查看有關bulk_insert_buffer_size變數的答案，這在進行批量插入時很重要。LOAD DATA INFILE

以隨機順序插入記錄不會產生最佳的寫入性能。

您可以在插入之前測試按主鍵對集合進行排序，或者只是按遞增順序生成它們（例如，將最後一個值儲存在 var 中並按隨機數遞增）。

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