联合索引几个字段最好

前面一篇文章，松哥和大家聊了 MySQL 中的索引合并，虽然 MySQL 提供了索引合并机制来提升 SQL 执行的效率，然而在具体实践中，如果能避免发生索引合并是最好的，毕竟这是没办法的办法，是一个下下策。发生索引合并大概率是因为我们索引在设计的时候就有问题，设计好联合索引，我们就能在一定程度上避免发生索引合并问题。

1. 联合索引1.1 什么是联合索引

联合索引就是数据表中的多个字段，共同组成一个索引。由于 InnoDB 中索引的数据结构是一个 B+Tree，当是一个联合索引的时候，排序的时候会首先按照联合索引的第一个字段排序，如果第一个字段的值相同，则按照第二个字段排序，如果第二个字段的值也相同，则按照第三个字段排序，以此类推。

举一个简单的例子，假设我有如下数据：

id username age address gender 1 ab 99 深圳 男 2 ac 98 广州 男 3 af 88 北京 女 4 bc 80 上海 女 5 bg 85 重庆 女 6 bw 95 天津 男 7 bw 99 海口 女 8 cc 92 武汉 男 9 ck 90 深圳 男 10 cx 93 深圳 男

现在我给 username 和 age 字段建立联合索引，那么 B+Tree 在排序的时候，会首先按照 username 排序，当 username 相同的时候，再按照 age 进行排序。画出来的 B+Tree 如下图：

如上图，bw 相同的时候，按照 age 进行排序。

如果我们想要在 MySQL 中，让联合索引发挥最大作用，就要充分考虑到联合索引中各字段的顺序。

1.2 联合索引顺序要考虑哪些因素？

在设计联合索引的时候，我们最容易想到的原则是查询条件影响了联合索引中各个字段的顺序，要根据查询条件来设计联合索引中各个字段的顺序。

实际上，除了上面提到的查询条件之外，联合索引的顺序还会影响到查询的排序和分组等，所以，设计联合索引的顺序可以算是一个真真正正的技术活。

2. 案例分析

松哥这里还是使用官方的案例吧，小伙伴们在公众号后台回复 mysql官方案例 可以获取到这个数据库脚本的下载地址。

在 MySQL 的官方案例中，有一个支付表 payment，如下图：

小伙伴们从图中可以看到，这个表中有一个 customer_id 和一个 staff_id，现在假设我想要按照这两个来进行搜索，例如执行如下 SQL：

ｓｅｌｅｃｔ * from payment where customer_id=1 and staff_id=2;

查询条件有两个，我想建立一个联合索引，那么究竟是把 customer_id 放在前面还是把 staff_id 放在前面呢？

一个比较常用的法则是看字段的选择性，选择性高的字段应该是放在前面。有的小伙伴可能还不清楚什么是字段的选择性，可以参考松哥之前的文章：前缀索引，在性能和空间中寻找平衡。

那么怎么获取各个字段的选择性呢？这个很好计算，一个 SQL 搞定，如下：

ｓｅｌｅｃｔ count(distinct customer_id)/count(1) as c,count(distinct staff_id)/count(1) as s from payment;

执行结果如下：

可以看到，customer_id 的选择性为 0.0373，而 staff_id 的选择性为 0.0001，那么在建立联合索引的时候，将 customer_id 放在第一列显然更合适一些，因为它的选择性更高（意味着字段里边重复的值相对来说会少一些），根据 customer_id 更容易锁定一行，查询效率要更高一些。

不过需要注意，上面的法则并非放之四海而皆准，还是要具体问题具体分析。在一些特别极端的情况下，索引选择性非常之低，那个时候就没有必要建立联合索引了。特殊情况甚至需要我们从业务逻辑上去解决。

松哥举一个例子来说明这个问题。

在我第一版的 vhr 中，当时有一个系统通知的功能，就是管理员可以给所有的用户群发消息。用户之间也可以互发消息，如果发送消息的时候，用户不在线，就需要先把消息存到数据库中，等用户上线了再推给用户，那么就需要一张表来保存消息。这个表中有一个字段就是消息发送者，由于网站经常需要发送通知，就导致这个字段的值分布非常不均，大约有 50% 的值都是 admin，剩下的 50% 则是其他普通用户，那么查询的时候，据此字段建立的联合索引，如果查询条件不是 admin，则过滤效果不错，如果查询条件是 admin，则过滤效果就非常差。对于这样的问题，我们就需要从业务上去解决，例如禁止根据 admin 去查询等等。总之，建立联合索引时，我们前面所所说的字段选择性最高的原则，并不是放之四海而皆准的，小伙伴们还是要具体情况具体分析。

3. 注意事项

由于联合索引也是存储在 B+Tree 中，如 1.1 小节图示，username 在整棵 B+Tree 中是有序的，但是从整体上来看，age 是无序的，所以对于联合索引在搜索的时候，需要满足最左匹配原则才是有效的，否则会失效。举例来说，如果查询条件里只有 age，则索引就会失效，因为顺着索引的 B+Tree 去查询满足条件的记录，得一个一个找，还不如直接遍历主键索引。