如何提升1500万行MyISAM转InnoDB后的SELECT查询性能

针对SELECT COUNT(pk)的专项优化 #

• 利用二级索引加速统计：InnoDB的聚簇索引包含所有字段（包括体积较大的field3），扫描聚簇索引做COUNT会加载大量数据。而二级索引（比如已存在的idx_field4）的叶子节点仅存储主键值，体积远小于聚簇索引。可以直接执行SELECT COUNT(*) FROM big_table（InnoDB会自动选择最小的索引来扫描），或者强制指定索引：SELECT COUNT(pk) FROM big_table FORCE INDEX (idx_field4);，能大幅减少扫描的数据量，提升查询速度。
• 预热缓存消除冷启动影响：调大innodb_buffer_pool_size后首次查询变慢，是因为缓存为空需要从磁盘加载数据。可以在业务低峰期执行一次索引扫描（比如SELECT field4 FROM big_table;），将idx_field4的内容加载到buffer pool，后续COUNT查询就能直接从内存读取，速度会显著提升。

通用InnoDB配置优化 #

• 合理设置innodb_buffer_pool_size：8G的配置如果服务器总内存在16G及以上是合理的，但要确保系统预留足够内存给操作系统和其他进程（避免触发swap）。缓存需要时间预热，持续运行一段时间后，缓存命中率提升，查询性能会逐步稳定。
• 开启缓存持久化：启用innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup参数，让MySQL在关闭时保存buffer pool缓存到磁盘，启动时自动加载，避免每次重启都要重新预热缓存。
• 降低事务IO开销：如果业务允许秒级数据丢失的风险，将innodb_flush_log_at_trx_commit设置为2，减少每次事务提交的磁盘IO次数，提升整体读写性能（默认值1为强一致性，IO开销大）。
• 启用独立表空间：确保innodb_file_per_table参数开启（MySQL 8.0默认已开启），每个表使用独立的表空间，便于后续碎片整理和空间回收。

表结构与维护优化 #

• 拆分大字段：field3为mediumtext大字段，会增大聚簇索引体积。如果业务场景允许，可将field3拆分到单独的关联表，减小主表聚簇索引的大小，提升索引扫描效率。
• 定期整理表碎片：在业务低峰期执行OPTIMIZE TABLE big_table;，整理表空间碎片，优化索引和数据的存储结构，提升访问效率（注意：该操作会锁表，需提前评估影响）。
• 监控缓存命中率：通过SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';查询缓存相关状态，计算命中率：(1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100，正常命中率应保持在99%以上，若过低需调整buffer pool大小。

内容的提问来源于stack exchange，提问作者Chris Barnes Clarumedia