文档章节

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

w
 wb123456
发布于 2017/05/31 13:46
字数 538
阅读 4
收藏 0
点赞 0
评论 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
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
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
SQL语句常见优化十大案例

1、慢SQL消耗了70%~90%的数据库CPU资源; 2、SQL语句独立于程序设计逻辑,相对于对程序源代码的优化,对SQL语句的优化在时间成本和风险上的代价都很低; 3、SQL语句可以有不同的写法; 下面是...

孟飞阳
2016/06/16
49
0
Oracle 宣布 MySQL 5.6 正式版发布

在 MySQL 5.5 发布两年后,Oracle 宣布 MySQL 5.6 正式版发布,首个正式版版本号为 5.6.10。在 MySQL 5.5 中使用的是 InnoDB 作为默认的存储引擎,而 5.6 则对 InnoDB 引擎进行了改造,提供全...

oschina
2013/02/06
11.7K
40
MYSQL子查询与连接

37:子查询与连接 SET 列名 gbk;//改变客户端数据表的编码类型。 子查询 子查询(Subquery)是指出现在其他SQL语句内的SELECT子句 例如 SELECT FROM t1 WHERE col1=(SELECT col2 FROM t2); 其...

别叫小伙
2016/09/30
0
0
MySQL学习笔记一

MySQL目录结构 配置my.ini MySQL5.7的my.ini位于ProgramDataMySQLMySQL Server 5.7目录下(可能有的版本的my.ini就在安装目录下),该该目录下还有一个data目录存放我们的创建的数据库。 打开...

Aaron_DMC
2016/12/16
27
0
「mysql优化专题」90%程序员都会忽略的增删改优化(2)

前文一篇「mysql优化专题」这大概是一篇最好的mysql优化入门文章(1)让大家知道msql优化,究竟在优化什么,本篇为mysql优化专题的第二篇,主要先从增删改进行优化。大家可以收藏关注一波,若...

java进阶架构师
2017/11/22
0
0
MySQL · 性能优化 · MySQL常见SQL错误用法

前言 MySQL在2016年仍然保持强劲的数据库流行度增长趋势。越来越多的客户将自己的应用建立在MySQL数据库之上,甚至是从Oracle迁移到MySQL上来。但也存在部分客户在使用MySQL数据库的过程中遇...

阿里云RDS-数据库内核组
2017/03/04
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

CVE-2013-0077 堆溢出分析

找了很久才发现这个环境比较容易搭建分析... 环境: 系统---Win XP SP3 漏洞程序:QQPlayer 3.7.892.400 出错DLL:quartz.dll 6.5.2600.5512 调试工具:x32db+gflag.exe 过程: 首先gflag设置...

Explorer0
4分钟前
0
0
python上传文件

//注意 <form action="/login/" method="post" enctype="multipart/form-data"> f=request.FILES.get('fafa') ff=open(f.name,mode='wb') for i in f.chunks(): ff.write(i) ff.close()......

南桥北木
16分钟前
0
0
CISCO VPN Client Reason 442 WIN8/10错误解决方案

http://jdkleo.iteye.com/blog/2163493 引用 http://my.oschina.net/cloudcoder/blog/220391?p={{currentPage 1}} 在使用cisco VPN 客户端登录时,产生Reason 442:Failedto enable Virtual......

chenfj_fer
20分钟前
0
0
信号量有没有容量限制?

之前一直误以为信号量初始化的时候那个初始化的值是信号量的“容量”,昨天同事指出了我的错误,最初我是不相信的,经过以下代码实践,证明了我的错误: Java版: import java.util.concurr...

锟斤拷烫烫烫
23分钟前
0
0
【RocketMQ】Message存储笔记

概述 消息中间件存储分为三种,一是保存在内存中,速度快但会因为系统宕机等因素造成消息丢失;二是保存在内存中,同时定时将消息写入DB中,好处是持久化消息,如何读写DB是MQ的瓶颈;三是内...

SaintTinyBoy
35分钟前
0
0
Android应用Context详解及源码解析

Android应用Context详解及源码解析 本文定位:优质文章收集 本文转载 1 背景 今天突然想起之前在上家公司(做TV与BOX盒子)时有好几个人问过我关于Android的Context到底是啥的问题,所以就马...

lichuangnk
今天
0
0
PostgreSQL的昨天今天和明天

PostgreSQL 是一种非常复杂的对象-关系型数据库管理系统(ORDBMS), 也是目前功能最强大,特性最丰富和最复杂的自由软件数据库系统。有些特性甚至连商业数据库都不具备。 这个起源于伯克利(...

闻术苑
今天
1
0
Mysql对自增主键ID进行重新排序

1,删除原有主键: ALTER TABLE `table_name` DROP `id`; 2,添加新主键字段: ALTER TABLE `table_name` ADD `id` MEDIUMINT( 8 ) NOT NULL FIRST; 3,设置新主键: ALTER TABLE `table_nam......

niithub
今天
0
0
福利篇:免费csdn vip账号分享

分享一个发布免费csdn vip账号的网站:啰嗦vip www.lostvip.com , 各种软件开发类的视频教程:慕课网、动脑学院、黑马各大培训机构VIP视频教程,非常不错!

在水一方发盐人
今天
1
0
Nginx+Tomcat搭建高性能负载均衡集群

一、 工具   nginx-1.8.0   apache-tomcat-6.0.33 二、 目标   实现高性能负载均衡的Tomcat集群:    三、 步骤   1、首先下载Nginx,要下载稳定版:      2、然后解压两个Tom...

码代码的小司机
今天
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部