第 2 章索引优化分析

分手后的思念是犯贱 2022-11-26 08:54 155阅读 0赞

# 第 2 章 索引优化分析 #

## 1、慢 SQL ##

> **性能下降、 SQL 慢、执行时间长、等待时间长的原因分析**

1.  查询语句写的烂
2.  索引失效：
    
     *  单值索引：在user表中给name属性建个索引，`create index idx_user_name on user(name)`
     *  复合索引：在user表中给name、email属性建个索引，`create index idx_user_nameEmail on user(name,email)`
3.  关联查询太多join(设计缺陷或不得已的需求)
4.  服务器调优及各个参数设置(缓冲、线程数等)

## 2、join 查询 ##

### 2.1、SQL 执行顺序 ###

> **我们手写的 SQL 顺序**

![image-20200803212731183][]

> **MySQL 实际执行 SQL 顺序**

1.  mysql 执行的顺序：随着 Mysql 版本的更新换代， 其优化器也在不断的升级， 优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。
2.  下面是经常出现的查询顺序：

![image-20200803212826429][]

*  总结：mysql 从 FROM 开始执行~

![image-20200803212936111][]

### 2.2、JOIN 连接查询 ###

> **常见的 JOIN 查询图**

![image-20200803213106434][]

> **建表 SQL**

CREATE TABLE tbl_dept(
    	id INT(11) NOT NULL AUTO_INCREMENT,
    	deptName VARCHAR(30) DEFAULT NULL,
    	locAdd VARCHAR(40) DEFAULT NULL,
    	PRIMARY KEY(id)
    )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    
    CREATE TABLE tbl_emp (
    	id INT(11) NOT NULL AUTO_INCREMENT,
    	NAME VARCHAR(20) DEFAULT NULL,
    	deptId INT(11) DEFAULT NULL,
    	PRIMARY KEY (id),
    	KEY fk_dept_Id (deptId)
    	#CONSTRAINT 'fk_dept_Id' foreign key ('deptId') references 'tbl_dept'('Id')
    )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    
    INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
    INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
    INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
    INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
    INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);
    
    INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1);
    INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);
    INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1);
    INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2);
    INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);
    INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);
    INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);
    INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);

*  tbl\_dept 表结构

mysql> select * from tbl_dept;
    +----+----------+--------+
    | id | deptName | locAdd |
    +----+----------+--------+
    |  1 | RD       | 11     |
    |  2 | HR       | 12     |
    |  3 | MK       | 13     |
    |  4 | MIS      | 14     |
    |  5 | FD       | 15     |
    +----+----------+--------+
    5 rows in set (0.00 sec)

*  tbl\_emp 表结构

mysql> select * from tbl_emp;
    +----+------+--------+
    | id | NAME | deptId |
    +----+------+--------+
    |  1 | z3   |      1 |
    |  2 | z4   |      1 |
    |  3 | z5   |      1 |
    |  4 | w5   |      2 |
    |  5 | w6   |      2 |
    |  6 | s7   |      3 |
    |  7 | s8   |      4 |
    |  8 | s9   |     51 |
    +----+------+--------+
    8 rows in set (0.00 sec)

> **7 种 JOIN 示例**

**笛卡尔积**

1.  tbl\_emp 表和 tbl\_dept 表的笛卡尔乘积：`select * from tbl_emp, tbl_dept;`
2.  其结果集的个数为：5 \* 8 = 40

mysql> select * from tbl_emp, tbl_dept;
    +----+------+--------+----+----------+--------+
    | id | NAME | deptId | id | deptName | locAdd |
    +----+------+--------+----+----------+--------+
    |  1 | z3   |      1 |  1 | RD       | 11     |
    |  1 | z3   |      1 |  2 | HR       | 12     |
    |  1 | z3   |      1 |  3 | MK       | 13     |
    |  1 | z3   |      1 |  4 | MIS      | 14     |
    |  1 | z3   |      1 |  5 | FD       | 15     |
    |  2 | z4   |      1 |  1 | RD       | 11     |
    |  2 | z4   |      1 |  2 | HR       | 12     |
    |  2 | z4   |      1 |  3 | MK       | 13     |
    |  2 | z4   |      1 |  4 | MIS      | 14     |
    |  2 | z4   |      1 |  5 | FD       | 15     |
    |  3 | z5   |      1 |  1 | RD       | 11     |
    |  3 | z5   |      1 |  2 | HR       | 12     |
    |  3 | z5   |      1 |  3 | MK       | 13     |
    |  3 | z5   |      1 |  4 | MIS      | 14     |
    |  3 | z5   |      1 |  5 | FD       | 15     |
    |  4 | w5   |      2 |  1 | RD       | 11     |
    |  4 | w5   |      2 |  2 | HR       | 12     |
    |  4 | w5   |      2 |  3 | MK       | 13     |
    |  4 | w5   |      2 |  4 | MIS      | 14     |
    |  4 | w5   |      2 |  5 | FD       | 15     |
    |  5 | w6   |      2 |  1 | RD       | 11     |
    |  5 | w6   |      2 |  2 | HR       | 12     |
    |  5 | w6   |      2 |  3 | MK       | 13     |
    |  5 | w6   |      2 |  4 | MIS      | 14     |
    |  5 | w6   |      2 |  5 | FD       | 15     |
    |  6 | s7   |      3 |  1 | RD       | 11     |
    |  6 | s7   |      3 |  2 | HR       | 12     |
    |  6 | s7   |      3 |  3 | MK       | 13     |
    |  6 | s7   |      3 |  4 | MIS      | 14     |
    |  6 | s7   |      3 |  5 | FD       | 15     |
    |  7 | s8   |      4 |  1 | RD       | 11     |
    |  7 | s8   |      4 |  2 | HR       | 12     |
    |  7 | s8   |      4 |  3 | MK       | 13     |
    |  7 | s8   |      4 |  4 | MIS      | 14     |
    |  7 | s8   |      4 |  5 | FD       | 15     |
    |  8 | s9   |     51 |  1 | RD       | 11     |
    |  8 | s9   |     51 |  2 | HR       | 12     |
    |  8 | s9   |     51 |  3 | MK       | 13     |
    |  8 | s9   |     51 |  4 | MIS      | 14     |
    |  8 | s9   |     51 |  5 | FD       | 15     |
    +----+------+--------+----+----------+--------+
    40 rows in set (0.00 sec)

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

**inner join**

1.  tbl\_emp 表和 tbl\_dept 的交集部分（公共部分）
2.  `select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id;`

