备战BAT｜不会优化索引查询怎么办？学会这个法则无敌了！

关注公众号“AI码师”领取2021最新面试资料一份

程序员日常

程序员日常与DBA打交道应该会很多，因为他会时不时的给你抛个慢sql，让你去优化。

可是对于刚处在新手村的你来说，这无疑是一个大难题，因为自己根本不知道如何去优化索引，这可怎么办呢？

不过不用怕，今天你如果学会了我的这篇法则，任何sql优化都难不倒你，我们进入正题吧！

首先，要搞清楚我们为什么要用索引

相信大家在面试过程中，肯定会被面试官问到过这个问题。

索引是一个排好顺序的数据结构，由于它的顺序特性，所以我们在海量数据中查询一条数据，将会使效率变得更高。但是它付出的代价是索引要占用一部分空间，mysql采用的是以空间换时间的策略。

那么，这个法则到底是什么呢？

既然是干货，我就不卖关子了，这个法则就是最经典的：最左匹配原则

介绍下这个原则：

这个原则，主要是针对联合索引建立的，文章中的优化也主要是针对这种索引。

假设我们建立了一个联合索引 ABC,那么我们where查询的时候，也必须要严格按照这个顺序排列字段，如：select a,b,c from t where a=1 and b=2

既然大家已经了解了最左匹原则，那么我接下来会和大家一起分析下，哪些sql会导致联合索引查询失效及如何判断联合索引的字段有没有都生效

如何利用最左匹配法则，助你拿到高薪

CREATE TABLE `test_left_index` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_bin NOT NULL,
  `age` int(11) NOT NULL,
  `address` varchar(50) COLLATE utf8_bin NOT NULL,
  `email` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_age_addr` (`name`,`age`,`address`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `test_index`.`test_left_index`(`id`, `name`, `age`, `address`) VALUES (1, 'yang', 10, '安徽省合肥市');
INSERT INTO `test_index`.`test_left_index`(`id`, `name`, `age`, `address`) VALUES (2, 'zhang', 10, '安徽省安庆市');

explain select name from test_left_index where NAME ='111' and age =10

• 是否走索引 是
• 哪些字段用到索引 name和age
• 索引长度 156
• 原因分析 根据执行计划，我们可以看出sql满足最左前缀匹配原则，走了索引； 索引长度计算= name长度 * 3 + 2 + age长度 = 156，根据长度是可以判断sql走了name和age的联合索引

explain select name from test_left_index where NAME ='111' and age > 10

• 是否走索引 是
• 哪些字段用到索引 name和age
• 索引长度 156
• 原因分析 按照最左前缀匹配原则，我们可以看出sql首先使用name常量过滤，过滤后的数据中，age是排好顺序的，所以进行范围查询的时候，可以直接按照从到右，依次和索引比较，所以会走索引。 索引长度计算= name长度 * 3 + 2 + age长度 = 156，根据长度是可以判断出sql走了name和age的索引

explain select name from test_left_index where NAME ='111' and age > 10 and address ='安徽省合肥市'

• 是否走索引 是
• 哪些字段用到索引 name和age
• 索引长度 156
• 原因分析 按照最左前缀匹配原则，我们可以看出sql首先使用name常量过滤，滤后的数据中，age是排好顺序的，所以进行范围查询的时候，可以直接按照从做到右，依次和索引比较，所以会走索引；由于根据age字段过滤后，adrees字段是无序的，因为age会匹配到多个，导致age对应的address是无序的，我们可以看图：

根据分析得到：第二部分是根据name和age过滤后的结果，是无序的，无法进行索引排序，mysql会选择进行全表扫描，导致address索引字段失效

索引长度计算= name长度 * 3 + 2 + age长度 = 156，根据长度是可以判断出sql走了name和age的索引

explain select name from test_left_index where NAME like 'y%' and age = 10

• 是否走索引 是
• 哪些字段用到索引 name和age
• 索引长度 156
• 原因分析 笔者之前理解的like后匹配查询和范围查询是一样的，本身字段会走索引，但是like后面的字段是不会走索引的，因为按照最左匹配原则是可以得出这样结论的。 但是我们还是得按照实际的mysql优化结果来看，具体原因，我们可以通过trace工具来分析:

 set session optimizer_trace="enabled=on",end_markers_in_json=on;
select name from test_left_index  where NAME like 'y%' and age = 10;
select * from information_schema.OPTIMIZER_TRACE;

根据mysql最终优化的结果，发现此语句全部走索引，效率更高，有可能是由于数据量比较少的原因(猜测)，最终是否选择走索引按照mysql最终优化结果来决定。

索引长度计算= name长度 * 3 + 2 + age长度 = 156，根据长度是可以判断出sql走了name和age的索引

in 查询 和like 情况类似，后面的字段也会走索引，估计也是和数据量有关系，如果in 过滤后的数据特别多，mysql会觉得还不如直接全表扫描来的快。

explain select name from test_left_index where NAME ='yang' and address='1111';

• 是否走索引 是
• 哪些字段用到索引 name
• 索引长度 152
• 原因分析

由于我们创建联合索引的顺序是name->age->address,可以看出此条sql跳过了age，所以经过name过滤后的数据，age是按顺序排的，但是address是乱序的，通过图是很容易看出的:

在这里插入图片描述

导致address无法按照索引顺序查找，所以该字段不会走索引，这个其实和范围查询类似，范围查询会导致范围查询后面的字段无法使用索引。

最后再看一个

explain select name from test_left_index where NAME ='yang' and address='1111' and age= 10

• 是否走索引 是
• 哪些字段用到索引 name，age,address
• 索引长度 308 计算方式: 3 * name长度+2+3 * address长度+2+4（age长度）=308
• 原因分析

大家是不是很诧异，为什么多加一个字段，又会走索引呢？大家不必惊慌，因为咱们创建索引刚好使用了where语句后面的三个字段，mysql看到这种情况，发现顺序不对，会自动给我们优化，使索引生效。

结论

所以我们最终能够得到结论是：写sql时，尽量按照最左匹配原则，有效用上索引；另外判断是否使用索引可以结合 explain执行计划结果中的type,key,key_len字段进行判断，key_len主要用在联合索引上，判断联合索引中，有哪些字段用上了索引。最后再贴上索引长度计算公式：

• 字符串 • char(n) n字节长度 • varchar(n) 2字节存储字符串长度，如果是utf-8,则长度为3n+2
• 数值类型 • tinyint 1字节 • smallint 2字节 • int 4字节 • bigint 8字节
• 时间类型 • date 3字节 • timestamp 4字节 • datetime 8字节

如果字段允许为空 则对应字段需要另外加一字节的长度