评论

收藏

[MySQL] MySQL 索引创建和优化实践

数据库 数据库 发布于:2021-11-09 23:05 | 阅读数:711 | 评论:0

本文以 employees 表为例子,结合具体的索引运用实践案例,通过分析 EXPLAIN 关键字获取执行计划,来验证我们这些索引实践。如果是执行计划相关的详细信息,大家可以参考 mysql 官网 explain 介绍。
DSC0000.jpg

本文以 employees 表为例子,结合具体的索引运用实践案例,通过分析 EXPLAIN 关键字获取执行计划,来验证我们这些索引实践。如果是执行计划相关的详细信息,大家可以参考 mysql 官网 explain 介绍。
mysql 版本: 5.7.23
使用的表
CREATETABLEemployees(
idint(11)NOTNULLAUTO_INCREMENT,
namevarchar(24)NOTNULLDEFAULT''COMMENT'姓名',
ageint(11)NOTNULLDEFAULT'0'COMMENT'年龄',
positionvarchar(20)NOTNULLDEFAULT''COMMENT'职位',
hire_timetimestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'入职时间',
PRIMARYKEY(id),
KEYidx_name_age_positionUSINGBTREE(name,age,position)
)ENGINE=InnoDBAUTO_INCREMENT=4CHARSET=utf8COMMENT'员工记录表';
 
INSERTINTOemployees(name,age,position,hire_time)VALUES('LiLei',22,'manager',NOW());
 
INSERTINTOemployees(name,age,position,hire_time)VALUES('WaKen',23,'dev',NOW());
 
INSERTINTOemployees(name,age,position,hire_time)VALUES('Lucy',23,'dev',NOW());
复合索引数据结构
下面是一个复合索引的是示意图
DSC0001.jpg

索引最佳实践
1. 全值匹配
对于全值匹配,就是查询条件能够命中索引的全部列,或者复合索引需要命中左边的数据
EXPLAINSELECT*FROMemployeesWHEREname='LiLei';
DSC0002.jpg
EXPLAINSELECT*FROMemployeesWHEREname='LiLei'ANDage=22;
DSC0003.jpg
EXPLAINSELECT*FROMemployeesWHEREname='LiLei'ANDage=22ANDposition='manager';
DSC0004.jpg

2.最佳左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
EXPLAINSELECT*FROMemployeesWHEREage=22ANDposition='manager';
DSC0005.jpg
EXPLAINSELECT*FROMemployeesWHEREposition='manager';
DSC0006.jpg
EXPLAINSELECT*FROMemployeesWHEREname='LiLei';
DSC0007.jpg

3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
使用全值匹配能够正常使用索引
EXPLAINSELECT*FROMemployeesWHEREname='LiLei';
DSC0008.jpg

使用 left 或者其他的数据库函数导致索引未命中。会走一个全表扫描。
EXPLAINSELECT*FROMemployeesWHEREleft(name,3)='LiLei';
DSC0009.jpg

4.存储引擎不能使用索引中范围条件右边的列
全值匹配如下。
EXPLAINSELECT*FROMemployeesWHEREname='LiLei'ANDage=22ANDposition='manager';
DSC00010.jpg

如果我们中间增加一个范围查找,会导致右边的查询条件无法使用索引。我们对比一下,上下两条 sql 的差别。
EXPLAINSELECT*FROMemployeesWHEREname='LiLei'ANDage>22ANDposition='manager';
DSC00011.jpg

5.尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少select *语句
对于 select 指定字段查询,如果查询的所有列都是索引上的数据,那么可以减少 “回表”。查询的效率高于 select *
explainselectname,agefromemployeeswherename='Lilei'andage=23andposition='manage';
DSC00012.jpg
EXPLAINSELECT*FROMemployeesWHEREname='LiLei'ANDage=23ANDposition='manager';
DSC00013.jpg

6.mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
EXPLAINSELECT*FROMemployeesWHEREname!='LiLei';
DSC00014.jpg

7.is null,is not null 也无法使用索引
因为在 mysql 变长的数据类型中,如 varchar 。null 不会存储,它需要一个额外的标志位来存储。所以如果我们使用 is null , is not null 是无法使用索引的。所以我们在 DDL 语句中尽量让每个字段都有默认值。
EXPLAINSELECT*FROMemployeesWHEREnameisnull;
DSC00015.jpg

8.like以通配符开头('$abc...')mysql索引失效会变成全表扫描操作
EXPLAINSELECT*FROMemployeesWHEREnamelike'%Lei';
DSC00016.jpg
EXPLAINSELECT*FROMemployeesWHEREnamelike'Lei%';
DSC00017.jpg

问题:解决like'%字符串%'索引不被使用的方法?a)使用覆盖索引,查询字段必须是建立覆盖索引字段
EXPLAINSELECTname,age,positionFROMemployeesWHEREnamelike'%Lei%';
b)当覆盖索引指向的字段是varchar(380)及380以上的字段时,覆盖索引会失效!
9. 如果查询条件导致类型转换会导致索引失效
字符串不加单引号索引失效
加上单引号,无需数据转换 name 存储数据也是 varchar 类型。
EXPLAINSELECT*FROMemployeesWHEREname='1000';
DSC00018.jpg

如果不加单引号,发生数据类型转换导致本次查询索引失效。
EXPLAINSELECT*FROMemployeesWHEREname=1000;
DSC00019.jpg

10.少用or,用它连接时很多情况下索引会失效
EXPLAINSELECT*FROMemployeesWHEREname='LiLei'orname='HanMeimei';
DSC00020.jpg

小总结
like KK%相当于=常量,%KK和%KK% 相当于范围 下面是常见的几种索引查询失效或者,可用的判定
总结 1
DSC00021.jpg

总结 2
DSC00022.jpg

参考文档
https://blog.csdn.net/qq_38138069/article/details/82998658
https://www.processon.com/u/5e26625de4b00fbcc45e576d
https://zhuanlan.zhihu.com/p/94190700
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
原文地址:https://mp.weixin.qq.com/s/VGzbOE0kSrR3CT6nJeCVng

关注下面的标签,发现更多相似文章