当前位置:首页- 建站教程 - 数据库8种常见SQL错误用法-危笑资源

数据库8种常见SQL错误用法-危笑资源

2019-11-13 作者:危笑资源网  分类: 建站教程    阅读:1422 天猫内部优惠券

1,LIMIT语句

对于下面简单的语句,一般DBA认为的方法是在类型,名称,create_time上下上组合索引。能有效的利用到索引,性能迅速提升。

  1. 选择 *
  2. 从操作
  3. WHERE类型='SQLStats'
  4. AND名称='SlowLog'
  5. ORDER BY create_time
  6. LIMIT 1000,10;
复制代码

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

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

在重新数据浏览翻页,或者大数据分批添加等场景下,是可以将上一页的完全当成参数作为查询条件的。SQL重新设计如下:

  1. 选择 *
  2. 从操作
  3. WHERE类型='SQLStats'
  4. AND名称='SlowLog'
  5. AND create_time>'2017-03-16 14:00:00'
  6. ORDER BY create_time限制为10;
复制代码

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

2,隐式转换

SQL语句中查询变量和变量定义类型不匹配是另一个常见的错误。

  1. mysql>解释扩展的SELECT *
  2. >从my_balance b
  3. >其中b.bpn = 14000000123
  4. > AND b.isverified IS NULL;
  5. mysql>显示警告;
  6. 警告| 1739 | 由于字段“ bpn”上的类型或排序规则转换,因此无法在索引“ bpn”上使用引用访问
复制代码

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

现在应用框架很多很繁杂,使用方便的同时也小心它可能给自己挖坑。

3,关联更新,删除

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

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

  1. 更新操作o
  2. SET状态=“正在申请”
  3. o.id在哪里(选择ID
  4. 从(选择o.id,
  5. 状态
  6. 从操作o
  7. o.group = 123
  8. AND o.status NOT IN('done')
  9. o.parent订购
  10. o.id
  11. LIMIT 1)t);
复制代码

执行计划:

  1. ---- -------------------- ------- ------- ------------ --- --------- --------- ------- ------ ---------------- -------------------------------------
  2. id | select_type | 桌子| 类型 可能的钥匙| 关键 key_len | 参考| 行| 额外|
  3. ---- -------------------- ------- ------- ------------ --- --------- --------- ------- ------ ---------------- -------------------------------------
  4. 1 | 主要| o | 索引| 主要| 8 | 24 | 在哪里使用 使用临时|
  5. 2 | 依赖子查询| 读取const表后,不可能在哪里注意到
  6. 3 | 派生| o | 参考| idx_2,idx_5 | idx_5 | 8 | const | 1 | 在哪里使用 使用文件排序|
  7. ---- -------------------- ------- ------- ------------ --- --------- --------- ------- ------ ---------------- -------------------------------------
复制代码

改为为JOIN之后,子查询的选择模式从DEPENDENT子查询转换为DERIVED,执行速度大大加快,从7秒降低到2秒钟。

  1. 更新操作o
  2. 加入(SELECT o.id,
  3. 状态
  4. 从操作o
  5. o.group = 123
  6. AND o.status NOT IN('done')
  7. o.parent订购
  8. o.id
  9. 极限1)t
  10. ON o.id = t.id
  11. SET状态=“正在申请”  
复制代码

执行计划简化为:


---- ------------- ------- ------ --------------- ----- ---------- ------- ------ -------------------------- --------------------------- | id | select_type | 桌子| 类型 可能的钥匙| 关键 key_len | 参考| 行| 额外| ---- ------------- ------- ------ --------------- ----- ---------- ------- ------ -------------------------- --------------------------- | 1 | 主要| 读取const表后,不可能在哪里注意到 2 | 派生| o | 参考| idx_2,idx_5 | idx_5 | 8 | const | 1 | 在哪里使用 使用文件排序| ---- ------------- ------- ------ --------------- ----- ---------- ------- ------ -------------------------- ---------------------------

4,混合排序

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


SELECT * FROM my_order o INNER JOIN my_appraise一个ON a.orderid = o.id OR.by a.is_reply ASC,a.appraise_time DESC LIMIT 0,20  

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


---- ------------- ------- -------- ------------- ----- ---- --------- --------------- ----------| id | select_type | 桌子| 类型 可能的钥匙| 关键 key_len | 参考| 行| 额外---- ------------- ------- -------- ------------- ---- ----- --------- --------------- ----------| 1 | 简单 一个| 全部| idx_orderid | NULL | NULL | NULL | 1967647 | 使用文件排序| 1 | 简单 o | eq_ref | 主要| 主要| 122 | a.orderid | 1 | NULL | ---- ------------- ------- -------- --------- --------- --------- ----------------- ----------

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


