天天看點

mysql驅動表優化_查詢優化--小表驅動大表(In,Exists差別)

===============

本文将以真執行個體子來講解小表驅動大表(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 大