持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第10天，点击查看活动详情

一般开发一个系统，都是先设计表结构，表结构必须满足业务需求，然后写代码，代码都写好之后，再根据代码如何查询表的，来设计里面的索引，考虑设计几个索引，是不是联合索引，选择哪些字段，字段顺序如何排列，才能让查询语句都用上索引。

根据查询语句设计良好的索引，让所有查询都尽可能用上索引，这本身就是一种SQL优化的技巧，但是他仅仅只是其一罢了，并不能说掌握这个，就掌握了所有的SQL优化技巧。SQL优化还有很多其他的东西。

面试官：SQL执行计划你有了解过吗？

SQL执行计划

有时候往往会发现自己的数据库里有很多表，每个表的数据量也不小，然后写出来的SQL也比较复杂，各种关联和嵌套子查询，搞的人看的都眼晕，然后表面上看起来这个SQL部分用上了索引，结果性能还是差，这是为什么呢？基础的以及日常的SQL优化就是设计好索引，让一般不太复杂的普通查询都用上索引，但是针对复杂表结构和大数据量的上百行复杂SQL的优化，必须得先懂这个复杂SQL是怎么执行的。

那么多的数据表，每个表都有一个聚簇索引，聚簇索引的叶子就是那个表的真实数据，同时每个表还设计了一些二级索引，那么上百行的复杂SQL跑起来的时候到底是如何使用各个索引，如何读取数据的？这个SQL语句（不管是简单还是复杂），在实际的MySQL底层，针对磁盘上的大量数据表、聚簇索引和二级索引，如何检索查询，如何筛选过滤，如何使用函数，如何进行排序，如何进行分组，到底怎么能把想要的东西查出来，这个过程就是一个很重要的东西：执行计划！

也就是说，每次提交一个SQL给MySQL，内核里的查询优化器，都会针对这个SQL语句的语义去生成一个执行计划，这个执行计划就代表了，他会怎么查各个表，用哪些索引，如何做排序和分组，看懂执行计划之后，还能根据他的实际情况去想各种办法改写SQL语句，改良索引设计，进而优化SQL语句的执行计划，最终让SQL语句的性能得到提升，这个就是所谓的SQL调优。

执行计划包含的信息

面试官：执行计划里具体包含哪些信息呢？

id

select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序。 id号分为三种情况：

如果id相同，那么执行顺序从上到下。
如果id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
id相同和不同的，同时存在：相同的可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行

select_type

主要用来分辨查询的类型，是普通查询还是联合查询还是子查询。

sample:简单的查询，不包含子查询和union。
primary:查询中若包含任何复杂的子查询，最外层查询则被标记为Primary。
union:若第二个select出现在union之后，则被标记为union。
dependent union:跟union类似，此处的depentent表示union或union all联合而成的结果会受外部表影响。
union result:从union表获取结果的select。
subquery:在select或者where列表中包含子查询。
dependent subquery:subquery的子查询要受到外部表查询的影响。
derived: from子句中出现的子查询，也叫做派生类。
uncacheable subquery:表示使用子查询的结果不能被缓存。
uncacheable union:表示union的查询结果不能被缓存。

table

对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集。

如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名。
表名是derivedN的形式，表示使用了id为N的查询产生的衍生表。
当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id。

type

显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

all:全表扫描，一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。
index:全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序。
range:表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符: =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()。
index_subquery:利用索引来关联子查询，不再扫描全表。
unique_subquery:该连接类型类似与index_subquery,使用的是唯一索引。
index_merge:在查询过程中需要多个索引组合使用。
ref_or_null:对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式。
ref:使用了非唯一性索引进行数据的查找。
eq_ref:使用唯一性索引进行数据查找。
const:这个表至多有一个匹配行。
system:表只有一行记录（等于系统表），这是const类型的特例，平时不会出现。

possible_keys

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

key

实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

key_len

表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

rows

根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好

extra

包含额外的信息。

using filesort:说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置。
using temporary:建立临时表来保存中间结果，查询完成之后把临时表删除。
using index:这个表示当前的查询是覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找。
using where:使用where进行条件过滤。
using join buffer:使用连接缓存。
impossible where:where语句的结果总是false。

面向面试编程：MySQL的执行计划（一）