MySql

MySQL的BufferPool和ChangeBuffer是如何加快数据读写速度的

bufferpool：

在MySql启动时会生成一片连续的物理内存用来缓存数据这部分空间就叫bufferpool；

• 在生产坏境中一个bufferpool会拆分成多个instance提高响应速度
• 每个instance是由多个chunk相同大小的chunk组成的，它是比较大的物理内存块，默认值128m
• instance是20个g，chunk是128m，一个instance包含16个chunk
• 每个chunk都是由blocks（被称为控制块数组）数组和frames（缓存页数组）数组构成的

• 数据怎么知道有没有被加载熬缓存页

bufferpool提供了一个hash表，key：表空间号+页号；value 就是缓存页里边的数据；

如果要访问数据先在bufferpool的hash表看有没有数据，没有就从free链表中，选出一个空闲的缓存页，然后把硬盘上对应的页，加载到这个缓存页中，如果这时候修改了 BufferPool中的某个缓存页中的数据，就和硬盘中的数据页不一致了，这样的缓存页 被称为“脏页，最简单的做法就是 每发生一次修改，就立刻同步到硬盘中对应的页上，但频繁给硬盘写数据，影响性能，这个时候呢就诞生了flush链表

flush 链表用来存储，哪些页需要被同步到硬盘上，有一个cant来统计需要同步到硬盘上的页的数量，flush 链表是逐渐增加的，当数据达到两个脏页或更多脏页，可以异步的批量的同步到硬盘上

---

预加载原理

1. 为了加快读写速度inodb有使用预加载机制，默认的加载方式称为线性预读（顺序的加载了某个区的56%的数据，就会异步的把下一个区中，全部的数据都加载到BufferPool中）
2. 另外一种预加载模式 叫做“随机预读：默认是关闭的，表示的含义只要你读取了这个区中连续13个页，不管是否顺序读，只要这13个页是连续的，就会把这个区剩余的页全部 给你读取过来
3. 预加载有利有弊如果用到了会提升sql效率，如果没用到就浪费了内存空间，比如出现了全表扫描的情况，通过这种方式加载的数据会替换到原来的，但这些数据也有可能下次再也用不到了。

所以InnoDB设计了LRU链表

1. LRU列表按照一定的比例裁成了两段，一段被称为新生代用来存储使用率非常高的缓存页，热数据会保存在新生代中。另外一部分：老生代用来存储那些使用频率不高的页。
2. 如果select一批数据，这些数据首先会被加载到老生代的链表头部
3. LRU链表保存的是控制块的信息，每个控制块会指向具体的缓存页，这样即使发生了全表扫描，这些数据也只会被加载在老生代，不会影响热点数据。
4. 在老生代的数据如果超过1000毫秒后还被使用，就会提升为新生代，缓存页的数据不会移动，只是LRU双向链表的指针指向发生改变，这个数据会放到LRU链表的头部（为了解决每次放到头部开销大。inodb的优化：位于整个新生代列表的1/4之后这部分数据才会被提升到头部

ChangeBuffer是如何加快数据读写速度

1. ChangeBuffer是BufferPool的一部分
2. 如果现在我要更新非唯一索引的page100页的数据，这个数据不在BufferPool中，这个时候inodb并不会把这个数据加载到缓存中而是将这个要修改的操作把它记录在ChangeBuffer中并且会把这个记录更新给RedoLog，如果这个时候有又一个select查询他要查询 page-100 中的数据了，这个时候是需要把page100加载到BufferPoll中，但上一个事务做了修改，这时就会利用 ChangeBuffer 里的操作日志生成一个正确的结果展示给用户也就是这里的 merge 操作会修正数据给用户提供正确的结果
3. 这样做的好处是并不是每修改一次非唯一索引的数据，就写一次硬盘相当于把原来应该是两次I0合并成了1次
4. 非唯一索引的变更才会用到BufferPool

---

MySQL的主键索引、普通索引和联合索引

单个数据页介绍

