背景

研发在预生产环境上线一个 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 语句也能减小数据库的负担。