文档章节

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
MySQL8.0新特性——锁读取

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

一个笨小孩
07/19
0
0
Rails + MySQL 的三个常见的错误

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

红薯
2011/02/15
388
0
SQL优化之一则MySQL中的DELETE、UPDATE 子查询的锁机制失效案例

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

全部原谅
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

没有更多内容

加载失败,请刷新页面

加载更多

下一页

MySQL 乱七八糟的可重复读隔离级别实现

MySQL 乱七八糟的可重复读隔离级别实现 摘要: 原文可阅读 http://www.iocoder.cn/Fight/MySQL-messy-implementation-of-repeatable-read-isolation-levels 「shimohq」欢迎转载,保留摘要,谢...

DemonsI
55分钟前
2
0
Spring源码阅读——2

在阅读源码之前,先了解下Spring的整体架构: 1、Spring的整体架构 1. Ioc(控制反转) Spring核心模块实现了Ioc的功能,它将类与类之间的依赖从代码中脱离出来,用配置的方式进行依赖关系描...

叶枫啦啦
今天
1
0
jQuery.post() 函数格式详解

jquery的Post方法$.post() $.post是jquery自带的一个方法,使用前需要引入jquery.js 语法:$.post(url,data,callback,type); url(必须):发送请求的地址,String类型 data(可选):发送给后台的...

森火
今天
0
0
referer是什么意思?

看看下面这个回答(打不开网页可以把网址复制到搜索栏): https://zhidao.baidu.com/question/577842068.html

杉下
今天
1
0
使用U盘安装CentOS-解决U盘找不到源

1. 使用UltraISO制作CentOS安装盘 如果需要安装带界面的系统,为保证安装顺利,可选择Everything版本的ISO制作安装盘。 2. 在BIOS中选择使用U盘安装 系统启动后,进入安装选择界面,其中有三...

Houor
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部