MySQL主键顺序插入和随机插入对性能的影响

本文是针对聚簇索引

首先来看顺序插入的情况。如果主键是顺序的，所以InnoDB会把每插入的记录存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB默认的最大填充因子是15/16，流出不封空间用于以后可能产生的修改），下一条记录就会写入新的页中。一旦数据按照这种顺序的的方式插入，主键就会近似于被顺序的记录填满。

再来看随机插入的情况，比如使用了uuid聚簇索引的表插入数据。因为新插入的值是随机，可能比上一个插入的主键值大，也可能小，所以InnoDB无法简单的总是把新的记录插入到索引的最后，也就是说插入的位置很有可能是在现有数据的中间。这往往会导致性能恶化。

这种随机插入方式可能会有以下缺点：

1. 写入的目标页可能不在内存缓存区，那么插入记录的时候需要先从磁盘读取目标页到内存中。这会导致大量的随机IO.如果是顺序插入，由于是插入到上一个记录的后面，则大多数情况下（不需要开辟新页的情况）磁盘页是已经加载到内存了的。
2. 因为写入是乱序的，InnoDB可能需要不断的的做页分裂操作，以便为新的行分配空间。而页分裂会导致移动大量的数据，而且一次分裂至少要修改三个页而不是一个页。
3. 由于频繁的分页，页面会变得稀疏并被不规则的填充，最后会导致数据碎片。

所以，当把随机值载入到聚簇索引后，最好做一次optimize table来重建表并优化页的填充。

经过以上分析，使用InnoDB引擎时最好尽可能的按主键顺序插入数据，并且尽可能地使用单调递增的聚簇键值来插入新行。

当然顺序的主键在一些情况下也有可能有一些缺点。比如对于高并发的场景，如果按主键顺序插入可能会造成明显的争用。因为所有的插入都是上一次记录的后面，所以主键的上边界就会成为热点，这可能会导致间隙锁竞争。如果是随机插入的话，因为插入的地方可能都不一样，所以竞争就是少一点。

另一个问题可能是AUTO_INCREMENT锁机制。 即InnoDB采用一种特殊的表锁机制来保证并发插入下自增操作依然是串行操作，为提高插入效率，该锁会在插入语句完成后立即释放，而不是插入语句所在事务提交时释放。该设计并发性能太差，尤其在大批量数据在一条语句中插入时(INSERT SELECT ), 会导致该语句长时间持有这个“表锁”，从而阻塞其他事务的插入操作。对于这个问题，可能需要考虑重新设计表或者应用，或者更改innodb_autoinc_lock_mode配置。