查询优化--小表驱动大表(In,Exists区别)

Mysql 系列文章主页 

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

本文将以真实例子来讲解小表驱动大表(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

一篇文章可供参考:https://www.cnblogs.com/beijingstruggle/p/5885137.html

3 结论

小表驱动大表

IN 小 EXISTS 大

posted @ 2018-04-24 15:13  cyhbyw  阅读(497)  评论(0编辑  收藏  举报