文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

联合索引该如何选择合适的列?

2024-11-30 15:12

关注

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

来源:江南一点雨内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-后端开发
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