这些经常被忽视的SQL错误用法,你踩过几个坑?

2020-06-19 00:00:00 索引 查询 数据 语句 排序

之前已经讲过mysql的性能优化,也介绍了一些面试过程中经常被问到的一些问题。想了解的请看我之前的文章:《能避开很多坑的mysql面试题,你知道吗》。其实不只是在数据库设计的过程中容易犯一些低级的错误,日常开发过程中会经常忽略一些Sql的写法,从而导致系统性能低等一系列问题。今天就来总结哪些经常被我们忽视的SQL错误写法,看看你都踩过哪些坑?


一、LIMIT语句

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

SELECT *FROM   operationWHERE  type = 'xxx'       AND name = 'xxx'ORDER  BY create_timeLIMIT  1000, 10;

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

优化方案

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

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

select *from operationwhere id>1000000AND type = 'xxx'AND name = 'xxx'ORDER BY create_timelimit 10

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

二、隐式转换

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

explainselect *from my_balance bwhere b.bpn = 14000000123and b.isverified is null;

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

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

优化方案

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

所以,上面的sql语句改为如下即可:

explain extendedselect *from my_balance bwhere b.bpn = '14000000123'and b.isverified is null;

三、关联更新、删除

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

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

explainUPDATE operation oSET 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.idSET status = 'applying'

执行计划简化为:


四、Order by排序

MySQL中的两种排序方式:

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

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

优化方案

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

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

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

explainselect store_id,email,last_namefrom my_userorder by store_id,email,last_name;

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


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

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

五、混合排序

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

SELECT *FROM my_order o     INNER JOIN my_appraise a ON a.orderid = o.idORDER BY a.is_reply ASC,         a.appraise_time DESCLIMIT , 20

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


优化方案

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

六、where 条件的顺序

有些人会容易忽视where 条件的顺序问题,如果where 条件的顺序不对,很有可能会导致索引失效,查询性能慢等问题。以下两点是需要特别注意的:

1、排除数据越多的条件越靠前,where 条件从左往右执行的,在数据量小的时候不用考虑,但数据量大的时候必须要考虑条件的先后顺序。

2、左前缀原则,如果查询的时候查询条件匹配索引的左边连续一列或几列,则此列就可以被用到。如下:

select * from user where name=xx and city=xx ; //可以命中索引select * from user where name=xx ; // 可以命中索引select * from user where city=xx ; // 无法命中索引

这里需要注意的是,查询的时候如果两个条件都用上了,但是顺序不同,如 city= xx and name =xx,那么现在的查询引擎会自动优化为匹配联合索引的顺序,这样是能够命中索引的。

由于左前缀原则,在创建联合索引时,索引字段的顺序需要考虑字段值去重之后的个数,较多的放前面。ORDER BY子句也遵循此规则。

七、EXISTS语句

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

explainSELECT *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,能够避免嵌套子查询,这样会大大提高查询效率。

explainSELECT *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 子查询;

  • 输出字段中的子查询;

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

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

优化方案

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

explainSELECT target,       Count(*)FROM operationWHERE 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.pidWHERE o.display =       AND o.ostaus = 1ORDER BY o.selltime DESCLIMIT , 15

通过查询计划可以看出90万,时间消耗为12秒。

优化方案

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

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

再检查执行计划:


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

十、中间结果集下推

看下面这个sql语句:

SELECT a.*        c.allocatedFROM        FROM my_distribute d       LEFT JOIN        (            SELECT resourcesid, sum(allocation) allocated            FROM my_resources            GROUP BY resourcesid        ) cON a.resourceid = c.resourcesid

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

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

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

其实,这段sql我们还可以优化,可以将 my_distribute的查询结果作为临时表,这样能避免大量重复的代码。


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

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



推荐阅读:

  • SpringBoot入门系列(二)Controller介绍及如何返回json数据

  • SpringBoot入门系列(一)如何快速创建SpringBoot项

  • Nginx总结(五)如何配置Nginx和Tomcat实现反向代理

  • Nginx总结(四)基于域名的虚拟主机配置

  • Nginx总结(一)如何安装Nginx【详细教程】



相关文章