|
|
众所周知MySQL联合索引遵循最左前缀匹配原则，在少数情况下也会不遵循（有兴趣，可以翻一下上篇文章）。
创建联合索引的时候，建议优先把区分度高的字段放在第一列。
至于怎么统计区分度，可以按照下面这种方式。
创建一张测试表，用来测试：
CREATE TABLE `test` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`a` int NOT NULL,
`b` int NOT NULL,
`c` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='测试表';统计每个字段的区分度：
select
count(distinct a)/count(*),
count(distinct b)/count(*),
count(distinct c)/count(*)
from test;
值越大，区分度越高，优先放在第一列。
很多人不知道联合索引在B+树中是怎么存储的？我简单画一下。
比如在（a,b）字段上面创建联合索引，存储结构类似下面这样：
叶子节点存储全部数据，用顺序指针相连，数据都是先按a字段排序，a字段的值相等时再按b字段排序。
a字段的值是全局有序的，分别有1,1,1,2,2,2。
b字段的值是全局无序的，分别有1,3,5,1,3,5，只有在a字段的值相等时才呈现出局部有序。
所以在进行SQL查询的时候，如果where条件中没有a字段，只有b字段，是无法用到索引的，像下面这样：
select * from test where b=1;像有些文章上面说的，在（a,b）两个字段上创建联合索引，就会创建两个索引，分别是（a）和（a,b），这其实是一种不恰当的表述，虽然结果是对的。
下面做几道联合索引的经典面试题，试一下大家掌握的怎么样？
第一题：
下面这条SQL，该怎么创建联合索引？
SELECT * FROM test WHERE a = 1 and b = 1 and c = 1;你以为的答案是（a,b,c），其实答案是6个，abc三个的排列组合，（a,b,c）、（a,c,b）、（b,a,c）、（b,c,a）、（c,a,b）、（c,b,a）。
MySQL优化器为了适应索引，会调整条件的顺序。
再给面试官补充一句，区分度高的字段放在最前面，大大加分。
第二题：
下面这条SQL，该怎么创建联合索引？
SELECT * FROM test WHERE a = 1 and b > 1 and c = 1;考察的知识点是： 联合索引遇到范围匹配会停止，不会再匹配后面的索引字段。
所以答案应该是：（a,c,b）和 （c,a,b）。
当创建（a,c,b）和 （c,a,b）索引的时候，查询会用到3个字段的索引，效率更高。
怎么判断是用到了3个字段的索引，而不是只用到前两个字段的索引呢？
有个非常简单的方法，看执行计划的索引长度。
由于int类型的字段占4个字节，3个字段长度刚好是12个字节。
第三题：
下面这条SQL，该怎么创建联合索引？
SELECT * FROM test WHERE a in (1,2,3) and b > 1;答案是（a,b）。in条件查询会被转换成等值查询，可以验证一下：
可以看到用到了两个字段的索引。
所以我们在平时做开发，尽量想办法把范围查询转换成in条件查询，效率更高。
推荐阅读：《我爱背八股系列》
一灯架构：面试官竟然问我订单ID是怎么生成的？难道不是MySQL自增主键？
一灯架构：面试官竟然问我怎么分库分表？幸亏我总结了一套八股文
一灯架构：面试官竟然问我怎么实现分布式锁？幸亏我总结了全套八股文
一灯架构：面试官竟然问我消息队列为啥会丢失消息？幸亏我总结了全套八股文
一灯架构：面试官问我MySQL索引为啥用B+树？我让他去问作者
一灯架构：记一次ThreadLocal引发的线上故障，年终奖没了，可能还面临辞退
一灯架构：一篇文章讲清楚MySQL的聚簇/联合/覆盖索引、回表、索引下推
一灯架构：面试官竟然问我MySQL事务的底层原理？幸亏我总结了全套八股文
一灯架构：MySQL的锁这么多，不知从何学起，看完这篇文章就够了
一灯架构：面试官问我一条update语句加了多少锁？我总结了全套八股文
一灯架构：记一次排查线上MySQL死锁过程，不能只会crud，还要知道加锁原理
一灯架构：彻底搞懂三大MySQL日志，Redo Log、Undo Log、Bin Log
一灯架构：高级程序员必知必会，一文详解MySQL主从同步原理
一灯架构：查询效率提升10倍！3种优化方案，帮你解决MySQL深分页问题
一灯架构：别再问我MySQL为啥没走索引？就这几种原因，全都告诉你
一灯架构：【干货】MySQL底层架构设计，你了解多少？
一灯架构：学会使用MySQL的Explain执行计划，SQL性能调优从此不再困难
一灯架构：吐血总结十三条经验，帮你创建更合适的MySQL索引
一灯架构：精心整理16条MySQL使用规范，减少80%问题，推荐分享给团队
一灯架构：手把手教你定位线上MySQL慢查询问题，包教包会
一灯架构：我说MySQL联合索引遵循最左前缀匹配原则，面试官让我回去等通知
一灯架构：MySQL查询性能优化七种武器之索引潜水
一灯架构：手把手教你定位线上MySQL锁超时问题，包教包会
一灯架构：不知道怎么分析MySQL查询瓶颈，这款自带工具太香了，强烈推荐
一灯架构：MySQL查询性能优化七种武器之链路追踪 |
|
发表于 2022-9-22 02:49:43