SQL执行效率的分析-explain分析慢查询

olei 31 views 0

定位慢SQL

在工作中,某个功能或者某个接口需要很久才返回结果,我们应该去确定是不是因为慢查询导致的。定位慢SQL有如下两种方案

  • 查看慢查询日志,确定已经执行完的慢查询
  • show processlist查看正在执行的慢查询

两个场景介绍

通过慢查询日志

如果需要定位慢查询,一般的方法是通过慢查询日志来查询的,MySQL的慢查询日志用来记录在MySQL中响应超过参数long_query_time(单位秒,默认值10)设置的值并且扫描记录数不小于min_examined_row_limit(默认值为0)的语句,能够帮助我们找到执行完的慢查询,方便我们对这些SQL进行优化

知识拓展

默认情况下,慢查询日志不会记录管理语句,可以通过设置log_slow_admin_statement = on让管理语句中的慢查询也会记录到慢查询日志中

通常情况下,也不会记录查询时间不超过long_query_time但是不使用索引的语句,可以通过配置log_queries_not_using_indexes = on让不使用索引的SQL都被记录到慢查询日志中(即使查询时间没有超过long_query_time配置的值)

如果需要使用慢查询日志,一般分为四步:开启慢查询日志、设置慢查询阈值、确定慢查询日志路径、确定慢查询日志的文件名。如下分析:

  • 首先开启慢查询日志,由参数slow_query_log决定是否开启,在MySQL命令行下输入下面的命令:
mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.00 sec)

默认情况下,慢查询是关闭的

  • 设置慢查询阈值
mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

知识拓展
MySQL 中 long_query_time 的值如何确定呢

线上业务一般建议把long_query_time设置为1s。如果某个业务的MySQL要求比较高的QPS,可设置慢查询为0.1s。发现慢查询及时优化或者提醒开发者改写。

一般测试环境的long_query_time设置的阈值比生产环境的要小,比如生产环境是1s,则测试环境为0.5s。便于在测试环境中及时发现一些效率低的SQL

甚至某个重要的业务,测试环境设置long_query_time0s,以便记录所有的语句。并留意慢查询日志的输出,上线前的功能测试完成之后,分析慢查询日志每类语句的输出,重点关注Rows_examined(语句执行期间从存储引擎获取的行数),提前优化

  • 确定慢查询日志路径

慢查询日志的路径默认是MySQL的数据目录

mysql> show global variables like "datadir";
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /data/mysql/data/3306/ |
+---------------+------------------------+

1 row in set (0.00 sec)
  • 确定慢查询日志的文件名
mysql> show global variables like "slow_query_log_file";
+---------------------+----------------+
| Variable_name       | Value          |
+---------------------+----------------+
| slow_query_log_file | mysql-slow.log |
+---------------------+----------------+

1 row in set (0.00 sec)

根据上面的查询结果,可以直接查看/data/mysql/data/3306/mysql-slow.log文件获取已经执行完的慢查询

[root@mysqltest ~]# tail -n5 /data/mysql/data/3306/mysql-slow.log

Time: 2019-05-21T09:15:06.255554+08:00

User@Host: root[root] @ localhost []  Id: 8591152

Query_time: 10.000260  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0

SET timestamp=1558401306;
select sleep(10);

执行结果详细描述

  • tail -n5 :只查看慢查询文件的最后5行
  • Time:慢查询发生的时间
  • User@Host:客户端用户和IP
  • Query_time:查询时间
  • Lock_time:等待表锁的时间
  • Rows_sent:语句返回的行数
  • Rows_examined:语句执行期间从存储引擎读取的行数

上面是系统自带的慢查询日志查看的,可以通过pt-query-digest或者mysqldumpslow等工具对慢查询日志进行分析

通过show processlist

有时候,慢查询正在运行,已经导致数据库负载偏高了,而由于慢查询还没有执行完毕,因此慢查询日志还是看不到任何语句。此时可以使用show processlist命令判断正在执行的慢查询。show processlist显示哪些线程正在运行。如果有PROCESS权限,则可以看到所有线程。否则,只能看到当前会话的线程。

知识拓展
如果不使用FULL关键字,在info字段中只显示每个语句的前100个字符,如果想看语句的全部内容可以使用full修饰(show full processlist)。

  • 执行结果如下
mysql> show processlist\G
*************************** 1. row ***************************
     Id: 4
   User: root
   Host: localhost:49799
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
1 row in set (0.00 sec)

解释

  • Time:表示执行的时间
  • Info:表示SQL语句

我们这里可以通过它的执行时间(Time)来判断是否是慢SQL

使用explain分析慢查询

可以通过explainshow profiletrace等诊断工具来分析慢查询。

expalin

