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

2024-04-28 07:19•Java教程•阅读 295

以下文章来源：https://blog.csdn.net/defonds/article/details/46745143

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 '0',

`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 '1',

`CUP_CD` varchar(6) DEFAULT NULL,

`CUP_NM` varchar(80) DEFAULT NULL,

`UPI_TYPE` tinyint(4) NOT NULL DEFAULT '1',

`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 '营业执照',

`tax_register_cert` text COMMENT '税务登记证',

`ins_cert` text COMMENT '组织机构代码证',

`legal_id_card` text COMMENT '法人身份证',

`open_license` text COMMENT '开户许可证',

`auth_letter` text COMMENT '授权书',

`portal_photo` text COMMENT '门头照片',

`cashier_photo` text COMMENT '收银台照片',

`scene_photo` text COMMENT '经营场景照片',

`mer_agreement` text COMMENT '商户协议',

`other_qualification` text COMMENT '其他特殊资质',

`EXPECT_OPEN_TIME` datetime DEFAULT NULL,

`IN_OUT_FLAG` varchar(32) DEFAULT NULL,

`DCC_MODE` int(2) DEFAULT '0',

`SPECIAL_FLAG` tinyint(4) DEFAULT NULL,

`TRADING_CURRENCY` varchar(3) DEFAULT NULL,

`STATUS` int(11) DEFAULT '0',

`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 '记录创建时间',

`modify_time` datetime NOT NULL COMMENT '最好修改时间',

`TERM_CNT` int(11) DEFAULT NULL,

`DATA_SRC` tinyint(4) NOT NULL DEFAULT '1',

`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 '入账人身份证',

`in_account_bank_card` text COMMENT '入账银行卡信息',

`ins_credit_card` text COMMENT '机构信用代码证',

`ins_store_photo` text COMMENT '仓库照片',

`lease_agreement` text COMMENT '租赁协议',

`sct` decimal(22,3) DEFAULT NULL COMMENT '扫码支付（支付宝、微信整合）',

`card_type` char(1) DEFAULT '1' COMMENT '法人证件类型(1:身份证,2:护照)',

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 '流程id',

`proc_name` varchar(32) NOT NULL COMMENT '流程名称，如新增商户全聚德审批流程',

`proc_type` tinyint(4) NOT NULL COMMENT '流程类型：1－新增商户，2-变更商户，3-新增终端',

`associated_id` int(11) NOT NULL COMMENT '流程关联的商户id或其他',

`node_id` tinyint(4) NOT NULL COMMENT '流程进行到哪个节点',

`associated_name` varchar(64) DEFAULT NULL COMMENT '流程关联的商户名称',

`proc_status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '流程状态：1-启动流程，2-进行中，3-已完成',

`starter_id` int(11) NOT NULL COMMENT '流程发起者用户id',

`starter_name` varchar(32) NOT NULL COMMENT '流程发起者用户名',

`node_name` varchar(64) NOT NULL COMMENT '节点名称',

`next_id` tinyint(4) NOT NULL COMMENT '下一节点id',

`next_name` varchar(64) NOT NULL COMMENT '下一节点名称',

`create_time` datetime NOT NULL COMMENT '记录创建时间',

`ass_version` datetime NOT NULL COMMENT '关联版本号',

`node_remark` varchar(255) DEFAULT NULL COMMENT '备注',

`modify_time` datetime DEFAULT NULL COMMENT '上一节点完成时间',

`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='流程';

关于这两张表的一个慢查询日志如下：

# 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 = '外查询结果.stage_id';

这就是个坑。我相信，每个写出上面这种 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 那条 sql 的子查询优化的很糟糕。

参考资料

https://dev.mysql.com/doc/refman/5.5/en/correlated-subqueries.html

上一篇 »php where 各种子句
下一篇 »python list tuple 如何传参正确使用MySQL where in ？

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

相关推荐

Error querying database. Cause: java.sql.SQLSyntaxErrorException: Unknown column 'xxx' in 'where clause'

MYSQL的安全模式：sql_safe_updates介绍

MySQL中的SQL的常见优化策略

MySQL入门到精通，十：SQL优化第一篇

30个MySQL千万级大数据SQL查询优化技巧详解

提高mysql千万级大数据SQL查询优化30条经验，Mysql索引优化注意

mysql补充，3优化sql语句查询常用的30种方法

Apache Phoenix的子查询

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