绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
联合索引该如何选择合适的列?
2023-05-05 17:34:26

前面一篇文章和大家聊了 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:

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

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

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

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

select count(distinct customer_id)/count(1as c,count(distinct staff_id)/count(1as 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 去查询满足条件的记录,得一个一个找,还不如直接遍历主键索引。

分享好友

分享这个小栈给你的朋友们,一起进步吧。

MySQL干货资料
创建时间:2020-05-06 14:18:32
每天都有干货输出哦
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

技术专家

查看更多
  • 飘絮絮絮丶
    专家
戳我,来吐槽~