C++11 标准库 bind 函数
281 2023-04-03 03:59:28
join方式连接多个表,本质就是各个表之间数据的循环匹配。MySQL5.5版本之前,MySQL只支持一种表间关联方式,就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大,
则join关联的执行时间会非常长。在MySQL5.5以后的版本中,MySQL通过引入BNLJ算法来优化嵌套执行。
驱动表就是主表,被驱动表就是从表、非驱动表
SELECT * FROM A JOIN B ON ...
A一定是驱动表吗?不一定,优化器会根据你查询语句做优化,决定先查哪张表。先查询的那张表就是驱动表,反之就是被驱动表。通过explain关键字可以查看。
算法相当简单,从表A中取出一条数据1,遍历表B,将匹配到的数据放到result…以此类推,驱动表A中的每一条记录与被驱动表B的记录进行判断:
可以看到这种方式效率是非常低的,以上述表A数据100条,表B数据1000条计算,则A*B= 10万次。开销统计如下:
Index Nested-Loop Join其优化思路主要是为了减少内层表数据的匹配次数,所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配,避免和内层表每层记录进行比较,这样极大的减少了对内层表的匹配次数。
驱动表中的每条记录通过被驱动表的索引进行访问,因为索引查询的成本是比较固定的,故mysql优化器都倾向于使用记录数少的表作为驱动表(外表)。
如果被驱动表加索引,效率是非常高的,但如果索引不是主键索引,所以还得进行一次回表查询。相比,被驱动表的索引是主键索引,效率会更高。
如果存在索引,那么会使用index的方式进行join,如果join的列没有索引,被驱动表要扫描的次数太多了。每次访问被驱动表,其表中的记录都会被加载到内存中,然后再从驱动表中取一条与其匹配,匹配结束后清除内存,然后再从驱动表中加载一条记录,然后把被驱动表的记录在加载到内存匹配,这样周而复始,大大增加了IO的次数。为了减少被驱动表的IO次数,就出现了Block Nested-Loop Join的方式。
不再是逐条获取驱动表的数据,而是一块一块的获取,引入了join buffer缓冲区,将驱动表join相关的部分数据列(大小受join buffer的限制)缓存到join buffer中,然后全表扫描被驱动表,被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配(内存中操作),将简单嵌套循环中的多次比较合并成一次,降低了被驱动表的访问频率。
注意:
通过show variables like '%optimizer_switch' 查看block_nested_loop状态。默认是开启的。
show variables like '%optimizer_switch';
驱动表不能一次性加载完,要看join buffer能不能存储所有的数据,默认情况下join_buffer_size=256k
show variables like '%join_buffer%';
mysql> show variables like '%join_buffer%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)
join_buffer_size的最大值在32位系统可以申请4G,而在64位操做系统下可以申请大于4G的Join Buffer空间(64位Windows除外,其大值会被截断为4GB并发出警告)。
案例:假设t2表的字段数量大于1,如果用第二的话,t2.*要比t1.b的字段要多,在join_buffer里面能加载到的条目数就减少了。
select t1.b, t2.* from t1 straight.join t2 on (t1.b=t2.b) where t2.id<=108;#推荐
select t1.b,t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100;#不推荐
从MySQL的8.0.20版本开始将废弃BNLJ,因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join