面试官：给你一段 SQL，你会如何优化？

我在面试的时候很喜欢问候选人这样一个问题：“你在项目中遇到过慢查询问题吗？你是怎么做 SQL 优化的？”

很多时候，候选人会直接跟我说他们在编写 SQL 时会遵循的一些常用技巧，比如：

• 合理使用索引

• 使用 UNION ALL 替代 UNION

• 不要使用 select * 写法

• JOIN 字段建议建立索引

• 避免复杂 SQL 语句

这里不能说完全错误，因为这些技巧确实可以提高 SQL 运行效率；但是也不能说完全正确，毕竟我是想问他具体怎么是做 SQL 优化的。

接下来我问他，我这里有一段复杂的 SQL，你可以动手帮我优化一下吗？到这一步的时候就有很多候选人做不好打了退堂鼓。他们有很扎实的理论知识，但是动手能力却差点火候。

今天这篇文章就从实战的角度出发，带大家走一遍 SQL 优化的真实流程。

找出有问题的 SQL？

在实际开发中要判断一段 SQL 有没有问题可以从两方面来判断：

1、系统层面

• CPU 消耗严重

• IO 等待严重

• 页面响应时间过长

• 应用的日志出现超时等错误

2、SQL 语句层面

• 冗长

• 执行时间过长

• 从全表扫描获取数据

• 执行计划中的 rows、cost 很大

冗长的 SQL 都好理解，一段 SQL 太长阅读性肯定会差，出现问题的频率肯定会更高。更进一步判断 SQL 问题就必须得从执行计划入手，如下所示：

执行计划告诉我们本次查询走了全表扫描 Type=ALL，rows 很大(9950400)基本可以判断这是一段"有味道"的 SQL。

查看 SQL 执行计划？

找到了有问题的 SQL 就要确定优化方案，那究竟从何处下手呢？这里必须要通过执行计划来观察。

执行计划会告诉你哪些地方效率低，哪里可以需要优化。我们以 MYSQL 为例，看看执行计划是什么。（每个数据库的执行计划都不一样，需要自行了解）

explain select * from xxx

当使用 explain sql 后会看到执行计划

执行计划中几个重要字段的解释说明，大家需要记住

通过执行计划我们就可以确定优化方案，优化一处后再回过头来观察执行计划，如此往复循环直到找到最优目标为止。

下面给出一段有问题的 SQL 具体操作一下。

SQL 优化案例

慢查询

1、表结构如下：

CREATE TABLE `a`(    `id`          int(11) NOT NULLAUTO_INCREMENT,    `seller_id`   bigint(20)                                       DEFAULT NULL,    `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,    `gmt_create`  varchar(30)                                      DEFAULT NULL,    PRIMARY KEY (`id`));CREATE TABLE `b`(    `id`          int(11) NOT NULLAUTO_INCREMENT,    `seller_name` varchar(100) DEFAULT NULL,    `user_id`     varchar(50)  DEFAULT NULL,    `user_name`   varchar(100) DEFAULT NULL,    `sales`       bigint(20)   DEFAULT NULL,    `gmt_create`  varchar(30)  DEFAULT NULL,    PRIMARY KEY (`id`));CREATE TABLE `c`(    `id`         int(11) NOT NULLAUTO_INCREMENT,    `user_id`    varchar(50)  DEFAULT NULL,    `order_id`   varchar(100) DEFAULT NULL,    `state`      bigint(20)   DEFAULT NULL,    `gmt_create` varchar(30)  DEFAULT NULL,    PRIMARY KEY (`id`));

2、有问题的查询 SQL

select a.seller_id,       a.seller_name,       b.user_name,       c.statefrom a,     b,     cwhere a.seller_name = b.seller_name  and b.user_id = c.user_id  and c.user_id = 17  and a.gmt_create    BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)    AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)order by a.gmt_create；

a，b，c 三张表关联，查询用户 17 在当前时间前后 10 个小时的订单情况，并根据订单创建时间升序排列

优化步骤

1、先查看各表数据量

2、查看原执行时间，总耗时 0.21s

3、查看原执行计划

4、通过观察执行计划和 SQL 语句，确定初步优化方案

• SQL 中 where 条件字段类型要跟表结构一致，表中 user_id 为 varchar(50)类型，实际 SQL 用的 int 类型，存在隐式转换，也未添加索引。将 b 和 c 表 user_id 字段改成 int 类型。

• 因存在 b 表和 c 表关联，将 b 和 c 表 user_id 创建索引

• 因存在 a 表和 b 表关联，将 a 和 b 表 seller_name 字段创建索引

• 利用复合索引消除临时表和排序

初步优化的 SQL：

alter table b modify `user_id` int(10) DEFAULT NULL;alter table c modify `user_id` int(10) DEFAULT NULL;alter table c add index `idx_user_id`(`user_id`);alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);

查看优化后的执行时间

初步优化后执行速度提升了 20 倍，是否还能继续优化呢？

5、继续查看优化后的执行计划

这里只看到查询需要扫描的元素比较大，不过还看到了有两处告警信息，直接查看告警信息

show warnings

Cannot use range access on index ‘idx_sellname_gmt_sellid’ due to type or collation conversion on field ‘get_create’，这句话是告诉你由于 gmt_create 列发生了类型转换所以无法走索引。

查看 SQL 建表语句发现 gmt_create 字段被设计成了 varchar 类型，在 SQL 查询时需要转化成时间格式做查询，确实不能走索引。

所以需要调整一下 gmt_create 字段格式

alter table a modify "gmt_create" datetime DEFAULT NULL;

6、修改字段后再来查看执行时间

执行速度非常完美。

7、再观察优化后的执行计划

可以看到执行计划也很完美，至此 SQL 优化结束。

SQL 优化小结

这里给大家总结一下优化 SQL 的套路

1. 查看执行计划 explain

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

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

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

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

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

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

作者：飘渺 Jam

链接：https://juejin.cn/post/7204984033100316728

来源：稀土掘金