网站建设中国的发展,h5短视频源码,烟台网站制作哪家好,上海建设工程质监局网站目录要不要用joinJoin驱动表选择Multi-Range Read优化Batched Key Access #xff08;BKA#xff09;对NLJ进行优化BNL算法性能问题BNL转BKA要不要用join
1、如果使用的是Index Nested-Loop Join算法#xff0c;即可以用上被驱动表的索引#xff0c;可以用
2、如果使用的… 目录要不要用joinJoin驱动表选择Multi-Range Read优化Batched Key Access BKA对NLJ进行优化BNL算法性能问题BNL转BKA 要不要用join
1、如果使用的是Index Nested-Loop Join算法即可以用上被驱动表的索引可以用
2、如果使用的是Block Nested-Loop Join算法。扫描行数过多尤其是大表join会导致扫描多次被驱动表会占用大量系统资源这种Join尽量不要用
Join驱动表选择
1、如果是Index Nested-Loop Join算法使用小表做驱动表
2、如果是Block Nested-Loop Join算法在 join_buffer_size 足够大大表小表一样当 join_buffer_size 不够大时选择小表做驱动表
注意在决定哪个表做驱动表时应该是两个表按照各自条件过滤完成之后计算参与join的各个字段的总数据量数据量小的表那就是小表。
Multi-Range Read优化
若有这样查询语句
select * from t1 where a1 and a100;a值是递增的但是回表后的id并非如此而是随机的会带来性能损失。
大多数数据按照主键递增顺序插入得到所以我们可以认为如果按照主键的递增顺序查找的话对磁盘的读比较接近顺序读从而可以提升读性能。
1、根据索引a定位到满足条件的记录将id值放入read_rnd_buffer中
2、将read_rnd_buffer中的id进行递增排序
3、排序后的id数组依次到主键id索引中查找记录并作为结果返回
总的来说就是**先将索引数据缓存查到id之后排序之后再回表 **
用法
设置
set optimizer_switchmrr_cost_basedoff现在的优化器在判断消耗时更倾向于不使用MRR所以需要设置为off后就会固定使用MRR
Batched Key Access BKA对NLJ进行优化
Index Nested-Loop Join执行逻辑是从驱动表t1一行行取出a值再到驱动表t2去做join。对于表t2来说每次都是匹配一个值MMR优势用不上。
既然这样将表t1的数据取出来一部分先放到一个临时内存里join_buffer.
然后在此基础上复用MRR即可。
使用方法
set optimizer_switchmrron,mrr_cost_basedoff,batched_key_accesson;BNL算法性能问题
之前提到过InnoDB的LRU优化第一次从磁盘读入内存的数据页会先放到old区域如果1s后这个数据页不再被访问就不会移动到LRU链表头部这样对Buffer Pool命中率影响就不大了。
如果使用了BNL的join语句多次扫描一个冷表并且这个语句执行时间超过1s就会在再次扫描冷表时把冷表的数据页移动到LRU链表头部。
如果冷表数据很大 会一直占据old区正常页无法进入无法更新young区 tips: 冷表指表中数据还没有加载到bufferpool中需要先从盘里读出来的表 又因为优化机制一个正常访问的数据页要进入young区域需要隔1s再次被访问到。由于join’语句在循环都磁盘和淘汰内存页进入old区域的数据页很可能在1s之内就被淘汰了。
大表join后对于Buffer Pool的影响是持续性的需要依靠后续的查询请求慢慢恢复内存命中率。
总结BNL对于系统的影响
1、可能多次扫描被驱动表占用磁盘IO资源
2、判断join条件执行M * N次占用CPU资源
3、可能导致Buffer Pool的热数据被淘汰影响内存命中率
所以我们需要优化BNL通过给驱动表的join字段加索引的方式将BNL转换为BKA
BNL转BKA
对于一些不常执行大表join的sql不在被驱动表上创建索引的情况可以创建一个临时表 create templete table在这个临时表上创建索引然后让驱动表与临时表做join操作。 为什么不在被驱动表上创建索引是因为这块sql功能不常用创建索引浪费空间并且可能触发这块的join sql 也不经常调用。
创建临时表以及join语句示例如下
create temporary table temp_t(id int primary key, a int, b int, index(b))engineinnodb;
insert into temp_t select * from t2 where b1 and b2000;
select * from t1 join temp_t on (t1.btemp_t.b);
