日常排查 SQL 时经常会听到“最左匹配失效”“范围查询后索引失效”“有索引却没走”。这些说法如果脱离 B树的排序结构很容易只记住结论遇到组合条件又不知道怎么判断。本文从 InnoDB 联合索引的字典序出发梳理常见场景中“为什么不能继续利用索引定位”以及“为什么优化器明明可以用索引却选择全表扫描”。1. 联合索引不是三列各自排序对于联合索引INDEX(a,b,c)叶子节点中的键按照字典序排列先按a排a相同时按b排a、b都相同时才按c排。abc123125141217232因此a1 AND b2 AND c5可以沿着 B树不断缩小范围最终定位到非常小的叶子区间。后面所有场景的判断基础都是这份有序性还能不能继续使用。2. 不满足最左匹配后续列不能继续用于定位INDEX(a,b,c)下WHERE b10没有a的起点无法从根节点判断应该进入哪个a分支。因为单独的b在整棵(a,b,c)索引中并不连续。WHERE a1 AND c5则可以先利用a1把范围缩小但缺少中间列bc5不能再用于继续缩小 B树扫描边界。这里不应说“整个索引都失效”更准确的表述是从最左列开始连续匹配中间断开后后续列不能继续用于索引定位。3. 函数、运算和隐式类型转换普通索引保存的是原始索引值而不是计算后的表达式结果。-- name 有普通索引WHEREUPPER(name)TOM;-- age 有普通索引WHEREage120;树中保存的是Tom、19而不是UPPER(name)、age1的结果。若没有对应函数索引或生成列索引数据库通常需要对候选记录逐条计算再比较不能按照原索引顺序直接查找。隐式转换也应单独检查。若code是varchar且有索引使用WHERE code 123可能触发对列的数值转换效果类似于在索引列上加函数。应优先保持参数与列类型一致例如WHERE code 123。实际是否转换以及是否走索引需要结合列定义、比较规则和EXPLAIN确认。4.LIKE %关键词为什么无法利用普通索引name LIKE 张%能使用前缀有序性所有“张”开头的字符串在索引叶子节点中形成连续区间B树可以直接定位到该区间起点并向后扫描。name LIKE %张则不知道从哪个前缀开始。可能命中“李张”“王张”“赵张”这些记录散落在不同位置普通 B树不能确定查找起点。后缀或任意位置搜索应考虑全文索引、倒排索引或按业务维护反转列。5. 范围查询为什么会截断后续列看联合索引(a,b,c)WHEREa1ANDb10ANDc5;a1与b10可以定义索引范围。但这个范围横跨多个b值后c只在每个固定b内局部有序并不是全局连续有序。例如b11,c100、b12,c1、b13,c80在b10的范围内c的顺序是100、1、80因此c5通常不能继续缩小扫描范围。后续列仍可能参与索引条件下推ICP或覆盖索引过滤但这与“继续用 B树定位”是两件事。6. 有索引却全表扫描优化器的成本选择即使 SQL 的条件符合索引顺序优化器也可能主动不用索引。以gender为例若只有“男、女”两种值WHERE gender男很可能命中接近一半的数据。走二级索引后再大量回表的随机 I/O可能比顺序扫描整张表更贵。同样低选择性字段、!、、NOT IN或返回行数很多的OR条件也可能让优化器认为全表扫描更便宜。这不是索引“语法失效”而是基于统计信息的成本选择。真正排查时应通过EXPLAIN或EXPLAIN ANALYZE观察访问类型、预估与实际行数、是否回表再结合数据量和选择性调整 SQL 或索引设计。7. 补充B树的节点结构B树的非叶子节点只保存索引键和子页指针用于判断下一层该走哪个分支真正的索引记录位于叶子节点。叶子节点按键值有序并通过双向链表连接因此等值查询可以定位到单个叶子位置范围查询则从起点沿链表顺序读取。8. 补充一次索引定位发生了什么以id35为例查询会在根节点比较键值进入对应的内部节点再进入包含35的叶子节点。B树每层都是一次页内查找树高通常很低因此可以用很少的页访问完成等值定位。9. 补充二级索引为什么会产生回表成本InnoDB 的二级索引叶子节点通常保存“二级索引键 主键值”而不是整行数据。若查询字段不被二级索引覆盖数据库需要先从二级索引找到主键再到聚簇索引按主键取整行这一步称为回表。命中行较少时回表成本可接受命中行很多时大量随机读取会使优化器更倾向于全表顺序扫描。
