最近在跟着掘金小册：MySQL 是怎样运行的：从根儿上理解 MySQL 重温Mysql，写的很好，再次结合自己理解，做下笔记，希望没有侵权。

1.启动选项

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

2.设置系统变量

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提供了一条非常简便的语句:

SET NAMES 字符集名;
<=等价于=>
SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;

或者在配置文件中指定：

[client]
default-character-set=utf8

如果你在使用 MySQL 或 MariaDB，不要使用用“utf8”编码，改用“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并没有什么所谓的表空间一说，==表数据都存放到对应的数据库子目录下==。

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

表名.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.搜索条件中只出现包含在（联合）索引中的列。

例如：

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

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

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

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

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索引合并：

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

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

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

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

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

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索引合并：

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子句符合空值拒绝的条件后，外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询。例如：

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这些语句也就是多余的了，优化器会自动干掉。

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

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语法。

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语句或者聚集函数。

例如：

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列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的rows列就代表预计扫描的索引记录行数。

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 格式的执行计划，里面包含了该计划花费的成本：

"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 详解（下）

[笔记]从根儿上理解 MySQL