mysql> select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id;
    +----+------+--------+----+----------+--------+
    | id | NAME | deptId | id | deptName | locAdd |
    +----+------+--------+----+----------+--------+
    |  1 | z3   |      1 |  1 | RD       | 11     |
    |  2 | z4   |      1 |  1 | RD       | 11     |
    |  3 | z5   |      1 |  1 | RD       | 11     |
    |  4 | w5   |      2 |  2 | HR       | 12     |
    |  5 | w6   |      2 |  2 | HR       | 12     |
    |  6 | s7   |      3 |  3 | MK       | 13     |
    |  7 | s8   |      4 |  4 | MIS      | 14     |
    +----+------+--------+----+----------+--------+
    7 rows in set (0.00 sec)

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

**left join**

1.  tbl\_emp 与 tbl\_dept 的公共部分 + tbl\_emp 表的独有部分
2.  left join：取左表独有部分 + 两表公共部分
3.  `select * from tbl_emp e left join tbl_dept d on e.deptId = d.id;`

mysql> select * from tbl_emp e left join tbl_dept d on e.deptId = d.id;
    +----+------+--------+------+----------+--------+
    | id | NAME | deptId | id   | deptName | locAdd |
    +----+------+--------+------+----------+--------+
    |  1 | z3   |      1 |    1 | RD       | 11     |
    |  2 | z4   |      1 |    1 | RD       | 11     |
    |  3 | z5   |      1 |    1 | RD       | 11     |
    |  4 | w5   |      2 |    2 | HR       | 12     |
    |  5 | w6   |      2 |    2 | HR       | 12     |
    |  6 | s7   |      3 |    3 | MK       | 13     |
    |  7 | s8   |      4 |    4 | MIS      | 14     |
    |  8 | s9   |     51 | NULL | NULL     | NULL   |
    +----+------+--------+------+----------+--------+
    8 rows in set (0.00 sec)

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

**right join**

1.  tbl\_emp 与 tbl\_dept 的公共部分 + tbl\_dept表的独有部分
2.  right join：取右表独有部分 + 两表公共部分
3.  `select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;`

mysql> select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;
    +------+------+--------+----+----------+--------+
    | id   | NAME | deptId | id | deptName | locAdd |
    +------+------+--------+----+----------+--------+
    |    1 | z3   |      1 |  1 | RD       | 11     |
    |    2 | z4   |      1 |  1 | RD       | 11     |
    |    3 | z5   |      1 |  1 | RD       | 11     |
    |    4 | w5   |      2 |  2 | HR       | 12     |
    |    5 | w6   |      2 |  2 | HR       | 12     |
    |    6 | s7   |      3 |  3 | MK       | 13     |
    |    7 | s8   |      4 |  4 | MIS      | 14     |
    | NULL | NULL |   NULL |  5 | FD       | 15     |
    +------+------+--------+----+----------+--------+
    8 rows in set (0.00 sec)

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

**left join without common part**

1.  tbl\_emp 表的独有部分：将 left join 结果集中的两表公共部分去掉即可：`where d.id is null`
2.  `select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null;`

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

**right join without common part**

1.  tbl\_dept表的独有部分：将 right join 结果集中的两表公共部分去掉即可：`where e.id is null`
2.  `select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.id is null;`

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

**full join**

1.  兄弟，mysql 不支持 full join ，但是我们可以通过骚操作实现 full join ，union 关键字用于连接结果集，并且自动去重
2.  tbl\_emp 与 tbl\_dept 的公共部分 + tbl\_emp 表的独有部分 + tbl\_dept表的独有部分：将 left join 的结果集和 right join 的结果集使用 union 合并即可
3.  `select * from tbl_emp e left join tbl_dept d on e.deptId = d.id union select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;`

mysql> select * from tbl_emp e left join tbl_dept d on e.deptId = d.id
        -> union
        -> select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;
    +------+------+--------+------+----------+--------+
    | id   | NAME | deptId | id   | deptName | locAdd |
    +------+------+--------+------+----------+--------+
    |    1 | z3   |      1 |    1 | RD       | 11     |
    |    2 | z4   |      1 |    1 | RD       | 11     |
    |    3 | z5   |      1 |    1 | RD       | 11     |
    |    4 | w5   |      2 |    2 | HR       | 12     |
    |    5 | w6   |      2 |    2 | HR       | 12     |
    |    6 | s7   |      3 |    3 | MK       | 13     |
    |    7 | s8   |      4 |    4 | MIS      | 14     |
    |    8 | s9   |     51 | NULL | NULL     | NULL   |
    | NULL | NULL |   NULL |    5 | FD       | 15     |
    +------+------+--------+------+----------+--------+
    9 rows in set (0.00 sec)

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

**full join without common part**

1.  tbl\_emp 表的独有部分 + tbl\_dept表的独有部分
2.  `select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null union select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.id is null;`

mysql> select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null
        -> union
        -> select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.id is null;
    +------+------+--------+------+----------+--------+
    | id   | NAME | deptId | id   | deptName | locAdd |
    +------+------+--------+------+----------+--------+
    |    8 | s9   |     51 | NULL | NULL     | NULL   |
    | NULL | NULL |   NULL |    5 | FD       | 15     |
    +------+------+--------+------+----------+--------+
    2 rows in set (0.00 sec)

## 3、索引简介 ##

### 3.1、索引是什么 ###

> **索引是个什么东东？**

1.  MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：**索引是数据结构**
2.  你可以简单理解为"**排好序的快速查找数据结构**"，即**索引 = 排序 + 查找**
3.  一般来说索引本身占用内存空间也很大，不可能全部存储在内存中，因此**索引往往以文件形式存储在硬盘上**
4.  我们平时所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉树)结构组织的索引。
5.  聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引(hash index)等。

### 3.2、索引原理 ###

> **将索引理解为"排好序的快速查找数据结构"**

1.  在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构**以某种方式引用（指向）数据**，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
2.  下图就是一种可能的索引方式示例：
    
     *  左边是数据表，一共有两列七条记录，最左边的十六进制数字是数据记录的物理地址
     *  为了加快col2的查找，可以维护一个右边所示的二叉查找树，**每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针**，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

![image-20200803222848380][]

### 3.3、索引优劣势 ###

> **索引的优势**

1.  类似大学图书馆的书目索引，提高数据检索效率，**降低数据库的IO成本**
2.  通过索引列对数据进行排序，**降低数据排序成本**，降低了CPU的消耗

> **索引的劣势**

1.  实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占用空间的
2.  虽然索引大大提高了查询速度，**同时却会降低更新表的速度**，如果对表INSERT，UPDATE和DELETE。因为更新表时，MySQL不仅要不存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
3.  索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句

### 3.4、MySQL 索引分类 ###

> **mysql 索引分类**

参考资料：https://www.cnblogs.com/luyucheng/p/6289714.html

1.  普通索引：是最基本的索引，它没有任何限制，即一个索引只包含单个列，一个表可以有多个单列索引；建议一张表索引不要超过5个，优先考虑复合索引
2.  唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
3.  主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：
4.  复合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
5.  全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配

### 3.5、MySQL 索引语法 ###

> **建立索引的 SQL 语句**

