文档章节

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

w
 wb123456
发布于 2017/05/31 13:46
字数 538
阅读 6
收藏 0

近日写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。

好文要顶 关注我 收藏该文  

© 著作权归作者所有

w
粉丝 0
博文 77
码字总数 62434
作品 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

没有更多内容

加载失败,请刷新页面

加载更多

HeyUI组件库按需加载功能上线,盘点HeyUI组件库有哪些独特功能?

HeyUI组件库 如果你还不了解heyui组件库,欢迎来我们的官网或者github参观。 官网 github 当然,如果能给我们一颗✨✨✨,那是最赞的了! 按需加载 当heyui组件库的组件越来越多的时候,按需...

vvpvvp
13分钟前
2
0
Dockerfile文件详解

Dockerfile文件详解 什么是dockerfile? Dockerfile是一个包含用于组合映像的命令的文本文档。可以使用在命令行中调用任何命令。 Docker通过读取Dockerfile中的指令自动生成映像。 docker bui...

Jeam_
26分钟前
0
0
阿里云PolarDB发布重大更新 支持Oracle等数据库一键迁移上云

5月21日,阿里云PolarDB发布重大更新,提供传统数据库一键迁移上云能力,可以帮助企业将线下的MySQL、PostgreSQL和Oracle等数据库轻松上云,最快数小时内迁移完成。据估算,云上成本不到传统...

zhaowei121
34分钟前
0
0
在数据数据探索过程中的一些常用操作

###pandas在做数据探索时,分组统计均值和中位数参考资料:http://www.cnblogs.com/nxld/p/6058591.htmlhttp://python.jobbole.com/85742/按字典重新赋值,可以直接使用pandas中的repla...

KYO4321
37分钟前
0
0
好程序员分享干货 弹性分布式数据集RDD

一、RDD定义 RDD(Resilient Distributed Dataset)叫做分布式数据集,是Spark中最基本的数据抽象,它代表一个不可变(数据和元数据)、可分区、里面的元素可并行计算的集合。其特点在于自动容...

好程序员IT
38分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部