一.MySQL 如何实现读的高性能MySQL 读的高性能是通过存储结构内存缓存索引机制实现的1.索引结构B数减少查询磁盘IO次数叶子结点存全部数据双向链表 非叶子节点存键值一个16kb 的page页从根到叶子仅3-4次磁盘IO2.辅助索引与少回表辅助索引叶子存的是主键值二级索引查询需要回表覆盖索引查询的列全在索引里减少回表索引下推查询过滤条件下推到索引遍历的过程中完成减少回表行数3.内存缓存独立于操作系统的缓存池数据页和索引页都缓存在里面读请求先查 buffer pool命中就直接返回靠LRU变种算法淘汰冷数据预读检测到顺序读时把后续相邻页提载入缓存4.并发控制MVCC机制RC 和RR 隔离级别下普通读走一致性快照读读的是历史版本 不加锁不阻赛不被写阻塞二.MySQL 如何实现写的高性能与可靠性之间兼得高性能预先日志更新数据时不直接改磁盘上的数据页1.改内存里的Buffer Pool 数据页标记为脏页2.顺序追加写redo log 记录 页上改了什么写磁盘走顺序IO3.返回成功 脏页由后台线程异步刷盘不阻塞事务可靠性 redobinlog 两阶段提交两阶段提交事务提交时拆成两步保证两个日志一致redo log 写入 - prepare状态写binlogredo 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 bufferredo log解决数据丢了 数据落后的问题doublewriter 解决写坏了的问题半页写会把页变成既非新也非旧的残缺状态redo 的LSN重放前提是页完整页被破坏之后 必须靠双写预留的完整副本先还原redo 才能继续工作半页写的产生InnoDB 系统页大小是16kb操作系统和磁盘是以4kb扇区为单位写入的一次刷16kb页需要写4个扇区不是原子的redo log 工作原理redo log 的工作原理是基于页的LSN做增量重放磁盘上的页本身要是完整的 校验合法的而半页写的页页头LSN 可能也顺坏没法跟redo 的LSN比较 chencSum 校验失败没法确认这个页的状态redo log 没法保证页的完整性所以需要从双写区县拿副本还原。五.binlogredolog 都是用于异常恢复有什么区别本质区别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 pool2.写瓶颈写入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 视图后面执行的事务不可见可重复度