中海建筑建设有限公司网站,简述网站规划的主要任务,装修设计合同范本,永久免费虚拟空间Join语句如何优化?
Join语句的两种算法#xff0c;分别为Index Nested-Loop Join和Block Nested-Loop Join
NLJ在大表Join当中还不错#xff0c;但BNL在大表join时性能就差很多#xff0c;很耗CPU资源。
如何优化这两个算法
创建t1#xff0c;t2算法#xff0c;在t1中…Join语句如何优化?
Join语句的两种算法分别为Index Nested-Loop Join和Block Nested-Loop Join
NLJ在大表Join当中还不错但BNL在大表join时性能就差很多很耗CPU资源。
如何优化这两个算法
创建t1t2算法在t1中插1000行数据每一行a1001-id也就是a是逆序的t2中插入10万条数据。
Multi-Range Read优化MRR优化的主要目的为尽量使用顺序读盘。
我们先来看看回表回表的概念InnoDB在普通索引a上查到主键id后再根据一个个主键id到主键索引当中去查找数据的概念。
那么回表是一行行查数据还是批量的查数据呢
因为主键是一颗b树在这颗树上每次只能根据一个主键id查数据所以回表过程必然是一行一行进行的。
若是随着a的值递增的id的值就会变为随机的那么会出现随机访问性能差但是如果改变了查询的顺序性能就会提升。所以我们可以认为若是按照主键递增的顺序查找对磁盘的读就会接近顺序读所以可以提升读性能。
这就是MRR优化的主要思路语句的执行变成下面这种样子。
1.根据索引a定位满足的记录将id值放入read_rnd_buffer中
2.将read_rnd_buffer中的id进行递增排序
3.排序后的id数组依次到主键id索引当中查记录。
read_rnd_buffer的大小由read_rnd_buffer_size控制满了之后走完了23之后再到1继续。
要稳定的使用MRR优化需要设置optimizer_switch“mmr_cost_basedoff”因为优化器策略会更倾向于不使用MRR优化我们这样设置可以强制使用。
MRR能够提升性能的核心在于查询的a是范围查询能够获得足够多的主键id排序后再去查才能体现出顺序的优势。
Batched Key Access
MySQL5.6之后引入BKA算法对NLJ的优化。
NLJ算法是从驱动表t1一行行的取出a的值再到被驱动表当中去做join对于t2来说每次匹配一个值MRR是无法使用的。
所以我们可以将t1当中的数据取出来一部分放入join_buffer一次行传入多个数据给t2就可以进行优化了。
启用BKA算法
set optimizer_switchmmron,mmr_cost_basedoff,batched_key_accesson
BKA主要依赖于RMM所以我们得先打开RMM算法。
BNL算法的性能问题
若是一个使用BNL算法的Join语句多扫描一个冷表而且这个语句执行时间超过了1秒就没会再次扫描冷表的时候将这个冷表的数据页移到LRU头部这种情况是冷表数据量小于整个Buffer Pool的3/8能够完全放入old区域。
若是这个冷表很大一个正常访问的数据页进入young区域join语句就在读磁盘和淘汰内存页的时候进入old区域的数据页可能会被删除。
大表join对Io有影响但是语句结束之后对IO的影响就会结束但是对于Buffer Pool的影响是持续性的。
可以通过增大joinn_buffer_size的值减少对驱动表的扫描行数。
对无BNL常见的优化方法就是给被驱动表的join字段加上索引将BNL转为BKA算法。
一些情况下直接进将被驱动表上加索引就可以转为BKA算法当时有些情况下是不可以的比如一个十万的表在where的限制条件下筛选出的需要参加join语句的只有2000条这个时候添加索引就有些浪费了但是要是不添加索引一次一次的的对比就太耗费CPU资源有一种方法可以完美解决这种问题就是创建一个临时表。
创建一个临时表将这2000条符合条件的语句添加到其中然后给这个临时表添加索引这样触发BKA算法拉提升性能。
