MySQL查询使用possible_keys未列出的PRIMARY索引问题咨询

为什么主键索引PRIMARY未出现在possible_keys中却被选中 #

• 首先明确possible_keys的定义：该列仅展示MySQL优化器在评估WHERE条件过滤、表关联匹配阶段，认为可以用来加速数据筛选的索引。主键索引在本次查询中没有被用于过滤WHERE条件，因此不会出现在该列表中。
• 优化器选择主键的核心原因是你的查询携带了order by id asc limit 20语法：InnoDB的主键索引本身就是按id升序组织的B+树结构，优化器评估两种执行路径成本后选择了成本更低的方案：
  • 路径1：走你创建的index(payDate, actionType)二级索引，需要先筛出符合payDate、actionType条件的行，回表查询全字段，再过滤deleted <>1、isCoupayOrder=0的条件，最后对所有符合条件的行排序取前20条。如果符合payDate范围的行数较多，排序成本会非常高。
  • 路径2：走主键索引按id从小到大扫描，每扫一行就校验所有WHERE条件，凑够20条符合条件的记录就直接终止执行。如果符合条件的记录在主键序列中分布均匀，通常只需要扫描很少的行数就能拿到结果，成本远低于前者。

是否属于全表扫描 #

不属于全表扫描：

• EXPLAIN结果的type列是index（索引全扫描）而非ALL（全表扫描），且因为有limit 20的限制，优化器不需要扫描整个主键索引，只要找到20条符合条件的记录就会停止执行，实际扫描的行数通常远小于表的总行数。

优化建议 #

如果该查询实际执行速度不符合预期，可以调整联合索引覆盖更多过滤条件，避免回表和排序：

ALTER TABLE payments ADD INDEX idx_pay_date_action_filter(payDate, actionType, isCoupayOrder, deleted);

调整后二级索引可以直接过滤所有WHERE条件，优化器大概率会优先选择该索引执行。

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