1. inodb的的数据会存储在一个16kb的页中，一个页数据到了16kb会增加新的页，inodb有预读机制，每次会往内存中提前预存一个页16kb数据，一次也最多往硬盘上写回16kb数据。
2. 一个页中的数据存储空间分成7部分，有些部分占用的字节数是固定的，当我们insert一条数据的时候会从Free Space的剩余空间中申请一条记录大小的空间把它划给user records，因此 当我们在insert数据的时候free space和user records的空间是此消彼长的关系
3. 数据页中的数据是按照主键值升序排列的，是串联在一起组成一个单向链表，inodb会为每个数据页都自动添加两条记录一条叫做infimum最小记录，另外一个是Supermum最大记录，在查数据的时候会按照分块查找，他会把每4条记录划分成一个分组，每个分组会选择一个最大的记录会指向一个槽，每个槽代表着这个分组中最大的主键值，并且这些组件值是按照从小到大升序排列的，所以在查询数据的时候先确认数据在哪个块，再在块中顺序遍历
4. 

多个数据页之间数据查找B+树（主键（聚簇）索引）

1. 上面是在一个数据页中查找的情况，如果一个页数据空间不足了这个时候 会再分配一个新的数据页，多个数据页之间会有上页或者下页类似于双向链表的方式进行链接，但是你要查询inodb是不知道你要查询的页的编号是多少，因此就有了B+树；
2. 
3. 插入数据:假设现在要插入一条主键等于4的数据，在第一个页中已经有4条数据，假设每条是5kb，那么第一个页只能16kb空间只能有这么3条，那么现在要插入的这条数据就得在一个新的数据页，而上一个数据页中的数据主键有1，2，5，我现在要插入的这条数据主键是4，由于下一个页的数据记录主键值必须大于上一个页中记录主键值，（因为在进行数据查找的时候每个页会分组每个组的主键是当前组中数据的最小值）所以现在4插入的时候会做一次移动上一个页中主键5会移动到下一个页，主键4插入到上一个页，这个过程称为页分裂。
4. 页与页的编号在物理内存中不是连续的，它们只是通过双向链表关联，如果要通过主键值快速定位到数据所在的页，就需要给这些页做一个目录，每个目录项对应一个数据页号和数据页中记录的最小主键值；
5. 目录项中也是按照主键值进行升序排列的最后组成一个升序排列的单向列表，目录项中不包含完整的数据记录，之有主键和数据页号
6. 
7. 一个目录项也只有16kb，如果超过了会生成更多目录项，而目录项多了有一个更大的目录页来记录这些目录页记录的仍然是页编号和当前页最小主键值不存储完整数据，这就是B+树，实际数据都存放在最顶层的叶子节点，存放目录项的就叫非叶子节点，最上面就是根节点
8. 
9. 如果现在要查询主键等于61的数据需要经过3次io从根节点，再二分查找，再到根节点找到完整数据
10. 

普通索引（二级索引）

1. 通过CREATE INDEXidx nameON t user (name);创建普通索引。普通索引只存储叶子节点索引列和对应的主键值，主键索引存的就是完整的数据，目录项也变成了，变成了按照索引列升
2. 
3. 
4. 如果我现在要查找name='K'的记录，首先会在普通索引B+树中找到name='K'的主键id，然后再到聚簇索引中主键ID等于15的完整记录，这个过程称为回表查询
5. 

联合索引

1. 假设我们为name列和age列创建了一个联合索引，联合索引的叶子节点存储的是name age和主键值每个目录项他都是由name、age和页号组成的；
2. 在数据页中是按照name进行顺序排列的，如果name相同就根据age排序，所以联合索引必须要遵循最左匹配原则，因为如果按照age进行查询全局是无序的，就只能按照顺序遍历全表扫描去查询了根本就用不上这个联合索引
3. 联合索引的查询方式是跟普通索引一样的，如果再查询到了多条相同的数据就会进行多次回表查询
4. 

从原理和经验层面讲解MySQL单表上限2000w行不合理

