词条信息

admin
admin
超级管理员
词条创建者 发短消息   

相关词条

热门词条

更多>>
什么是端口?到底是做什么的呢?
端口一般指两种,一种是硬件比如路由器或者交换机的插网线的端口,一种是软件的逻辑的概念,比如http的80端口!...
7种进阶方法让你快速测试端口连通性
Ping是Windows、Linux和Unix系统下的一个检查网络连通性的命令工具,对于大部分互联网用户来说很...
电脑开机,总需要按F1,是什么原因造成的?
一.主板掉电这个说法是行业内的叫法了,一般是主板的CMOS电池没电了导致的。也是最常见的一种提示你按F1的提示...
社保降费对个人有什么影响?
下调城镇职工基本养老保险单位缴费比例是政府给企业发的一个大红包,特别是对于企业来说是一个利好,但是对个人来说有...
车辆“出险”对下年保费的影响,到底有多大?
【出险对交强险的影响】【出险对商业险的影响】车辆“出险”对下年保费的影响,到底有多大?这里有必要先提下车险第三...
解决网 >>所属分类 >> 程序开发    数据库   

8 种常被忽视的 SQL 错误用法

标签: MySQL sql

顶[0] 发表评论(0) 编辑词条

前言


MySQL在2016年仍然保持强劲的数据库流行度增长趋势。越来越多的客户将自己的应用建立在MySQL数据库之上,甚至是从Oracle迁移到MySQL上来。但也存在部分客户在使用MySQL数据库的过程中遇到一些比如响应时间慢,CPU打满等情况。阿里云RDS专家服务团队帮助云上客户解决过很多紧急问题。现将《ApsaraDB专家诊断报告》中出现的部分常见SQL问题总结如下,供大家参考。


常见SQL错误用法


目录

[显示全部]

1. LIMIT 语句编辑本段回目录


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

8 种常被忽视的 SQL 错误用法

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


要知道数据库也并不知道第1000000条记录从什么地方开始,即使有索引也需要从头计算一次。出现这种性能问题,多数情形下是程序员偷懒了。在前端数据浏览翻页,或者大数据分批导出等场景下,是可以将上一页的最大值当成参数作为查询条件的。SQL重新设计如下:


8 种常被忽视的 SQL 错误用法

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


2. 隐式转换编辑本段回目录


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


8 种常被忽视的 SQL 错误用法

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


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


3. 关联更新、删除编辑本段回目录


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


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


8 种常被忽视的 SQL 错误用法

8 种常被忽视的 SQL 错误用法


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


8 种常被忽视的 SQL 错误用法

执行计划简化为:

8 种常被忽视的 SQL 错误用法



4. 混合排序编辑本段回目录


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


8 种常被忽视的 SQL 错误用法

执行计划显示为全表扫描

8 种常被忽视的 SQL 错误用法

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

8 种常被忽视的 SQL 错误用法



5. EXISTS语句编辑本段回目录


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

8 种常被忽视的 SQL 错误用法

执行计划为:

8 种常被忽视的 SQL 错误用法


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

8 种常被忽视的 SQL 错误用法


新的执行计划:


8 种常被忽视的 SQL 错误用法




6. 条件下推编辑本段回目录


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


聚合子查询;

含有LIMIT的子查询;

UNION 或UNION ALL子查询;

输出字段中的子查询;

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



新的执行计划:


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

8 种常被忽视的 SQL 错误用法



7. 提前缩小范围编辑本段回目录


先上初始SQL语句:


8 种常被忽视的 SQL 错误用法


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


8 种常被忽视的 SQL 错误用法


8. 中间结果集下推编辑本段回目录


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


8 种常被忽视的 SQL 错误用法

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


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


8 种常被忽视的 SQL 错误用法

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


8 种常被忽视的 SQL 错误用法

AliSQL即将推出WITH语法,敬请期待。


总结编辑本段回目录


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

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

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

使用云上数据库遇到难点(不局限于SQL问题),随时寻求阿里云原厂专家服务的帮助。

 

 

附件列表


按字母顺序浏览:A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

→我们致力于为广大网民解决所遇到的各种电脑技术问题
 如果您认为本词条还有待完善,请 编辑词条

上一篇中国“芯”奋起 助力5G应用百花齐放
下一篇用人单位可以随意解除劳动合同的情况有哪些?

0
1. 本站部分内容来自互联网,如有任何版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
2. 本站内容仅供参考,如果您需要解决具体问题,建议您咨询相关领域专业人士。
3. 如果您没有找到需要的百科词条,您可以到百科问答提问或创建词条,等待高手解答。

关于本词条的提问

查看全部/我要提问>>