记录一次SQL慢查询优化（sql查询速度慢如何优化）
作者：京东物流 赫占星
一、慢SqL发现
在一次需求UAT上线后，本来在测试环境没问题的接口，UAT环境出现了接口超时，通过查询接口日志发现是SQL查询超时了，原因是UAT环境的数据量比测试环境大得多。
一般来说，我们可以通过数据库本身的慢查询日志去定位出问题的慢SQL，但是对于京东，易维平台为我们提供了更为方便的慢SQL查询方式。我们可以通过应用名称和时间范围等条件筛选出自己需要定位的慢SQL。
通过易维平台，我们拿到了我们出问题的慢SQL语句：
select
count(1) as planed_count,
sum(case when muc.read_flag = 1 then 1 else 0 end) as success_count,
m.msg_no as msg_no,
m.msg_title as msg_title,
m.msg_publish_time as msg_publish_time,
m.msg_publisher_erp as msg_publish_erp,
muc.channel,
t.terminal_name as channel_name
from message_user_channel muc
join message m on muc.msg_no = m.msg_no
join terminal t on muc.channel = t.terminal_code
where
muc.msg_no = ?
and m.is_delete = 0
group by muc.channel
order by m.msg_publish_time desc
limit ?, ?;
二、慢SQL分析与优化
一提到慢SQL分析，可能大家的首先想到的就是Explain命令，但是其实我们可以先从更高的视角去看问题。
我们可以从4个方面去分析，分别是表设计、数据量级、索引、语法。
1、库表设计
好的表设计会让我们的查询变得更方便，比如在表关系比较复杂时，适当增加中间表，会减少查询的复杂度。表设计优化过后仍无法满足业务需要，可以考虑分库分表设计。
2、数据量级
大部分慢SQL是在生产上线以后才暴露的，因为生产环境数据量的急剧膨胀，导致在测试环境执行毫无问题的SQL，在生产环境出现了慢查询，甚至可能直接执行超时。因此我们在编写SQL时，要充分考虑数据量级对SQL执行的影响。
这次问题涉及的慢SQL，就命中了这个问题。message_user_channel表是一个千万量级的表，此表前后又跟另外2张表做了JOIN关联，笛卡尔积直接爆炸，我尝试将原SQL在易维平台上执行，发现直接查询超时。所以我将原SQL优化成了以下样式，通过子查询的方式，达到减少数据量的目的：
select
count(1) as planed_count,
sum(case when muc.read_flag = 1 then 1 else 0 end ) as success_count,
m.msg_no as msg_no,
m.msg_title as msg_title,
m.msg_publish_time as msg_publish_time,
m.msg_publisher_erp as msg_publish_erp,
muc.channel,
(select t.terminal_name from terminal t where muc.channel = t.terminal_code) as channel_name
from message m
join message_user_channel muc on muc.msg_no = m.msg_no
where
muc.msg_no = ?
and m.is_delete = 0
group by muc.channel
order by m.msg_publish_time desc
limit ?, ?;
我们将优化后的SQL放在易维上查询，发现果真可以查询出结果了，但是优化还没有结束，查询时间可以进一步缩短，我们继续往后看。
3、索引
索引可以通过减少回表大大降低SQL的执行时间。索引创建以后不一定按照设计者所想的那样生效，所以我们需要通过Explain命令来分析我们的SQL，尤其是看索引是否按照设计生效。
oid：SELECT的查询序列号，体现执行优先级，如果是子查询，id的序号会递增，id 值越大优先级越高，越先被执行
oselect_type：表示查询的类型
otable：输出结果集的表，如设置了别名，也会显示
opartitions：匹配的分区
otype：对表的访问方式
opossible_keys：表示查询时，可能使用的索引
okey：表示实际使用的索引
okey_len：索引字段的长度
oref：列与索引的比较
orows：扫描出的行数（估算的行数）
ofiltered：按表条件过滤的行百分比
oExtra：执行情况的描述和说明
当我们发现SQL执行没有按照设计走索引时，我们需要分析索引失效原因，以下是一些常见的会导致索引失效的场景：
1.需符合最左匹配原则
2.字段类型转换导致不用索引
3.字段前面加函数/加减运算会导致索引失效
4.模糊查询使用通配符“%”开头会导致全表扫描
5.WHERE子句中使用!=或<>操作符会导致全表扫描
6.用IN或UNION来替换OR低效查询
7.尽量避免使用NOT IN，会导致引擎走全表扫描，建议用NOT EXISTS代替
注意：不是所有的SQL都必须走索引，这需要根据数据量级、业务场景等灵活分析，走索引也不意味着一定会更快，尤其是在数据量较小的情况下。另外不是索引包含的字段越多越好，索引是需要占用存储空间的，当数据量特别大时，索引的维护也是一个问题。
4、语法
除了上述索引失效相关语法外，我们还有以下语法需要注意：
1.尽量避免使用 SELECT *，只查询业务需要的字段
2.读取适当的记录LIMIT M,N
3.尽量不要超过三个表JOIN
4.减少子查询的使用，使用JOIN代替
5.删除表中所有记录时请用TRUNCATE，不要用DELETE
6.避免不必要的ORDER BY排序
再回到我们本次问题SQL本身，我们发现优化版本1中针对terminal表的字查询会被执行多次，所以我们可以使用先查询出中间结果再JOIN的方式，进一步缩短执行时间：
select
temp.*,
t.terminal_name as channel_name
from
(
select
count(1) as planed_count,
sum(case when muc.read_flag = 1 then 1 else 0 end ) as success_count,
m.msg_no as msg_no,
m.msg_title as msg_title,
m.msg_publish_time as msg_publish_time,
m.msg_publisher_erp as msg_publish_erp,
muc.channel
from message m
join message_user_channel muc on muc.msg_no = m.msg_no
where
muc.msg_no = ?
and m.is_delete = 0
group by muc.channel
order by m.msg_publish_time desc
limit ?, ?
) temp
join terminal t on temp.channel = t.terminal_code;
三、总结
慢SQL是我们日常开发中常见的问题，而且往往只有生产上线后才能体现出来。因为库表设计可能因为历史数据兼容的原因导致不好修改，那数据量级、索引、语法就成了我们优化慢SQL非常有效的手段，希望此文能对大家有所帮助。
参考文献：
[1] Mysql慢查询及优化
[2] MySql慢查询解决方案