MySql两百万条数据计算方式：

---

现在硬件升级了，内存空间也比较大了，再加上BufferPool中的LRU+大内存就已经解决了2000万条数据上限的问题。

---

MySQL两大内存BufferPool、RedoLogBuffer和三大日志binlog、redolog和undolog

数据的更新流程（redolog和undolog）

1. 在执行更新语句时客户端会将更新语句发送给执行器由执行器去执行，内存读写操作比硬盘要快的多所以inodb将一切读写操作在内存中处理，这个内存的缓冲区就是BufferPool。
2.  由于innodb是以页为单位和硬盘进行数据交互的，虽然要修改的数据是一个页上的几个字节，但也需要将硬盘中整个页的数据加载到BufferPool中在其中进行修改，为了支持数据回归在修改前会把旧数据复制到undolog文件中之后才能修改如果sql有问题还可以基于undolog回滚到之前的版本，然后BufferPool中修改的这个缓存页就是脏页（缓存页和硬盘中数据不一致时这个缓存页就称为脏页），等更新到硬盘之后缓存页就是干净的页。
3. 虽然BufferPool效率高，但存在数据丢失的风险，如果数据写入硬盘之前BufferPool崩溃了数据就会丢失，由此就诞生了redolog，我们在执行update语句更新BufferPool的时候，同时 也会将数据写到redo logbuffer，如果innodb_flush_log_at_trx_commit=2（表示：实时写，延时刷），那么在执行commit操作的时候会先将数据写到PageCache上，如果我现在又执行了一个update的语句，仍然是先更新BufferPool中的数据然后再把新数据写到 Redolog Buffer中，最后实时的把数据写到PageCache上（实时写），每隔1秒钟它会调用一个fsync()函数（延时刷），把PageCache中的数据批量的顺序的写到redolog日志中，这样就提高了redolog的写入效率。
4. 当把数据写到PageCache的时候，在执行updeat语句的时候也BufferPool中的数据写到硬盘上，最后才是执行fsync()函数。
5. redolog也没有完全解决数据丢失的问题：1.数据 已经被写到了Redolog文件中，但还没被写到硬盘的数据文件上这个时候BufferPool崩溃了数据不会丢失因为InnoDB会利用RedoLog文件中的数据对数据文件中的数据进行修复。2.如果我们在执行commit语句的时候，redlog buffer中的数据已经被写到了PageCache上，但是无论是PageCache 还是BufferPoo他们都还没有把数据写到对应的数据文件上（PageCache 写到Redolog,BufferPoo写到硬盘）这个时候PageCache和BufferPoo都崩溃，这时候数据库重启后会丢数据，所以在参数innodb_flush_log_at_trx_commit=2（表示：实时写，延时刷）的场景下并没有完全解决数据丢失问题，innodb_flush log_at_trx_commit=1（实时写，实时刷）才能保证数据不丢失。
6. 修改数据的流程是：执行器执行updeat在bufferpool中修改数据，同时将新数据写到redolog Buffer，然后调用fsync()将数据写到redlog，最最后把BufferPoo数据写到硬盘。
7. BufferPool：内存缓冲区inodo用来执行读写操作，在内存中效率高；RedoLogBuffer：修改数据时防止要修改的数据丢失会在redolog中保存一份；undolog：防止数据修改旧的数据丢失，在修改前将旧的数据复制一份在undolog中；binlog：执行错误sql语句后数据回滚，和时候数据日志记录；

binlog

1. 它就相当于SOL语句的二进制版本，里面保存的sql语句二进制格式，通常可以通过my2sql或者binlog2sql或者MyFlash工具生成回滚或者叫前滚的SQL语句，这样我们就可以挽回由于执行错误的sql语句而造成的数据丢失，比如我们执行的DELETE的操作，那么就会给我们反向的生成Insert语句把删除的数据补回来。
2. binglog也有不同的刷盘策略sync_binlog=0（默认值：就是当我们执行commit操作的时候它会先把数据写到 PageCache上最后在调用fsync()函数的时候才把数据写到redolog文件中，当把binlog写到这个硬盘上的时候紧接着就会完成最终的事务提交比时 就会把本次更新的binblog文件名以及这次更新binblog日志在文件中的位置都写入redolog文件中，同时加一个commit标志，这么做是为了保证r和b数据一致；
3. sync_binlog=1在执行commit操作的时候会强制的把binblog日志直接刷到硬盘上

