《Mysql - 到底可不可以使用 Join ?》
一:Join 的问题?
- 在实际生产中,使用 join 一般会集中在以下两类:
- DBA 不让使用 Join ,使用 Join 会有什么问题呢?
- 如果有两个大小不同的表做 join,应该用哪个表做驱动表呢?
二:数据准备
CREATE TABLE `t2` (
`id` int() NOT NULL,
`a` int() DEFAULT NULL,
`b` int() DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB; CREATE TABLE `t1` (
`id` int() NOT NULL,
`a` int() DEFAULT NULL,
`b` int() DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB;- 建立 t1,t2 两个完全相同的表,t1 表中写入 100 条数据,t2 表中 写入 1000 条数据。
三:Index Nested-Loop Join(NLJ) (被驱动表有索引的情况选择)
- 语句
- 为了避免Mysql选择驱动表对于分析的影响,改用 straight_join 让 MySQL 使用固定的连接方式执行查询。
- t1 是驱动表,t2 是被驱动表。
- select * from t1 straight_join t2 on (t1.a=t2.a);
- 执行流程
- 在这条语句里,被驱动表 t2 的字段 a 上有索引,join 过程用上了这个索引
- 从表 t1 中读入一行数据 R;
- 从数据行 R 中,取出 a 字段到表 t2 里去查找;
- 取出表 t2 中满足条件的行,跟 R 组成一行,作为结果集的一部分;
- 重复执行步骤 1 到 3,直到表 t1 的末尾循环结束。
-
- 小结
- 这个过程是先遍历表 t1,然后根据从表 t1 中取出的每行数据中的 a 值,去表 t2 中查找满足条件的记录。
- 在形式上,这个过程很像写程序时的嵌套查询类似,并且可以用上被驱动表的索引,所以我们称之为“Index Nested-Loop Join”,简称 NLJ。
- 整个过程, 总扫描行数是 200(t1 200 + t2 索引树200)
四:Block Nested-Loop Join(NLJ)(被驱动表无索引选择)
- 语句
- select * from t1 straight_join t2 on (t1.a=t2.b);
- 由于表 t2 的字段 b 上没有索引,因此在执行流程时,每次到 t2 去匹配的时候,就要做一次全表扫描。
- 流程
- 把表 t1 的数据读入线程内存 join_buffer 中,由于我们这个语句中写的是 select *,因此是把整个表 t1 放入了内存;
- 扫描表 t2,把表 t2 中的每一行取出来,跟 join_buffer 中的数据做对比,满足 join 条件的,作为结果集的一部分返回。
-
- 小结
- 可以看到,在这个过程中,对表 t1 和 t2 都做了一次全表扫描,因此总的扫描行数是 1100。
- 由于 join_buffer 是以无序数组的方式组织的,因此对表 t2 中的每一行,都要做 100 次判断,总共需要在内存中做的判断次数是:100*1000=10 万次。
- join_buffer 的大小是由参数 join_buffer_size 设定的,默认值是 256k。如果放不下表 t1 的所有数据话,策略很简单,就是分段放。
五:总结
- 能不能使用 join ?
- 如果可以使用 Index Nested-Loop Join 算法,也就是说可以用上被驱动表上的索引,其实是没问题的;
- 如果使用 Block Nested-Loop Join 算法,扫描行数就会过多。
- 尤其是在大表上的 join 操作,这样可能要扫描被驱动表很多次,会占用大量的系统资源。所以这种 join 尽量不要用。
- 如果要使用 join,应该选择大表做驱动表还是选择小表做驱动表?
- 在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与 join 的各个字段的总数据量,数据量小的那个表,就是“小表”,应该作为驱动表。
最新文章
- Android GridView 通过seletor 设置状态和默认状态
- 查看Sql Server被锁的表以及解锁
- MongoDB的学习--索引类型和属性
- MVC – 4.mvc初体验(1)
- 淘宝TFS分布式文件系统内部实现
- 三层架构中bll层把datatable转换为实体model的理解
- 在没备份undo的情况下,undo丢失,重启数据库报ORA-01157错误
- hadoop2.2编程:MRUnit
- docker_openwrt
- Windows XP硬盘安装Ubuntu 12.04双系统图文详解
- dos命令创建安卓签名
- Python 项目实践三(Web应用程序)第二篇
- Matlab中数据的存储方式
- IIS部署Angular2
- JavaSet接口、唯一元素和Map接口整理
- JAVA核心技术I---JAVA基础知识
- 浏览器修改或添加Cookie--chrome插件【edit this cookie】、【postman】
- CodeSmith和Powerdesigner的搭建和实例化操作 转载自黄聪同学
- mybatis的resultMap与resultType的区别
- <;NET CLR via c# 第4版>;笔记 第14章 字符,字符串和文本处理