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

Mysql 系列文章主页

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

本文将以真实例子来讲解小表驱动大表(In,Exists区别)

1 准备数据

1.1 创建表、函数、存储过程

参照  这篇(调用函数和存储过程批量插入数据)  文章中的第 1-7 步,注意,不要执行第8步

1.2 插入数据

现在来执行第8步。

1.2.1 向 Department 表中插入 100 条记录网址:yii666.com<

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文章地址https://www.yii666.com/article/754093.html

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 分析并总结网址:yii666.com

在 Case#1,#2 中,Employee 是大表,Department 是小表,用 IN(Department) 的效果较好(大概是用 EXISTS 时间的三分之一)====> IN 后面跟小表~

在 Case#3,#4 中,Employee 是大表,Department 是小表,用 EXISTS(Employee) 的效果较好(大概是用 IN 时间的十分之一)====> EXISTS 后面跟大表~文章来源地址https://www.yii666.com/article/754093.html

记忆:IN 后面跟小表~EXISTS 后面跟大表~~~因为 IN 这个单词比 EXISTS 单词更短(更小),EXISTS 这个单词比 IN 更长(更大)文章来源地址:https://www.yii666.com/article/754093.html

2.4 进一步分析

至于为什么 Case#1 优于 Case#2,Case#3 优于 Case#4,还没搞清楚到底是为什么,,,,,TODO

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

3 结论

小表驱动大表

IN 小 EXISTS 大

版权声明:本文内容来源于网络,版权归原作者所有,此博客不拥有其著作权,亦不承担相应法律责任。文本页已经标记具体来源原文地址,请点击原文查看来源网址,站内文章以及资源内容站长不承诺其正确性,如侵犯了您的权益,请联系站长如有侵权请联系站长,将立刻删除

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信图片_20190322181744_03.jpg

微信扫一扫打赏

请作者喝杯咖啡吧~

支付宝扫一扫领取红包,优惠每天领

二维码1

zhifubaohongbao.png

二维码2

zhifubaohongbao2.png