联接查询处理
复杂查询
联接查询
Nested Loop Join
驱动表就是在嵌套循环和哈希连接中，用来最先获得数据，并以此表为依据，逐步获得其他表的数据，直至最终查询到所有符合条件的数据的第一个表。驱动表不一定是表，也可以是一个数据集，即由某个表中满足条件的数据行组成的子集合
在A left join B on condition 的执行过程如下：
- 以
table_A 为驱动表，检索table_B ；根据on 条件过滤table_B 的数据，构建table_A 结果集，并且添加外部行。 - 对结果集执行
where 条件过滤。如果A 中有一行匹配where 子句但是B 中没有一行匹配on 条件，则生成另一个B 行，其中所有列设置为NULL 。 - 依次执行
group by 语句分组，执行having 语句对分组结果筛选，执行select 出结果集，执行distinct 对结果去重，执行order by 语句，执行limit 语句。
如果还有第三个参与
一个简单的嵌套循环联接（NLJ）算法，循环从第一个表中依次读取行，取到每行再到联接的下一个表中循环匹配；这个过程会重复多次直到剩余的表都被联接了。假设表
Table Join Type
t1 range
t2 ref
t3 ALL
for each row in t1 matching range {
for each row in t2 matching reference key {
for each row in t3 {
if row satisfies join conditions,
send to client
}
}
}
因为cost = outer access cost + (inner access cost * outer cardinality)。
Block Nested-Loop Join Algorithm
一个块嵌套循环联接（BNL）算法，将外循环的行缓存起来，读取缓存中的行，减少内循环的表被扫描的次数。例如，如果
join_buffer_size 系统变量的值决定了每个联接缓冲区的大小。- 联接类型为
ALL 、index、range 时（换句话说，联接的过程会扫描索引或数据时） ，MySQL 会使用联接缓冲区。 - 缓冲区是分配给每一个能被缓冲的联接，所以一个查询可能会使用多个联接缓冲区。
- 联接缓冲区永远不会分配给第一个表，即使该表的查询类型为
ALL 或index 。 - 联接缓冲区联接之前分配，查询完成之后释放。
- 使用到的列才会放到联接缓冲区中，并不是所有的列。
上面的例子使用的是
for each row in t1 matching range {
for each row in t2 matching reference key {
store used columns from t1, t2 in join buffer
if buffer is full {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions,
send to client
}
}
empty buffer
}
}
}
if buffer is not empty {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions,
send to client
}
}
}
首先将
(S * C)/join_buffer_size + 1
由此可见，随着
结果排序
在
CREATE TABLE `person` (
`id` int(11) NOT NULL,
`city` varchar(16) NOT NULL,
`name` varchar(16) NOT NULL,
`age` int(11) NOT NULL,
`addr` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `city` (`city`)
) ENGINE=InnoDB;
select city,name,age from person where city='武汉' order by name limit 100 ;
使用
- 初始化
sortbuffer ，用来存放结果集； - 找到
city 索引，定位到city 等于武汉的第一条记录，获取主键索引ID ； - 根据
ID 去主键索引上找到对应记录，取出city,name,age 字段放入sortbuffer ； - 在
city 索引取下一个city 等于武汉的记录的主键ID ； - 重复上面的步骤，直到所有
city 等于武汉的记录都放入sortbuffer ； - 对
sortbuffer 里的数据根据name 做快速排序； - 根据排序结果取前面
1000 条返回；
这里是查询
另外如果
如果
alter table person add index city_user(city, name);
这样查询过程如下：
- 根据
city,name 联合索引定位到city 等于武汉的第一条记录，获取主键索引ID ； - 根据
ID 去主键索引上找到对应记录，取出city,name,age 字段作为结果集返回； - 继续重复以上步骤直到
city 不等于武汉，或者条数大于1000 。
由于联合所以在构建索引的时候，在