MySQL的測試環境
#測試表如下
create table test_table2 ( id int auto_increment primary key, pay_id int, pay_time datetime, other_col varchar(100) )
#建構一個存儲過程插入測試數據,測試數據的特點是pay_id可重複,這裡在存儲過程處理成,循環插入300W條數據的過程中,每隔100條數據插入一條重複的pay_id,時間字段在一定範圍內隨機
CREATE DEFINER=`root`@`%` PROCEDURE `test_insert`(IN `loopcount` INT) LANGUAGE SQLNOT DETERMINISTICCONTAINS SQL SQL SECURITY DEFINER COMMENT ''BEGINdeclare cnt int;set cnt = 0;while cnt< loopcount doinsert 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) theninsert 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
執行call test_insert(3000000); 插入303000行資料
兩種子查詢的寫法
#查詢大概大概的意思是查詢某個時間段之內的業務Id大於1的數據,於是就出現兩種寫法。
第一種寫法如下:IN子查詢中是某段時間業務統計行數大於1的業務Id,外層依照IN子查詢的結果進行查詢,業務Id的列pay_id上有索引,邏輯也比較簡單,
這種寫法,在資料量大的時候確實效率比較低,用不到索引
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);
執行結果:2.23秒
第二種寫法,與子查詢進行join關聯,這種寫法相當於上面的IN子查詢寫法,下面測試發現,效率確實有不少的提高
select tpp1.* from test_table2 tpp1, ( select pay_id from test_table2 WHERE pay_time>="2016-07-01 00:00:00" AND pay_time<="2017-07-03 12:59:59" group by pay_id having count(pay_id) > 1) tpp2 where tpp1.pay_id=tpp2.pay_id
執行結果:0.48秒
In子查詢的執行計劃,發現外層查詢是一個全表掃描的方式,沒有用到pay_id上的索引
join自查的執行計劃,外層(tpp1別名的查詢)是用到pay_id上的索引的。
後面想對第一種查詢方式使用強制索引,雖然是不報錯的,但是發現根本沒用
如果子查詢是直接的值,則是可以正常使用索引的。
# 可見MySQL對IN子查詢的支持,做的確實不怎麼樣。
另外:加上一個使用臨時表的情況,雖然比不少join方式查詢的,但是也比直接使用IN子查詢效率要高,這種情況下,也是可以使用到索引的,不過這種簡單的情況,是沒有必要使用臨時表的。
# 下面是類似案例在sqlserver 2014中的測試,幾萬完全一樣的測試表結構和數量,可見這種情況下,兩種寫法,在SQL Server中可以認為是完全一樣的(執行計劃+效率),這一點SQL Server要比MySQL強不少
下面是sqlserver中的測試環境腳本。
create table test_table2 ( id int identity(1,1) primary key, pay_id int, pay_time datetime, other_col varchar(100) )begin trandeclare @i int = 0while @i<300000begininsert into test_table2 values (@i,getdate()-rand()*300,newid()); if(@i%1000=0)begininsert into test_table2 values (@i,getdate()-rand()*300,newid());endset @i = @i + 1endCOMMITGOcreate index idx_pay_id on test_table2(pay_id); create index idx_time on test_table2(pay_time);GOselect * from test_table2 where pay_id in (select pay_id from test_table2 where pay_time>='2017-01-21 00:00:00' AND pay_time<='2017-07-03 12:59:59' group by pay_id having count(pay_id) > 1); select tpp1.* from test_table2 tpp1, ( select pay_id from test_table2 WHERE pay_time>='2017-01-21 00:00:00' AND pay_time<='2017-07-30 12:59:59' group by pay_id having count(pay_id) > 1) tpp2 where tpp1.pay_id=tpp2.pay_id
總結:在MySQL資料中,截止5.7.18版本,對IN子查詢,仍要慎用
以上是MySQL中兩種子查詢的寫法的詳細內容。更多資訊請關注PHP中文網其他相關文章!