MySQL优化之执行计划

1.MySql优化可以优化哪些方面

（1）表设计（结构）上

       范式，存储引擎，字段类型

（2）功能上

       索引，分区，缓存

（3） sql语句上

       合理sql（经验）

（4）架构（配置）上

       分库分表

       主从复制

       读写分离

       负载均衡

2.Mysql执行计划

explain各列字段及说明

0. id：

编号

id值相同：

从上往下，顺序执行

表的执行顺序，因数量的个数改变而改变的原因（即：数据量小的表，优先查询）：笛卡儿积

id值不同：

id值越大越优先查询（本质：在嵌套子查询时，先查内存，再查外层）

1. select_type：

查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询。

- SIMPLE：简单的select查询（查询中不包含子查询或者union）；

- PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary；

- SUBQUERY：在select 或 where列表中包含了子查询；

- DERIVED：在from列表中包含的子查询被标记为derived（衍生），使用到了临时表；

- UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived；

- UNION RESULT：从union表获取结果的select；

2. table：

显示这一行的数据是关于哪张表的

3. type：

索引类型，比较重要的列，显示连接使用了何种类型。从最好到最差的连接类型为：

system > const > eq_ref > ref > range > index > all

要对type进行优化的前提：有索引

- system：（忽略）只有一条数据的系统表；或衍生只有一条数据的主查询；

- const：（表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所以很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const）；

- eq_ref：（唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描）；

- ref：（非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体 ）；

- range：（只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引）；in有时候会失效，从而转为无索引。

- index：（Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，应为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取） ）；

- all：（Full Table Scan，遍历全表以找到匹配的行；

4. possible_key

显示可能用在这张表中的索引。如果为空，没有可能的索引。

5. key

实际使用的索引。如果为NULL，则没有使用索引。

6. key_len

使用索引的长度。在不损失精确性的情况下，长度越短越好。

7. ref

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

8. rows

MYSQL认为必须检查的用来返回请求数据的行数

9. extra

不适合在其他字段中显示，但是十分重要的额外信息。如下两个值需注意：

using filesort

需要“额外”的一次排序（查询），看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。

（1）对于单索引

如果排序和查找同一个字段，会出现using filesort

（2）对于复合索引

不能跨列（最佳左前缀），跨列会出现using filesort

如：

alert table test02 add index idx_a1_a2_a3(a1,a2,a3)

explain select * from test02 where a1='' order by a3; // using filesort，跨了a2

explain select * from test02 where a2='' order by a3; // using filesort，跨了a1

explain select * from test02 where a1='' order by a2; // 不会出现

using temporary

性能损耗大，用到了临时表，看到这个的时候，查询需要优化了。一般出现在group by语句中，已经有表了，需要额外使用一个临时表

如：

explain select * from test03 where a2 = 2 and a4 = 4 group by a2,a4; // 没有using temporary

explain select * from test03 where a2 = 2 and a4 = 4 group by a3; // using temporary

using index

性能提升了，索引覆盖。原因：不读取原文件，只从索引文件中获取数据（不需要回表查询）

只要使用到的列，全部都在索引中，就是索引覆盖

如：age是索引列

explain select age from test02 where age = 20;// 不需要回原表查询

using where

如：age是索引列

explain select age,name from test02 where age = 20;// 需要回原表查询

如：a1、a2是联合索引

explain select a1,a3 from test02 where a3 = '';

impossible where

where 永远不可能