MySQL - 為 InnoDB 更改表的最快方法

我有一個要更改的 InnoDB 表。該表有大約 80M 行，並退出了一些索引。

我想更改其中一列的名稱並添加更多索引。

• 最快的方法是什麼（假設我什至會遭受停機 - 伺服器是未使用的從屬伺服器）？
• 是“普通” alter table，最快的解決方案嗎？

這個時候，我只關心速度 :)

加速 ALTER TABLE 的一種可靠方法是刪除不必要的索引

以下是載入新版本表的初始步驟

CREATE TABLE s_relations_new LIKE s_relations;
#
# Drop Duplicate Indexes
#
ALTER TABLE s_relations_new
   DROP INDEX source_persona_index,
   DROP INDEX target_persona_index,
   DROP INDEX target_persona_relation_type_index
;

請注意以下事項：

• 我刪除了 source_persona_index 因為它是其他 4 個索引中的第一列

  • unique_target_persona
  • 唯一目標對象
  • source_and_target_object_index
  • source_target_persona_index

• 我刪除了 target_persona_index 因為它是其他 2 個索引中的第一列

  • target_persona_relation_type_index
  • target_persona_relation_type_message_id_index

• 我刪除了 target_persona_relation_type_index 因為前 2 列也在 target_persona_relation_type_message_id_index

好的，這會處理不必要的索引。有沒有低基數的索引？以下是確定的方法：

執行以下查詢：

SELECT COUNT(DISTINCT sent_at)               FROM s_relations;
SELECT COUNT(DISTINCT message_id)            FROM s_relations;
SELECT COUNT(DISTINCT target_object_id)      FROM s_relations;

根據您的問題，大約有 80,000,000 行。根據經驗，如果所選列的基數大於表行數的 5%，MySQL 查詢優化器將不使用索引。在這種情況下，這將是 4,000,000。

• 如果COUNT(DISTINCT sent_at)> 4,000,000

  • 然後ALTER TABLE s_relations_new DROP INDEX sent_at_index;

• 如果COUNT(DISTINCT message_id)> 4,000,000

  • 然後ALTER TABLE s_relations_new DROP INDEX message_id_index;

• 如果COUNT(DISTINCT target_object_id)> 4,000,000

  • 然後ALTER TABLE s_relations_new DROP INDEX target_object_index;

一旦確定了這些索引的有用性或無用性，您就可以重新載入數據

#
# Change the Column Name
# Load the Table
#
ALTER TABLE s_relations_new CHANGE sent_at sent_at_new int(11) DEFAULT NULL;
INSERT INTO s_relations_new SELECT * FROM s_relations;

就是這樣，對吧？沒有 ！！！

如果您的網站一直在執行，則在載入 s_relations_new 期間可能有針對 s_relations 的 INSERT。您如何檢索那些失去的行？

在 s_relations_new 中找到最大 id 並在 s_relations 中附加該 ID 之後的所有內容。為確保表被凍結並僅用於此更新，您必須有一點停機時間才能獲取插入到 s_relation_new 中的最後幾行。這是你要做的：

在作業系統中，重新啟動 mysql 以便除 root@localhost 之外沒有其他人可以登錄（禁用 TCP/IP）：

$ service mysql restart --skip-networking

接下來，登錄 mysql 並載入最後幾行：

mysql> SELECT MAX(id) INTO @maxidnew FROM s_relations_new;
mysql> INSERT INTO s_relations_new SELECT * FROM s_relations WHERE id > @maxidnew;
mysql> ALTER TABLE s_relations RENAME s_relations_old;
mysql> ALTER TABLE s_relations_new RENAME s_relations;

然後，正常重啟mysql

$ service mysql restart

現在，如果你不能關閉 mysql，你將不得不在 s_relations 上做一個誘餌和切換。只需登錄 mysql 並執行以下操作：

mysql> ALTER TABLE s_relations RENAME s_relations_old;
mysql> SELECT MAX(id) INTO @maxidnew FROM s_relations_new;
mysql> INSERT INTO s_relations_new SELECT * FROM s_relations_old WHERE id > @maxidnew;
mysql> ALTER TABLE s_relations_new RENAME s_relations;

試一試 ！！！

CAVEAT ：一旦您對此操作感到滿意，您可以儘早刪除舊表：

mysql> DROP TABLE s_relations_old;

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