8个SQL错误:您是否犯了这些错误?
SQL编程既令人兴奋又具有挑战性。 即使是经验丰富的SQL程序员,开发人员和数据库管理员(DBA)有时也要面对SQL语言的挑战。 本文旨在帮助用户识别此类严重错误并学习克服它们。
让我们深入研究以下几节中最严重的8个SQL错误。
1)LIMIT语句
分页查询是最常见的情况之一,但也是一个非常普遍的问题。 例如,对于以下简单语句,DBA通常为type,name和create_time字段添加复合索引。 这种条件排序可有效使用索引并快速提高性能。 这是90%以上的DBA解决此问题的常用方法。 但是,当LIMIT子句更改为" LIMIT 1000000,10"时,程序员经常抱怨仅检索10条记录会花费太长时间。 发生这种情况是因为数据库不知道第1,000,000条记录从何处开始。 因此,即使索引可用,也必须从头开始计算。 通常由于程序员的懒惰而导致出现此性能问题。 在前端数据浏览和分页或大数据的批量导出之类的方案中,可以将上一页的最大值用作查询条件。 重写SQL代码,如下所示:
![8个SQL错误:您是否犯了这些错误?](https://s5.51cto.com/oss/202007/01/367d15d9163acb965c11cbb0806bc474.jpeg)
使用新设计,查询时间基本上是固定的,并且不会随着数据量的增加而改变。
2)隐式转换
当查询变量与SQL语句中的字段定义类型不匹配时,会发生另一种常见错误。 以下语句就是这样的一个例子:
![8个SQL错误:您是否犯了这些错误?](https://s2.51cto.com/oss/202007/01/35d39fe02a1df96aee95db7d4f6a0287.jpeg)
bpn字段定义为varchar(20),MySQL策略是在比较之前将字符串转换为数字。 当函数作用于表字段时,索引变为无效。 前面的问题可能是由应用程序框架自动完成的参数引起的,而不是由于程序员的有意识的错误。 当前,许多应用程序框架都很复杂。 尽管它们使用起来非常方便,但是您还必须意识到它们可能引起的潜在问题。
3)更新和删除加入
尽管实例化功能是MySQL 5.6中引入的,但是请注意,目前仅针对查询语句进行了优化。 手动将UPDATE或DELETE语句重写为JOIN语句。
例如,在下面的UPDATE语句中,MySQL实际上运行循环或嵌套子查询(DEPENDENT SUBQUERY),并且执行时间相对较长。
![8个SQL错误:您是否犯了这些错误?](https://s1.51cto.com/oss/202007/01/ae991df044c9a87dd055c18513870f9c.jpeg)
考虑以下执行计划。
![8个SQL错误:您是否犯了这些错误?](https://s5.51cto.com/oss/202007/01/860b78bb5e87844ea650c7b9f3d9d44e.jpeg)
将语句重写为JOIN语句后,子查询选择模式将从DEPENDENT SUBQUERY更改为DERIVED,从而将所需时间从7秒减少到2毫秒。
![8个SQL错误:您是否犯了这些错误?](https://s3.51cto.com/oss/202007/01/757e0ad235e99f689d89dfe824c39900.jpeg)
请参考以下简化的执行计划。
![8个SQL错误:您是否犯了这些错误?](https://s5.51cto.com/oss/202007/01/ee80567dbb6f9706c7e53f94c89c6932.jpeg)
4)混合排序
MySQL不能将索引用于混合排序。 但是,在某些情况下,用户仍然可以使用特殊方法来提高性能。
![8个SQL错误:您是否犯了这些错误?](https://s2.51cto.com/oss/202007/01/7570d2cb689a4009b5e3cfaf29273602.jpeg)
执行计划以全表扫描形式呈现。
由于is_reply在按照方法重写后仅具有状态0和1,因此执行时间从1.58秒减少到2毫秒。
![8个SQL错误:您是否犯了这些错误?](https://s1.51cto.com/oss/202007/01/539c5142c44275bd5313d7076a8f4004.jpeg)
5)EXISTS 陈述
MySQL仍然使用嵌套子查询来处理EXISTS子句。 例如,考虑下面的SQL语句:
![8个SQL错误:您是否犯了这些错误?](https://s4.51cto.com/oss/202007/01/d43a2f6a8f90d078e8df19f4b5119ef9.jpeg)
请参考以下执行计划。
![8个SQL错误:您是否犯了这些错误?](https://s3.51cto.com/oss/202007/01/849d986b14282ac6196102ba7596d27c.jpeg)
将EXISTS语句更改为JOIN语句可避免嵌套子查询,并将执行时间从1.93秒减少到1毫秒。
![8个SQL错误:您是否犯了这些错误?](https://s2.51cto.com/oss/202007/01/7ffabaa1bf91ab95b9127036589b1f79.jpeg)
考虑下面的新执行计划。
![8个SQL错误:您是否犯了这些错误?](https://s1.51cto.com/oss/202007/01/e58c835ce237715ba9b9e94e9bf95ab6.jpeg)
6)有条件下推
在以下情况下,无法将外部查询条件下推到复杂视图或子查询:
- 汇总子查询
- LIMIT个子查询
- UNION或UNION ALL子查询
- 输出字段中的子查询
在以下语句的执行计划中,请注意,条件在聚集子查询之后起作用。
![8个SQL错误:您是否犯了这些错误?](https://s3.51cto.com/oss/202007/01/05a25c5786b755c6a07b2bc1748a0cef.jpeg)
![8个SQL错误:您是否犯了这些错误?](https://s3.51cto.com/oss/202007/01/4f645fbbbed639178ffe414e605008f4.jpeg)
确保直接向下推语义查询条件,然后将其重写如下:
请参考以下更新的执行计划。
![8个SQL错误:您是否犯了这些错误?](https://s1.51cto.com/oss/202007/01/8b380f6a02ea77ef1670873e647e6c8a.jpeg)
7)提前缩小范围
从初始SQL语句开始,如下所示。
![8个SQL错误:您是否犯了这些错误?](https://s4.51cto.com/oss/202007/01/cb892bd3cba99a62bd05b6360631f4a9.jpeg)
数量为900,000,执行时间为12秒。
![8个SQL错误:您是否犯了这些错误?](https://s1.51cto.com/oss/202007/01/718760a17af5d9a98e1fff6e2baf6a47.jpeg)
由于最后一个WHERE条件和排序是在最左边的主表上执行的,因此在执行左连接之前,首先要缩小数据量以进行my_order排序。 如下所示重写SQL语句后,执行时间减少到大约1毫秒。
![8个SQL错误:您是否犯了这些错误?](https://s4.51cto.com/oss/202007/01/ef5716fbb8942b082fbd5776229e1a36.jpeg)
查看执行计划。 实现子查询后,select_type = DERIVED参与JOIN操作。 尽管估计要扫描的行数仍为900,000,但在应用索引和LIMIT子句后,实际的执行时间会减少。
![8个SQL错误:您是否犯了这些错误?](https://s3.51cto.com/oss/202007/01/40e75e76141cff02a83e79de34f544f2.jpeg)
8)下推中间结果集
让我们看一下以下最初优化的示例(查询条件首先作用于左联接中的主表):
![8个SQL错误:您是否犯了这些错误?](https://s5.51cto.com/oss/202007/01/ea6c318eb6d83c8c7744eda808b3bcfa.jpeg)
此声明还有其他问题吗? 不难看出,子查询c是全表聚合查询。 因此,当表的数量特别多时,整个语句的性能会下降。
实际上,对于子查询c,左联接的最终结果集仅与匹配主表resourceid的数据有关。 因此,按如下所示重写该语句,以将执行时间从2秒减少到2毫秒。
![8个SQL错误:您是否犯了这些错误?](https://s2.51cto.com/oss/202007/01/4d3d97a99ca7ba7844ddcade87e3f871.jpeg)
但是,子查询多次出现在SQL语句中。 此方法不仅会导致额外的开销,而且会使整个语句更加复杂。 使用WITH语句再次重写该语句。
![8个SQL错误:您是否犯了这些错误?](https://s3.51cto.com/oss/202007/01/ee06489b4bd213bbb8b188b7926c8d0b.jpeg)
摘要
数据库编译器生成一个执行计划,该计划确定SQL语句的实际执行方法。 但是,编译器仅尽其所能提供服务,而没有哪个数据库编译器是完美的。
在大多数上述情况下,其他数据库中也会出现性能问题。 您必须了解数据库编译器的功能,以避免其缺点并编写高性能的SQL语句。
在设计数据模型和编写SQL语句时,请结合您的算法思想和认识。 例如,在编写复杂的SQL语句时,请尽可能使用WITH子句。 简单明了的SQL语句还可以减轻数据库的负担。
相关文章
- Java String类
- 通过无服务器架构实现Amazon Neptune图数据库数据分析可视化
- 利用 AWS Glue 自动触发数据目录和 ETL job 构建自动化无服务器数据湖
- Amazon EMR 5.24 中的 Apache Spark 性能升级 — 性能比 Amazon EMR 5.16 最高提升 13 倍 | AWS 大数据博客
- Amazon Forecast – 现已正式发布
- 向使用 SAML 和 Okta 的 Open Distro for Elasticsearch Kibana 添加单点登录 (SSO)
- 2019 年 Amazon Prime 会员日 — 由 AWS 提供支持
- java----------Integer类
- Elasticsearch 分片分配探秘
- Python bytes 的使用
- 认证授权专题(五) : API Gateway + Custom Authorizer 精细化权限控制
- AWS Lake Formation – 现已正式推出
- 通过 IoT 设备模拟器、AWS IoT Analytics、Amazon QuickSight、Microsoft Power BI 和 Tableau 了解商业结果 | AWS 上的物联网
- 使用 Elasticsearch 的 _rollover API 以提高存储分配的效率
- Open Distro for Elasticsearch 1.1.0 正式发布
- 基于个性化查询场景的 Amazon Redshift 压力测试方案
- 使用 LoRaWAN 将您的设备连接到 AWS IoT | AWS 上的物联网
- Python 10 训练模型
- Amazon Transcribe Streaming 现在支持 WebSocket
- AWS 与 SAP 宣布推出 IoT 互操作性解决方案