你还在这样写SQL吗？赶紧改改吧

最新推荐文章于 2024-06-29 12:12:59 发布

原创最新推荐文章于 2024-06-29 12:12:59 发布 · 2.2w 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #java #sql

数据库同时被 3 个专栏收录

2 篇文章

订阅专栏

sql

1 篇文章

订阅专栏

mysql

1 篇文章

订阅专栏

本文盘点了SQL常见错误用法及改正方法，包括分页查询、中间结果集下推、关联更新与删除等七种用法。通过重写SQL语句，如使用WITH语句、将exists改为join等，可显著提升性能。同时指出要了解数据库编译器特性，编写高性能SQL语句。

今天我们来盘点一下SQL常见错误用法以及如何去改正~

霍霍~开搞

SQL常见错误用法

用法一分页查询

这是最常用的场景之一，同样也是最容易出问题的地方，看这个sql

SELECT *
FROM   test_paper
WHERE  type = 0
AND name = '测试试卷'
ORDER  BY create_time
LIMIT  1000, 10;

你一般想到的办法应该是给 type、name、create_time字段加组合索引，这样条件排序就能有效利用到索引，性能迅速提升~

BUT

这只能解决90%的问题

假如 LIMIT子句变成“LIMIT 10000000,10”呢？

要知道DB不知道10000000是从什么地方开始，即使你加了索引也需要从头计算，进而出现性能问题

场景一解决方案

在前端浏览翻页数据时或者需要大数据批量导出等场景下，我们可以将上一页的最大值当成参数作为查询条件的~

SELECT   *
FROM     test_paper
WHERE    type = 0 
AND      name = '测试试卷' 
AND      create_time > '2017-03-16 14:00:00' 
ORDER BY create_time limit 10;

动动你的小手试试吧

用法二中间结果集下推

SELECT    a.*,
          c.allocated
FROM      (
                   SELECT   resourceid
                   FROM     my_distribute d
                   WHERE    isdelete = 0 
                   AND      cusmanagercode = '1234567' 
                   ORDER BY salecode limit 20) a
LEFT JOIN 
          (
                   SELECT   resourcesid， sum(ifnull(allocation, 0) * 12345) allocated
                   FROM     my_resources
                   GROUP BY resourcesid) c
ON        a.resourceid = c.resourcesid

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

其实对于子查询 c，左连接最后结果集只关心能和主表resourceid能匹配的数据。因此我们可以重写语句如下，执行时间从原来的2秒下降到2毫秒。

SELECT    a.*,
          c.allocated
FROM      (
                   SELECT   resourceid
                   FROM     my_distribute d
                   WHERE    isdelete = 0 
                   AND      cusmanagercode = '1234567' 
                   ORDER BY salecode limit 20) a
LEFT JOIN 
          (
                   SELECT   resourcesid， sum(ifnull(allocation, 0) * 12345) allocated
                   FROM     my_resources r,
                            (
                                     SELECT   resourceid
                                     FROM     my_distribute d
                                     WHERE    isdelete = 0 
                                     AND      cusmanagercode = '1234567' 
                                     ORDER BY salecode limit 20) a
                   WHERE    r.resourcesid = a.resourcesid
                   GROUP BY resourcesid) c
ON        a.resourceid = c.resourcesid

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

WITH a AS
(
         SELECT   resourceid
         FROM     my_distribute d
         WHERE    isdelete = 0 
         AND      cusmanagercode = '1234567' 
         ORDER BY salecode limit 20)
SELECT    a.*,
          c.allocated
FROM      a
LEFT JOIN 
          (
                   SELECT   resourcesid， sum(ifnull(allocation, 0) * 12345) allocated
                   FROM     my_resources r,
                            a
                   WHERE    r.resourcesid = a.resourcesid
                   GROUP BY resourcesid) c
ON        a.resourceid = c.resourcesid

用法三关联更新与删除

UPDATE operation o
SET    status = 'applying'
WHERE  o.id IN (SELECT id
FROM   (SELECT o.id,
o.status
FROM   operation o
WHERE  o.group = 123
AND o.status NOT IN ( 'done' )
ORDER  BY o.parent,
o.id
LIMIT  1) t);

上边语句，MySQL 实际执行的是循环/嵌套子查询（DEPENDENT SUBQUERY)，其执行时间可想而知。

重写为JOIN之后，子查询的选择模式从DEPENDENT SUBQUERY变成DERIVED,执行速度大大加快，从7秒降低到2毫秒。

