IN苛白、EXISTS 使用方式以及區(qū)別
數(shù)據(jù)背景
協(xié)議表數(shù)據(jù)總數(shù)為277811倾芝，協(xié)議簽署表總數(shù)為541621 一看數(shù)據(jù) 協(xié)議簽署表是協(xié)議表的兩倍 就誤以為使用EXISTS 性能會好一點但結(jié)果如何呢？下面的生產(chǎn)環(huán)境在跑的結(jié)果查詢耗時20s, 不對呀 订框！為啥才幾十萬的數(shù)據(jù)為啥能查詢時間這么長析苫！難道是因為join 的表太多了導致時間太久？
進一步查看改sql 的執(zhí)行計劃 居然執(zhí)行了全表掃描穿扳，明明ps.signer_ent_id = '979425854629085184' 使用了索引字段查詢按道理會走索引衩侥，但真實卻沒有。所以查詢時間才這么久矛物！
原始sql 使用EXISTS
-- 原始sql 使用EXISTS
EXPLAIN SELECT
ep.id,
ep.expired_date AS expireDate,
ep.create_id AS createId,
ep.create_time AS createTime,
ep.finish_time AS finishTime,
ep.dead_time AS deadTime,
ep.page_no AS pageNo,
ep.protocol_code AS protocolCode,
ep.buss_type AS bussType,
ep.protocol_type AS protocolType,
ep.contract_name AS contractName,
ep.contract_no AS contractNo,
ep.biz_id AS bizId,
ep.biz_code AS bizCode,
ep.sign_state AS signState,
ep.remark AS remark,
ep.is_sign AS isSign,
ep.system_channel AS systemChannel,
ep.fail_reason AS failReason,
ep.download_state AS downloadState,
ep.can_download AS canDownload,
ep.file_temp_name AS fileTempName,
ep.file_related_id AS fileRelatedId,
ep.file_save_type AS fileSaveType,
IF (ep.file_save_type = 2, 1, 0) AS isBackup,
ps1.signer_id AS signerAId,
ps1.signer_ent_id AS signerAEntId,
ps1.signer_name AS signerAName,
ps1.sign_time AS signATime,
ps1.signer_state AS signerAState,
ps1.remark AS aRemark,
ps2.signer_id AS signerBId,
ps2.signer_ent_id AS signerBEntId,
ps2.signer_name AS signerBName,
ps2.sign_time AS signBTime,
ps2.signer_state AS signerBState,
ps2.remark AS bRemark,
ps3.signer_id AS signerCId,
ps3.signer_ent_id AS signerCEntId,
ps3.signer_name AS signerCName,
ps3.sign_time AS signCTime,
ps3.signer_state AS signerCState,
ps3.remark AS cRemark
FROM
pub_enterprise_protocol ep
LEFT JOIN pub_enterprise_protocol_signer ps1 ON ps1.contract_no = ep.contract_no
AND ps1.signer_type = 'A'
LEFT JOIN pub_enterprise_protocol_signer ps2 ON ps2.contract_no = ep.contract_no
AND ps2.signer_type = 'B'
LEFT JOIN pub_enterprise_protocol_signer ps3 ON ps3.contract_no = ep.contract_no
AND ps3.signer_type = 'C'
WHERE
1 = 1
AND EXISTS (
SELECT
1
FROM
pub_enterprise_protocol_signer ps
WHERE
ps.contract_no = ep.contract_no
AND ps.signer_ent_id = '979425854629085184'
)
AND ep.protocol_type = '1'
LIMIT 0,
100;
優(yōu)化分析
當子查詢結(jié)果集很大茫死，而外部表較小的時候，查詢效率會優(yōu)于IN泽谨。
當子查詢結(jié)果集較小璧榄，而外部表很大的時候，IN的查詢效率會優(yōu)于Exists吧雹。
改成in 看一下結(jié)果
優(yōu)化后sql 使用in
-- 優(yōu)化后sql 使用in
EXPLAIN SELECT
ep.id,
ep.expired_date AS expireDate,
ep.create_id AS createId,
ep.create_time AS createTime,
ep.finish_time AS finishTime,
ep.dead_time AS deadTime,
ep.page_no AS pageNo,
ep.protocol_code AS protocolCode,
ep.buss_type AS bussType,
ep.protocol_type AS protocolType,
ep.contract_name AS contractName,
ep.contract_no AS contractNo,
ep.biz_id AS bizId,
ep.biz_code AS bizCode,
ep.sign_state AS signState,
ep.remark AS remark,
ep.is_sign AS isSign,
ep.system_channel AS systemChannel,
ep.fail_reason AS failReason,
ep.download_state AS downloadState,
ep.can_download AS canDownload,
ep.file_temp_name AS fileTempName,
ep.file_related_id AS fileRelatedId,
ep.file_save_type AS fileSaveType,
IF (ep.file_save_type = 2, 1, 0) AS isBackup,
ps1.signer_id AS signerAId,
ps1.signer_ent_id AS signerAEntId,
ps1.signer_name AS signerAName,
ps1.sign_time AS signATime,
ps1.signer_state AS signerAState,
ps1.remark AS aRemark,
ps2.signer_id AS signerBId,
ps2.signer_ent_id AS signerBEntId,
ps2.signer_name AS signerBName,
ps2.sign_time AS signBTime,
ps2.signer_state AS signerBState,
ps2.remark AS bRemark,
ps3.signer_id AS signerCId,
ps3.signer_ent_id AS signerCEntId,
ps3.signer_name AS signerCName,
ps3.sign_time AS signCTime,
ps3.signer_state AS signerCState,
ps3.remark AS cRemark
FROM
pub_enterprise_protocol ep
LEFT JOIN pub_enterprise_protocol_signer ps1 ON ps1.contract_no = ep.contract_no
AND ps1.signer_type = 'A'
LEFT JOIN pub_enterprise_protocol_signer ps2 ON ps2.contract_no = ep.contract_no
AND ps2.signer_type = 'B'
LEFT JOIN pub_enterprise_protocol_signer ps3 ON ps3.contract_no = ep.contract_no
AND ps3.signer_type = 'C'
WHERE
ep.contract_no IN (
SELECT
ps.contract_no
FROM
pub_enterprise_protocol_signer ps
WHERE
ps.signer_ent_id = '979425854629085184'
)
AND ep.protocol_type = '1'
LIMIT 0,
100;
結(jié)果證明 改成in后查詢時間只需0.019s 相比優(yōu)化前20.57s 提升1085倍骨杂，并且查看了優(yōu)化后的執(zhí)行計劃 條件命中索引 掃描的行數(shù)也只有32 行 相比原來277811 行全表掃描，可謂提升巨大
問題分析 EXISTS變慢了雄卷？
由EXISTS執(zhí)行計劃看到 子查詢并沒有走索引走的是where 接下來做個實驗 去掉查詢條件
發(fā)現(xiàn)還是會全表掃描 但是子查詢走了索引 索引字段是合同編號 查詢時間也到了毫秒級別 這才是一個正常的查詢時間（雖然和in 還有一倍的差距 但是也提升巨大）
子查詢中 兩個條件 （A）ps.contract_no = ep.contract_no（B）AND ps.signer_ent_id = '979425854629085184' 都是索引列為啥使用B 條件 反而沒走索引了搓蚪？
有執(zhí)行計劃中的子查詢可知：
DEPENDENT SUBQUERY：子查詢中的第一個SELECT，取決于外面的查詢 丁鹉，DEPENDENT SUBQUERY , 那么就會先執(zhí)行外部的查詢 , 然后再循環(huán)執(zhí)行內(nèi)部的查詢
MATERIALIZED: 子查詢視圖
EXISTS 的子查詢出現(xiàn) DEPENDENT SUBQUERY 先查詢了外部表277811 行 在根據(jù)查詢子查詢條件 當只有A時走了contract_no索引字段 當同時出現(xiàn)B時 雖然執(zhí)行計劃上顯示可能用到的索引字段是contract_no 但最終走了where 條件妒潭，這就說明了EXISTS 慢的原因
