工作中用好 MySQL 的联合索引，你需要注意这些“手段”！

前言

在之前《带你扒一扒 MySQL 的数据在磁盘上到底长什么样子…》一文中和大家聊了 MySQL 中的数据在磁盘上，存储的形式都是怎样的。感兴趣的可以回顾一下。

然而最后还留了个尾巴，今天来补上：工作中用到最多的联合索引和最左前缀原则是怎么一回事。

定义

多个字段组成的索引。

我们之前讲的都是单值索引，就是只用到了一个字段，而现在是多个字段。

SQL 语句

表创建之前，在建表语句最后加上 INDEX 索引名称(字段名1, 字段名2, ...) USING BTREE

CREATE TABLE t_employee  (
  id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  create_time datetime NOT NULL,
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
  name varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  age int(10) UNSIGNED NOT NULL COMMENT '年龄',
  dept varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '部门',
    
  PRIMARY KEY (id) USING BTREE,
  INDEX idx_name_age_dept(name, age, dept) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '员工表' ROW_FORMAT = Compact;

在已有表的基础上建立联合索引，执行语句：create index 索引名称 on 表名(字段名1, 字段名2, ...);

create index idx_name_age_dept on t_employee(name, age, dept);

如果你要创建的是唯一性索引，在 index 前加上 unique 就好了，否则就是普通索引。

UNIQUE INDEX uk_name_age_dept(name, age, dept) USING BTREE

create unique index uk_name_age_dept on t_employee(name, age, dept);

在磁盘上的存储形式

插入10条记录

之前说过， MySQL 在磁盘上的数据结构是以 B+tree 进行存储的。

并且除主键索引外，其它的索引都叫二级索引：叶子节点上的数据除索引本身只存储主键值。

底层的叶子节点的 name 字段是有序的，从左至右依次递增；

从全局来看，age 字段和 dept 字段是无序的；

但从局部来看，当第一个字段 name 相等时，会以 age 字段值进行排序；如果 age 还相等，那就以第三个字段来排序。

底层查找过程

在底层查找数据比对的时候，会先对第一个字段(name)进行比较，如果相同再比较第二个(age)，同理相同再比较第三个(dept)。

在数据页目录（数据节点）中，会通过二分法快速定位，然后再遍历对应区间中的记录即可找到至具体的数据项。

找到了数据后会将叶子节点上存储的主键值(1)回表去聚集索引树上找到完整的记录读取出来。

最左前缀原则

其实最左前缀原则就是在联合索引 B+tree 中数据查找的方式，也就是我们刚刚说的底层查找过程。

查询的时候最左边的索引列如果不用，索引一定失效，但用了只是可能有效。

下面直接举各种例子，来看是否用到了索引：idx_name_age_dept(name, age, dept)

语句	效果	说明
select * from t_employee	❌	没有查询条件，直接全表查
~ where name = 'HanMeimei'	✔	索引用到了 name 字段
~ where name = 'HanMeimei' and age = 18	✔	索引用到了 name 和 age 字段
~ where age = 18 and name = 'HanMeimei'	✔	索引用到了 name 和 age 字段。查询优化器会分析搜索条件并且按照可以使用的索引中列的顺序来决定先使用哪个搜索条件，后使用哪个搜索条件。
~ where name = 'HanMeimei' and age < 18	✔	索引用到了 name 和 age 字段
~ where name = 'HanMeimei' and age = 18 and dept = 'finance'	✔	索引用到了 name, age 和 dept 字段
~ where name = 'HanMeimei' and dept = 'finance'	✔	索引只用到了 name 字段；通过 `B+tree` 查到了 name = 'HanMeimei' 的节点后再从这里面挨个比较 dept 字段
~ where age = 18	❌	查询条件没有带上最左边的 name 字段，直接全表查
~ where name < 'WangWu' and age = 18	✔	索引只用到了 name 字段；通过 `B+tree` 查到了 name < 'WangWu' 的节点后再从这里面挨个比较 age 字段
~ where name like '%Han%'	❌	最左边是%，具体的值是模糊不定的，没办法在 `B+tree` 中进行值比较。
~ where name like 'Han%'	✔	索引用到了 name 字段；通过 `B+tree` 查到了 name 开头是 'Han' 的数据
~ where name = 'HanMeimei' or age = 22	❌	用不上索引，但是 5.6 之后会采用索引合并的方式查询该 SQL：index(name) index(age)
~ where substring(name, 3) = 'hhh'	❌	用不上索引，在索引列上进行隐式转换、计算或者函数操作都会失效

优点

减少建立索引的开销

拿我们刚刚的索引来说，idx_name_age_dept(name, age, dept) 相当仨于同时建立了3个索引

idx(name)
idx(name, age)
idx(name, age, dept)

覆盖索引

覆盖索引是 select 的数据列只用从索引中就能够取得，不必读取数据行。

也就是我们要查的字段信息可以直接通过我们建立的联合索引树结构中直接拿到，不用拿到主键值后再进行回表查询。

我们之前的语句是 select * from t_employee where xxx，查询的是所有字段信息。

而现在如果是 select name, age, dept from t_employee where name ='HanMeimei' and age = 18 ，就不需要回表查询数据。

name, age 和 dept 字段的数据都可以直接在自己建立的联合索引结构上拿到。

这是SQL优化的一种重要手段，有时候还会人为地构造覆盖索引，这种查询方式，就叫覆盖索引。

有效缩小筛选范围

现在我们这张表有 1000w，查询语句为 select * from t_employee where name = 'HanMeimei' and age = 18 and dept = 'finance'，假设每个条件能够筛选出 10% 的数据。

当没有联合索引时，只有单值索引，那么通过该单值索引筛选出 10% 的数据后，再从 100w 数据里面去回表查询。
当有联合索引时，那么通过该索引筛选出 10% × 10% × 10% 的数据后，再从 1w 数据里面去回表查询，效率就高了很多。

排序更快

比如现在有一个查询需要排序：SELECT * FROM t_employee WHERE name = 'HanMeimei' ORDER BY create_time;

将查出的结果用 create_time 字段进行排序。

使用 explain 执行计划看看效果

当排序使用联合索引中的 age 字段时：SELECT * FROM t_employee WHERE name = 'HanMeimei' ORDER BY age;

Using index condition : 查询使用了索引，但是数据还需要回表获取。

Using filesort : 采用了文件排序。

可以发现后者的排序方式会比前者少一步 filesort 操作。

filesort 文件排序其实就是当我们在查询数据后，在内存中或者磁盘上进行排序的方式。有的时候可能查询的结果集太大以至于不能在内存中进行排序的话，还可能暂时借助磁盘的空间来存放中间结果，排序操作完成后再把排好序的结果集返回到客户端。

而当你需要进行排序的字段就在索引中，因为索引 B+tree 本身就是有序的，少了文件排序这部操作也会大大提升效率。

注意

联合索引列的顺序

联合索引列的顺序是很重要的

index(name, age) 不等同于 index(name) + index(age)

index(name, age) 不等同于 index(age, name)

被索引的字段值为 null

看了很多资料，说啥的都有…实验是检验真理的唯一标准

现在将 name 和 dept 字段改成可以为 null

并且加一个 hhh 字段，联合索引和单值索引都设置成唯一索引。

插入 null 数据，看效果👇

结果可以看出，两个唯一索引，都可以插入 null 值。

查询值为 null 的数据

使用 explain 执行计划验证查询时索引是否生效。
- 验证联合索引 uk_name_age_dept 是否有效
```
EXPLAIN SELECT * FROM t_employee WHERE ISNULL(name);
```
这里是能看出用到了联合索引的；
- 验证单值索引 idx_hhh 是否有效
```
EXPLAIN SELECT * FROM t_employee WHERE ISNULL(hhh);
```
这里是能看出也是用到了单值索引的；

现在我们做点改变：把前10行的值全部设成 null

再走一遍执行计划

这时候发现这次查询根本就没有用到我们的索引 idx_hhh，过程是直接走的全表查询。原因如下：

在 MySQL 里有个叫查询优化器的东西，它会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数。

需要回表的记录数越多，就越倾向于使用全表扫描；

反之倾向于使用二级索引 + 回表的方式。

当然优化器做的分析工作不仅仅是这么简单，但是大致上是个这个过程。

一般情况下，限制查询获取较少的记录数会让优化器更倾向于选择使用二级索引 + 回表的方式进行查询，因为回表的记录越少，性能提升就越高。

所以我们在前10行记录 hhh 字段都有值的情况下，去查 null 就走了索引；

后面再把前10行记录 hhh 字段都变成 null 的时候，表中这字段大部分都为 null，查询优化器提前知道了，就直接全表查了。

其实 MySQL 官网也有作相关解释

翻译过来就是 UNIQUE 索引允许可以包含 NULL 的列使用多个 NULL 值。

这块也说明了 MySQL 可以使用索引搜索 NULL 数据。

但是我们在设计数据库表的时候，最好还是有一个将字段设为 NOT NULL 的习惯，至于 NULL 会带来哪问题，今天的另一篇文章会告诉你答案。