SELECT * FROM((SELECT * FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.id AND is_reply = 0 ORDER BY appraise_time DESC LIMIT 0,20)UNION ALL(SELECT * FROM my_order o INNER JOIN my_appraise a ON a。 orderid = o.id AND is_reply = 1 ORDER BY appraise_time DESC LIMIT 0,20))t ORDER BY is_reply ASC,评估时间DESC LIMIT 20;  

5,EXISTS语句

MySQL处理EXISTS子句时,仍采用交替子查询的执行方式。如下面的SQL语句:


SELECT * FROM my_neighbor n左联接my_neighbor_apply sra ON n.id = sra.neighbor_id AND sra.user_id ='xxx'其中n.topic_status <4并且存在(从message_info m中选择1,其中n.id = m.neighbor_id AND m。 inuser ='xxx')和n.topic_type <> 5

执行计划为:


---- -------------------- ------- ------ ----- -------- ---------------------------------- --------- ------- --------- ----- | id | select_type | 桌子| 类型 可能的钥匙| 关键 key_len | 参考| 行| 额外| ---- -------------------- ------- ------ ----- -------- ---------------------------------- --------- ------- --------- ----- | 1 | 主要| n | 全部| NULL | NULL | NULL | 1086041 | 在哪里使用 1 | 主要| sra | 参考| idx_user_id | 123 | const | 1 | 在哪里使用 2 | 依赖子查询| 米| 参考| idx_message_info | 122 | const | 1 | 使用索引条件;在哪里使用 ---- -------------------- ------- ------ ----- -------- ---------------------------------- --------- ------- --------- -----

去掉存在更改为join,能够避免重叠子查询,将执行时间从1.93秒降低为1秒钟。


SELECT * FROM my_neighbor n INNER JOIN message_info m ON n.id = m.neighbor_id AND m.inuser ='xxx'左联接my_neighbor_apply sra ON n.id = sra.neighbor_id AND sra.user_id ='xxx'WHERE n.topic_status < 4 AND n.topic_type <> 5

新的执行计划:


---- ------------- ------- -------- ----- ------------- ----------------------------- --------- ----- ------- ---- | id | select_type | 桌子| 类型 可能的钥匙| 关键 key_len | 参考| 行| 额外| ---- ------------- ------- -------- ----- ------------- ----------------------------- --------- ----- ------- ---- | 1 | 简单 米| 参考| idx_message_info | 122 | const | 1 | 使用索引条件 1 | 简单 n | eq_ref | 主要| 122 | ighbor_id | 1 | 在哪里使用 1 | 简单 sra | 参考| idx_user_id | 123 | const | 1 | 在哪里使用 ---- ------------- ------- -------- ----- ------------- ----------------------------- --------- ----- ------- ----

6,条件下推

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

  • 聚合子查询;
  • 包含LIMIT的子查询;
  • UNION或UNION ALL子查询;
  • 输出细分中的子查询;

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


SELECT * FROM(SELECT目标,Count(*)FROM操作GROUP BY目标)t WHERE目标='rm-xxxx'

---- ---------------- ------------ ------- -------------- -------------- --------- ------- ------ ------------- | id | select_type | 桌子| 类型 可能的钥匙| 关键 key_len | 参考| 行| 额外| ---- ---------------- ------------ ------- -------------- -------------- --------- ------- ------ ------------- | 1 | 主要| <derived2> | 参考| <auto_key0> | <auto_key0> | 514 | const | 2 | 在哪里使用 2 | 派生| 操作| 索引| idx_4 | idx_4 | 519 | NULL | 20 | 使用索引 ---- ---------------- ------------ ------- -------------- -------------- --------- ------- ------ -------------

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


SELECT目标,Count(*)来自操作WHERE目标='rm-xxxx'GROUP BY目标

执行计划体现:


---- ------------- ----------- ------ ---------------- ------ --------- ------- ------ -------------------- | id | select_type | 桌子| 类型 可能的钥匙| 关键 key_len | 参考| 行| 额外| ---- ------------- ----------- ------ ---------------- ------ --------- ------- ------ -------------------- | 1 | 简单 操作| 参考| idx_4 | idx_4 | 514 | const | 1 | 在哪里使用 使用索引 ---- ------------- ----------- ------ ---------------- ------ --------- ------- ------ --------------------

7,提前缩小范围

先上初始SQL语句:


SELECT * FROM my_order o左联接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)按o.selltime排序DESC LIMIT 0、15

