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

Home

Database

Mysql Tutorial

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 12:59 PM

trap

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 'correlated' 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 的子查询优化的很糟糕。

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

C++ 递归的陷阱和解决方案：常见错误规避指南May 02, 2024 am 10:54 AM

避免无界递归：设置递归基线，明确停止条件。优化递归效率：考虑使用循环或迭代代替深度递归调用。预防栈溢出：控制递归深度，利用优化技术或辅助数据结构。禁止修改传入参数：传递值副本或使用全局变量存储递归结果。实战示例：通过优化fibonacci()函数阐述最佳实践应用。

使用 STL 函数对象需要注意哪些陷阱？Apr 25, 2024 pm 02:42 PM

STL函数对象使用陷阱：不可修改函数对象的状态，否则可能导致后果或崩溃。函数对象应作为右值使用，左值使用会导致未定义行为。捕获局部变量时应确保捕获所有引用的变量，否则可能导致崩溃。

PHP CI/CD 的陷阱：常见问题及解决方法Mar 05, 2024 pm 10:10 PM

PHP持续集成和持续交付(CI/CD)的实施对于现代WEB开发至关重要，可以显著提高软件开发和部署的效率和质量。然而，在这一过程中也存在一些常见的陷阱，如果不及时解决，可能会阻碍团队实现CI/CD流程的全部好处。本文着重介绍这些陷阱并提供实用的解决方法，从而为phpCI/CD管道建立一个稳固的基础。1.脚本维护不善在CI/CD管道中，自动化脚本是执行任务和验证构建的基石。然而，如果没有适当的维护，这些脚本可能会变得陈旧或失效。解决方法：将脚本保存在版本控制系统中，例如git。定期回顾和更新脚本，

Golang协程的常见错误与陷阱Apr 15, 2024 pm 06:09 PM

Go协程中的常见错误包括：协程泄漏：未正确释放资源导致内存消耗过多；解决方法：使用defer语句。死锁：多个协程循环等待；解决方法：避免循环等待模式，使用channel或sync.Mutex协调访问。数据竞争：共享数据同时被多个协程访问；解决方法：使用sync.Mutex或sync.WaitGroup保护共享数据。计时器取消：协程取消后计时器未正确取消；解决方法：使用context.Context传播取消信号。

Java框架中的陷阱：识别并避免它们的指南Jun 04, 2024 pm 12:23 PM

Java框架的使用陷阱可阻碍应用程序的性能、可维护性和安全性。这些陷阱包括：过度使用框架：避免不必要地依赖框架，使用简单的工厂模式或依赖项注入代替。忽略框架约束：遵守框架文档中的约束和最佳实践，避免违规导致错误。缺乏自定义：使用扩展点和回调机制自定义框架的特定部分，满足特定需求。性能问题：了解框架的性能影响，并使用剖析工具识别和解决瓶颈。

C++语法中的陷阱与解决方案Jun 03, 2024 pm 04:22 PM

C++语法中的陷阱与解决方案C++是一门强大的编程语言，但它的语法也让程序员很容易陷入陷阱。本文将讨论C++语法中的一些常见陷阱，并提供避免或解决它们的解决方案。陷阱1：误用引用问题：将一个指针错误地用作引用。代码示例：int&ref=*ptr;//错误：ptr是指针，不能解引用为引用解决方案：使用指针指针或将指针解引用为非引用类型。int*ptr2=&*ptr;//使用指针指针intval=*ptr;//解引用为非引用类型陷阱2：条件语句中的默认行为问

PHP ZipArchive 扩展的常见陷阱：避免常见错误Mar 10, 2024 pm 09:20 PM

陷阱1：未关闭文件句柄在使用ZipArcHive对象操作压缩文件时，必须始终确保在完成操作后关闭文件句柄。未关闭文件句柄会导致文件损坏或资源泄漏。$zip=newZipArchive();$zip->open("file.zip");//...进行操作...$zip->close();//关闭文件句柄陷阱2：处理不合格的文件如果要打开的文件已损坏或不符合ZIP规范，ZipArchive会抛出异常。在处理文件之前，应检查文件的有效性。if($zip->open("file.zip")!==