MySQL中一些概念2

1.join的使用

如果使用join,优化器将自己选择驱动表;使用straight_join,左边的为驱动表。

NLJ(index nested loop join):从驱动表中读一行(全表扫描),按照join条件获取索引到被驱动表中取出一行(索引查找,如果是非主键索引还有一次回表),将俩行组成结果集。此时让大表作为被驱动较好,能使用上被驱动表的索引是关键。

注:全表扫描就是走主键索引

(simple nested loop join):被驱动表没有join条件中的索引,总扫描行数为M*N,量级太大。(实际上使用BNL)

Block Nested-Loop Join

BNL(block nested loop join):被驱动表不能使用索引,将驱动表全表扫描出来放到join buffer,扫描被驱动表每一行和join buffer中所有行比较,返回结果。总扫描行数为M+N,比较次数还是M*N,比较是在内存的速度快。

问题是:join buffer放不下。此时分段放入buffer,循环k次以上流程。扫描行数为N+k*M,比较次数不变,其中k和N线性相关,因此小表作为驱动表更好。

注意:小表是指满足where条件的行数、总数据量更少的表。

2.MRR

非主键索引有序,主键索引随机->从非主键读出行数据放入read_rnd_buffer中,将buffer中的主键id按照递增排序,再去主键索引中查找完整的行记录。

3.join查询的优化

有了MRR,NLJ可以优化为BKA(batched key access):从驱动表中多拿几行出来,放入临时内存join_buffer中,按照被驱动表需要查询的索引有序为目标进行排序,顺序读出多行记录。

BNL的问题:驱动表多次扫描,同一行数据相隔超过1s再次被扫描到,被放入young区,使得正常查询的内存命中率低;前提是:驱动表的数据量小于old区域buffer的大小(3/8)。问题2:驱动表太大,一直往old区域的头处插入,同时不断从尾端淘汰行数据,导致正常查询的不能在old区待够1s。

可以在被驱动表上添加索引,使得BNL算法改良为BKA算法;如果索引代价不值得,使用临时表:将被驱动表(大量行)筛选后的数据(少量行)放入临时表,给临时表join条件中的字段加索引,让驱动表和临时表做join操作,就使用了BKA算法。

mysql本身没有实现,可以在业务端实现的方法:使用hash join。

4.临时表

临时表不是内存表,可以使用innodb或myisam等其他引擎,必须使用engine=memory才是内存表。

临时表只能被创建他的线程访问(所以线程结束后临时表自动删除),其他线程不可见,临时表可以和普通表同名,同名时候优先访问临时表(curd),show tables不显示临时表。

临时表的名字由进程id,线程id,序列号等组成,不会冲突。

每个线程都管理一个临时表的链表。

以上是手动创建的临时表,还有自动创建的内部临时表:例如使用union语句,会把其中一个查询放到临时表中缓存,因为需要做对比,所以后台自动创建了一个临时表。

5.内存表

数据和索引分开存放,索引是hash结构,不是有序的。因此全表扫描不走主键索引,直接扫描数据,不能保证有序,就是按照写入的顺序存放的。把内存表数据看做一个数组即可。

内存表也支持B树索引,可以范围查询。

内存表锁的粒度较大,只支持表级锁。和MDL不同,不是DDL操作也会锁住整张表。

bug:重启直接丢失数据,如果binlog再记录一条delete from t1,从备库传到主库,主库正常使用的内存表都被清空了。

6.自增主键

自增主键不是连续的。

对innodb引擎,在5.7及之前的版本,放在内存,每次重启扫描现有的最大值,+1作为自增值。在8.0及之后的版本,自增值记录在redo log 中,依靠redo log恢复。

实际使用:以语句中指定的和系统中维护的较大的值为新的自增值插入,如果语句中的大于系统中维护的,为保持之后的一致性,系统中维护的也要增大。可以设置系统维护的自增值每次增加的步长,例如在主备情况下可以设置为2,让一个始终为奇数一个始终为偶数避免冲突。

系统维护的自增值的修改操作早于DML操作,例如插入失败,维护的自增值不会回退了。为什么不回退呢?多事务并发场景,回退可能冲突,而判断不冲突的代价太大,索性不回退。或者自增值上的锁直到事务结束才解开,这样粒度太大了不值得。

statement格式的binlog日志只能原样记录语句,如果语句中没有显式指定自增值,在并发情况下,主备库之间通过bin log同步时候自增值可能会混乱。