之前的文章介绍了mysql的底层数据结构和mysql优化的神器讲解。有后台的朋友说，小强只是介绍概念，平时用起来还是一头雾水。他强烈要求小强出一篇实战sql优化的文章。90%的sql优化都会介绍。篇幅过长，分3篇。创建表`employees`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(24)NOTNULLDEFAULT''COMMENT'name',`age`int(20)NOTNULLDEFAULT'0'COMMENT'age',`position`varchar(20)NOTNULLDEFAULT''COMMENT'position',`hire_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'入职时间',PRIMARYKEY(`id`),KEY`idx_name_age_position`(`name`,`age`,`position`)USINGBTREE)ENGINE=InnoDB='员工表';insertintoemployees(name,age,position,hire_time)values('LiLei',22,'manager',NOW())insertintoemployees(name,age,position,hire_time)values('韩梅梅',23,'dev',NOW())insertintoemployees(name,age,position,hire_time)values('Lucy',23,'dev',NOW())全值匹配索引的字段类型为varchar(n):2bytes存储字符串长度，如果是utf-8，则长度为3n+2EXPLAINselect*fromemployeeswherename='LiLei';EXPLAINselect*fromemployeeswherename='LiLei'ANDage=22;EXPLAINselect*fromemployeeswherename='LiLei'ANDage=22ANDposition='manager';最左前缀规则如果索引是多列的，它最受最左前缀规则的约束。指的是从索引最左边的前列开始查询，不跳过索引中的列。下面三个SQL不会按照最左前缀规则进行索引。EXPLAIN选择*fromemployeeswhereage=22ANDposition='manager';EXPLAINselect*fromemployeeswhereposition='manager';EXPLAINselect*fromemployeeswhereage=17;索引失效不要对索引列做任何操作（计算、函数、类型转换），会导致索引失效，转为全表扫描。EXPLAINselect*fromemployeeswherename='李磊';EXPLAINselect*fromemployeeswhereleft(name,3)='李磊';hire_time增加一个普通索引：altertable`employees`ADDINDEX`idx_hire_time`(`hire_time`)USINGBTREE;EXPLAINselect*fromemployeeswheredate(hire_time)='2019-08-25';恢复原来的索引状态ALTERTABLE`employees`DROPINDEX`idx_hire_time`;存储引擎不能使用索引中范围条件右边的列--EXPLAINSELECT*FROMemployeesWHEREname='LiLei'ANDage=22ANDposition='manager';EXPLAINSELECT*FROMemployeesWHEREname='LiLei'ANDage>22ANDposition='manager';可以看到key_len的索引长度为78，也就是只使用了前两个字段name和age，postition没有使用索引。覆盖索引尽量使用覆盖索引（只访问索引的查询（索引列包含查询列））来减少selelct*语句。EXPLAINSELECTname,age,positionFROMemployeesWHEREname='LiLei'ANDage=22ANDposition='manager';条件决定mysql在不等于(!=或<>)时不能使用索引，会导致全表扫描EXPLAINSELECT*FROMemployeesWHEREname!='LiLei';null值判断为null，不为null不能使用索引EXPLAINSELECT*FROMemployeesWHEREnameisnull；likelike以通配符('$abc')开头mysql索引失败会变成全表扫描操作EXPLAINSELECT*FROMemployeesWHEREnameLIKE'%磊';字符串不加单引号索引无效EXPLAINSELECT*FROMemployeesWHEREname='1000';EXPLAINSELECT*FROMemployeesWHEREname=1000;不带单引号的字符串，mysql底层会使用cust函数将其转为字符串，此时索引失效。使用or&inless和使用ororinless。当你用它来查询的时候，mysql不一定使用索引。mysql内部的优化器会综合考虑索引比例、表大小等多个因素来评估是否整体使用索引。EXPLAINSELECT*FROMemployeesWHEREname='李磊'orname='韩梅梅';范围查询优化添加单值索引到年龄ALTERTABLE`employees`ADDINDEX`idx_age`(`age`)USINGBTREE;EXPLAINselect*fromemployeeswhereage>1andage<=2000;不采用索引的原因：mysql内部优化器会根据检索率、表大小等多个因素，整体评估是否采用索引。在此示例中，未使用索引。可能是因为单次数据量查询太大，所以优化器最终选择不使用索引。优化方法：一个大区间可以拆分成多个小区间。
