基础9——SQL优化,慢日志查询"/>
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合并结果集;
-
- 具体表名或者表的别名,从具体的物理表中获取数据;
-
- 表明为derivedN的形式,表示 id 为 N 的查询产生的衍生表;
-
- 当有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; 然后优化该语句;
更多推荐
MySQL基础9——SQL优化,慢日志查询
发布评论