MySQL 8.0 InnoDB 13万行表COUNT(*)查询耗时12秒优化咨询

优化方案 #

1. 调整InnoDB缓冲池配置 #

首先检查MySQL核心参数innodb_buffer_pool_size的取值，对于8G内存的服务器，若该服务器主要运行MySQL业务，建议将该参数设置为物理内存的50%_{70%，也就是4G}5.5G区间。当前服务器内存使用率已经达到80%，如果缓冲池配置过小，索引数据无法缓存到内存，每次查询都需要读取磁盘，会大幅拉慢查询速度。
修改方式是在my.ini配置文件中添加/调整如下配置，重启MySQL生效：

innodb_buffer_pool_size = 4G

MySQL 8.0支持动态修改参数，线上业务无法重启时可直接执行：

SET GLOBAL innodb_buffer_pool_size = 4 * 1024 * 1024 * 1024;

2. 创建更小的专用二级索引 #

InnoDB的全表COUNT查询会优先选择体积最小的二级索引进行扫描，降低IO开销。你当前表只有id_manual_type一个二级索引，且该字段允许为NULL，扫描时还需要额外的NULL判断逻辑，同时INT类型的索引体积还可以进一步压缩。
建议基于表中宽度最小的非空字段创建二级索引，比如你的表中active是tinyint(1) NOT NULL DEFAULT 1，占用空间极小，创建该字段的索引后，COUNT查询会直接扫描这个超小的索引树，13万行数据可以做到毫秒级返回：

CREATE INDEX idx_mytable_active ON mytable(active);

3. 整理表碎片 #

如果表频繁执行增删改操作，会产生大量碎片，导致索引体积膨胀，扫描耗时增加。可以在业务低峰期执行碎片整理命令，该操作会锁表，注意避开业务高峰：

OPTIMIZE TABLE mytable;

4. 非精确计数场景采用近似查询 #

如果你不需要完全精确的行数统计，可以直接查询系统表获取近似值，误差通常在5%以内，查询速度极快：

SELECT TABLE_ROWS 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = '你的数据库名' 
  AND TABLE_NAME = 'mytable';

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