记录一次 SQL 慢查询优化

2025-01-02
北京
本文字数：2455 字
阅读完需：约 8 分钟

作者：京东物流赫占星

一、慢 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_namefrom message_user_channel mucjoin message m on muc.msg_no = m.msg_nojoin terminal t on muc.channel = t.terminal_codewhere  muc.msg_no = ?  and m.is_delete = 0group by muc.channelorder by m.msg_publish_time desclimit ?, ?;

复制代码

二、慢 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_namefrom message mjoin message_user_channel muc on muc.msg_no = m.msg_nowhere  muc.msg_no = ?  and m.is_delete = 0group by muc.channelorder by m.msg_publish_time desclimit ?, ?;

复制代码

我们将优化后的 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_namefrom    (    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 ?, ?    ) tempjoin terminal t on temp.channel = t.terminal_code;