mysql慢sql优化_MySQL慢SQL优化-阿里云开发者社区

基础知识

慢SQL官方定义

If a query takes longer than this many seconds, the server increments the Slow_queries status variable. If the slow query log is enabled, the query is logged to the slow query log file; Unit:Second.

阿里云控制台慢SQL参数

SQL优化原因与影响

•SQL优化原因：查询效率低的SQL(慢SQL)会占用机器大量的CPU、内存和IO资源，影响正常业务。

•慢SQL现象/影响：SQL查询缓慢、业务超时、数据库机器CPU/IO飙升、业务连接不上数据库、现网故障等。

SQL语句执行过程

Optimizer查询优化器方式

逻辑优化

优化器的逻辑优化，即根据关系代数规则，对SQL语句进行等价变化:

(1) 对投影、选择等操作进行句式优化;

(2) 对条件表达式进行谓词优化、条件化简;

(3) 对连接语义进行外连接、嵌套连接的优化;

(4) 对集合、GROUP BY、ORDER BY等优化

(5) 子查询优化、视图重写、语义优化

逻辑优化例子

条件化简 :

select id,sfzh,address from t1 where 1=1 and name ='宸谦';

子查询优化 :

select * from t1 where a in (select a from t2);

优化器逻辑优化结果：

select t1.* from t1 join t2 on t1.a= t2.a;

物理优化

物理优化的总代价模型 Cost = CPU Cost + IO Cost

(1) CPU Cost：MySQL Server层，处理返回记录所花开销。

CPU Cost = records / TIME_FOR_COMPARE =

records / 5，即每5条记录的处理时间，作为 1 CPU Cost。

(2) IO Cost：存储引擎层面，读取页面的IO开销。

执行计划

查看

在SQL语句前加explain关键字可查看SQL语句的执行计划，

常用语法:

(1)explain extended(输出更多扩展信息)

(2)explain format=json (json格式输出，可看到cost等信息)

结果字段注解

案例分析

优化原则

优化总原则：业务侧优化 > SQL 优化 > 数据库+操作系统参数优化

SQL优化原则：高频SQL收益 > 低频SQL收益

业务侧优化例子：

(1)某业务每隔1分钟会并发几十路去数据库查询A表(全表扫描)，造成其他业务响应超时

(2)统计分析类SQL拆分，热点数据缓存

慢SQL例子

SELECT

count(*) count

FROM

app_mst

LEFT JOIN app_profile ON app_mst.app_id = app_profile.app_id

LEFT JOIN app_apply_step ON app_mst.app_id = app_apply_step.app_id

WHERE

app_apply_step.is_exp = '0';

执行计划：

优化步骤

(1)查看执行计划:

在SQL前加上 explain extended,主要关注key(用到什么索引)、rows(查询执行扫描的元组个数)、extra(是否利用到排序、临时表等)。

(2)查看SQL涉及的表结构，此处主要查看app_apply_step 表:

show create table app_apply_step, 主要关注表的索引和where 条件中的字段类型。可见 app_apply_step 表 is_exp 字段上无索引,且为tinyint类型。

(3)思考可能优化的点:

先查看表 app_apply_step

字段is_exp的索引区分度 ，执行语句：

select count(distinct col1),

count(distinct col2)/count(*) from tb_name;

(4)is_exp 字段区分度约等于0，表明通过索引筛选非常高效。

alter table app_apply_step add index idx_is_exp (is_exp);

优化步骤总结

查看执行计划 explain extended

如果有告警信息，查看告警信息 show warnings;

查看SQL涉及的表结构和索引信息

根据执行计划，思考可能的优化点

按照可能的优化点执行表结构变更、增加索引、SQL改写等操作

查看优化后的执行时间和执行计划

如果优化效果不明显，重复第四步操作

慢SQL常见问题汇总