《MySQL数据库》MySQL 优化SQL（explain）

前言

      如果要写出优质的SQL语句，就需要了解MySQL的存储原理。MySQL是如何分析SQL，如何利用索引查询。

Explain 关键字

explain select * from ic_base;                  --查看SQL的执行情况

id:  执行编号，标识Select的执行顺序，存在子查询等负责查询的时候用来标识执行的优先顺序。

select_type:   select查询语句的类型（simple，primary，subquery，derived，union，union result，dependent union，dependent subquery）。

         > simple : 简单SQL

         > primary : 包含union或者子查询，最外层的部分标记为primary

         > subquery : 非相关子查询（子查询和主表之间有关联关系）

         > derived : 派生表——该临时表是从子查询派生出来的，位于form中的子查询

         > union : 包含union 或者 union all 的查询

         > union result : MySQL建立的临时表需要去掉重复数据

         > dependent union : 顾名思义，首先需要满足UNION的条件，及UNION中第二个以及后面的SELECT语句，同时该语句依赖外部的查询

         > dependent subquery : 相关子查询（子查询和主表之间有关联关系）

         > materialized :当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询。

table: 访问的表名

partitions: 分区信息

type: 访问类型（system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL），innodb中不存在system.一般来说，得保证查询至少达到range级别，最好能达到ref。

         > system : 这是const连接类型的一种特例，表只有一行数据

         > const : 主键索引仅有一行满足条件

         > eq_ref : 被驱动表和驱动表通过驱动表的主键关联。

         > ref : 辅助索引访问，等值匹配或者常量匹配。

         > ref_or_null : 索引访问，等值匹配或者常量匹配，存在null值。

         > index_merge : 合并索引，有多个索引可以选择。

         > unique_subquery : 子查询使用唯一索引

         > index_subquery : 子查询使用索引

         > range : 范围扫描，一个有限制的索引扫描。key列显示使用了哪个索引。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN 和IN 操作符,用常量比较关键字列时。

         > index : 和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。

         > ALL : 全表扫描

possible_keys: 显示能够使用的索引

key: 使用的索引

key_len: 使用的索引长度（使用的索引字段长度之和，字段没有设置非空，需要加1，varchar 字段还要加2）

ref: 关联列或者常数

rows: 数据行数（来自统计信息）

filtered: 过滤百分比，可能满足条数/总条数

Extra: MySQL的补充额外信息

       > no tables used : 没有额外信息

       > Using where : 当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件

       > Using index: 当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况

       > Impossible where: 查询语句的WHERE子句永远为FALSE时将会提示该额外信息

       > Using index condition: 有些搜索条件中虽然出现了索引列，但却不能使用到索引

       > no matching min/max row: 当查询列表处有MIN或者MAX聚集函数，但是并没有符合WHERE子句中的搜索条件的记录

       > Using join buffer(block nested loop) : 在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join

buffer的内存块来加快查询速度

       > Using filesort : 很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort）

       > Using temporary : 在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含DISTINCT、GROUP BY、UNION等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询

       > start temporary , end temporary: 查询优化器会优先尝试将IN子查询转换成semi-join，而semi-join又有好多种执行策略，当执行策略为DuplicateWeedout时，也就是通过建立临时表来实现为外层查询中的记录进行去重操作时，驱动表查询执行计划的Extra列将显示Start temporary提示，被驱动表查询执行计划的Extra列将显示End temporary提示

       > FirstMatch(表名) : 在将In子查询转为semi-join的去重的方式（找到一条就返回）

查询优化器

set optimizer_trace="enabled=on";       --打开查询优化器

---------------需要执行的SQL（可以使用explain）-------------------------

select * from information_schema.OPTIMIZER_TRACE     --查询优化器的执行结果

set optimizer_trace="enabled=off";       --关闭查询优化器

查询优化器查询的结果

query: 对应SQL.

trace : 优化过程（里面有行估算，走索引，最左前缀等一些计算成本的过程，然后MySQL自动选择一种最优方式）。

成本计算公式：全表扫描的公式： 主键索引页数*1 + 行数*0.2 = Data_length/16K + Rows*0.2 ;    主键索引页数，行数来自统计信息， 其他数据为MySQL固定常量

由于MySQL的成本计算也是估算（99%是靠谱的），但是当遇到很复杂的SQL，或者统计信息与实际情况差距太大的时候，会导致MySQL的优化过程出现问题。

这个时候我们需要干扰MySQL的执行计划，强制走我们需要的索引或者更新统计信息。

索引失效

1. 隐式转换，不走索引

表ic_website的url 创建了一个普通索引，并且该字段是varchar类型，会出现一下情况：

explain select * from ic_website where url = '123';    --  索引有效
explain select * from ic_website where url = 123;    --  索引失效

2. <>,not in 不走普通索引，主键索引还是可以走索引。

explain select * from ic_website where url <> '123';

3. 左边有% 的不走索引

explain select * from ic_website where url like '%123';

实战

一、ANALYZE TABLE  ic_base;   -- 重新收集ic_base 的统计信息，保证MySQL优化器计算准确。 查看统计信息是否被更新：select * from mysql.innodb_table_stats;

select * from mysql.innodb_index_stats; 

二、hints 

USE INDEX：限制索引的使用范围，们在数据表里建立了很多索引，当MySQL对索引进行选择 时，这些索引都在考虑的范围内。但有时我们希望MySQL只考虑几个索引，而不是全部的索引， 这就需要用到USE INDEX对查询语句进行设置。

IGNORE INDEX ：限制不使用索引的范围

FORCE INDEX：我们希望MySQL必须要使用某一个索引(由于 MySQL在查询时只能使用一个索 引，因此只能强迫MySQL使用一个索引)。这就需要使用FORCE INDEX来完成这个功能。

hints 语法：

SELECT * FROM table1 USE|IGNORE|FORCE INDEX (col1_index,col2_index) WHERE col1=1 AND
col2=2 AND col3=3;

三、straight_join   -- 强制改成左边为驱动表

select t1.*  from Table1 t1 STRAIGHT_JOIN  Table2 t2
on t1.CommonID = t2.CommonID  where t1.FilterID = 1

总结

SQL优化伴随开发的全部过程，随着时间和业务的推移，一些SQL的性能跟不上了，需要重新整理和优化。

笔者遇到过好几千行的SQL（union,子查询，嵌套查询，函数，分组，排序）几乎用了SQL的全部特性，优化这类SQL第一要熟悉SQL的作用，第二点要将这类SQL拆小，先找到慢的地方，然后再考虑优化。优化SQL是一个需要耐心的活，平时一定要注意SQL写法，尽量避免一些糟糕的写法，减少后期的维护。