慢查询定位与分析

定位慢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);

可以看到查询时间、表锁时间。

2、show processlist 查看正在执行的慢查询

mysql> show processlistG`

`*************************** 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_TRACEG;