1.  创建索引：
    
     *  如果是CHAR和VARCHAR类型，length可以小于字段实际长度；
     *  如果是BLOB和TEXT类型，必须指定length。
    
        CREATE [UNIQUE] INDEX  indexName ON mytable(columnname(length));
        ' or '
        ALTER mytable ADD [UNIQUE]  INDEX [indexName] ON(columnname(length));
2.  删除索引
    
        DROP INDEX [indexName] ON mytable;
3.  查看索引（`\G`表示将查询到的横向表格纵向输出，方便阅读）
    
        SHOW INDEX FROM table_name\G

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

**使用 ALTER 命令，有四种方式来添加数据表的索引：**

1.  `ALTER TABLE tbl_name ADD PRIMARY KEY(column_list)`：该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
2.  `ALTER TABLE tbl_name ADD UNIQUE index_name(column_list)`：这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
3.  `ALTER TABLE tbl_name ADD INDEX index_name(column_list)`：.添加普通索引，索引值可出现多次。
4.  `ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list)`：该语句指定了索引为FULLTEXT，用于全文索引。

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

**带你看看 mysql 索引：Index\_type 为 BTREE**

### 3.6、MySQL 索引结构 ###

#### 3.6.1、Btree 索引 ####

> **Btree 索引搜索过程**

【初始化介绍】

1.  一颗 b 树， **浅蓝色的块我们称之为一个磁盘块**， 可以看到每个磁盘块包含几个**数据项（深蓝色所示） 和指针（黄色所示）**
2.  如磁盘块 1 包含数据项 17 和 35， 包含指针 P1、 P2、 P3
3.  P1 表示小于 17 的磁盘块， P2 表示在 17 和 35 之间的磁盘块， P3 表示大于 35 的磁盘块
4.  **真实的数据存在于叶子节点和非叶子节点中**

【查找过程】

1.  如果要查找数据项 29， 那么首先会把磁盘块 1 由磁盘加载到内存， 此时发生一次 IO， 在内存中用二分查找确定 29在 17 和 35 之间， 锁定磁盘块 1 的 P2 指针， 内存时间因为非常短（相比磁盘的 IO） 可以忽略不计
2.  通过磁盘块 1的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存， 发生第二次 IO， 29 在 26 和 30 之间， 锁定磁盘块 3 的 P2 指针
3.  通过指针加载磁盘块 8 到内存， 发生第三次 IO， 同时内存中做二分查找找到 29， 结束查询， 总计三次 IO。

![image-20200804093236612][]

#### 3.6.2、B+tree 索引 ####

> **B+tree 索引搜索过程**

【B+Tree 与 BTree 的区别】

B-树的关键字（数据项）和记录是放在一起的； B+树的非叶子节点中只有关键字和指向下一个节点的索引， 记录只放在叶子节点中。

【B+Tree 与 BTree 的查找过程】

1.  在 B 树中， 越靠近根节点的记录查找时间越快， 只要找到关键字即可确定记录的存在； 而 B+ 树中每个记录的查找时间基本是一样的， 都需要从根节点走到叶子节点， 而且在叶子节点中还要再比较关键字。
2.  从这个角度看 B 树的性能好像要比 B+ 树好， 而在实际应用中却是 B+ 树的性能要好些。 因为 B+ 树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比 B 树多， 树高比 B 树小， 这样带来的好处是减少磁盘访问次数。
3.  尽管 B+ 树找到一个记录所需的比较次数要比 B 树多， 但是一次磁盘访问的时间相当于成百上千次内存比较的时间， 因此实际中B+ 树的性能可能还会好些， 而且 B+树的叶子节点使用指针连接在一起， 方便顺序遍历（范围搜索）， 这也是很多数据库和文件系统使用 B+树的缘故。

【性能提升】

真实的情况是， 3 层的 B+ 树可以表示上百万的数据， 如果上百万的数据查找只需要三次 IO， 性能提高将是巨大的，如果没有索引， 每个数据项都要发生一次 IO， 那么总共需要百万次的 IO， 显然成本非常非常高。

【思考： 为什么说 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引？】

1.  B+树的磁盘读写代价更低：B+树的内部结点并没有指向关键字具体信息的指针。 因此其内部结点相对 B 树更小。 如果把所有同一内部结点的关键字存放在同一盘块中， 那么盘块所能容纳的关键字数量也越多。 一次性读入内存中的需要查找的关键字也就越多。 相对来说 IO 读写次数也就降低了。
2.  B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点， 而只是叶子结点中关键字的索引。 所以任何关键字的查找必须走一条从根结点到叶子结点的路。 所有关键字查询的路径长度相同， 导致每一个数据的查询效率相当。

![image-20200811145838930][]

### 3.7、何时需要建索引 ###

> **哪些情况下适合建立索引**

1.  **主键自动建立唯一索引**
2.  **频繁作为查询的条件的字段**应该创建索引
3.  **查询中与其他表关联的字段**，外键关系建立索引
4.  频繁更新的字段不适合创建索引
5.  Where 条件里用不到的字段不创建索引
6.  单间/组合索引的选择问题，Who？（在高并发下倾向创建组合索引）
7.  **查询中排序的字段**，排序字段若通过索引去访问将大大提高排序的速度
8.  **查询中统计或者分组字段**

> **哪些情况不要创建索引**

1.  表记录太少
2.  **经常增删改的表**
3.  **数据重复且分布平均的表字段**，因此应该只为经常查询和经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

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

**案例分析：**

1.  假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
2.  索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。
3.  一个索引的选择性越接近于1，这个索引的效率就越高。

## 4、性能分析 ##

### 4.1、性能优化概述 ###

> **MySQL Query Optimizer 的作用**

1.  MySQL 中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（MySQL认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间）
2.  当客户端向MySQL 请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer 首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query中的Hint信息（如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint 或Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

> **MySQL 常见瓶颈**

1.  CPU 瓶颈：CPU在饱和的时候一般发生在数据装入在内存或从磁盘上读取数据时候
2.  IO 瓶颈：磁盘I/O瓶颈发生在装入数据远大于内存容量时
3.  服务器硬件的性能瓶颈：top、free、iostat和vmstat来查看系统的性能状态

### 4.2、Explain 概述 ###

> **Explain**

**是什么？Explain 是查看执行计划**

1.  使用EXPLAIN关键字可以模拟优化器执行SQL语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈
2.  官网地址：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

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

**能干嘛？**

1.  表的读取顺序（id 字段）
2.  数据读取操作的操作类型（select\_type 字段）
3.  哪些索引可以使用（possible\_keys 字段）
4.  哪些索引被实际使用（keys 字段）
5.  表之间的引用（ref 字段）
6.  每张表有多少行被优化器查询（rows 字段）

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

**怎么玩？**

*  Explain + SQL语句

### 4.3、Explain 详解 ###

> **id：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序**

id 取值的三种情况：

