记录一次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，尤其是看索引是否按照设计生效。

•id：SELECT的查询序列号，体现执行优先级，如果是子查询，id的序号会递增，id 值越大优先级越高，越先被执行

•select_type：表示查询的类型

•table：输出结果集的表，如设置了别名，也会显示

•partitions：匹配的分区

•type：对表的访问方式

•possible_keys：表示查询时，可能使⽤的索引

•key：表示实际使⽤的索引

•key_len：索引字段的长度

•ref：列与索引的比较

•rows：扫描出的行数（估算的行数）

•filtered：按表条件过滤的⾏百分比

•Extra：执行情况的描述和说明

当我们发现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慢查询解决方案

标签：查询,索引,muc,SQL,msg,优化,channel
From： https://www.cnblogs.com/Jcloud/p/18647458