【进阶】索引优化原则

亦凉 2022-03-06 01:04 220阅读 0赞

# 前言 #

索引优化有很作最佳实践原则，下面对常用原则进行分析。

MySQL 索引底层数据结构和算法：[https://blog.csdn.net/yhl\_jxy/article/details/88392411][https_blog.csdn.net_yhl_jxy_article_details_88392411]

MySQL explan 执行计划详解：[https://blog.csdn.net/yhl\_jxy/article/details/88570154][https_blog.csdn.net_yhl_jxy_article_details_88570154]

优化原则实例 SQL 准备。

CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_name` varchar(24) NOT NULL DEFAULT '' COMMENT '用户姓名',
      `user_age` int(11) NOT NULL DEFAULT 0 COMMENT '用户年龄',
      `user_level` varchar(20) NOT NULL DEFAULT '' COMMENT '用户等级',
      `register_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
      PRIMARY KEY (`id`),
      KEY `idx_userName_userAge_userLevel` (`user_name`,`user_age`,`user_level`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';
    
    INSERT INTO user(user_name, user_age, user_level, register_time)
    VALUES('ZhangSan', 30, 'A', NOW()), ('LiSi', 31, 'B', NOW()), ('WangWu', 31, 'C', NOW());

**1、全值匹配**

按索引字段顺序匹配使用。

mysql> explain select * from user where user_name = 'ZhangSan';

![20190318125428548.png][]

通过 explain 分析，type 为 ref，使用索引，效率高。key\_len 为74，根据 key\_len 计算规则，

如果字段类型为 varchar(n)，并且是 utf-8 编码格式，则 key\_len = 3n+2，where 后使用了

user\_name，则 key\_len=3\*24+2=74，证明索引用到了联合索引的第一个字段 user\_name，

从 ref 也可以看到一个 const。

使用联合索引两个字段时：

mysql> explain select * from user where user_name = 'ZhangSan' and user_age = 30;

![20190318130102593.png][]

使用联合索引三个字段时：

mysql> explain select * from user where user_name = 'ZhangSan'and user_age = 30 and user_level = 'A';

![20190318130205387.png][]

**2、最佳左前缀法则**

如果建的是联合索引，要遵循最左前缀法则。

要想使用索引，where 后面的条件需要从索引的最左前列开始并且不跳过索引中的列使用。

mysql> explain select * from user where user_name = 'ZhangSan'and user_age = 30 and user_level = 'A';

![20190318165727695.png][]

按照索引字段顺序使用，三个字段都使用了索引。

mysql> explain select * from user where user_age = 30 and user_level = 'A';

![20190318170019290.png][]

直接跳过 user\_name 使用索引字段，索引无效，未使用到索引。

mysql> explain select * from user where user_age = 30 and user_level = 'A' and user_name = 'ZhangSan';

![20190318170401703.png][]

where 后面查询条件顺序是 user\_age、user\_level、user\_name 与我们建的索引顺序

user\_name、user\_age、user\_level 不一致，为什么还是使用了索引，这是因为 MySQL 底层优化

器给咱们做了优化。但是，咱们最好还是按顺序使用索引。

**3、不要在索引列上做任何操作，比如计算、使用函数、自动或手动进行类型转换，**

**会导致索引失效，从而使查询转向全表扫描。**

mysql> explain select * from user where user_name = 'ZhangSan';

![20190318182402399.png][]

where 条件直接使用索引字段 user\_name 用到了索引。

mysql> explain select * from user where right(user_name, 3) = 'San';

![20190318182648299.png][]

where 条件使用计算后的索引字段 user\_name，没有使用索引，索引失效。

**4、存储引擎不能使用范围条件右边的索引列。**

mysql> explain select * from user where user_name = 'ZhangSan' and user_age = 30 and user_level = 'A';

![20190318183811113.png][]

三个列都使用“=”号，顺序使用三个字段，三个字段都使用了索引。

mysql> explain select * from user where user_name = 'ZhangSan' and user_age > 30 and user_level = 'A';

![20190318184611549.png][]

将 user\_age 修改为 ">" 之后，Extra 为 Using index condition，表明索引没有被完全使用，

并且 key\_len 由 140 降为 78，说明最后一个字段 user\_level 没有使用索引。

即范围之后索引全无效。

但是如果我们把 “>” 变为 ">=" 呢？

mysql> explain select * from user where user_name = 'ZhangSan' and user_age >= 30 and user_level = 'A';

![20190318185221992.png][]

加上等号后，key\_len 变为了 140，但是 Using index condition确又表明索引没有被完全使用，

只能说明在满足 user\_age 为“=”号条件时全部索引使用，否则，范围之后的索引失效。

**5、尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select \* 语句。**

mysql> explain select * from user where user_name = 'ZhangSan' and user_age  = 30 and user_level = 'A';

![20190318185846386.png][]

Extra 显示 null，表示查询的列未被索引列覆盖，并且 where 筛选条件是索引的前导列，说明用到

了索引，但是部分字段未被索引列覆盖，必须通过“回表”来实现，所以不是纯粹地用到了索引，也

不是完全没用到索引。

mysql> explain select user_name, user_age from user where user_name = 'ZhangSan' and user_age  = 30 and user_level = 'A';

![20190318185943328.png][]

将 \* 换成索引列，查询时使用了索引，用索引列覆盖查询的 \*， 叫做覆盖索引。

**6、MySQL 在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。**

mysql> explain select user_name, user_age from user where user_name = 'ZhangSan';

![20190318190304134.png][]

mysql> explain select user_name, user_age from user where  user_name != 'ZhangSan';

![2019031819045680.png][]

Extra 显示 Using whre Using index，表示查询的列被索引列覆盖，但是 where 后面条件未使用索

引，说明无法直接通过索引查找查询到符合条件的数据。

**7、is null，is not null 也无法使用索引。**

mysql> explain select * from user where user_name = 'ZhangSan';

![20190318190743234.png][]

使用了索引。

mysql> explain select * from user where user_name is not null;

![20190318190901190.png][]

未使用索引。

**8、like 以通配符开头（like '%aaa'）MySQL 索引失效会变成全表扫描操作。**

mysql> explain select * from user where user_name like '%San';

![20190318191107334.png][]

% 开头，未使用索引。

mysql> explain select * from user where user_name like 'Zhang%';

![20190318191206461.png][]

% 结尾，使用了索引。

mysql> explain select * from user where user_name like '%Zhang%';

![20190318191304177.png][]

%aaa% 前后都使用 % 号，也未使用索引。

like 使用总结：aaa% 可以使用索引，但是 %aaa 或 %aaa% 相当于范围查询，

无法使用索引，以及在之后的索引列也会索引失效。

**总结**

关于索引优化原则，可以做如下总结。

全值匹配心上人（这是基本原则），最左前缀要遵行（联合索引一般都围绕最左前缀优化）；

带头大哥活才行（联合索引从最左边字段开始使用），中间兄弟规矩行（不能跳过中间的字段，跳过后索引无效）；

索引列上少计算（索引列上尽量不要进行计算），范围之后全完蛋（where后面使用范围查询的之后的索引无效）；

like百分最右写（%号写最右边，写左边会导致索引失效），覆盖索引别写星（尽量避免select\*这样的语句，能写索引列最好）；

空值不等还有or，索引失效最无情（is null，is not null，!=，<>，or会导致索引无效）；

关于索引优化原则，不同的sql版本会有不同，并且需要结合explain的各项参数分析，需要不断体会，

explain博大精深。

[https_blog.csdn.net_yhl_jxy_article_details_88392411]: https://blog.csdn.net/yhl_jxy/article/details/88392411
[https_blog.csdn.net_yhl_jxy_article_details_88570154]: https://blog.csdn.net/yhl_jxy/article/details/88570154
[20190318125428548.png]: /images/20220303/4be993c930d543609d7d16ed393d05d5.png
[20190318130102593.png]: /images/20220303/aec387ef97c440d584b7cf7ec60ddfbb.png
[20190318130205387.png]: /images/20220303/21e3764b603249c59cb02981a864e5af.png
[20190318165727695.png]: /images/20220303/fc816ec8b1b34460a157038bd458729d.png
[20190318170019290.png]: /images/20220303/16aa0879af0e4eafb4e7d7f30aee8762.png
[20190318170401703.png]: /images/20220303/c9054ea4d40d4252987e8baf4d72c9a5.png
[20190318182402399.png]: /images/20220303/80d905249a0242179273648c6cb090a6.png
[20190318182648299.png]: /images/20220303/140e0429a8804b7cb75c22b4699f7a4b.png
[20190318183811113.png]: /images/20220303/241314ed87f848c68c0cbd6db59b2fd2.png
[20190318184611549.png]: /images/20220303/3a6a9908ab3c42418cf236394a01b765.png
[20190318185221992.png]: /images/20220303/ba18219b461947ea8291a0c9305ef151.png
[20190318185846386.png]: /images/20220303/94ab7d2592ed41218cd20d72d59567b4.png
[20190318185943328.png]: /images/20220303/e5108a20ed6a47c490066579ed3d612e.png
[20190318190304134.png]: /images/20220303/67d2e03a901a4247abc9ae7d12b40737.png
[2019031819045680.png]: /images/20220303/e90afea04ef7443ba0d8002af0126070.png
[20190318190743234.png]: /images/20220303/dd3ede4cf7144fd4b9bb4c9e1fe64c3c.png
[20190318190901190.png]: /images/20220303/97aa3c68f1a349a98c35621882a751c7.png
[20190318191107334.png]: /images/20220303/c569ca000e604dd78bbe9397a36af021.png
[20190318191206461.png]: /images/20220303/d119908a21d14757a1ec17023356c00a.png
[20190318191304177.png]: /images/20220303/3a85ba302f4f44418c44b95c4bc0a11d.png