文档章节

MYSQL的UPDATE子查询,UPDATE时避免使用子查询

 刘景刘恒
发布于 2017/05/31 16:43
字数 545
阅读 1
收藏 0

大概率

 

MYSQL的UPDATE子查询,UPDATE时避免使用子查询

近日写mysql储存过程的时候,有个SQL执行不动:

UPDATE t_csi_comment
SET is_valid = 0
WHERE
        comment_id IN (
                SELECT
                        comment_id
                FROM
                        (
                                SELECT
                                        *
                                FROM
                                        t_csi_comment
                                WHERE
                                        DATE_FORMAT(comment_time, '%Y%m%d') BETWEEN 20170425
                                AND 20170528
                                ORDER BY
                                        comment_id DESC
                        ) a
                GROUP BY
                        openid,
                        dlr_code
                HAVING
                        count(1) > 2
        );
很奇怪,按道理这条SQL的检索量小于10W,应该怎么慢也不会几分钟不动的地步。

单独执行子查询:

SELECT
        comment_id
FROM
        (
                SELECT
                        *
                FROM
                        t_csi_comment
                WHERE
                        DATE_FORMAT(comment_time, '%Y%m%d') BETWEEN 20170425
                AND 20170528
                ORDER BY
                        comment_id DESC
        ) a
GROUP BY
        openid,
        dlr_code
HAVING
        count(1) > 2;
结果比想象中的快,1秒都不到,EXPLAIN后检索量不到4W行。我就郁闷了。

EXPLANIN第一条update语句:



注意:select_type 里出现了 DEPENDENT SUBQUERY。

这意味着什么?——子查询取决于外面的查询,Mysql 先执行外查询,内查询根据这个查询结果(如执行计划里所述,38196 rows)的每一条记录组成新的查询语句后执行。多重子查询情况下,我已经不想去解析它是如何转换SQL了。 Mysql在这点上并不比人类聪明。

解决办法(子查询转换成联表查询):

UPDATE t_csi_comment a INNER JOIN
 (
        SELECT
                comment_id
        FROM
                (
                        SELECT
                                *
                        FROM
                                t_csi_comment
                        WHERE
                                DATE_FORMAT(comment_time, '%Y%m%d') BETWEEN 20170425
                        AND 20170528
                        ORDER BY
                                comment_id DESC
                ) a
        GROUP BY
                openid,
                dlr_code
        HAVING
                count(1) > 2
) b ON a.comment_id = b.comment_id;
SET a.is_valid = 0
        


毫秒级别完工。

按理说,越复杂的程序逻辑关系要越明朗,出现复杂SQL的几率要越低。但是总会有一块业务相对复杂多变,无法把控,或者就是整个系统的架构不够明朗,脱离不了复杂SQL。这是在UPDATE时发现的子查询问题,在其它SQL语句中肯定也会有所体现,这是Mysql的查询机制问题,子查询会让Mysql变笨。所以还是慎用子查询,各种复杂SQL下尽量先测试吧。
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。互联网+时代,时刻要保持学习,携手千锋PHP,Dream It Possible。

© 著作权归作者所有

共有 人打赏支持
粉丝 1
博文 172
码字总数 139067
作品 0
私信 提问
Rails + MySQL 的三个常见的错误

本文译自:http://www.engineyard.com/blog/2011/3-common-rails-mysql-mistakes/ Rails 使得数据库的操作非常简单,因此经常会让你忘记数据库并不是总让你很happy,这里罗列了三个常见的 Ra...

红薯
2011/02/15
961
0
MySQL8.0新特性——锁读取

MySQL8.0 InnoDB支持 NOWAIT和SKIP LOCKED选项SELECT ... FOR SHARE以及SELECT ... FOR UPDATE锁定读取语句。 NOWAIT如果请求的行被另一个事务锁定,则会立即返回该语句。SKIP LOCKED从结果集...

一个笨小孩
2018/07/19
0
0
SQL优化之一则MySQL中的DELETE、UPDATE 子查询的锁机制失效案例

前言 开发与维护人员避免不了与 in/exists、not in/not exists 子查询打交道,接触过的人可能知道 in/exists、not in/not exists 相关子查询会使 SELECT 查询变慢,没有 join 连接效率,却不...

全部原谅
2018/07/27
0
0
MySQL can’t specify target table for update in FROM clause

今天在更新mysql以前的一些数据时,需要用到子查询的东西,忘了以前刚刚学习mysql的时候,讲到要给子查询给个额外的表名,很少直接写sql语句了。我再想如果不给个表面,mysql就不会生成临时表...

清天君
2016/11/08
4
0
10条SQL优化语句,让你的MySQL数据库跑得更快!

慢SQL消耗了70%~90%的数据库CPU资源; 1 不使用子查询 例:SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’); 子查询在MySQL5.5版本里,内部执行计划器是这样执行的...

kangjunfei
2017/12/14
0
0

没有更多内容

加载失败,请刷新页面

加载更多

大数据教程(11.9)hive操作基础知识

上一篇博客分享了hive的简介和初体验,本节博主将继续分享一些hive的操作的基础知识。 DDL操作 (1)创建表 #建表语法CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name ...

em_aaron
56分钟前
0
0
OSChina 周四乱弹 —— 我家猫真会后空翻

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @我没有抓狂 :#今天听这个# 我艇牛逼,百听不厌,太好听辣 分享 Led Zeppelin 的歌曲《Stairway To Heaven》 《Stairway To Heaven》- Led Z...

小小编辑
今天
1
0
node调用dll

先安装python2.7 安装node-gyp cnpm install node-gyp -g 新建一个Electron-vue项目(案例用Electron-vue) vue init simulatedgreg/electron-vue my-project 安装electron-rebuild cnpm ins......

Chason-洪
今天
3
0
scala学习(一)

学习Spark之前需要学习Scala。 参考学习的书籍:快学Scala

柠檬果过
今天
3
0
通俗易懂解释网络工程中的技术,如STP,HSRP等

导读 在面试时,比如被问到HSRP的主备切换时间时多久,STP几个状态的停留时间,自己知道有这些东西,但在工作中不会经常用到,就老是记不住,觉得可能还是自己基础不够牢固,知识掌握不够全面...

问题终结者
昨天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部