MySQL索引优化疑问：同查询用不同索引为何扫描行数差千万？

问题背景 #

我们有三个虚构表Parts、Brand和Warehouse：

• Parts表包含字段：id、brand_id、warehouse_id
• 拥有两个索引：
  • 单列索引：index_parts_on_brand_id（仅包含brand_id字段）
  • 复合索引：index_parts_on_brand_id_and_warehouse_id（包含brand_id + warehouse_id字段）

执行查询语句：

SELECT COUNT(*) FROM `parts` WHERE `parts`.`brand_id` = {some_id};

当查询某拥有700万条零件数据的品牌时，MySQL执行计划自动选择了复合索引。强制使用单列索引的查询语句为：

SELECT COUNT(*) FROM `parts` FORCE INDEX (index_parts_on_brand_id) WHERE `parts`.`brand_id` = {some_id};

两种索引的执行计划参数对比：

单列索引index_parts_on_brand_id #

• type: ref
• extra: Using index
• 扫描行数：13,012,370

复合索引index_parts_on_brand_id_and_warehouse_id #

• type: ref
• extra: Using index
• 扫描行数：2,391,314

实际该品牌仅有700万条数据，但单列索引的扫描行数估算值远高于复合索引，且与实际数据量不符，核心原因如下：

核心原因分析 #

1. MySQL统计信息过时或不准确 #

MySQL优化器完全依赖表和索引的统计信息来估算扫描行数。如果统计信息存在以下问题，就会导致估算值严重偏离实际：

• 单列索引的统计信息长时间未更新，或者统计采样时未覆盖到该品牌的大量数据，导致优化器错误高估了需要扫描的行数。
• 复合索引的统计信息更新时间更近，或采样样本更具代表性，因此估算的扫描行数更接近实际700万的量级。

2. 复合索引的结构特性提升了估算精度 #

复合索引(brand_id, warehouse_id)的叶子节点先按brand_id排序，再按warehouse_id排序，相同brand_id的记录在索引中会更集中存储。这种结构下，MySQL优化器能更精准地定位该品牌对应的索引区间，从而给出更合理的行数估算。
而单列索引仅包含brand_id，若该品牌的记录因数据插入顺序等原因在索引中分布较为碎片化，优化器就容易高估扫描行数。

3. 强制索引绕过了优化器的最优选择 #

MySQL自动选择复合索引，是因为优化器通过统计信息判断它的查询成本更低。当使用FORCE INDEX强制指定单列索引时，优化器无法基于实际的索引效率调整判断，只能依赖该索引的过时或不准确的统计信息给出估算值，最终出现了估算行数远高于实际的情况。

验证与解决建议 #

• 执行ANALYZE TABLE parts;更新表的统计信息，之后重新查看两种索引的扫描行数估算，大概率会回归合理范围。
• 检查单列索引的碎片化情况，若碎片率较高，可执行OPTIMIZE TABLE parts;（注意该操作会锁表，需在业务低峰期进行）整理索引碎片，再对比估算值。

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