explain可以获取MySQL中的SQL语句的执行计划,比如语句是否使用了关联查询,是否使用了索引、扫描行数等。可以帮助我们选择更好的索引和写出更优的SQL。使用方法:在查询语句前面加上explain就可以了

  • 实例

为了便于理解,先创建两张测试表,建表以及数据写入语句如下

CREATE DATABASE muke;           /* 创建测试使用的database,名为muke */
use muke;                       /* 使用muke这个database */
drop table if exists t1;        /* 如果表t1存在则删除表t1 */

CREATE TABLE `t1` (             /* 创建表t1 */
  `id` int(11) NOT NULL auto_increment,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`),
  KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

drop procedure if exists insert_t1; /* 如果存在存储过程insert_t1,则删除 */
delimiter ;;
create procedure insert_t1()        /* 创建存储过程insert_t1 */
begin
  declare i int;                    /* 声明变量i */
  set i=1;                          /* 设置i的初始值为1 */
  while(i<=1000)do                  /* 对满足i<=1000的值进行while循环 */
    insert into t1(a,b) values(i, i); /* 写入表t1中a、b两个字段,值都为i当前的值 */
    set i=i+1;                      /* 将i加1 */
  end while;
end;;
delimiter ;                 /* 创建批量写入1000条数据到表t1的存储过程insert_t1 */
call insert_t1();           /* 运行存储过程insert_t1 */

drop table if exists t2;    /* 如果表t2存在则删除表t2 */
create table t2 like t1;    /* 创建表t2,表结构与t1一致 */
insert into t2 select * from t1;   /* 将表t1的数据导入到t2 */

下面尝试使用explain分析一条SQL,例子如下

mysql> explain select * from t1 where b=100;

SQL执行效率的分析-explain分析慢查询

  • explain结果各字段解释如下

    加粗的列为需要重点关注的列

列名 解释
id 查询编号
__select_type__ 查询类型:显示本行是简单还是复杂查询
table 涉及到的表
partitions 匹配的分区:查询将匹配记录所在的分区。仅当使用partition关键字时才显示该列。对于非区分表,该值为NULL
type 本次查询的表连接类型
possible_keys 可能选择的索引
key 实际选择的索引
key_len 被选择的索引长度:一般用于判断联合索引有多少列被选择了
ref 与索引比较的列
rows 预计需要扫描的行数,对InnoDB来说,这个值是估值,并不一定准确
filtered 按条件筛选的行的百分比
Extra 附加信息
  • 介绍几个比较重要的列常包含的值:select_typetypeExtra

    select_type

select_type的值 解释
SIMPLE 简单查询
PRIMARY 如果包含关联查询或者子查询,则最外层的查询部分标记为priamry
UNION 联合查询中第二个及后面的查询
DEPENDENT UNION 满足依赖外部的关联查询第二个及以后的查询
UNION RESULT 联合查询结果
SUBQUERY 子查询中的第一个查询
DEPENDENT SUBQUERY 子查询中的第一个查询,并且依赖外部查询
DERIVED 用到派生表的查询
MATERIALIZED 被物化的子查询
UNCACHEABLE SUBQUERY 一个子查询的结果不能被缓存,必须重新评估外层查询的每一行
UNCACHEABLE UNION 关联查询第二个或后面的语句属于不可缓存的子查询

type

type的值 解释
system 查询对象表只有一行数据,且只能用于MyISAMMemory引擎的表,这是最好的情况
const 基于主键或唯一索引查询,最多返回一条结果
eq_ref 表连接时基于主键或非NULL的唯一索引完成扫描
ref 基于普通索引的等值查询,或者表间等值连接
fulltext 全文检索
ref_or_null 表连接类型是ref,但进行扫描的索引列中可能包含NULL
index_merge 利用多个索引
unique_subquery 子查询中使用唯一索引
index_subquery 子查询中使用普通索引
range 利用索引进行范围查询
index 全索引扫描
ALL 全表扫描

Extra

Extra常见的值 解释 例子
Using filesort 将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序 explain select *from t1 order by create time;
Using temporary 需要创建一个临时表来存储结构,通常发生对没有索引的列进行GROUP BY explain select *from t1 order by create time;
Using index 使用覆盖索引 explain select *from t1 where a=111;
Using where 使用where语句来处理结果 explain select * from t1 where create_time='2019-06-18 14:38:24'
Impossible WHERE where子句判断的结果总是false,而不能选择任何数据 explain select * from t1 where 1<0
Using join buffer(Block Nested Loop) 关联查询中,被驱动表的关联字段没索引 explain select * from t1 straight_join t2 on (t1.create_time=t2.create_time);
Using index condition 先条件过滤索引,再查数据 explain select * from t1 where a>900 and like "%9";
Select tables optimized away 使用某些聚合函数(比如maxmin)来访问存在索引的某个字段时 explain select max(a) from t1;

发表评论 取消回复
表情 图片 链接 代码

分享