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

sql 同时被 3 个专栏收录
1 篇文章 0 订阅
1 篇文章 0 订阅
1 篇文章 0 订阅

今天我们来盘点一下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

用法六 复杂视图

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

  1. 聚合子查询;
  2. 含有LIMIT的子查询;
  3. UNION 或UNION ALL子查询;
  4. 输出字段中的子查询;
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语句也能减小数据库的负担

  • 1
    点赞
  • 1
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 1024 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值