为什么MySQL InnoDB在大型表上插入/更新会在有几个索引时变得非常慢？
我们有一系列有机地增长到数百万行的表，在生产中插入或更新可能需要两秒钟。但是，如果我转储表并从转储重新创建查询闪电般快速。为什么MySQL InnoDB在大型表上插入/更新会在有几个索引时变得非常慢？
我们重建了其中一个表，方法是创建一个副本，重建索引，然后执行重命名切换并复制任何新行，因为该表只会附加到该表上。这样做使插入和更新迅速闪电。
我的问题：
为什么插入会随着时间变慢？ 为什么重新创建表并执行导入修复此问题？ 有没有什么办法可以重建索引而不锁定更新的表？
这听起来像它要么
- 指数不平衡随着时间的推移
- 磁盘碎片
- 内部InnoDB的数据文件（一个或多个）碎片
你可以尝试analyze table foo不采取锁，只是几次索引潜水，需要几秒钟。
如果仍不能解决问题，则可以使用
mysql> SET PROFILING=1;
mysql> INSERT INTO foo ($testdata);
mysql> show profile for QUERY 1;
，你应该看到的大部分时间都花在。
显然innodb在插入按PK顺序完成时表现更好，这是你的情况吗？
更新表格需要重建索引。如果您正在进行批量插入，请尝试在一个事务中执行它们（如转储和还原一样）。如果表格是写有偏见的，我会考虑放弃索引，或者让后台作业读取表格处理（例如，将其复制到索引表中）。
我不认为你读了这个问题，插入/更新非常快，当索引已经重建，即。通过优化表格，当表格增长有机时，它们只会变慢。我想知道这是为什么。 – Greg 2010-02-09 22:09:35
出于某种原因，我偏向于你进行批量更新，即有时添加更多行，有时会增加更少的突发。我认为一旦你放弃指数，它就会很快。 – hurikhan77 2010-02-09 23:16:19
我还没有尝试删除索引，我不能在生产中这样做，只要我在测试机器上导入转储的速度很快。我认为放弃指数会让它变快。我可以尝试将数据文件复制到测试机器来测试。 – Greg 2010-02-09 23:32:20
难道是由于XFS的碎片？
复制/从http://stevesubuntutweaks.blogspot.com/2010/07/should-you-use-xfs-file-system.html粘贴：
要检查驱动器的碎片级别，例如位于的/ dev/SDA6：
须藤xfs_db -c断枝-r的/ dev/SDA6
结果看起来就像这样：
实际51270，理想的174，断裂因子99.66％
这是我第一次安装这些实用程序的实际结果，以前没有XFS维护知识。很讨厌。基本上，分区上的174个文件分散在51270个独立的部分。要整理，运行以下命令：
须藤xfs_fsr -v的/ dev/SDA6
让它运行一段时间。 -v选项让它显示进度。它完成后，请再次检查碎片级别：
须藤xfs_db -c断枝-r的/ dev/SDA6
实际176，理想174，断裂因子1.14％
好多了！
InnoDB性能严重依赖于RAM。如果索引不适合内存，性能可能大幅下降。重建整个表可以提高性能，因为数据和索引现在已经过优化。
如果您只是插入表中，MyISAM更适合这种情况。由于记录被添加到文件的末尾，因此如果仅追加，则不会出现锁定问题。 MyISAM还允许您使用MERGE表格，这些表格非常适合将部分数据脱机或存档，而无需执行导出和/或删除操作。
追踪正在使用my.ini并增加key_buffer_size我有一个1.5GB的表格，其中每秒查询次数（全部写入）下降到17个大关键字。我发现奇怪的是在管理面板（当表被锁定写入以加速进程时），它每秒执行200次InnoDB读取，每秒写入24次。
它*从磁盘读取索引表。我将key_buffer_size从8M改为128M，性能跳跃到每秒完成150次查询，只需执行61次读取即可获得240次写入。 （重启后）
对于innoDB，innodb_buffer_pool_size直接影响存储在内存中的行/索引的数量，这减少了写入之前的冲突查找。 – ppostma1 2014-02-28 22:15:19
key_buffer_size对innodb没有影响，它是myisam设置。 – 2016-02-03 22:05:38
存储引擎？文件系统？ MySQL版本？记录非常大吗？ – ggiroux 2010-02-08 16:28:17
对不起，应该包括这个问题！ InnoDB，XFS上有一个15k磁盘的RAID阵列。 – Greg 2010-02-08 17:12:32