警惕MySql更新sql的WHERE从句中的IN()子查询时出现的陷阱_MySQL

作者：小妮子831020 | 来源：互联网 | 2018-04-18 09:44

mer_stage表有216423条记录，DDL：

mer_stage 表有 216423 条记录，DDL：

CREATE TABLE `mer_stage` (
  `STAGE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `MER_ID` int(11) NOT NULL,
  `MER_CODE` varchar(16) DEFAULT NULL,
  `MER_NAME` varchar(80) NOT NULL,
  `INS_CODE` varchar(16) NOT NULL,
  `INS_NAME` varchar(64) DEFAULT NULL,
  `AGENT_CODE` varchar(16) DEFAULT NULL,
  `AGENT_NAME` varchar(64) DEFAULT NULL,
  `BIG_CATEGORY_NAME` varchar(32) DEFAULT NULL,
  `SUB_CATEGORY_CODE` char(4) DEFAULT NULL,
  `SUB_CATEGORY_NAME` varchar(64) DEFAULT NULL,
  `LICENSE_CODE` varchar(64) DEFAULT NULL,
  `LICENSE_NAME` varchar(64) DEFAULT NULL,
  `SHORT_NAME` varchar(25) DEFAULT NULL,
  `MER_STATUS` tinyint(4) DEFAULT NULL,
  `PROVINCE_NAME` varchar(16) DEFAULT NULL,
  `CITY_CODE` char(4) DEFAULT NULL,
  `CITY_NAME` varchar(12) DEFAULT NULL,
  `REGISTER_ADDRESS` varchar(128) DEFAULT NULL,
  `BIZ_ADDRESS` varchar(128) DEFAULT NULL,
  `TAX_REGISTRATION` varchar(32) DEFAULT NULL,
  `INSTITUTION` varchar(16) DEFAULT NULL,
  `LEGAL_NAME` varchar(40) DEFAULT NULL,
  `LEGAL_CARD` varchar(32) DEFAULT NULL,
  `LEGAL_PHONE` varchar(16) DEFAULT NULL,
  `BIZ_SCOPE` varchar(128) DEFAULT NULL,
  `BIZ_CONTENT` varchar(64) DEFAULT NULL,
  `BIZ_TIME` varchar(32) DEFAULT NULL,
  `LICENSE_EXPIRED` varchar(16) DEFAULT NULL,
  `AVG_SINGLE_TRADE` int(11) DEFAULT NULL,
  `AVG_MONTH_TRADE` int(11) DEFAULT NULL,
  `BIZ_PLACE_OWNER` varchar(64) DEFAULT NULL,
  `REGISTERED_CAPITAL` decimal(11,0) DEFAULT NULL,
  `PAID_IN_CAPITAL` int(11) DEFAULT NULL,
  `BIZ_PERIOD` tinyint(4) DEFAULT NULL,
  `BIZ_AREA` int(11) DEFAULT NULL,
  `SETTLE_PERIOD` tinyint(4) DEFAULT NULL,
  `DELAY_TIME` varchar(50) DEFAULT NULL,
  `DELAY_TYPE` tinyint(4) DEFAULT &＃39;0&＃39;,
  `BANK_CODE` varchar(40) DEFAULT NULL,
  `BRANCH_CODE` varchar(25) DEFAULT NULL,
  `BRANCH_CODE_ONE` varchar(25) DEFAULT NULL,
  `BRANCH_CODE_TWO` varchar(25) DEFAULT NULL,
  `BRANCH_NAME` varchar(128) DEFAULT NULL,
  `ACCOUNT_CODE` varchar(32) DEFAULT NULL,
  `ACCOUNT_NAME` varchar(80) DEFAULT NULL,
  `BRANCH_PROVINCE` varchar(32) DEFAULT NULL,
  `BRANCH_CITY_CODE` varchar(10) DEFAULT NULL,
  `BRANCH_CITY_NAME` varchar(50) DEFAULT NULL,
  `SETTLE_CURRENCY` varchar(16) DEFAULT NULL,
  `SETTLE_PARAM` char(1) DEFAULT NULL,
  `CUP_TYPE` tinyint(4) NOT NULL DEFAULT &＃39;1&＃39;,
  `CUP_CD` varchar(6) DEFAULT NULL,
  `CUP_NM` varchar(80) DEFAULT NULL,
  `UPI_TYPE` tinyint(4) NOT NULL DEFAULT &＃39;1&＃39;,
  `UPI_CD` varchar(6) DEFAULT NULL,
  `UPI_NM` varchar(80) DEFAULT NULL,
  `VISA_EDC_FEE` double DEFAULT NULL,
  `VISA_DCC_FEE` double DEFAULT NULL,
  `MASTERCARD_EDC_FEE` double DEFAULT NULL,
  `MASTERCARD_DCC_FEE` double DEFAULT NULL,
  `JCB_EDC_FEE` double DEFAULT NULL,
  `AE_EDC_FEE` double DEFAULT NULL,
  `DC_EDC_FEE` double DEFAULT NULL,
  `CONTACT_NAME` varchar(40) DEFAULT NULL,
  `CONTACT_FIXED` varchar(32) DEFAULT NULL,
  `CONTACT_MOBILE` varchar(32) DEFAULT NULL,
  `CONTACT_FAX` varchar(32) DEFAULT NULL,
  `CONTACT_EMAIL` varchar(80) DEFAULT NULL,
  `CONTACT_ADDRESS` varchar(128) DEFAULT NULL,
  `CONTACT_ZIP` varchar(8) DEFAULT NULL,
  `biz_license` text COMMENT &＃39;营业执照&＃39;,
  `tax_register_cert` text COMMENT &＃39;税务登记证&＃39;,
  `ins_cert` text COMMENT &＃39;组织机构代码证&＃39;,
  `legal_id_card` text COMMENT &＃39;法人身份证&＃39;,
  `open_license` text COMMENT &＃39;开户许可证&＃39;,
  `auth_letter` text COMMENT &＃39;授权书&＃39;,
  `portal_photo` text COMMENT &＃39;门头照片&＃39;,
  `cashier_photo` text COMMENT &＃39;收银台照片&＃39;,
  `scene_photo` text COMMENT &＃39;经营场景照片&＃39;,
  `mer_agreement` text COMMENT &＃39;商户协议&＃39;,
  `other_qualification` text COMMENT &＃39;其他特殊资质&＃39;,
  `EXPECT_OPEN_TIME` datetime DEFAULT NULL,
  `IN_OUT_FLAG` varchar(32) DEFAULT NULL,
  `DCC_MODE` int(2) DEFAULT &＃39;0&＃39;,
  `SPECIAL_FLAG` tinyint(4) DEFAULT NULL,
  `TRADING_CURRENCY` varchar(3) DEFAULT NULL,
  `STATUS` int(11) DEFAULT &＃39;0&＃39;,
  `EDITABLE` tinyint(4) DEFAULT NULL,
  `MER_SINGLE_LIMIT` decimal(30,5) DEFAULT NULL,
  `MER_DAY_LIMIT` decimal(30,5) DEFAULT NULL,
  `MER_NATION` varchar(3) DEFAULT NULL,
  `ROUTE_SCHEME` varchar(13) DEFAULT NULL,
  `CREATOR_ID` int(11) DEFAULT NULL,
  `CREATOR_NAME` varchar(32) DEFAULT NULL,
  `create_time` datetime NOT NULL COMMENT &＃39;记录创建时间&＃39;,
  `modify_time` datetime NOT NULL COMMENT &＃39;最好修改时间&＃39;,
  `TERM_CNT` int(11) DEFAULT NULL,
  `DATA_SRC` tinyint(4) NOT NULL DEFAULT &＃39;1&＃39;,
  `CUP_CARD_PLAN` bit(1) DEFAULT NULL,
  `UPI_CARD_PLAN` bit(1) DEFAULT NULL,
  `RISK_DESC` varchar(50) DEFAULT NULL,
  `IS_FLAG` char(1) DEFAULT NULL,
  `ALP` decimal(22,3) DEFAULT NULL,
  `WXP` decimal(22,3) DEFAULT NULL,
  `dfs_edc_fee` decimal(22,3) DEFAULT NULL,
  `prp_edc_fee` decimal(22,3) DEFAULT NULL,
  `in_account_id_card` text COMMENT &＃39;入账人身份证&＃39;,
  `in_account_bank_card` text COMMENT &＃39;入账银行卡信息&＃39;,
  `ins_credit_card` text COMMENT &＃39;机构信用代码证&＃39;,
  `ins_store_photo` text COMMENT &＃39;仓库照片&＃39;,
  `lease_agreement` text COMMENT &＃39;租赁协议&＃39;,
  `sct` decimal(22,3) DEFAULT NULL COMMENT &＃39;扫码支付（支付宝、微信整合）&＃39;,
  `card_type` char(1) DEFAULT &＃39;1&＃39; COMMENT &＃39;法人证件类型(1:身份证,2:护照)&＃39;,
  PRIMARY KEY (`STAGE_ID`),
  KEY `mer_stage_s_e_ms` (`STATUS`,`EDITABLE`,`MER_STATUS`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=216826 DEFAULT CHARSET=utf8;

proc 表有 6450 条记录，DDL：

CREATE TABLE `proc` (
  `proc_id` int(11) NOT NULL AUTO_INCREMENT COMMENT &＃39;流程id&＃39;,
  `proc_name` varchar(32) NOT NULL COMMENT &＃39;流程名称，如 新增商户全聚德审批流程&＃39;,
  `proc_type` tinyint(4) NOT NULL COMMENT &＃39;流程类型：1－新增商户，2-变更商户，3-新增终端&＃39;,
  `associated_id` int(11) NOT NULL COMMENT &＃39;流程关联的商户id或其他&＃39;,
  `node_id` tinyint(4) NOT NULL COMMENT &＃39;流程进行到哪个节点&＃39;,
  `associated_name` varchar(64) DEFAULT NULL COMMENT &＃39;流程关联的商户名称&＃39;,
  `proc_status` tinyint(4) NOT NULL DEFAULT &＃39;1&＃39; COMMENT &＃39;流程状态：1-启动流程，2-进行中，3-已完成&＃39;,
  `starter_id` int(11) NOT NULL COMMENT &＃39;流程发起者用户id&＃39;,
  `starter_name` varchar(32) NOT NULL COMMENT &＃39;流程发起者用户名&＃39;,
  `node_name` varchar(64) NOT NULL COMMENT &＃39;节点名称&＃39;,
  `next_id` tinyint(4) NOT NULL COMMENT &＃39;下一节点id&＃39;,
  `next_name` varchar(64) NOT NULL COMMENT &＃39;下一节点名称&＃39;,
  `create_time` datetime NOT NULL COMMENT &＃39;记录创建时间&＃39;,
  `ass_version` datetime NOT NULL COMMENT &＃39;关联版本号&＃39;,
  `node_remark` varchar(255) DEFAULT NULL COMMENT &＃39;备注&＃39;,
  `modify_time` datetime DEFAULT NULL COMMENT &＃39;上一节点完成时间&＃39;,
  `mer_id` int(11) NOT NULL,
  PRIMARY KEY (`proc_id`),
  KEY `proc_mer_id_index` (`mer_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6451 DEFAULT CHARSET=utf8 COMMENT=&＃39;流程&＃39;;

关于这两张表的一个慢查询日志如下：
# Time: 150703 15:13:33
# User@Host: test[test] @ localhost [127.0.0.1] Id: 1
# Query_time: 2.101248 Lock_time: 0.046034 Rows_sent: 0 Rows_examined: 865689
SET timestamp=1435907613;
update mer_stage set editable = 1 where stage_id in(
select associated_id from proc where proc_id in(6446 , 6447 , 6450));
日志中可以看出该 sql 的执行时间是 2.101 s。
我们来查看一下该 sql 的执行计划：

注意：select_type 里出现了 DEPENDENT SUBQUERY。
这意味着什么？——子查询取决于外面的查询，MySql 先执行外查询，内查询根据这个查询结果(如执行计划里所述，190102 rows)的每一条记录组成新的查询语句：

select associated_id from proc where proc_id in(6446 , 6447 , 6450) and associated_id = &＃39;外查询结果.stage_id&＃39;;

这就是个坑。我相信，每个写出上面这种 sql 的程序员都不会想到 MySql 会对其这样执行，这是大家不想看到的结果。
怎么办？
Uncorrelated subqueries treated as DEPENDENT by MySQL 提出了同样的问题但是却没有给出解决方案。
MySql 官方给出的解决方案是：
If you have a slow &＃39;correlated&＃39; subquery with IN, you can optimize it with a join to get around the bug described by Ryan and Stephen. After the optimization the execution time is no longer O(M×N).
于是我们的 update 语句改写为：

update mer_stage m join proc p on m.stage_id = p.associated_id set m.editable = 1
		where p.proc_id =6446 or p.proc_id =6447 or p.proc_id =6450;

它的执行计划是：

执行这个 update，用时 0.047s，意料之中。搞定。
有趣的是，我们来做一个尝试，把该 update 改为 select：

select * from mer_stage where stage_id in (select associated_id from proc where proc_id in (6446 , 6447 , 6450));

它的执行时间是 0.053 s，毫秒级。
该 sql 的执行计划是：
update改为select后的执行计划

同样的写法，唯一不同的是一个 update 另一个 select，差别咋就那么大呢？看来优化器并不总是那么靠谱的，它在这里就对 update 那条 sql 的子查询优化的很糟糕。

推荐阅读

go
拥抱Android Design Support Library新变化（导航视图、悬浮ActionBar）

转载请注明明桑AndroidAndroid5.0Loollipop作为Android最重要的版本之一，为我们带来了全新的界面风格和设计语言。看起来很受欢迎࿰ ... [详细]

蜡笔小新 2023-12-13 16:11:00
timestamp
Android 新闻App的本地服务器搭建教程

本文介绍了在开发Android新闻App时，搭建本地服务器的步骤。通过使用XAMPP软件，可以一键式搭建起开发环境，包括Apache、MySQL、PHP、PERL。在本地服务器上新建数据库和表，并设置相应的属性。最后，给出了创建new表的SQL语句。这个教程适合初学者参考。 ... [详细]

蜡笔小新 2023-12-14 17:15:19
case
如何在MySQL中将零值替换为先前的非零值？

本文介绍了如何在MySQL中将零值替换为先前的非零值的方法，包括使用内联查询和更新查询。同时还提供了选择正确值的方法。 ... [详细]

蜡笔小新 2023-12-14 16:59:24
case
mysql分组排序_在MySQL中实现分组排序功能

在数据分析工作中，我们通常会遇到这样的问题，一个业务部门由若干业务组构成，需要筛选出每个业务组里业绩前N名的业务员。这其实是一个分组排序的 ... [详细]

蜡笔小新 2023-12-14 14:41:26
case
如何限制php数据库链接数和连接超时时间？

本文介绍了如何使用php限制数据库插入的条数并显示每次插入数据库之间的数据数目，以及避免重复提交的方法。同时还介绍了如何限制某一个数据库用户的并发连接数，以及设置数据库的连接数和连接超时时间的方法。最后提供了一些关于浏览器在线用户数和数据库连接数量比例的参考值。 ... [详细]

蜡笔小新 2023-12-14 14:06:10
case
Oracle Database 10g许可授予信息及高级功能详解

本文介绍了Oracle Database 10g许可授予信息及其中的高级功能，包括数据库优化数据包、SQL访问指导、SQL优化指导、SQL优化集和重组对象。同时提供了详细说明，指导用户在Oracle Database 10g中如何使用这些功能。 ... [详细]

蜡笔小新 2023-12-14 13:12:10
case
Hibernate基础映射

在说Hibernate映射前，我们先来了解下对象关系映射ORM。ORM的实现思想就是将关系数据库中表的数据映射成对象，以对象的形式展现。这样开发人员就可以把对数据库的操作转化为对 ... [详细]

蜡笔小新 2023-12-14 10:57:47
case
如何查看mysql的安装路径

本文介绍了通过mysql命令查看mysql的安装路径的方法，提供了相应的sql语句，并希望对读者有参考价值。 ... [详细]

蜡笔小新 2023-12-13 13:23:09
数组
如何在php中将mysql查询结果赋值给变量

本文介绍了在php中将mysql查询结果赋值给变量的方法，包括从mysql表中查询count(学号)并赋值给一个变量，以及如何将sql中查询单条结果赋值给php页面的一个变量。同时还讨论了php调用mysql查询结果到变量的方法，并提供了示例代码。 ... [详细]

蜡笔小新 2023-12-12 18:22:57
foreach
MyBatis多表查询与动态SQL使用

本文介绍了MyBatis多表查询与动态SQL的使用方法，包括一对一查询和一对多查询。同时还介绍了动态SQL的使用，包括if标签、trim标签、where标签、set标签和foreach标签的用法。文章还提供了相关的配置信息和示例代码。 ... [详细]

蜡笔小新 2023-12-12 17:12:51
tree
Go语言实现堆排序的详细教程

本文主要介绍了Go语言实现堆排序的详细教程，包括大根堆的定义和完全二叉树的概念。通过图解和算法描述，详细介绍了堆排序的实现过程。堆排序是一种效率很高的排序算法，时间复杂度为O(nlgn)。阅读本文大约需要15分钟。 ... [详细]

蜡笔小新 2023-12-12 16:23:00
tree
MySQL显示SQL语句执行时间的实例详解

本文详细介绍了如何使用MySQL来显示SQL语句的执行时间，并通过MySQL Query Profiler获取CPU和内存使用量以及系统锁和表锁的时间。同时介绍了效能分析的三种方法：瓶颈分析、工作负载分析和基于比率的分析。 ... [详细]

蜡笔小新 2023-12-12 16:16:42
tree
Python SQLAlchemy库的使用方法详解

本文详细介绍了Python中使用SQLAlchemy库的方法。首先对SQLAlchemy进行了简介，包括其定义、适用的数据库类型等。然后讨论了SQLAlchemy提供的两种主要使用模式，即SQL表达式语言和ORM。针对不同的需求，给出了选择哪种模式的建议。最后，介绍了连接数据库的方法，包括创建SQLAlchemy引擎和执行SQL语句的接口。 ... [详细]

蜡笔小新 2023-12-12 15:23:06
tree
mysql升级（从5.6.15升级到5.7.15的步骤）

本文介绍了将mysql从5.6.15升级到5.7.15的详细步骤，包括关闭访问、备份旧库、备份权限、配置文件备份、关闭旧数据库、安装二进制、替换配置文件以及启动新数据库等操作。 ... [详细]

蜡笔小新 2023-12-12 14:45:04
go
智能消息服务数字短信使用FAQ

本文介绍了智能消息服务数字短信的开通流程和操作步骤，包括开通数字短信的路径、申请流程、控制台操作以及API接口对接模式。同时还介绍了数字短信模板的创建规则和要求，包括审核状态的说明和建议。 ... [详细]

蜡笔小新 2023-12-12 13:08:10

小妮子831020

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章