解決MySQL中IN子查詢會導(dǎo)致無法使用索引問題

2024-07-24 12:51:34

字體：大中小

供稿：網(wǎng)友

今天看到一篇關(guān)于MySQL的IN子查詢優(yōu)化的案例，

一開始感覺有點(diǎn)半信半疑（如果是換做在SQL Server中，這種情況是絕對不可能的，后面會做一個(gè)簡單的測試。）

隨后動(dòng)手按照他說的做了一個(gè)表來測試驗(yàn)證，發(fā)現(xiàn)MySQL的IN子查詢做的不好，確實(shí)會導(dǎo)致無法使用索引的情況（IN子查詢無法使用所以，場景是MySQL，截止的版本是5.7.18）

MySQL的測試環(huán)境

測試表如下

create table test_table2( id int auto_increment primary key, pay_id int, pay_time datetime, other_col varchar(100))

建一個(gè)存儲過程插入測試數(shù)據(jù)，測試數(shù)據(jù)的特點(diǎn)是pay_id可重復(fù)，這里在存儲過程處理成，循環(huán)插入300W條數(shù)據(jù)的過程中，每隔100條數(shù)據(jù)插入一條重復(fù)的pay_id，時(shí)間字段在一定范圍內(nèi)隨機(jī)

CREATE DEFINER=`root`@`%` PROCEDURE `test_insert`(IN `loopcount` INT) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT ''BEGIN declare cnt int; set cnt = 0; while cnt< loopcount do insert into test_table2 (pay_id,pay_time,other_col) values (cnt,date_add(now(), interval floor(300*rand()) day),uuid()); if (cnt mod 100 = 0) then insert into test_table2 (pay_id,pay_time,other_col) values (cnt,date_add(now(), interval floor(300*rand()) day),uuid()); end if; set cnt = cnt + 1; end while;END

　　執(zhí)行 call test_insert(3000000); 插入303000行數(shù)據(jù)

兩種子查詢的寫法

查詢大概的意思是查詢某個(gè)時(shí)間段之內(nèi)的業(yè)務(wù)Id大于1的數(shù)據(jù)，于是就出現(xiàn)兩種寫法。

第一種寫法如下：IN子查詢中是某段時(shí)間內(nèi)業(yè)務(wù)統(tǒng)計(jì)大于1的業(yè)務(wù)Id，外層按照IN子查詢的結(jié)果進(jìn)行查詢，業(yè)務(wù)Id的列pay_id上有索引，邏輯也比較簡單，這種寫法，在數(shù)據(jù)量大的時(shí)候確實(shí)效率比較低，用不到索引

select * from test_table2 force index(idx_pay_id)where pay_id in (　　select pay_id from test_table2 　　where pay_time>="2016-06-01 00:00:00" 　　　　AND pay_time<="2017-07-03 12:59:59" 　　group by pay_id 　　having count(pay_id) > 1);

執(zhí)行結(jié)果：2.23秒