MySQL学习整理-码农进阶之路(二)

二：MySQL基础架构

2.1硬盘

机械硬盘由磁盘片、磁头和磁头臂等组成，一次IO的流程大致是寻道[磁头臂移动]->旋转[获取扇区]->读取，一次大概9ms，主要开销在寻道和旋转上，读取1%不到。

固态硬盘：page[4k]-block[64/128]-plane[n]-die-闪存片-SSD[n]

写放大问题：SSD写入是page级别，但擦除是block级别，每次写入必须是全新的page，好处是避免了磁盘碎片，因为闪存必须擦除才能写，因此需要将block里的有效数据移到新块上，再擦除当前整个块才能写，这种数据的移动重写被称为写放大问题。当固态的可用空间变少时，问题越严重，固态硬盘的寿命和急剧降低。因此固态硬盘的空间利用率比机械硬盘低，固态硬盘闲置的空间越大越好，固态硬盘存储过满可能会发生不可预料的数据丢失问题。

预读和局部性原理：因为数据存取是以扇区为单位的，数据集中存储时一次能够读取到更多需要的数据。时间局部性：一般用到的数据不久之后会再次用到。因此计算机会把集中存储的数据即局部变量加载到cpu缓存以提高性能。

顺序IO和随机IO：机械硬盘的顺序IO大概是随机IO的百倍，固态硬盘虽然快的多顺序IO也大概是随机IO的4-10倍。因此查询应减少随机IO以提高性能。

页[4k-16k]：页是操作系统管理存储器的逻辑块，一般为4k，这也是我们重装系统时会有所谓4k对齐的原因。InnoDB的B+树页节点是16k，相当于4个存储页，这4个页一般都是有序的，因为顺序IO更快，所以mysql应该尽可能顺序读写，因为B+树的有序性，因此主键自增以保证写的顺序是很有必要的。

2.2 B树 B+树 跳表

B树[MongoDB WiredTiger]：每个子节点存储数据，查询时不用到叶子节点就可获取到数据，相比较B+树单条查询更快，范围查询较慢。

        mongo作为非关系型的文档数据库，单条记录查询原比遍历更常见，B树查询一条数据的平均随机IO要比B+树快[B+树每次都要到叶子节点]，因此mongoDB的WiredTiger引擎选用了B树作为索引底层的数据结构。

B+树[MySql InnoDB]：从上文我们知道B+树的页节点大小是16k，磁盘页大小为4-16k，可以充分利用磁盘预读功能减少IO，且B+树是一颗自下向上生长的树，是专为磁盘读写设计的数据结构。

        B+树叶子节点有指向后面的指针，对于关系型数据常见的范围查询非常快。B+树数据存储在叶子节点，非叶子节点不存数据因此B+树的扇出[阶/叉]非常大，一般三层的B+树能存2000w+行数据，足以应付绝大多数存储场景。

计算过程：假如主键用的是bigint 8字节，mysql索引6字节，则一个页节点下最多有16*1024/(6+8)≈1170个指针。假如一行数据1k，则一个页可以存16条数据，则三层B+树：1170*1170*16=21902400。(1170*1170*16/1024/1024=20.88G)

跳表[redis zset]：跳表具有较高的综合性能[增删改查O(logn)]，通过对单链表随机生成索引的方式近似实现了二分查找，有效平衡了执行效率和内存的消耗。

        因为redis是在内存中存储数据，不涉及IO，而B+树的页分裂和页合并比较复杂，插入删除开销比较大，因此redis使用跳表实现了有序表zset。Mysql的MyRocks存储引擎也使用了跳表，对CPU更依赖，在小内存下myRocks性能表现较好，大内存下InnoDB则表现更好。

评测地址：MyRocks Performance - Percona Database Performance Blog

中午翻译：MyRocks vs InnoDB 的性能基准测试_老叶茶馆-CSDN博客

2.3 MySQL逻辑架构及查询流程

mysql分三层结构分别是：连接层、server层、存储引擎层

连接层：认证授权、使用线程池管理连接[数据库连接池]BIO模型

server层：mysql主要功能区，SQL解析、优化器、查询及结果处理等

存储引擎层：插件式配置，负责和磁盘交互读取数据，各个存储引擎自行实现server层的API接口。

流程：客户端 - 权限认证 - 数据库连接池 - 查询 - 缓存[mysql8已默认禁用] - 解析器[解析树] - 预处理器[解析树] - 查询优化器[执行计划] - 存储引擎[API调用] - 磁盘[文件与日志系统,数据]

2.4 Mysql文件

建表文件：

        .frm 存储表结构

        .idb InnoDB数据及索引文件

        .MYI .MYD MyISAM的索引文件及数据文件

日志文件：

        error-log                  错误日志

        slow-query-log        慢查询日志

        query-log                查询日志  

三：事务和MVCC

MVCC只在REPETABLE READ和READ COMMITED两个隔离级别下工作，因为READ UNCOMMITED总是读取最新的行，而不是符合当前事务版本的数据行，而SERIALIZABLE则会对所有读取的行都加锁。

3.1 两个落盘配置

sync-binlog(0,1,n) bin log刷盘频率

        0 不主动刷盘，由操作系统决定

        1 每次提交事务刷盘

        n n是数字代表批量事务刷盘，n越大性能越好，数据丢失的概率越高

innodb_flush_log_at_trx_commit(1,0,2) redo log刷盘频率

        1 每次提交事务提交写入磁盘，写入成功事务才能算成功

        0 InnoDB主线程每秒执行一次刷盘

        2 写入到操作系统缓冲区，由操作系统决定刷盘，如果mysql挂了操作系统没挂则没有问题。

