当有少量索引时，为什么MySQL InnoDB在大型表上插入/更新会变得非常慢？ [英] Why do MySQL InnoDB inserts / updates on large tables get very slow when there are a few indexes?

问题描述

我们有一系列有机增长到数百万行的表，在生产中进行插入或更新可能需要长达两秒钟。但是，如果我转储表并从转储中重新创建查询是快速的。

We have a series of tables that have grown organically to several million rows, in production doing an insert or update can take up to two seconds. However if I dump the table and recreate it from the dump queries are lightning fast.

我们通过创建副本重建索引然后执行一个表来重建其中一个表重命名切换并复制任何新行，这是有效的，因为该表只被附加到。这样做可以快速插入和更新插件。

We have rebuilt one of the tables by creating a copy rebuilding the indexes and then doing a rename switch and copying over any new rows, this worked because that table is only ever appended to. Doing this made the inserts and updates lightning quick.

我的问题：

为什么插入会随着时间的推移变慢？
为什么重新创建表并进行导入修复？
有没有办法可以重建索引而不锁定表进行更新？

Why do inserts get slow over time? Why does recreating the table and doing an import fix this? Is there any way that I can rebuild indexes without locking a table for updates?

推荐答案

这听起来像是

• 指数随时间的不平衡


• 磁盘碎片


• 内部innodb数据文件碎片

您可以尝试分析表foo 哪个没有锁定，只需几次索引潜水，需要几秒钟。

You could try analyze table foo which doesn't take locks, just a few index dives and takes a few seconds.

如果这不能解决问题，你可以使用

If this doesn't fix it, you can use

mysql> SET PROFILING=1;
mysql> INSERT INTO foo ($testdata);
mysql> show profile for QUERY 1;

你应该看到大部分时间花在哪里。

and you should see where most of the time is spent.

显然，当插入按PK顺序完成时，innodb表现更好，这是你的情况吗？

Apparently innodb performs better when inserts are done in PK order, is this your case?

这篇关于当有少量索引时，为什么MySQL InnoDB在大型表上插入/更新会变得非常慢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！