主页 / dba / 问题 / 9746
Accepted
Ran
Asked: 2011-12-27 16:46:27 +0800 CST

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

  • 772

我有一个要更改的 InnoDB 表。该表有大约 80M 行，并退出了一些索引。

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

  • 最快的方法是什么（假设我什至会遭受停机 - 服务器是未使用的从属服务器）？
  • 是“普通” alter table，最快的解决方案吗？

这个时候，我只关心速度 :)

mysql index

5 个回答

  1. Best Answer

    加速 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;
    • 17

  2. 正确答案取决于您使用的 MySQL 引擎的版本。

    如果使用 5.6+，重命名和添加/删除索引是在线执行的，即不复制所有表的数据。

    像往常一样使用ALTER TABLE，它对于重命名和索引删除几乎是即时的，并且对于索引添加相当快（就像读取所有表一次一样快）。

    如果使用 5.1+，并且启用了 InnoDB 插件，则添加/删除索引也将在线。不确定重命名。

    如果使用旧版本，ALTER TABLE仍然是最快的，但可能会非常慢，因为您的所有数据都将重新插入到引擎盖下的临时表中。

    最后，是时候揭穿神话了。不幸的是，我在这里没有足够的业力来评论答案，但我觉得纠正投票最多的答案很重要。这是错误的：

    根据经验，如果选定列的基数大于表行数的 5%，MySQL 查询优化器将不使用索引

    它实际上是相反的。

    索引对于选择几行很有用，因此它们具有高基数很重要，这意味着许多不同的值和具有相同值的统计上很少的行。

    • 12

  3. 我在 Maria DB 10.1.12 上遇到了同样的问题，然后在阅读文档后，我发现有一个选项可以“就地”执行操作，从而消除了表的副本。使用此选项，alter table 非常快。就我而言，它是：

    alter table user add column (resettoken varchar(256),
  resettoken_date date, resettoken_count int), algorithm=inplace;

    这非常快。如果没有算法选项，它将永远不会终止。

    https://mariadb.com/kb/en/mariadb/alter-table/

    • 3

  4. 对于列重命名，

    ALTER TABLE tablename CHANGE columnname newcolumnname datatype;

    应该没问题，不会有任何停机时间。

    对于索引，CREATE INDEX 语句将锁定表。如果它是您提到的未使用的奴隶，那不是问题。

    另一种选择是创建一个具有正确列名和索引的全新表。然后您可以将所有数据复制到其中，然后执行一系列

    BEGIN TRAN;
ALTER TABLE RENAME tablename tablenameold;
ALTER TABLE RENAME newtablename tablename;
DROP TABLE tablenameold;
COMMIT TRAN;

    这将以临时使用两倍的空间为代价最小化停机时间。

    • 0

  5. 我也遇到了这个问题，我使用了这个 SQL：

    /*on créé la table COPY SANS les nouveaux champs et SANS les FKs */
CREATE TABLE IF NOT EXISTS prestations_copy LIKE prestations;

/* on supprime les FKs de la table actuelle */
ALTER TABLE `prestations`
DROP FOREIGN KEY `fk_prestations_pres_promos`,
DROP FOREIGN KEY `fk_prestations_activites`;

/* on remet les FKs sur la table copy */
ALTER TABLE prestations_copy 
    ADD CONSTRAINT `fk_prestations_activites` FOREIGN KEY (`act_id`) REFERENCES `activites` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    ADD CONSTRAINT `fk_prestations_pres_promos` FOREIGN KEY (`presp_id`) REFERENCES `pres_promos` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION;

/* On fait le transfert des données de la table actuelle vers la copy, ATTENTION: il faut le même nombre de colonnes */
INSERT INTO prestations_copy
SELECT * FROM prestations;

/* On modifie notre table copy de la façon que l'on souhaite */
ALTER TABLE `prestations_copy`
    ADD COLUMN `seo_mot_clef` VARCHAR(50) NULL;

/* on supprime la table actuelle et renome la copy avec le bon nom de table */
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE prestations;
RENAME TABLE prestations_copy TO prestations;
SET FOREIGN_KEY_CHECKS=1;

    我希望它可以帮助某人

    问候，

    将要

    • 0

相关问题