8种常被忽视的SQL错误用法
zhangmin
zhangmin 868 0
2019-09-23 14:09
文水电子商务。商务合作QQ:群:483714163
撸了今年阿里、腾讯和美团的面试,我有一个重要发现…….

作者:一杯甜酒
原文:https://blog.csdn.net/u012562943/article/details/71403500
sql语句的执行顺序:
FROM ON  JOIN WHERE GROUP BY HAVING SELECTDISTINCT ORDER BY LIMIT 1. LIMIT 语句

分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。比如对于下面简单的语句,一般 DBA 想到的办法是在 type, name, create_time 字段上加组合索引。这样条件排序都能有效的利用到索引,性能迅速提升。
SELECT * FROM   operation WHERE  type = 'SQLStats' AND name = 'SlowLog' ORDER  BY create_time LIMIT  1000, 10;好吧,可能90%以上的 DBA 解决该问题就到此为止。但当 LIMIT 子句变成 “LIMIT 1000000,10” 时,程序员仍然会抱怨:我只取10条记录为什么还是慢?
要知道数据库也并不知道第1000000条记录从什么地方开始,即使有索引也需要从头计算一次。出现这种性能问题,多数情形下是程序员偷懒了。
在前端数据浏览翻页,或者大数据分批导出等场景下,是可以将上一页的最大值当成参数作为查询条件的。SQL 重新设计如下:
SELECT * FROM operation WHERE type = 'SQLStats' AND name = 'SlowLog' AND create_time > '2017-03-16 14:00:00' ORDER BY create_time limit 10;在新设计下查询时间基本固定,不会随着数据量的增长而发生变化。
2. 隐式转换

SQL语句中查询变量和字段定义类型不匹配是另一个常见的错误。比如下面的语句:
mysql> explain extended SELECT * FROM  my_balance b    > WHERE  b.bpn = 14000000123    >       AND b.isverified IS NULL ;mysql> show warnings;| Warning | 1739 | Cannot use ref access on index 'bpn' due to type or collation conversion on field 'bpn'其中字段 bpn 的定义为 varchar(20),MySQL 的策略是将字符串转换为数字之后再比较。函数作用于表字段,索引失效。
上述情况可能是应用程序框架自动填入的参数,而不是程序员的原意。现在应用框架很多很繁杂,使用方便的同时也小心它可能给自己挖坑。
3. 关联更新、删除

虽然 MySQL5.6 引入了物化特性,但需要特别注意它目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成 JOIN。
比如下面 UPDATE 语句,MySQL 实际执行的是循环/嵌套子查询(DEPENDENT SUBQUERY),其执行时间可想而知。
UPDATE operation o SET 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,执行速度大大加快,从7秒降低到2毫秒。
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) tON o.id = t.id SET    status = 'applying'执行计划简化为:

4. 混合排序

MySQL 不能利用索引进行混合排序。但在某些场景,还是有机会使用特殊方法提升性能的。
SELECT * FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.id ORDER  BY a.is_reply ASC, a.appraise_time DESC LIMIT  0, 20执行计划显示为全表扫描:

由于 is_reply 只有0和1两种状态,我们按照下面的方法重写后,执行时间从1.58秒降低到2毫秒。
SELECT * FROM ((SELECT * FROM my_order o INNER JOIN my_appraise a  ON a.orderid = o.idAND 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.idAND is_reply = 1 ORDER  BY appraise_time DESC LIMIT  0, 20)) t ORDER  BY  is_reply ASC, appraisetime DESC LIMIT  20;5. EXISTS语句

MySQL 对待 EXISTS 子句时,仍然采用嵌套子查询的执行方式。如下面的 SQL 语句:
SELECT *FROM   my_neighbor n      LEFT JOIN my_neighbor_apply sra             ON n.id = sra.neighbor_id                AND sra.user_id = 'xxx' WHERE  n.topic_status

本文内容来源文水手机台,如有侵权请立即与我们联系,我们将及时处理!
分享:
游客
要评论请先登录 或者 注册
返回顶部