InnoDB并发票在MySQL排序中的工作机制及相关问题

排序操作是否会应用InnoDB并发票？ #

只有带排序的写操作（比如INSERT ... SELECT ... ORDER BY、UPDATE t SET col=val ORDER BY idx_col这类涉及二级索引修改的操作）才会触发并发票的使用。纯读取的排序查询（SELECT ... ORDER BY）不会用到并发票——因为并发票本质是用来缓存二级索引的插入、更新、删除操作的，和读取行为无关。

排序查询时InnoDB并发票是如何减少的？ #

可以通过以下几种场景降低并发票的使用：

• 基于主键排序的写操作：主键是聚簇索引，InnoDB不会对聚簇索引使用并发票，这类操作完全不会产生并发票写入。
• 按二级索引顺序排序的批量写：如果批量写操作的顺序和目标二级索引的物理顺序一致，InnoDB会直接将数据写入索引页（因为索引页会被连续加载到缓冲池），无需缓存到并发票。
• 使用覆盖索引完成写操作：如果写操作仅修改覆盖索引包含的列，不需要回表访问聚簇索引，此时仅需修改目标覆盖索引，不会触发其他二级索引的修改，自然减少并发票的使用场景。
• 全局禁用并发票：通过设置innodb_change_buffering=none或innodb_change_buffer_max_size=0可以彻底禁用，但这是全局设置，会影响所有二级索引写操作的性能，不建议随意使用。

MySQL如何计算排序查询对应的并发票？ #

并发票没有针对单个排序查询的“计算”逻辑，它的触发和占用是基于二级索引页的缓冲状态：

• 当带排序的写操作要修改二级索引时，InnoDB会检查目标索引页是否在缓冲池中：
  • 若不在缓冲池，就将修改操作（记录要插入/更新/删除的索引项信息）缓存到并发票中。
  • 若在缓冲池，则直接修改索引页，不经过并发票。
• 并发票的最大占用空间由innodb_change_buffer_max_size控制（默认是缓冲池的25%，最大可设为50%），MySQL会根据缓冲池使用率、后台负载自动触发合并时机——比如缓冲池不足时，或者后台purge线程定期合并并发票中的操作到实际索引页。

排序查询扫描行数不足时是否会引发性能问题？ #

这里的“扫描行数不足”通常指优化器预估扫描行数与实际不符，或扫描行数过少导致执行计划选择不当，确实可能引发性能问题：

• 错误触发文件排序：如果优化器认为扫描行数少，使用索引排序的成本（比如回表开销）高于文件排序，就会选择在内存或磁盘中做文件排序。哪怕数据量小，频繁执行这类查询也会增加CPU和IO开销。
• 执行计划误判：若统计信息过时，优化器可能错误预估扫描行数，导致本该用索引排序的场景用了文件排序，反之亦然，产生不必要的性能损耗。
• 随机IO增加：如果扫描行数少但需要随机访问索引页，会导致并发票合并次数增加，或直接触发随机IO，影响整体性能。

InnoDB并发票针对排序查询的工作机制 #

仅针对带排序的写操作，工作流程如下：

1. 当执行带排序的写操作且涉及二级索引修改时，InnoDB先检查目标索引页是否在缓冲池：
   • 不在缓冲池：将修改操作缓存到并发票，避免立即加载索引页产生的随机IO。
   • 在缓冲池：直接修改索引页，不经过并发票。
2. 若排序后的写操作是按二级索引顺序进行的，索引页会被连续加载到缓冲池，大部分修改会直接写入索引页，仅少数不在缓冲池的页会进入并发票。
3. 后台purge线程会定期合并并发票中的操作到实际索引页；当有查询需要访问被并发票缓存的索引页时，也会触发即时合并，保证数据一致性。
4. 并发票的核心作用是减少随机IO，提升随机顺序修改二级索引的排序写操作性能。

排序查询执行时可能出现的性能问题 #

• 文件排序开销：无法利用索引排序时，MySQL会使用sort_buffer_size内存进行排序，内存不足时会生成临时文件，导致IO和CPU开销飙升，数据量大时尤为明显。
• 并发票合并竞争：大量排序写操作使用并发票后，后台合并操作会占用缓冲池和IO资源，影响其他查询的响应速度，甚至导致系统负载波动。
• 回表操作开销：排序查询若无法使用覆盖索引，需要回表获取数据，会产生大量随机IO，结果集越大，性能损耗越严重。
• 统计信息过时导致的执行计划错误：优化器依赖表统计信息选择排序方式，统计信息过时会导致低效执行计划，比如放弃索引排序选择文件排序。
• 缓冲池资源竞争：排序操作需要大量缓冲池内存存储排序数据或索引页，若缓冲池不足，会引发频繁的页换入换出，增加IO开销。

内容的提问来源于stack exchange，提问作者Kannan M