使用載入文件進行批量載入之前或之後的索引？
我有一個超過 1B 行和兩列被索引的數據庫（除了 PK）。是在載入文件之前在表中預定義索引更好，還是在載入數據之後更好地進行索引？
關於數據大小和系統的幾點說明：
- 系統是具有 8 個核心和 32GB 記憶體的 Linux（目前已滿，除非我遷移到新硬體）
- DB 是 1B 行，原始數據大小為 150GB 數據。
- 數據庫是 MyISAM，載入後主要是只讀的。
我已經嘗試了具有類似數據負載的不同解決方案 - 超過 1B - 但我發現更好的是：
LOAD DATA INFILE通過一些額外的工作，當 MyISAM 表有許多索引時，可以使執行速度更快。使用以下過程：
- 執行
FLUSH TABLES語句或mysqladmin flush-tables命令。- 用於
myisamchk --keys-used=0 -rq /path/to/db/tbl_name刪除表的所有索引使用。- 使用 將數據插入表中
LOAD DATA INFILE。這不會更新任何索引，因此非常快。- 使用 重新創建索引
myisamchk -rq /path/to/db/tbl_name。這會在將索引樹寫入磁碟之前在記憶體中創建索引樹，這比在 LOAD DATA INFILE 期間更新索引要快得多，因為它避免了大量的磁碟尋軌。生成的索引樹也是完美平衡的。- 執行
FLUSH TABLES語句或mysqladmin flush-tables命令。
LOAD DATA INFILE如果插入數據的 MyISAM 表為空，則會自動執行上述優化。自動優化和顯式使用過程之間的主要區別在於，您可以讓 myisamchk 為索引創建分配比您希望伺服器在執行 LOAD DATA INFILE 語句時為索引重新創建分配更多的臨時記憶體。為了從 myisamchk 獲得更好的性能，您必須調整一些參數，例如：
--key_buffer_size --myisam_sort_buffer_size --read_buffer_size --write_buffer_size取決於您的硬體架構
筆記
使用
LOCALwith 時LOAD DATA，會在伺服器的臨時目錄中創建文件的副本。這不是由 tmpdir 或 slave_load_tmpdir 的值決定的目錄，而是作業系統的臨時目錄，在 MySQL Server 中不可配置。所以，你有這種問題，你的文件是一個 csv，你可以將你的“巨大”文件分割成塊
$ split -l (numbersofrowsinfile / ((filesize/tmpsize) + 1)) /path/to/your/<file>.csv然後對每個塊文件重複您的
LOAD DATA LOCAL（步驟 3）。
我會在這個答案上保持一般性，因為 Cristian 看起來已經涵蓋了大量 MySQL 特定的考慮因素。
批量操作的一般建議肯定是在之後刪除和重建索引。維護每個索引的樹結構平衡的工作量相當高，並且取決於插入順序，可能會導致顯著的索引碎片（即數據文件中頁面的位置）和使用的空間量（可能部分使用的頁面比例很高）。
如果表中沒有數據，那麼一般建議是：
- 刪除所有索引。請注意，唯一約束也是索引，但除非您絕對確定傳入數據在這方面有效，否則我會將它們保留在原處。PK 也是索引，但是如果 FK 引用它們，您將無法刪除它們（並且只有在您知道傳入的數據不包含重複項時才應該刪除它們）。與流行的看法相反，外鍵並不意味著所有 RDBMS 中的索引（參見http://www.sqlskills.com/blogs/kimberly/when-did-sql-server-stop-putting-indexes-on-foreign-key-columns / )
- 進行批量插入。
- 由於該表將主要是只讀的，因此重建主鍵（在許多 DBMS 中，這將減少碎片和空間浪費）並具有高填充因子（如果您的 DBMS 允許您對此進行調整）。
- 重新創建索引和唯一約束（如果您刪除了它們），再次使用高填充因子。如果您有聚集索引，請確保在其他索引和約束之前（重新）創建它。
如果您的表中已經有數據，那麼這個建議當然需要仔細重新考慮。我記得一個舊的“經驗法則”，如果您要添加或更新超過 70% 的表內容，則值得刪除並重新創建索引，否則不會，但我不知道該規則是否有任何正確依據進行實驗，或者如果它是由一位專家憑空提出並被其他人重複的！此外，如果存在現有數據刪除索引，並且如果有使用者積極使用數據庫支持的系統，則約束將成為問題。
當然，如果時間允許，並且您有一台可以在將數據導入生產環境之前執行測試的機器，您可以在各個過程中執行自己的基準測試。