如何定位慢查询和如何分析 SQl 执行效率

定位慢 SQL

定位慢 SQL 有如下两种解决方案:
  • 查看慢查询日志确定已经执行完的慢查询
  • show processlist 查看正在执行的慢查询

我们一起来了解下这两种方法的使用场景和使用技巧!

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

默认情况下,慢查询日志中不会记录管理语句,可通过设置 log_slow_admin_statements = 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;

默认环境下,慢查询日志是关闭的。

  • 设置慢查询时间阀值
mysql> set global long_query_time = 1;

MySQL 中 long_query_time 的值如何确定呢?
线上业务一般建议把 long_query_time 设置为 1 秒,如果某个业务的 MySQL 要求比较高的 QPS,可设置慢查询为 0.1 秒。发现慢查询及时优化或者提醒开发改写。
一般测试环境建议 long_query_time 设置的阀值比生产环境的小,比如生产环境是 1 秒,则测试环境建议配置成 0.5 秒。便于在测试环境及时发现一些效率低的 SQL。
甚至某些重要业务测试环境 long_query_time 可以设置为 0,以便记录所有语句。并留意慢查询日志的输出,上线前的功能测试完成后,分析慢查询日志每类语句的输出,重点关注 Rows_examined(语句执行期间从存储引擎读取的行数),提前优化。

  • 确定慢查询日志路径

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

mysql> show global variables like "datadir";
  • 确定慢查询日志的文件名
show global variables like "slow_query_log_file";

根据上面的查询结果获取慢日志文件已经执行完的慢查询:

[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:语句执行期间从存储引擎读取的行数

2.通过 show processlist

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

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

mysql> show processlist\G`

`*************************** 10. row ***************************`

   `Id: 7651833`

   `User: one`

   `Host: 192.168.1.251:52154`

   `db: ops`

   `Command: Query`

   `Time: 3`

  `State: User sleep`

  `Info: select sleep(10)`

`......`

`10 rows in set (0.00 sec)`

这里对上面结果解释一下:

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

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

使用 explain 分析慢查询

分析 SQL 执行效率是优化 SQL 的重要手段,通过上面讲的两种方法,定位到慢查询语句后,我们可以通过 explain、show profile 和 trace 等诊断工具来分析慢查询。

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

这也是分析 SQL 时最常用的,也是作者最推荐的一种分析慢查询的方式。下面我们来看下示例~~

为了便于理解,先创建两张测试表(方便第 1、2 节实验使用),建表及数据写入语句如下:

CREATE DATABASE test;   /* 创建测试使用的database,名为test */
use test;              /* 使用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;

Explain 的结果各字段解释如下,加粗的列为需要重点关注的项:

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

下面将列出它们常见的一些值,可稍微过一遍,不需要完全记下来,对比各种值的区别。

Type.png

上表的这些情况,查询性能从上到下依次是最好到最差。

Extra.png

Extra2.png
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 215,463评论 6 497
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 91,868评论 3 391
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 161,213评论 0 351
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 57,666评论 1 290
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 66,759评论 6 388
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 50,725评论 1 294
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,716评论 3 415
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,484评论 0 270
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,928评论 1 307
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,233评论 2 331
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,393评论 1 345
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 35,073评论 5 340
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,718评论 3 324
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,308评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,538评论 1 268
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 47,338评论 2 368
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 44,260评论 2 352

推荐阅读更多精彩内容