MySQL8.0二级覆盖索引查询返回主键列性能更慢原因排查

两条SQL的执行计划一致是正常现象：EXPLAIN只会展示优化器选择的访问路径、索引选择等粗粒度信息，不会体现执行阶段的细粒度优化逻辑，两者实际扫描、结果构造的逻辑存在本质差异，核心原因如下：

• S1触发了等值查询的常量返回优化，S2无法利用该优化
  S1查询的issue_code正好是WHERE子句中等值匹配的常量'1104'，在ref类型的二级索引扫描过程中，执行器明确知道所有符合条件的返回行中，该字段的值必然等于这个常量，根本不需要从二级索引记录中实际读取、解析字段值。执行时只需要顺着二级索引的记录链表计数，凑够LIMIT要求的20万条记录即可，不需要解析记录体中存储的业务数据，扫描开销极低。
  而S2查询的id是每条记录唯一的主键值，不存在常量替换的可能，必须逐行读取每一条二级索引记录尾部存储的8字节bigint类型主键值，逐行做值校验、内存拷贝，必须完整解析每一条记录的存储结构才能拿到目标值，扫描开销天然更高。
• InnoDB二级索引存储格式进一步放大了开销差距
  从执行计划的rows估算值可以看到，符合issue_code = '1104'的记录超过22万条，这些连续存储在二级索引页中的记录索引列值完全相同，InnoDB会对相邻同值的索引记录做前缀压缩：不会重复存储完整的索引列值，只保留必要的记录偏移指针。S1不需要读取索引列之外的任何值，扫描时只需要顺着记录的next_record指针做链表遍历即可，几乎不需要访问记录的实际数据区，属于纯内存指针操作，速度极快。
  而S2要拿到记录尾部的主键id，必须逐行解析每条记录的变长字段长度列表、记录头信息，计算偏移量跳过前面的索引列存储区，才能定位到主键值的存储位置。20万行量级下，这种逐行解析、偏移计算的开销会被放大多倍。
• 结果集构造开销差异直接导致上下文切换、缺页异常激增
  S1返回的20万行数据中，所有行的issue_code值完全相同，构造结果集时可以直接批量填充固定常量值，内存拷贝、序列化的开销极低，CPU占用少，几乎不会触发内存申请导致的缺页异常，也很少因为时间片耗尽、资源等待触发上下文切换，和profile中S1的CPU消耗、上下文切换数据完全吻合。
  S2返回的20万个id值全部不同，需要逐行申请内存空间、拷贝不同的id值、序列化成MySQL结果集的传输格式，CPU用户态消耗是S1的6倍以上，CPU时间片用完时自然会触发大量非自愿上下文切换；同时申请新内存存储不同id值会触发主缺页异常，内存分配、网络发送缓冲区等待时也会触发自愿上下文切换，和profile中S2的监控数据完全对应。

验证方式 #

可以通过两个测试快速验证上述结论：

1. 执行select id,issue_code from test WHERE issue_code = '1104' limit 200000;，因为需要读取id字段，无法触发常量返回优化，耗时会和S2基本持平。
2. 将查询条件改为范围匹配或多值匹配，比如select issue_code from test WHERE issue_code in ('1104','1105') limit 200000;，此时返回的issue_code存在多个可能值，无法直接用常量替换，S1类查询的耗时会明显上升。

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