===============
本文将以真執行個體子來講解小表驅動大表(In,Exists差別)
1 準備資料
1.1 建立表、函數、存儲過程
參照 這篇(調用函數和存儲過程批量插入資料) 文章中的第 1-7 步,注意,不要執行第8步
1.2 插入資料
現在來執行第8步。
1.2.1 向 Department 表中插入 100 條記錄
CALL insert_dept(1000, 100)
1.2.2 向 Employee 表中插入 100000 條記錄
CALL insert_employee(100000000, 100000);
2 測試
2.1 查詢所有 Employee 資訊,要求是:Employee 的 dept_id 存在于 Department 表中
Case#1:用 IN
SELECT * FROM employee WHERE dept_id IN (SELECT id FROM department);
結果:在我本機測試了數十次,耗時大概是 120--130 ms
Case#2:用 EXISTS
SELECT * FROM employee e WHERE EXISTS (SELECT 1 FROM department d WHERE e.dept_id = d.id);
結果:在我本機測試了數十次,耗時大概是 350--370 ms
2.2 查詢所有 Department 資訊,要求是:至少有一條 Employee 記錄的 dept_id 對應 Department(或者說:此部門下至少有一條員工記錄)
Case#3:用 EXISTS
SELECT * FROM department d WHERE EXISTS (SELECT 1 FROM employee e WHERE d.id = e.dept_id);
結果:在我本機測試了數十次,耗時大概是 4--6 ms
Case#4:用 IN
SELECT * FROM department WHERE id IN (SELECT dept_id FROM employee);
結果:在我本機測試了數十次,耗時大概是 50--55 ms
2.3 分析并總結
在 Case#1,#2 中,Employee 是大表,Department 是小表,用 IN(Department) 的效果較好(大概是用 EXISTS 時間的三分之一)====> IN 後面跟小表~
在 Case#3,#4 中,Employee 是大表,Department 是小表,用 EXISTS(Employee) 的效果較好(大概是用 IN 時間的十分之一)====> EXISTS 後面跟大表~
記憶:IN 後面跟小表~EXISTS 後面跟大表~~~因為 IN 這個單詞比 EXISTS 單詞更短(更小),EXISTS 這個單詞比 IN 更長(更大)
2.4 進一步分析
至于為什麼 Case#1 優于 Case#2,Case#3 優于 Case#4,還沒搞清楚到底是為什麼,,,,,TODO
3 結論
小表驅動大表
IN 小 EXISTS 大