mysql如何使用索引

概述

索引优化的目的主要是让索引不失效，走正确的索引，今天主要分享的是最近整理的索引八大法则上篇，看完的话面试考索引应该没问题了~

下面主要以实验来帮助大家理解~

一、最佳左前缀法则

1、定义

在创建了多列索引的情况下，查询从索引的最左前列开始且不能跳过索引中的列。

最佳左前缀法则就是说如果创建了多个索引，在使用索引时要按照创建索引的顺序来使用，不能缺少或跳过，当然如果只使用最左边的索引列，也就是第一个索引是可以的。

2、环境准备

ｄｒｏｐ TABLE IF EXISTS `tb_emp`;CREATE TABLE `tb_emp` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) NOT NULL,`age` int(11) NOT NULL,gender varchar(10) NOT NULL,email varchar(20),PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;ｉｎｓｅｒｔ INTO `tb_emp` (name,age,gender,email) VALUES (‘Tom‘, ‘22‘,‘male‘,‘1@qq.com‘);ｉｎｓｅｒｔ INTO `tb_emp` (name,age,gender,email) VALUES (‘Mary‘, ‘21‘,‘female‘,‘2@qq.com‘);ｉｎｓｅｒｔ INTO `tb_emp` (name,age,gender,email) VALUES (‘Jack‘, ‘27‘,‘male‘,‘3@qq.com‘);ｉｎｓｅｒｔ INTO `tb_emp` (name,age,gender,email) VALUES (‘Rose‘, ‘23‘,‘female‘,‘4@qq.com‘);

3、创建组合索引

create index idx_all on tb_emp(name,age,gender);show index from tb_emp;

这里用火车头代表name,车厢代表age,车尾代表gender。

4、只有火车头

说明：

1）索引的创建顺序为name，age，gender；

2）直接使用name（火车头）作为条件，可以看到type=ref，key_len=82，ref=const，效果还行。

5、只有车厢

说明：没使用火车头（name），直接用车厢，导致走全表扫描（type=ALL）

6、火车头加车厢、火车头加车尾

说明：

火车头加车厢、火车头加车尾,虽然都是type=ref，但是观察key_len和ref两项，并对比只有火车头中的结果，可得出在使用火车头（name）和车尾（gender）时，只使用了部分索引也就是火车头（name）的索引。

通俗理解：火车头单独跑没问题，火车头与直接相连的车厢一起跑也没问题，但是火车头与车尾，如果中间没有车厢，只能火车头自己跑。

7、火车头加车厢加车尾

说明：火车头加车厢加车尾，三者串联，就变成了奔跑的小火车。type=ref，key_len=128，ref=const，const，const。

二、索引列不做计算

在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效从而转向全表扫描。

1、函数计算

说明：这里使用了函数计算，type=ALL，导致索引失效。

2、隐式类型转换

说明：这里‘123‘是字符串，而123是数字，发生了隐式类型转换，导致全表扫描(type=ALL)

三、范围右边索引列全失效

存储引擎不能使用索引中范围右边的列，也就是说范围右边的索引列会失效。

对以上4个SQL进行分析：

1）条件单独使用name时，type=ref，key_len=82，ref=const。

2）条件加上age时（使用常量等值），type=ref，key_len=86，ref=const，const。

3）当全值匹配时，type=ref，key_len=128，ref=const，const，const。说明索引全部用上，从key_len与ref可以看出。

4）当使用范围时（age>27），type=range，key_len=86，ref=Null，可以看到只使用了部分索引，但gender索引没用上。

结论：范围右边的索引列失效。

四、尽量使用覆盖索引

1、覆盖索引定义

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。

只扫描索引而无需回表的优点：

1）索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。

2）因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。

3）一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用

4）innodb的聚簇索引，覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)

覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B-tree索引做覆盖索引。

当发起一个索引覆盖查询时，在explain的extra列可以看到using index的信息

2、对比是否使用覆盖索引好处

尽量使用覆盖索引（查询列和索引列尽量一致，通俗说就是对A、B列创建了索引，然后查询中也使用A、B列），减少ｓｅｌｅｃｔ *的使用。

mysql> explain ｓｅｌｅｃｔ * from tb_emp where name=‘Jack‘ and age=27 and gender=‘male‘;mysql> explain ｓｅｌｅｃｔ name,age,gender from tb_emp where name=‘Jack‘ and age=27 and gender=‘male‘;

说明：对比两个sql，第一个使用ｓｅｌｅｃｔ *，第二个使用覆盖索引（查询列与条件列对应），可看到Extra从Null变成了Using index，提高检索效率。

篇幅有限，所以就分成两部分来介绍了，觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~