警惕MySql更新sql的WHERE從句中的IN()子查詢時出現的陷阱_MySQL

來源：懂視網責編：小采時間：2020-11-09 19:52:14

警惕MySql更新sql的WHERE從句中的IN()子查詢時出現的陷阱_MySQL

警惕MySql更新sql的WHERE從句中的IN()子查詢時出現的陷阱_MySQL: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`

推薦度：

點擊下載本文 文檔為doc格式

導讀警惕MySql更新sql的WHERE從句中的IN()子查詢時出現的陷阱_MySQL: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`

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 另一個 select，差別咋就那么大呢？看來優化器并不總是那么靠譜的，它在這里就對 update 那條 sql 的子查詢優化的很糟糕。

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com

警惕MySql更新sql的WHERE從句中的IN()子查詢時出現的陷阱_MySQL

推薦度：

點擊下載本文 文檔為doc格式

標簽： in mysql 陷阱

熱門焦點

警惕MySql更新sql的WHERE從句中的IN()子查詢時出現的陷阱_MySQL

警惕MySql更新sql的WHERE從句中的IN()子查詢時出現的陷阱_MySQL

警惕MySql更新sql的WHERE從句中的IN()子查詢時出現的陷阱_MySQL

最新推薦

猜你喜歡

熱門推薦