联合索引该如何选择合适的列？_西江热线

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

发布时间：2023-05-05 13:14:27 文章来源：江南一点雨

前面一篇文章，松哥和大家聊了MySQL中的索引合并，虽然MySQL提供了索引合并机制来提升SQL执行的效率，然而

前面一篇文章，松哥和大家聊了 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(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 去查询满足条件的记录，得一个一个找，还不如直接遍历主键索引。

标签：

资讯播报

乐活HOT

《熊出没·伴我“熊芯”》票房破
据各方数据，1月31日上午10时许，深圳出品的动画电影《熊出没·伴我熊芯》票房突破10亿元，在首日票房、档期票房、连续破亿天数等多方面打
今年春节深圳游客出境游订单量大
1月27日，携程发布的《2023年春节旅游总结报告》显示，今年春节，深圳游客的出境游订单量同比去年增长近5倍。相较国内热门景点的人山人海，
2023年春节黄金周深圳共接待游客
1月27日，记者从深圳市文化广电旅游体育局获悉，2023年春节黄金周期间(1月21日至27日)，深圳共接待游客469 25万人次，旅游收入31 58亿元，
深圳机场连续多日客流量超过10万
据深圳市春运办统计，1月15日和16日连续两天，深圳对外发送旅客人数都在48万左右，春运进入客流高峰期。1月15日，深圳春运对外旅客发送量达
深圳交响乐团将将举办两场音乐会
新年音乐会是观众喜闻乐见的年度重要文化品牌活动，多年来已成为深圳市民跨岁迎新的例牌项目。12月30日、31日晚，深圳交响乐团将在深圳音乐
深圳大力推进实施人才强市战略
作为来深科研人员中的一员，中山大学附属第八医院的助理研究员郭雅婕对深圳在人才服务方面的举措赞不绝口：我作为基础研究人员，很希望自己
2023年故宫年票正式开售有消费
12月1日晚8点，2023年故宫年票正式开售。相比于去年，今年购票顺畅了许多。据了解，2022年故宫年票发售时，因短时间内购买年票人数过多，曾
御寒类商品消费需求有所上升羽
昨日，冷空气到达，广州气温逐步下降。据商超方面预测，随着气温的逐渐下降，市民对于御寒类商品消费需求有所上升，不少广州商超准备了有关
深圳全市共排查窨井盖约350万个
小井盖、大民生。今年以来，深圳开展全市窨井盖专项治理工作成效显著。截至11月20日，全市共排查窨井盖约350万个，发现存在问题的窨井盖约3
汕汕铁路汕头站公布最新动态首
备受关注的汕汕(汕头至汕尾)铁路汕头站及站区工程近日传出最新动态：项目已顺利完成工程招标工作，由中铁建设集团有限公司中标承建。目前，

娱乐LOVE

精彩推送

友情链接:

网站简介网站律师版权声明广告刊登联系邮箱：51 46 76 11 3 @qq.com

Copyright © 1999-2020 www.xjrb.net All Rights Reserved

浙ICP备2022016517号-11