该SQL语句原意是:先做一段的左连接,然后排序取前15条记录。从执行计划也可以裁剪,最后一步将记录数设为90万,时间消耗为12秒。


---- ------------- ------- -------- --------------- --- ------ --------- ----------------- -------- ---------- ------------------------------------------ | id | select_type | 桌子| 类型 可能的钥匙| 关键 key_len | 参考| 行| 额外| ---- ------------- ------- -------- --------------- --- ------ --------- ----------------- -------- ---------- ------------------------------------------ | 1 | 简单 o | 全部| NULL | NULL | NULL | NULL | 909119 | 在哪里使用 使用临时;使用文件排序| 1 | 简单 你 eq_ref | 主要| 主要| 4 | o.uid | 1 | NULL | 1 | 简单 p | 全部| 主要| NULL | NULL | NULL | 6 | 在哪里使用 使用联接缓冲区(块嵌套循环)---- ------------- ------- -------- --------------- --- ------ --------- ----------------- -------- ---------- ------------------------------------------

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


SELECT * FROM(SELECT * FROM my_order o在(o.display = 0)AND(o.ostaus = 1)OR.selltime DESC LIMIT 0,15排序o左联接my_userinfo u ON o.uid = u.uid左JOIN my_productinfo p ON o.pid = p.pid OR BY BY o.selltime DESC限制0、15

再检查执行计划:子查询物化后(select_type = DERIVED)参与JOIN。虽然逐步行扫描仍然为90万,但利用了索引以及LIMIT子句后,实际执行时间变得很小。


---- ------------- ------------ -------- ------------- ---------- --------- ------- -------- --------------- ------------------------------------- | id | select_type | 桌子| 类型 可能的钥匙| 关键 key_len | 参考| 行| 额外| ---- ------------- ------------ -------- ------------- ---------- --------- ------- -------- --------------- ------------------------------------- | 1 | 主要| <derived2> | 全部| NULL | NULL | NULL | NULL | 15 | 使用临时;使用文件排序| 1 | 主要| 你 eq_ref | 主要| 主要| 4 | o.uid | 1 | NULL | 1 | 主要| p | 全部| 主要| NULL | NULL | NULL | 6 | 在哪里使用 使用联接缓冲区(块嵌套循环)2 | 派生| o | 索引| NULL | idx_1 | 5 | NULL | 909112 | 在哪里使用 ---- ------------- ------------ -------- ------------- ---------- --------- ------- -------- --------------- -------------------------------------

8,中间结果集下推

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


SELECT a。*,c.allocation FROM(SELECT myid from my_distribute d WHERE isdelete = 0 AND cusmanagercode ='1234567'OR BY BY salecode limit 20)一个LEFT JOIN(SELECT resourcesid,sum(ifnull(allocation,0)* 12345)分配FROM my_resources GROUP BY resourcesid)c打开a.resourceid = c.resourcesid

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

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


SELECT a。*,c.allocation FROM(SELECT myid from my_distribute d WHERE isdelete = 0 AND cusmanagercode ='1234567'OR BY BY salecode limit 20)一个LEFT JOIN(SELECT resourcesid,sum(ifnull(allocation,0)* 12345)分配FROM my_resources r,((从my_distribute d选择SELECT resourceid,如果isdelete = 0并且cusmanagercode ='1234567'OR BY BY salecode限制20)WHERE r.resourcesid = a.resourcesid GROUP BY resourcesid)

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


使用AS(从my_distribute d的SELECT resourceid isdelete = 0和cusmanagercode ='1234567'的ORDER BY salecode限制20)从左联接中选择a。*,c。分配(SELECT resourcesid,sum(ifnull(allocation,0)* 12345)从my_resources r分配,WHERE r.resourcesid = a.resourcesid GROUP BY resourcesid)c ON a.resourceid = c.resourcesid

总结

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

了解数据库编译器的特性,能够避免法规其短处,写出高效的SQL语句。

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

编写且复杂的SQL语句也能解析数据库的负担。


欢迎关注官方公众号

欢迎关注官方公众号  欢迎关注官方公众号

发表评论

快捷回复:

欢迎关注官方公众号

欢迎关注官方公众号

腾讯云服务器99元一年

腾讯云服务器99元一年

免费领流量卡

免费领流量卡

最新微语

  • 危笑资源网,欢迎各位投稿分享,投稿审核通过的用户有奖哦!!!!要求:投稿要有质量,介绍,图片,以及文字说明,如不达标一律无效,谢谢支持!!!!

    2019-11-27 16:42