1.  **id相同**，执行顺序由上至下
    
    ![image-20200804101016101][]
2.  **id不同**，如果是子查询，id的序号会递增，**id值越大优先级越高，越先被执行**
    
    ![image-20200804101005684][]
3.  **id相同不同**，同时存在：id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行；衍生=DERIVED
    
    ![image-20200804101502048][]

> **select\_type：查询的类型，主要用于区别普通查询、联合查询、子查询等复杂查询**

1.  SIMPLE：简单的select查询，查询中不包含子查询或者UNION
2.  PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
3.  SUBQUERY：在SELECT或者WHERE列表中包含了子查询
4.  DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里
5.  UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
6.  UNION RESULT：从UNION表获取结果的SELECT

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

**UNION 和 UNION RESULT举例**

> **table：显示这一行的数据是关于哪张表的**

> **type：访问类型排列，显示查询使用了何种类型**

1.  type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：`system>const>eq_ref>ref>fultext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL`
2.  挑重要的来说：`system>const>eq_ref>ref>range>index>ALL`，一般来说，得保证查询至少达到range级别，最好能达到ref。

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

**从最好到最差依次是：system>const>eq\_ref>ref>range>index>ALL**

1.  system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计
2.  const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
    
    ![image-20200804103028065][]
3.  eq\_ref：唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
    
    ![image-20200804103543783][]
4.  ref：非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
    
    ![image-20200804103959011][]
5.  **range**：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了`between`、`<`、`>`、`in`等的查询这种范围扫描索引扫描比全表扫描要好，因为他**只需要开始索引的某一点，而结束于另一点，不用扫描全部索引**
    
    ![image-20200804104130086][]
6.  **index**：Full Index Scan，index与ALL区别为index类型**只遍历索引树**。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说**虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘数据库文件中读的**）
    
    ![image-20200804104254208][]
7.  **all**：FullTable Scan，**将遍历全表以找到匹配的行（全表扫描）**
    
    ![image-20200804104358142][]
8.  备注：一般来说，得保证查询只是达到range级别，最好达到ref

> **possible\_keys**

1.  显示**可能**应用在这张表中的索引，一个或多个
2.  若查询涉及的字段上存在索引，则该索引将被列出，但不一定被查询实际使用

> **key**

1.  **实际**使用的索引，如果为null，则没有使用索引
2.  **若查询中使用了覆盖索引，则该索引仅出现在key列表中**
    
    ![image-20200804105225100][]

> **key\_len**

1.  **表示索引中使用的字节数**，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
2.  key\_len显示的值为**索引最大可能长度**，并非实际使用长度，即key\_len是根据表定义计算而得，不是通过表内检索出的

![image-20200804105833936][]

> **ref**

1.  **显示索引哪一列被使用了**，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值
2.  由key\_len可知t1表的索引idx\_col1\_col2被充分使用，t1表的col1匹配t2表的col1，t1表的col2匹配了一个常量，即’ac’

![image-20200804110346982][]

> **rows**

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

![image-20200804111249713][]

> **Extra：包含不适合在其他列中显示但十分重要的额外信息**

1.  **Using filesort（文件排序）：**
    
     *  MySQL中无法利用索引完成排序操作成为“文件排序”
     *  说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取
     *  **出现 Using filesort 不好（九死一生），需要尽快优化 SQL**
     *  示例中第一个查询只使用了 col1 和 col3，原有索引派不上用场，所以进行了外部文件排序
     *  示例中第二个查询使用了 col1、col2 和 col3，原有索引派上用场，无需进行文件排序
    
    ![image-20200804111738931][]
2.  **Using temporary（创建临时表）：**
    
     *  使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by
     *  **出现 Using temporary 超级不好（十死无生），需要立即优化 SQL**
     *  示例中第一个查询只使用了 col1，原有索引派不上用场，所以创建了临时表进行分组
     *  示例中第二个查询使用了 col1、col2，原有索引派上用场，无需创建临时表
    
    ![image-20200804112558915][]
3.  **Using index（覆盖索引）：**
    
     *  表示相应的select操作中使用了覆盖索引（Coveing Index），避免访问了表的数据行，效率不错！
     *  如果同时出现using where，表明索引被用来执行索引键值的查找
     *  如果没有同时出现using where，表明索引用来读取数据而非执行查找动作
        
        ![image-20200804113450147][]
     *  **覆盖索引（Covering Index），也说为索引覆盖**
        
         *  理解方式一：就是**select的数据列只用从索引中就能够取得，不必读取数据行**，MySQL可以**利用索引返回select列表中的字段，而不必根据索引再次读取数据文件**，换句话说查询列要被所建的索引覆盖。
         *  理解方式二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。
         *  注意：**如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可`select *`** ，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。
4.  **Using where**：表明使用了where过滤
5.  **Using join buffer**：表明使用了连接缓存
6.  impossible where：where子句的值总是false，不能用来获取任何元组
    
    ![image-20200804113946144][]
7.  select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化`COUNT(*)`操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
8.  distinct：优化distinct，在找到第一匹配的元组后即停止找同样值的工作

> **Explain 热身 Case**

1.  第一行（执行顺序4）：id列为1，表示是union里的第一个select，select\_type列的primary表示该查询为外层查询，table列被标记为`<derived3>`，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【`select d1.name ...`】
2.  第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【`select id, name from t1 where other_column= ' '`】
3.  第三行（执行顺序3）：select列表中的子查询select\_type为subquery，为整个查询中的第二个select。【`select id from t3`】
4.  第四行（执行顺序1）：select\_type为union，说明第四个select是union里的第二个select，最先执行【`select name, id from t2`】
5.  第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1, 4>表示用第一个和第四个select的结果进行union操作。【两个结果进行uinion操作】

![image-20200804114203012][]

## 5、索引优化 ##

### 5.1、单表索引优化 ###

> **单表索引优化分析**

**创建表**

*  建表 SQL

CREATE TABLE IF NOT EXISTS article(
    	id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    	author_id INT(10) UNSIGNED NOT NULL,
    	category_id INT(10) UNSIGNED NOT NULL,
    	views INT(10) UNSIGNED NOT NULL,
    	comments INT(10) UNSIGNED NOT NULL,
    	title VARCHAR(255) NOT NULL,
    	content TEXT NOT NULL
    );
    
    INSERT INTO article(author_id,category_id,views,comments,title,content)
    VALUES
    (1,1,1,1,'1','1'),
    (2,2,2,2,'2','2'),
    (1,1,3,3,'3','3');

*  表中的测试数据

mysql> SELECT * FROM article;
    +----+-----------+-------------+-------+----------+-------+---------+
    | id | author_id | category_id | views | comments | title | content |
    +----+-----------+-------------+-------+----------+-------+---------+
    |  1 |         1 |           1 |     1 |        1 | 1     | 1       |
    |  2 |         2 |           2 |     2 |        2 | 2     | 2       |
    |  3 |         1 |           1 |     3 |        3 | 3     | 3       |
    +----+-----------+-------------+-------+----------+-------+---------+
    3 rows in set (0.00 sec)

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

