Mysql优化原则_小表驱动大表IN和EXISTS的合理利用详解数据库
看以上两个for循环,总共循环的次数是一样的。但是对于mysql数据库而言,并不是这样了,我们尽量选择第②个for循环,也就是小表驱动大表。
数据库最伤神的就是跟程序链接释放,第一个建立了10000次链接,第二个建立了50次。假设链接了两次,每次做上百万次的数据集查询,查完就走,这样就只做了两次;相反建立了上百万次链接,申请链接释放反复重复,这样系统就受不了了。
这时候就诞生了in 和exists的对比。
小表驱动大表:即小的数据集驱动大的数据集。
这里假设A表代表员工表,B表代表部门表。
假设部门只有三个,销售、技术部、行政部,言下之意是在这三个部门里的所有员工都查出。
select * from A where id in (select id from B);
这样写就等价于:
for select id from B。比如华为有100个部门,但是华为的员工少说有15W-20W,员工总比部门多,这时候就相当于得到了小表(部门表);for select * from A where A.id = B.id,相当于A.id等B表里面的,相当于从部门表获得对应的id。
当B表的数据集必须小于A表的数据集时,用in优于exists。
反之
select * from A where exists (select 1 from B where B.id = A.id); //这里的select 1并不绝对,可以写为select X或者A,B,C都可以,只要是常量就可以。
这样写就等价于:
for select * from A,先从A表做循环
for select * from B where B.id = A.id,再从B表做循环。
这样exists就会变成看看A表是否存在于(select 1 from B where B.id = A.id)里面,这个查询返回的是TRUE或者FALSE的BOOL值,简单来说就是要当A表的数据集小于B表的数据集时,用exists优于in。要注意的是:A表与B表的ID字段应该建立索引。
语法:EXISTS
SELECT FROM table WHERE EXISTS(subquery)。
理解:将主查询的数据放到子查询中做条件验证,根据验证结果(TRUE或者FALSE)来决定朱查询的数据结果是否得意保留。
相当于从表A和B中取出交集,然后再从A表中取出所在交集的部分数据,当然后面加WHERE条件还可以进一步筛选。
补充:
1:EXISTS(subquery)只返回TRUE或者FALSE,因此子查询中的SELECT * 也可以是SELECT 1或者SELECT X ,官方说法是实际执行时会忽略SELECT清单,因此没有区别。
2:EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比,如果担忧效率问题,可进行实际校验。
3:EXISTS子查询旺旺可以用条件表达式,其他子查询或者JOIN来替代,何种最优需要具体问题具体分析。
如果查询的两个表大小相当,那么用in和exists差别不大。
延伸举例巩固:
如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in:
例如:表A(小表),表B(大表)
select * from A where cc in (select cc from B) ;// 效率低,用到了A表上cc列的索引; select * from A where exists(select cc from B where cc=A.cc) ;// 效率高,用到了B表上cc列的索引。
相反的
select * from B where cc in (select cc from A) ; //效率高,用到了B表上cc列的索引; select * from B where exists(select cc from A where cc=B.cc) ;//效率低,用到了A表上cc列的索引。
not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。
原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/5705.html
mysql相关文章
- mysql倒序截取字符串_MySQL数据库之mysql截取字符串与reverse函数
- MySQL数据库:完整的PDF指南(mysql数据库pdf)
- MySQL字段值的累加运算(mysql字段累加)
- MySQL字符串查找之路:解决数据库难题(mysql字符串查找)
- MySQL数据库:简单更新操作(mysql数据库更新)
- 使用Python编程连接MySQL数据库(python连mysql)
- 恢复MySQL数据库损坏文件(mysql损坏)
- JavaWeb实现MySQL数据库连接(javaweb连接mysql)
- 数据库学习MySQL数据库编辑技巧(如何编辑mysql)
- MySQL数据库的外键约束(mysql的外键约束)
- MySQL集群:强大的工具解决方案(mysql集群工具)
- MySQL中表空间的管理技术(mysql有没有表空间)
- 约束MySQL列实施唯一约束的必要性(mysql列唯一)
- MySQL中查看字符集编码的方法(mysql显示编码)
- MySQL中的序列与应用(序列mysql)
- MySQL管理库存,轻松有效(mysql库存管理)
- MySQL实现分页:让你更快获取资料(实现mysql分页)
- MySQL中增加分区的方法(mysql增加分区)
- MySQL操作之操作数据集返回结果(mysql返回集合)
- 函数使用MySQL的Concat函数连接字符串(mysql中concat)
- 如何启动MySQL数据库:详解MySQL启动命令与步骤(mysql数据库的启动)
- 提高数据处理效率:探究Qt与MySQL数据库的结合应用(qt与mysql数据库)
- 探寻Mysql最佳读物:推荐好的书籍解读数据库(mysql好的书籍)
- 优雅式PHP配置MySQL数据库(php 配置 mysql)
- MySQL遗忘密码,如何重置登录?(mysql密码忘记)
- MySQL附加:实现双重保障的数据库稳定性(mysql 附加 数据库)
- MySQL 使用联合唯一索引确保数据可靠性(mysql联合唯一索引)
- 优化MySQL数据库,轻松节省空间(mysql数据库整理)
- 如何在PHP中使用MySQL数据库(mysql中php)
- MySQL中AS的使用方法(mysql中as用法)
- MySQL中超屌的CID优化你的数据库(cid mysql)
- 使用asp技术连接MySQL数据库(asp与mysql数据库)
- 快速高效处理大量数据MySQL删除操作技巧(mysql中删除大量数据)
- 利用MySQL高效查询一千万条数据的技巧(mysql一千万中查询)
- MySQL中不同版本的DIS差异解析(mysql 不同dis)
- MySQL查询上季度末日期(mysql 上季度末日期)
- MySQL插入数据失败,分析原因及解决方法(mysql不能插数据库)