做的网站怎么发网上,seo网站推广简历,作业提交免费网站,网站 动态join类型
在一些报表统计或数据展示时候需要提取的数据分布在多个表中#xff0c;这个时候需要进行join连表操作。join将两个或多个表当成不同的数据集合#xff0c;然后进行集合取交集运算。比如有订单Order表记录用户id#xff0c;如果像查询订单对应的用户信息#xff…join类型
在一些报表统计或数据展示时候需要提取的数据分布在多个表中这个时候需要进行join连表操作。join将两个或多个表当成不同的数据集合然后进行集合取交集运算。比如有订单Order表记录用户id如果像查询订单对应的用户信息可以将Order和User表进行关联。
根据join结果集计算方式不同join大致分为两种主要类型
内连接
内连接inner join也称为等值连接是最常用的Join方式。它只返回两个表中匹配的行即两个表中具有相同值的列的行。比如 from order o inner join user u on o.uidu.uid。
外连接
外连接分两种左外连接和右外连接。两个区别于内连接地方是不仅包含两个表等值情况返回左表中的所有行以及右表中与左表匹配的行。如果右表中没有匹配的行则返回NULL。
如from order o left join user u on o.uidu.uid。order.uid即使在user表不存在也会返回订单记录只不过对应的用户信息为null。
右连接同理以右表数据为主返回右表所有数据。
还有一种全外连接没有关联条件查找两个表之间的所有关联和不关联的记录无论它们是否有匹配条件。这种应该不常用只有数据对比时候可能会用。
join原理
mysql在表join查询时候使用嵌套循环方式nested-loop algorithm进行数据匹配。就是拿驱动表每一条和被驱动表所有数据依次进行匹配。这也很好理解。两个关联表相当于两个集合然后求两个集合的交集。就是从一个集合依次拿出所有和另一个进行比较是否相等是交集。假如有t1、t2、t3三个表进行关联,t1上有range范围筛选t1和t2关联有ref类型索引关联执行过程大概这样
for each row in t1匹配范围数据 {for each row in t2匹配索引值 {for each row in t3 {t3满足连接条件记录}}
}这样假设表A有M行记录表B有N行记录。则需要M乘N次比较取交集。表B被扫描M*N次。这些都是磁盘全表扫描。显然如果M和N都比较大的时候会比较慢。
上面看到嵌套循环外循环每行记录都会导致内循环多次执行。这里注意多表关联时候不是先计算前两个的关联结果然后结果再和后面的表进行关联。比如上面例子不会有t1和t2的临时连接结果集。
Block Nested-Loop Join Algorithm
为了提高join的效率mysql在嵌套循环比对时候进行了一定的修改优化。使用一个变种块嵌套循环Block Nested-Loop Join Algorithm。什么意思呢这次我读取驱动表的时候不是每次读取一条了我读取多条存放到一个join buffer 块里。然后这样每次多条和被驱动表的数据进行比较这样就将原来的多次磁盘读取比较转移到了内存比较。这样被驱动表全表扫描次数变成了(M/buffer rows)*N。
一次读取的行数buffer rows由每行需要的数据大小和join_buffer_size大小两个因素决定。
join_buffer_size的大小默认是256KB
mysql SELECT join_buffer_size;
--------------------
| join_buffer_size |
--------------------
| 262144 |
--------------------
从上面的计算公式不难看出驱动表的行数越少join_buffer_size越大扫描被驱动表的次数越少。这也是为什么要使用小表驱动大表的原因。如果驱动表join数据可以一次放到join_buffer_size中则被驱动表只需要一次全表扫描。如果不能驱动表会被分成多次读取到join_buffer_size中join_buffer_size会循环使用。
join buffer有以下几个特点
1、当执行计划的type类型是ALL、index或range的时候会使用join buffer。
2、如果一个查询中有多个表进行连接操作 Join Buffer 只会为非常量表分配内存空间即使这个表的类型是 ALL 或者 index。
3、连接操作的 Join Buffer 中只存储连接所需的列而不是整个行。
4、一个join分配一个join buffer如果一个查询有多个join可能会使用多个join buffer。
5、join buffer的分配在join执行前完成查询结束后立即释放。
如果使用了join buffer一般在explain的Extra列会有Using join buffer (Block Nested Loop)信息。
Index Nested-Loop Join
上面说的使用Block Nested-Loop Join算法相对最初的嵌套循环使用join buffer提高了匹配的效率。但是当表数据很大时仍然不太理想。在Block Nested-Loop Join算法中执行计划type最好是range。还有更好的ref和eq_ref没有说。也就是当join的列是索引列时。
注意这里说的索引列是指被驱动表因为驱动表总是要进行一次全表扫描。但是这个时候去被驱动表找数据就不是全表扫描了因为有索引是直接在索引树上进行查找这就快多了。
在常规业务开发(非高并发)进行表join操作是非常常见的在表连接时候尽量使用小表驱动大表这里的小表是指条件过滤后参加join条件的数据。连接时在被驱动表连接字段上创建索引也就是使用Index Nested-Loop Join。
join优化
Batched Key Access JoinsBKA
Batched Key Access Joins从名字上也能看出来大致意思。key是索引意思是对Index Nested-Loop Join的查询优化。 BKA要借助于MRR,先来说下MRR。
Multi-Range Read
当在某个辅助索引上进行范围查找时通常在回表查询的过程中会发生大量的随机读。
比如下面的语句
select * from user where uname like 曹%;uname列上建有索引可以从uname索引上获取主键值然后再回表根据主键逐条从聚簇索引上查询行数据。这个时候获取的主键是无序的会造成大量的随机读。MRR(Multi-Range Read)优化就是为了解决这种场景
MRR处理过程是在根据索引获取到主键后不是立即进行回表查询而是先将主键值放入到一个read_rnd_buffer中然后对其进行排序最后再有序的去聚簇索引检索行数据。如果对应主键索引值足够密集这样可以大大减少随机读。
read_rnd_buffer的大小由read_rnd_buffer_size参数来进行控制默认大小是256kb。另外开启MMR需要在optimizer_switch中设置mrron。默认值是on。另外还有一个相关的参数mrr_cost_based如果这个参数设置为off表示符合条件就使用mrr如果为on则优化器会基于成本进行考虑是否使用mrr。该值默认是on 。
如果使用到了mrr在explain的Extra列一般会显示Using index condition; Using MRR。
看完了上面的MRR这里回到使用索引join连表查询其实也存在上面的问题。回想Index Nested-Loop Join的查询执行过程是每次一行行的去驱动表中去找数据每次驱动表只被匹配一个值。这里Batched Key Access Joins就结合了Block Nested-Loop Join和MRR两个的优点执行过程大致如下
1、批量取驱动表的关联字段放入join buffer中
2、将join buffer中的关联列值批量的发送给引擎在被驱动表上通过MRR以最优的方式进行索引树搜索匹配获取到对应的主键rowId。
3、按检索到的rowId进行排序顺序从被驱动表上获取关联数据。
BKA是对Index Nested-Loop Join的优化被驱动表上要有对应的索引。并且被驱动表上会发生回表查询的情况。
开启BKA首先需要开启MRR需要进行以下设置
mysql SET optimizer_switchmrron,mrr_cost_basedoff,batched_key_accesson;