UPDATE operation o
       JOIN  (SELECT o.id,
                            o.status
                     FROM   operation o
                     WHERE  o.group = 123 
                            AND o.status NOT IN ( 'done' )
                     ORDER  BY o.parent,
                               o.id
                     LIMIT  1) t
         ON o.id = t.id
SET    status = 'applying'

用法四混合排序

SELECT *
FROM   my_order o
       INNER JOIN my_appraise a ON a.orderid = o.id
ORDER  BY a.is_reply ASC,
          a.appraise_time DESC 
LIMIT  0, 20

由于is_reply只有0和1两种状态，我们按照下面的方法重写后，执行时间从1.58秒降低到2毫秒。

SELECT *
FROM   ((SELECT *
         FROM   my_order o
                INNER JOIN my_appraise a
                        ON a.orderid = o.id
                           AND is_reply = 0 
         ORDER  BY appraise_time DESC 
         LIMIT  0, 20)
        UNION ALL
        (SELECT *
         FROM   my_order o
                INNER JOIN my_appraise a
                        ON a.orderid = o.id
                           AND is_reply = 1 
         ORDER  BY appraise_time DESC 
         LIMIT  0, 20)) t
ORDER  BY  is_reply ASC,
          appraisetime DESC 
LIMIT  20;

用法五 EXISTS

SELECT *
FROM   my_neighbor n
       LEFT JOIN my_neighbor_apply sra
              ON n.id = sra.neighbor_id
                 AND sra.user_id = 'xxx' 
WHERE  n.topic_status < 4 
       AND EXISTS(SELECT 1 
                  FROM   message_info m
                  WHERE  n.id = m.neighbor_id
                         AND m.inuser = 'xxx')
       AND n.topic_type <> 5

去掉exists更改为join，能够避免嵌套子查询，将执行时间从1.93秒降低为1毫秒。

SELECT *
FROM   my_neighbor n
       INNER JOIN message_info m
               ON n.id = m.neighbor_id
                  AND m.inuser = 'xxx' 
       LEFT JOIN my_neighbor_apply sra
              ON n.id = sra.neighbor_id
                 AND sra.user_id = 'xxx' 
WHERE  n.topic_status < 4 
       AND n.topic_type <> 5

用法六复杂视图

外部查询条件不能够下推到复杂的视图或子查询的情况有：

聚合子查询；
含有LIMIT的子查询；
UNION 或UNION ALL子查询；
输出字段中的子查询；

SELECT *
FROM   (SELECT target,
               Count(*)
        FROM   operation
        GROUP  BY target) t
WHERE  target = 'rm-xxxx'

确定从语义上查询条件可以直接下推后，重写如下：

SELECT target,
       Count(*)
FROM   operation
WHERE  target = 'rm-xxxx' 
GROUP  BY target

用法七提前缩小范围

SELECT *
FROM   my_order o
       LEFT JOIN my_userinfo u
              ON o.uid = u.uid
       LEFT JOIN my_productinfo p
              ON o.pid = p.pid
WHERE  ( o.display = 0 )
       AND ( o.ostaus = 1 )
ORDER  BY o.selltime DESC 
LIMIT  0, 15

初始SQL
该SQL语句原意是：先做一系列的左连接，然后排序取前15条记录。从执行计划也可以看出，最后一步估算排序记录数为90万，时间消耗为12秒。

由于最后WHERE条件以及排序均针对最左主表，因此可以先对my_order排序提前缩小数据量再做左连接。SQL重写后如下，执行时间缩小为1毫秒左右。

SELECT *
FROM (
SELECT *
FROM   my_order o
WHERE  ( o.display = 0 )
       AND ( o.ostaus = 1 )
ORDER  BY o.selltime DESC 
LIMIT  0, 15
) o
     LEFT JOIN my_userinfo u
              ON o.uid = u.uid
     LEFT JOIN my_productinfo p
              ON o.pid = p.pid
ORDER BY  o.selltime DESC
limit 0, 15

再检查执行计划：子查询物化后（select_type=DERIVED)参与JOIN。虽然估算行扫描仍然为90万，但是利用了索引以及LIMIT 子句后，实际执行时间变得很小。

总结：

数据库编译器产生执行计划，决定着SQL的实际执行方式。但是编译器只是尽力服务，所有数据库的编译器都不是尽善尽美的。上述提到的多数场景，在其它数据库中也存在性能问题。了解数据库编译器的特性，才能避规其短处，写出高性能的SQL语句。

程序员在设计数据模型以及编写SQL语句时，要把算法的思想或意识带进来。编写复杂SQL语句要养成使用WITH语句的习惯。简洁且思路清晰的SQL语句也能减小数据库的负担
。