mysql的索引分析_MySQL索引分析与优化

索引分析

- 准备

先创建三张表：

tb_emp(员工表)

tb_dept(部门表)

tb_desc(描述表)

1. tb_emp(员工表)

DROP TABLE IF EXISTS`tb_emp`;CREATE TABLE`tb_emp` (

`id`int(11) NOT NULLAUTO_INCREMENT,

`username`varchar(20) NOT NULL,

`deptid`int(11) NOT NULL,PRIMARY KEY(`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;------------------------------

INSERT INTO `tb_emp`(username,deptid) VALUES ('Tom', '1');INSERT INTO `tb_emp`(username,deptid) VALUES ('Jack', '1');INSERT INTO `tb_emp`(username,deptid) VALUES ('Mary', '2');INSERT INTO `tb_emp`(username,deptid) VALUES ('Rose', '3');

2.tb_dept(部门表)

DROP TABLE IF EXISTS`tb_dept`;CREATE TABLE`tb_dept` (

`id`int(11) NOT NULLAUTO_INCREMENT,

`name`varchar(20) NOT NULL,PRIMARY KEY(`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;------------------------------

INSERT INTO `tb_dept`(name) VALUES ('综合部');INSERT INTO `tb_dept`(name) VALUES ('研发');INSERT INTO `tb_dept`(name) VALUES ('测试');INSERT INTO `tb_dept`(name) VALUES ('总裁');

3.tb_desc(描述表)

DROP TABLE IF EXISTS`tb_desc`;CREATE TABLE`tb_desc` (

`id`int(11) NOT NULLAUTO_INCREMENT,

`empid`int(11) DEFAULT NULL,

`deptid`int(11) DEFAULT NULL,PRIMARY KEY(`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;------------------------------

INSERT INTO `tb_desc`(empid,deptid) VALUES (1, 1);INSERT INTO `tb_desc`(empid,deptid) VALUES (2, 1);INSERT INTO `tb_desc`(empid,deptid) VALUES (3, 2);INSERT INTO `tb_desc`(empid,deptid) VALUES (4, 3);

注：为了进行join的分析，这里将员工表与部门表不直接关联，再通过tb_desc(描述表)进行关联。

1.left join

1).首先执行查询。

2).通过explain进行分析。

分析：从explain执行结果可以看出对两表都是用了全表扫描(ALL)，并且在tb_desc(描述表)中还使用了join连接缓存，需要进行优化。

但是如何优化？是在左表建立索引还是右表建立索引呢 还是都建立索引？

——左连接左表是全有，或许应该在右表建立索引？

3).右表创建索引。

在创建索引后，可以获得比较不错的结果。(type=ref,Extra=Using index)

结论：left join(左连接)情况下，应该在右表(tb_desc)创建索引。

2.right join

通过上面left join的测试结果，我们直接交换两表位置，并将left join改变成right join。

与left join进行对比：

1)在left join下，首先执行tb_emp(左表)，type=ALL，因为左连接情况下左表全有，因此我们在tb_desc(右表)创建索引，得到比较理想的效果。

2)在right join下(我们交换了tb_emp和tb_desc的位置)，执行顺序：tb_emp(右表)→ tb_desc(左表)。右表type=ALL，因为右连接情况下右表全有，因此在左表(tb_desc，我们交换了位置)创建索引，效果肯定和left join一样。

总结

left join(左连接)：右表创建索引。

right join(右连接)：左表创建索引。

浓缩得出精华——索引相反建

索引优化

—索引优化的目的主要是让索引不失效

-准备

创建经典的tb_emp表。

DROP TABLE IF EXISTS`tb_emp`;CREATE TABLE`tb_emp` (

`id`int(11) NOT NULLAUTO_INCREMENT,

`name`varchar(20) NOT NULL,

`age`int(11) NOT NULL,

gendervarchar(10) NOT NULL,

emailvarchar(20),PRIMARY KEY(`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

------------------------------

INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Tom', '22','male','1@qq.com');INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Mary', '21','female','2@qq.com');INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Jack', '27','male','3@qq.com');INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Rose', '23','female','4@qq.com');

：创建了tb_emp表，并插入了4条数据。

1.最佳左前缀法则

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

最佳左前缀法则就是说如果创建了多个索引，在使用索引时要按照创建索引的顺序来使用，不能缺少或跳过，当然如果只使用最左边的索引列，也就是第一个索引是可以的，通俗理解：“带头大哥不能死，中间兄弟不能断”。要点：“头不能掉”。

用案例进行说明

2)创建组合索引，并执行explain。

Case 1：

分析：

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

②直接使用name(带头大哥)作为条件，可以看到type=ref，key_len=82，ref=const，效果还不错。

Case 2：

分析：

没使用带头大哥(name)，直接用兄弟，type=ALL，为全表扫描。

Case 3：

分析：

①对比上面两句sql语句可发现：我们使用：火车头(name)和中间车厢(age)、火车头(name)和车尾(gender)。

②虽然type=ref，但是观察key_len和ref两项，并对比Case1中的结果，可得出在使用火车头(name)和车尾(gender)时，只使用了部分索引也就是火车头(name)的索引。

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

Case 4：

分析：

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

最佳左前缀法则总结：带头大哥不能死，中间兄弟不能断；带头大哥可跑路，老二也可跟着跑，其余兄弟只能死。

2.不要在索引列上做任何操作

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

Case 1：

分析：

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

Case 2：

分析：

将name=‘Tom’的值修改为‘123’，使用sql后，发生了类型转换，type=ALL，导致全表扫描。

结论：在索引列上做任何操作，都会导致索引失效转向全表扫描。

3.范围右边全失效

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

Case 1：

Case 2：

Case 3：

Case 4：

对以上4个case进行分析：

①条件单独使用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可以看出。

④当使用范围时(age>27)，type=range，key_len=86，ref=Null，与Case 1、Case2和Case3可知，使用了部分索引，但gender索引没用上(与Case 3对比)。

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

4.尽量使用覆盖索引

尽量使用覆盖索引(查询列和索引列尽量一致，通俗说就是对A、B列创建了索引，然后查询中也使用A、B列)，减少select *的使用。

Case 1：

Case 2：

分析：

对比Case1和Case2，Case1使用select *，Case2使用覆盖索引(查询列与条件列对应)，可看到Extra从Null变成了Using index，提高检索效率。

5.使用不等于(！=或<>)会使索引失效

结论：使用！=会使type=ALL，key=Null，导致全表扫描，并且索引失效。

6.is null 或 is not null也无法使用索引

Case 1：

Case 2：

分析：

在使用is null的时候，索引完全失效，使用is not null的时候，type=ALL全表扫描，key=Null索引失效。

这里的例子可能有点特殊，具体情况肯能和case上的有所不同，但是还是要注意is null和is not null的使用。

7.like通配符以%开头会使索引失效

Case 1：

Case 2：

Case 3：

分析：

①like的%位置不同，所产生的效果不一样，当%出现在左边的时候，type=ALL，key=Null(全表扫描，索引失效)，当%出现在右边的时候，type=range，索引未失效。

②like查询为范围查询，%出现在左边，则索引失效。%出现在右边索引未失效。口诀：like百分加右边。

但是在实际生产环境中，%仅出现在右边可能不能够解决我们的问题，所以解决%出现在左边索引失效的方法：使用覆盖索引。

Case 4：

分析：对比Case1可知，通过覆盖索引type=index，并且使用了Using index，从全表扫描变成了全索引扫描，还是不错的。

Case 5：

分析：这里出现type=index，因为主键自动创建唯一索引。

Case 6：

分析：上面四组explain执行的结果都相同，表明都使用了索引，从这里可以深刻的体会到覆盖索引：完全吻合或者沾边(age)，都可以使type=index。

Case 7：

分析：由于只在(name，age，gender)上创建索引，当包含email时，导致结果集偏大(email未建索引)【锅大，锅盖小，不能匹配】，所以type=ALL。

8.字符串不加单引号导致索引失效

Case 1：

分析：上述两条sql语句都能查询出相同的数据。

Case 2：

分析：

通过explain执行结果可以看出，字符串(name)不加单引号在查询的时候，导致索引失效(type=ref变成了type=ALL，并且key=Null)，并全表扫描。

结论：varchar类型的字段，在查询的时候不加单引号导致索引失效，转向全表扫描。

9.少用or，用or连接会使索引失效

结论：通过上述explain的执行结果可看出，在使用or连接的时候type=ALL，key=Null，索引失效，并全表扫描。

总结

①全值匹配。

②最佳左前缀法则：带头大哥不能死，中间兄弟不能断；带头大哥可跑路，老二也可跟着跑，其余兄弟只能死。

③索引列上不计算。

④覆盖索引记住用。

⑤不等于、is null、is not null导致索引失效。

⑥like百分加右边，加左边导致索引失效，解决方法：使用覆盖索引。

⑦字符串不加单引号导致索引失效。

⑧少用or，用or导致索引失效。

更多精彩内容；你每先多积累一分，时间越长你与其他人的差距就会越发明显，复利算法应该了解一下

从一个Coder逐步走向CTO或是架构师 必然需要不断越过周遭同阶段的同行，加油！