MySQL 语句优化

1.打开慢日志开关

因为开启慢查询日志是有代价的（跟 bin log、optimizer-trace 一样），所以它默认是关闭的：

//查询慢日志是否开启
show variables like 'slow_query%';
//查询慢日志配配置时间
show variables like '%long_query%';

//直接动态修改参数（重启后失效）。
set @@global.slow_query_log=1; -- 1 开启，0 关闭，重启后失效 
set @@global.long_query_time=3; -- mysql 默认的慢查询时间是 10 秒，另开一个窗口后才会查到最新值 

//或者修改配置文件 my.cnf。以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径。
slow_query_log = ON 
long_query_time=2 
slow_query_log_file =/var/lib/mysql/localhost-slow.log

2.慢日志分析

1、日志内容

show global status like 'slow_queries'; -- 查看有多少慢查询 
show variables like '%slow_query%'; -- 获取慢日志目录

cat /var/lib/mysql/ localhost-slow.log

2、mysqldumpslow

官方文档：https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

MySQL 提供了 mysqldumpslow 的工具，在 MySQL 的 bin 目录下。

mysqldumpslow --help

//查询用时最多的 10 条慢 SQL
mysqldumpslow -s t -t 10 -g 'select' /var/lib/mysql/localhost-slow.log

//Count 代表这个 SQL 执行了多少次；
//Time 代表执行的时间，括号里面是累计时间；
//Lock 表示锁定的时间，括号是累计；
//Rows 表示返回的记录数，括号是累计。

3.执行计划

官方文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

//explain 语法
EXPLAIN [sql]

1.id

2.select type

官方文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types

这里并没有列举全部（其它：DEPENDENT UNION、DEPENDENT SUBQUERY、MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION）。

下面列举了一些常见的查询类型：

SIMPLE

简单查询，不包含子查询，不包含关联查询 union。

PRIMARY

子查询 SQL 语句中的主查询，也就是最外面的那层查询。

SUBQUERY

子查询中所有的内层查询都是 SUBQUERY 类型的。

DERIVED

衍生查询，表示在得到最终查询结果之前会用到临时表
对于关联查询，先执行右边的 table（UNION），再执行左边的 table，类型是DERIVED

UNION

用到了 UNION 查询。同上例。

UNION RESULT

主要是显示哪些表之间存在 UNION 查询。<union2,3>代表 id=2 和 id=3 的查询存在 UNION。同上例。

3.type-连接类型

所有的连接类型中，上面的最好，越往下越差。
在常用的链接类型中：system > const > eq_ref > ref > range > index > al
l这 里 并 没 有 列 举 全 部 （ 其 他 ： fulltext 、 ref_or_null 、 index_merger 、unique_subquery、index_subquery）。

以上访问类型除了 all，都能用到索引。

const

主键索引或者唯一索引，只能查到一条数据的 SQL。

system

system 是 const 的一种特例，只有一行满足条件。例如：只有一条数据的系统表。

EXPLAIN SELECT * FROM mysql.proxies_priv;

eq_ref

通常出现在多表的 join 查询，表示对于前表的每一个结果,，都只能匹配到后表的一行结果。一般是唯一性索引的查询（UNIQUE 或 PRIMARY KEY）。
eq_ref 是除 const 之外最好的访问类型。

小结：

以上三种 system，const，eq_ref，都是可遇而不可求的，基本上很难优化到这个状态。

ref

查询用到了非唯一性索引，或者关联操作只使用了索引的最左前缀。

range

索引范围扫描。
如果 where 后面是 between and 或 <或 > 或 >= 或 <=或 in 这些，type 类型就为 range。
不走索引一定是全表扫描（ALL），所以先加上普通索引。
执行范围查询（字段上有普通索引）：
IN 查询也是 range（字段有主键索引）

index

Full Index Scan，查询全部索引中的数据（比不走索引要快）。

all

Full Table Scan，如果没有索引或者没有用到索引，type 就是 ALL。代表全表扫描。

小结：

一般来说，需要保证查询至少达到 range 级别，最好能达到 ref。
ALL（全表扫描）和 index（查询全部索引）都是需要优化的。

4.possible_key、key

可能用到的索引和实际用到的索引。如果是 NULL 就代表没有用到索引。
possible_key 可以有一个或者多个，可能用到索引不代表一定用到索引。
反过来，possible_key 为空，key 可能有值吗？
如果通过分析发现没有用到索引，就要检查 SQL 或者创建索引。

5.key_len

索引的长度（使用的字节数）。跟索引字段的类型、长度有关。

6.rows

MySQL 认为扫描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越好。

7.filtered

这个字段表示存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数量的比例，它是一个百分比。

8.ref

使用哪个列或者常数和索引一起从表中筛选数据。

9.Extra

执行计划给出的额外的信息说明。

10.using index

用到了覆盖索引，不需要回表。

11.using where

使用了 where 过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要在 server 层进行过滤（跟是否使用索引没有关系）。

12.using filesort

不能使用索引来排序，用到了额外的排序（跟磁盘或文件没有关系）。需要优化。（复合索引的前提）

12.using temporary

用到了临时表。例如（以下不是全部的情况）：
1、distinct 非索引列
2、group by 非索引列

总结一下：

模拟优化器执行 SQL 查询语句的过程，来知道 MySQL 是怎么处理一条 SQL 语句的。通过这种方式我们可以分析语句或者表的性能瓶颈。

分析出问题之后，就是对 SQL 语句的具体优化。