如何在 MySQL innoDB 中重建索引和更新统计信息？

这是通过

ANALYZE TABLE table_name;

在这里阅读更多关于它的信息。

ANALYZE TABLE 分析并存储表的键分布。在分析过程中，该表被 MyISAM、BDB 和 InnoDB 的读锁锁定。此语句适用于 MyISAM、BDB、InnoDB 和 NDB 表。

为什么？人们几乎从不需要更新统计数据。重建索引的情况就更少了。

OPTIMIZE TABLE tbl;将重建索引并执行;这需要时间。ANALYZE

ANALYZE TABLE tbl;InnoDB 可以快速重建统计数据。在 5.6.6 中，它甚至更少需要。

（注：以上适用于常规;InnoDB确实需要定期重建。INDEXesFULLTEXT

您还可以使用提供的 CLI 工具运行优化。它有很多开关，但最基本的是，你只需传入数据库、用户名和密码。mysqlcheck

将其添加到 cron 或 Windows 计划程序可以使此过程自动化。（MariaDB，但基本上是一样的。

迄今为止（mysql 8.0.18），mysql中没有合适的函数来重新创建索引。
由于 mysql 8.0 myisam 正在慢慢进入弃用状态，因此 innodb 是当前的主要存储引擎。
在大多数实际情况下，innodb 是最佳选择，它应该保持索引正常工作。
在大多数实际情况下，innodb 也做得很好，你不需要重新创建索引。几乎总是如此。

当涉及到具有数百 GB 数据和行的大型表和大量写入情况发生变化时，索引的性能可能会下降。
就我个人而言，我看到性能从使用二级索引的计数 （*） 的 ~15 分钟下降到写入表的 2 个月后的 4300 分钟，时间线性增加。
重新创建索引后，性能将恢复到 15 分钟。

到目前为止，我们有两种选择：
1） OPTIMIZE TABLE（或 ALTER TABLE）
Innodb 不支持优化，因此在这两种情况下，整个表都会被读取并重新创建。
这意味着您需要存储临时文件，并且需要花费大量时间（我的情况是优化需要一周才能完成）。 这将压缩数据并重新生成所有索引。
尽管没有正式推荐，但我强烈建议在最大 100GB 的写入密集型表上使用 OPTIMIZE 过程。

2） ALTER TABLE DROP KEY -> ALTER TABLE ADD KEY
按名称手动删除密钥，然后再次手动创建它。在生产环境中，您需要先创建它，然后删除旧版本。
好处是：这可能比优化快得多。缺点：您需要手动创建语法。
“SHOW CREATE TABLE”可用于快速查看哪些索引可用以及它们是如何调用的。

附录：
1） 要更新统计数据，您可以使用已经提到的“分析表”。
2）如果您在写入密集型服务器上遇到性能下降，则可能需要重新启动mysql。当前 mysql （8.0） 中有几个错误可能会导致显着的减速，而不会显示在错误日志中。最终，这些减速会导致服务器崩溃，但可能需要数周甚至数月才能累积到崩溃，在此过程中，服务器的响应速度越来越慢。
3）如果您希望重新创建一个需要数周时间才能完成的大型表，或者由于内部数据完整性问题而在数小时后失败，则应执行CREATE TABLE，LIKE INSERT-INTO SELECT *。然后对表进行“原子重命名”。
4） 如果在大型表上完成 INSERT INTO SELECT * 需要数小时到数天才能完成，则可以使用多线程方法将该过程加快约 20-30 倍。您将表“分区”为块，并并行插入 INTO SELECT *。

对于基本的清理和重新分析，您可以运行“OPTIMIZE TABLE ...”，它将压缩索引中的开销并运行ANALYZE TABLE，但它不会重新排序并使它们尽可能小和高效。

https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html

但是，如果希望完全重新生成索引以获得最佳性能，则可以：

1. 删除/重新添加索引（显然）
2. 转储/重新加载表
3. ALTER TABLE 并使用相同的存储引擎进行“更改”
4. REPAIR TABLE（仅适用于 MyISAM、ARCHIVE 和 CSV）

https://dev.mysql.com/doc/refman/8.0/en/rebuilding-tables.html

如果对字段（作为索引的一部分）执行 ALTER TABLE 并更改其类型，则它还将完全重建相关索引。

如MySQL手册中所述，有多种方法可以重建表。如果您不更改MySQL服务器的版本，并且希望支持具有各种引擎（MyISAM，InnoDB）的表，那么这样的存储过程可能会派上用场：

CREATE PROCEDURE rebuildTables(in dbName VARCHAR(100))
BEGIN
    -- flag marking cursor end 
    DECLARE done INT DEFAULT FALSE;
    DECLARE tableName VARCHAR(255) DEFAULT "";
    DECLARE tableEngine VARCHAR(100) DEFAULT "";

    -- declare cursor for table names and engine
    DEClARE curTables
        CURSOR FOR
        SELECT TABLE_NAME, ENGINE FROM information_schema.tables where table_schema = dbName and table_type = 'BASE TABLE';

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET done = TRUE;

    OPEN curTables;

    rebuildTables: LOOP
        FETCH curTables INTO tableName, tableEngine;
        SELECT concat('Processing ', tableName, ' engine ', tableEngine);
        IF done THEN
            LEAVE rebuildTables;
        END IF;
        -- rebuild table as adviced in https://dev.mysql.com/doc/refman/5.7/en/rebuilding-tables.html
        SET @query = CONCAT('ALTER TABLE ', tableName, ' ENGINE=', tableEngine);
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE curTables;
END;

为了调用它，只需：

CALL rebuildTables('yourDbName');

请注意，此过程可能需要大量时间，尤其是对于大型表。

使用 CLI，

mysqlcheck -u root -p --auto-repair --optimize --all-databases