MySql 优化全集
前言：在日常工作中，我们需要对查询较慢的SQL进行分析，这时我们就会用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描。
-- 分析SQL EXPLAIN SELECT * FROM zy_ticket_sw_classify WHERE industry_name = '石油开采'；
expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
描述：
id：选择标识符；如果explain的结果包括多个id值，则数字越大越先执行；而对于相同id的行，则表示从上往下依次执行。
select_type：表示查询的类型；
| 查询类型 | 作用 |
|---|---|
| SIMPLE | 简单的SELECT语句（不包括UNION操作或子查询操作） |
| PRIMARY | 查询中最外层的SELECT（如两表做UNION或者存在子查询的外层的表操作为PRIMARY，内层的操作为UNION） |
| UNION | 在UNION中的第二个和随后的SELECT被标记为UNION。如果UNION被FROM子句中的子查询包含，那么它的第一个SELECT会被标记为DERIVED。 |
| DEPENDENT UNION | UNION中的第二个或后面的查询，依赖了外面的查询；（内层的SELECT语句与外层的SELECT语句有依赖关系） |
| UNION RESULT | UNION操作的结果，id值通常为NULL |
| SUBQUERY | 子查询中第一个SELECT（如果有多个子查询存在） |
| DEPENDENT SUBQUERY | 子查询中第一个SELECT，但依赖于外层的表（如果有多个子查询存在） |
| DERIVED | 被驱动的SELECT子查询（子查询位于FROM子句），MySQL会递归执行并将结果放到一个临时表中。MySQL内部将其称为是Derived table（派生表），因为该临时表是从子查询派生出来的 |
| DEPENDENT DERIVED | 派生表，依赖了其他的表 |
| MATERIALIZED | 被物化的子查询 |
| UNCACHEABLE SUBQUERY | 子查询，对于外层的主表，该子查询不可被物化也就是说结果无法缓存，针对外部查询的每一行重新评估，每次都需要计算（耗时操作）； |
| UNCACHEABLE UNION | UNION属于UNCACHEABLE SUBQUERY的第二个或后面的查询；内层的不可被物化的子查询（类似于UNCACHEABLE SUBQUERY） |
特别注意：DEPENDENT SUBQUERY；1 会严重消耗性能；2 不会进行子查询，会先进行外部查询,生成结果集,再在内部进行关联查询；3 子查询的执行效率受制于外层查询的记录数 ;4 可以尝试改成join查询。
table：输出结果集的表
partitions：当前查询匹配记录的分区。对于未分区的表，返回null
type：表示表的连接类型；性能从好到坏排序；
| 连接类型 | 说明（性能自上往下为从好到坏的排序） |
|---|---|
| system | 该表只有一行（相当于系统表），system是const类型的特例 |
| const | 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可 |
| eq_ref | 当使用了索引的全部组成部分，并且索引是PRIMARY KEY或UNIQUE NOT NULL 才会使用该类型，性能仅次于system及const。 |
| ref | 当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。 |
| fulltext | 全文索引 |
| ref_or_null | 该类型类似于ref，但是MySQL会额外搜索哪些行包含了NULL。这种类型常见于解析子查询 |
| index_merge | 此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引 |
| unique_subquery | 该类型和eq_ref类似，但是使用了IN查询，且子查询是主键或者唯一索引。 |
| index_subquery | 和unique_subquery类似，只是子查询使用的是非唯一索引 |
| range | 范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。 |
| index | 全索引扫描，和ALL类似，只不过index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型。 |
| all | 全表扫描，性能最差。 |
possible_keys：展示当前查询可以使用哪些索引，这一列的数据是在优化过程的早期创建的，因此有些索引可能对于后续优化过程是没用的。
key：表示实际使用的索引
key_len：索引字段的长度；由于存储格式，当字段允许为NULL时，key_len比不允许为空时大1字节。
ref：列与索引的比较；如果ref是一个函数，则使用的值是函数的结果。要想查看是哪个函数，可在EXPLAIN语句之后紧跟一个SHOW WARNING语句。
rows：扫描出的行数(估算的行数)，数值越小越好。
filtered：按表条件过滤的行百分比，表示符合查询条件的数据百分比，最大100。用rows × filtered可获得和下一张表连接的行数。例如rows = 1000，filtered = 50%，则和下一张表连接的行数是500。
Extra：执行情况的描述和说明；
| Extra类型 | 说明及优化方案 |
|---|---|
| Using filesort |
MySQL有两种方式可以生成有序的结果，通过排序操作或者使用索引;当Extra中出现了Using filesort 说明MySQL使用了后者，这类SQL语句性能极差，需要进行优化。
典型的，在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。
|
| Using temporary | 需要建立临时表(temporary table)来暂存中间结果。这类SQL语句性能较低，需要进行优化。典型的，group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。 |
| Not exists | MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 就不再搜索了。 |
| Using index | QL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录。这类SQL语句往往性能较好。 |
| Using index condition | 确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。这类SQL语句性能也较高，但不如Using index。 |
| Using where | SQL使用了where条件过滤数据。常见的优化方法为，在where过滤属性上添加索引。（1）返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需要进行优化； （2）使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接类型）来综合判断； |
| Using join buffer | 使用了连接缓存：Block Nested Loop，连接算法是块嵌套循环连接;Batched Key Access，连接算法是批量索引连接。这类SQL语句性能往往也较低，需要进行优化。 典型的，两个关联表join，关联字段均未建立索引，就会出现这种情况。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。 |
| impossible where | where子句的值总是false，不能用来获取任何元组 |
| select tables optimized away | 在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。 |
| distinct | 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作 |
