MySQL 索引的 “最左前缀原则”，用查字典的例子讲透

前言

前两天和实习生聊天，他问我为啥MySQL的索引要讲究最左前缀。

我当时没直接回答，想着找个日常的例子才好说，不然光讲概念应该比较难理解。继而转念想到——这不就跟咱们小时候查新华字典一个道理嘛！

正文

先说查字典的例子

小学的时候老师教我们查新华字典，你想查一个字，得先找偏旁部首，再数笔画。

假如你想查“赵钱孙李”的赵字，你会怎么查？

肯定是先找走字旁，然后在走字旁里面数笔画。你不可能说，我就看这个字右边那部分，然后跳过偏旁直接去找，那肯定是行不通的，不合常理。

MySQL的索引就是这个意思。

再拿身份证号码举例

咱们国内的身份证号码，前六位是地区编码。比如110101开头的，一看就知道是北京东城区的。

假设现在有个用户表，建了个联合索引：

INDEX idx_area_birth (province, city, birthday)

这个索引有三个字段：省份、城市、生日。就像身份证号码的结构一样，从大到小排列。

能用上索引的查询

你要查北京朝阳区1990年出生的人：

WHERE province = '北京' 
  AND city = '朝阳' 
  AND birthday = '1990-01-01'

这个必然没问题，就像你翻通讯录，先找北京，再找朝阳区，最后找1990年的。一路定位下来，肯定是顺利的。

或者你只查北京的：

WHERE province = '北京'

这样也行，就找第一层，找到就算，后面不管了。

用不上索引的查询

但要是这么查：

WHERE city = '朝阳'

跳过省份直接查城市？就不好弄了。

就好比你拿着通讯录，不看省份，直接找朝阳区。全国好几个地方都有朝阳区呢，北京有朝阳区，长春也有朝阳区，非得把整本通讯录都翻一遍才行。

这就是为什么要最左前缀。

索引是按顺序建的，你得从最左边开始匹配。

graph TD
    A[联合索引树根节点] --> B[北京]
    A --> C[上海]
    A --> D[广东]
    B --> B1[朝阳]
    B --> B2[海淀]
    B --> B3[东城]
    B1 --> B1a[1990-01-01]
    B1 --> B1b[1991-05-20]
    B1 --> B1c[1992-08-15]
    
    style B1a fill:#90EE90
    style B1b fill:#90EE90
    style B1c fill:#90EE90

看这个树状结构，也比较清晰。

要找数据，必须从根往下走，一旦跳过北京这一层，就不知道该往哪个分支走了。

B+树的结构

MySQL的索引其实用的是B+树，这玩意儿可以简单理解成一个多叉排序树。

就像咱们前面说的通讯录，整个通讯录的目录就是根节点，先按省份分成几大类（北京、上海、广东这些）。每个省份下面又按城市分小类（北京下面有朝阳、海淀、东城），每个城市下面再按生日排列具体的人。

graph TD
    Root[索引根节点<br/>指向不同范围]
    
    Root --> L1[范围1<br/>province: 北京]
    Root --> L2[范围2<br/>province: 上海]
    Root --> L3[范围3<br/>province: 广东]
    
    L1 --> L1_1[北京+朝阳<br/>指向数据]
    L1 --> L1_2[北京+海淀<br/>指向数据]
    
    L2 --> L2_1[上海+浦东<br/>指向数据]
    L2 --> L2_2[上海+徐汇<br/>指向数据]
    
    L3 --> L3_1[广东+深圳<br/>指向数据]
    L3 --> L3_2[广东+广州<br/>指向数据]
    
    L1_1 --> Data1[实际数据记录]
    L1_2 --> Data2[实际数据记录]
    L2_1 --> Data3[实际数据记录]
    
    style Root fill:#FFB6C1
    style L1 fill:#87CEEB
    style L2 fill:#87CEEB
    style L3 fill:#87CEEB
    style Data1 fill:#90EE90
    style Data2 fill:#90EE90
    style Data3 fill:#90EE90

B+树就是这样的结构，如果跳过第一层（省份）直接查第二层（城市），数据库就不知道该往哪个分支走。它只能把所有分支都走一遍，那还要索引干嘛呢？

为什么要这样设计？

其实仔细想一下，挺简单的。

你在超市买东西，商品分类摆放。日用品在左边，食品在右边，生鲜在最里面。你要买个牙膏，直接去左边日用品区找就行，不会说让你在整个超市乱转悠。

数据库索引也是为了快速定位数据。

如果每次查询都要扫描全表，那数据库性能得有多差啊。

建立索引的时候，数据库会把数据按照索引字段的顺序排好。就像图书管理员会把书按分类、按作者、按书名整理好，这样查的时候才能快。

但这个排序也是有顺序的，你不能指望一堆书既按作者排序，又同时按书名排序。它必须先按一个维度排，再按另一个维度排。

实际工作中怎么用？

比方说现在有个订单表，建了个索引(user_id, order_time, status)，用户ID、下单时间、订单状态。

运营那边提了个需求，说要查某个时间段内所有已完成的订单。然后写了个SQL：

SELECT * FROM orders 
WHERE order_time BETWEEN '2024-01-01' AND '2024-12-31'
  AND status = '已完成'

结果这个查询慢得要死。为啥？因为它跳过了user_id这个字段，直接用order_time查，索引其实完全没有用上。

于是再加个索引(order_time, status)，问题得以解决。

所以建索引的时候，务必得想清楚业务上的查询场景，最忌讳闭门造车。

那问题就来了，建索引的时候，怎么决定字段的顺序呢？

这个要看你的业务场景。一般来说，把最常用的查询条件放最左边。如果某个字段几乎每次查询都会用到，那就把它放第一位。

还有就是区分度。假如某个字段只有几个固定值，比如性别只有男女，这种字段的区分度就很低，放在索引前面意义不大。

像用户ID、订单号这种，每条记录都不一样，区分度很高，适合放前面。

写在最后

说到底，还是得看具体的业务场景，理论归理论，实践起来还真说不好。

最好的办法就是建完索引后，先用 EXPLAIN 看看执行计划。 如果没用上你想要的索引，那就再改、再测。

我想这是一个程序员面对现实的必修课——在不断地试错中，接受自己的平庸，并为那一点点性能提升而努力。