8 种很坑的 SQL 错误用法

末蓝、 2021-08-19 17:04 389阅读 0赞

点击上方 [Java后端][Java]，选择 设为星标

优质文章，及时送达![format_png][]作者 | 程序员追风  
链接 | juejin.im/post/5dd15451e51d453b3d3d4329 上篇 |   [一千行 MySQL 学习笔记][MySQL]

## **1、LIMIT 语句**    ##

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

好吧，可能90%以上的 DBA 解决该问题就到此为止。但当 LIMIT 子句变成 “LIMIT 1000000,10” 时，程序员仍然会抱怨：我只取10条记录为什么还是慢？

要知道数据库也并不知道第1000000条记录从什么地方开始，即使有索引也需要从头计算一次。出现这种性能问题，多数情形下是程序员偷懒了。

在前端数据浏览翻页，或者大数据分批导出等场景下，是可以将上一页的最大值当成参数作为查询条件的。SQL 重新设计如下：

![format_png 1][]

在新设计下查询时间基本固定，不会随着数据量的增长而发生变化。

## **2、隐式转换** ##

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

![format_png 2][]

其中字段 bpn 的定义为 varchar(20)，MySQL 的策略是将字符串转换为数字之后再比较。函数作用于表字段，索引失效。

上述情况可能是应用程序框架自动填入的参数，而不是程序员的原意。现在应用框架很多很繁杂，使用方便的同时也小心它可能给自己挖坑。

## **3、关联更新、删除** ##

虽然 MySQL5.6 引入了物化特性，但需要特别注意它目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成 JOIN。

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

![format_png 3][]

执行计划：

![format_png 4][]

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

![format_png 5][]

执行计划简化为：

![format_png 6][]

## **4、混合排序** ##

MySQL 不能利用索引进行混合排序。但在某些场景，还是有机会使用特殊方法提升性能的。

![format_png 7][]

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

![format_png 8][]

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

![format_png 9][]

## **5、EXISTS语句** ##

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

![format_png 10][]

执行计划为：

![format_png 11][]

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

![format_png 12][]

新的执行计划：

![format_png 13][]

**6、条件下推**

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

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

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

![format_png 14][]

![format_png 15][]

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

![format_png 16][]

执行计划变为：

![format_png 17][]

**7、提前缩小范围**

先上初始 SQL 语句：

![format_png 18][]

数为90万，时间消耗为12秒。

![format_png 19][]

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

![format_png 20][]

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

![format_png 21][]

## **8、中间结果集下推** ##

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

![format_png 22][]

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

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

![format_png 23][]

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

![format_png 24][]

**总结**

数据库编译器产生执行计划，决定着SQL的实际执行方式。但是编译器只是尽力服务，所有数据库的编译器都不是尽善尽美的。

上述提到的多数场景，在其它数据库中也存在性能问题。了解数据库编译器的特性，才能避规其短处，写出高性能的SQL语句。

程序员在设计数据模型以及编写SQL语句时，要把算法的思想或意识带进来。

编写复杂SQL语句要养成使用 WITH 语句的习惯。简洁且思路清晰的SQL语句也能减小数据库的负担 。

--------------------

【END】

如果看到这里，说明你喜欢这篇文章，请 **转发** **、点赞**。微信搜索「web\_resource」，关注后回复「进群」或者扫描下方二维码即可进入无广告交流群。**↓扫描二维码进群↓**![format_png 25][]

--------------------

**推** **荐** **阅** **读 *****1.***  [一千行 MySQL 学习笔记][MySQL]***2.***    [一份工作坚持多久跳槽最合适？][Link 1]***3.***  [项目中常用到的 19 条 MySQL 优化][19 _ MySQL]***4. *** *** *** [零基础认识 Spring Boot][Spring Boot]

***5.***  [团队开发中 Git 最佳实践][Git]

![format_png 26][]

**喜** **欢** **文** **章** **，** **点** **个** **在看 ** ![format_png 27][]

[Java]: https://blog.csdn.net/qq_37217713/article/details/101445094
[format_png]: /images/20210731/82f09bd28b394958921063ec65ec4843.png
[MySQL]: https://blog.csdn.net/qq_37217713/article/details/102658688
[format_png 1]: /images/20210731/2f4bd63b6d3a43648e134d5707792b43.png
[format_png 2]: /images/20210731/840ec5fffa3447768d196ea8c6eafdc8.png
[format_png 3]: /images/20210731/0b01ce2569a64cfcbf5cfc447dfb1c9b.png
[format_png 4]: /images/20210731/ca051d2a98684ca5acb1766bc5dd8ece.png
[format_png 5]: /images/20210731/ed646005e0cd415d8f42dcadfb867734.png
[format_png 6]: /images/20210731/0b0a6529979a4d608e350772d5ee0317.png
[format_png 7]: /images/20210731/6dd7a791c3d041439a340deca3bdc6a2.png
[format_png 8]: /images/20210731/362d4aa805284a0993be393800709032.png
[format_png 9]: /images/20210731/036ad779348e4a5da3a0afe1beb5c7ab.png
[format_png 10]: /images/20210731/47685a0c869a4e5cb83c50769887ffd9.png
[format_png 11]: /images/20210731/17b109b01eb749bbbe0275e7e6059806.png
[format_png 12]: /images/20210731/4acd125bbc1742099bd10873ab8471fd.png
[format_png 13]: /images/20210731/a0a6372095d04871b55d689c15656f14.png
[format_png 14]: /images/20210731/5ccca01e79b1430f8859550ebfcb103b.png
[format_png 15]: /images/20210731/691e67d6708b4fe79eb4aaf0ab23d8f8.png
[format_png 16]: /images/20210731/fbe023f7cc78431fb11e5393a60efac9.png
[format_png 17]: /images/20210731/9f47042284d1499484bf828d8b3de7aa.png
[format_png 18]: /images/20210731/4772e796956f49f2b18d575a1201b9e8.png
[format_png 19]: /images/20210731/ecaf3c11793945de9370d37d1e7fef2d.png
[format_png 20]: /images/20210731/35c08cafcd924f04b9cc33dd6731715f.png
[format_png 21]: /images/20210731/3a68b91ca13a441aa6366bc3a00dafa5.png
[format_png 22]: /images/20210731/d6b55e47ac144feca28d7d8b6b738e6f.png
[format_png 23]: /images/20210731/0b245ca676e74bd5be53b4bf0270dc30.png
[format_png 24]: /images/20210731/53a75640a9724677a99a894afd8790d2.png
[format_png 25]: /images/20210731/90bf9055243941058c0b4bcaa3212131.png
[Link 1]: https://blog.csdn.net/qq_37217713/article/details/103170812
[19 _ MySQL]: https://blog.csdn.net/qq_37217713/article/details/103144284
[Spring Boot]: https://blog.csdn.net/qq_37217713/article/details/103094622
[Git]: https://blog.csdn.net/qq_37217713/article/details/102481709
[format_png 26]: /images/20210731/6186a77d7fb948ddb711d8a2d0d685fe.png
[format_png 27]: /images/20210731/b57177fc468b4de8a751ef686a7744ff.png