**查询案例**

*  查询category\_id为1且comments 大于1的情况下，views最多的article\_id。

mysql> SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
    +----+-----------+
    | id | author_id |
    +----+-----------+
    |  3 |         1 |
    +----+-----------+
    1 row in set (0.00 sec)

*  此时 article 表中只有一个主键索引

*  使用 explain 分析 SQL 语句的执行效率：`EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;`

*  结论：
    
     *  很显然，type是ALL，即最坏的情况。
     *  Extra 里还出现了Using filesort，也是最坏的情况。
     *  优化是必须的。

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

**开始优化：新建索引**

*  创建索引的 SQL 命令

# ALTER TABLE article ADD INDEX idx_article_ccv('category_id', 'comments', 'views'); 
    create index idx_article_ccv on article(category_id, comments, views);

*  在 category\_id 列、comments 列和 views 列上建立联合索引

*  再次执行查询：type变成了range，这是可以忍受的。但是extra里使用Using filesort仍是无法接受的。

*  分析：
    
     *  但是我们已经建立了索引，为啥没用呢？
     *  这是因为按照B+Tree索引的工作原理，先排序 category\_id，如果遇到相同的 category\_id 则再排序comments，如果遇到相同的 comments 则再排序 views。
     *  当comments字段在联合索引里处于中间位置时，**因为`comments>1`条件是一个范围值**（所谓 range），MySQL 无法利用索引再对后面的views部分进行检索，即 **range 类型查询字段后面的索引无效**。
 *  将查询条件中的 `comments > 1` 改为 `comments = 1` ，发现 Use filesort 神奇地消失了，从这点可以验证：范围后的索引会导致索引失效

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

**删除索引**

*  删除索引的 SQL 指令

DROP INDEX idx_article_ccv ON article;

*  删除刚才创建的 idx\_article\_ccv 索引

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

**再次创建索引**

*  创建索引的 SQL 指令

# ALTER TABLE article ADD INDEX idx_article_ccv('category_id',  'views'); 
    create index idx_article_ccv on article(category_id, views);

*  由于 range 后（`comments > 1`）的索引会失效，这次我们建立索引时，直接抛弃 comments 列，先利用 category\_id 和 views 的联合索引查询所需要的数据，再从其中取出 `comments > 1` 的数据（我觉着应该是这样的）

*  再次执行查询：可以看到，type变为了ref，Extra中的Using filesort也消失了，结果非常理想

*  为了不影响之后的测试，删除该表的 idx\_article\_ccv 索引

### 5.2、两表索引优化 ###

> **两表索引优化分析：主外键**

**创建表**

*  建表 SQL

CREATE TABLE IF NOT EXISTS class(
    	id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    	card INT(10) UNSIGNED NOT NULL,
    	PRIMARY KEY(id)
    );
    
    CREATE TABLE IF NOT EXISTS book(
    	bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    	card INT(10) UNSIGNED NOT NULL,
    	PRIMARY KEY(bookid)
    );
    
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
    
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));

*  class 表中的测试数据

mysql> select * from class;
    +----+------+
    | id | card |
    +----+------+
    |  1 |   12 |
    |  2 |   13 |
    |  3 |   12 |
    |  4 |   17 |
    |  5 |   11 |
    |  6 |    3 |
    |  7 |    1 |
    |  8 |   16 |
    |  9 |   17 |
    | 10 |   16 |
    | 11 |    9 |
    | 12 |   17 |
    | 13 |   18 |
    | 14 |   16 |
    | 15 |    7 |
    | 16 |    8 |
    | 17 |   19 |
    | 18 |    9 |
    | 19 |    6 |
    | 20 |    5 |
    | 21 |    6 |
    +----+------+
    21 rows in set (0.00 sec)

*  book 表中的测试数据

mysql> select * from book;
    +--------+------+
    | bookid | card |
    +--------+------+
    |      1 |   16 |
    |      2 |    1 |
    |      3 |   17 |
    |      4 |    3 |
    |      5 |   20 |
    |      6 |   12 |
    |      7 |   18 |
    |      8 |   13 |
    |      9 |   13 |
    |     10 |    4 |
    |     11 |    1 |
    |     12 |   13 |
    |     13 |   20 |
    |     14 |   20 |
    |     15 |    1 |
    |     16 |    2 |
    |     17 |    9 |
    |     18 |   16 |
    |     19 |   14 |
    |     20 |    2 |
    +--------+------+
    20 rows in set (0.00 sec)

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

**查询案例**

*  实现两表的连接，连接条件是 class.card = book.card

mysql> SELECT * FROM class LEFT JOIN book ON class.card = book.card;
    +----+------+--------+------+
    | id | card | bookid | card |
    +----+------+--------+------+
    |  1 |   12 |      6 |   12 |
    |  2 |   13 |      8 |   13 |
    |  2 |   13 |      9 |   13 |
    |  2 |   13 |     12 |   13 |
    |  3 |   12 |      6 |   12 |
    |  4 |   17 |      3 |   17 |
    |  5 |   11 |   NULL | NULL |
    |  6 |    3 |      4 |    3 |
    |  7 |    1 |      2 |    1 |
    |  7 |    1 |     11 |    1 |
    |  7 |    1 |     15 |    1 |
    |  8 |   16 |      1 |   16 |
    |  8 |   16 |     18 |   16 |
    |  9 |   17 |      3 |   17 |
    | 10 |   16 |      1 |   16 |
    | 10 |   16 |     18 |   16 |
    | 11 |    9 |     17 |    9 |
    | 12 |   17 |      3 |   17 |
    | 13 |   18 |      7 |   18 |
    | 14 |   16 |      1 |   16 |
    | 14 |   16 |     18 |   16 |
    | 15 |    7 |   NULL | NULL |
    | 16 |    8 |   NULL | NULL |
    | 17 |   19 |   NULL | NULL |
    | 18 |    9 |     17 |    9 |
    | 19 |    6 |   NULL | NULL |
    | 20 |    5 |   NULL | NULL |
    | 21 |    6 |   NULL | NULL |
    +----+------+--------+------+
    28 rows in set (0.00 sec)

*  使用 explain 分析 SQL 语句的性能，可以看到：驱动表是左表 class 表

*  结论：
    
     *  **type 有 All ，rows 为表中数据总行数，说明 class 和 book 进行了全表检索**
     *  即每次 class 表对 book 表进行左外连接时，都需要在 book 表中进行一次全表检索

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

**添加索引：在右表添加索引**

*  添加索引的 SQL 指令

ALTER TABLE 'book' ADD INDEX Y ('card');

*  在 book 的 card 字段上添加索引

*  测试结果：可以看到第二行的type变为了ref，rows也变成了优化比较明显。

