MySQL 联合索引中的范围查询“截断”效应是数据库优化中最反直觉、也最容易被忽视的性能杀手。简单来说在联合索引(A, B, C)中如果A用了精确匹配但B用了范围查询,,BETWEEN,LIKE ...那么C列的索引将完全失效。这就好比你有一本按“省 - 市 - 区”排序的电话簿。如果你锁定了“广东省”然后说“我要找所有姓张的人”范围因为姓张的有很多那么在“姓张”的这个范围内“区”的信息是杂乱无章的你无法再利用“区”来快速定位只能一个个翻。一、核心原理B 树的“局部有序性”要理解截断必须理解 MySQL 联合索引B 树的排序规则先按第一列排序第一列相同再按第二列排序以此类推。1. 全局有序 vs 局部有序假设联合索引为(age, score)。数据在磁盘上的物理存储顺序如下age (第一列)score (第二列)状态185018601880范围起点18851890范围终点18951910下一组开始19201930现象对于age 18的所有行score是有序的50, 60, 80…。但是一旦age变成 19score直接从 10 开始重新排序。结论score的有序性仅依赖于age固定不变。2. 范围查询的破坏力当你执行WHERE age 18 AND score 80 AND score 90时引擎快速定位到age18且score80的位置。向右扫描直到score90。停止。因为再往右age可能变成 19 了而age19的数据不符合age18的条件。关键点来了如果此时还有一个条件AND class_id 5假设索引是(age, score, class_id)在age18且score在 (80, 90) 的这段小区间内class_id是有序的吗答案是不一定或者说无法利用其全局有序性进行二分查找。虽然在这个极小的范围内class_id可能是有序的但 MySQL 优化器通常认为范围查询之后的列失去了作为“导航灯塔”的能力。引擎必须在扫描到的每一行上单独检查class_id是否等于 5而不能直接跳到class_id5的位置。 核心洞察联合索引的排序是层级依赖的。上一层级如果是“一段范围”而不是“一个点”那么下一层级的排序就失去了全局意义无法用于快速定位Seek只能用于过滤Filter。二、直观图解电话簿隐喻想象一本巨大的电话簿索引顺序是【省份】 - 【姓氏】 - 【名字】。场景 A精确匹配无截断查询省份广东 AND 姓氏李 AND 名字明过程翻到“广东”部分。在“广东”里翻到“李”姓部分。在“李”姓里直接找到“明”。结果三步都利用了索引极速。场景 B范围查询发生截断查询省份广东 AND 姓氏 LIKE 张% AND 名字伟注意LIKE 张%是范围查询所有姓张的张三、张四、张五…。过程翻到“广东”部分。找到“张”姓的起始位置。开始逐页翻阅所有姓张的人因为姓张的是一个范围不是一个点。悲剧发生在翻阅“姓张”的这些页时你想找“名字‘伟’的人。你能直接跳到“张伟”吗不能因为在“姓张”这个范围内名字是按拼音排的但“张伟”可能分散在“张三”和“张四”之间或者这一页全是“张强”下一页才有“张伟”。你必须读完每一个姓张的人看他的名字是不是“伟”。结果省份用到索引。姓氏用到索引定位起始点。名字索引失效。变成了在内存中逐行判断if name 伟。三、实战案例与 EXPLAIN 分析表结构CREATETABLEusers(idINTPRIMARYKEY,ageINT,scoreINT,levelINT,INDEXidx_age_score_level(age,score,level));案例 1正常全用EXPLAINSELECT*FROMusersWHEREage20ANDscore90ANDlevel5;Extra:Using index condition(或类似)Key_len: 显示用到了 3 列的长度。结果完美三列索引都生效。案例 2中间截断EXPLAINSELECT*FROMusersWHEREage20ANDscore80ANDlevel5;分析age 20精确匹配生效。score 80范围查询生效但到此为止。level 5失效。Key_len: 只计算了age和score的长度level没算进去。Extra:Using index condition(表示 level 是在存储引擎层过滤的而不是通过索引树定位的)。后果数据库会取出所有age20且score80的行然后在这些行里逐个检查level是否等于 5。如果score80的数据有 10 万行就要检查 10 万次。案例 3最左缺失EXPLAINSELECT*FROMusersWHEREscore80ANDlevel5;结果索引完全失效除非触发索引合并但很少见。原因跳过了最左列ageB 树中score是无序的不同 age 的 score 混在一起。四、破解之道如何绕过“截断”既然知道了原理我们有哪些策略来优化策略 1调整索引顺序最推荐将范围查询的列尽量往后放将精确匹配的列往前放。原索引(age, score, level)新查询WHERE age 20 AND level 5 AND score 80新索引建议(age, level, score)效果age() - 精确OK。level() - 精确OK。score() - 范围放在最后。结果三列全部用到索引没有浪费。策略 2将范围转化为精确匹配特定场景如果范围很小或者可以枚举将其改为IN。原句WHERE score BETWEEN 80 AND 85改写WHERE score IN (80, 81, 82, 83, 84, 85)效果IN在某些版本和优化器下被视为多个精确匹配的组合可能会让后面的列重新生效取决于具体 MySQL 版本和优化器智能程度不保证 100% 有效但在某些场景有用。策略 3覆盖索引避免回表如果SELECT的字段都在索引里即使level没用到索引定位也不需要回表查数据直接在索引树上过滤即可性能损失较小。SQL:SELECT age, score, level FROM users WHERE age 20 AND score 80 AND level 5优势不需要访问主键索引聚簇索引减少 IO。策略 4强制优化器慎用在某些极端情况下可以使用FORCE INDEX强迫使用某个索引但这通常治标不治本需配合EXPLAIN验证。 总结截断效应的“三字经”现象原因对策范围即截断范围查询破坏了后续列的全局有序性范围列放最后最左不能丢跳过左侧列右侧列无序遵循最左前缀函数必失效对列运算导致无法匹配索引值保持列纯净终极心法联合索引就像一串密码锁。前面的数字必须完全对上精确匹配才能转动后面的轮盘。一旦中间有一个轮盘是“模糊范围”比如转到 80~90 之间后面的轮盘就被锁死了无法独立转动去对齐数字。设计索引的黄金法则把“等值查询”的列放在前面把“范围查询”的列放在最后面。行动指南审查 SQL找出所有包含BETWEENLIKE的联合索引查询。检查顺序确认范围查询的列是否位于索引的最末端如果不是考虑调整索引列顺序。EXPLAIN 验证永远不要猜用EXPLAIN看key_len和Extra字段确认是否有列被“截断”。