关于mysql:联合索引该如何选择合适的列

后面一篇文章，松哥和大家聊了 MySQL 中的索引合并，尽管 MySQL 提供了索引合并机制来晋升 SQL 执行的效率，然而在具体实际中，如果能防止产生索引合并是最好的，毕竟这是没方法的方法，是一个下下策。产生索引合并大概率是因为咱们索引在设计的时候就有问题，设计好联结索引，咱们就能在肯定水平上防止产生索引合并问题。

1. 联结索引

1.1 什么是联结索引

联结索引就是数据表中的多个字段，独特组成一个索引。因为 InnoDB 中索引的数据结构是一个 B+Tree，当是一个联结索引的时候，排序的时候会首先依照联结索引的第一个字段排序，如果第一个字段的值雷同，则依照第二个字段排序，如果第二个字段的值也雷同，则依照第三个字段排序，以此类推。

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

当初我给 username 和 age 字段建设联结索引，那么 B+Tree 在排序的时候，会首先依照 username 排序，当 username 雷同的时候，再依照 age 进行排序。画进去的 B+Tree 如下图：

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

如果咱们想要在 MySQL 中，让联结索引施展最大作用，就要充分考虑到联结索引中各字段的程序。

1.2 联结索引程序要思考哪些因素？

在设计联结索引的时候，咱们最容易想到的准则是查问条件影响了联结索引中各个字段的程序，要依据查问条件来设计联结索引中各个字段的程序。

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

2. 案例剖析

松哥这里还是应用官网的案例吧，小伙伴们在公众号后盾回复 mysql 官网案例 能够获取到这个数据库脚本的下载地址。

在 MySQL 的官网案例中，有一个领取表 payment，如下图：

小伙伴们从图中能够看到，这个表中有一个 customer_id 和一个 staff_id，当初假如我想要依照这两个来进行搜寻，例如执行如下 SQL：

select * from payment where customer_id=1 and staff_id=2;

查问条件有两个，我想建设一个联结索引，那么到底是把 customer_id 放在后面还是把 staff_id 放在后面呢？

一个比拟罕用的法令是 看字段的选择性，选择性高的字段应该是放在后面。有的小伙伴可能还不分明什么是字段的选择性，能够参考松哥之前的文章：前缀索引，在性能和空间中寻找均衡。

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

select 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 去查问满足条件的记录，得一个一个找，还不如间接遍历主键索引。