MySQL简单查询性能问题：COUNT(*)查询耗时过长

核心原因：InnoDB的COUNT(*)机制与你的索引特性 #

InnoDB作为事务型存储引擎，不会像MyISAM那样缓存表的总行数——因为MVCC（多版本并发控制）特性，不同事务可能看到不同的数据版本，所以执行COUNT(*)时必须扫描数据或索引来统计真实行数。

你的索引ix_2011_index存在两个可能导致扫描效率低下的问题：

• 这是非唯一索引，且索引列index允许NULL：虽然非唯一索引的叶子节点会包含所有行（包括index为NULL的行），但从基数292691对比数千万行的数据量能看出，该列重复度极高，索引的实际存储空间可能并没有比聚簇索引（主键索引）小太多，扫描速度提升有限。
• 优化器可能未选择该索引：MySQL优化器会优先选择体积最小的索引扫描COUNT(*)，如果你的二级索引因列长度过大、碎片过多等原因，实际体积接近聚簇索引，优化器可能会选择扫描包含全列数据的聚簇索引，自然耗时更长。

排查与解决方法 #

1. 确认执行计划
   用EXPLAIN查看优化器是否使用了目标索引：

   EXPLAIN SELECT COUNT(*) from mytable.2011;
   

   查看输出的type列是否为index，key列是否为ix_2011_index，以此判断优化器的索引选择逻辑。

2. 强制使用索引测试
   尝试强制指定索引执行查询，验证是否能提升速度：

   SELECT COUNT(*) from mytable.2011 FORCE INDEX(ix_2011_index);
   

   如果速度明显提升，说明是优化器选择问题，可后续调整索引或优化器参数。

3. 创建更适合COUNT(*)的索引
   最优方案是创建体积最小的二级索引，比如基于非空、数据长度极小的列，或虚拟列（MySQL 8.0+支持）：

   -- 示例：基于非空短列建索引
   CREATE INDEX ix_2011_small_col ON mytable.2011(short_non_null_col);
   -- 示例：创建常量虚拟列索引
   ALTER TABLE mytable.2011 ADD COLUMN dummy INT GENERATED ALWAYS AS (1) STORED;
   CREATE INDEX ix_2011_dummy ON mytable.2011(dummy);
   

   这类索引体积极小，扫描速度会远快于现有索引或聚簇索引。

4. 使用近似值替代（业务允许时）
   若不需要精确行数，可直接查询表状态获取估算值，几乎瞬间完成：

   SHOW TABLE STATUS LIKE '2011';
   

   结果中的Rows字段是InnoDB统计的近似行数。

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