慢查询定位与分析

定位慢sql

1、查看慢查询日志确定已经执行完的慢查询

mysql> set global slow_query_log = on;
mysql> set global long_query_time = 1;
mysql> show global variables like "datadir";
mysql> 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);

可以看到查询时间、表锁时间、扫描行数Rows_examined。

在有些场景下,执行器调用一次,在引擎内部则扫描了多行,因此引擎扫描行数跟rows_examined并不是完全相同的。

2、show 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)`

可以看到执行时间和SQL语句

分析慢查询

1、使用 explain 分析慢查询

获取 MySQL 中 SQL 语句的执行计划.需要重点关注以下:

  • select_type :查询类型,是简单还是复杂查询

  • type :查询的表连接类型

  • key :实际选择的索引

  • rows: 预计需要扫描的行数,对 InnoDB 来说,这个值是估值

  • Extra :附加信息

select_type 含义
SIMPLE 简单查询,不使用关联查询或子查询
PRIMARY 如果包含关联查询或者子查询,则最外层的查询部分标记为primary
UNION 联合查询中第二个及后面的查询
DEPENDENT UNION 满足依赖外部的关联查询中第二个及以后的查询
UNION RESULT 联合查询的结果
SUBQUERY 子查询中的第一个查询
DEPENDENT SUBQUERY 子查询中的第一个查询,并且依赖外部查询
DERIVED 用到派生表的查询
MATERIALIZED 被物化的子查询
UNCACHEABLE SUBQUERY 一个子查询的结果不能被缓存,必须重新评估外层查询的每一行
UNCACHEABLE UNION 关联查询第二个或后面的语句属于不可缓存的子查询
type 含义
system 查询对象表只有一行数据
const 基于主键或唯一索引查询,最多返回一条结果
eq_ref 最多只返回一条符合条件的记录。
ref 基于普通索引的等值查询,或者表间等值连接
fulltext 全文检索
ref_or_null 表连接类型是
index_merge 利用多个索引
unique_subquery 子查询中使用唯一索引
index_subquery 子查询中使用普通索引
range 利用索引进行范围查询
index 全索引扫描
ALL 全表扫描
Extra 解释
Using filesort 将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序
Using temporary 需要创建一个临时表来存储结构,通常发生对没有索引的列进行 GROUP BY 时
Using index 使用覆盖索引
Using where 使用 where 语句来处理结果
Impossible WHERE 对 where 子句判断的结果总是 false 而不能选择任何数据
Using join buffer(BlockNested Loop) 关联查询中,被驱动表的关联字段没索引
Using index condition 先条件过滤索引,再查数据
Select tables optimized away 使用某些聚合函数(比如 max、min)来访问存在索引的某个字段

2、show profile

了解 SQL 执行过程的资源使用情况,能让我们知道到底慢在哪个环节

mysql> select @@have_profiling;
mysql> select @@profiling;
mysql> set profiling=1;
mysql> select * from t1 where b=1000;
mysql> show profiles;  /*获取query id*/
mysql> show profile for query 1;

+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000115 |
| checking permissions | 0.000013 |
| Opening tables | 0.000027 |
| init | 0.000035 |
| System lock | 0.000017 |
| optimizing | 0.000016 |
| statistics | 0.000025 |
| preparing | 0.000020 |
| executing | 0.000006 |
| Sending data | 0.000294 |
| end | 0.000009 |
| query end | 0.000012 |
| closing tables | 0.000011 |
| freeing items | 0.000024 |
| cleaning up | 0.000016 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

3、trace

使用 trace 查看优化器如何选择执行计划

mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;
/* optimizer_trace="enabled=on" 表示开启 trace;end_markers_in_json=on 表示 JSON 输出开启结束标记 */
mysql> select * from t1 where a >900 and b > 910 order by a;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G;

TRACE包括准备阶段、优化阶段、执行阶段。trace中可以看到使用每个索引的成本。

也可以看number_of_tmp_files中判断是否使用了临时文件。


   转载规则


《慢查询定位与分析》 wangyixin-tom 采用 知识共享署名 4.0 国际许可协议 进行许可。
 上一篇
索引失效 索引失效
函数操作对条件字段做函数操作,可能破坏了索引值的有序性,走不了索引。 select * from t1 where date(c) ='2019-05-21'; 优化:改成范围查询 select * from t1 where c>='20
2021-03-13
下一篇 
redis应用 redis应用
分布式锁实现:set lock:codehole true ex 5 nx 注意:不要用于较长任务,可能超时释放 优化:设置value是一个随机值,保证不会被其他线程释放 可重入锁:基于线程的Threadlocal变量存储当前持有锁的计数。
2021-03-10
  目录