mysql优化（Explain分析）

1.概念

①MySQL 内优化器

mysql体系结构中，包含sql解析器，优化器等组件，sql解析器解析sql之后，生成解析树，经过验证，解析树如果正确后，由优化器进一步优化解析树，最终形成一个执行计划（profile）

②Explain 分析

使用explail关键字可以模拟优化器执行sql语句的查询，从而知晓mysql是如何处理你的sql语句的，分析你的查询语句或是表结构的瓶颈

explain各个参数的介绍：

id:在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type:SELECT关键字对应的那个查询的类型
table:表名
partitions:匹配的分区信息
type:针对单表的访问方法
possible_keys:可能用到的索引
key:实际用到的索引
key_len:实际使用到的索引的长度
ref:当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows:预估的需要读取的记录条数
filtered:某个表经过搜索条件过滤后剩余记录条数的百分比
Extra:一些额外的信息

如图：

2.Explain 分析各个列名对应的解释：

1.id字段的含义

- 一个 select 对应一个 id 唯一的值
- id 值每出现一次，就代表执行 SQL 语句的一个大步骤
- 总体的 SQL 语句执行的『大步骤』越少越好
- 多个 id 值
    - id 值相同：相同 id 值中包含的多条记录可以理解为执行这个大步骤时的各个『小步骤』。按照从上到下的顺序依次执行
    - id 值不同：代表执行这条 SQL 语句需要有多个大步骤，这些大步骤按照 id 值从大到小的顺序值。

这个是小步骤一般情况下是在本表查询

 当然如果在查询语句中加入了子查询就会出现大步骤：

 

2.select_type 字段

其中可能会包含很多个 select 关键字。每一个 select 代表整个 SQL 语句执行计划中的一次小的查询，而每一个 select 关键字的每一次查询都有可能是不同类型的查询。

select_type 字段就是用来描述每一个 select 关键字的查询类型，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色。

总体介绍字段取值含义：

 3.table 字段

显示当前这一步查询操作所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是别名。不论我们的查询语句有多复杂，里边儿包含了多少个表，到最后也是需要对每个表进行单表访问的，所以 MySQL 规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。

4.partitions 字段

代表分区表中的命中情况。如果是非分区表，该项为 null。逻辑上是一个整体的数据，可以在物理层保存时，拆分成很多个分片。分片在分区中保存。数据分片的好处是：

• 方便在很多个不同分区之间方便的移动分片，分摊负载，弹性扩容。
• 给主分片创建复制分片可以增强数据安全性。、

 5.type 字段 [重要]

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。具体取值参见下表（从上到下，性能越来越好）：

 有一下几种情况

 

 

 

 

 

6.possible_keys 字段

在查询中可能会用到的索引列，如果没有用到索引列就放回null

7.key 字段

key字段显示本次查询真正用到的索引，包含在 possible_keys 中

8.key_len 字段[重要]

key_len 表示索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要，值越大索引的效果越好——因为值越大说明索引被利用的越充分。

字节数计算方式：

- 索引对应字段类型：
    - 数值类型：最终结果就是数值类型字段宽度（我们关心的是字节数）本身
        - int(11)：我们关心的是 int 类型占 4 个字节，而不关心 11
        - double(10,5)：我们关心的是 double 类型占 8 个字节，而不关心 10,5
    - 字符串类型：查看字符集类型
        - UTF-8：需要给字段长度 × 3
        - GBK：需要给字段长度 × 2
    - 如果是 varchar 这样的**变长**字符串类型：再 + 2
    - 如果是允许为空的字段：再 + 1

举例：customer_name 字段声明的类型是 varchar(200)，允许为空。

200 × 3 + 2 + 1 = 603

9.ref 字段

在查询中，表示我们的索引列跟谁去比较，是常量还是另外的一张表的字段

 

 

10.rows 字段

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。从优化 SQL 语句的角度来说，这个值通常越小越好。 

11.filtered 字段

通过存储引擎从硬盘加载数据到服务层时，受限于内存空间，有可能只能加载一部分数据。filtered 字段显示的值是：已加载数据 / 全部数据 的百分比。只是不显示百分号。

12.extra 字段

顾名思义，Extra 列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，我们就不一个一个介绍了，所以我们只挑比较重要的额外信息介绍给大家。

using where：
    不用读取表中所有信息，仅通过索引就可以获取所需数据。  
    言外之意是 select 查询的字段都带有索引。  
    不管 select 查询多少个字段，这些字段都在索引中。
Using temporary：
    表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
Using filesort：
    当语句中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”  
    这里的文件指的是保存在硬盘上的文件。  
    之所以会用到硬盘，是因为如果查询的数据量太大，内存空间不够，需要在硬盘上完成排序。  
    如果确实是很大数据量在硬盘执行排序操作，那么速度会非常慢。

13.使用建议

• EXPLAIN 不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN 不考虑各种 Cache
• EXPLAIN 不能显示 MySQL 在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN 只能解释 SELECT 操作，其他操作要重写为 SELECT 后查看执行计划