成都专业网站搭建公司,网络设计报告书,网站建设制作费用,网站建设存在的问题本笔记为以前整理的零碎的关于Mysql的知识点#xff0c;有深入源码的也有浅层的八股。已经被我整理成了一个pdf。 实习岗位正好也是和数据库内核有关的#xff0c;之后应该还会更新。做个整理#xff0c;方便秋招的时候快速回顾吧。 链接#xff1a;链接 提取码#xff1a…本笔记为以前整理的零碎的关于Mysql的知识点有深入源码的也有浅层的八股。已经被我整理成了一个pdf。 实习岗位正好也是和数据库内核有关的之后应该还会更新。做个整理方便秋招的时候快速回顾吧。 链接链接 提取码1234 目录《DBNotesBuffer Pool刷脏页细节以及改进》获取一个空闲页的源码逻辑Page_Cleaner_ThreadLRU_Manager_ThreadHazard Pointer作为驱逐算法改进参考《DBNotesJoin算法的前世今生》NestLoopJoin算法Simple Nested-Loop JoinIndex Nested-Loop JoinBlock Nested-Loop JoinBatched Key AccessHash Join算法In-Memory Join(CHJ)On-Disk Hash Join参考链接《DBNotes_ Buffer Pool对于缓冲页的链表式管理》Buffer Pool回顾Buffer Pool内部组成freelistflushlistLRU链表管理以及改进《DBNotes_single_table访问方法、MRR多范围读取优化、索引合并》single_table访问方法constrefref_or_nullrangeindexallMRR多范围读取优化索引合并intersectionunionsort-union《MySQL8.0.22Lock(锁)知识总结以及源码分析》1、关于锁的一些零碎知识需要熟知事务加锁方式Innodb事务隔离MVCC多版本并发控制常用语句 与 锁的关系意向锁行级锁2、锁的内存结构以及一些解释3、InnoDB的锁代码实现锁系统结构lock_sys_tlock_t 、lock_rec_t 、lock_table_tbitmap锁的基本模式的兼容关系和强弱关系行锁类别代码记录锁的alloc函数记录锁的add函数记录锁的create函数4、锁的流程表锁加锁流程行锁加锁流程插入加锁流程删除加锁流程带来的死锁释放锁流程死锁流程5、参考count()用法《MySQL——join语句优化tips》要不要用joinJoin驱动表选择Multi-Range Read优化Batched Key Access BKA对NLJ进行优化BNL算法性能问题BNL转BKA《MySQL——redo log 与 binlog 写入机制》binlog写入机制redo log写入机制组提交机制实现大量的TPS理解WAL机制如何提升IO性能瓶颈《MySQL——备库多线程复制策略》备库并行复制能力MySQL5.6版本 并行复制策略MariaDB 并行复制策略MySQL5.7版本 并行复制策略MySQL5.7.22版本 并行复制策略总结《MySQL——查询长时间不返回的三种原因与查询慢的原因》查询长时间不返回等MDL锁等flush等行锁查询慢幻读现象幻读带来的问题如何解决幻读next-key lock临时表的应用临时表可以重名的原因临时表的主备同步覆盖索引优化查询思考事务事务的必要性MySQL中如何控制事务手动开启事务事务的四大特征事务的四大特征事务开启方式事务手动提交与手动回滚事务的隔离性脏读现象不可重复读现象幻读现象串行化一些补充使用长事务的弊病commit work and chain的语法是做什么用的?怎么查询各个表中的长事务如何避免长事务的出现?事务隔离是怎么通过read-view(读视图)实现的参考索引回表覆盖索引最左前缀原则联合索引的时候如何安排索引内的字段顺序索引下推重建索引问题联合主键索引和 InnoDB 索引组织表问题in与between的区别表锁是什么表锁有什么用表锁怎么用行锁是什么行锁有什么用行锁怎么用死锁与死锁检测何时会死锁检测如何避免高量级的死锁检测练习主备一致性备库为什么要设置为只读模式备库设置为只读如何与主库保持同步更新A到B的内部流程如何binlog内容是什么row格式对于恢复数据有何好处M-M结构的循环复制问题以及解决方案关于查询能力关于change buffer关于写能力基于change bufferMySQL索引底层原理理解以及常见问题总结二叉查找树为索引红黑树为索引B树作为索引B树作为索引MyISAM存储引擎索引实现InnoDB存储引擎索引实现常见问题聚集索引与非聚集索引InnoDB基于主键索引和普通索引的查询有什么区别InnoDB主键索引为何是整型的自增主键何时使用业务字段作为主键呢哈希与B树“N叉树”的N值在MySQL中是可以被人工调整的么 《DBNotesBuffer Pool刷脏页细节以及改进》
本笔记知识沿用之前DBNotes: Buffer Pool对于缓冲页的链表式管理的部分知识
获取一个空闲页的源码逻辑
任何一个读写请求都需要从Buffer pool来获取所需页面。如果需要的页面已经存在于Buffer pool那么直接利用当前页面进行操作就行。但是如果所需页面不在Buffer pool比如UPDATE操作那么就需要从Buffer pool中新申请空闲页面将需要读取的数据放到Buffer pool中进行操作。
如何从buffer pool中获取一个页面呢这依赖于buf_LRU_get_free_block函数该函数会循环尝试去淘汰LRU list上的页面。每次循环都会访问freelist查看是否有足够的空闲页面如果没有就继续从LRUlist去淘汰。这样的循环在负载较高的时候会加剧对freelist以及LRUlist的mutex的竞争。可以设置buf_pool-try_LRU_scan是做了一个优化如果当前用户线程扫描的时候 发现没有空闲页面那么其他用户线程就不需要进行同样的扫描。
MySQL的free页面的获取依赖于Page_Cleaner_Thread的刷新能力如果刷新不及时那么系统就会使用上面所说的循环逻辑来为用户线程申请空闲页面可以看出是十分耗时间的。而如果刷新过快也会导致性能问题因为刷新是需要io操作的。
所以引入独立的线程负责LRU list的刷脏。目的是为了让独立线程根据系统负载动态调整LRU的刷脏能力。由于LRU list的刷脏从page cleaner线程中脱离出来调整LRU list的刷脏能力不再会影响到page cleaner。
同时由于单线程LRUlist刷脏存在问题设计者进行了改进。继续将LRU list独立于page cleaner threads并将LRU list单线程刷脏增加为多线程刷脏。page cleaner只负责flush list的刷脏lru_manager_thread只负责LRU List刷脏。这样的分离可以使得LRU list刷脏和Flush List刷脏并行执行。
Page_Cleaner_Thread
主要负责flushlist的刷脏避免用户线程同步刷脏页。
也是每隔一定时间刷一次脏页sleep time是自适应的依赖于当前的lsnflushlist中的oldest_modification以及当前的同步刷脏点。
与LRU_Manager_Thread不同该线程每次执行刷的脏页数量也是自适应的依赖于当前系统中脏页的比率日志产生的速度以及几个参数。
LRU_Manager_Thread
一个系统线程随着InnoDB启动而work作用是定期清理出空闲的数据页数量为innodb_LRU_scan_depth并加入到Freelist中防止用户线程去做同步刷脏影响效率。
该线程每隔一段时间就去FLUSH。先尝试从LRU中驱逐部分数据页如果数量不够就从Flushlist中驱逐。
线程执行频率是自适应的
设定max_free_len innodb_LRU_scan_depth * innodb_buf_pool_instances。
如果Freelist中的数量小于max_free_len 的1%则sleep time 0表示这时候空闲页太少了需要一直执行buf_flush_LRU_tail操作从而腾出空闲的数据页。
如果Free List中的数量介于max_free_len的1%-5%则sleep time减少50ms(默认为1000ms)如果Free List中的数量介于max_free_len的5%-20%则sleep time不变如果Free List中的数量大于max_free_len的20%则sleep time增加50ms但是最大值不超过rds_cleaner_max_lru_time。
Hazard Pointer作为驱逐算法改进
在学术上Hazard Pointer是一个指针如果这个指针被一个线程所占有在它释放之前其他线程不能对他进行修改但是在InnoDB里面概念刚好相反一个线程可以随时访问Hazard Pointer但是在访问后他需要调整指针到一个有效的值便于其他线程使用。我们用Hazard Pointer来加速逆向的逻辑链表遍历。 先来说一下这个问题的背景我们知道InnoDB中可能有多个线程同时作用在Flush List上进行刷脏例如LRU_Manager_Thread和Page_Cleaner_Thread。同时为了减少锁占用的时间InnoDB在进行写盘的时候都会把之前占用的锁给释放掉。这两个因素叠加在一起导致同一个刷脏线程刷完一个数据页A就需要回到Flush List末尾(因为A之前的脏页可能被其他线程给刷走了之前的脏页可能已经不在Flush list中了)重新扫描新的可刷盘的脏页。另一方面数据页刷盘是异步操作在刷盘的过程中我们会把对应的数据页IO_FIX住防止其他线程对这个数据页进行操作。我们假设某台机器使用了非常缓慢的机械硬盘当前Flush List中所有页面都可以被刷盘(buf_flush_ready_for_replace返回true)。我们的某一个刷脏线程拿到队尾最后一个数据页IO fixed发送给IO线程最后再从队尾扫描寻找可刷盘的脏页。在这次扫描中它发现最后一个数据页(也就是刚刚发送到IO线程中的数据页)状态为IO fixed(磁盘很慢还没处理完)所以不能刷跳过开始刷倒数第二个数据页同样IO fixed发送给IO线程然后再次重新扫描Flush List。它又发现尾部的两个数据页都不能刷新(因为磁盘很慢可能还没刷完)直到扫描到倒数第三个数据页。所以存在一种极端的情况如果磁盘比较缓慢刷脏算法性能会从O(N)退化成O(N*N)。 要解决这个问题最本质的方法就是当刷完一个脏页的时候不要每次都从队尾重新扫描。我们可以使用Hazard Pointer来解决方法如下遍历找到一个可刷盘的数据页在锁释放之前调整Hazard Pointer使之指向Flush List中下一个节点注意一定要在持有锁的情况下修改。然后释放锁进行刷盘刷完盘后重新获取锁读取Hazard Pointer并设置下一个节点然后释放锁进行刷盘如此重复。当这个线程在刷盘的时候另外一个线程需要刷盘也是通过Hazard Pointer来获取可靠的节点并重置下一个有效的节点。通过这种机制保证每次读到的Hazard Pointer是一个有效的Flush List节点即使磁盘再慢刷脏算法效率依然是O(N)。 这个解法同样可以用到LRU List驱逐算法上提高驱逐的效率。
参考 MySQL · 源码分析 · Innodb缓冲池刷脏的多线程实现 MySQL · 源码分析 · InnoDB LRU List刷脏改进之路 MySQL · 引擎特性 · InnoDB Buffer Pool 《DBNotesJoin算法的前世今生》
在8.0.18之前MySQL只支持NestLoopJoin算法最简单的就是Simple NestLoop JoinMySQL针对这个算法做了若干优化实现了Block NestLoop JoinIndex NestLoop Join和Batched Key Access等有了这些优化在一定程度上能缓解对HashJoin的迫切程度。但是HashJoin的支持使得MySQL优化器有更多选择SQL的执行路径也能做到更优尤其是对于等值join的场景。
NestLoopJoin算法
长期以来在MySQL中执行联接的唯一算法是嵌套循环算法的变体。
Simple Nested-Loop Join
如果我们执行这样一条等值查询语句
select * from t1 straight_join t2 on (t1.at2.b);由于表 t2 的字段 b 上没有索引每次到 t2 去匹配的时候就要做一次全表扫描。就相当于是双for循环。如果 t1 和 t2 都是 10 万行的表当然了这也还是属于小表的范围就要扫描 100 亿行。 SimpleNestLoopJoin显然是很低效的对内表需要进行N次全表扫描实际复杂度是N*MN是外表的记录数目M是记录数代表一次扫描内表的代价。为此MySQL针对SimpleNestLoopJoin做了若干优化。
Index Nested-Loop Join
如果我们能对内表的join条件建立索引那么对于外表的每条记录无需再进行全表扫描内表只需要一次Btree-Lookup即可整体时间复杂度降低为N*O(logM)。 再来看看这一句
select * from t1 straight_join t2 on (t1.at2.a);在这条语句里被驱动表 t2 的字段 a 上有索引join 过程用上了这个索引因此这个语句的执行流程是这样的 执行流程示意图如下 对比HashJoin对于外表每条记录HashJoin是一次HashTable的search当然HashTable也有build时间还需要处理内存不足的情况不一定比INLJ好。
Block Nested-Loop Join
MySQL采用了批量技术即一次利用join_buffer_size缓存足够多的记录每次遍历内表时每条内表记录与这一批数据进行条件判断这样就减少了扫描内表的次数如果内表比较大间接就缓解了IO的读压力。 Simple Nested-Loop Join 与 Block Nested-Loop Join从时间复杂度上来说这两个算法是一样的。但是Block Nested-Loop Join是内存操作速度上会快很多性能也更好。 示意图如下
Batched Key Access
IndexNestLoopJoin利用join条件的索引通过Btree-Lookup去匹配减少了遍历内表的代价。如果join条件是非主键列那么意味着大量的回表和随机IO。BKA优化的做法是将满足条件的一批数据按主键排序这样回表时从主键的角度来说就相对有序缓解随机IO的代价。BKA实际上是利用了MRR特性(MultiRangeRead)访问数据之前先将主键排序然后再访问。主键排序的缓存大小通过参数read_rnd_buffer_size控制。
Hash Join算法
NestLoopJoin算法简单来说就是双重循环遍历外表(驱动表)对于外表的每一行记录然后遍历内表然后判断join条件是否符合进而确定是否将记录吐出给上一个执行节点。从算法角度来说这是一个M*N的复杂度。HashJoin是针对equal-join场景的优化基本思想是将外表数据load到内存并建立hash表这样只需要遍历一遍内表就可以完成join操作输出匹配的记录。如果数据能全部load到内存当然好逻辑也简单一般称这种join为CHJ(Classic Hash Join)之前MariaDB就已经实现了这种HashJoin算法。如果数据不能全部load到内存就需要分批load进内存然后分批join下面具体介绍这几种join算法的实现。
In-Memory Join(CHJ)
HashJoin一般包括两个过程创建hash表的build过程和探测hash表的probe过程。 1).build phase 遍历外表以join条件为key查询需要的列作为value创建hash表。这里涉及到一个选择外表的依据主要是评估参与join的两个表(结果集)的大小来判断谁小就选择谁这样有限的内存更容易放下hash表。 2).probe phase hash表build完成后然后逐行遍历内表对于内表的每个记录对join条件计算hash值并在hash表中查找如果匹配则输出否则跳过。所有内表记录遍历完则整个过程就结束了
On-Disk Hash Join
CHJ的限制条件在于要求内存能装下整个外表。在MySQL中Join可以使用的内存通过参数join_buffer_size控制。如果join需要的内存超出了join_buffer_size那么CHJ将无能为力只能对外表分成若干段每个分段逐一进行build过程然后遍历内表对每个分段再进行一次probe过程。假设外表分成了N片那么将扫描内表N次。这种方式当然是比较弱的。
在MySQL8.0中如果join需要内存超过了join_buffer_sizebuild阶段会首先利用hash算将外表进行分区并产生临时分片写到磁盘上然后在probe阶段对于内表使用同样的hash算法进行分区。由于使用分片hash函数相同那么key相同(join条件相同)必然在同一个分片编号中。接下来再对外表和内表中相同分片编号的数据进行CHJ的过程所有分片的CHJ做完整个join过程就结束了。这种算法的代价是对外表和内表分别进行了两次读IO一次写IO。相对于之之前需要N次扫描内表IO现在的处理方式更好。 顺序为外表的分片、内表分片、哈希连接
参考链接 join语句怎么优化 MySQL8.0 新特性 Hash Join 哈希加入MySQL 8 MySQL · 新特征 · MySQL 哈希连接实现介绍 《DBNotes_ Buffer Pool对于缓冲页的链表式管理》
Buffer Pool回顾
我们知道针对数据库的增删改删操作都是在Buffer Pool中完成的一条sql的执行步骤可以认为是这样的 1、innodb存储引擎首先在缓冲池中查询有没有对应的数据有就直接返回 2、如果不存在则去磁盘进行加载并加入缓冲池 3、同时该记录会被加上独占锁防止多人修改出现数据不一致 而且我们知道可以通过设置my.cnf配置中的innodb_buffer_pool_size来修改缓冲池大小加快sql查询速度当然也需要注意设置过大会造成系统swap空间被占用导致系统变慢降低查询性能。
Buffer Pool内部组成
缓冲池对应一片连续内存我们将其划分为大小为16kb的页(与innodb对应)这些页称为缓冲页。 为了很好的管理这些页设计者为每个缓冲页都创建了一些控制信息表空间编号、页号、缓冲页在缓冲池中的地址、链表节点信息等。将每个页对应的控制信息占用的一块内存称为一个控制块。控制块与缓冲页一一对应都存放在缓冲池中。 在Mysql启动时会自己完成对缓冲池的初始化向操作系统申请内存自己划分成若干对控制块和缓冲页。
freelist
当我们从磁盘中load一个数据页到缓冲池中我们应该放到哪个缓冲页中呢 很显然我们应该把数据页放到“空闲”的缓冲页中。 设计者将所有空闲的缓冲页对应的控制块作为一个节点放到一个链表中称为freelist。每次从freelist中取出一个空闲的缓冲页中并且将该缓冲页对应的控制块信息填上然后将该节点移除表示缓冲页已经被使用了
flushlist
当一个控制块节点被从freelist中移除说明该页已经被使用了。如果这种“使用操作”是对数据进行修改的话那么必定需要将该页数据flush到磁盘上。但是每次修改一页就将那一页flush的话磁盘IO占用率高。所以每次修改缓冲页后将这些脏页控制块放入一个fulshlist上。当flush时机到了,就把flushlist节点对应的缓冲页刷新搭配磁盘上。
LRU链表管理以及改进
缓冲池内存有限当freelist中没有多余的空闲缓冲页就需要把某些旧的缓冲页从缓冲池中移除然后把新的数据页放进来。为了提高内存命中率使用LRU。 但是普通的LRU不能解决下面的问题; 1、加载到缓冲池的页不一定被用到针对于预读 2、如果有非常多的使用频率低的页被同时加载到缓冲池中则可能会把那些使用频率非常高的页从缓冲池中淘汰。针对全表扫描 关于innodb对于LRU的改进见如链接 MySQL——Innodb改进LRU算法 当然还有进一步的优化 对于young区域的缓冲页每次访问一个缓冲页就要把它移动到LRU链表的头部开销比较大。毕竟young区域的缓冲页都是热点数据。所以我们可以这样优化只有被访问的缓冲页位于young区域1/4的后面时才会被移动到LRU链表头部。也就是说我们将young的前0.25部分称为very youngvery young里面的数据访问不会移动到头部因为大家访问频率都是非常高的。 提醒一下在LRUlist的节点不是freelist节点可能是flushlist节点。不理解的话再去上面看看两个list定义。 然而这一切的目的只有一个尽量高效地提高缓冲池命中率。
《DBNotes_single_table访问方法、MRR多范围读取优化、索引合并》
single_table访问方法
const
在主键列或者unique二级索引与一个常数进行等值比较时才有效。 如果主键或者unique二级索引的索引列由多个列构成则只有在索引列中的每个列都与常数进行等值比较时才是const访问
ref
搜索条件为二级索引非unique与常数进行等值比较形成的扫描区间为单点扫描区间即【‘abc’,‘abc’】采用二级索引来执行查询的访问方法为ref。注意采用二级索引执行查询时每获取到一条二级索引记录就会进行一次回表操作。 TIPS
二级索引列允许存储NULL值时不限制NULL值的数量所以执行key is NULL查询时最优只能执行ref操作索引列中包含多个列的二级索引时只要最左边连续的列是与常数进行等值比较就可以使用ref访问。
ref_or_null
当想找出某个二级索引列的值等于某个常数的记录并且将该列中值为NULL的记录也找出来
select * from single_table where key1 abc or key1 is null;若使用二级索引此时的扫描区间为[‘abc’,‘abc’] 以及[NULL,NULL]。 这种访问方法即为ref_or_null。
range
select * from single_table where key2 IN (1438,6328) OR (key2 38 AND key2 79);使用二级索引扫描区间为[1438,1438] 、[6328,6328]、[38,79]改扫描区间为若干个单点扫描区间或者范围扫描区间。访问方法为range。当然(-无穷无穷)不为range访问方法。
index
key_part1,key_part2,key_part3 为二级索引它们三个构成了一个联合索引并且key_table2并不是联合索引的最左列。
select key_part1,key_part2,key_part3 from single_table where key_table2 abc; 此时无法形成合适的范围区间来减少扫描的记录数量。 需要注意此时的查询符合两个条件
查询列表中key_part1,key_part2,key_part3都包含在联合索引中搜索条件只有key_part2这个列也包含在联合索引中 很显然需要扫描全部的联合索引扫描区间为[-无穷无穷]。由于二级索引记录只有存放索引列和主键也不需要回表所以此时扫描去不的二级索引记录比直接扫描全部的聚集索引记录成本要小。这种方法称为index访问。
又如
select * from single_table order by id;通过全表扫描对表进行查询时有order by。此时也是使用index方法。
all
全表扫描直接扫描全部的聚集索引记录。
MRR多范围读取优化
select * from single_table where key1 abc and key2 1000; 该语句的执行步骤 1、通过key1的索引定位扫描区间[‘abc’,‘abc’]; 2、根据上面得到的主键值回表得到完整用户记录然后检测记录是否满足key2 1000的条件满足则返回 3、重复2步骤直到不满足key1 ‘abc’ 每次从二级索引中读取到一条记录后就会根据该记录的主键值执行回表操作。 而某个扫描区间中的二级索引记录的主键值是无序的每次回表都会随机读取一个聚集索引页面带来的IO开销较大。 MRR会先读取一部分二级索引记录将它们的主键值排序后再同意执行回表操作节省IO开销。
索引合并
intersection
使用多个索引完成一次查询的执行方法称为索引合并
select * from single_table where key1 a and key3 b;可以先搜key1的索引然后回表根据key3条件筛选。 也可以先搜key1的索引然后回表根据key1条件筛选。 当然可以同时使用key1和key2的索引。在key1索引中扫描key1值得到区间[‘a’,‘a’]在key3索引中扫描key3值得到区间[‘b’,‘b’] 然后从两者操作结果中找到id列值相同的记录。然后根据共有的id值执行回表这样可能会省下回表操作带来的开销。 当然需要注意的是要求从不同二级索引中获取到的二级索引记录都按照主键值排好序
从两个有序集合中取交集比两个从无序集合中取交集要容易如果获取到的id值有序排列则在根据这些id值执行回表操作时不再是进行单纯的随机IO就会提高效率。
如果从扫描区间中获得的记录并不是按照主键值排序的那么就不能使用intersection索引合并。
union
select * from single_table where key1 a or key3 b;
同时使用key1和key2的索引。在key1索引中扫描key1值得到区间[‘a’,‘a’]在key3索引中扫描key3值得到区间[‘b’,‘b’] 然后对两个结果进行去重对去重后的id值进行回表操作。 同样二级索引记录都是要按照主键值排序如果从扫描区间中获得的记录并不是按照主键值排序的那么就不能使用union索引合并。
sort-union
union索引合并条件苛刻下面的查询就不能使用union索引合并
select * from single_table where key1 a or key3 z;
我们可以这样操作; 1、根据key1a’条件从key1的二级索引中获取记录并将获取到的记录的主键值排序 2、根据key3z’条件从key3的二级索引中获取记录并将获取到的记录的主键值排序 3、按照union操作两个记录合并 sort-union 索引合并比union索引合并多了一步对二级索引记录的主键值进行排序。
《MySQL8.0.22Lock(锁)知识总结以及源码分析》
1、关于锁的一些零碎知识需要熟知
事务加锁方式
两阶段锁
整个事务分为两个阶段前一个阶段加锁后一个阶段为解锁。在加锁阶段事务只能加锁也可以操作数据但是不能解锁直到事务释放第一个锁就进入了解锁阶段此阶段事务只能解锁也可以操作数据不能再加锁。
两阶段协议使得事务具有比较高的并发度因为解锁不必发生在事务结尾。
不过它没有解决死锁问题因为它在加锁阶段没有顺序要求如果两个事务分别申请了A,B锁接着又申请对方的锁此时进入死锁状态。
Innodb事务隔离
在MVCC并发控制中读操作可以分为两类快照读和当前读。
快照读读取的是记录的可见版本有可能是历史版本不用加锁。
当前读读取的是记录的最新版本并且当前读返回的记录都会加上锁保证其他事务不再会并发修改这条记录。
Read Uncommited可以读未提交记录Read CommittedRC当前读操作保证对独到的记录加锁存在幻读现象。使用MVCC,但是读取数据时读取自身版本和最新版本以最新为主可以读已提交记录存在不可重复Repeatable ReadRR当前读操作保证对读到的记录加锁同时保证对读取的范围加锁新的满足查询条件的记录不能够插入间隙锁不存在幻读现象。使用MVCC保存两个事务操作的数据互相隔离不存在不可重复读现象。SerializableMVCC并发控制退化为基于锁的并发控制。不区分快照读和当前读所有读操作均为当前读读加S锁写加X锁。
MVCC多版本并发控制
MVCC是一种多版本并发控制机制。锁机制可以控制并发操作但是其系统开销较大而MVCC可以在大多数情况下替代行级锁降低系统开销。
MVCC是通过保存数据在某个时间点的快照来实现的典型的有乐观并发控制和悲观并发控制。
InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的这两个列分别保存这个行的创建时间和删除时间这里存储的并不是实际的时间值而是版本号可以理解为事务的ID。每开始一个新的事务这个版本号就会自动递增。
对于几种的操作
INSERT:为新插入的每一行保存当前版本号作为版本号UPDATE新插入一行记录并且保存其创建时间为当前事务ID同时保存当前DELETE为删除的每一行保存当前版本号作为版本号SELECT InnoDB只会查找版本号小于等于事务系统版本号行的删除版本要么未定义要么大于当前事务版本号这样可以确保事务读取的行在事务开始删除前未被删除
事实上在读取满足上述两个条件的行时InnoDB还会进行二次检查。
活跃事务列表RC隔离级别下在语句开始时从全局事务表中获取活跃未提交事务构造Read ViewRR隔离级别下事务开始时从全局事务表获取活跃事务构造Read View
1、取当前行的修改事务ID和Read View中的事务ID做比较若小于最小的ID或小于最大ID但不在列表中转2步骤。若是大于最大ID转3
2、若进入此步骤可说明最后更新当前行的事务在构造Read View时已经提交返回当前行数据
3、若进入此步骤可说明最后更新当前行的事务在构造Read View时还未创建或者还未提交取undo log中记录的事务ID重新进入步骤1.
根据上面策略在读取数据的时候InnoDB几乎不用获得任何锁每个查询都能通过版本查询只获得自己需要的数据版本从而大大提高了系统并发度。
缺点是每行记录都需要额外的存储空间更多的行检查工作额外的维护工作。
一般我们认为MVCC有几个特点
每个数据都存在一个版本每次数据更新时都更新该版本修改时copy出当前版本修改各个事务之间没有干扰保存时比较版本号如果成功则覆盖原记录失败则rollback
看上去保存是根据版本号决定是否成功有点乐观锁意味但是Innodb实现方式是
事务以排他锁的形式修改原始数据把修改前的数据存放于undo log通过回滚指针与主数据关联修改成功后啥都不做失败则恢复undo log中的数据。
innodb没有实现MVCC核心的多版本共存undo log内容只是串行化的结果记录了多个事务的过程不属于多版本共存。当事务影响到多行数据理想的MVCC无能为力。
如事务1执行理想MVCC修改row1成功修改row2失败此时需要回滚row1但是由于row1没有被锁定其数据可能又被事务2修改如果此时回滚row1内容会破坏事务2的修改结果导致事务2违反ACID。
理想的MVCC难以实现的根本原因在于企图通过乐观锁代替二阶段提交。修改两行数据但为了保证其一致性与修改两个分布式系统数据并无区别而二阶段提交是目前这种场景保证一致性的唯一手段。二阶段提交的本质是锁定乐观锁的本质是消除锁定二者矛盾。innodb只是借了MVCC名字提供了读的非阻塞。
采用MVCC方式读-写操作彼此并不冲突性能更高如果采用加锁方式读-写操作彼此需要排队执行从而影响性能。一般情况下我们更愿意使用MVCC来解决读-写操作并发执行的问题但是在一些特殊业务场景中要求必须采用加锁的方式执行。
常用语句 与 锁的关系
对读取的记录加S锁
select ... lock in share mode;
对读取的记录加X锁
select ... for update;
delete:
对一条语句执行delete先在B树中定位到这条记录位置然后获取这条记录的X锁最后执行delete mark操作。
update
如果未修改该记录键值并且被更新的列所占用的存储空间在修改前后未发生变化则现在B树定位到这条记录的位置然后再获取记录的X锁最后在原记录的位置进行修改操作。如果为修改该记录的键值并且至少有一个被更新的列占用的存储空间在修改后发生变化则先在B树中定位到这条记录的位置然后获取记录的X锁然后将原记录删除再重新插入一个新的记录。如果修改了该记录的键值则相当于在原记录上执行delete操作之后再来一次insert操作。
insert
新插入的一条记录收到隐式锁保护不需要在内存中为其生成对应的锁结构。
意向锁
为了允许行锁和表锁共存实现多粒度锁机制。InnoDB还有两种内部使用的意向锁两种意向锁都是表锁。
意向共享锁(IS):事务打算给数据行加行共享锁事务在给一个数据行加共享锁前必须先取得该表的IS锁
意向排他锁(IX):事务打算给数据行加排他锁事务在给一个数据行加排他锁前必须先取得该表的IX锁。 意向锁仅仅用于表锁和行锁的共存使用。它们的提出仅仅是为了在之后加表级S锁或者X锁是可以快速判断表中的记录是否被上锁以避免用遍历的方式来查看表中有没有上锁的记录。 需要注意的三点 1、意向锁是表级锁但是却表示事务正在读或写某一行记录 2、意向锁之间不会冲突因为意向锁仅仅代表对某行记录进行操作在加行锁的时候会判断是否冲突 3、意向锁是InnoDB自动加的不需要用户干预。
行级锁 Record Lock:就是普通的行锁官方名称LOCK_REC_NOT_GAP用来锁住在聚集索引上的一条行记录 Gap Lock:用来在可重复读隔离级别下解决幻读现象。已知幻读还有一种方法解决MVCC还一种就是加锁。但是在使用加锁方案时有个问题事务在第一次执行读取操作时“幻影记录”还没有插入所以我们无法给“幻影记录”加上Record Lock。InnoDB提出了Gap锁官方名称LOCK_GAP,若一条记录的numberl列为8前一行记录number列为3我们在这个记录上加上gap锁意味着不允许别的事务在number值为38区间插入记录。只有gap锁的事务提交后将gap锁释放掉后其他事务才能继续插入。 注意gap锁只是用来防止插入幻影记录的共享gap和独占gap起到作用相同。对一条记录加了gap锁不会限制其他事务对这条记录加Record Lock或者继续加gap锁。另外对于向限制记录后面的区间的话可以使用Supremum表示该页面中最大记录。 Next-Key Lock:当我们既想锁住某条记录又想阻止其他事务在该记录前面的间隙插入新记录使用该锁。官方名称LOCK_ORDINARY,本质上就是上面两种锁的结合。 Insert Intention Lock一个事务在插入一条你记录时需要判断该区间点上是否存在gap锁或Next-Key Lock如果有的话插入就需要阻塞。设计者规定事务在等待时也需要在内存中生成一个锁结构表明有个事务想在某个间隙中插入记录但是处于等待状态。这种状态锁称为Insert Intention Lock官方名称LOCK_INSERT_INTENTION,也可以称为插入意向锁。
2、锁的内存结构以及一些解释
一个事务对多条记录加锁时不一定就要创建多个锁结构。如果符合下面条件的记录的锁可以放到一个锁结构中
在同一个事务中进行加锁操作被加锁的记录在同一个页面中加锁的类型是一样的等待状态是一样的 type_mode是一个32位比特的数被分为lock_mode、lock_type、rec_lock_type三个部分。 低4位表示lock_mode锁的模式 0表示IS锁 1表示IX锁 2表示S锁 3表示X锁 4表示AI锁就是auto-inc自增锁 第5~8位表示lock_type锁的类型 LOCK_TABLE第5位为1表示表级锁 LOCK_REC第6位为1表示行级锁 其余高位表示rec_lock_type表示行锁的具体类型只有lock_type的值为LOCK_REC时才会出现细分 LOCK_ORDINARY:为0表示next-key锁 LOCK_GAP为512即当第10位设置为1时表示gap锁 LOCK_REC_NOT_GAP为1024当第11位设置为1表示正常记录锁 LOCK_INSERT_INTENTION为2048当第12位设置为1时表示插入意向锁 LOCK_WAIT为256当第9位设置为1时表示is_waiting为false表明当前事务获取锁成功。 一堆比特位 其他信息涉及了一些哈希表和链表 更加细节的结构可以看这一张图
3、InnoDB的锁代码实现
锁系统结构lock_sys_t
详细讲解见https://dev.mysql.com/doc/dev/mysql-server/latest/structlock__sys__t.html#details 锁系统结构在innodb启动的时候初始化在innodb结束时释放。保存锁的hash表相关事务、线程的一些信息
/** The lock system struct */
struct lock_sys_t {/** The latches protecting queues of record and table locks */locksys::Latches latches;/** The hash table of the record (LOCK_REC) locks, except for predicate(LOCK_PREDICATE) and predicate page (LOCK_PRDT_PAGE) locks */hash_table_t *rec_hash;/** The hash table of predicate (LOCK_PREDICATE) locks */hash_table_t *prdt_hash;/** The hash table of the predicate page (LOCK_PRD_PAGE) locks */hash_table_t *prdt_page_hash;/** Padding to avoid false sharing of wait_mutex field */char pad2[ut::INNODB_CACHE_LINE_SIZE];/** The mutex protecting the next two fields */Lock_mutex wait_mutex;/** Array of user threads suspended while waiting for locks within InnoDB.Protected by the lock_sys-wait_mutex. */srv_slot_t *waiting_threads;/** The highest slot ever used in the waiting_threads array.Protected by lock_sys-wait_mutex. */srv_slot_t *last_slot;/** TRUE if rollback of all recovered transactions is complete.Protected by exclusive global lock_sys latch. */bool rollback_complete;/** Max lock wait time observed, for innodb_row_lock_time_max reporting. */ulint n_lock_max_wait_time;/** Set to the event that is created in the lock wait monitor thread. A valueof 0 means the thread is not active */os_event_t timeout_event;#ifdef UNIV_DEBUG/** Lock timestamp counter, used to assign lock-m_seq on creation. */std::atomicuint64_t m_seq;
#endif /* UNIV_DEBUG */
};
lock_t 、lock_rec_t 、lock_table_t
无论是行锁还是表锁都使用lock_t结构保存其中用一个union来分别保存行锁和表锁不同的数据分别为lock_table_t和lock_rec_t
/** Lock struct; protected by lock_sys latches */
struct lock_t {/** transaction owning the lock */trx_t *trx;/** list of the locks of the transaction */UT_LIST_NODE_T(lock_t) trx_locks;/** Index for a record lock */dict_index_t *index;/** Hash chain node for a record lock. The link node in a singlylinked list, used by the hash table. */lock_t *hash;union {/** Table lock */lock_table_t tab_lock;/** Record lock */lock_rec_t rec_lock;};/** Record lock for a page */
struct lock_rec_t {/** The id of the page on which records referenced by this locks bitmap arelocated. */page_id_t page_id;/** number of bits in the lock bitmap;NOTE: the lock bitmap is placed immediately after the lock struct */uint32_t n_bits;/** Print the record lock into the given output streamparam[in,out] out the output streamreturn the given output stream. */std::ostream print(std::ostream out) const;
};struct lock_table_t {dict_table_t *table; /*! database table in dictionarycache */UT_LIST_NODE_T(lock_t)locks; /*! list of locks on the sametable *//** Print the table lock into the given output streamparam[in,out] out the output streamreturn the given output stream. */std::ostream print(std::ostream out) const;
};
bitmap
Innodb 使用位图来表示锁具体锁住了那几行在函数 lock_rec_create 中为 lock_t 分配内存空间的时候会在对象地址后分配一段内存空间(当前行数 64)用来保存位图。n_bits 表示位图大小。
锁的基本模式的兼容关系和强弱关系
/* LOCK COMPATIBILITY MATRIX* IS IX S X AI* IS - * IX - - * S - - -* X - - - - -* AI - - -** Note that for rows, InnoDB only acquires S or X locks.* For tables, InnoDB normally acquires IS or IX locks.* S or X table locks are only acquired for LOCK TABLES.* Auto-increment (AI) locks are needed because of* statement-level MySQL binlog.* See also lock_mode_compatible().*/
static const byte lock_compatibility_matrix[5][5] {/** IS IX S X AI *//* IS */ { TRUE, TRUE, TRUE, FALSE, TRUE},/* IX */ { TRUE, TRUE, FALSE, FALSE, TRUE},/* S */ { TRUE, FALSE, TRUE, FALSE, FALSE},/* X */ { FALSE, FALSE, FALSE, FALSE, FALSE},/* AI */ { TRUE, TRUE, FALSE, FALSE, FALSE}type_mode
};/* STRONGER-OR-EQUAL RELATION (mode1row, mode2column)* IS IX S X AI* IS - - - -* IX - - -* S - - -* X * AI - - - - * See lock_mode_stronger_or_eq().*/
static const byte lock_strength_matrix[5][5] {/** IS IX S X AI *//* IS */ { TRUE, FALSE, FALSE, FALSE, FALSE},/* IX */ { TRUE, TRUE, FALSE, FALSE, FALSE},/* S */ { TRUE, FALSE, TRUE, FALSE, FALSE},/* X */ { TRUE, TRUE, TRUE, TRUE, TRUE},/* AI */ { FALSE, FALSE, FALSE, FALSE, TRUE}
};
行锁类别代码
#define LOCK_WAIT \256 /*! Waiting lock flag; when set, it \means that the lock has not yet been \granted, it is just waiting for its \turn in the wait queue */
/* Precise modes */
#define LOCK_ORDINARY \0 /*! this flag denotes an ordinary \next-key lock in contrast to LOCK_GAP \or LOCK_REC_NOT_GAP */
#define LOCK_GAP \512 /*! when this bit is set, it means that the \lock holds only on the gap before the record; \for instance, an x-lock on the gap does not \give permission to modify the record on which \the bit is set; locks of this type are created \when records are removed from the index chain \of records */
#define LOCK_REC_NOT_GAP \1024 /*! this bit means that the lock is only on \the index record and does NOT block inserts \to the gap before the index record; this is \used in the case when we retrieve a record \with a unique key, and is also used in \locking plain SELECTs (not part of UPDATE \or DELETE) when the user has set the READ \COMMITTED isolation level */
#define LOCK_INSERT_INTENTION \2048 /*! this bit is set when we place a waiting \gap type record lock request in order to let \an insert of an index record to wait until \there are no conflicting locks by other \transactions on the gap; note that this flag \remains set when the waiting lock is granted, \or if the lock is inherited to a neighboring \record */
#define LOCK_PREDICATE 8192 /*! Predicate lock */
#define LOCK_PRDT_PAGE 16384 /*! Page lock */
记录锁的alloc函数
Create the lock instance创建一个lock实例在create函数中被调用。主要就是分配一些内存还有设置事务请求记录锁、锁的索引号、锁的模式、行锁的pageid、n_bits。
/**
Create the lock instance
param[in, out] trx The transaction requesting the lock
param[in, out] index Index on which record lock is required
param[in] mode The lock mode desired
param[in] rec_id The record id
param[in] size Size of the lock bitmap requested
return a record lock instance */
lock_t *RecLock::lock_alloc(trx_t *trx, dict_index_t *index, ulint mode,const RecID rec_id, ulint size) {ut_ad(locksys::owns_page_shard(rec_id.get_page_id()));/* We are about to modify structures in trx-lock which needs trx-mutex */ut_ad(trx_mutex_own(trx));lock_t *lock;if (trx-lock.rec_cached trx-lock.rec_pool.size() ||sizeof(*lock) size REC_LOCK_SIZE) {ulint n_bytes size sizeof(*lock);mem_heap_t *heap trx-lock.lock_heap;lock reinterpret_castlock_t *(mem_heap_alloc(heap, n_bytes));} else {lock trx-lock.rec_pool[trx-lock.rec_cached];trx-lock.rec_cached;}lock-trx trx;lock-index index;/* Note the creation timestamp */ut_d(lock-m_seq lock_sys-m_seq.fetch_add(1));/* Setup the lock attributes */lock-type_mode LOCK_REC | (mode ~LOCK_TYPE_MASK);lock_rec_t rec_lock lock-rec_lock;/* Predicate lock always on INFIMUM (0) */if (is_predicate_lock(mode)) {rec_lock.n_bits 8;memset(lock[1], 0x0, 1);} else {ut_ad(8 * size UINT32_MAX);rec_lock.n_bits static_castuint32_t(8 * size);memset(lock[1], 0x0, size);}rec_lock.page_id rec_id.get_page_id();/* Set the bit corresponding to rec */lock_rec_set_nth_bit(lock, rec_id.m_heap_no);MONITOR_INC(MONITOR_NUM_RECLOCK);MONITOR_INC(MONITOR_RECLOCK_CREATED);return (lock);
}
记录锁的add函数
将锁添加到记录锁哈希和事务的锁列表中。
void RecLock::lock_add(lock_t *lock) {ut_ad((lock-type_mode | LOCK_REC) (m_mode | LOCK_REC));ut_ad(m_rec_id.matches(lock));ut_ad(locksys::owns_page_shard(m_rec_id.get_page_id()));ut_ad(locksys::owns_page_shard(lock-rec_lock.page_id));ut_ad(trx_mutex_own(lock-trx));bool wait m_mode LOCK_WAIT;hash_table_t *lock_hash lock_hash_get(m_mode);lock-index-table-n_rec_locks.fetch_add(1, std::memory_order_relaxed);if (!wait) {lock_rec_insert_to_granted(lock_hash, lock, m_rec_id);} else {lock_rec_insert_to_waiting(lock_hash, lock, m_rec_id);}#ifdef HAVE_PSI_THREAD_INTERFACE
#ifdef HAVE_PSI_DATA_LOCK_INTERFACE/* The performance schema THREAD_ID and EVENT_ID are used onlywhen DATA_LOCKS are exposed. */PSI_THREAD_CALL(get_current_thread_event_id)(lock-m_psi_internal_thread_id, lock-m_psi_event_id);
#endif /* HAVE_PSI_DATA_LOCK_INTERFACE */
#endif /* HAVE_PSI_THREAD_INTERFACE */locksys::add_to_trx_locks(lock);if (wait) {lock_set_lock_and_trx_wait(lock);}
}
记录锁的create函数
就是调用alloc然后add加锁
Create a lock for a transaction and initialise it.
param[in, out] trx Transaction requesting the new lock
param[in] prdt Predicate lock (optional)
return new lock instance */
lock_t *RecLock::create(trx_t *trx, const lock_prdt_t *prdt) {ut_ad(locksys::owns_page_shard(m_rec_id.get_page_id()));/* Ensure that another transaction doesnt access the trxlock state and lock data structures while we are adding thelock and changing the transaction state to LOCK_WAIT.In particular it protects the lock_alloc which uses trxs private pool oflock structures.It might be the case that we already hold trx-mutex because we got here from:- lock_rec_convert_impl_to_expl_for_trx- add_to_waitq*/ut_ad(trx_mutex_own(trx));/* Create the explicit lock instance and initialise it. */lock_t *lock lock_alloc(trx, m_index, m_mode, m_rec_id, m_size);#ifdef UNIV_DEBUG/* GAP lock shouldnt be taken on DD tables with some exceptions */if (m_index-table-is_dd_table strstr(m_index-table-name.m_name,mysql/st_spatial_reference_systems) nullptr strstr(m_index-table-name.m_name, mysql/innodb_table_stats) nullptr strstr(m_index-table-name.m_name, mysql/innodb_index_stats) nullptr strstr(m_index-table-name.m_name, mysql/table_stats) nullptr strstr(m_index-table-name.m_name, mysql/index_stats) nullptr) {ut_ad(lock_rec_get_rec_not_gap(lock));}
#endif /* UNIV_DEBUG */if (prdt ! nullptr (m_mode LOCK_PREDICATE)) {lock_prdt_set_prdt(lock, prdt);}lock_add(lock);return (lock);
}
4、锁的流程
lock system 开始启动 申请lock_sys_t结构初始化结构体
lock system 结束关闭 释放lock_sys_t结构的元素释放结构体
表锁加锁流程
1、检查当前事务是否拥有更强的表锁如果有的话直接返回成功否则继续往下走2、遍历表的锁列表判断是否有冲突的锁没有转3有转43、直接创建一个表锁放入事务的lock list中放入table 的lock list中加锁成功4、创建等待的表锁然后进行死锁检测和死锁解决回滚当前事务或者挂起当前事务
行锁加锁流程 插入加锁流程
1、对表加IX锁2、对修改的页面加X锁3、如果需要检测唯一键冲突尝试给需要加的唯一键加一个S | next-key lock。可能会产生锁等待4、判断是否插入意向锁冲突冲突的话加等待的插入意向锁不冲突直接插入数据5、释放页面锁
删除加锁流程带来的死锁
删除加锁有个问题删除并发的时候的加锁会导致死锁。
1、事务1获取表IX锁2、事务1获取页面X锁3、事务1获取第n行的 x | not gap锁4、事务1删除第n行5、事务1释放页面X锁6、事务2获取页面X锁7、事务2尝试获取第n行的 x | not gap锁发现冲突等待8、事务2释放页面X锁9、事务1释放第n行的锁提交事务10、释放第n行锁的时候检查到事务2有一个等待锁发现可以加锁了唤醒事务2成功加锁11、事务3获取页面X锁12、事务3尝试删除第n行发现第n行已经被删除尝试获取第n行的next-key lock发现事务2有个 x| gap锁冲突等待13、事务3释放页面X锁14、事务2获取页面X锁检查页面是否改动重新检查第n行数据发现被删尝试获取该行next- key lock发现事务3在等待这个锁事务2冲突进入等待15、造成死锁
释放锁流程 死锁流程
构造wait-for graph
构造一个有向图图中的节点代表一个事务图的一个边A-B代表着A事务等待B事务的一个锁
具体实现是在死锁检测时从当前锁的事务开始搜索遍历当前行的所有锁判断当前事务是否需要等待现有锁释放是的话代表有一条边进行一次入栈操作
死锁检测
有向图判断环用栈的方式如果有依赖等待进行入栈如果当前事务所有依赖的事务遍历完毕进行一次出栈
回滚事务选择
如果发现循环等待选择当前事务和等待的事务其中权重小的一个回滚具体的权重比较函数是 trx_weight_ge, 如果一个事务修改了不支持事务的表那么认为它的权重较高否则认为 undo log 数加持有的锁数之和较大的权重较高。
5、参考
1、https://segmentfault.com/a/1190000017076101?utm_sourcecoffeephp.com 2、Mysql 8.022源代码 3、深入浅出MySQL 8.0 lock_sys锁相关优化
count()用法
count()语义该函数为一个聚合函数对于返回的结果集一行行地判断如果count函数地参数不是NULL累计值就加1否则不加。最后返回累计值。 所以count(*),count(主键id)和count(1)都表示返回满足条件地结果集地总行数 而count(字段)则表示返回满足条件地数据行里面参数“字段”不为NULL的总个数。
count(主键id) InnoDB引擎会遍历整张表把每一行的id值都取出来返回给server层。 sever层拿到id后判断id是不可能为空的就按行累加
count(1) InnoDB引擎遍历整张表但不取值。server层对于返回的每一行放一个数字“1”进去判断是不可能为空的按行累加
count(字段) 1、如果这个字段定义为not null的话一行行地从记录里面读出这个字段判断不能为null按行累加 2、如果这个字段允许为null那么在执行的时候要判断字段是否为null不是null才累加
count(*) 不会把全部字段取出来而是专门做了优化不取值。并且count(*)肯定不是null按行累加。 所以按照效率排序的话 count(字段) count(主键id) count(1) 约等于 count(*) InnoDB是支持事务的MyISAM不支持事务。 InnoDB每一行记录都要判断自己是否对这个会话是否可见所以对于count(*)请求来说InnoDB只好把数据一行一行地读出依次判断可见地行才能够用于计算“基于这个查询”地表地总行数。
《MySQL——join语句优化tips》
要不要用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);
《MySQL——redo log 与 binlog 写入机制》
WAL机制告诉我们只要redo log与binlog保证持久化到磁盘里就能确保MySQL异常重启后数据可以恢复。
下面主要记录一下MySQL写入binlog和redo log的流程。
binlog写入机制
1、事务执行过程中先把日志写到binlog cache事务提交的时候再把binlog cache写到binlog文件中。
2、binlog cache系统为每个线程分配了一片binlog cache内存参数binlog_cache_size控制单个线程内binlog cache大小。如果超过了这个大小就要暂存磁盘
3、事务提交的时候执行器把binlog cache里完整的事务写入binlog中。并清空binlog cache
4、每个线程都有自己的binlog cache共用一份binlog文件
5、write是把日志写入到文件系统的page cache内存中没有持久化到磁盘所以速度比较快图中的fsync是将数据持久化到磁盘占用磁盘的IOPS
关于何时write、fsync是由参数sync_binlog控制的
1、sync_binlog 0时每次提交事务都只write不fsync;
2、sync_binlog 1时每次提交事务都会执行fsync
3、sync_binlog NN1时表示每次提交事务都write但累积N个事务后才fsync。
sync_binlog控制binlog真正刷盘的频率对于一个IO非常大的情景这个数字调大可以提高性能但是如果容错率非常低的情况下必须设为1.sync_binlog设置为N对应的风险是如果主机发生异常重启会丢失最近N个事务的binlog日志
redo log写入机制
事务在执行过程中生成的redo log是要先写到redo log buffer的。
redo log buffer里面的内容并不需要每次生成后都要持久化到磁盘中。
如果事务执行期间MySQL发生异常重启那么这部分日志就丢了。由于事务并没有提交所以这时日志丢了也不会有损失。
事务没提交的时候redo log buffer部分日志也是有可能被持久化到磁盘中的。 上面三个颜色表征了redo log可能的三种状态
1、存在redo log buffer中物理上是在MySQL进程内存中即红色部分
2、写到磁盘(write),但是没有持久化(fsync),物理上实在文件系统的page cache里面即黄色部分
3、持久化到磁盘对应的是hard disk也就是图中的绿色部分
前两步是写内存最后一步是磁盘IO所以要在page cache够大且不影响写入page cache前将redo log 持久化到磁盘 。
为了控制redo log 的写入策略InnoDB提供了innodb_flush_log_at_trx_commit参数他有三种可能取值
1、设置为0每次事务提交的时候都只是把redo log留在redo log buffer中
2、设置为1每次事务提交的时候都只是把redo log直接持久化到磁盘
3、设置为2每次事务提交时都只是把redo log写到page cache
与binlog不同binlog是每个线程都有一个binlog cache而redo log是多个线程共用一个redo log buffer。
InnoDB有一个后台线程每隔1s就会把redo log buffer中的日志调用write写到文件系统的page cache然后调用fsync持久化到磁盘事务执行过程中的redo log也是直接写在redo log buffer上的所以未提交的事务的redolog也可能被持久化到磁盘。
还有两种场景也会导致没有提交的事务的redo log写入到磁盘中
情形1
redo log buffer占用的空间即将达到innodb_log_buffer_size一半的时候后台线程会主动写盘。
这里只是write没有fsync
情形2
并行的事务提交的时候顺带将这个事务的redo log buffer持久化到磁盘。
事务A执行一半部分redo log到buffer中事务B提交且 innodb_flush_log_at_trx_commit 会把redo log buffer里的log全部持久化到磁盘中
补充说明
两阶段提交在时序上redo log先prepare 再写binlog最后再把redo log commit;
innodb_flush_log_at_trx_commit 设置成 1prepare阶段redo log就已经落盘。所以redo log再commit的时候就不需要fsync了只会write到文件系统的page cache中就够了。
sync_binlog 和 innodb_flush_log_at_trx_commit都设置为1即一个事务完整提交前需要等待两次刷盘一次是redo log(prepare阶段)一次是binlog。
组提交机制实现大量的TPS
首先介绍日志逻辑序列号(log sequence number,LSN)的概念。LSN是单调递增的每次写入长度length的redo logLSN的值就会加上length。
三个并发事务(trx1,trx2,trx3)在prepare阶段都写完redo buffer并持久化到磁盘。
对应的LSN为50、120、160.
对应流程
1、trx1第一个到达被选为这组的leader
2、等trx1要开始写盘的时候这个组里面已经有三个事务这时候LSN也变成了160
3、trx1去写盘的时候LSN160trx1返回时所有LSN 160的redo log都被持久化到磁盘中
4、trx2与trx3直接返回。
总结
一次组提交中组员越多节约磁盘IOPS的效果越好。如果是单线程就只能一个事务对应一次持久化操作
两阶段提交 两阶段提交细化
这样保证binlog也可以组提交了。由于step3速度快所以集合到一起的binlog比较少所以binlog的组提交效果不如redo log组提交。
提升binlog效果
--1.binlog_group_commit_sync_delay :b表示延迟多少微秒后才调用fsync;
--2.binlog_group_commit_sync_no_delay_count :表示累积多少次以后才调用fsync;
理解WAL机制
WAL机制是减少磁盘写可是每次提交事务都要写redo log和binlog 磁盘读写次数没有变少。
所以WAL机制主要得益于两个方面
--1、redo log和binlog都是顺序写磁盘的顺序写比随机写速度要快
--2、组提交机制可以降低磁盘IOPS消耗
如何提升IO性能瓶颈
1、设置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count参数减少binlog的写盘次数。这个方法是基于“额外故意等待”来实现的可能会增加语句的响应时间但是不会丢失数据
2、将sync_binlog设置为大于1的值(100~1000)。不过会有主机掉电时丢binlog日志的风险
3、将 innodb_flush_log_at_trx_commit 设置为2。会有主机掉电丢数据的风险
《MySQL——备库多线程复制策略》
备库并行复制能力
主要涉及两个方面的并行度
1、客户端写入主库的能力
2、备库上sql_thread执行中转日志relay log
1的并行能力比2强。
主库上由于InnoDB支持行锁对业务并行度的支持比较友好。
备库上如果用单线程会导致备库应用日志不够快造成主备延迟。
现在MySQL使用的是多线程复制
coordinator 就是原来的sql_thread不过现在它不再直接更新数据了只负责读取中转日志和分发事务。真正更新日志的是worker线程。线程个数由slave_parallel_workers决定一般设置为8~16。
coordinator在分发事务的时候要遵循两个要求
不能造成更新覆盖。也就是说更新同一行的两个事务必须被分发到同一个worker中。同一个事务不能被拆开必须放到同一个worker中。
MySQL5.6版本 并行复制策略
支持粒度库
用于决定分发策略的hash表key值数据库名
优势
1、构造hash值快一个实例上的DB数目不会很多。
2、不要求binlog格式。row和statement格式的binlog都可以拿到库名。
缺点
1、主库表在同一个DB中策略失效
2、不同DB热点不同起不到并行效果
MariaDB 并行复制策略
策略
1、能够在同一组里提交的事务一定不会修改同一行
2、主库上可以并行执行的事务备库上一定是可以并行执行的
为了实现该策略MariaDB实现方法为
1、在一组里面一起提交的事务有一个相同的commit_id下一组就是commit_id1
2、commit_id直接写到binlog里
3、传到备库应用的时候相同commit_id的事务分发到多个worker执行
4、一组全部执行完后coordinator再去取下一批
这个策略目标就是备库模拟主库的并行模式。
不过主库再一组事务commit的时候下一组事务实际上是处于执行中状态的。
而按照MariaDB策略在备库上执行的时候要等一组事务完全执行完下一组事务才能开始执行这样系统的吞吐量就不够。
这个策略对于长事务来说不友好。如果一组里有一个超大事务线程该组其他线程执行完后要等待这个线程执行完之后才能切换到下一组。这段时间只有一个线程进行工作浪费了资源。
MySQL5.7版本 并行复制策略
策略思想
1、同时处于prepare状态的事务在备库执行时是可以并行的
2、处于prepare状态的事务与处于commit状态的事务之间在备库执行时也是可以并行的
通过调节binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count参数
来来拉长binlog从write到fsync的时间以此减少binlog’的写盘次数。同时在并行复制策略里可以用来制造更多“同时处于prepare阶段的事务”。这样就能增加备库复制的并行度。
通俗来讲这两个参数既可以让主库提交慢一点又可以让备库执行快一点。在MySQL5.7处理备库延迟时可以调节这两个参数达到提升备库复制并行度的目的。
MySQL5.7.22版本 并行复制策略
新增了一个参数binlog-transaction-dependency-tracking用来控制是否启用这个新策略。
可选值
1、COMMIT_ORDER表示根据同时进入prepare和commit来判断是否可以并行
2、WRITESET,表示对于事务涉及更新的每一行计算出这一行的hash值组成集合writeset。如果两个事务没有操作相同的行即writeset没有交集就可以并行。
3、WRITESET_SESSION在WRITESET基础上多了一个约束在主库上同一线程先后执行的两个事务在备库执行的时候要保证相同的先后顺序
为了唯一标识hash通过库名表名索引名值计算。如果表上除了主键索引外还有其他唯一索引那么对于每个唯一索引insert语句对应的writeset就要多增加一个hash值。
这个版本的好处在于
--1、writeset是在主库生成后直接写入到binlog里的在备库执行的时候不需要解析binlog内容节省了备库计算量
--2、不需要把整个事务的binlog都扫一边才能决定分发到哪个worker更加节省内存
--3、备库的分发策略不依赖于binlog内容所以binlog是statement格式也是可以的
对于表上没有主键和外键约束的场景WRITSET策略也没有办法并行会暂时退化为单线程模型。 所以表是否有主键也是影响主备同步延迟原因之一。
总结
单线程复制能力低于多线程复制对于更新压力较大的主库备库可能一直追不上主库。
MySQL备库并行策略修改了binlog的内容也就是说不是向上兼容的所以需要注意。
《MySQL——查询长时间不返回的三种原因与查询慢的原因》
构造一张表表有两个字段id和c再里面插入了10万行记录
create table t (id int(11) not null,c int(11) default null,primary key (id)
) engine InnoDB;delimiter ;;
create procedure idata()
begindeclare i int;set i 1;while( i 100000) doinsert into t values(i,i);set i i1;end while;
end;;
delimiter ;call idata();
查询长时间不返回
在表t执行
select * from t where id 1;
查询结果长时间不返回。
等MDL锁
大概率是表t被锁住了接下来分析原因一般都是首先执行show processlist命令看看当前语句处于什么状态。 表示现在有个线程正在表t上请求或者持有MDL写锁把select语句阻塞了 session A通过lock table命令持有表t的MDL写锁而sessionB 的查询需要获取MDL读锁所以session B 进入等待状态。
处理方式找到谁持有MDL写锁然后把它kill掉。
通过
select blocking_pid from sys.schema_table_lock_waits;
得到blocking_pid 4;
然后用kill命令断开即可。
等flush
在表t上执行下面语句
select * from information_schema.processlist where id1; 可以查看出该线程的状态是Waiting for table flush;
表示现在有一个线程正要对表t做flush操作。
flush tables t with read lock; --只关闭表t
--or
flush tables with read lock; --关闭MySQL里面所有打开的表
正常来说这两个语句执行起来都很快除非它们也被别的线程堵住了。
所以可能是有一个flush tables命令被别的语句堵住了然后它又堵住了我们的select语句。
下图是执行结果
等行锁
select * from t where id 1 lock in share mode;
由于访问id 1这个记录时要加读锁如果这时候已经有一个事务在这行记录上持有一个写锁我们的select语句就会被堵住如下
session A启动事务占用写锁但是不提交导致session B被堵住。
可以通过
mysql select * from t sys.innodb_lock_waits where locked_tabletest.t\G
进行查询查出是谁占着这个写锁 发现是4号线程然后我们kill 4
查询慢
select * from t where c 50000 limit 1;
由于字段c上没有索引所以这个语句只能走id主键顺序扫描因此需要扫描5万行。
扫描行数多所以执行慢这个很好理解。
而下面的这条语句扫描行数为1但是执行时间取却较长
select * from t where id 1; 在这个场景下session A先启动了一个事务之后session B才开始执行update语句。
session B 更新完 100 万次生成了 100 万个回滚日志 (undo log)
带lock in share mode 的sql是当前读因此会直接读到 1000001 所以速度很快。
select * from t where id 1语句是一致性读因此需要从 1000001 开始依次执行undo log执行100万次后才将1返回 **回滚日志过大引起的一致性读慢当前读快 **
#《MySQL——幻读与next-key lock与间隙锁带来的死锁》
create table t (id int(11) not null,c int(11) default null,d int(11) default null,primary key (id),key c (c)
) engine InnoDB;
insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
该表除了主键id还有索引c。问下面的语句序列是怎么加锁的加的锁又是什么时候释放的呢sql
begin;
select * from t where d 5 for update;
commit;
这条语句会命中d5这一行对应主键id5因此在select语句执行完成后id5这一行会加一个写锁并且由于两阶段锁协议这个写锁会在执行commit语句的时候释放。
由于字段d上没有索引因此这条查询语句会做全表扫描那么其他被扫描的不满足的行记录会不会被加锁
幻读现象
如果旨在id5这一行加锁而其他行不加锁在下面这个情况下 session A执行了三次当前读并且加上了写锁。
幻读指的是一个事务在前后两次查询同一个范围的时候后一次查询看到了前一次查询没有看到的行。
幻读与不可重复读的区别
在同一个事务中两次读取到的数据不一致的情况称为幻读和不可重复读。幻读是针对insert导致的数据不一致不可重复读是针对 delete、update导致的数据不一致。
1、在可重复读隔离级别下普通的查询是快照读是不会看到别的事务插入的数据的。
2、session B的修改结果被session A之后的select语句用当前读看到不能称为幻读。幻读仅仅指新插入的行
幻读带来的问题
1、破坏语义。
session A在T1就说了把d5的行锁住不准别的事务进行读写此时被破坏。
因为如果我们这时插入d5的数据这条新的数据不在锁的保护范围之内。
2、数据一致性问题
锁的设计是为了保证数据的一致性不止是数据库内部数据状态在此刻的一致性还包含了数据和日志在逻辑上的一致性。
即使给所有行加上了锁也避免不了幻读这是因为给行加锁的时候这条记录还不存在没法加锁 。
也就是说即使把所有的记录都上锁了还是阻止不了新插入的记录
如何解决幻读
产生的幻读的原因是行锁只能锁住行
为了解决幻读问题InnoDB引入新的锁间隙锁(Gap Lock)
间隙锁锁的就是两个值之间的空隙比如在表t初始化插入了6个记录就产生了7个间隙
执行
select * from t where d 5 for update
6个记录加上了行锁同时加上了7个间隙锁。
间隙锁与行锁有点不一样
行锁可以分为读锁与写锁
与行锁有冲突关系的是另外一个行锁。
间隙锁不一样间隙锁之间不存在冲突关系。
与间隙锁存在冲突关系的是向间隙中插入一个记录这个操作。
举例 由于表t中并没有c7这个记录所以session A加的是间隙锁(5,10)。而session B也是在这个间隙加的间隙锁它们的目标都是保护这个间隙不允许插入值所以两者不冲突。
next-key lock
间隙锁与行锁合称next-key lock每个lock都是前开后闭区间。间隙锁是开区间。
如上面我们插入数据使用
select * from t for update
形成了7个next-key lock分别是
(-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, supremum]
supremum是一个不存在的最大值。
next-key lock 的引入解决了幻读问题但是也带来了新的问题。
如现在有这样一个业务逻辑
任意锁住一行如果这一行不存在的话就插入如果存在这一行就更新它的数据。
begin;
select * from t where id N for update;
--如果行不存在
insert into t values(N,N,N);
--如果行存在
update t set d N set id N;commit;
现在出现这个现象这个逻辑一旦有并发就会碰到死锁。
死锁的产生两个间隙锁不冲突相互等待行锁
执行流程
1、session A执行select…for update语句由于id9这一行不存在因此会加上间隙锁(5,10)
2、session B执行select…for update语句同样会加上间隙锁(5,10)
3、session B插入(9,9,9)被session A的间隙锁锁住进入等待
4、session A擦汗如·插入(9,9,9),被session B的间隙锁锁住。
InnoDB死锁检测发现了这对死锁关系然后报错返回了。
所以说间隙锁的引入可能会导致相同的语句锁住更大的范围从而影响并发度。
间隙锁是在可重复读隔离级别下才会生效的。所以你如果把隔离级别设置为读提交的话就没有间隙锁了。 但同时你要解决可能出现的数据和日志不一致问题需要把 binlog 格式设置为 row。
#《MySQL——临时表》
## 内存表与临时表区别
临时表一般是人手动创建。 内存表是mysql自动创建和销毁的。
内存表指的是使用Memory引擎的表建表语法create table ... engine memeory
表的数据存在内存里系统重启后会被清空但是表的结构还在。
临时表可以使用各种引擎类型。如果使用的是InnoDB或者MyISAM引擎写数据是写在磁盘上的。当然临时表也可以使用Memory引擎。
临时表特性
1、一个临时表只能被创建它的session访问对于其他线程不可见当此session结束时会自动删除临时表
2、临时表可以与普通表同名。如果同一个session里有同名的临时表和普通表使用show create语句以及增删改查语句访问的是临时表
3、show tables命令不显示临时表
临时表的应用
由于不用担心线程之间的重名冲突临时表经常被用在复杂查询的优化过程中。其中分库分表系统的跨库查询就是一个典型的使用场景。
查询语句到所有的分库中查找满足条件的行然后统一做order by操作。
可以把各个分库拿到的数据汇总到一个MySQL实例的一个表中然后在这个汇总实例上做逻辑操作。如下
至于临时表的存储位置可以放在分库中的某一个。
另外一个使用场景就是使用union如果使用的是union all就不需要用了。系统会先创建一个内部临时表执行第一个子查询的结果放到临时表中执行第二个子查询的结果先看看插入是否成功成功则插入。最后从临时表中按行取数据然后返回结果删除临时表。
临时表可以重名的原因
无论是普通表还是临时表一个表都会对应一个table_def_def
一个普通表的table_def_def的值由库名表名得到。所以在同一个库下创建两个同名的普通表会由重复性错误。对于临时表table_def_def在“库名表名”的基础上还加上了“server_id thread_id”
在实现上每个线程都维护了自己的临时表链表每次session内操作表的时候先遍历链表检查是否有这个名字的的临时表有就优先操作否则再操作普通表。
session结束时对链表中的每个临时表执行drop操作。这个操作也会被写道binlog里用于主备复制。
临时表的主备同步
row格式的binlog不会记录临时表相关语句只有statement或者mixed格式才会记录。
创建临时表的语句会传到备库执行因此备库的同步线程就会创建这个临时表。主库在线程退出的时候会自动删除临时表但是备库同步线程还是在运行的所以主库还需要写个DROP TEMPORARY TABLE传给备库。
当主库上两个session创建了同名临时表t1这两个语句被传给备库上。
主库执行语句的线程id会被写道binlog中备库可以用线程id构造临时表的table_def_key:
备库名 t1 “主库的serverid” “session的thread_id”,所以两个表在备库的应用线程不会冲突。
#《MySQL——使用联合索引、覆盖索引避免临时表的排序操作》
## 联合索引避免临时表排序
在上一篇笔记(MySQL——order by逻辑全字段排序与rowid排序)中讲到查询语句查询多个字段的时候使用order by语句实现返回值是有序的而order by是使用到了临时表的会带来时间和空间损失。 其实使用联合索引就可以避免临时表的排序操作。 只要保证city这个索引上取出来的行天然就是按照name递增排序的话就可以不用再排序了。
alter table t add index city_user(city,name);
在这个索引里面通过树搜索的方式定位到第一个满足city 杭州’的记录并且额外确保了接下来按顺序取“下一条记录”的遍历过程中只要city值是杭州name值一定有序。 查询流程变为 1、从索引(city,name)找到第一个满足city 杭州’条件的主键id; 2、到主键id索引取出整行取name、city、age三个字段值作为结果集的一部分直接返回 3、从索引(city,name)取下一个记录主键id 4、重复step2、3直到查到第1000条记录或者不满足city 杭州’条件时循环结束。
覆盖索引优化查询
可以使用覆盖索引继续优化查询的执行流程 覆盖索引指索引上的信息足够满足查询请求不需要再回到主键索引上取数据。 针对select city,name,age from t 这个查询可以创建一个city、name和age的联合索引对应语句为
alter table t add index city_user_age(city,name,age);
这时对于city字段的值相同的行来说还是按照name字段的值递增排序。查询语句的执行流程变为 1、从索引(city,name,age)找到第一个满足city 杭州’条件的记录取出其中的city、name和age三个字段值作为结果集的一部分直接返回 2、从索引(city,name,age)取下一个记录同样取出这三个字段的值作为结果集的一部分直接返回 3、重复步骤2直到查到第1000条记录或者是不满足city 杭州’条件时循环结束。
当然并不是说每个查询能用上覆盖索引就要把语句中涉及的字段都建上联合索引。因为索引有维护代价。
思考
假设表里面已经有了city_name(city,name)联合索引。你需要查询杭州和苏州两个城市中所有市民的名字并且按名字排序显示前100条记录。
select * from t where city in(杭州,苏州) order by name limit 100;
这个语句会有排序。因为条件是苏州或杭州。如果只有一个条件如只有杭州那么就不需要排序操作。 如果我们需要实现一个在数据库端不需要排序的方案可以这么实现 把这一条语句拆成两条语句流程如下 1、执行select * from t where city 杭州 order by name limit 100; (这个语句不需要排序客户端用一个长度为100的内存数组A保存结果) 2、执行select * from where city 苏州 order by name limit 100; (相同的方法结果被存入内存数组B) 3、对AB两个有序数组采用归并排序得到name最小的前100值这就是我们需要的结果了。
事务
事务的必要性
mysql中事务是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性。 比如我们的银行转账
-- a - -100
UPDATE user set money money - 100 WHERE name a;-- b - 100
UPDATE user set money money 100 WHERE name b;
如果程序中只有一条语句执行成功了而另外一条没有执行成功就会出现前后不一致。就会有人白嫖。 因此在执行多条有关联 SQL 语句时事务可能会要求这些 SQL 语句要么同时执行成功要么就都执行失败。 也就是说事务具有原子性。
MySQL中如何控制事务
1、mysql是默认开启事务的(自动提交) 默认事务开启的作用
-- 查询事务的自动提交状态
SELECT AUTOCOMMIT;
--------------
| AUTOCOMMIT |
--------------
| 1 |
--------------
当我们执行一个sql语句时候效果会立即体现出来且不能回滚。 回滚举例
CREATE DATABASE bank;USE bank;CREATE TABLE user (id INT PRIMARY KEY,name VARCHAR(20),money INT
);INSERT INTO user VALUES (1, a, 1000);SELECT * FROM user;
-----------------
| id | name | money |
-----------------
| 1 | a | 1000 |
-----------------
执行插入语句后数据立刻生效原因是 MySQL 中的事务自动将它提交到了数据库中。那么所谓回滚的意思就是撤销执行过的所有 SQL 语句使其回滚到最后一次提交数据时的状态。
在 MySQL 中使用 ROLLBACK 执行回滚 由于所有执行过的 SQL 语句都已经被提交过了所以数据并没有发生回滚。
-- 回滚到最后一次提交
ROLLBACK;SELECT * FROM user;
-----------------
| id | name | money |
-----------------
| 1 | a | 1000 |
-----------------
将自动提交关闭后可以数据回滚
-- 关闭自动提交
SET AUTOCOMMIT 0;-- 查询自动提交状态
SELECT AUTOCOMMIT;
--------------
| AUTOCOMMIT |
--------------
| 0 |
--------------
现在我们测试一下
INSERT INTO user VALUES (2, b, 1000);-- 关闭 AUTOCOMMIT 后数据的变化是在一张虚拟的临时数据表中展示
-- 发生变化的数据并没有真正插入到数据表中。
SELECT * FROM user;
-----------------
| id | name | money |
-----------------
| 1 | a | 1000 |
| 2 | b | 1000 |
------------------- 数据表中的真实数据其实还是
-----------------
| id | name | money |
-----------------
| 1 | a | 1000 |
------------------- 由于数据还没有真正提交可以使用回滚
ROLLBACK;-- 再次查询
SELECT * FROM user;
-----------------
| id | name | money |
-----------------
| 1 | a | 1000 |
-----------------
可以使用COMMIT将虚拟的数据真正提交到数据库中
INSERT INTO user VALUES (2, b, 1000);
-- 手动提交数据持久性
-- 将数据真正提交到数据库中执行后不能再回滚提交过的数据。
COMMIT;-- 提交后测试回滚
ROLLBACK;-- 再次查询回滚无效了
SELECT * FROM user;
-----------------
| id | name | money |
-----------------
| 1 | a | 1000 |
| 2 | b | 1000 |
-----------------
总结 1、查看自动提交状态: select AUTOCOMMIT; 2、设置自动提交状态: set AUTOCOMMIT 0; 3、手动提交: 在 AUTOCOMMIT 0 时可以使用commit 命令提交事务 4、事务回滚: 在 AUTOCOMMIT 0 时可以使用rollback 命令回滚事务 事务给我们提供了一个可以反悔的机会假设在转账时发生了意外就可以使用 ROLLBACK 回滚到最后一次提交的状态。假设数据没有发生意外这时可以手动将数据COMMIT 到数据表中。
手动开启事务
可以使用BEGIN 或者 START TRANSACTION 手动开启一个事务。
-- 使用 BEGIN 或者 START TRANSACTION 手动开启一个事务
-- START TRANSACTION;
BEGIN;
UPDATE user set money money - 100 WHERE name a;
UPDATE user set money money 100 WHERE name b;-- 由于手动开启的事务没有开启自动提交
-- 此时发生变化的数据仍然是被保存在一张临时表中。
SELECT * FROM user;
-----------------
| id | name | money |
-----------------
| 1 | a | 900 |
| 2 | b | 1100 |
------------------- 测试回滚
ROLLBACK;SELECT * FROM user;
-----------------
| id | name | money |
-----------------
| 1 | a | 1000 |
| 2 | b | 1000 |
-----------------
当然事务开启之后使用commit提交后就不能回滚了。
事务的四大特征
事务的四大特征
A 原子性事务是最小的单位不可以分割 C 一致性事务要求同一事务中的sql语句必须要保证同时成功或者同时失败 I 隔离性事务1 和事务2 之间是具有隔离性的 D 持久性事务一旦结束(commit or rollback)就不可以返回
事务开启方式
1、修改默认提交 set autocommit 0; 2、begin 3、start transaction
事务手动提交与手动回滚
手动提交commit 手动回滚rollback
事务的隔离性
事务的隔离性 1、read uncommitted; 读未提交的 2、read committed; 读已经提交的 3、repeatable read; 可以重复读 4、serializable; 串行化 脏读现象
在read uncommitted的隔离级别下 脏读一个事务读到了另外有一个事务没有提交的数据 实际开发不允许脏读出现。 如果有两个事务 a、b a事务对数据进行操作在操作的过程中事务并没有被提交但是b可以看见a操作的结果。b看到转账到了然后就不管了。后面a进行rollback操作钱又回去了完成白嫖。
不可重复读现象
在read committed的隔离级别下 小王一开始开启了一个事务然后提交了几个数据然后出去抽烟。 在他抽烟的时候小明在其他电脑上开启了一个事务然后对那个表提交了一个数据。 小王烟抽完了然后统计表中数据发现不对劲。前后不一致了。
幻读现象
在repeatable read;的隔离级别下 事务a和事务b同时操作一张表事务a提交的数据也不能被事务b读到就可以造成幻读。 可以观察如下步骤 小明 在杭州 开启一个事务 小王 在北京 开启一个事务 小明 对table进行插入数据操作然后commit然后查看表发现操作成功 小王在对table进行插入之前也查看表然而并没有小明插入的数据于是乎他插入了同样的一条数据数据库报错。 小王很是疑惑这就是幻读现象。
串行化
在serializable的隔离级别下 当user表被事务a操作的时候事务b里面的写操作是不可以进行的会进入排队状态(串行化)。 “读-读”在串行化隔离级别允许并发。 直到事务a结束之后事务b的写入操作才会执行。 串行化的问题是性能特差。 一般来说隔离级别越高性能越差。 MySQL默认隔离级别是:repeatable read;
一些补充
使用长事务的弊病
从存储空间上来说 长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据所以这个事务提交之前数据库里面它可能用到的回滚记录都必须保留这就会导致大量占用存储空间。 长事务还占用锁资源也可能拖垮整个库。
commit work and chain的语法是做什么用的?
提交上一个事务并且再开启一个新的事务。它的功能等效于commit begin。
怎么查询各个表中的长事务
这个表中记录了所有正在运行的事务信息里面有事务的开始时间。可以从这里看出哪些事务运行的时间比较长。
select * from information_schema.innodb_trx;
如何避免长事务的出现?
从数据库方面 a.设置autocommit1不要设置为0。
b.写脚本监控information_schemal.innodb_trx表中数据内容发现长事务kill掉它。
c.配置SQL语句所能执行的最大运行时间如果查过最大运行时间后中断这个事务
从**SQL语句**方面
设置回滚表空单独存放便于回收表空间从业务代码方面 1、检查业务逻辑代码能拆分为小事务的不要用大事务。 2、检查代码把没有必要的select语句被事务包裹的情况去掉 事务隔离是怎么通过read-view(读视图)实现的
每一行数有多个版本当我们要去读取数据的时候要判断这个数据的版本号对当前事务而言是否可见如果不可见则要根据回滚日志计算得到上一个版本。如果上一个版本也不符合要求则要找到再上一个版本 直到找到对应正确的数据版本。
参考
一天学会MySQL https://time.geekbang.org/column/article/68963
索引
回表
回到主键索引树搜索的过程我们称为回表。
覆盖索引
覆盖索引就是在这次的查询中所要的数据已经在这棵索引树的叶子结点上了。
select ID from T where k between 3 and 5 ID 的值已经在 k 索引树上了因此可以直接提供查询结果不需要回表. 由于覆盖索引可以减少树的搜索次数显著提升查询性能所以使用覆盖索引是一个常用的性能优化手段。
覆盖索引的第二个使用在联合索引上使用也可以避免回表。 如果现在有一个高频请求要根据市民的身份证号查询他的姓名。我们可以建立一个身份证号、姓名的联合索引。它可以在这个高频请求上用到覆盖索引不再需要回表查整行记录减少语句的执行时间。
最左前缀原则
联合索引先根据第一个字段排序如果第一个字段有相同的就按照第二个字段排序。 只要满足最左前缀就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段也可以是字符串索引的最左 M 个字符。
联合索引的时候如何安排索引内的字段顺序
第一原则 如果通过调整顺序可以少维护一个索引那么这个顺序往往就是需要优先考虑采用的。 如果我们有个频繁的要求根据姓名找到该人身份证那么应该建立联合索引(name,ID) 反之如果我们有个频繁的要求根据该人身份证找到该人姓名那么应该建立联合索引(ID,name)
索引下推
索引覆盖是你要查的信息在二级索引中已经有了就不需要回表。索引下推是你的过滤条件有一部分符合了最左前缀那么会用上索引如果此时不符合最左前缀的部分刚好有联合索引中的字段那么在利用最左前缀进行索引查询的同时会根据这些字段多做一步过滤减少索引查询出来的条数这样就减少了回表次数。 如
mysql select * from tuser where name like 张% and age10 and ismale1;
1 每次都需要回表2 下推先滤除不符合的再回表重建索引问题
假设我们有一个主键列为 ID 的表表中有字段 k并且在 k 上有索引。
mysql create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engineInnoDB;
如果你要重建索引 k
alter table T drop index k;
alter table T add index(k);
如果你要重建主键索引
alter table T drop primary key;
alter table T add primary key(id);
上面这两个重建索引的作法对此有什么理解 为什么要重建索引 索引可能因为删除或者页分裂等原因导致数据页有空洞重建索引的过程会创建一个新的索引把数据按顺序插入这样页面的利用率最高也就是索引更紧凑、更省空间。 理解 不论是删除主键还是创建主键都会将整个表重建。所以连着执行这两个语句的话第一个语句就白做了。 推荐使用
alter table T engineInnoDB
联合主键索引和 InnoDB 索引组织表问题
有这么一个表
CREATE TABLE geek (a int(11) NOT NULL,b int(11) NOT NULL,c int(11) NOT NULL,d int(11) NOT NULL,PRIMARY KEY (a,b),KEY c (c),KEY ca (c,a),KEY cb (c,b)
) ENGINEInnoDB;
既然主键包含了 a、b 这两个字段那意味着单独在字段 c 上创建一个索引就已经包含了三个字段了呀为什么要创建“ca”“cb”这两个索引同事告诉他是因为他们的业务里面有这样的两种语句
select * from geek where cN order by a limit 1;
select * from geek where cN order by b limit 1;
为了这两个查询模式这两个索引是否都是必须的为什么呢 表记录 主键 ab 的聚簇索引组织顺序相当于 order by a,b 也就是先按 a 排序再按 b 排序c 无序。
abcd123d132d143d213d222d234d
索引 ca 的组织是先按 c 排序再按 a 排序同时记录主键: 这个跟索引 c 的数据是一模一样的。
cab213222312314321423
索引 cb 的组织是先按 c 排序再按 b 排序同时记录主键
cba222231312321341432
所以结论是 ca 可以去掉cb 需要保留。
in与between的区别
--1.
select * from T where k in(1,2,3,4,5)
--2.
select * from T where k between 1 and 5 1.in 内部的数字是未知的不知道是否有序是否连续等所以你只能一个一个去看。 2.一个已知的升序、范围查询只需定位第一个值后面遍历就行了。 ## 全局锁是什么全局锁有什么用全局锁怎么用
全局锁主要用在逻辑备份过程中对于InnoDB 引擎的库使用–single-transaction; MySQL 提供了一个加全局读锁的方法命令是 Flush tables with read lock (FTWRL),让整个库处于只读状态。
表锁是什么表锁有什么用表锁怎么用
表锁一般是在数据库引擎不支持行锁的时候才会被用到的. 表锁的语法是 lock tables … read/write; 加上读锁不会限制别的线程读但会限制别的线程写。加上写锁会限制别的线程读写。
行锁是什么行锁有什么用行锁怎么用
行锁就是针对数据表中行记录的锁。 比如事务 A 更新了一行而这时候事务 B 也要更新同一行则必须等事务 A 的操作完成后才能进行更新。 在 InnoDB 事务中行锁是在需要的时候才加上的但并不是不需要了就立刻释放而是要等到事务结束时才释放。这个就是两阶段锁协议。 事务 B 的 update 语句会被阻塞直到事务 A 执行 commit 之后事务 B 才能继续执行。 一定知道了事务 A 持有的两个记录的行锁都是在 commit 的时候才释放的。若行锁不是在 commit 之后被释放而是在该语句执行完就被释放则不会出现事务 B 被锁住。 如果你的事务中需要锁多个行要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。 调整语句顺序并不能完全避免死锁。
死锁与死锁检测
并发系统中不同线程出现循环资源依赖涉及的线程都在等待别的线程释放资源时就会导致这几个线程都进入无限等待的状态称为死锁。 为了避免这个操作常用死锁检测。 发起死锁检测发现死锁后主动回滚死锁链条中的某一个事务让其他事务得以继续执行。将参innodb_deadlock_detect 设置为 on表示开启这个逻辑。 死锁检测算法复杂度很高 N个进程遍历N遍M个资源每个资源操作一次。则复杂度 O(M*N^2)。 假设有 1000 个并发线程要同时更新同一行那么死锁检测操作就是 100 万这个量级的。最终检测的结果可能是没有死锁但是这期间要消耗大量的 CPU 资源。
何时会死锁检测
每条事务执行前都会进行检测吗 并不是如果他要加锁访问的行上有锁他才要检测。 一致性读不会加锁就不需要做死锁检测 并不是每次死锁检测都都要扫所有事务。比如某个时刻事务等待状态是这样的 B在等A D在等C 现在来了一个E发现E需要等D那么E就判断跟D、C是否会形成死锁这个检测不用管B和A。 死锁检测其实就是算法环的检测不必每次遍历一遍当前事务只需要判断事务链表中每加入一个新事物后是否有环的生成有就形成死锁。这个方法和leetcode的链表中的环检测应该是一个道理。
如何避免高量级的死锁检测
为了避免这个问题一般来说有两种方法 1、果你能确保这个业务一定不会出现死锁可以临时把死锁检测关掉。一旦发生死锁现象则会出现超时(50s) 2、控制并发度: 1、对于相同行的更新在进入引擎之前排队。 2、减少行更新锁冲突的方法将单行拆成逻辑上的多行 练习
如果你要删除一个表里面的前 10000 行数据有以下三种方法可以做到 第一种直接执行 delete from T limit 10000; 第二种在一个连接中循环执行 20 次 delete from T limit 500; 第三种在 20 个连接中同时执行 delete from T limit 500。 方案一事务相对较长则占用锁的时间较长会导致其他客户端等待资源时间较长。 方案二串行化执行将相对长的事务分成多次相对短的事务则每次事务占用锁的时间相对较短其他客户端在等待相应资源的时间也较短。这样的操作同时也意味着将资源分片使用每次执行使用不同片段的资源可以提高并发性。 方案三人为自己制造锁竞争加剧并发量。
主备一致性
备库为什么要设置为只读模式
有这样几点考虑
1、有时候一些运营类的查询语句会被放到备库上去查设置为只读可以防止误操作
2、防止切换逻辑有bug比如切换过程中出现双写 同时写两个库(A、B 造成主备不一致
3、可以用 readonly 状态来判断节点的角色
备库设置为只读如何与主库保持同步更新
readonly的设置对于super权限用户是无效的。用于同步的线程就拥有super权限。
A到B的内部流程如何
主库接收到客户端的更新请求后执行内部事务的更新逻辑同时写binlog
备库B与主库A之间维持一个长连接。主库内部有一个线程专门用于服务备库B这个长连接。
一个事务日志同步的完整过程
1、备库B通过change master命令设置主库A的IP、端口、用户名、密码以及请求binlog的起始位置文件名日志偏移量
2、备库B执行start slave命令备库启动两个线程io_thread、sql_thread。io_thread负责与主库建立连接
3、主库A校验完用户名、密码后按照备库B传过来的起始位置读取本地的binlog然后发给备库B
4、备库B拿到binlog后写到本地文件称为中转日志(relay log)
5、sql_thread读取中转日志relay log 解析日志里的命令并执行
binlog内容是什么
在解释内容之前需要知道binlog的格式。
binlog有三种格式statement 、row、mixed
statement
binlog_formatstatement 时binlog 里面记录的就是 SQL 语句的原文 statement格式的binlog的缺陷有个缺陷
主备使用的索引可能是不一致的最终导致执行删除时删除的数据不一致。
**row **
row 格式的 binlog 里没有了 SQL 语句的原文而是替换成了两个 event Table_map和Delete_rows.
1、 Table_map 用于说明操作的表是test库的表t
2、Delete_rows 用于定义删除的行为
当binlog_format rowbinlog里面记录了真实删除行的主键id这样binlog传到备库去的时候肯定不会出现主备删除不同行的问题
mixed
mixed格式用于哪些场景呢
statement格式可能会导致主备不一致所以要使用row格式
row格式比较占空间同时也更要耗费IO资源影响执行速度
所以采用这种方案采用mixed格式MySQL自己会判断这条SQL语句是否可能引起主备不一致如果可能使用row格式否则使用statement格式。
row格式对于恢复数据有何好处
现在越来越多场景要求使用row格式的binlog可以从delete、insert、update三种sql语句角度看待这个问题。
使用delete语句row格式会把被删除的行的整行信息保存。所以删错之后只需要把binlog记录的delete语句转成insert就能恢复了。
使用insert语句row格式会记录所有的字段信息。所以插入错误的时候只需要把binlog记录的insert语句转成delete语句就能恢复了。
使用update语句binlog会记录修改前整行的数据和修改后的整行数据。所以如果update误执行只需要把event前后的两行信息对调再去数据库执行就能恢复数据了。
M-M结构的循环复制问题以及解决方案
M-S结构 M-M结构
图1是M-S结构但是现在常用的是M-M结构M-M结构区别在于节点A与节点B总是互为主备关系所以在切换的时候就不用修改主备关系了。
M-M存在循环复制问题
在节点A更新一个语句把生成的binlog发给节点B。
节点B执行完更新语句后也会生成binlog。
如果A同时为B的备库A会把节点B新生成的binlog拿过去执行。节点A和B之间会不断循环执行这个更新语句。
解决方案
已知MySQL在binlog中记录了命令第一次执行所在实例的server id。
1、规定两个库的server id 必须不同。若相同则不能设定为主备关系
2、备库接到binlog生成与原binlog的server id相同的新的binlog
3、每个库在收到从自己的主库发过来的日志后先判断server id如果和自己的相同表示这个日志是自己生成的丢弃这个日志。
所以使用M-M结构的日志执行流程如下
1、从节点A更新的事务binlog里记录的都是A的server id
2、传到节点B执行一次后节点B生成的binlog的server id 也是A的server id
3、再传给节点AA判断这个server id与自己的相同不处理这个日志 如果能够保证业务代码不会写入重复数据就可以继续往下看。 如果业务不能保证那么必须创建唯一索引。
关于查询能力
普通索引和唯一索引在查询能力上是没有很大差别的。 如select id from T where k5 1、普通索引查找到满足条件的第一个记录5500后需要查找下一个记录直到碰到第一个不满足k5条件的记录。 2、对于唯一索引由于索引定义了唯一性查找到第一个满足条件的记录后就会停止搜索。
InnoDB的数据按照数据页来读写每一个数据页大小默认为16KB. 对于普通索引来说查找k5的记录该记录所在的数据页都在内存里无非就是多做一次 查找与判断下一条记录的操作。 当然如果刚好k5这个记录在数据页的最后一行那么就得读取下一个数据页这个会稍微复杂一点。
关于change buffer
需要更新一个数据页时如果数据页在内存中就直接更新。 如果这个数据页在磁盘中InnoDB会将这些更新操作缓存在change buffer中这样就不需要从磁盘中读这个数据页了。 在下次查询需要访问这个数据页的时候将数据页读入内存然后执行change buffer中的关于这个页的操作。 change buffer 优点 将更新操作先记录到change buffer 减少读磁盘语句执行速度会提升。 数据读入内存会占用buffer pool使用change buffer可以避免占用内存提高内存利用率 change buffer 缺点 1、唯一索引的更新不能使用change buffer 2、change buffer的主要目的就是将记录变更动作缓存下来在一个数据页merge之前change buffer上记录越多收益越大 如果一个业务的更新模式是写入后马上做查询这样不会减少IO访问反而增加了change buffer的维护代价。
关于写能力基于change buffer
普通索引在不需要立即读时候可以很好的应用change buffer所以大部分场合建议使用普通索引。 如果在更新之后马上伴随这个记录拆线呢那么建议关闭change buffer。 redo log 主要节省的是随机写磁盘的IO消耗change buffer 主要节省的则是随机读磁盘的IO消耗。
MySQL索引底层原理理解以及常见问题总结
二叉查找树为索引
二叉树的key为col2value为索引所在行的磁盘地址。 但如果拿col1来作为key的话会发现二叉搜索树退化成链表。
红黑树为索引
仍然以col1作为索引key发现找6只需要查找3次。比二叉查找树更加合适一点 当表中有1百万行数据时这棵树的高度会越来越大。如果我们查找的元素在叶子节点查找次数会非常多。
B树作为索引
可以在树的横向上做文章每个节点原本只存储一行数据的地址现在可以修改为存储多行数据。因为树的高度越多说明IO操作越多导致与磁盘的交互越多。 B树 叶节点具有相同的深度叶节点的指针为空。 所有的索引元素不重复 节点中数据索引从左到右递增排列
B树作为索引
B树 非叶子节点不存储data只存储索引这样可以放更多索引 叶子节点包含所有索引字段。 叶子节点用指针连接提高区间访问性能。
也就是说在叶子节点存储了完整的元素然后把一些处于中间位置的索引元素提取出来作为非叶子节点。 MySQL设置默认节点大小为16kb一个bigint为8byte一个指针为6byte。所以一个节点最多能存16kb/14b 1170。 再假设叶子节点一个元素占空间大小为1kb。 如果全部节点存储了满了h 3的时候一共能够存储1170 * 1170 * 16 21902400这样可以存两千多万个数据了。 以下面为例 注意整个树都放在磁盘中每次load一个节点进入内存。一般来说先从根节点开始load。 我们现在要找6。比对根节点的36大于3向右比较发现6大于5于是从5右边的指针找到下面一层的节点. 然后把这一层的节点从磁盘里面load到内存中。 我们还可以看到最底层的节点之间会有链表相连。
MyISAM存储引擎索引实现
注意存储引擎是用来形容数据库中的表的。 MyISAM索引文件和数据文件是分离的。 我们使用查询语句
select * from ... where Col1 49;
首先查找是否是索引字段如果是就从MYI文件中的B树里面去定位到这个元素。key存储的是索引元素data存储的是索引元素所在的那一行的磁盘地址指针。拿到指针后去MYD文件定位。
InnoDB存储引擎索引实现
索引和数据放到了同一个文件中.ibd文件。 叶节点包含了完整的数据记录而不只是一个地址指针。
常见问题
聚集索引与非聚集索引
InnoDB就是聚集索引索引和数据文件合在一起。 MyISAM是非聚集索引索引和数据文件分离。 非聚集索引要查找两次一次找到指针地址一次根据指针地址找具体数据。 聚集索引只需要查找一次直接找到具体数据所以效率要更高。
InnoDB基于主键索引和普通索引的查询有什么区别
如果语句是 select * from T where ID500即主键查询方式则只需要搜索 ID 这棵 B 树 如果语句是 select * from T where k5即普通索引查询方式则需要先搜索 k 索引树得到 ID 的值为 500再到 ID 索引树搜索一次。这个过程称为回表。 也就是说基于非主键索引的查询需要多扫描一棵索引树。因此我们在应用中应该尽量使用主键查询。
InnoDB主键索引为何是整型的自增主键
自增主键的使用关于存储和性能 InnoDB必须要有主键而且推荐使用的是整型的自增主键。 因为数字好建立索引方便比较而且相比较于字符串类型占用的空间更小。 关于自增由于底层叶子节点是递增排列的如果此时主键是递增的那么新插入的元素就显然在叶子节点的最右边。 如果主键不是递增的插入一个新的元素可能就会在叶子节点链表中间某处。B树的结构调整就十分巨大了可能上层的非叶子节点的索引值要修改。 例如这里我们插入8 树的结构发生了很大变化直接裂开。 自增主键的插入数据模式每次插入一条新记录都是追加操作都不涉及到挪动其他记录也不会触发叶子节点的分裂。
何时使用业务字段作为主键呢
只有唯一的索引而且该索引为唯一索引。由于没有其他索引所以也就不用考虑其他索引的叶子节点大小的问题。 直接将这个索引设置为主键可以避免每次查询需要搜索两棵树。
哈希与B树
哈希查找某个key很快但是不支持范围查找。 B树用到范围查找就很方便了。叶子节点从左到右是一个递增的趋势。并且叶子节点之间通过指针相连所以不需要再返回到上层索引中寻找。如果我们要找大于20的元素那么只要在最底层20元素的右边进行遍历即可。 如果是小于某个元素的情况就是从底层叶子节点的左边开始一直包含到边界即可。
“N叉树”的N值在MySQL中是可以被人工调整的么
1 通过改变key值来调整 N叉树中非叶子节点存放的是索引信息索引包含Key和Point指针。Point指针固定为6个字节假如Key为10个字节那么单个索引就是16个字节。如果B树中页大小为16K那么一个页就可以存储1024个索引此时N就等于1024。我们通过改变Key的大小就可以改变N的值 2 改变页的大小 页越大一页存放的索引就越多N就越大。
数据页调整后如果数据页太小层数会太深数据页太大加载到内存的时间和单个数据页查询时间会提高需要达到平衡才行。
