SQL语句，在实际的MySQL底层，针对磁盘上的大量数据表、聚簇索引和二级索引，如何检索查询，如何筛选过滤，如何使用函数，如何进行排序，如何进行分组，到底怎样才能把你想要的东西查出来，这个过程就是执行计划。
- 使用explain命令来查看语句的执行计划，MySQL在执行某个语句之前，会将该语句过一遍查询优化器，之后会拿到对语句的分析，也就是执行计划，其中包含了很多信息。
- 可以通过其中和索引相关的信息来分析是否命中了索引，比如：possible_key，key，key_len分别说明了此语句可能会使用的索引、实际使用的索引以及使用的索引长度
如何获取执行计划
- 只要在SQL语句前面加一个explain就可以拿到这个SQL语句的执行计划。比如：
explain select * from table
mysql> explain select 1\G -- desc select 1\G
*************************** 1. row *************************** -- 在查询中每个表的输入中只有一行，如果查询的时两个表的联结，那么输出将有2行
id: 1
select_type: SIMPLE --- 不包含UNION查询和子查询
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
1 row in set, 1 warning (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select 1 AS `1` --->实际执行的语句：【已经被查询优化器优化了的语句】
mysql> explain format=json select 1\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"message": "No tables used"
}
}上面的id、select_type、table、partitions、type，这些就是所谓的执行计划：
- id：每个select都会对应一个id，一个复杂的SQL里可能会有多个SELECT，也可能会包含多条执行计划，每一条执行计划都会有唯一的id
- 执行一个explain，可能会出现一行，此时只有一个执行计划
- 执行一个explain，可能会出现多行，此时说明这条SQL语句拆分成多个步骤，需要执行多个执行计划
- 可能会有同一个id对应着多个执行计划，这说明一条SQL语句需要拆分成多个执行计划（当出现join查询时）
- 可能会有2个id对应着多个执行计划，这可能是因为有2个select（就是有主查询select和子查询select）
- 如果id是UNION呢？它不对应着SELECT语句，可能是临时语句，比如UNION查询，用来临时表指令的
- select_type：就是这一条执行计划对应的查询是个什么类型
- 其实select_type并不是很关键，因为他主要是代表了大SQL里的不同的SELECT代表了一个什么角色，比如有的SELECT是PRIMARY查询，有的是UNION，有的是SUBQUERY。
- 关键的是type，它直接决定了对某个表示如何从里面查询数据的
- table ：表名，表示要查询哪个表
- partitions ：表分区
- type：这个比较关键，表示针对这个表的访问方法，比如const、ref、range、index、all，分布代表了使用聚簇索引、二级索引、全表扫描之类的访问方式
- 走主键/唯一索引的查询：
- 执行计划中的const，那么肯定是通过主键或者唯一索引来访问，速度极高
- 执行计划中的ref，就是使用了普通索引，或者用主键/唯一索引搞了一个IS NULL/IS NOT NULL，速度也很快
- 执行计划中的range，顾名思义，就是你的SQL里有范围查询的时候就会走这个方式。
- 说明：
- const、ref、range本质都是基于索引树的二分查找和多层跳转来查询，所以一般问题不是太大
- 除非你通过索引查出来的数据量太多了。比如范围查询一下子找出了10万条数据就会搞死MySQL。
- 走二级索引的查询：index
- 针对这种只要遍历二级索引就可以拿到你想要的数据，而不需要回源到聚簇索引的访问方式，就叫做index访问方式
- 肯定比索引树的二分查找要慢多了，但是比全表扫描还是要好的
- all表示全表扫描。
- possible_keys，这也很关键，它是跟type结合起来的，意思是说你type确定访问方式了，那么到底有哪些索引可以选择。
- key：就是在possible_keys里实际选择的那个索引
- key_len：索引的长度
- ref：就是使用某个字段的索引进行等值匹配搜索的时候，跟索引列进行等值匹配的那个目标值的一些信息。
- row：是预估通过索引或者别的方式访问这个表的时候，大概可以读取多少条数据
- filtered ：就是经过搜索条件过滤后的剩余数据的百分比
- 100%，没有任何过滤条件，此时直接筛选出来的数据就算是表里数据的100%占比。
- extra：一些额外的信息，不是太重要
|类|含义 |
|–|–|
|id |执行计划的id标志
|select_type |SELECT的类型
|table |输出记录的表
|partitions |符合的分区，[PARTITIONS]
|type |JOIN的类型
|possible_keys| 优化器可能使用到的索引
|key |优化器实际选择的索引
|key_len| 使用索引的字节长度
|ref |进行比较的索引列
|rows| 优化器预估的记录数量
|filtered |根据条件过滤得到的记录的百分比[EXTENDED]
|extra |额外的显示选项
下面的是我抄的，实际工作我基本上没用处，一般我要进行调优的话，我会：
先看type之后看key，再看rows，最后是Extra。
- 如果type出现ALL时是全表扫描，这时候就一定要进行调优
- rows的最佳值是1：row越小越好
- 当extra中出现Using temporary或者Using filesort时，说明sql语句需要进行优化。对于Using temporary，当我们的查询涉及多张表时，需要将查询结果放入第三张临时表中来存放。这样势必会降低我们的查询效率，所以当遇到extra中为Using temporary时，也许就是我们应该优化的时候了。
id
如果语句当中简单子查询、在FROM中的子查询以及UNION查询，内层的select子句将会按照从外到里顺序编号，对应于在子查询中的位置。否则，id = 1。
- 简单子查询
ysql> EXplain select (select 1 from sakila.actor limit 1) from sakila.film\G
*************************** 1. row ***************************
id: 1 --- select xx from sakila.film 是外层子查询，所以id = 1
select_type: PRIMARY
table: film ---》从表1, film中查询
partitions: NULL
type: index
possible_keys: NULL
key: idx_fk_language_id
key_len: 1
ref: NULL
rows: 1000
filtered: 100.00
Extra: Using index
*************************** 2. row *************************** -- 因为这个语句中关联到了2个表，所以有两行
id: 2 -->select 1 from sakila.actor limit 1是内层子查询，所以id=2。内层子查询先执行。
select_type: SUBQUERY
table: actor ---》从表2, actor中查询
partitions: NULL
type: index
possible_keys: NULL
key: idx_actor_last_name
key_len: 137
ref: NULL
rows: 200
filtered: 100.00
Extra: Using index
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings \G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select (/* select#2 */ select 1 from `sakila`.`actor` limit 1) AS `(select 1 from sakila.actor limit 1)` from `sakila`.`film`因为子查询是先执行内层查询的，推测，id越大，越先执行，个人推测，等我找完资料或者验证完毕之后再来填坑
- from子句的子查询，也叫做派生表 。派生表的意思时语句执行时临时会生成一个匿名临时表，MYSQL内部通过别名在外层查询中引用这个临时表。
mysql> explain select actor_id from (select actor_id from sakila.actor LIMIT 5) as der_1\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY ---->说明有子查询或者UNION查询，而且这个是最外层的select子句
table: <derived2> --->这个就是派生表了, MYSQL内部先执行内层子查询select actor_id from sakila.actor LIMIT 5，将查询结果放入一个匿名临时表中，然后执行外层子查询时从匿名子查询中查询需要的数据
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 2 -->select 1 from sakila.actor limit 1是内层子查询，所以id=2。内层子查询先执行。
select_type: DERIVED -- from 之后的SELECT子查询。
table: actor
partitions: NULL
type: index
possible_keys: NULL
key: idx_actor_last_name
key_len: 137
ref: NULL
rows: 200
filtered: 100.00
Extra: Using indexMYSQL会从内到外递归执行from之后的子查询，并将查询结果放入一个匿名临时表中，这个临时表就叫做派生表，因为该临时表是从子查询中派生来的。
- union子查询
mysql> explain SELECT actor_id from actor union SELECT first_name from actor\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: actor
partitions: NULL
type: index
possible_keys: NULL
key: idx_actor_last_name
key_len: 137
ref: NULL
rows: 200
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 2
select_type: UNION ---》UNION后面跟着的语句
table: actor
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 200
filtered: 100.00
Extra: NULL
*************************** 3. row ***************************
id: NULL ---》这个临时表不在原SQL中出现，因此他的id列是NULL
select_type: UNION RESULT ---》用来从UNION的匿名临时表检索结果的select被标记为UNION RESULT
table: <union1,2> --》UNION结果总是将id1和id2结果放在一个匿名临时表中，之后MYSQL将结果读取到临时表之外
partitions: NULL
type: ALL -- 全表扫描
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Using temporary ---优化器需要使用临时表select_type
select_type | 含义 |
SIMPLE | 简单SELECT(不使用UNION或子查询等) |
PRIMARY | 如果查询有任何复杂的子部分，则将最外层的SELECT标记为PRIMARY |
SUBQUERY | 在SELECT列表中的SELECT(不在from中的子查询) |
UNION | UNION中的第二个或后面的SELECT语句 |
DEPENDENT UNION | UNION中的第二个或后面的SELECT语句，依赖于外面的查询 |
UNION RESULT | UNION的结果 |
DEPENDENT SUBQUERY | 子查询中的第一个SELECT，依赖于外面的查询 |
DERIVED | FROM子句的SELECT |
MATERIALIZED | 物化子查询 |
UNCACHEABLE SUBQUERY | 不会被缓存的并且对于外部查询的每行都要重新计算的子查询 |
UNCACHEABLE UNION | 属于不能被缓存的 UNION中的第二个或后面的SELECT语句 |
MATERIALIZED |
- 产生中间临时表(实体)
- 临时表自动创建索引并和其他表进行关联，提高性能
- 和子查询的区别式，优化器将可以进行MATERIALIZED 的语句自动
mysql> explain select film_id , (select rental_id from sakila.rental limit 1) from sakila.film AS der_2\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: der_2 --- sakila.film 的别名der_2
partitions: NULL
type: index -- 索引扫描
possible_keys: NULL
key: idx_fk_language_id
key_len: 1
ref: NULL
rows: 1000
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 2
select_type: SUBQUERY --- 在SELECT列表中的SELECT
table: rental
partitions: NULL
type: index -- 索引扫描
possible_keys: NULL
key: idx_fk_staff_id
key_len: 1
ref: NULL
rows: 16008
filtered: 100.00
Extra: Using indexmysql> explain select film_id , (select @var1 from sakila.rental limit 1) from sakila.film AS der_2\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: der_2
partitions: NULL
type: index -- 索引扫描
possible_keys: NULL
key: idx_fk_language_id
key_len: 1
ref: NULL
rows: 1000
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 2
select_type: UNCACHEABLE SUBQUERY -- @var1 的结果被缓存在一个ITem-cache中
table: rental
partitions: NULL
type: index -- 索引扫描
possible_keys: NULL
key: idx_fk_staff_id
key_len: 1
ref: NULL
rows: 16008
filtered: 100.00
Extra: Using indextable
- 通常是用户操作的用户表
- <unionM, N> UNION得到的结果表
- 派生表，由id=N的语句产生
- 子查询物化产生的表，由id=N的语句产生
mysql> explain select film.film_id from sakila.film inner join sakila.film_actor using(film_id) inner join sakila.actor using(actor_id);
+----+-------------+------------+
| id | select_type | table |
+----+-------------+------------+-
| 1 | SIMPLE | actor |
| 1 | SIMPLE | film_actor |
| 1 | SIMPLE | film | -- 可以从table列中从上往下查询关联优化器查询时的关联顺序：先actor -> film_actor --> film
+----+-------------+------------+-type
依据箭头，成本从小到大
除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引
- ALL：按照索引次序一行一行的从头到尾扫描整张表
- index：按照索引次序扫描整张表，和全表扫描不同的是，它只扫描索引，不会扫描其他行
- 优点：不需要排序
- 缺点：如果是按随机次序访问行，开销会很大
- range：一个有限制的索引扫描–>从索引里的某一点，返回匹配这个值域的行，它不需要遍历全部索引
- 比如between，where >，in(索引)，or(索引)等
- 常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。
- index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
- unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值
- index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range
- ref_or_null：ref的一个变体，它意味着MYSQL必须在初次查询的结果中进行第二次查询以找出NULL条目
- fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
- ref：索引查找，返回所有匹配某个单个值得行
- 缺点：可能会找到多个符合条件的行。
- 只有当使用非唯一性索引或者唯一性索引的非唯一性前缀才会发生
- 之所以叫做ref是因为索引需要和某个参考值比较。这个参考值可以是常数或者来自多表查询前一个表中的结果集
- ef_ref：最多只返回一条符合条件的记录
- const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描
- system：表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index
- NULL：MYSQL能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引。
possible_keys
优化器可能使用到的索引：这个列表是优化早期创建的，因此有些索引可能会与后续优化过程是没有用的。---->揭示哪些索引有助于高效的查找
key
实际采用的索引：
extra
- Using filesort：可以使用复合索引将filesort进行优化。提高性能
- Using index：比如使用覆盖索引
- Using where: 使用where过滤条件
Extra的信息是可以作为优化的提示，但是更多的是优化器优化的一种说明 - Select tables optimized away：在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
分析
id、select_type分析
看个例子，对于：
explain select * from t1
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3457 | 100.00 | NULL
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+- id是1
- select_type是SIMPLE ，顾名思义，这个表的查询类型是很普通的，而且简单
- table 是tl，意思是要访问t1这个表
- type是all，all表示全表扫描。因为SQL中没有任何where条件，当前只能全表扫描了。这里直接会扫描表的聚簇索引的叶子节点，按顺序扫描过去拿到表里的全部数据
- rows是3457，这说明全表扫描会扫描这个表的2457条数据，说明这个表里有3457条数据。
- filtered是100%，你没有任何where过滤条件，所以直接筛选出来的数据就算是表里数据的100%占比。
第二个例子，对于：
explain select * from t1 join t2这是一个多表关联语句，这种关联语句，实际上会选择一个表先查询出来数据，接着遍历每一条数据去另外一个表里查询可以关联在一起的数据，然后关联起来，此时它的执行计划大概长下面这个样子：
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------
--------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered| Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------
--------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3457 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 4568 | 100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------
--------------+- 因为这是一个多表关联的语句，所以它的执行计划分为了两条，也就是访问两个表。
- 针对第一个表t1，明显是先用ALL全表扫描，而且扫描出了3457条数据
- 接着对第二个表t2，同样是全表扫描，因为它这种多表关联方式，基本上是笛卡尔积的效果，t1表的每条数据都会去t2表全表扫描所有4568条数据，跟t2表的每一条数据都会做一个关联，而且extra里说了是Nested Loop，也就是嵌套循环的访问方式。
- 另外上面两条执行计划都是1，因为在执行计划中，一个select会对应一个id，因为这两条执行计划对应的是一个SELECT语句，所以它们俩的id都是1
- 如果你要是有一个子查询，有另外一个select，那么另外一个select子查询对应的执行计划的id就可能是2了
下一个例子：
EXPLAIN SELECT * FROM t1 WHERE x1 IN (SELECT x1 FROM t2) OR x3 = 'xxxx';这个SQL语句就有点复杂了，因为主SELECT语句的WHERE筛选条件是基于一个子查询的，而且除此之外自己还有一个筛选条件，它的执行计划如下：
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |
filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | PRIMARY | t1 | NULL | ALL | index_x3 | NULL | NULL | NULL | 3457 | 100.00 | Using where |
| 2 | SUBQUERY | t2 | NULL | index | index_x1 | index_x1 | 507 | NULL | 4687 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+- 第一条执行计划的id是1，第二条执行计划的id是2，这是因为这个SQL里面有两个SELECT，主查询SELECT的执行计划的id就是1，子查询SELECT的执行计划的id就是2
- 其次，第一条执行计划里，select_type是PRIMARY ，不是SIMPLE了，说明第一个执行计划的查询类型是主查询的意思，对主查询而言，它有一个where条件是x3=‘xxx’，所以它的possible_keys里包含了index_x3，意思是x3字段的索引，但是它的key实际是NULL，而且type是ALL，所以它最后没有选用x3字段的索引，而是选择了全表扫描
- 这是为什么呢？这是因为MySQL分析成本发现，使用x3字段的索引扫描xxx这个值，几乎跟全表扫描差不多，可能x3这个字段的值几乎都是xxx，所以最好就还不如直接全表扫描呢
- 接着第二条执行计划，select_type是SUBQUERY ，也就是子查询，子查询针对的是t2这个表，当然子查询本身就是一个全表查询，但是对主查询而言，会使用x1 in这个筛选条件，它这里type是index，说明使用index_x1这个x1字段的二级索引的方式，直接扫描x1字段的二级索引，来跟子查询的结果集做比对
再看一个UNION的例子：
EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2这是一个典型的union语句，把两个表的查询结果合并起来。它的执行计划如下：
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |
filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3457 | 100.00 |NULL |
| 2 | UNION | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 4687 | 100.00 | NULL|
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL| NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+- 上面的第一条和第二条：两个SELECT字句对应两个id，就是分别从t1表和t2表里进行全表扫描
- 第三条执行计划是什么呢？因为union默认的作用是把两个结果集合起来以及去重，所以这个计划就是要去重
- 所以上面他的table是< union 1,2>，这就是一个临时表的表名，而且extra中的Using temporary，也是使用临时表的意思。他就是把结果集放到临时表里进行去重的。当然如果你用的是union all，那么就不用去重了
下一个例子：
EXPLAIN SELECT * FROM t1 WHERE x1 IN (SELECT x1 FROM t2 WHERE x1 = 'xxx' UNION SELECT x1
FROM t1 WHERE x1 = 'xxx');这个SQL语句就稍微有点复杂了，因为他有一个外层查询，还有一个内层子查询，子查询里还有两个
SELECT语句进行union操作，那么我们来看看他的执行计划会是什么样的呢？
+----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+------
--------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |
filtered | Extra |
+----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------
------------------+
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3467 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | NULL | ref | index_x1 | index_x1 | 899 | const | 59 | 100.00 | Using where; Using index |
| 3 | DEPENDENT UNION | t1 | NULL | ref | index_x1 | index_x1 | 899 | const | 45 | 100.00 | Using where; Using index |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------
------------------+- 第一个执行计划是针对t1表查询的那个外层循环，select_type是PRIMARY，因为这里涉及到了子查询，所以外层循环的select_type一定是PRIMARY
- 第二个执行计划是子查询里针对t2表的查询语句，select_type是 DEPENDENT SUBQUERY
- 第三个执行计划是子查询里针对t1表的另外一个查询语句，select_type是DEPENDENT UNION，因为第三个执行计划是在执行UNION后的查询
- 第四个执行计划的select_type是UNIONRESULT，因为在执行子查询里两个结果集的合并以及去重
下一个例子：
EXPLAIN SELECT * FROM (SELECT x1, count(*) as cnt FROM t1 GROUP BY x1) AS _t1 where cnt >
10这个SQL可有点麻烦了，他是FROM子句后跟了一个子查询，在子查询里是根据x1字段进行分组然后进行count聚合操作，也就是统计出来x1这个字段每个值的个数，然后在外层则是针对这个内层查询的结果集进行查询通过where条件来进行过滤，看看他的执行计划：
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |
filtered | Extra |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 3468 | 33.33 | Using where
| 2 | DERIVED | t1 | NULL | index | index_x1 | index_x1 | 899 | NULL | 3568 | 100.00 |
Using index |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+- 上面的执行计划里，我们其实应该先看第二条执行计划，他说的是子查询里的那个语句的执行计划，他的select_type是derived，意思是说，针对子查询执行后的结果集会物化为一个内部临时表，然后外层查询是针对这个临时的物化表执行的
- 大家可以看到，这里执行分组聚合的之后，是使用index_x1这个索引来进行的，type是index，意义是扫描了index_x1这个索引树的所有叶子节点，把x1相同值的个数都统计出来就可以了
- 然后外层循环是第一个指向计划，select_type是一个PRIMARY，针对table是，就是一个子查询结果集物化形成的临时表，它是直接针对这个物化临时表进行了全表扫描根据where条件进行筛选的
小结：
我们都知道，SQL执行计划里有一个id的概念。这个id是什么意思呢？简单来说，有一个SELECT子句就会对应一个id，如果有多个SELECT那么就会对应多个id。但是往往有时候一个SELECT语句涉及到了多个表，所以会对应多个执行计划，此时可能多条执行计划的id是一样的
对于select_type：
- 一般如果是单表查询或者代表查询，它们的select_type都是SIMPLE，意思就是简单的查询
- 然后如果是union语句的话，就是类似于
select * from t1 union select * from t2，那么会对应两条执行计划，第一条执行计划是针对t1表的，select_type是PRIMARY，第二条执行计划是针对t2表的，select_type是UNION，这就是在出现union语句的时候，他们就不一样了。 - 在使用UNION语句的时候，会有第三条执行计划，它的意思是针对两个查询的结构依托一个临时表去重，因此第三条执行计划的select_type就是union_result
- 如果是在SQL里有子查询，类似于
select * from t1 where x1 in (select x1 ffrom t2) or x3='xxx'，此时其实会有两条执行计划，第一条执行计划的select_type是PRIMARY，第二条执行计划的select_type是SUBQUER
type分析
其实select_type并不是很关键，因为他主要是代表了大SQL里的不同的SELECT代表了一个什么角色，比如有的SELECT是PRIMARY查询，有的是UNION，有的是SUBQUERY。
关键的是type，它直接决定了对某个表示如何从里面查询数据的，查询方式包括const、ref、range、index、all这几种方式，分别是根据主键/唯一索引查询，根据二级索引查询，对二级索引进行全索引扫描，对聚簇索引进行全表扫描。
看个例子：
select * fromt1 where id=110对于上面的SQL，直接根据主键进行等值匹配查询，那执行计划里的type就会是const，意思是极为快速，性能几乎是线性的。
事实上也非常快，因为主键值是不会重复的，这个唯一值匹配，在一个索引树里跳转查询，基本上几次磁盘IO就可以定位到了
下一个例子：
EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id这里是通过两个表的id进行关联查询的，此时他的执行计划如下：
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |
filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3467 | 100.00 |
NULL |
| 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 10 | test_db.t1.id | 1 |
100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+- 可以看到，针对t1表示一个全表扫描，这个是必然的，因为关联的时候会先查询一个驱动表，这里是t1，它没什么where筛选条件，自然只能是全表扫描出来所有的数据了
- 接着针对t2表的查询type是eq_ref，而且使用了PRIMARY主键。这个意思是说，针对t1表全表扫描获取到的每条数据，打回去t2表里基于主键进行等值匹配，此时会在t2表的聚簇索引里根据主键值进行快速查询，所以在连接查询时，针对被驱动表如果基于主键进行等值匹配，那么它的查询方式是eq_ref
- 而如果要是正常基于某个二级索引进行等值匹配的时候，type就会是ref，而如果基于二级索引查询的时候允许值为null，那么查询方式就会是ref_or_null
- 有一些特殊场景下针对单表查询可能会基于多个索引提取数据后进行合并，此时查询方式会是index_merge这种。
- 查询方式是range的话就是基于二级索引进行范围查询
- 查询方式是index的时候是直接扫描二级索引的叶子节点，也就是扫描二级索引里的每条数据
- 最后如果是all的话就是全表扫描，也就是对聚簇索引的叶子节点扫描每条数据。
const 与ref
- 我们知道，类似
select * from table where id = xx，或者select * from table where name = x的语句，直接就可以通过聚簇索引或者二级索引+聚簇索引回源，轻松查到你要找的数据，这种根据索引直接可以快速查找数据的过程，在执行计划里叫做const，意思是性能超高的常量级。所以执行计划里的const就是直接通过索引定位到数据，速度极快。 - 但是这里有一个要点，你的二级索引必须是唯一索引，才是属于const的。也就是说你必须建立unique key唯一索引，保证一个二级索引的每一个值都是唯一的，才可以。
- 那么如果你是一个普通的二级索引呢？就是个普通KEY索引，这个时候的
select * from table where name = x的数据，name是一个普通二级索引，不是唯一索引，那么此时这种查询速度也是很快的，它在执行计划里叫做ref。 - 如果你是包含多个列的普通索引的话，那么必须是从索引最左侧开始连续多个列都是等值比较才可以是ref方式，就是类似select * from table where name = x and age = x and xx = xx，然后索引可能是KEY(name，age，xx)
- 然后有一个例外，就是如果你用name IS NULL这种语法的话，即使name是主键或者唯一索引，还是只能走ref方式。但是如果你是针对一个二级索引同时比较了一个值还有限定了IS NULL，类似于select * from table where name = x and nand IS NULL，那么此时在执行计划里就叫做ref_or_null。
- 说白了，就是在二级索引里搜你要的值以及是NULL的值，然后再回源去聚簇索引里查而已。因为同时有索引等值比较以及NULL值查询，就叫做ref_or_null
小结：
- 执行计划中的const，那么肯定是通过主键或者唯一索引来访问，速度极高
- 执行计划中的ref，就是使用了普通索引，或者用主键/唯一索引搞了一个IS NULL/IS NOT NULL，速度也很快
range
- range，顾名思义，就是你的SQL里有范围查询的时候就会走这个方式。
- 比如写一个select * from table where age >= xx and age <= x，假设age就是一个普通索引，此时就必然会利用索引来进行范围筛选，一单利用索引做了范围索引，那么这种方式就是range
对比：
- const、ref、range都是基于索引在查询，总之就是走索引，所以一般问题不是太大，除非你通过索引查出来的数据量太多了。比如范围查询一下子找出了10万条数据就会搞死MySQL。
index
假设我们有一个表，里面完整的字段联合索引是KEY(x1,x2,x3)，好，现在一个SQL语句是select x1,x2,x3 from table where x2=xxx，这就完蛋了，x2不是联合索引的最左侧的那个字段，因此这个SQL是没办法直接从联合索引的索引树的根节点开始二分查找，快速一层一层跳转的。那么它会怎么执行呢？
- 观察上面SQL语句，我们可以发现这份SQL里要查的几个字段，都是联合索引里的几个字段。所以针对这种SQL，实际查询的时候，就会直接遍历KEY(x1，x2，x3)这个联合索引的索引树的叶子节点。
- 我们知道，聚簇索引的叶子节点放的是完整的数据页，里面包含完整的一行一行的数据；联合索引的叶子节点放的也是页，但是页里每一行就只有x’1、x2、x3和主键的值。
- 以此时针对这个SQL，会直接遍历KEY(x1, x2, X3)索引树的叶子节点的那些页，一个接一个的遍历，然后找到x2=xxx的那个数据，把里面的x1, x2, x3三个字段的值直接提取出来。这个遍历二级索引的过程，要比遍历聚簇索引块多了，毕竟二级索引叶子节点就包含几个字段的值，比聚簇索引叶子节点小多了，所以速度也快。
- 也就是说，此时只要遍历一个KEY(x1, x2, x3)索引就可以了，不需要回源到聚簇索引里去。针对这种只要遍历二级索引就可以拿到你想要的数据，而不需要回源到聚簇索引的访问方式，就叫做index访问方式
对比：
- const、ref和range，本质都是基于索引树的二分查找和多层跳转来查询，所以性能一般是很高的。
- index，速度就比上面三种要差一点了。因为它是走遍二级索引树的叶子节点的方式来进行的，那肯定比索引树的二分查找要慢多了，但是比全表扫描还是要好的
possible_keys && key && key_len && ref
- possible_keys就是针对每一个表进行查询的时候有哪些潜在的可以使用的索引
- 比如你有两个索引，一个是KEY(x1, x2, x3)，一个是KEY(x1, x2, x4)，此时要是在where条件里要根据x1和x2两个字段进行查询，那么此时明显是上述两个索引都可以使用的，那么到底要使用哪个呢？
- 此时就需要通过成本优化方法，去估算使用两个索引进行查询的成本，看使用哪个索引的成本更低，那么就选择用那个索引，最终选择的索引，就是执行计划里的key这个字段的值了
- 而key_len，其实就是当你在key里选择使用某个索引之后，那个索引里的最大值的长度是多少，这个就是给你一个参考，大概知道那个索引里的值最大能多长。
- 而执行计划里的ref也会相对比较关键一些，当你的查询条件是索引等值匹配的时候，比如const、ref、eq_ref、ref_or_null这些方式的时候，此时执行计划的ref字段告诉你的就是：你的索引列等值匹配的是什么？是等值匹配一个常量值，还是等值匹配另外一个字段的值？
比如SQL语句：
EXPLAIN SELECT * FROM t1 WHERE x1 = 'xxx'他的执行计划是下面这样的：
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |
filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | index_x1 | index_x1 | 589 | const | 468 | 100.00 |NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+- 针对t1表的查询，type是ref方式的，也就是说基于普通的二级索引进行等值匹配。
- 然后possible_keys只有一个，就是index_x1，针对x1字段建立的一个索引，而实际使用的索引也是index_x1
- 然后key_len是589，意思是说index_x1这个索引里的x1字段最大值的长度也就是589个字节，其实这个不算太大，不过基本可以肯定这个x1字段是存储字符串的，因为是一个不规律的长度。
- 比较关键的是ref字段，它的意思是说，比较关键的是ref字段，它的意思是说，既然你是针对某个二级索引进行等值匹配的，那么跟index_x1索引进行等值匹配的是什么？是一个常量或者是别的字段？这里的ref的值是const，意思就是说，是使用一个常量值跟index_x1索引里的值进行等值匹配的。
假设你要是用了类似如下的语句：
EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;此时执行计划里的ref肯定不是const，因为你跟t1表的id字段等值匹配的是另外一个表的id字段，此时ref的值就是那个字段的名称了，执行计划如下：
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |
filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3457 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 10 | test_db.t1.id | 1 |100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+- 针对t1表作为驱动表执行一个全表扫描，接着针对t1表里每条数据都会去t2表根据t2表的主键执行等值匹配，所以第二个执行计划的type是eq_ref，意思就是被驱动表基于主键进行等值匹配，而且使用的索引是PRIMARY就是使用了t2表的主键。
- 至于ref，意思就是说，到底是谁跟t2表的聚簇索引里的主键值进行等值匹配呢？是常量值吗？不是，是test_db这个库下的t1表的id字段，这里跟t2表的主键进行 等值匹配的是t1表的主键id字段，所以ref这里显示的清清楚楚的。
rows&&filtered
- rows顾名思义就是使用指定的查询方式，会查出来多少条数据
- filtered意思就是说，在查询方式查出来的这波数据里再用上其他的不在索引范围里的查询条件，又会过滤出来百分之几的数据。
比如：
EXPLAIN SELECT * FROM t1 WHERE x1 > 'xxx' AND x2 = 'xxx'它只有一个x1字段建了索引，x2子弹是没有索引的。执行计划如下：
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
| 1 | SIMPLE | t1 | NULL | range | index_x1 | index_x1 | 458 | NULL | 1987 | 13.00 |Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+- 针对t1表的查询方式是range，也就是基于索引进行范围查询，用的索引是index_x1，也就是x1字段的索引。
- 然后基于x1>'xxx’这个条件通过index_x1索引查询出来的数据大概是1987条，然后会针对这1987条数据在根据where条件里的其他条件，也就是x2=xxx进行过滤。
- 这个filtered是13.00，意思是估算基于x2='xxx’条件过滤后的数据大概是13%，也就是最终查出来的数据大概是1987*13%=258条左右
extra分析
- 很多人可能认为extra字段是无关紧要的，其实并不是。因为除了extra字段以外的其他内容，最多就是告诉你针对你SQL里的每个表是如何查询的，用了哪个索引，查出来了多少数据，但是很多时候，往往针对一个表并不是那么简单的
- 因为除了基于索引查询数据，可能同时还得基于where条件里的其他过滤条件去筛选数据
- 这个extra里的信息可能会非常多，掌握一些常见的，比较有用的就可以了
比如下面的SQL语句：
EXPLAIN SELECT x1 FROM t1 WHERE x1 = 'xxx'他的执行计划：
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | index_x1 | index_x1 | 456 | const | 25 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+- 首先它是访问了t1表，使用的是ref访问方法，也就是基于二级索引去查找，找到是基于index_x1这个索引，这个索引的最大数据长度是456字节，查找的目标是一个const代表的常量值，通过索引可以查出来25条数据，经过其他条件筛选过后，最终剩下数据时100%
- 而extra中是Using index，它的意思是说这次查询下，仅涉及到一个二级索引，不需要回表，因为它仅仅查出来了x1这个字段，直接从index_x1索引里查就行了
- 如果没有回表操作，仅仅在二级索引里执行，那么extra里会告诉in是Using index。
第二个例子：
SELECT * FROM t1 WHERE x1 > 'xxx' AND x1 LIKE '%xxx'- 此时他会先在二级索引index_x1里查找，查找出来的结果还会额外的跟x1 LIKE '%xxx’条件做比对，如果满足条件的才会被筛选出来，这种情况下，extra显示的是Using index condition。
Using where差不多是extra中最常见的：
- 一般是见于你直接针对一个表扫描，没用到索引，然后where里好几个条件，就会告诉你using where
- 或者是你用了索引去查找，但是除了索引之外，还需要用到其他的字段进行筛选，也会告诉你using where
看个例子：
EXPLAIN SELECT * FROM t1 WHERE x2 = 'xxx'这里的x2是没有建立索引的，所以此时他的执行计划就是下面这样的
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered| Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 4578 | 15.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+- 针对t1表进行查询，用的是全表扫描的方式，没有使用任何索引，然后全表扫描，扫出来5478条数据，这个时候extra中显示了Using where，意思是说，他对每条数据都用了where x2 = xxx去进行筛选。
- 最终filtered告诉了，过滤出来了15%的数据，也就是说从这个表里筛选出来了686条数据
那么如果你的where条件里有一个条件是针对索引列查询的，有一个列是普通列的筛选，类似下面的SQL语句：
EXPLAIN SELECT * FROM t1 WHERE x1 = 'xxx' AND x2 = 'xxx'此时执行计划如下
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | index_x1 | index_x1 | 458 | const | 250 | 18.00 |Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+- 这里针对t1表去查询，先通过ref方式直接在index_x1索引里查找，是跟const代表的常量值去查找，然后查出来250条数据
- 接着用Using where代表的方式，去使用AND x2 = 'xxx’进行筛选，筛选后的数据比例为18%，最终查出来的数据大概是45条
另外，在多表关联的时候，有时候你的关联条件不是索引，而是一种叫做join buffer的内存技术来提升关联的性能。比如下面：
EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.x2 = t2.x2他们的连接条件x2是没有索引的，此时一起看看他的执行计划
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered
| Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 4578 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 3472 | 1.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+- 因为要执行join，那么肯定是先查询t1表的数据，此时是对t1表直接全表查询，查出来4578条数据。
- 接着对每条数据的x2字段的值，跑到t2表里去查对应的数据，进行关联
- 但是此时因为t2表里也没法根据索引来查，所以每次需要对t2进行全表扫描。根据extra提示的Using where，就是根据t1表每条数据的x2字段的值去t2表查找对应的数据了，然后此时就用join buffer技术，在内存里做一些优化，减少t2表的全表扫描次数。
Using filesort：有时候我们在SQL语句里进行排序的时候，如果排序字段是有索引的，那么其实是可以直接从索引里按照顺序去查询这个数据的。
比如：
EXPLAIN SELECT * FROM t1 ORDER BY x1 LIMIT 10这就是典型的一个排序后再分页的语句，他的执行计划如下
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |
filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | index | NULL | index_x1 | 458 | NULL | 10 | 100.00 | NULL
|
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+- 可以看到，这个SQL语句，他是用了index方式访问的，意思就是说直接扫描了二级索引，而且实际使用的索引也是index_x1，本质上来说，他就是在 index_x1索引里，按照顺序找你LIMIT 10要求的10条数据罢了。
- 所以可以看到返回的数据时10条，也没别的过滤条件了，所以filtered是100%，也就是10条数据都返回了
但是如果我们排序的时候没法用到索引，此时就会基于内存或者磁盘文件来排序，大部分时候都是基于磁盘文件来排序。
比如：
EXPLAIN SELECT * FROM t1 ORDER BY x2 LIMIT 10x2字段是没有索引的，此时执行计划如下
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered| Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 4578 | 100.00 | Usingfilesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+- 这个SQL很明确的是，它是基于x2字段来排序的，没法直接根据有序的索引去查找数据，只能把所有数据写入一个临时的磁盘文件，基于排序算法在磁盘文件里按照x2字段的值完成排序，然后再按照LIMIT 10的要求取出来头10条数据。
但是这种把全部数据放到磁盘文件排序的做法相当糟糕，性能会极差。
如果我们用group by，union，distinct之类的语法的时候，万一要是没法直接利用索引来进行分组聚合，那么它会基于临时表来完成，也会有大量的磁盘操作，性能也非常差。
比如：
EXPLAIN SELECT x2, COUNT(*) AS amount FROM t1 GROUP BY x2这里的x2是没有索引的，所以此时的执行计划如下
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 5788 | 100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+- 这个SQL里只能对全表数据放到临时表里做大量的磁盘文件操作，然后才能完成对x2字段的不同的值去分组，分组完了以后对不同x2值的分组去做聚合操作，这个过程也是相当的耗时的，性能是极低的。
其实在进行SQL调优的时候，核心就是分析执行计划里哪些地方出现了全表扫描，或者扫描数据过大，尽可能通过合理优化索引来保证执行计划每个步骤都可以基于索引执行，避免扫描过多的数据
怎么应对索引
- 基础的以及日常的SQL优化就是设计好索引，让一般不太复杂的普通查询都用上索引
- 可能还需要做SQL调优：得到执行计划，就可以根据它的实际情况去想办法改写你的SQL语句，改良你的索引设计，进而优化SQL语句的执行计划，最终让SQL语句的性能得到提升
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