---

使用自增ID或者UUID做MySQL的主键，雪花算法生成的主键存在哪些问题

自增ID

优点：有序，插入数据时性能高因为添加数据时mysql有固定的分区，如果顺序是连续的可以一直往一个库写，接着写另一个库；

缺点：分布式系统中在合并数据时可能会出现数据冲突，连续的id容易被抓取数据

1. MySQL自增ID：

   • 概述：MySQL自增ID是一种数据库自动生成唯一标识符的方式，通常用于表的主键（Primary Key）字段。
   • 特点：每当插入一条新记录时，MySQL会自动为该记录的自增ID字段分配一个唯一的整数值，且该值会自动递增。
   • 优点：简单、高效，适合大部分传统的数据库场景。由数据库本身维护，不需要应用层额外操作。
   • 缺点：分布式环境下可能存在唯一性和性能瓶颈问题，不适合需要跨多个节点生成全局唯一ID的场景。

UUID

优点：唯一

缺点：id太长不好读懂，没有顺序在mysql插入数据时会导致索引分裂插入效率低，并影响查询性能

1. UUID（通用唯一识别码）：

   • 概述：UUID是一种标准化的128位（16字节）长的标识符，通常以32个十六进制数字表示，被设计为在所有的计算机上都能够唯一地识别信息。
   • 特点：全局唯一，生成过程不依赖于中心节点，分布式系统中可以独立生成。
   • 优点：适合分布式系统，不依赖数据库生成，无需中心化的管理。
   • 缺点：占用空间大（128位），不易于人类读取和理解，排序性较差，不适合作为索引键。

雪花算法

1. 雪花算法（Snowflake算法）：

   • 概述：雪花算法是Twitter开发的一种分布式唯一ID生成算法，用来生成64位的唯一ID。
   • 特点：64位整数构成，分布式系统内生成唯一ID时不依赖于中心节点，具有一定的顺序性。
   • 结构：雪花算法将64位整数划分成多个部分，包括一个时间戳、机器ID、数据中心ID和一个自增序列。
   • 优点：高效、性能较好，适合在分布式系统中使用。
   • 缺点：需要依赖时钟回拨的解决方案，对时钟要求较高。

---

MySQL的MVCC原理，RR不能完全解决幻读问题的底层原理，Undo版本链

mysql事务如何实现隔离

多个id对一条数据做修改就会产生事物，就会有多个版本，MVCC是基于undolog版本链和readview一起实现的。

1. undolog的作用：在更新数据库数据前会先在undolog中记录，再更新数据库，方便之后数据回滚；
2. 每张数据库表都有两个隐藏字段，每当有一条数据对某条聚簇索引进行改动的时候都会把这个事务的事务编号复制给传trx_id隐藏列，另外一个隐藏列 roll_pointer负责指向UndoLog 中旧版本的数据，这样就能找到这条数据在修改前的信息；
3. 如果这个时候事务2也来修改同样也是将修改前的数据同步到UndoLog日志然后修改数据库表中的数据最后修改指针指向。接着还有其它事务也是同样的步骤，每一步执行完成之后它就形成了这样一个undolog的版本链，这个版本链就会帮我们实现mvcc；
4. 那么在做数据多版本查询时到底查询那个版本的数据，就涉及到mvcc的另一个机制叫做 ReadView就是读视图准确的说是一个内存结构的读视图，select 在做数据查询的时候都会生成-个ReadView它当中记录了UndoLog版本链里边的一些统计信息，所以在做数据查询的时候就不用去遍历UndoLog 版本链的每一条数据并且可以帮我们定位到UndoLog版本链中的哪一层；
5. ReadView中UndoLog版本链里边的一些统计信息包含：1.当前数据库中活跃的事务ID列表:m_ids：也就是说这些事务已经启动了但是还没有进行commit操作就被称为活跃的事务它是一堆事务的列表；2.这些事务集合中最小的事务ID也就是版本链末尾的事务ID；3.就是下一个要被分配的事务ID也就是说这些版本链中最大的事务ID的值+1；4.就是创建ReadView的事物的事务ID，创建好之后就可以根据一定的规则找到唯一可用的版本数据了；

