本文转载自微信公众号《运维开发故事》，作者老郑。转载本文请联系运维开发故事公众号。本文以employees表为例，结合具体的索引应用实践案例，通过分析EXPLAIN关键字获取执行计划来验证我们的索引实践。详细的执行计划可以参考mysql官网的explain介绍。mysql版本：5.7.23表使用CREATETABLEemployees(idint(11)NOTNULLAUTO_INCREMENT,namevarchar(24)NOTNULLDEFAULT''COMMENT'name',ageint(11)NOTNULLDEFAULT'0'COMMENT'age',positionvarchar(20)NOTNULLDEFAULT''COMMENT'Position',hire_timetimestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'入职时间',PRIMARYKEY(id),KEYidx_name_age_positionUSINGBTREE(name,age,position))ENGINE=InnoDBAUTO_INCREMENT=4CHARSET=utf8COMMENT'员工记录表';INSERTINTOemployees(name,age_retime,position)VA'李磊',22,'经理',现在());INSERTINTOemployees(姓名、年龄、职位、雇用时间)VALUES('WaKen',23,'dev',NOW());INSERTINTOemployees（姓名、年龄、职位、雇用时间）VALUES（'Lucy',23,'dev',NOW());复合索引数据结构下面是复合索引示意图索引最佳实践1.全值匹配全值匹配，查询条件可以命中索引的所有列，或者复合索引需要命中左边的数据EXPLAINSELECT*FROMemployeesWHEREname='李磊';EXPLAINSELECT*FROMemployeesWHEREname='李磊'ANDage=22;EXPLAINSELECT*FROMemployeesWHEREname='李磊'ANDage=22ANDposition='经理';2.BestLeftprefixrule如果索引了多个列，则必须遵循最左边的前缀规则。指的是从索引最左边的前列开始查询，不跳过索引中的列。EXPLAINSELECT*FROMemployeesWHEREage=22ANDposition='manager';EXPLAINSELECT*FROMemployeesWHEREposition='manager';EXPLAINSELECT*FROMemployeesWHEREname='李磊';3、不对索引列做任何操作（计算、函数、（自动或手动）类型转换），会使索引失效，转为全表扫描。使用全值匹配可以正常使用索引EXPLAINSELECT*FROMemployeesWHEREname='LiLei';使用left或其他数据库函数会导致索引未命中。将执行全表扫描。EXPLAINSELECT*FROMemployeesWHEREleft(name,3)='李磊';4、存储引擎不能使用索引中范围条件右侧列的全值来匹配如下。EXPLAINSELECT*FROMemployeesWHEREname='LiLei'ANDage=22ANDposition='manager';如果我们在中间加上范围搜索，右边的查询条件就不能使用索引了。下面对比一下上下两个sql的区别。EXPLAINSELECT*FROMemployeesWHEREname='LiLei'ANDage>22ANDposition='manager';5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select指定字段查询的select*语句，如果查询的所有列都是索引上的数据，所以可以减少“返桌”。查询效率高于select*explainselectname,agefromemployeeswherename='Lilei'andage=23andposition='manage';EXPLAINSELECT*FROMemployeesWHEREname='LiLei'ANDage=23ANDposition='manager';6.Mysql不等于(!=or<>)会导致全表扫描EXPLAINSELECT*FROMemployeesWHEREname!='LiLei';7.isnull,isnotnull索引不能使用，因为在mysql中是变长数据类型，比如varchar。null不存储，它需要一个额外的标志位来存储它。所以如果我们使用isnull，isnotnull不能使用索引。所以我们尽量让每个字段在DDL语句中都有一个默认值。EXPLAINSELECT*FROMemployeesWHEREnameisnull；8.like以通配符('$abc...')开头mysql索引失败会变成全表扫描操作问题：解决like'The%string%'indexisnotused？a)使用覆盖索引时，查询字段必须是覆盖索引字段EXPLAINSELECTname,age,positionFROMemployeesWHEREnamelike'%Lei%';b)当覆盖索引指向的字段为varchar(380)字段大于380时，覆盖索引将失效！9.如果查询条件导致类型转换，索引将失效。如果字符串没有加单引号，则索引无效。使用单引号，不需要进行数据转换。EXPLAINSELECT*FROMemployeesWHEREname='1000';如果不加单引号，数据类型转换会导致查询索引失效。EXPLAINSELECT*FROMemployeesWHEREname=1000;10.少用or，用它连接时索引很多时候会失败像KK%这样的小总结等价于=常数，%KK和%KK%等价范围以下是索引查询失败的常见类型或者可用的决策总结1总结2参考文档https://blog.csdn.net/qq_38138069/article/details/82998658https://www.processon.com/u/5e26625de4b00fbcc45e576dhttps://zhuanlan.zhihu.com/p/94190700https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
