MySQL基础9——SQL优化，慢日志查询

SQL优化

可查看文档：MySQL :: MySQL 5.7 Reference Manual :: 8 Optimization

EXPLAIN

用来查看SQL语句的具体执行过程。

原理：模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。

执行计划

Column	Meaning
id	The SELECT identifier （查询id）
select_type	The SELECT type （查询类型）
table	The table for the output row （输出结果集的表）
partitions	The matching partitions （匹配的分区）
type	The join type （表的连接类型）
possible_keys	The possible indexes to choose（可能使用的索引）
key	The index actually chosen （实际使用的索引）
key_len	The length of the chosen key （索引字段的长度）
ref	The columns compared to the index （列与索引的比较）
rows	Estimate of rows to be examined （预估扫描行数）
filtered	Percentage of rows filtered by table condition （按表条件过滤的行百分比）
extra	Additional information （额外信息，如是否使用索引覆盖）

id

select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序；
id号分为三种情况：

id号分为三种情况：

• 1.id相同，那么执行顺序从上到下；
• 2.id不同，id越大越先执行；
• 3.id有相同的也有不同的，id相同的按 1 执行，id不同的按 2 执行；

select_type

主要用来分辨查询的类型，是普通查询还是联合查询还是子查询

select_type Value	Meaning
SIMPLE	Simple SELECT (not using UNION or subqueries) （简单查询-没有联合查询 和子查询）
PRIMARY	Outermost SELECT (最外层select)
UNION	Second or later SELECT statement in a UNION （若第二个select出现在 union之后，则被标记为union）
DEPENDENT UNION	Second or later SELECT statement in a UNION, dependent on outer query （union或union all联合而成的结果会受外部表影响）
UNION RESULT	Result of a UNION. （从union表获取结果的select）
SUBQUERY	First SELECT in subquery （在select或者where列表中包含子查询）
DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query（subquery的子查询 要受到外部表查询的影响）
DERIVED	Derived table（ from子句中出现的子查询，也叫做派生类）
UNCACHEABLE SUBQUERY	A subquery for which the result cannot be cached and must be re evaluated for each row of the outer query（表示使用子查询的结果不能被 缓存）
UNCACHEABLE UNION	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)（表示union的查询结果不能被缓 存：sql语句未验证）

table

对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集;

• 1. 具体表名或者表的别名，从具体的物理表中获取数据；
• 2. 表明为derivedN的形式，表示 id 为 N 的查询产生的衍生表；
• 3. 当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id;

type

type 显示访问类型；采用怎么样的方式来访问数据 。

效率从好到坏依次为 ：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery >index_subquery > range > index > ALL

ALL ： 全表扫描；如果数据量大则需要进行优化；
index ：全索引扫描这个比 ALL 的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，
即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序；
range ：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了 index 的全
索引扫描，适用的操作符： = , <> , > , >= , < , <= , IS NULL , BETWEEN , LIKE , or IN()
index_subquery ：利用索引来关联子查询，不再扫描全表；
unique_subquery ：该连接类型类似与 index_subquery ，使用的是唯一索引；

index_merge ：在查询过程中需要多个索引组合使用；
ref_or_null ：对于某个字段即需要关联条件，也需要 null 值的情况下，查询优化器会选择这
种访问方式；
ref ：使用了非唯一性索引进行数据的查找；
eq_ref ：使用唯一性索引进行数据查找；
const ：这个表至多有一个匹配行；
system ：表只有一行记录（等于系统表），这是 const 类型的特例；

possible_keys

查询涉及到字段的索引，则这些索引都会列举出来，但是不一定采纳；

key

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

key len

表示索引中使用的字节数；查询中使用的索引长度；在不损失精度的情况下长度越短越好。

ref

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

rows

大致估算出找出所需记录需要读取的行数，反映了sql找了多少条数据，该值越小越好。

extra

额外信息；
using filesort ：使用了文件排序；
using temporary ：建立临时表来保存中间结果，查询完成之后把临时表删除；
using index ：采用覆盖索引，直接从索引中读取数据，而不用访问数据表。如果同时出现using
where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的
查找；
using where ：使用where进行条件过滤；
using join buffer ：使用连接缓存；
impossible where ： where 语句的结果总是 false ;

优化器选择过程

优化器根据解析树可能会生成多个执行计划，然后选择最优的的执行计划；

SHOW VARIABLES LIKE 'optimizer_trace';
-- 启用优化器的追踪
SET optimizer_trace='enabled=on';
-- 执行一条查询语句
SELECT * FROM information_schema.optimizer_trace;
-- 用完关闭
SET optimizer_trace="enabled=off";
SHOW VARIABLES LIKE 'optimizer_trace';

慢日志查询

开启

查看

SHOW GLOBAL VARIABLES LIKE 'slow_query%';
SHOW GLOBAL VARIABLES LIKE 'long_query%';

设置

SET GLOBAL slow_query_log = ON; -- on 开启 off 关闭
SET GLOBAL long_query_time = 4; -- 单位秒；默认10s；此时设置为4s

修改配置

slow_query_log = ON
long_query_time = 4
slow_query_log_file = D:/mysql/mysql57-slow.log

mysqldumpslow

查找最近5条慢查询日志

mysqldumpslow -s t -t 5 -g 'select' D:/mysql/mysql57-slow.log

SHOW PROFILE

# 查看是否开启
SELECT @@profiling;
# 设置开启
SET profiling = 1;
# 查看所有 profiles
show profiles;
# 查看query id 为 10 那条查询
show profile for query 10;
# 查看最后一条查询
show profile;
# 最后关闭
SET profiling = 0;

SHOW PROCESSLIST

查看连接线程；可以查看此时线上运行的 sql 语句；
如果要查看完整的SQL语句：SHOW FULL PROCESSLIST; 然后优化该语句；