*  分析：
    
     *  这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引。
     *  **左表连接右表，则需要拿着左表的数据去右表里面查，索引需要在右表中建立索引**

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

**添加索引：在右表添加索引**

*  删除之前 book 表中的索引

DROP INDEX Y ON book;

*  在 class 表的 card 字段上建立索引

ALTER TABLE class ADD INDEX X(card);

*  再次执行左连接，凉凉~~~

*  别怕，我们来执行右连接：可以看到第二行的type变为了ref，rows也变成了优化比较明显。

*  分析：
    
     *  这是因为RIGHT JOIN条件用于确定如何从左表搜索行，右边一定都有，所以左边是我们的关键点，一定需要建立索引。
     *  class RIGHT JOIN book ：book 里面的数据一定存在于结果集中，我们需要拿着 book 表中的数据，去 class 表中搜索，所以索引需要建立在 class 表中
 *  为了不影响之后的测试，删除该表的 idx\_article\_ccv 索引

### 5.3、三表索引优化 ###

> **三表索引优化分析**

**创建表**

*  建表 SQL

CREATE TABLE IF NOT EXISTS phone(
    	phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    	card INT(10) UNSIGNED NOT NULL,
    	PRIMARY KEY(phoneid)
    )ENGINE=INNODB;
    
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));

*  phone 表中的测试数据

mysql> select * from phone;
    +---------+------+
    | phoneid | card |
    +---------+------+
    |       1 |    7 |
    |       2 |    7 |
    |       3 |   13 |
    |       4 |    6 |
    |       5 |    8 |
    |       6 |    4 |
    |       7 |   16 |
    |       8 |    4 |
    |       9 |   15 |
    |      10 |    1 |
    |      11 |   20 |
    |      12 |   18 |
    |      13 |    9 |
    |      14 |    9 |
    |      15 |   20 |
    |      16 |   11 |
    |      17 |   15 |
    |      18 |    3 |
    |      19 |    8 |
    |      20 |   10 |
    +---------+------+
    20 rows in set (0.00 sec)

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

**查询案例**

*  实现三表的连接查询：

mysql> SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
    +----+------+--------+------+---------+------+
    | id | card | bookid | card | phoneid | card |
    +----+------+--------+------+---------+------+
    |  2 |   13 |      8 |   13 |       3 |   13 |
    |  2 |   13 |      9 |   13 |       3 |   13 |
    |  2 |   13 |     12 |   13 |       3 |   13 |
    |  8 |   16 |      1 |   16 |       7 |   16 |
    | 10 |   16 |      1 |   16 |       7 |   16 |
    | 14 |   16 |      1 |   16 |       7 |   16 |
    |  8 |   16 |     18 |   16 |       7 |   16 |
    | 10 |   16 |     18 |   16 |       7 |   16 |
    | 14 |   16 |     18 |   16 |       7 |   16 |
    |  7 |    1 |      2 |    1 |      10 |    1 |
    |  7 |    1 |     11 |    1 |      10 |    1 |
    |  7 |    1 |     15 |    1 |      10 |    1 |
    | 13 |   18 |      7 |   18 |      12 |   18 |
    | 11 |    9 |     17 |    9 |      13 |    9 |
    | 18 |    9 |     17 |    9 |      13 |    9 |
    | 11 |    9 |     17 |    9 |      14 |    9 |
    | 18 |    9 |     17 |    9 |      14 |    9 |
    |  6 |    3 |      4 |    3 |      18 |    3 |
    |  4 |   17 |      3 |   17 |    NULL | NULL |
    |  9 |   17 |      3 |   17 |    NULL | NULL |
    | 12 |   17 |      3 |   17 |    NULL | NULL |
    |  1 |   12 |      6 |   12 |    NULL | NULL |
    |  3 |   12 |      6 |   12 |    NULL | NULL |
    |  5 |   11 |   NULL | NULL |    NULL | NULL |
    | 15 |    7 |   NULL | NULL |    NULL | NULL |
    | 16 |    8 |   NULL | NULL |    NULL | NULL |
    | 17 |   19 |   NULL | NULL |    NULL | NULL |
    | 19 |    6 |   NULL | NULL |    NULL | NULL |
    | 20 |    5 |   NULL | NULL |    NULL | NULL |
    | 21 |    6 |   NULL | NULL |    NULL | NULL |
    +----+------+--------+------+---------+------+
    30 rows in set (0.00 sec)

*  使用 explain 分析 SQL 指令：

*  结论：
    
     *  **type 有All ，rows 为表数据总行数，说明 class、 book 和 phone 表都进行了全表检索**
     *  Extra 中 Using join buffer ，表明连接过程中使用了 join 缓冲区

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

**创建索引**

*  创建索引的 SQL 语句

ALTER TABLE book ADD INDEX Y (card);
    ALTER TABLE phone ADD INDEX Z (card);

*  **进行 LEFT JOIN ，永远都在右表的字段上建立索引**

*  执行查询：后2行的type都是ref，且总rows优化很好，效果不错。因此索引最好设置在需要经常查询的字段中。

mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card = phone.card;
    +----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref             | rows | Extra       |
    +----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
    |  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL            |   21 | NULL        |
    |  1 | SIMPLE      | book  | ref  | Y             | Y    | 4       | db01.class.card |    1 | Using index |
    |  1 | SIMPLE      | phone | ref  | Z             | Z    | 4       | db01.book.card  |    1 | Using index |
    +----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
    3 rows in set (0.00 sec)

> **Join 语句优化的结论**

**将 left join 看作是两层嵌套 for 循环**

1.  尽可能减少Join语句中的NestedLoop的循环总次数；
2.  **永远用小结果集驱动大的结果集（在大结果集中建立索引，在小结果集中遍历全表）；**
3.  优先优化NestedLoop的内层循环；
4.  保证Join语句中被驱动表上Join条件字段已经被索引；
5.  当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置；

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

**我的理解**

1.  使用小表驱动大表，这就相当于外层 for 循环的次数少，内层 for 循环的次数多
2.  然后我们在大表中建立了索引，这样内层 for 循环的效率明显提高
3.  综上，使用小表驱动大表，在大表中建立了索引

## 6、索引失效 ##

> **索引失效（应该避免）**

**创建表**

*  建表 SQL

CREATE TABLE staffs(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
    	`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
    	`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
    	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
    )CHARSET utf8 COMMENT'员工记录表';
    
    INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
    INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
    INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
    
    ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);

*  staffs 表中的测试数据

mysql> select * from staffs;
    +----+------+-----+---------+---------------------+
    | id | name | age | pos     | add_time            |
    +----+------+-----+---------+---------------------+
    |  1 | z3   |  22 | manager | 2020-08-04 14:42:33 |
    |  2 | July |  23 | dev     | 2020-08-04 14:42:33 |
    |  3 | 2000 |  23 | dev     | 2020-08-04 14:42:33 |
    +----+------+-----+---------+---------------------+
    3 rows in set (0.00 sec)

