背景

研发在预生产环境上线一个 SQL ，执行时间耗时 6s ，研发通过加索引、排查隐式转换等方式，经过初步优化后，耗时 2s 。

需求

想让 dba 给协助看下是否还有优化空间

处理过程

1、先看下面这个已经初步优化过的例子(左连接中的主表优先作用查询条件)：

SELECT    a.*,           c.transaction FROM      (               SELECT   resource_id               FROM     user_desc d                    WHERE    isdelete = 0                    AND      user_code = 'ac160589'                    ORDER BY sale_code limit 20) a LEFT JOIN           (               SELECT   resources_id ， sum(ifnull(allocation, 0) * 12345) transaction               FROM     my_resources                    GROUP BY resources_id) c ON        a.resource_id = c.resources_id;  

2、该语句还存在其它问题。不难看出子查询 c 是全表聚合查询，在表数量特别大的情况下会导致整个语句的性能下降。

其实对于子查询 c ，左连接最后结果集只关心能和主表 resource_id 能匹配的数据。因此我们可以重写语句如下，执行时间从原来的 2 秒下降到 2 毫秒。 SELECT    a.*,           c.transaction FROM      (                    SELECT   resource_id                    FROM     user_desc d                    WHERE    isdelete = 0                    AND      user_code = 'ac160589'                    ORDER BY sale_code limit 20) a LEFT JOIN           (                    SELECT   resources_id ， sum(ifnull(allocation, 0) * 12345) transaction                    FROM     my_resources r,                             (                                      SELECT   resource_id                                      FROM     user_desc d                                      WHERE    isdelete = 0                                      AND      user_code = 'ac160589'                                      ORDER BY sale_code limit 20) a                    WHERE    r.resources_id = a.resources_id                    GROUP BY resources_id) c ON        a.resource_id = c.resources_id;

3、但是子查询 a 在我们的SQL语句中出现了多次。这种写法不仅存在额外的开销，还使得整个语句显的繁杂。使用 WITH 语句再次重写：

WITH a AS (          SELECT   resource_id          FROM     user_desc d          WHERE    isdelete = 0          AND      user_code = 'ac160589'          ORDER BY sale_code limit 20) SELECT    a.*,           c.transaction FROM      a LEFT JOIN           (                    SELECT   resources_id ， sum(ifnull(allocation, 0) * 12345) transaction                    FROM     my_resources r,                             a                    WHERE    r.resources_id = a.resources_id                    GROUP BY resources_id) c ON        a.resource_id = c.resources_id;

4、总结

数据库编译器产生执行计划，决定着 SQL 的实际执行方式。但是编译器只是尽力服务，所有数据库的编译器都不是尽善尽美的。 上述提到的多数场景，在其它数据库中也存在性能问题。了解数据库编译器的特性，才能避规其短处，写出高性能的 SQL 语句。 程序员在设计数据模型以及编写 SQL 语句时，要把算法的思想或意识带进来。编写复杂 SQL 语句要养成使用 WITH 语句的习惯。简洁且思路清晰的 SQL 语句也能减小数据库的负担。