文字内容
1. 分析mysql acid设计实现 blog- xiaorui.cc author- rfyiamcool
2. List innodb 基本结构 innodb acid 概念 innodb buffer pool mvcc 各种延伸 锁, 各种锁
3. innodb的抽象结构 tablespace segment leaf node segment extent no-leaf segment … extent page rollback segment … … page trx id rollback ptr field ptr … row row row row row row row
4. b+tree 数据结构 15 15 30 56 45 77 … … page 22 row 20 row 17 row 31 row 35 row 44 row 16 row 32 row [16, 17, 20, 22] [31, 32, 35, 44] 有序数组
5. file system vs disk innodb engine - - - > 16k file system - - -> 4k disk - - - > 512 bytes
6. size不同如何上下对齐? partial page write ? 解决办法是 doublewrite buffer + doublewrite doublewrite 性能影响? 5% - 10%
7. dirty page double write buffer (2m) dirty page double write double write crash recovery !!! redo log 数据⽂件
8. what is acid atomicity consistency isolation durability
9. atomicity ⼀个事务为⼀个原⼦ 不可分割 要么成功 要么失败
10. consistency 经过⼀系列改动及异常崩溃,最后的结果是我要的. 最终⼀致性 强⼀致性
11. isolation mvcc 隔离级别 READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE
12. durability ⼀旦提交成功,那么事务涉及的数据都会持久化 即使系统崩溃,修改的数据也不会丢失
13. innodb buffer pool page buffered page buffered new 63% page buffered page buffered page buffered 新数据 新数据 新数据 old 37% page buffered 新数据
14. update where blog = ‘xiaorui.cc’ data in buffer pool yes ! no ! Load page to buffer update data and mark dirty page !!!
15. innodb buffer pool lru dirty page free list flush list
16. ⼀些概念总览 redo undo checkpoint purge
17. if not checkpoint 写操作都是在buffer pool进⾏,每次提交都会顺序io增加redo日志. buffer pool里面的dirty page越来越多 . redo 越来越⼤ , 导致recovery时间过长 .
18. if checkpoint 当 buffer pool 内存不够用时, 将dirty page刷新到⽂件. 当redo⽂件过⼤又不可循环写⼊时 , 同上 缩短crash recovery时间
19. checkpoint + lsn Redo Log 待清理 buffer pool flush list p1 p3 p2 last checkpoint : 1000 顺序: redo log == flush list ? p5 p4 刷新到磁盘 ! 创建checkpoint lsn: 1500 作为下⼀个点
20. ⼀个事务过程 1. begin 2. undo log if crash ? 3. udate where id = xxx; if crash ? 4. redo buffer 5. redo fsync; if crash ? binlog; commit one of three happen crash ?
21. redo when redo save ? when commit ! 顺序io page number + block when redo reduce ? Ringbuffer full flush timer run
22. undo 随机io 是否存⼊⽂件, 取决于buffer pool
23. flush log rule innodb_flush_log_at_trx_commit = 0 innodb_flush_log_at_trx_commit = 1 timer flush every 1s Log Buffer File System Buffer innodb_flush_log_at_trx_commit = 2 write os cache Lb_logfile
24. 数据库并发处理的演变 基于表的读写锁 基于索引的读写锁 (分离锁) mvcc
25. mvcc 读读非阻塞 读写非阻塞 写写阻塞 一句话, 并发的效果,串行的结果 !!!
26. 又是概念 read view 隐藏字段 是否可见的条件 当前读 快照读
27. mvcc流程图 read & write undo data read view list trx_id roll ptr data … trx_id 10 trx_id 15 undo data <= trx_id trx_id in read view row trx_id rollback ptr … trx_id roll ptr data
28. 幻读 record lock gap lock next gap lock
29. 业务数据不⼀致 ts ts ts ts ts ts ts ts 事务 A begin select stock where ticket=10 update stock=stock-1 commit 事务B stock=1 stock=0 begin select stock where ticket = 10 update stock=stock-1 stock=-1 commit 最后 -1
30. 互斥锁 vs 乐观锁 互斥锁 select * from train where ticket = 10 for update; 乐观锁 while 1: update train set stock=stock-1 where ticket = 10 and stock = x;
31. crash recovery 从doublewrite 修正缺⽄少两的page 重做提交的redo日志 回滚执⾏⼀半的 未提交的 事务
32. “ Q & A” –fengyun rui