为保证事务一般两个都配置为1，也因此mysql性能会下降。

3.2 事务相关日志文件

bin-log[主从复制]：二进制日志，是server层实现的。所有的引擎都可以使用binlog，记录的逻辑日志，类似于redis的AOF是追加写的全量日志，无法用于判定数据是否落盘。

undo-log[事务MVCC]：回滚日志，存储事务的上一个版本数据，用于保证事务的原子性。第一次对表的增删改后分配事务版本号trx_id[自增row-id]，当row-id是256的倍数时会持久化到表空间的TRX-SRS里，

redo-log[崩溃恢复]：前滚日志，存储当前事务修改后的结果，用于数据库崩溃恢复，保证了事务的持久性。innoDB引擎自己实现的独有的，记录的物理日志，redo文件只有两个是循环写日志。可以判定哪些数据落盘哪些没有落盘。

3.3 版本链、ReadView和MVCC

3.3.1版本链

要了解版本链先看看InnoDB表的两个字段[隐藏列]：

        trx_id：事务id、版本号、因为是自增的，可以当作是程序的执行时钟，只会存储在聚簇索引里。

        roll_pointer：指向上一条undo log的指针。

InnoDB事务每修改一次记录都会生成一次旧值的undo log,随着更新的次数增多所有的版本就会

在roll_pointer指针下串起来形成一个链表，就被称为版本链。

3.3.2 ReadView的4个属性

        m_ids：活跃事务列表，即创建ReadView时trx_id的事务创建了但未提交。

        min_trx_id：最小[早]活跃事务，即小于这个事务的都提交了

        max_trx_id：下一个待分配的事务id，如果目前最大事务id是4，则该值为5

        creater_trx_id：创建改readView的事务id，即自己

3.3.3 MVCC：

流程：mvcc判定修改是否可见的方式是：从最新[大]版本链向下遍历，获取第一个符合RewdView判定规则的行，若均不符合则返回查询结果为空。

判定规则：

        1.若trx_id = creater_trx_id，则说明是自己修改的值，事务可见，返回该行。

        2.若trx_id < min_trx_id，则说明该事务在当前事务之前提交了，事务可见，返回该行。

        3.若trx_id >= max_trx_id，则说明该事务是在当前事务之后创建的，事务不可见，继续下一个版本链的比较直到结束。

        4.若min_trx_id <= trx_id < max_trx_id，则需要判定trx_id是否在m_ids里，若在则说明该事务在本事务创建时是活跃事务[未提交]，则事务不可见，否则可以访问

3.3.4 MVCC在不同隔离级别下的区别：

READ COMMITED：该隔离级别下，每次查询都会新建一个ReadView，即获取最新的事务提交状态，根据ReadView规则从版本链下获取符合要求的记录。

REPEATABLEREAD：只创建一次ReadView，在事务执行期间ReadView的相关记录保持不变，即对新的事务提交无感知，因此能做到可重复读，顺带还解决了幻读问题。

3.3.5 dml操作的过程：

        delete:

        delete mark 为1时，代表数据行被删除，先给行打标志位，在移到删除链里

        update：

        不更新主键：两次空间大小一致就地直接更新，空间大小不一致则标记删除原纪录再插入一条新的记录。

        更新主键：删除旧记录，创建一条新记录。

        先删除后创建这种模式会产生两条undolog

3.4 commit前后

此处按照双1配置 sync-binlog=1，innodb_flush_log_at_trx_commit=1

1.begin

2.update tb set a = 1 where id = 1

3.buffer pool           //磁盘读取数据a=0，放入缓冲区

4.undo log[a=0]      //记录undo日志，IO刷盘，顺序写，很快

5.a = 1                    //修改a为1

6.redo log buffer [a=1]  //记入redo log buffer

7.redo log               //redo log 刷盘

8.prepare               // XA协议支持,server层标记prepare，表示完成提交的准备工作可以提交

9.commit               //客户端commit 

10.bin log                //bin log刷盘，redo log刷盘成功才会走这里

11.after sync           //主从同步 master-slave

12.释放锁，undolog事务版本状态改为提交

13.commit ok         //客户端提示修改成功

3.5 InnoDB的三大特性

双写缓冲区[double write]：

在内存页中修改数据刷盘之前，会先把数据写到双写缓冲区，再写到磁盘页，因为是顺序写所以开销比较小。

写磁盘页比较慢如果写的过程中发生崩溃，则会使用双写缓冲区的文件来恢复数据页，如果双写缓冲区的写入也失败了，则会去磁盘读取数据配合redo log恢复数据，该特性大大减少了redo log记录数的IO情况，使redo log可以更轻量级。

AHI[adaptive hash index]自适应hash索引 ：

InnoDB会根据访问的频率和模式为热点页建立hash索引以提高查询效率。索引的建立是InnoDB对索引页查询的监控，通过缓冲池的B+树构建，建立速度很快，索引由InnoDB引擎自身创建并使用。自适应hash索引只适用于等值查询，如where a = 1，范围查询查找仍需要扫描B+树。

BufferPool：

InnoDB引擎自己的缓存，存放读取的缓存页，使用三个链表来管理，默认128M，可以配置chunk来动态申请空间。这里面可以存的有：索引页、数据页、undo页、insert buffer、AHI、锁等。

        free链表：存空闲缓存页的控制块，相当于栈-堆的栈区，以表空间+页号作为key

        flush链表：存脏页，用于数据刷盘

        lru链表：缓存空间管理缓存淘汰策略，类似jvm的新生代、老年代分两个部分。热区域63%，冷区域37%