为什么在MySQL或MariaDB中SELECT COUNT(*) FROM TABLE有时会非常慢
内容提要
本文讨论了MySQL中SELECT COUNT(*) FROM TABLE查询速度慢的原因，包括明显和不明显的因素。明显的原因有表大小、存储引擎和并发工作负载，不明显的原因有MySQL变体和版本、事务上下文和表碎片化。文章还提到了MySQL和MariaDB版本之间的差异以及不同事务上下文中查询速度的差异。此外，还讨论了MySQL 8.0中并行读取线程和数据文件创建方式对查询结果的影响。总的来说，事务上下文对查询速度有重要影响，需要仔细考虑。
延伸解读
事务上下文：被忽视的性能杀手
文章通过对比实验揭示，当存在未提交的删除事务时，SELECT COUNT(*) 可能因 MVCC 机制而变得极慢。例如在 MariaDB 中，相同查询在有无未提交事务时性能相差约 340 倍。这是因为查询需要读取旧版本数据，导致大量额外的页读取。读者在排查慢查询时，应检查是否有长时间未提交的事务，尤其是涉及大量行修改的情况。
版本差异：MySQL 与 MariaDB 的性能陷阱
不同 MySQL 和 MariaDB 版本对 COUNT(*) 的处理差异巨大。MySQL 5.7.18 至 5.7.44 在独立事务中变慢，而 MySQL 8.0.13-8.0.16 也存在类似问题。MariaDB 自 10.3 起，即使在同一事务中执行 COUNT(*) 也会变慢，性能下降高达 37000%。因此，升级或迁移时需针对具体版本进行测试，避免因版本特性导致性能回退。
并行读取：MySQL 8.0.20+ 的磁盘读放大问题
MySQL 8.0.20 及以后版本中，并行读取线程（innodb_parallel_read_threads）在表大于缓冲池时，会导致不必要的磁盘读取，反而增加查询开销。测试显示，在缓冲池较小时，使用多线程的 COUNT(*) 比单线程更慢，且消耗更多 CPU。若遇到此类性能问题，可尝试将 innodb_parallel_read_threads 设置为 1 来规避。
表优化与数据文件创建方式的影响
文章指出，即使表数据相同，数据文件的创建方式也会影响 COUNT(*) 性能。例如，通过 INSERT 逐行插入创建的表，与经过 OPTIMIZE TABLE 重建的表，在相同查询下耗时可能相差一倍以上（17 秒 vs 36 秒）。这可能与页填充率和碎片化有关。定期优化表或关注数据文件组织方式，有助于维持查询性能。
Q&A
为什么在MySQL中执行SELECT COUNT(*)查询会很慢？
查询速度慢的原因包括表大小、存储引擎、并发工作负载等明显因素，以及MySQL版本、事务上下文、表碎片化等不明显因素。
MySQL和MariaDB在处理COUNT查询时有什么区别？
MySQL和MariaDB在不同版本中处理COUNT查询的性能差异显著，尤其是在存在未提交数据时，MariaDB的性能通常较差。
事务上下文如何影响MySQL的查询性能？
事务上下文会影响查询性能，特别是在存在未提交数据时，可能导致查询速度显著变慢。
MySQL 8.0版本中并行读取线程对查询性能有什么影响？
MySQL 8.0中的并行读取线程在数据不适合内存时，可能导致更多的磁盘读取，从而影响查询性能。
如何优化MySQL中的COUNT查询性能？
可以通过优化表结构、减少并发事务、调整内存缓存等方式来提高COUNT查询的性能。
MySQL 5.7版本在COUNT查询中表现如何？
MySQL 5.7在某些上下文中表现较快，但从5.7.18版本开始，在不同事务上下文中性能显著下降。