*  staffs 表中的复合索引：name、age、pos

### 6.1、索引失效准则 ###

> **索引失效判断准则**

1.  全值匹配我最爱
2.  最佳左前缀法则：如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
3.  不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
4.  存储引擎不能使用索引中范围条件右边的列
5.  尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少`select *`
6.  mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
7.  `is null`，`is not null` 也无法使用索引（早期版本不能走索引，后续版本应该优化过，可以走索引）
8.  like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描操作
9.  字符串不加单引号索引失效
10. 少用or，用它连接时会索引失效

> **最佳左匹配法则：带头大哥不能死，中间兄弟不能断**

*  只有带头大哥 name 时
    
     *  key = index\_staffs\_nameAgePos 表明索引生效
     *  ref = const ：这个常量就是查询时的 ‘July’ 字符串常量

*  带头大哥 name 带上小弟 age
    
     *  key = index\_staffs\_nameAgePos 表明索引生效
     *  ref = const,const：两个常量分别为 ‘July’ 和 23

*  带头大哥 name 带上小弟 age ，小弟 age 带上小小弟 pos
    
     *  key = index\_staffs\_nameAgePos 表明索引生效
     *  ref = const,const,const ：三个常量分别为 ‘July’、23 和 ‘dev’

*  带头大哥 name 挂了
    
     *  key = NULL 说明索引失效
     *  ref = null 表示 ref 也失效

*  带头大哥 name 没挂，小弟 age 跑了
    
     *  key = index\_staffs\_nameAgePos 说明索引没有失效
     *  ref = const 表明只使用了一个常量，即第二个常量（pos = ‘dev’）没有生效

> **在索引列上进行计算，会导致索引失效，进而转向全表扫描**

*  不对带头大哥 name 进行任何操作：key = index\_staffs\_nameAgePos 表明索引生效

*  对带头大哥 name 进行操作：使用 LEFT 函数截取子串
    
     *  key = NULL 表明索引生效
     *  type = ALL 表明进行了全表扫描

> **范围之后全失效**

*  精确匹配
    
     *  type = ref 表示非唯一索引扫描，SQL 语句将返回匹配某个单独值的所有行。
     *  key\_len = 140 表明表示索引中使用的字节数

*  将 age 改为范围匹配
    
     *  type = range 表示范围扫描
     *  key = index\_staffs\_nameAgePos 表示索引并没有失效
     *  key\_len = 78 ，ref = NULL 均表明范围搜索使其后面的索引均失效

> **尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少 `select *`**

*  `SELECT *` 的写法

*  覆盖索引的写法：Extra = Using where; Using index ，Using index 表示使用索引列进行查询，将大大提高查询的效率

*  覆盖索引中包含 range 条件：type = ref 并且 Extra = Using where; Using index ，虽然在查询条件中使用了 范围搜索，但是由于我们只需要查找索引列，所以无需进行全表扫描

> **mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描**

*  在使用 != 会 <> 时会导致索引失效：
    
     *  key = null 表示索引失效
     *  rows = 3 表示进行了全表扫描

> **is null，is not null 也无法使用索引**

*  is null，is not null 会导致索引失效：key = null 表示索引失效

> **like % 写最右**

*  staffs 表的索引关系

*  like % 写在左边的情况
    
     *  type = All ，rows = 3 表示进行了全表扫描
     *  key = null 表示索引失效

*  like % 写在右边的情况：key = index\_staffs\_nameAgePos 表示索引未失效

> **解决【like ‘%str%’ 】索引失效的问题：覆盖索引**

**创建表**

*  建表 SQL

CREATE TABLE `tbl_user`(
    	`id` INT(11) NOT NULL AUTO_INCREMENT,
    	`name` VARCHAR(20) DEFAULT NULL,
    	`age`INT(11) DEFAULT NULL,
    	`email` VARCHAR(20) DEFAULT NULL,
    	PRIMARY KEY(`id`)
    )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    
    INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
    INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');
    INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
    INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');

*  tbl\_user 表中的测试数据

mysql> select * from tbl_user;
    +----+------+------+-----------+
    | id | name | age  | email     |
    +----+------+------+-----------+
    |  1 | 1aa1 |   21 | a@163.com |
    |  2 | 2bb2 |   23 | b@163.com |
    |  3 | 3cc3 |   24 | c@163.com |
    |  4 | 4dd4 |   26 | d@163.com |
    +----+------+------+-----------+
    4 rows in set (0.00 sec)

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

创建索引

*  创建索引的 SQL 指令

CREATE INDEX idx_user_nameAge ON tbl_user(name, age);

*  在 tbl\_user 表的 name 字段和 age 字段创建联合索引

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

**测试覆盖索引**

*  如下 SQL 的索引均不会失效：
    
     *  只要查询的字段能和覆盖索引扯得上关系，并且没有多余字段，覆盖索引就不会失效
     *  但我就想不通了，id 扯得上啥关系。。。

EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE '%aa%';
    
    EXPLAIN SELECT name FROM tbl_user WHERE NAME LIKE '%aa%';
    EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';
    
    EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
    EXPLAIN SELECT id, name FROM tbl_user WHERE NAME LIKE '%aa%';
    EXPLAIN SELECT id, age FROM tbl_user WHERE NAME LIKE '%aa%';
    EXPLAIN SELECT id, name, age FROM tbl_user WHERE NAME LIKE '%aa%';

*  如下 SQL 的索引均会失效：但凡有多余字段，覆盖索引就会失效

EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
    EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE NAME LIKE '%aa%';

> **字符串不加单引号索引失效**

*  正常操作，索引没有失效

*  如果字符串忘记写 ‘’ ，那么 mysql 会为我们进行隐式的类型转换，但凡进行了类型转换，索引都会失效

> **少用or，用它连接时会索引失效**

*  使用 or 连接，会导致索引失效

### 6.2、索引优化面试题 ###

> **索引优化面试题**

**创建表**

*  建表 SQL

create table test03(
        id int primary key not null auto_increment,
        c1 char(10),
        c2 char(10),
        c3 char(10),
        c4 char(10),
        c5 char(10)
    );
    
    insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
    insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
    insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
    insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
    insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');
    
    create index idx_test03_c1234 on test03(c1,c2,c3,c4);

*  test03 表中的测试数据

mysql> select * from test03;
    +----+------+------+------+------+------+
    | id | c1   | c2   | c3   | c4   | c5   |
    +----+------+------+------+------+------+
    |  1 | a1   | a2   | a3   | a4   | a5   |
    |  2 | b1   | b2   | b3   | b4   | b5   |
    |  3 | c1   | c2   | c3   | c4   | c5   |
    |  4 | d1   | d2   | d3   | d4   | d5   |
    |  5 | e1   | e2   | e3   | e4   | e5   |
    +----+------+------+------+------+------+
    5 rows in set (0.00 sec)

