转自:http://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 的子查询优化的很糟糕。
参考资料