这些经常被忽视的SQL错误用法，你有没有踩过坑？

之前已经讲过mysql的性能优化，感兴趣的朋友可以看看之前的文章。但是有些问题其实是我们自身的SQL语句有问题导致的。今天就来总结哪些经常被我们忽视的SQL错误写法，看看你都踩过哪些坑？

一、LIMIT语句

Limit是分页查询是最常用的场景之一，但也通常也是最容易出问题的地方。比如对于下面简单的语句，一般我们觉得在type, name, create_time字段上加组合索引。这样条件排序都能有效的利用到索引，性能迅速提升。

SELECT * 
FROM   operation 
WHERE  type = 'xxx' 
       AND name = 'xxx' 
ORDER  BY create_time 
LIMIT  1000, 10;

但是当数据量很大的时候，当我们查询最后几页数据时，分页会越来越慢。这就是我们经常碰到的海量数据的分页问题。这是为什么呢？

优化方案

因为数据库也并不知道第1000000条记录从什么地方开始，即使有索引也需要从头计算一次，即进行全表扫描。出现这种性能问题，主要还是我们没有考虑到大量数据的情况。

其实在前端数据浏览翻页时，是可以将上一页的最大值作为查询条件传给后台的。SQL 重新设计如下：

select *
from operation
where id>1000000
AND type = 'xxx'
AND name = 'xxx'
ORDER BY create_time
limit 10

经过这种优化，可以保证系统不会随着数据量的增长而变慢。

二、隐式转换

SQL语句中查询变量和字段定义类型不匹配是另一个常见的错误。比如下面的语句：

explain extended
select * 
from my_balance b
where b.bpn = 14000000123
and b.isverified is null;

字段 bpn 的定义为 varchar 类型，而查询条件传入的却是int 类型。MySQL 会将字符串转换为数字之后再比较。函数作用于表字段，导致所以索引失效。如下图所示：

这个坑我们以前也遇见过，花了好半天才发现是这个问题。所以程序员在开发的过程中，一定要认真仔细，确保查询变量和字段类型匹配。

优化方案

保证传入的参数类型和字段定义的类型一致。

所以，上面的sql语句改为如下即可：

explain extended
select * 
from my_balance b
where b.bpn = '14000000123'
and b.isverified is null;

三、关联更新、删除

MySQL5.6之后有个新特性，会自动把SQL语句中的嵌套子查询优化为关联查询（join），所以有些时候你会发现嵌套子查询的效率和关联查询的效率差不多。但是需要特别注意mysql目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成 JOIN。

比如下面 UPDATE 语句，MySQL 实际执行的还是嵌套子查询（DEPENDENT SUBQUERY)，其执行时间可想而知。

explain extended
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);

执行计划：

优化方案

改为 JOIN 之后，子查询的选择模式从嵌套子查询(DEPENDENT SUBQUERY) 变成了关联查询(DERIVED)，执行速度大大加快

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 = 'applying1

执行计划简化为：

四、Order by

MySQL中的两种排序方式：

1、通过有序索引顺序扫描直接返回有序数据,因为索引的结构是B+树，索引中的数据是按照一定顺序进行排列的，所以在排序查询中如果能利用索引，就能避免额外的排序操作。EXPLAIN分析查询时，Extra显示为Using index。

2、Filesort排序，对返回的数据进行排序,所有不是通过索引直接返回排序结果的操作都是Filesort排序，也就是说进行了额外的排序操作。EXPLAIN分析查询时，Extra显示为Using filesort。

优化方案

一般排序的原则就是：尽量减少额外的排序，通过索引直接返回有序数据。

所以我们需要注意以下这些情况：

1、排序字段在多个索引中，无法使用索引排序，查询一次只能使用一个索引：

explain 
select store_id,email,last_name 
from my_user 
order by store_id,email,last_name;

查询计划显示，没有走所以直接返回有序数据，额外排序放回结果：

2、排序字段顺序与索引列顺序不一致，同样也无法利用索引排序。这个就不举例了跟where条件是一样的。

需要注意的是：这些都是细节的东西，经常会在开发过程中忽略。然后SQL就莫名其妙的不走索引了。

五、混合排序

索引中的数据是按照一定顺序进行排列的，所以在排序查询中如果能利用索引直接返回数据，就能避免额外的排序操作。但是如果出现这种混合了升序和降序的情况，MySQL 无法利用索引直接返回排序结果的。

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

执行计划显示为全表扫描：

优化方案

此类问题根据实际常见优化，原则就是应该避免这种排序的情况。如果确实有多种排序的需求，最好使用其他的方法提升性能。

六、EXISTS语句

MySQL 对待 EXISTS 子句时，会采用嵌套子查询的执行方式。如下面的 SQL 语句：

explain 
SELECT *
FROM my_order n
     LEFT JOIN my_appraise sra 
            ON n.id = sra.orderid 
            
WHERE 1=1
      AND EXISTS(SELECT 1
                    FROM my_user m
                    WHERE n.user_id = m.id 
                        AND m.usercode = '111' )
      AND n.id <> 5

执行计划为：

优化方案

去掉 exists 更改为 join，能够避免嵌套子查询，这样会大大提高查询效率。

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

新的执行计划显示没有了嵌套子查询：

七、条件下推

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

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

如下面的语句，从执行计划可以看出其条件作用于聚合子查询之后

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

优化方案

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

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

执行计划变为：

八、提前缩小数据范围

先上初始 SQL 语句：

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

数为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 子句后，实际执行时间变得很小。

九、中间结果集下推

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

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(allocation) allocated 
            FROM my_resources
            GROUP BY resourcesid
        ) c
ON a.resourceid = c.resourcesid

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

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

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

最后

以上总结了一些sql语句常见的坑。里面很多都是不仔细导致的。只有仔细认真尽量考虑一些大数据的情况，这样才能写出高性能的SQL语句。

同时，程序员在设计数据模型以及编写SQL语句时，要把索引及性能提前考虑进去，这样才能避免后期遇到的一些坑。

posted @ 2021-09-01 10:28 章为忠阅读(2607) 评论(22) 编辑收藏举报

刷新页面返回顶部

章为忠

【章小鱼】虽千万人，吾往矣。微信公众号：架构师精进

这些经常被忽视的SQL错误用法，你有没有踩过坑？

一、LIMIT语句

二、隐式转换

三、关联更新、删除

四、Order by

五、混合排序

六、EXISTS语句

七、条件下推

八、提前缩小数据范围

九、中间结果集下推

最后

公告

章为忠

【章小鱼】虽千万人，吾往矣。 微信公众号：架构师精进

这些经常被忽视的SQL错误用法，你有没有踩过坑？

一、LIMIT语句

二、隐式转换

三、关联更新、删除

四、Order by

五、混合排序

六、EXISTS语句

七、条件下推

八、提前缩小数据范围

九、中间结果集下推

最后

公告

【章小鱼】虽千万人，吾往矣。微信公众号：架构师精进