MySQL 8执行COUNT(*)时磁盘IO达100%，查询耗时过长求解决

针对你在CentOS 7/8上使用MySQL 8查询27GB的test.logs_sync表时，SELECT COUNT(*)耗时10分钟且磁盘IO占满的问题，以下是具体排查和解决步骤：

• 调整InnoDB缓冲池大小
  MySQL 8默认的innodb_buffer_pool_size可能不足以容纳大表的热数据，导致大量磁盘随机读取。建议将其设置为服务器物理内存的50%-70%（专用数据库服务器）：

  -- 临时生效（重启后失效）
  SET GLOBAL innodb_buffer_pool_size = 16*1024*1024*1024; -- 示例设置为16GB
  

  编辑/etc/my.cnf（或/etc/my.cnf.d/mysql-server.cnf）持久化配置：

  innodb_buffer_pool_size = 16G
  

  重启MySQL服务生效。

• 更新表统计信息
  不准确的表统计信息会导致优化器选择低效的执行计划。执行以下命令更新统计：

  ANALYZE TABLE test.logs_sync;
  

  若统计仍不准确，可调整采样页数：

  SET GLOBAL innodb_stats_persistent_sample_pages = 1000;
  

  再重新执行ANALYZE TABLE。

• 利用二级索引优化COUNT(*)查询
  InnoDB的COUNT(*)会优先选择最小的索引进行扫描（而非全表扫描聚簇索引）。给表创建一个轻量的二级索引：

  -- 选择一个非空的小字段（如主键id）创建索引
  ALTER TABLE test.logs_sync ADD INDEX idx_count_dummy (id);
  

  之后执行SELECT COUNT(*)时，优化器会自动使用这个更小的索引，大幅降低IO开销。

• 调整并行读取线程数
  MySQL 8支持并行扫描InnoDB表，调大并行线程数可提升扫描速度：

  SET GLOBAL innodb_parallel_read_threads = 8; -- 默认是4，可根据CPU核心数调整
  

  持久化配置需添加到my.cnf：

  innodb_parallel_read_threads = 8
  

• 使用近似计数（业务允许的情况下）
  如果不需要精确的行数，可直接查询系统表获取近似值，速度极快：

  SELECT TABLE_ROWS FROM information_schema.TABLES 
  WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'logs_sync';
  

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