警惕MySql更新sql的WHERE从句中的IN（）⼦查询时出现的
性能陷阱
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,
ps素描效果制作`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_licen` text COMMENT '营业执照',
`tax_register_cert` text COMMENT '税务登记证',
孩子咳嗽吃什么药
`ins_cert` text COMMENT '组织机构代码证',
`legal_id_card` text COMMENT '法⼈⾝份证',
`open_licen` 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 '仓库照⽚',
`lea_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
# Ur@Host: test[test] @ localhost [127.0.0.1] Id: 1
# Query_time: 2.101248 Lock_time: 0.046034 Rows_nt: 0 Rows_examined: 865689
SET timestamp=1435907613;
update mer_stage t editable = 1 where stage_id in(
lect associated_id from proc where proc_id in(6446 , 6447 , 6450));
⽇志中可以看出该 sql 的执⾏时间是 2.101 s。
我们来查看⼀下该 sql 的执⾏计划：
注意：lect_type ⾥出现了 DEPENDENT SUBQUERY。
这意味着什么？——⼦查询取决于外⾯的查询，MySql 先执⾏外查询，内查询根据这个查询结果(如执
⾏计划⾥所述，190102 rows)的每⼀条记录组成新的查询语句：
lect associated_id from proc where proc_id in(6446 , 6447 , 6450) and associated_id = '外查询结果.stage_id';
这就是个坑。我相信，每个写出上⾯这种 sql 的程序员都不会想到 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 t m.editable = 1
where p.proc_id =6446 or p.proc_id =6447 or p.proc_id =6450;
它的执⾏计划是：十一月四日
执⾏这个 update，⽤时 0.047s，意料之中。搞定。
有趣的是，我们来做⼀个尝试，把该 update 改为 lect：
lect * from mer_stage where stage_id in (lect associated_id from proc where proc_id in (6446 , 6447 , 6450));
它的执⾏时间是 0.053 s，毫秒级。
该 sql 的执⾏计划是：
同样的写法，唯⼀不同的是⼀个 update 另⼀个 lect，差别咋就那么⼤呢？看来优化器并不总是那么
靠谱的，它在这⾥就对 update 那条sql 的⼦查询优化的很糟糕。
参考资料