RC（读提交）隔离级别

在读提交隔离级别下每次读数据都会生成一个read view这就意味着在同一个事务中多次查询同一条数据的时候前后两次数据的结果可能会不一致，因为我们在查询时会有可能某一个事务对这条数据做了修改并提交了事务。比如说我们在做第一次select查询它生成Readview的时候这个活跃的事务ID是{202,203,204}所以在这个时间线上没有提交的事务就是{202,203}以及创建Readview的事务204，然后现在现在最小的事务是202，最小事务之前的事务都是已提交的事务比如 现在已提交的事务是201那我们就可以按照这个版本链UndoLog中事务ID等于201的这条数据然后把对应的结果读一出来。

在读提交的隔离级别下每次select查询数据的时候都会重新创建一个ReadView因此 多次读取同一条数据的时候前后两次读取就会出现不同，因此是rc级别没有解决幻读，不可重复读问题。

RR（可重复读）

它是在事务开启的时候生成一个read view然后在整个事务的运行过程中都会用同一个 ReadView都会复用第一次创建的 ReadView并没有完全解决幻读问题

rc和rr隔离级别都被称为快照读他们都是基于 MVCC机制找到UndoLog版本链中唯-一个版本去读取数据，这就是多版本并发控制查询的含义

幻读

举例：现在只有一条名字是张三的员工数据，事务202在查询名字是张三的那条数据的时候，肯定只能查到一条，紧接着事务 201插入了一条名字是张三的数据新数据的ID是2003并提交了事务，现在就有两条叫张三的数据了，然后事务202做了凡是员工编号小于等于2020的员工我们都把他的薪资调整成1万（注意updeat是当前读，因为在执行修改前是需要查询出这条数据再修改）在执行当前读的操作时就需要重新生成ReadView了相当于现在rr要变成rc隔离级别了，因此这时我们会查询到事务201插入的最新的这条数据员工编号等于2003的这条数据，由于updeat和slelect他们两个是在同一个事务中所以我们读取到的他们的薪资都变成了一万，其实在执行where语句id小于等于2020的时候在这加了一个临键锁（锁范围是-无穷大到2020）所以这个锁会覆盖事务201里边的ID=2003的这条数据，所以这里我们会查询到id2003的这条数据，也就是说临键锁破坏了可重复读也就出现了幻读。

如果这个时候把where语句里边的<=改成>=那么这个时候临键锁的范围就会变成2020到正无穷大，这个时候就不会覆盖数据了，他就会复用前面的ReadView查询结果是一样的，没有出现幻读，最后当事务202 把事务都提交的时候会做相应的修改。同样的道理如果这个时候我把 >= 给它改成=也不会出现幻读，也就是说在rr级别下连续两个快照读中间它即使加入了当前读也不一定出现幻读这个时候需要判断 这个当前读的临键锁的范围是否覆盖其它事务中的数据

---

MySQL的B+树索引原理，B+树索引有哪些好处

b+树索引查找顺序：先将根节点加载到内存中，再通过根节点找到指向的一级索引加载到内存中，再根据一级索引找到数据存储的数据页，再将叶子节点数据页加载到内存再在内存中顺序遍历找到数据。如果不在当前数据页可以通过指针到下一个页查找。所以b+树基本只需要3次io就可以找到你要的数据。b+树也是一种用空间换时间的做法