索引优化的目的是让索引不失效，使用正确的索引。今天主要分享最近整理的八个索引规则的第一部分。看完了，面试索引应该没问题~下面主要是做个实验帮助大家理解~1.最好的左前缀规则1.定义在创建多列索引的情况下，从最左边开始查询索引的前列，不能跳过索引中的列。最佳左前缀规则是指如果创建了多个索引，则按照索引创建的顺序使用，不能遗漏或跳过。当然，如果只使用最左边的索引列，即第一个索引就OK。2.环境准备DROPTABLEIFEXISTS`tb_emp`;CREATETABLE`tb_emp`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(20)NOTNULL,`age`int(11)NOTNULL,gendervarchar(10)NOTNULL,emailvarchar(20),PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;INSERTINTO`tb_emp`(name,age,gender,email)VALUES('Tom','22','male','1@qq.com');INSERTINTO`tb_emp`(姓名,年龄,性别,邮箱)VALUES('Mary','21','女','2@qq.com');INSERTINTO`tb_emp`(姓名,年龄,性别,邮箱)VALUES('杰克','27','男','3@qq.com');INSERTINTO`tb_emp`(name,age,gender,email)VALUES('Rose','23','female','4@qq.com');3.创建组合索引createindexidx_allontb_emp(name,age,gender);showindexfromtb_emp;在这里，机车代表名字，车厢代表年龄，尾巴代表性别。4、仅机车说明：索引创建顺序为姓名、年龄、性别；直接用name（机车）作为条件，可以看到type=ref，key_len=82，ref=const，效果还不错。5.只有车厢描述：没有机车（名称），直接使用车厢，导致全表扫描（type=ALL）6.机车加车厢，机车加尾描述：机车加车厢，机车加尾，虽然两者都是type=ref，但是观察key_len和ref，仅在机车中比较结果，可以得出结论，在使用机车（名字）和尾巴（性别）时，只使用了部分索引，即机车（名称）索引。通俗理解：机车可以单独运行，也可以机车和直接相连的车厢一起运行，但如果机车和后方之间没有车厢，机车就只能自己运行。7、机车加车厢加尾说明：机车加车厢加尾，三者串联，即为行驶中的列车。类型=ref，key_len=128，ref=常量，常量，常量。2.不计算索引列。对索引列做任何操作（计算、函数、（自动或手动）类型转换）都会导致索引失效，转为全表扫描。1、FunctionCompute说明：这里使用了FunctionCompute，type=ALL，导致索引失败。2、隐式类型转换说明：这里'123'是一个字符串，123是一个数字。发生了隐式类型转换，导致全表扫描（type=ALL）3.范围右侧的所有索引列都失效。中间范围右侧的列，也就是说范围右侧的索引列将无效。分析上面4条SQL：条件单独使用name时，type=ref，key_len=82，ref=const。当条件添加到age时（使用等价的常量），type=ref,key_len=86,ref=const,const。全值匹配时，type=ref,key_len=128,ref=const,const,const。说明使用了所有的索引，从key_len和ref可以看出。当使用range(age>27),type=range,key_len=86,ref=Null时，可以看到只使用了部分索引，而性别索引没有用。结论：范围右侧的索引列无效。4、尽量使用覆盖索引1、覆盖索引定义如果一个索引包含（或覆盖）了所有需要查询的字段的值，就称为“覆盖索引”。即只需要扫描索引，不需要回表。只扫描索引不回表的优点：索引项通常比数据行的大小小很多，只需要读取索引，那么mysql会大大减少数据访问量。因为索引是按照列值的顺序存储的，所以IO密集型范围查找会比从磁盘随机读取每一行数据的IO少很多。一些存储引擎如myisam只在内存中缓存索引，数据依赖于操作系统来缓存，所以访问数据需要系统调用。Innodb的聚集索引，覆盖索引对Innodb表特别有用。（Innodb的二级索引保存的是叶子节点中行的主键值，所以如果二级主键可以覆盖查询，就可以避免主键索引的二次查询）覆盖索引必须存储索引的值列，而哈希腊索引、空间索引、全文索引不存储索引列的值，所以mysql只能使用B树索引作为覆盖索引。发起索引覆盖查询时，在explain2的extracolumn中可以看到usingindex的信息，比较使用覆盖索引的好处，尽量使用覆盖索引（查询列和索引列要一致索引，然后在查询中也使用列A和B），减少使用select*.mysql>explainselect*fromtb_empwherename='Jack'andage=27andgender='male';mysql>explainselectname,age,genderfromtb_empwherename='Jack'andage=27andgender='male';说明：对比两条SQL，第一个使用select*，第二个使用覆盖索引（查询列对应条件列），可以看出Extra由Null改为Usingindex提高检索效率。
