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

How can I rebuild indexes and update stats in MySQL innoDB?

提问by Tomas Kubes

I have experience with MS SQL server where it is possible and useful to update statisticand rebuild indexes. I can't find such option in MySQL innoDB, is there such option? If not, how MySQL database create an execution plan? Does the MySQL update indexes and statistic with every UPDATE and INSERT?

我有使用 MS SQL 服务器的经验，在那里更新统计信息和重建索引是可能和有用的。我在 MySQL innoDB 中找不到这样的选项，有这样的选项吗？如果没有，MySQL数据库如何创建执行计划？MySQL 是否会在每次 UPDATE 和 INSERT 时更新索引和统计信息？

回答by fancyPants

This is done with

这是用

ANALYZE TABLE table_name;

Read more about it here.

在此处阅读更多相关信息。

ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for MyISAM, BDB, and InnoDB. This statement works with MyISAM, BDB, InnoDB, and NDB tables.

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

回答by Rick James

Why? One almost never needs to update the statistics. Rebuilding an index is even more rarely needed.

为什么？人们几乎不需要更新统计数据。很少需要重建索引。

OPTIMIZE TABLE tbl;will rebuild the indexes and do ANALYZE; it takes time.

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

ANALYZE TABLE tbl;is fast for InnoDB to rebuild the stats. With 5.6.6 it is even less needed.

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

回答by bbrown

You can also use the provided CLI tool mysqlcheckto run the optimizations. It's got a ton of switchesbut at its most basic you just pass in the database, username, and password.

您还可以使用提供的 CLI 工具mysqlcheck来运行优化。它有大量的开关，但在最基本的情况下，您只需传入数据库、用户名和密码。

Adding this to cron or the Windows Scheduler can make this an automated process. (MariaDB but basically the same thing.)

将此添加到 cron 或 Windows 调度程序可以使其成为一个自动化过程。（MariaDB 但基本上是一样的。）

回答by John

To date (mysql 8.0.18) there is no suitable function inside mysql to re-create indexes.
Since mysql 8.0 myisam is slowly phasing into deprecated status, innodb is the current main storage engine.
In most practical cases innodb is the best choice and it's supposed to keep indexes working well.
In most practical cases innodb also does a good job, you do not need to recreate indexes. Almost always.

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

When it comes to large tables with hundreds of GB data amd rows and a lot of writing the situation changes, indexes can degrade in performance.
In my personal case I've seen performance drop from ~15 minutes for a count(*) using a secondary index to 4300 minutes after 2 months of writing to the table with linear time increase.
After recreating the index the performance goes back to 15 minutes.

当涉及具有数百 GB 数据和行的大型表和大量写入情况时，索引可能会降低性能。
在我的个人情况下，我看到性能从使用二级索引的计数（*）的约 15 分钟下降到 2 个月后线性时间增加到 4300 分钟。
重新创建索引后，性能又回到了 15 分钟。

To date we have two options to do that:
1) OPTIMIZE TABLE (or ALTER TABLE)
Innodb doesn't support optimization so in both cases the entire table will be read and re-created.
This means you need the storage for the temporary file and depending on the table a lot of time (I've cases where an optimize takes a week to complete). This will compact the data and rebuild all indexes.
Despite not being officially recommended, I highly recommend the OPTIMIZE process on write-heavy tables up to 100GB in size.

迄今为止，我们有两种选择来做到这一点：
1) OPTIMIZE TABLE (或 ALTER TABLE)
Innodb 不支持优化，因此在这两种情况下，整个表都将被读取并重新创建。
这意味着您需要临时文件的存储空间并依赖于表很多时间（我有优化需要一周才能完成的情况）。这将压缩数据并重建所有索引。
尽管没有得到官方推荐，但我强烈推荐在大小不超过 100GB 的大量写入表上使用 OPTIMIZE 过程。

2) ALTER TABLE DROP KEY -> ALTER TABLE ADD KEY
You manually drop the key by name, you manually create it again. In a production environment you'll want to create it first, then drop the old version.
The upside: this can be a lot faster than optimize. The downside: you need to manually create the syntax.
"SHOW CREATE TABLE" can be used to quickly see which indexes are available and how they are called.

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

Appendix:
1) To just update statistics you can use the already mentioned "ANALYZE TABLE".
2) If you experience performance degradation on write-heavy servers you might need to restart mysql. There are a couple of bugs in current mysql (8.0) that can cause significant slowdown without showing up in error log. Eventually those slowdowns lead to a server crash but it can take weeks or even months to build up to the crash, in this process the server gets slower and slower in responses.
3) If you wish to re-create a large table that takes weeks to complete or fails after hours due to internal data integrity problems you should do a CREATE TABLE LIKE, INSERT INTO SELECT *. then 'atomic RENAME' the tables.
4) If INSERT INTO SELECT * takes hours to days to complete on huge tables you can speed up the process by about 20-30 times using a multi-threaded approach. You "partition" the table into chunks and INSERT INTO SELECT * in parallel.

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

回答by Jason

For basic cleanup and re-analyzing you can run "OPTIMIZE TABLE ...", it will compact out the overhead in the indexes and run ANALYZE TABLE too, but it's not going to re-sort them and make them as small & efficient as they could be.

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

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

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

However, if you want the indexes completely rebuilt for best performance, you can:

但是，如果您希望完全重建索​​引以获得最佳性能，您可以：

1. drop / re-add indexes (obviously)
2. dump / reload the table
3. ALTER TABLE and "change" using the same storage engine
4. REPAIR TABLE (only works for MyISAM, ARCHIVE, and CSV)

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

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

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

If you do an ALTER TABLE on a field (that is part of an index) and change its type, then it will also fully rebuild the related index(es).

如果您对字段（即索引的一部分）执行 ALTER TABLE 并更改其类型，那么它也将完全重建相关索引。