*  test03 表中的索引

mysql> SHOW INDEX FROM test03;
    +--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table  | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | test03 |          0 | PRIMARY          |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
    | test03 |          1 | idx_test03_c1234 |            1 | c1          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
    | test03 |          1 | idx_test03_c1234 |            2 | c2          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
    | test03 |          1 | idx_test03_c1234 |            3 | c3          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
    | test03 |          1 | idx_test03_c1234 |            4 | c4          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
    +--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    5 rows in set (0.00 sec)

> **问题：我们创建了复合索引idx\_test03\_c1234，根据以下SQL分析下索引使用情况？**

*  `EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4';`
 *  即全值匹配

*  `EXPLAIN SELECT * FROM test03 WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1';`
 *  mysql 优化器进行了优化，所以我们的索引都生效了

*  `EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4';`
 *  c3 列使用了索引进行排序，并没有进行查找，导致 c4 无法用索引进行查找

*  `EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4>'a4' AND c3='a3';`
 *  mysql 优化器进行了优化，所以我们的索引都生效了，在 c4 时进行了范围搜索

*  `EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3;`
 *  c3 列将索引用于排序，而不是查找，c4 列没有用到索引

*  `EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c3;`
 *  那不就和上面一样的嘛~~~，c4 列都没有用到索引

*  `EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c4;`
 *  妈耶，因为索引建立的顺序和使用的顺序不一致，导致 mysql 动用了文件排序
 *  看到 Using filesort 就要知道：此句 SQL 必须优化

*  `EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c2, c3;`
 *  只用 c1 一个字段索引，但是c2、c3用于排序，无filesort
 *  难道因为排序的时候，c2 紧跟在 c1 之后，所以就不用 filesort 吗？

*  `EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c3, c2;`
 *  出现了filesort，我们建的索引是1234，它没有按照顺序来，32颠倒了

*  `EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c2, c3;`
 *  用c1、c2两个字段索引，但是c2、c3用于排序，无filesort

*  `EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2, c3;`
 *  和 c5 这个坑爹货没啥关系

*  `EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c3, c2;`
 *  注意查询条件 c2=‘a2’ ，我都把 c2 查出来了（c2 为常量），我还给它排序作甚，所以没有产生 filesort

*  `EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c2, c3;`
 *  顺序为 1 2 3 ，没有产生文件排序

*  `EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c3, c2;`
 *  group by 表面上叫分组，分组之前必排序，group by 和 order by 在索引上的问题基本是一样的
 *  Using temporary; Using filesort 两个都有，我只能说是灭绝师太

*  结论：
    
     *  group by 基本上都需要进行排序，但凡使用不当，会有临时表产生
     *  定值为常量、范围之后失效，最终看排序的顺序

### 6.3、索引失效总结 ###

> **一般性建议**

1.  对于单键索引，尽量选择针对当前query过滤性更好的索引
2.  在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠左越好。
3.  在选择组合索引的时候，尽量选择可以能包含当前query中的where子句中更多字段的索引
4.  尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

> **索引优化的总结**

*  like 后面以常量开头，比如 like ‘kk%’ 和 like ‘k%kk%’ ，可以理解为就是常量

![image-20200804162716329][]

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

**like SQL 实测**

*  = ‘kk’ ：key\_len = 93 ，请记住此参数的值，后面有用

*  like ‘kk%’：
    
     *  key\_len = 93 ，和上面一样，说明 c1 c2 c3 都用到了索引
     *  type = range 表明这是一个范围搜索

*  like ‘%kk’ 和 like ‘%kk%’ ：key\_len = 31 ，表示只有 c1 用到了索引

*  like ‘k%kk%’ ：key\_len = 93 ，表示 c1 c2 c3 都用到了索引

> **索引优化的总结**

全值匹配我最爱， 最左前缀要遵守；

带头大哥不能死， 中间兄弟不能断；

索引列上少计算， 范围之后全失效；

LIKE 百分写最右， 覆盖索引不写 \*；

不等空值还有 OR， 索引影响要注意；

VAR 引号不可丢， SQL 优化有诀窍。

[image-20200803212731183]: /images/20221124/6fa2321326784e6796afd5fbc8dc8772.png
[image-20200803212826429]: /images/20221124/0ae53d110b72484badbc6e6ea56dce70.png
[image-20200803212936111]: /images/20221124/ed2be09970b14029b99fa032a453e681.png
[image-20200803213106434]: /images/20221124/ada4d71edb92450ca02ba05489d46c50.png
[image-20200803222848380]: /images/20221124/6e57f00b7d704fb2aad45ddcdb5a518d.png
[image-20200804093236612]: /images/20221124/ebcd8f426bed483fa419bf3ac8426f1b.png
[image-20200811145838930]: /images/20221124/3746cbcdf6bc471d95ae832d3327df2f.png
[image-20200804101016101]: /images/20221124/e772eb3a9fe242e5a39209b968617fff.png
[image-20200804101005684]: /images/20221124/8af20ff847574795be22b2e5e9c7c832.png
[image-20200804101502048]: /images/20221124/745aa39dc452472ba32cd48fb21bf1ef.png
[image-20200804103028065]: /images/20221124/8cfe2b4654264c668117f6140e61466e.png
[image-20200804103543783]: /images/20221124/038796aa1d1d4071be57a6705fabc26d.png
[image-20200804103959011]: /images/20221124/8960f02bc49544759eac597f4ffca969.png
[image-20200804104130086]: /images/20221124/89188faeb7e449298739c3ccd39f0709.png
[image-20200804104254208]: /images/20221124/27205b833aae407e8efef379a77952f4.png
[image-20200804104358142]: /images/20221124/683b479d2aa84860a578b82cee1ba20f.png
[image-20200804105225100]: /images/20221124/4ce8717891134cf4be41c9c8116d3f8d.png
[image-20200804105833936]: /images/20221124/7498d48a878444acadd4c158f6d21b9f.png
[image-20200804110346982]: /images/20221124/d94b6f3779cb4afaa4b930a9edcfcc6a.png
[image-20200804111249713]: /images/20221124/9eb48258ad0f4837bc1c51349f5992bd.png
[image-20200804111738931]: /images/20221124/63485836f9fd48819985988800a90093.png
[image-20200804112558915]: /images/20221124/ce918abd26f74ded94ed182ce86bae71.png
[image-20200804113450147]: /images/20221124/ee057e91b87a4b3aad95e0d4fc301d75.png
[image-20200804113946144]: /images/20221124/9663774fd4294158a541bae605ecffe4.png
[image-20200804114203012]: /images/20221124/9a2ae85cd13846a28e599f17e06a86a0.png
[image-20200804162716329]: /images/20221124/966d2c48c34340dca40082881d67cb7d.png