面试官:谈谈你对mysql联合索引的认识?-阿里云开发者社区

开发者社区> 数据库> 正文

面试官:谈谈你对mysql联合索引的认识?

简介:

面试官:谈谈你对mysql联合索引的认识?
引言
本文预计分为两个部分:
(1)联合索引部分的基础知识
在这个部分,我们温习一下联合索引的基础
(2)联合索引部分的实战题
在这个部分,列举几个我认为算是实战中的代表题,挑出来说说。

正文
基础
讲联合索引,一定要扯最左匹配!放心,我不扯有的没的,几句话懂个大概就行!
最左匹配
所谓最左原则指的就是如果你的 SQL 语句中用到了联合索引中的最左边的索引,那么这条 SQL 语句就可以利用这个联合索引去进行匹配,值得注意的是,当遇到范围查询(>、<、between、like)就会停止匹配。
假设,我们对(a,b)字段建立一个索引,也就是说,你where后条件为

a = 1
a = 1 and b = 2
是可以匹配索引的。但是要注意的是~你执行

b= 2 and a =1
也是能匹配到索引的,因为Mysql有优化器会自动调整a,b的顺序与索引顺序一致。
相反的,你执行

b = 2
就匹配不到索引了。
而你对(a,b,c,d)建立索引,where后条件为

a = 1 and b = 2 and c > 3 and d = 4
那么,a,b,c三个字段能用到索引,而d就匹配不到。因为遇到了范围查询!

最左匹配的原理?
假设,我们对(a,b)字段建立索引,那么入下图所示

如图所示他们是按照a来进行排序,在a相等的情况下,才按b来排序。

因此,我们可以看到a是有序的1,1,2,2,3,3。而b是一种全局无序,局部相对有序状态!
什么意思呢?
从全局来看,b的值为1,2,1,4,1,2,是无序的,因此直接执行b = 2这种查询条件没有办法利用索引。

从局部来看,当a的值确定的时候,b是有序的。例如a = 1时,b值为1,2是有序的状态。当a=2时候,b的值为1,4也是有序状态。
因此,你执行a = 1 and b = 2是a,b字段能用到索引的。而你执行a > 1 and b = 2时,a字段能用到索引,b字段用不到索引。因为a的值此时是一个范围,不是固定的,在这个范围内b值不是有序的,因此b字段用不上索引。

综上所示,最左匹配原则,在遇到范围查询的时候,就会停止匹配。

实战
OK,懂上面的基础,我们就可以开始扯了~我举了经典的五大题型,看完基本就懂!

题型一
如果sql为

SELECT * FROM table WHERE a = 1 and b = 2 and c = 3;
如何建立索引?
如果此题回答为对(a,b,c)建立索引,那都可以回去等通知了。
此题正确答法是,(a,b,c)或者(c,b,a)或者(b,a,c)都可以,重点要的是将区分度高的字段放在前面,区分度低的字段放后面。像性别、状态这种字段区分度就很低,我们一般放后面。

例如假设区分度由大到小为b,a,c。那么我们就对(b,a,c)建立索引。在执行sql的时候,优化器会 帮我们调整where后a,b,c的顺序,让我们用上索引。

题型二
如果sql为

SELECT * FROM table WHERE a > 1 and b = 2;
如何建立索引?
如果此题回答为对(a,b)建立索引,那都可以回去等通知了。
此题正确答法是,对(b,a)建立索引。如果你建立的是(a,b)索引,那么只有a字段能用得上索引,毕竟最左匹配原则遇到范围查询就停止匹配。
如果对(b,a)建立索引那么两个字段都能用上,优化器会帮我们调整where后a,b的顺序,让我们用上索引。

题型三
如果sql为

SELECT * FROM table WHERE a > 1 and b = 2 and c > 3;
如何建立索引?
此题回答也是不一定,(b,a)或者(b,c)都可以,要结合具体情况具体分析。

拓展一下

SELECT * FROM table WHERE a = 1 and b = 2 and c > 3;
怎么建索引?嗯,大家一定都懂了!

题型四
SELECT * FROM table WHERE a = 1 ORDER BY b;
如何建立索引?
这还需要想?一看就是对(a,b)建索引,当a = 1的时候,b相对有序,可以避免再次排序!
那么

SELECT * FROM table WHERE a > 1 ORDER BY b;
如何建立索引?
对(a)建立索引,因为a的值是一个范围,这个范围内b值是无序的,没有必要对(a,b)建立索引。

拓展一下

SELECT * FROM table WHERE a = 1 AND b = 2 AND c > 3 ORDER BY c;
怎么建索引?

题型五
SELECT * FROM table WHERE a IN (1,2,3) and b > 1;
如何建立索引?
还是对(a,b)建立索引,因为IN在这里可以视为等值引用,不会中止索引匹配,所以还是(a,b)!

拓展一下

SELECT * FROM table WHERE a = 1 AND b IN (1,2,3) AND c > 3 ORDER BY c;
如何建立索引?此时c排序是用不到索引的。

总结
希望大家在面试的时候遇到联合索引的题目,能够答出来吧

作者:孤独烟 出处: http://rjzheng.cnblogs.com/
原文地址https://www.cnblogs.com/rjzheng/p/12557314.html

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章