大表上的“創建唯一索引”耗時太長
我正在嘗試恢復 GHTorrent 的數據庫轉儲（包含 GitHub 元數據的 CSV 文件）。該
commits表有超過 8.91 億行，其中project_commits有超過 54 億行。由於這些表很大，我不得不使用LOAD DATA INFILE外鍵檢查來載入它們。我正在使用MyISAM引擎。完成將記錄導入表後，我正在嘗試為這些表創建索引。我正在為
commits表執行以下 mysql 命令，但它在超過 12 小時內沒有完成。CREATE UNIQUE INDEX `sha` ON `ghtorrent_restore`.`commits` (`sha` ASC) COMMENT '';送出表如下所示：
我已閱讀有關慢速索引的其他 stackexchange 問題，並
my.cnf在目錄中的文件中設置以下內容/etc/mysql。[mysqld] bulk_insert_buffer_size=1G myisam_sort_buffer_size=8G key_buffer_size=6G sort_buffer_size=10M由於前面的命令沒有及時完成，我不得不從控制台用 ctrl+z 停止它。我檢查了 MySQL 工作台上的表，它沒有顯示為損壞，但它顯示了一些 36GB 作為索引長度。
導入此表大約需要 25 分鐘，所以我預計索引它不會超過一個小時，但我現在執行“創建唯一索引”命令大約 2 小時，沒有任何進展跡象。
當我執行該命令時，mysqld 會佔用大量 CPU 並不斷佔用記憶體。達到 6GB 後，它變得不那麼活躍，似乎幾乎什麼都不做。
這是命令（下圖中選擇的一個）從 mysql 工作台中的外觀。
我在具有 16GB RAM 的 Linux Mint 17.03 機器上執行 Mysql 5.7.22-0ubuntu0.16.04.1。
由於我不是高級使用者，因此任何幫助都會有很大幫助。
更新$$ as Wilson H. suggested $$:
我的.cnf 文件
$$ 06/05/2018 01:06 $$
!includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/ [mysqld] secure-file-priv = "" [mysqld] bulk_insert_buffer_size=1G myisam_sort_buffer_size=8G key_buffer_size=6G sort_buffer_size=10M以下是緩衝區和記憶體相關變數的快照。
我沒有在我的 mysql 安裝中修改任何其他內容。根據系統中可用的 16GB 記憶體中的不同表，Mysqld 佔用不同數量的記憶體。除了 mysql，我不執行任何 CPU/記憶體密集型應用程序。
有趣的觀察：對其他表的一些測試顯示，隨著行數的增加，時間也在增加。趨勢看起來是多項式的。
以下統計數據是在執行上圖中的最後一個 mysql 命令時擷取的（即為表“項目”編制索引）。
最佳：
iostat -x：
ulimit -a：
df -h：
更新 2： 由於在 ‘commits’ 表上創建索引沒有完成，我正在嘗試其他表，最後我在昨晚睡覺前嘗試在 ‘project_commits’ 表上建立索引。令我驚訝的是，我發現完成索引只用了 18 分鐘。
我沒有做任何額外的修改，我不明白為什麼“送出”表永遠不會完成。我在“送出”表上再次執行索引，看看它走了多遠。
更新 3：
‘顯示創建表送出；’
CREATE TABLE `commits` ( `id` int(11) NOT NULL AUTO_INCREMENT, `sha` varchar(40) DEFAULT NULL, `author_id` int(11) DEFAULT NULL, `committer_id` int(11) DEFAULT NULL, `project_id` int(11) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `commits_ibfk_1` (`author_id`), KEY `commits_ibfk_2` (`committer_id`), KEY `commits_ibfk_3` (`project_id`) ) ENGINE=MyISAM AUTO_INCREMENT=922093754 DEFAULT CHARSET=utf8顯示處理程序：
底線：
除“commit”表外，所有表的索引都已完成（即，以下命令未完成執行）。
CREATE UNIQUE INDEX `sha` ON `ghtorrent_restore`.`commits` (`sha` ASC) COMMENT '';當我執行一個從 8.91 億行中計算約 19000 行的查詢（見下圖）時，大約需要 76 秒。考慮到我有一台配備 Core i707700HQ CPU @ 2.8Ghz x 4、16GB DDR4 Ram 並且數據庫安裝在 7200RPM 硬碟上的電腦，這個時間是否太高了？**76 秒是否表明索引在“送出”表中無法正常工作？**請注意，此查詢是在啟動電腦後立即執行的，以避免緩衝區的影響。
有多個問題。
922M 的 Auto_inc 是 20 億限制的一半
INT SIGNED。INT UNSIGNED建議您在下一次更改為（40 億限制）ALTER。MyISAM 節省了磁碟空間，但在其他方面比 InnoDB 更“差”。注意：更改為 InnoDB 需要更改幾個設置。
FOREIGN KEYS被 MyISAM 忽略。如果
sha是 SHA-1 雜湊，則索引很糟糕。如果
sha是 SHA-1 雜湊，則可以將其壓縮為BINARY(20)viaUNHEX()。這會將表縮小超過 20GB，即目前大小的 30%！如果
sha是 SHA-1 雜湊，不要使用 utf8；使用 ascii 或 latin1。如果
sha是 SHA-1 雜湊，並且這是您要在其上創建UNIQUE索引的列，請檢查SHOW PROCESSLIST. 如果它說“通過key_buffer修復”，那麼你應該殺死它；需要幾個月的時間才能完成。如果它說“按排序修復”，那麼它就有希望完成。考慮為作者、送出者和項目使用比 4 字節 INT 更小的 id——除非你真的有數十億個不同的值。等待！什麼？每一個都將是
UNIQUE？我對此表示懷疑。你有什麼
SELECTs？他們中的一些人可能需要“複合”索引嗎？將多個
ALTERs（包括創建索引）放入單個語句中。每個ALTER都是表的完整副本（在 MyISAM 中）。
myisam_sort_buffer_size = 8G是 RAM 的一半。這是不好的。建議3G。多項式
趨勢看起來是多項式的。
我希望您不要
UNIQUE INDEX在已經與PRIMARY KEY. 那將是完全多餘的。請提供SHOW CREATE TABLE其中之一。為什麼是“多項式”？如果是（我對此表示質疑），那麼這將是因為“通過 key_buffer 修復”的性質：
對於每一行，在所有唯一（包括主）索引中查找該列，以確保它不是“重複的”。此查找需要獲取索引的 BTree 塊並將其放入“key_buffer”進行測試。取決於鍵的“順序”與數據的“順序”：
- 對於 auto_increment 或時間戳，其中行是按時間順序插入的（並且表沒有變得碎片化），所需的下一個塊很可能是剛剛被觸及的塊。這是最有效的，因為它需要最少的 I/O。
- 對於 SHA1/MD5/UUID/等，此查找將在索引周圍跳躍。因此，需要的下一個塊越來越不可能在 RAM 中的 key_buffer 中。在極端情況下，這會導致每次查找幾乎 1 個磁碟讀取！
- 對於其他指數，時間介於兩者之間。
8.91 億行是相當多的。這是 MyISAM 和唯一索引的問題。
也許您可以遷移到沒有這個問題的PostgreSQL ？ghtorrent 似乎支持 PostgreSQL