MySQL 知识体系

一.MySQL 如何实现读的高性能
MySQL 读的高性能是通过存储结构+内存缓存+索引机制实现的
1.索引结构：B+数，减少查询磁盘IO次数，叶子结点存全部数据+双向链表， 非叶子节点存键值，一个16kb 的page页，从根到叶子仅3-4次磁盘IO

2.辅助索引与少回表：辅助索引叶子存的是主键值，二级索引查询需要回表，
覆盖索引：查询的列全在索引里，减少回表
索引下推：查询过滤条件下推到索引遍历的过程中完成，减少回表行数；

3.内存缓存：独立于操作系统的缓存池，数据页和索引页都缓存在里面
读请求先查 buffer pool，命中就直接返回
靠LRU变种算法淘汰冷数据
预读：检测到顺序读时，把后续相邻页提载入缓存
4.并发控制：MVCC机制，RC 和RR 隔离级别下，普通读走一致性快照读，读的是历史版本 ，不加锁，不阻赛，不被写阻塞；

二.MySQL 如何实现写的高性能与可靠性之间兼得

高性能：预先日志，更新数据时不直接改磁盘上的数据页
1.改内存里的Buffer Pool 数据页（标记为脏页）
2.顺序追加写redo log （记录 页上改了什么），写磁盘走顺序IO
3.返回成功， 脏页由后台线程异步刷盘，不阻塞事务

可靠性： redo+binlog + 两阶段提交
两阶段提交：事务提交时拆成两步，保证两个日志一致
redo log 写入 -> prepare状态
写binlog
redo log 标记 -> commit 状态
崩溃时按状态决定提交还是回滚， 当redo log 和binlog 读写好时提交， 当只写了redo log 还未写binlog 时回滚

三.为什么MySQL 的LRU 算法与常规的LRU 算法不一样

1.常规的LRU 有缓存污染问题
常规的LRU 使用双向链表 + 哈希表 实现，新插入的页放链表的头部，尾部淘汰
一条sql 全表扫描1亿行， 读取几十万个数据页，这些数据页全部被放到LRU 表头，大批量页把热点数据缓存掉。

2.InnoDB 的改良：分代（Young/Old区）
中点插入：新读入的页不插入链表头部，而是插入old区头部，Young 区值放 被确认是热的页
时间门槛晋升： old 区的数据停留超过一定时间后 （默认1秒） 再次被访问-> 晋升

四.有redo log 了为什么还需要doublewriter buffer
redo log解决数据丢了， 数据落后的问题，doublewriter 解决写坏了的问题，半页写会把页变成既非新也非旧的残缺状态，redo 的LSN重放前提是页完整，页被破坏之后， 必须靠双写预留的完整副本先还原，redo 才能继续工作；
半页写的产生：
InnoDB 系统页大小是16kb
操作系统和磁盘是以4kb扇区为单位写入的
一次刷16kb页，需要写4个扇区，不是原子的
redo log 工作原理：
redo log 的工作原理是基于页的LSN做增量重放，磁盘上的页本身要是完整的， 校验合法的，而半页写的页页头LSN 可能也顺坏，没法跟redo 的LSN比较， chencSum 校验失败没法确认这个页的状态，redo log 没法保证页的完整性，所以需要从双写区县拿副本还原。

五.binlog，redolog 都是用于异常恢复，有什么区别

本质区别：
redo 是物理日志:记录"页 LSN 从多少到多少,某个偏移写入什么"。重放是页级操作,快、精确、只认"同一份数据文件"。但它绑定死了具体的数据页,换个实例根本没法用。
binlog 是逻辑日志:记录"这个事务做了什么"(statement 记 SQL,row 记每一行的变更前后)。可以拿到任何实例上重放,但从库重放时有语义依赖(所以 row 格式比 statement 安全)。

恢复场景：
实例崩溃 → 重启 → 从最后一个 checkpoint 扫描 redo → 把"已提交但没落盘的修改"重放到数据页
恢复目标:回到崩溃那一刻的"最新一致状态"(只能前进,不能后退)

时间点恢复(binlog):
误删一张表 → 全量备份恢复到某个时间点 → 用 binlog 重放,跳过误删那条 → 回到误删前的状态
恢复目标:回到历史任意点(可以后退,可以跳过某条 SQL)

六.MySQL 单表数据量多少合适，为什么
1.推导数据量
假设主键是bigint， InnoDB一页16kb
非叶子结点大小为 主键8B +指针6B = 14B ，一页可存索引项 = 16384 / 14 = 1170个
叶子结点存整行，假设每行1kb 个，一页可以存16行
B+树可以存储的行树 = 1170 16 1170 = 2000万行
2.查询性能因素
Buffer Pool 命中率，热点数据可以放得下内存，查询还是内存命中
走索引+是否回表：全表扫描或大量回表，才会导致查询缓慢
逐渐类型：自增bigint 页填充率高， uuid，主键会导致页分裂，碎片问题
3. 何时分表
1.单表数据大于2000万，热点数据放不进buffer pool
2.写瓶颈：写入TPS到顶
3.大表维护成本：DDL 加列锁表时间长，备份恢复慢，从表同步慢，需要手动数据归集

七.MCVV 在RR 和RC级别下的区别
区别：
ReadView 读诗图的生成时机不通， RC 每次SELECT 都重新生成一个ReadView ；RR 只在事务第一次SELECT 时生成一次，整个事务复用；
MVCC机制：
1.版本链(undo log 串起来的)：每一行数据在 undo log 里保留历史版本,每个版本都记录写它的事务 ID(trx_id),从新到旧串成链表。
2.快照读:普通 SELECT 不加锁,读的是"某个可见版本",不阻塞也不被阻
3.ReadView(读视图)：
记录： 未提交事物列表
未提交事务列表中最小的id
生成readView时，下一个需要分配的事务id
创建readView的事务自己的id
未提交以及事务id 大于当前事务id时，当前ReadView 视图不可见 ， 否者可见
RC ，每一次SELECT 都是新试图，不可重复读
RR，每一次SELECT都是第一个ReadView 视图，后面执行的事务不可见，可重复度