如何在不加表鎖的情況下在大型MySQL生產表上建立索引

問題背景：

在大型MySQL 表上建立索引可能是一項艱鉅的任務，尤其是在不間斷存取至關重要的生產環境中。傳統的 CREATE INDEX 語句可能會導致完全表鎖定，從而阻塞所有並發操作。

MySQL 版本注意事項：

• 在 MySQL 5.6 及更高版本中，索引更新線上執行，允許在索引建立期間繼續讀寫操作。
• 但是，在 MySQL 5.5 及更早版本中，包括 InnoDB 和 MyISAM表，索引更新將阻止對錶的寫入。

循環主控方法：

對於5.6 之前的MySQL 版本，一種有效的方法是循環主控技術：

1. 設定從主主控（Master B）複製的輔助主控（Master B） A).
2. 在Master B上執行架構更新（允許其在升級過程中落後）。
3. 確保架構變更與從 Master A 上的降版本架構複製指令相容.
4. 自動將所有客戶端從 Master A 切換到 Master B。
5. 更新Master A 上的架構並將其設為輔助master.

Percona 的pt-online-schema-change 工具：

此工具透過以下方式自動化循環母版方法：

• 使用更新後的架構建立新表。
• 使用觸發器。
• 從原表中批次複製行。
• 以新錶取代原表。

AWS RDS 注意事項：

對於託管在Amazon RDS 上的MySQL 資料庫，可以使用「只讀副本升級」功能來促進架構更改，而無需表鎖定。這涉及對唯讀從站進行更改，然後將其提升為新的主站。

替代技術：

• 使用臨時table: 使用新索引建立臨時表，從原表插入數據，並以臨時表替換原表表。
• 對錶進行分區： 將表拆分為較小的分區，分別在每個分區上建立索引，然後將分區合併回一起。
• 使用後台程序： 建立一個單獨的後台程序，該程序會逐漸建立索引，同時表仍可用於正常操作。並非所有 MySQL 版本都支援此方法。