mysql根类别子类别包含关系_[笔记]从根儿上理解 MySQL

最近在跟着 [掘金小册：MySQL 是怎样运行的：从根儿上理解 MySQL](https://juejin.im/book/5bffcbc9f265da614b11b731) 重温Mysql，写的很好，再次结合自己理解，做下笔记，希望没有侵权。

# 1.启动选项

`defaults-extra-file`和`defaults-file`的区别：使用`defaults-extra-file`可以指定额外的配置文件搜索路径(也就是说那些固定的配置文件路径也会被搜索)。而`defaults-file`代表只在指定的路径下搜索。

# 2.设置系统变量

```bash

SET [GLOBAL|SESSION] 系统变量名 = 值;

<=等价于=>

SET [@@(GLOBAL|SESSION).]var_name = XXX;

# 例如：

SET SESSION default_storage_engine = MyISAM;

SET @@SESSION.default_storage_engine = MyISAM;

SET default_storage_engine = MyISAM;

# 设置系统变量时，默认的作用范围就是 SESSION。

SET 系统变量名 = 值

<=等价于=>

SET SESSION 系统变量名 = 值

# 同理，SHOW VARIABLES 语句默认查看的系统变量的作用范围也是 SESSION。例如：

SHOW SESSION VARIABLES LIKE 'default_storage_engine';

```

> 如果某个客户端改变了某个系统变量在`GLOBAL`作用范围的值，并不会影响该系统变量在当前已经连接的客户端作用范围为`SESSION`的值，只会影响后续连入的客户端在作用范围为`SESSION`的值。

特殊的：

- 有的变量只有SESSION 作用域，比如 insert_id。

- 有的变量只有GLOBAL 作用域，比如 max_connections。

- 还有的系统变量是只读的，并不能设置值，比如 version。

- 状态变量：用来显示mysql运行的状况，只能由mysql服务自己来设置。

# 3.字符集

- utf8mb3：阉割过的utf8字符集，只使用1～3个字节表示字符。(MySql中 utf8 是 utf8mb3 的别名)

- utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符。

- 如果创建或修改**列**时没有显式的指定字符集和比较规则，则该**列**默认用**表**的字符集和比较规则

- 如果创建或修改**表**时没有显式的指定字符集和比较规则，则该**表**默认用**库**的字符集和比较规则

- 如果创建或修改**库**时没有显式的指定字符集和比较规则，则该**库**默认用**服务器**的字符集和比较规则

字符集和比较规则是互相有联系的：

- 只修改字符集，则比较规则将变为修改后的字符集默认的比较规则。

- 只修改比较规则，则字符集将变为修改后的比较规则对应的字符集。

> 在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误。

比方说原先列使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为ascii的话就会出错，因为ascii字符集并不能表示汉字字符。

从客户端发送sql请求到mysql服务返回结果，这个过程中伴随着多次字符集的转换。

> 我们通常都把 ``character_set_client``(服务器解码请求时使用的字符集) 、``character_set_connection``(服务器处理请求时会把请求字符串从character_set_client转为character_set_connection)、``character_set_results``(服务器向客户端返回数据时使用的字符集)这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换。

MySQL提供了一条非常简便的语句:

```bash

SET NAMES 字符集名;

<=等价于=>

SET character_set_client = 字符集名;

SET character_set_connection = 字符集名;

SET character_set_results = 字符集名;

```

或者在配置文件中指定：

```bash

[client]

default-character-set=utf8

```

> 如果你在使用 MySQL 或 MariaDB，不要使用用“utf8”编码，改用“``utf8mb4``”。这里([https://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4](https://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4))提供了一个指南用于将现有数据库的字符编码从“utf8”转成“utf8mb4”。

# 4.InnoDB 记录结构

InnoDB表对主键的生成策略：

优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。

## VARCHAR(M) 最多能存储的数据

VARCHAR(M)类型的列最多可以占用``65535``个字节，MySQL对一条记录占用的最大存储空间是有限制的，除了``BLOB``或者``TEXT``类型的列之外，其他所有的列(不包括隐藏列和记录头信息)占用的字节长度加起来不能超过``65535``个字节。

这个``65535``个字节除了列本身的数据之外，还包括一些其他的数据(storage overhead)，比如说我们为了存储一个``VARCHAR(M)``类型的列，其实需要占用 3 部分存储空间：

- 真实数据

- 真实数据占用字节的长度

- NULL值标识，如果该列有``NOT NULL``属性则可以没有这部分存储空间

> 如果该VARCHAR类型的列**没有**``NOT NULL``属性，那最多只能存储**``65532``**个字节的数据，因为真实数据的长度可能占用2个字节，NULL值标识需要占用1个字节；如果VARCHAR类型的列有``NOT NULL``属性，那最多只能存储**``65533``**个字节的数据，因为真实数据的长度可能占用2个字节，不需要NULL值标识。

## VARCHAR(M)中M最大取值

如果``VARCHAR(M)``类型的列使用的不是 ASCII 字符集(一个字符就代表一个字节)，那``M``的最大取值取决于该字符集表示一个字符最多需要的字节数。在列的值允许为NULL的情况下：

- gbk字符集表示一个字符最多需要2个字节

那在该字符集下，M的最大取值就是 32766(也就是：65532/2)，也就是说最多能存储32766个字符；

- utf8字符集表示一个字符最多需要3个字节

那在该字符集下，M的最大取值就是 21844(也就是：65532/3)，就是说最多能存储21844个字符。

> 上述所言在列的值允许为NULL的情况下，gbk字符集下M的最大取值就是``32766``，utf8字符集下M的最大取值就是``21844``，这都是在表中**只有一个字段的情况**下说的，一定要记住一个行中的所有列(不包括隐藏列和记录头信息)占用的字节长度**加起来不能超过65535个字节**！

## 行溢出数据

**对于Compact和Reduntant行格式来说**，如果某一列中的数据非常多的话，在本记录的真实数据处只会存储该列的前**768**个字节的数据和一个指向其他页的地址(MySQL是以页为基本单位来管理存储空间的，记录会被分配到某个页中存储，而InnoDB一个页的大小一般是 16KB，也就是16384字节)，然后把剩下的数据存放到其他页中，这个过程也叫做==**行溢出**==，存储超出768字节的那些页面也被称为**溢出页**。

> 不只是 VARCHAR(M) 类型的列，其他的 TEXT、BLOB 类型的列在存储数据非常多的时候也会发生行溢出。

## 行溢出的临界点

MySQL中规定==一个页中至少存放两行记录==，至于为什么这么规定我们之后再说，现在看一下这个规定造成的影响。

先分析一下页中的空间都是如何利用的：

- 每个页除了存放我们的记录以外，也需要存储一些额外的信息。

乱七八糟的额外信息加起来需要136个字节的空间(现在只要知道这个数字就好了)，其他的空间都可以被用来存储记录。

- 每个记录需要的额外信息是27字节。

这27个字节包括下边这些部分：

- 2个字节用于存储真实数据的长度

- 1个字节用于存储列是否是NULL值

- 5个字节大小的头信息

- 6个字节的row_id列

- 6个字节的transaction_id列

- 7个字节的roll_pointer列

其实重点就：不用关注这个临界点是什么，只要知道如果我们向一个行中存储了很大的数据时，可能发生行溢出的现象。

## Dynamic和Compressed行格式

MySQL 5.7 默认的行格式就是``Dynamic``，这俩行格式和Compact行格式挺像，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储字段真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。

``Compressed``行格式和Dynamic不同的一点是，Compressed行格式会采用**压缩算法**对页面进行压缩，以节省空间。

# 5.InnoDB 数据页(索引页)结构

## 记录头信息的秘密

``delete_mask`` 属性:

这个属性标记着当前记录是否被删除，占用``1``个二进制位，值为``0``的时候代表记录并没有被删除，为1的时候代表记录被删除掉了。

``next_record`` 属性：

不论我们怎么对页中的记录做增删改操作，InnoDB 始终会维护一条记录的单链表，链表中的各个节点是按照主键值由小到大的顺序连接起来的(通过``next_record``指向)。

> InnoDB 会自动给每个页里边加两记录，由于这两个记录并不是我们自己插入的，所以有时候也称为伪记录或者虚拟记录。这两个伪记录一个代表最小记录(``Infimum记录``)，一个代表最大记录(``Supremum记录``)。最小记录的下一条记录就本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是最大记录。

``next_record``属性会指向下一条记录的 记录头信息和真实数据之间的位置(这个位置刚刚好，向左读取就是记录头信息[逆序存储]，向右读取就是真实数据)。

InnoDB并不会因为新记录的插入而为它申请新的存储空间，而是直接复用了原来被删除记录的存储空间(如果有的话)。

> 当数据页中存在多条被删除掉的记录时，这些记录的``next_record``属性将会把这些被删除掉的记录组成一个``垃圾链表``，以备之后重用这部分存储空间。

各个数据页可以组成一个双向链表。

# 6.B+树索引

``B+树``的叶子节点存储的是完整的用户记录(存储了所有列的值(包括隐藏列))。

> B树和B+树的区别：B+树的非叶子结点只包含导航信息，不包含实际的值，所有的叶子结点和相连的节点使用链表相连，便于区间查找和遍历。

> 优点：

>

> - 由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。

数据存放的更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。

> - B+树的叶子结点都是相连的，因此对整棵树的遍历只需要一次线性遍历叶子结点即可。

而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把它们存放到B+树这个数据结构中了，所以我们也称这些数据页为``节点``。我们的==实际用户记录其实都存放在B+树的最底层的节点上==，这些节点也被称为``叶子节点``或``叶节点``，其余用来存放``目录项``的节点称为``非叶子节点``或者``内节点``(目录项记录着``主键+页号``的搭配)，其中``B+树``最上边的那个节点也称为``根节点``。

> 一般情况下，我们用到的B+树都不会超过``4``层。

## 聚簇索引

- 1.使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

- 页内的记录是按照主键的大小顺序排成一个单向链表。

- 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。

- 存放``目录项记录``的页分为``不同的层次``，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

- 2.B+树的叶子节点存储的是完整的用户记录。

具有这两种特性的B+树称为``聚簇索引``，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。InnoDB存储引擎会自动的为我们创建聚簇索引。

> 另外，在InnoDB存储引擎中，聚簇索引就是数据的存储方式(所有的用户记录都存储在了叶子节点)，也就是所谓的==索引即数据，数据即索引==。

## 二级索引

二级索引的B+树只能确定我们要查找记录的主键值，所以如果我们想根据**索引列**的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程也被称为``回表``(如果像主键一样设计会太占用空间)。也就是根据**索引列**的值查询一条完整的用户记录需要使用到``2棵B+树``！！！

> 二级索引的B+树的叶子节点存储的并不是完整的用户记录，而只是``索引列+主键``这两个列的值。目录项记录中不再是``主键+页号``的搭配，而变成了``索引列+页号``的搭配。

这种按照**索引列**建立的B+树需要一次``回表``操作才可以定位到完整的用户记录，所以这种B+树也被称为``二级索引``(英文名secondary index)，或者``辅助索引``。

## 联合索引

此外，还可以对多个列建立``联合索引``(1棵B+树)。

## InnoDB的B+树索引的注意事项

- 根页面万年不动窝

> ==一个B+树索引的根节点自诞生之日起，便不会再移动==。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

- 内节点中目录项记录的唯一性

- 一个页面最少存储2条记录

## MyISAM中的索引方案

MyISAM的索引方案虽然也使用树形结构，但是却将索引和数据分开存储：

- 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为``数据文件``(不划分数据页)。

- 把索引信息另外存储到另一个称为``索引文件``的文件中。

- MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是``主键值 + 行号``的组合。

也就是先通过索引找到对应的行号，再通过行号去找对应的记录！

这一点和InnoDB是完全不相同的，在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次``回表``操作，意味着MyISAM中建立的索引**相当于全部都是二级索引**！

- 也可以对其它的列分别建立索引或者建立联合索引，原理和InnoDB中的索引差不多，不过在叶子节点处存储的是相应的``列 + 行号``。这些索引也全部都是二级索引。

> 也就是所谓的：==索引是索引、数据是数据==。

InnoDB和MyISAM会自动为``主键``或者声明为``UNIQUE``的列去建立B+树索引，但是如果我们想为其他的列建立索引就需要我们显式的去指明(如果自动为每个列都建立索引，会很费性能和存储空间)。

# 7.B+树索引的使用

> B+树索引适用于下边这些情况：

>

- 全值匹配

- 匹配左边的列

- 匹配范围值

- 精确匹配某一列并范围匹配另外一列

- 用于排序

- 用于分组

## 索引的代价

- 空间上的代价

这个是显而易见的，每建立一个索引都为要它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树会由许多数据页组成。。

- 时间上的代价

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录(也就是不论是用户记录还是目录项记录)都是按照索引列的值从小到大的顺序而形成了一个单向链表。

而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收啥的操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作。

> 所以说，一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。

阿里巴巴 Java 开发手册中，建议单张表索引不超过``5个``！

## 联合索引查询注意事项

- 如果我们想使用``联合索引``中尽可能多的列，搜索条件中的各个列必须是联合索引中``从最左边连续``的列。

- 在使用``联合索引``进行范围查找的时候需要注意，如果对多个列同时进行范围查找的话，只有对索引``最左边``的那个列进行``范围查找``的时候才能用到B+树索引。

- 对于同一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找。

## 联合索引排序注意事项

> 在MySQL中，把在内存中或者磁盘上进行排序的方式统称为文件排序(``filesort``)，一般就比较慢了，但是如果 ORDER BY 子句里使用到了我们的索引列，就有可能省去在内存或文件中排序的步骤。

- ORDER BY 的子句后边的列的顺序也必须按照索引列的顺序给出。

- 使用联合索引的各个排序列的排序顺序必须是一致的(不能ASC、DESC混用)。

- 匹配索引左边的列的形式可以使用部分的B+树索引。当联合索引左边列的值为常量，也可以使用后边的列进行排序。

## 无法使用索引进行排序的几种情况

- ASC、DESC混用

- WHERE子句中出现``非排序``使用到的索引列

- 排序列包含非同一个(联合)索引的列

- 排序列使用了复杂的表达式

## 用于分组

和使用B+树索引进行排序是一个道理，分组列的顺序也需要和索引列的**顺序一致**，也可以只使用索引列中左边的列进行分组。

## 回表的代价

==查询时需要回表的记录越多，使用二级索引的性能就越低==，甚至让某些查询宁愿使用全表扫描也不使用``二级索引``。

> ``回表``操作其实是一个随机IO，比较耗时！

查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用``二级索引 + 回表``的方式。

当然优化器做的分析工作不仅仅是这么简单，但是大致上是个这个过程。一般情况下，限制查询获取较少的记录数会让优化器更倾向于选择使用``二级索引 + 回表``的方式进行查询，因为回表的记录越少，性能提升就越高。

## 覆盖索引

只需要用到索引的查询方式可以省去``回表``操作带来的性能损耗，是为**索引覆盖**。

## 如何挑选索引

- 1.只为用于搜索、排序或分组的列创建索引

- 2.考虑列的基数(可选择性): 最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

> ``列的基数``指的是某一列中不重复数据的个数。在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基数越小，该列中的值越集中。

- 3.索引列的类型尽量小(该类型表示的数据范围的大小)

- 数据类型越小，在查询时进行的比较操作越快

- 数据类型越小，索引占用的存储空间就越少。

在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

> 这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

- 4.索引字符串值的前缀 (字符串类型前缀索引)

> 索引列前缀对排序的影响：

因为二级(前缀)索引中不包含完整的索引列信息，所以无法对前N个字符相同，后边的字符不同的记录进行排序，也就是使用索引列前缀的方式无法支持使用索引排序，只能使用文件排序。

- 5.让索引列在比较表达式中单独出现：

如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的。

- 6.主键插入顺序

如果想尽量避免(页面分裂和记录移位)这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。

- 7.冗余和重复索引

维护冗余/重复索引索引只会增加额外的维护成本，并不会对搜索有什么好处，应该避免。

- 8.尽量使用覆盖索引进行查询，避免回表带来的性能损耗。

# 8.MySQL 的数据目录

## 表在文件系统中的表示

InnoDB和MyISAM这两种存储引擎都在数据目录下对应的**数据库子目录**下创建了一个专门用于描述表结构的文件，文件名形如：``表名.frm``。

## InnoDB是如何存储表数据的

**系统表空间(system tablespace)：**

可以对应文件系统上一个或多个实际的文件，默认情况下，InnoDB会在``数据目录``下创建一个名为``ibdata1``，这个文件就是对应的系统表空间在文件系统上的表示。而且这个文件是所谓的自扩展文件，也就是当不够用的时候它会自动扩展文件大小。

> 需要注意的一点是，在一个MySQL服务器中，系统表空间只有一份。从``MySQL5.5.7到MySQL5.6.6``之间的各个版本中，我们表中的数据都会被默认存储到这个**系统表空间**。

**独立表空间(file-per-table tablespace)：**

在``MySQL5.6.6``以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间。

使用独立表空间来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同，只不过添加了一个.ibd的扩展名而已，形如：``表名.ibd``。

## MyISAM是如何存储表数据的

在MyISAM中的索引全部都是二级索引，该存储引擎的数据和索引是分开存放的。所以在文件系统中也是使用不同的文件来存储``数据文件``和``索引文件``。

而且和InnoDB不同的是，MyISAM并没有什么所谓的``表空间``一说，==表数据都存放到对应的数据库子目录下==。

新建一个表会创建三个文件：

```bash

表名.frm # 表结构文件

表名.MYD # 数据文件

表名.MYI # 索引文件

```

## 视图在文件系统中的表示

MySQL 中视图的本质就是查询语句的别名，所以在存储视图的时候是不需要存储真实的数据的，只需要把它的结构存储起来就行了。和表一样，描述视图结构的文件也会被存储到所属数据库对应的子目录下边，只会存储一个``视图名.frm``的文件。

## 文件系统对数据库的影响

- 数据库名称和表名称不得超过文件系统所允许的最大长度

- 特殊字符的问题

> 为了避免因为数据库名和表名出现某些特殊字符而造成文件系统不支持的情况，MySQL会把``数据库名``和``表名``中所有**除数字和拉丁字母以外**的所有字符在文件名里都映射成 ``@+编码值``的形式作为文件名。比如：``ghost@002dblog`` ==> ``ghost@002dblog``

- 数据文件大小受文件系统最大长度(大小)限制

## MySQL系统数据库简介

- ``mysql``

这个数据库是核心，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。

- ``information_schema``

这个数据库保存着MySQL服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引等。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为``元数据``。

- ``performance_schema``

这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，算是对MySQL服务器的一个``性能监控``。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等等信息。

- ``sys``

这个数据库主要是通过``视图``的形式把``information_schema``和``performance_schema``结合起来，让程序员可以更方便的了解MySQL服务器的一些``性能信息``。

# 9.访问方法/访问类型

MySQL 查询的执行方式大致分为下边两种：

- 使用全表扫描进行查询

- 使用索引进行查询

- 1.针对主键或唯一二级索引的等值查询

- 2.针对普通二级索引的等值查询

- 3.针对索引列的范围查询

- 4.直接扫描整个索引

MySQL 执行查询语句的方式又称之为``访问方法``或者``访问类型``。

## const

通过``主键``或者``唯一二级索引列``与常数的等值比较来定位一条记录的访问方法定义为：``const``，意思是常数级别的，代价是可以忽略不计的。

> 如果主键或者``唯一二级索引``是由**多**个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个 const 访问方法才有效(这是因为只有该索引中全部列都采用等值比较才可以定位唯一的一条记录)。

> ``唯一二级索引``列并不限制 NULL 值的数量，所以``唯一二级索引``使用``key IS NULL``时，有可能访问到多条记录。但规定它的访问方法也是 const。

## ref

普通的``二级索引列``与常数进行等值比较来定位一条记录的访问方法定义为：``ref``。

对于普通二级索引而言，通过索引列进行等值比较之后可能匹配到**多条连续**的记录，又由于需要``回表``，所以效率比``const``差一丢丢(二级索引等值比较时匹配的记录数较少时的效率还是很高的)。

特殊情况：

- 二级索引列值为NULL的情况

对于普通``二级索引``使用``key IS NULL``时，使用的方法是``ref``。

- 对于包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用``ref``的访问方法。

> 但是如果最左边的连续索引列并**不全部是等值比较**的话，访问方法就**不能**称为``ref``。

## ref_or_null

对于普通``二级索引``使用``key = 'xxx' or key IS NULL``时，使用的方法是``ref_or_null``。

> 先分别从 key 索引对应的 B+ 树中找出``key IS NULL``和``key1 = 'xxx'``的两个连续的记录范围，然后根据这些二级索引记录中的id(主键)值再``回表``查找完整的记录。

## range

利用``索引``进行范围匹配(匹配某个或某些范围的值)的访问方法称之为：``range``。

> 可以使聚簇索引，也可以是二级索引。

所谓范围，也就是``区间``。索引列等值匹配(比如 IN)的情况称之为``单点区间``，>，>=，

## index

假设有一个联合索引``KEY idx_key_part(key_part1, key_part2, key_part3)``，对于列``key_part2``并不是联合索引``idx_key_part``最左索引列，那么对列``key_part2``的查询就无法使用``ref``或者``range``访问方法来查询。

但是如果满足了下列两个条件：

- 1.查询列表中只出现包含在(联合)索引中的列。

- 2.搜索条件中只出现包含在(联合)索引中的列。

例如：

```sql

SELECT key_part1, key_part2, key_part3 FROM table WHERE key_part2 = 'xxx';

```

> 也就是说我们可以直接通过遍历``idx_key_part``索引的**叶子节点**的记录来比较``key_part2 = 'xxx'``这个条件是否成立，把匹配成功的二级索引记录的``key_part1, key_part2, key_part3``列的值直接加到结果集中就行了。

> 由于二级索引记录比聚簇索记录小的多(聚簇索引记录要存储所有用户定义的列以及所谓的隐藏列，而二级索引记录只需要存放索引列和主键)，而且这个过程也**不用进行回表**操作，所以**直接遍历二级索引**比直接遍历聚簇索引的成本要小很多。

这种采用遍历二级索引记录的执行方式(查询方法)称之为：``index``。

## all

顾名思义，即**全表扫描**，对于 InnoDB 引擎来说也就是直接扫描聚簇索引。这种使用全表扫描执行查询的方式称之为：``all``。

## 注意事项

### A.重温 二级索引 + 回表

``一般情况下``只能利用单个二级索引执行查询。(特殊情况可以看后文：索引合并``index merge``)

优化器一般会根据表的统计数据来判断到底使用哪个条件到对应的二级索引中查询扫描的行数会更少，选择那个扫描行数较少的条件到对应的二级索引中查询。然后将从该二级索引中查询到的结果经过回表得到完整的用户记录后再根据其余的WHERE条件过滤记录。

一般来说，等值查找比范围查找需要扫描的行数更少(也就是``ref``的访问方法一般比``range``好，但这也不总是一定的，也可能采用``ref``访问方法的那个索引列的值为特定值的行数特别多)。

> 需要注意的是，我们说**一般情况下**执行一个查询只会用到单个二级索引，不过还是有特殊情况的。

### B.明确 range 访问方法使用的范围区间

其实对于B+树索引来说，只要索引列和常数使用``=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、=、<=、BETWEEN、!=、<>``或者``LIKE``操作符连接起来，就可以产生一个所谓的``区间``。

> ``LIKE``操作符比较特殊，只有在匹配完整字符串或者匹配字符串前缀时才可以利用索引。

``IN``操作符的效果和若干个等值匹配操作符``=``之间用``OR``连接起来是一样的，也就是说会产生多个单点区间。

一个使用到索引的搜索条件和没有使用到索引的搜索条件使用``OR``连接起来后是**无法**使用该索引的。

## 索引合并

使用到多个索引来完成一次查询的执行方法称之为：``index merge``。

### Intersection 合并

``Intersection``直译就是交集，指某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集。MySQL在某些特定的情况下才可能会使用到Intersection索引合并：

- 情况1：**二级**索引列是**等值匹配**的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。比如：

```sql

SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

```

> 因为只有在这种情况下根据二级索引查询出的结果集是**按照主键值排序**的。

- 情况2：**主键**列可以是**范围匹配**，因为二级索引的记录中都带有主键值，可以直接匹配，无需回表。比如：

```sql

SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';

```

当然，查询条件符合了这些情况也不一定就会采用``Intersection``索引合并(必要非充分)。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数**太多**，导致回表开销太大，而通过``Intersection``索引合并后需要回表的记录数大大减少时才会使用``Intersection``索引合并。

> 按照有序的主键值去回表取记录有个专有名词儿，叫：``Rowid Ordered Retrieval``，简称``ROR``。

###

### Union合并

``Intersection``是``交集``的意思，适用于使用不同索引的搜索条件之间使用``AND``连接起来的情况；

``Union``是``并集``的意思，适用于使用不同索引的搜索条件之间使用``OR``连接起来的情况。

与``Intersection``索引合并类似，MySQL在某些特定的情况下才可能会使用到``Union``索引合并：

- 情况一：**二级**索引列是**等值匹配**的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。比如：

```sql

SELECT * FROM single_table WHERE key1 = 'a' OR ( key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');

```

- 情况二：**主键**列可以是**范围匹配**，比如：

```sql

SELECT * FROM single_table WHERE id > 100 OR key1 = 'a';

```

- 情况三：使用``Intersection``索引合并的搜索条件，就是搜索条件的某些部分使用``Intersection``索引合并的方式得到的主键集合和其他方式得到的主键集合取交集，比如：

```sql

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');

```

当然，查询条件符合了这些情况也不一定就会采用``Union``索引合并，也得看优化器的心情。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数**比较少**，通过``Union``索引合并后进行访问的代价比全表扫描更小时才会使用``Union``索引合并。

### Sort-Union合并

``Union``索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹配的条件下才可能被用到，比方说下边这个查询就无法使用到``Union``索引合并：

```sql

SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'

```

可以先按照``二级索引``记录的``主键值``进行排序，之后按照``Union``索引合并方式执行的方式称之为：``Sort-Union``索引合并，很显然，这种``Sort-Union``索引合并比单纯的``Union``索引合并多了一步对二级索引记录的主键值排序的过程。

> 为啥有``Sort-Union``索引合并，就没有``Sort-Intersection``索引合并么？是的，的确没有``Sort-Intersection``索引合并。

``Sort-Union``的适用场景是单独根据搜索条件从某个二级索引中获取的记录数比较**少**，这样即使对这些二级索引记录按照主键值进行排序的成本也不会太高；

而``Intersection``索引合并的适用场景是单独根据搜索条件从某个二级索引中获取的记录数**太多**，导致回表开销太大，合并后可以明显降低回表开销。

但是如果加入``Sort-Intersection``后，就需要为大量的二级索引记录按照主键值进行排序，这个成本可能比回表查询都高了，所以也就没有引入``Sort-Intersection``这个玩意儿。

# 10.连接的原理

在两表连接查询中，**驱动表**(第一个需要查询的表)只需要访问一次，被驱动表可能被访问多次。

对于``LEFT JOIN``类型的连接来说，把放在左边的表称之为**外表或者驱动表**，右边的表称之为**内表或者被驱动表**。``RIGHT JOIN`` 类推。

内连接和外连接的根本区别就是：**在驱动表中的记录不符合ON子句中的连接条件时不会把该记录加入到最后的结果集**。

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。不论哪个表作为驱动表，两表连接产生的笛卡尔积肯定是一样的。

而对于内连接来说，由于凡是不符合``ON``子句或``WHERE``子句中的条件的记录都会被过滤掉，其实也就相当于从两表连接的笛卡尔积中把不符合过滤条件的记录给踢出去。

所以对于``内连接``来说：驱动表和被驱动表是**可以互换**的，并不会影响最后的查询结果。

但是对于``外连接``来说：由于驱动表中的记录即使在被驱动表中找不到符合``ON``子句连接条件的记录，也会加入结果集。所以此时驱动表和被驱动表的关系就很重要了，也就是说左外连接和右外连接的驱动表和被驱动表**不能轻易互换**。

## 嵌套循环连接(Nested-Loop Join)

多表关联的过程就像是一个嵌套的循环，所以这种驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为：``嵌套循环连接``(Nested-Loop Join)，这是最简单，也是最笨拙的一种连接查询算法。

## 使用索引加快连接速度

在连接查询中对被驱动表使用主键值或者唯一二级索引列的值进行等值查找的查询执行方式称之为：``eq_ref``。

有时候连接查询的查询列表和过滤条件中可能只涉及被驱动表的部分列，而这些列都是某个索引的一部分，这种情况下即使不能使用``eq_ref``、``ref``、``ref_or_null``或者``range``这些访问方法执行对被驱动表的查询的话，也可以使用索引扫描，也就是``index``的访问方法来查询被驱动表。

所以我们建议在真实工作中最好**不要使用*作为查询列表**，最好把真实用到的列作为查询列表。

## 基于块的嵌套循环连接(Block Nested-Loop Join)

``join buffer``就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个``join buffer``中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和``join buffer``中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以**显著减少被驱动表的I/O**代价。

最好的情况是``join buffer``足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。这种加入了``join buffer``的嵌套循环连接算法称之为：**基于块的嵌套连接**(Block Nested-Loop Join)算法。

这个``join buffer``的大小是可以通过启动参数或者系统变量``join_buffer_size``进行配置，默认大小为``262144``字节(也就是``256KB``)，最小可以设置为``128``字节。

当然，对于优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引，如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大``join_buffer_size``的值来对连接查询进行优化。

另外需要注意的是，驱动表的记录并不是所有列都会被放到``join buffer``中，只有查询列表中的列和过滤条件中的列才会被放到``join buffer``中，所以再次提醒我们，**最好不要把*作为查询列表**，只需要把我们关心的列放到查询列表就好了，这样还可以在``join buffer``中放置更多的记录！

# 11.基于成本的优化

## 什么是成本

- I/O成本：

我们的表经常使用的 MyISAM、InnoDB 存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为``I/O成本``。

- CPU成本：

读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为``CPU成本``。

对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL 规定读取一个页面花费的成本默认是``1.0``，读取以及检测一条记录是否符合搜索条件的成本默认是``0.2``。1.0、0.2这些数字称之为：**成本常数**。

> 不管读取记录时需不需要检测是否满足搜索条件，其成本都算是``0.2``。

#12.基于规则的优化

MySQL 会依据一些规则，竭尽全力的把很糟糕的语句转换成某种可以比较高效执行的形式，这个过程也可以被称作：``查询重写``。

## 条件化简

- 移除不必要的括号

- 常量传递(constant_propagation)(AND 才可以)：

``a = 5 AND b > a`` --> ``a = 5 AND b > 5``

- 等值传递(equality_propagation)：

``a = b and b = c and c = 5`` --> ``a = 5 and b = 5 and c = 5``

- 移除没用的条件(trivial_condition_removal)：

对于一些明显永远为TRUE或者FALSE的表达式，优化器会移除掉它们

- 表达式计算：

在查询开始执行之前，如果表达式中只包含常量的话，它的值会被先计算出来。但是如果某个列并不是以单独的形式作为表达式的操作数时，比如出现在函数中，出现在某个更复杂表达式中时优化器不会对其做简化。

- HAVING子句和WHERE子句的合并

- 常量表检测：

## 外连接消除

外连接和内连接的本质区别就是：对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用 NULL 值填充；而内连接的驱动表的记录如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录，那么该记录会被舍弃。

把在外连接查询中，指定的 WHERE 子句中包含被驱动表中的列不为 NULL 值的条件称之为：``空值拒绝``(英文名：reject-NULL)。在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询。例如：

```sql

SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;

<==>

SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;

```

## 子查询优化

1.按返回的结果集区分子查询可分为：

- **标量子查询**：只返回一个单一值的子查询

- 行子查询：返回一条记录的子查询(包含多个列)

- 列子查询：查询出一个列的数据(包含多条记录)

- 表子查询：子查询的结果既包含很多条记录，又包含很多个列

2.按与外层查询关系来区分子查询可分为：

- 不相关子查询：子查询可以单独运行出结果，而不依赖于外层查询的值

- 相关子查询：子查询的执行需要依赖于外层查询的值

3.子查询在布尔表达式中的使用:

- 子查询可以使用``=、>、=、<=、<>、!=、<=>``作为布尔表达式的操作符

- ``[NOT] IN/ANY/SOME/ALL``也可以用于子查询

- ``EXISTS/NOT EXISTS``子查询

4.子查询语法注意事项:

- 子查询必须用小括号扩起来

- 在``SELECT``子句中的子查询必须是**标量子查询**

- 在想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用``LIMIT 1``语句来限制记录数量

- 对于``[NOT] IN/ANY/SOME/ALL``子查询来说，子查询中**不允许**有``LIMIT``语句

> 因此：这种子查询中的``ORDER BY、DISTINCT、GROUP BY``这些语句也就是多余的了，优化器会自动干掉。

- 不允许在一条语句中增删改某个表的记录时同时还对该表进行子查询

```sql

DELETE FROM t1 WHERE m1 < (SELECT MAX(m1) FROM t1);

ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause

```

5.标量子查询、行子查询的执行方式:

对于包含**不相关**的标量子查询或者行子查询的查询语句来说，MySQL会分别独立的执行外层查询和子查询，就当作两个单表查询就好了。

6.IN子查询优化：

不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里：

- 该临时表的列就是子查询结果集中的列。

- 写入临时表的记录会被去重

- 一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用``Memory``存储引擎的临时表，而且会为该表建立``哈希索引``。

> 如果子查询的结果集非常大，超过了系统变量``tmp_table_size``或者``max_heap_table_size``，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。

MySQL 把这个将子查询结果集中的记录保存到``临时表``的过程称之为``物化``(英文名：Materialize)。

正因为物化表中的记录都建立了**索引**(基于内存的物化表有哈希索引，基于磁盘的有B+树索引)，通过索引执行 IN 语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。

> 条件满足时物化表可以转连接。

7.将子查询转换为``semi-join``

``semi-join``只是在 MySQL 内部采用的一种执行子查询的方式，MySQL 并没有提供面向用户的``semi-join``语法。

```sql

SELECT * FROM s1

WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

==>

SELECT s1.* FROM s1 SEMI JOIN s2

ON s1.key1 = s2.common_field

WHERE key3 = 'a';

```

只有符合下边这些条件的子查询才可以被转换为``semi-join``：

- 该子查询必须是和IN语句组成的布尔表达式，并且在外层查询的WHERE或者ON子句中出现。

- 外层查询也可以有其他的搜索条件，只不过和IN子查询的搜索条件必须使用AND连接起来。

- 该子查询必须是一个单一的查询，不能是由若干查询由UNION连接起来的形式。

- 该子查询不能包含GROUP BY或者HAVING语句或者聚集函数。

例如：

```sql

SELECT ... FROM outer_tables

WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...

或者这样的形式也可以：

SELECT ... FROM outer_tables

WHERE (oe1, oe2, ...) IN (SELECT ie1, ie2, ... FROM inner_tables ...) AND ...

```

不适用于semi-join的情况：

- 外层查询的WHERE条件中有其他搜索条件与IN子查询组成的布尔表达式使用OR连接起来

- 使用NOT IN而不是IN的情况

- 在SELECT子句中的IN子查询的情况

- 子查询中包含GROUP BY、HAVING或者聚集函数的情况

- 子查询中包含UNION的情况

# 13.Explain 详解

一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的``执行计划``，这个执行计划展示了接下来具体执行查询的方式。

MySQL 提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划。

EXPLAIN 输出的各个列：

- id：在一个大的查询语句中每个SELECT关键字都对应一个唯一的id

- select_type：SELECT关键字对应的那个查询的类型

- table：表名

- partitions：匹配的分区信息

- type：针对单表的访问方法

- possible_keys：可能用到的索引

- key：实际上使用的索引

- key_len：实际使用到的索引长度

- ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息

- rows：预估的需要读取的记录条数

- filtered：某个表经过搜索条件过滤后剩余记录条数的百分比

- Extra：一些额外的信息

## 执行计划输出中各列详解

### table

EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法，该条记录的``table``列代表着该表的**表名**。

### id

查询语句中每出现一个``SELECT``关键字，设计 MySQL 的大叔就会为它分配一个唯一的``id``值。这个 id 值就是 EXPLAIN 语句的第一个列。

在**连接查询**的执行计划中，每个表都会对应一条记录，这些记录的 id 列的值是**相同**的，出现在**前边**的表表示**驱动表**，出现在**后边**的表表示**被驱动表**。

对于包含子查询的查询语句来说，就可能涉及多个 SELECT 关键字，所以在包含子查询的查询语句的执行计划中，**每个SELECT关键字都会对应一个唯一的id值**。

> 查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询，从 EXPLAIN 中可以分析出来。

使用``UNION``语法时，EXPLAIN 后最后一行会出现 id 为``NULL``的结果，表明这个是**临时表**，是为了合并两(多)个查询的结果集而创建的(为了将结果集合起来并去重)。而``UNION ALL``就不需要为最终的结果集进行去重(也不需要临时表)。

### select_type

每一个``SELECT``关键字代表的小查询都定义了一个称之为``select_type``的属性，只要知道了某个小查询的``select_type``属性，就知道了这个小查询在整个大查询中扮演了一个什么角色。

- ``SIMPLE``：查询语句中不包含``UNION``或者子查询的查询都算作是``SIMPLE``类型。

- ``PRIMARY``：对于包含``UNION、UNION ALL``或者子查询的大查询来说，它是由几个小查询组成的，其中**最左**边的那个查询的``select_type``值就是``PRIMARY``。

- ``UNION``：对于包含``UNION``或者``UNION ALL``的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的``select_type``值就是``UNION``。

- ``UNION RESULT``：选择使用临时表来完成 UNION 查询的去重工作，针对该临时表的查询的``select_type``就是``UNION RESULT``。

- ``SUBQUERY``：如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是**不相关**子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个 SELECT 关键字代表的那个查询的``select_type``就是``SUBQUERY``(由于SUBQUERY的子查询会被物化，所以只需要执行一遍)。

- ``DEPENDENT SUBQUERY``：如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是**相关**子查询，则该子查询的第一个 SELECT 关键字代表的那个查询的``select_type``就是``DEPENDENT SUBQUERY``(DEPENDENT SUBQUERY 的查询可能会被执行多次)。

- ``DEPENDENT UNION``：在包含``UNION``或者``UNION ALL``的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的``select_type``的值就是``DEPENDENT UNION``。

- ``DERIVED``：对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的``select_type``就是``DERIVED``。

- ``MATERIALIZED``：当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的``select_type``属性就是``MATERIALIZED``。

- ``UNCACHEABLE SUBQUERY``

- ``UNCACHEABLE UNION``

### partitions

一般情况下我们的查询语句的执行计划的``partitions``列的值都是``NULL``。

### type

前边说过执行计划的一条记录就代表着 MySQL 对某个表的执行查询时的访问方法，其中的``type``列就表明了这个访问方法是什么。

完整的访问方法如下：``system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL``。

- ``system``：当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如``MyISAM、Memory``，那么对该表的访问方法就是``system``。

- ``const``：根据**主键或者唯一二级索引**列与常数进行**等值**匹配时，对单表的访问方法就是``const``。

- ``eq_ref``：在连接查询时，如果被驱动表是通过**主键或者唯一二级索引**列**等值**匹配的方式进行访问的(如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较)，则对该被驱动表的访问方法就是``eq_ref``。

- ``ref``：当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是``ref``。

- ``fulltext``：全文索引。

- ``ref_or_null``：当对普通二级索引进行等值匹配查询，该索引列的值也可以是``NULL``值时，那么对该表的访问方法就可能是``ref_or_null``。

- ``index_merge``：一般情况下对于某个表的查询只能使用到一个索引，但在某些场景下可以使用``Intersection、Union、Sort-Union``这三种**索引合并**的方式来执行查询。

- ``unique_subquery``：类似于两表连接中被驱动表的``eq_ref``访问方法，``unique_subquery``是针对在一些包含``IN``子查询的查询语句中，如果查询优化器决定将``IN``子查询转换为``EXISTS``子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的``type``列的值就是``unique_subquery``。

- ``index_subquery``：``index_subquery``与``unique_subquery``类似，只不过访问子查询中的表时使用的是普通的索引。

- ``range``：如果使用索引获取某些**范围区间**的记录，那么就**可能**使用到``range``访问方法。

- ``index``：当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是``index``。

- ``ALL``：全表扫描。

### possible_keys 和 key

``possible_keys``列表示在某个查询语句中，对某个表执行单表查询时**可能**用到的索引有哪些，``key``列表示**实际**用到的索引有哪些

> 有一点特别的：就是在使用``index``访问方法来查询某个表时，``possible_keys``列是空的，而``key``列展示的是实际使用到的索引。

``possible_keys``列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量**删除**那些**用不到**的索引。

### key_len

表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，它是由这三个部分构成的：

- 1.对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，比如某个索引列的类型是VARCHAR(100)，使用的字符集是utf8，那么该列实际占用的最大存储空间就是100 × 3 = 300个字节。

- 2.如果该索引列可以存储NULL值，则key_len比不可以存储NULL值时多**1**个字节。

- 3.对于变长字段来说，都会有**2**个字节的空间来存储该变长列的实际长度。

### ref

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是``const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery``其中之一时，``ref``列展示的就是与索引列作等值匹配的东东是个啥，比如只是一个常数或者是某个列、或者是一个函数(func)。

### rows

如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的``rows``列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的``row``s列就代表预计扫描的索引记录行数。

### filtered (MySQL 5.7 后才有这一列)

之前在分析连接查询的成本时提出过一个 condition filtering 的概念，就是MySQL在计算驱动表扇出时采用的一个策略：

- 如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要估计出满足搜索条件的记录到底有多少条。

- 如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

对于单表查询来说，这个``filtered``列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的``filtered``值。

我们分析驱动表的执行计划，其中假如``rows``列为 n， ``filtered``列为 x，这意味着**驱动**表的**扇出值**就是**``n × x%``**，这个值表明还要对**被驱动**表执行的大概查询次数。

### Extra

``Extra``列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解 MySQL 到底将如何执行给定的查询语句。MySQL 提供的额外信息有好几十个，所以只挑一些平时常见的或者比较重要的额外信息介绍。

- ``No tables used``：当查询语句的没有 FROM 子句时将会提示该额外信息

- ``Impossible WHERE``：查询语句的WHERE子句永远为FALSE时将会提示该额外信息

- ``No matching min/max row``：当查询列表处有``MIN``或者``MAX``聚集函数，但是并没有符合 WHERE 子句中的搜索条件的记录时，将会提示该额外信息

- ``Using index``：当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在 Extra 列将会提示该额外信息。

- ``Using index condition``：有些搜索条件中虽然出现了索引列，但却不能使用到索引。

> MySQL 有一种改进(特性)称之为**索引条件下推**(英文名：Index Condition Pushdown)，使用索引条件下推时便会在 Extra 列显示``Using index condition``。

- ``Using where``：

当我们使用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中有针对该表的搜索条件时，在Extra列中会提示上述额外信息。

当使用索引访问来执行对某个表的查询，并且该语句的 WHERE 子句中有除了该索引包含的列之外的其他搜索条件时，在 Extra 列中也会提示上述额外信息。

- ``Using join buffer (Block Nested Loop)``：在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL 一般会为其分配一块名叫 join buffer 的内存块来加快查询速度，也就是**基于块的嵌套循环算法**。

- ``Not exists``：当我们使用左(外)连接时，如果 WHERE 子句中包含要求被驱动表的某个列等于 NULL 值的搜索条件，而且那个列又是不允许存储 NULL 值的，那么在该表的执行计划的 Extra 列就会提示``Not exists``额外信息(同理，右外连接也适用)。

- ``Using intersect(...)、Using union(...) 和 Using sort_union(...)``：

如果执行计划的 Extra 列出现了``Using intersect(...)``提示，说明准备使用``Intersect``索引合并的方式执行查询，括号中的...表示需要进行索引合并的索引名称；

如果出现了``Using union(...)``提示，说明准备使用``Union``索引合并的方式执行查询；

出现了``Using sort_union(...)``提示，说明准备使用``Sort-Union``索引合并的方式执行查询。

- ``Zero limit``：当我们的 LIMIT 子句的参数为 0 时，表示不打算从表中读出任何记录，将会提示该额外信息。

- ``Using filesort``：

有一些情况下对结果集中的记录进行排序是可以使用到索引的，但是很多情况下排序操作无法使用到索引，只能在内存中(记录较少的时候)或者磁盘中(记录较多的时候)进行排序，MySQL 把这种在内存中或者磁盘上进行排序的方式统称为：**文件排序**(英文名：``filesort``)。如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的 Extra 列中显示``Using filesort``提示。

> 需要注意的是，如果查询中需要使用``filesort``的方式进行排序的记录非常多，那么这个过程是很耗费性能的，我们最好想办法将使用文件排序的执行方式改为使用**索引**进行排序。

- ``Using temporary``：在许多查询的执行过程中，MySQL 可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含``DISTINCT、GROUP BY、UNION``等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL 很有可能寻求通过建立内部的临时表来执行查询。

如果查询中使用到了内部的临时表，在执行计划的 Extra 列将会显示``Using temporary``提示。

> 执行计划中出现``Using temporary``并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以我们最好能使用索引来替代掉使用临时表。

> MySQL 会在包含``GROUP BY``子句的查询中默认添加上``ORDER BY``子句，如果我们并不想为包含``GROUP BY``子句的查询进行排序，需要我们显式的写上``ORDER BY NULL``。

- ``Start temporary, End temporary``：查询优化器会优先尝试将 IN 子查询转换成 semi-join，而semi-join 又有好多种执行策略，当执行策略为``DuplicateWeedout``时，也就是通过建立临时表来实现为外层查询中的记录进行去重操作时，驱动表查询执行计划的 Extra 列将显示``Start temporary``提示，被驱动表查询执行计划的 Extra 列将显示``End temporary``提示。

- ``LooseScan``：在将 In 子查询转为 semi-join 时，如果采用的是``LooseScan``执行策略，则在驱动表执行计划的 Extra 列就是显示``LooseScan``提示。

- ``FirstMatch(tbl_name)``：在将 In 子查询转为 semi-join 时，如果采用的是``FirstMatch``执行策略，则在被驱动表执行计划的 Extra 列就是显示``FirstMatch(tbl_name)``提示。

## Json 格式的执行计划

上边介绍的 EXPLAIN 语句输出中缺少了一个衡量执行计划好坏的重要属性 —— **成本**。不过 MySQL 贴心的为我们提供了一种查看某个执行计划花费的成本的方式：**在 EXPLAIN 单词和真正的查询语句中间加上``FORMAT=JSON``**。

可以得到一个 json 格式的执行计划，里面包含了该计划花费的成本：

```json

"query_block": {

"select_id": 1, # 整个查询语句只有1个SELECT关键字，该关键字对应的id号为1

"cost_info": {

"query_cost": "3197.16" # 整个查询的执行成本预计为3197.16

},

"nested_loop": [ # 几个表之间采用嵌套循环连接算法执行

# 以下是参与嵌套循环连接算法的各个表的信息

{

"table": {

"table_name": "s1", # s1表是驱动表

"access_type": "ALL", # 访问方法为ALL，意味着使用全表扫描访问

"possible_keys": [ # 可能使用的索引

"idx_key1"

],

"rows_examined_per_scan": 9688, # 查询一次s1表大致需要扫描9688条记录

"rows_produced_per_join": 968, # 驱动表s1的扇出是968

"filtered": "10.00", # condition filtering代表的百分比

"cost_info": {

"read_cost": "1840.84", # 后面解释

"eval_cost": "193.76", # 后面解释

"prefix_cost": "2034.60", # 单次查询s1表总共的成本

"data_read_per_join": "1M" # 读取的数据量

},

"used_columns": [ # 执行查询中涉及到的列

"id",

"key1",

"key2",

"key3",

"key_part1",

"key_part2",

"key_part3",

"common_field"

],

# 对s1表访问时针对单表查询的条件

"attached_condition": "((`xiaohaizi`.`s1`.`common_field` = 'a') and (`xiaohaizi`.`s1`.`key1` is not null))"

}

},

{

"table": {

"table_name": "s2", # s2表是被驱动表

"access_type": "ref", # 访问方法为ref，意味着使用索引等值匹配的方式访问

"possible_keys": [ # 可能使用的索引

"idx_key2"

],

"key": "idx_key2", # 实际使用的索引

"used_key_parts": [ # 使用到的索引列

"key2"

],

"key_length": "5", # key_len

"ref": [ # 与key2列进行等值匹配的对象

"xiaohaizi.s1.key1"

],

"rows_examined_per_scan": 1, # 查询一次s2表大致需要扫描1条记录

"rows_produced_per_join": 968, # 被驱动表s2的扇出是968(由于后边没有多余的表进行连接，所以这个值也没啥用)

"filtered": "100.00", # condition filtering代表的百分比

# s2表使用索引进行查询的搜索条件

"index_condition": "(`xiaohaizi`.`s1`.`key1` = `xiaohaizi`.`s2`.`key2`)",

"cost_info": {

"read_cost": "968.80", # 后面解释

"eval_cost": "193.76", # 后面解释

"prefix_cost": "3197.16", # 单次查询s1、多次查询s2表总共的成本

"data_read_per_join": "1M" # 读取的数据量

},

"used_columns": [ # 执行查询中涉及到的列

"id",

"key1",

"key2",

"key3",

"key_part1",

"key_part2",

```

其他 Explain 解释可参考：[查询优化的百科全书 —— Explain 详解(下)](https://juejin.im/book/5bffcbc9f265da614b11b731/section/5c24b04df265da61542daebe)