定位慢SQL
当业务中的某个接口功能查询需要很久返回,如果是SQL相关操作,很大程度上就是慢SQL导致。定位慢SQL有以下两种方式:
- 查看慢日志
- show processlist 查看当前运行时
慢日志
一般的方法是通过慢查询日志来查询的,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 配置的值)。
慢日志的开启
开启慢查询日志、设置慢查询阀值、确定慢查询日志路径、确定慢查询日志的文件名
set global slow_query_log = on;
set global long_query_time = 1;
show global variables like "datadir";
show global variables like "slow_query_log_file";
线上业务一般建议long_query_time 设置为 0.5 ~ 1 秒,如果某个业务的 MySQL 要求比较高的 QPS,可设置慢查询为 0.1 秒。发现慢查询及时优化或者提醒开发改写。
一般测试环境建议 long_query_time 设置的阀值比生产环境的小,比如生产环境是 1 秒,则测试环境建议配置成 0.5 秒。便于在测试环境及时发现一些效率低的 SQL。
甚至某些重要业务测试环境 long_query_time 可以设置为 0,以便记录所有语句。并留意慢查询日志的输出,上线前的功能测试完成后,分析慢查询日志每类语句的输出,重点关注 Rows_examined(语句执行期间从存储引擎读取的行数),提前优化。
慢日志的解析
# Time: 2023-08-28T08:25:15.955234Z
# User@Host: root[root] @ localhost [] Id: 8
# Query_time: 0.014107 Lock_time: 0.001830 Rows_sent: 10 Rows_examined: 10
use test;
SET timestamp=1693211115;
select * from yue;
/*
Time:慢查询发生的时间
User@Host:客户端用户和IP
Query_time:查询时间
Lock_time:等待表锁的时间
Rows_sent:语句返回的行数
Rows_examined:语句执行期间从存储引擎读取的行数
*/
除了以上的分析方式还可以利用工具做详细分析: pt-query-digest 或者 mysqldumpslow
show processlist
有时慢查询正在执行,已经导致数据库负载偏高了,而由于慢查询还没执行完,因此慢查询日志还看不到任何语句。此时可以使用 show processlist 命令判断正在执行的慢查询。show processlist 显示哪些线程正在运行。如果有 PROCESS 权限,则可以看到所有线程。否则,只能看到当前会话的线程。
explain 分析慢查询
分析SQL 执行效率是优化 SQL 的重要手段,通过上面讲的两种方法,定位到慢查询语句后,可以通过 explain、show profile 和 trace 等诊断工具来分析慢查询。
测试数据
CREATE TABLE `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;
delimiter ;;
create procedure insert_t1()
begin
declare i int;
set i=1;
while(i<=1000)
do
insert into t1(a,b) values(i, i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t1();
create table t2 like t1;
insert into t2 select * from t1;
root@ 14:44: [test]> explain select * from t1 where b = 100;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | idx_b | idx_b | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
列名 | 解释 |
id | 查询编号,id相同由上至下,id不同由大至小 |
select_type | 查询类型:显示本行是简单还是复杂查询 |
table | 涉及到的表 |
partitions | 匹配的分区:查询将匹配记录所在的分区。仅当使用 partition 关键字时才显示该列。对于非分区表,该值为 NULL。 |
type | 本次查询的表连接类型 |
possible_keys | 可能选择的索引 |
key | 实际选择的索引 |
key_len | 被选择的索引长度:一般用于判断联合索引有多少列被选择了 |
ref | 与索引比较的列 |
rows | 预计需要扫描的行数,对 InnoDB 来说,这个值是估值,并不一定准确 |
filtered | 按条件筛选的行的百分比 |
Extra | 附加信息 |
执行计划详解
select_type
SIMPLE | 简单查询(不使用关联查询或子查询) |
PRIMARY | 如果包含关联查询或者子查询,则最外层的查询部分标记为primary |
UNION | 联合查询中第二个及后面的查询 |
DEPENDENT UNION | 满足依赖外部的关联查询中第二个及以后的查询 |
UNION RESULT | 联合查询的结果 |
SUBQUERY | 子查询中的第一个查询 |
DEPENDENT SUBQUERY | 子查询中的第一个查询,并且依赖外部查询 |
DERIVED | 用到派生表的查询 |
MATERIALIZED | 被物化的子查询 |
UNCACHEABLE SUBQUERY | 一个子查询的结果不能被缓存,必须重新评估外层查询的每一行 |
UNCACHEABLE UNION | 关联查询第二个或后面的语句属于不可缓存的子查询 |
type
下表:由上至下性能变差
system | 查询对象表只有一行数据,且只能用于 MyISAM 和 Memory 引擎的表,这是最好的情况 |
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 group by create_time; |
Using index | 使用覆盖索引 | explain select a 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); |
Select tables optimized away | 使用某些聚合函数(比如 max、min)来访问存在索引的某 个字段是 | explain select max(a) from t |
Using index condition | 先条件过滤索引,再查数据,使用了Index Condition Pushdown | explain select * from t1 where a >900 and a like “%9”; |