MySQL中一些概念2
1.join的使用
如果使用join,优化器将自己选择驱动表;使用straight_join,左边的为驱动表。
NLJ(index nested loop join):从驱动表中读一行(全表扫描),按照join条件获取索引到被驱动表中取出一行(索引查找,如果是非主键索引还有一次回表),将俩行组成结果集。此时让大表作为被驱动较好,能使用上被驱动表的索引是关键。
注:全表扫描就是走主键索引
(simple nested loop join):被驱动表没有join条件中的索引,总扫描行数为M*N,量级太大。(实际上使用BNL)
Block Nested-Loop JoinBNL(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同步时候自增值可能会混乱。