mysql技术内幕_MySQL技术内幕

内容摘取自<MySQL技术内幕 InnoDB存储引擎>by姜承尧，作者目前为腾讯T4的数据库总监，本文为书本内容的子集。

内容列表：

• InnoDB存储引擎
• 索引(index)
• 锁(lock)
• 事务(transaction)
• 复制(replication)
• RAID

名词解释：

• 数据库：文件的集合，frm，ibd等；
• 数据库实例：程序，位于用户与OS之间的一层数据管理软件
• OLTP：在线事务处理 OnLine Transaction Processing
• OLAP：在线分析处理 OnLine Analytical Processing
• Dirty Page：脏页，内存页和磁盘页数据不一致
• Redo Log：重做日志
• LSN：序列号，Log Sequence Number
• FRM：FoRMat，InnoDB表定义文件
• IBD：InnoBase Data，InnoDB数据文件

事务提交流程：Write Ahead Log策略：当事务提交时，先写Redo Log，再修改页。

• 先记录 undo/redo log，确保日志刷到磁盘上持久存储。(确保2/3宕机不丢数据/主从一致)
• 更新数据记录，缓存操作并异步刷盘。
• 将事务日志持久化到 binlog。
• 提交事务，在 redo log 中写入commit记录。

InnoDB存储引擎

1. 存储引擎

• 存储引擎是基于表的，而不是数据库。数据库还包括连接池 / Parser / Optimizer / File System等。
• MySQL数据库重要特点之一就是其插件式的表存储引擎。
• InnoDB存储引擎支持事务，行粒度锁，外键；聚集索引；支持mvcc；主要处理OLTP。
• MyISAM存储引擎不支持事务，表粒度锁，支持空间数据；无崩溃恢复；查询效率更高(唯一优势，且不明显)；主要处理OLAP。

2.InnoDB存储引擎

• Redo Log先放到Redo Log Buffer，然后按一定策略到文件系统的Redo Log文件或磁盘innodb_flush_log_at_trx_commit。
• 分区partition：不同子分区生成不同的idb文件，如t#P#p0.ibd, t#P#p1.ibd；分区类型包括Range(值范围)，List(指定值)，Hash(用户表达式)，Key(数据库提供的哈希函数)。
• 缓冲池(innodb_buffer_pool)
  • 不包含重做日志Redo Log
  • 页的修改操作，首先修改缓冲池中的页，再以一定机制/频率刷回磁盘。
  • 包含：数据页，索引页，锁信息，插入缓冲，自适应哈希索引等
  • 页的大小默认为16KB，使用LRU算法对缓冲池进行管理
  • 插入缓冲Insert Buffer
    • 对于非聚集索引的插入或更新，由于数据不在一个索引页，所以效率底
    • 先放入insert buffer，再以一定频率合并操作，通常在一个索引页
  • 自适应哈希Adaptive Hash Index
    • 如果观察到建立哈希索引可以带来速度提升，则通过B+树构造
    • 将3次查询减少到1次
  • 伙伴算法进行页的管理
    • 从缓冲池中申请4KB大小的页
    • 检查4KB的LRU列表，若有，则直接使用
    • 检查8KB的LRU列表，若有，分成两个4KB的页，放到其LRU列表
    • 检查16KB的LRU列表，若有，分成一个8KB，两个4KB的页，放入对应LRU
• CheckPoint：将某种策略脏页刷回磁盘的机制
  • • Async/Sync Flush：
      • • size = 重做日志LSN - 已经fsync LSN
        • sync: size > 90% * 总重做日志
        • async: size > 75% * 总重做日志
    • FLUSH_LRU_LIST：当空闲页小于阈值时fsync
    • Dirty Page Too Much：当脏页大于阈值时强制fsync
• 多线程模型
  • Master Thread：
    • 将缓存写到磁盘(flush to disk)，即CheckPoint执行；每秒；详情可参考事务后文中的redo log部分。
    • 合并插入缓冲Merge Insert Buffer。
    • 等等其他下列thread不做的。
  • IO Thread：insert buffer thread / log thread / read thread / write thread
  • Purge Thread：回收已经使用并分配的undo log页
  • Page Cleaner Thread：脏页刷新

索引(index)

1.B+树

• B+树中的B代表的不是Binary，而是Balance。
• B+树索引并不能找到一个给到键值的具体行，只是被查找数据行所在的页；数据库把整页读到内存中，再在内存中找到具体的行。
• 平衡二叉树：二叉查找树+任何节点的两个子树的高度最大差为1。
• 数据库中，B+树的高度一般是2～4层，查找某一记录的行记录时最多只需要2到4次IO，一般的机械磁盘每次IO大概为10毫秒。

2.索引方式

• 聚集索引(clustered index)：按照每张表的主键构造一棵B+树，同时叶子节点存放的即是整张表的整行记录数据。
• 辅助索引/非聚集索引(secondary index / non-clustered index)：构造一棵B+树，叶子节点不存储数据，而是存储聚集索引中的位置。
• 如果在高度为3的辅助索引树中查找数据，需要6次IO(3次找到主键+3次查聚集索引树)。
• 联合索引：对多个列进行索引，比如(a,b)。适用于where a=xxx and b=xxx 和 where a=xxx，但是不适用于where b=xxx。
• 一张表中包含一个聚集索引构成的B+树，以及若干辅助索引构成的B+树。

锁(lock)

1.乐观锁和悲观锁

• 乐观锁：是一种思想，并不是真正意义上的锁；尝试对资源进行修改，再回写时判断资源是否进行了更改；如果没有发生改变就写回，否则回滚；整个执行过程都没有对数据库进行加锁。在事务隔离级别Read Committed和Repeatable Read下，InnoDB存储引擎使用一致性非锁定读，使用undo log mvcc实现。
• 悲观锁：在获取资源前对资源进行加锁，确保同一时刻只有有限的线程可以访问该资源，其他想要尝试获取资源的操作进入等待状态，其他前面操作释放锁。
• 选择：
  • 乐观锁适用：并发量高，响应速度快；读多写少
  • 悲观锁适用：回滚成本高，冲突频率大

2.InnoDB中的锁

• 锁可以实现事务的隔离性需求，使得事务可以并发地工作，提高并发度。
• InnoDB锁的级别为行，悲观锁。
• 共享锁：S Lock，(Shared)允许事务读一行数据。
• 排他锁：X Lock，(eXclusive)允许事务删除或更新一行数据；经实验，其他事务仍可以读。
• select ... for update：对读取的行记录加X锁
• select ... lock in share mode：对读取的行记录加S锁
• 只有S Lock和S Lock可以共存；S & X，X & X皆不可共存。
• 读意向锁：IS Lock，Intention Shared Lock：表级别锁，不会阻塞，只表示有人在读表。
• 写意向锁：IX Lock，Intention eXclusive Lock：表级别锁，不会阻塞，只表示有人在写表。
• 意向锁在全表扫描类操作中可以提升效率。

3.锁的算法

• Record Lock：行锁
• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
  • 对于select * from t where a>2 for update
  • 对范围(2, 正无穷)加了X锁
  • 可以避免幻读
• Next-Key Lock：Record Lock + Gap Lock：没有找到翻译。

4.锁带来的问题

• 脏读：读到未提交的数据
  • Session A: select * from t; // {id=1,a=1}
  • Session B: insert into t select 2;
  • Session A: select * from t; // {id=1,a=1}{id=2,a=2}
• 不可重复读：读到已经提交的数据，但是违法了数据库事务一致性要求。
  • Session A: select * from t; // {id=1,a=1}
  • Session B: update t set a=2 where id=1;
  • Session B: commit;
  • Session A: select * from t; // {id=1,a=2}
• 幻读：与不可重复读区别为update vs insert。
  • Session A: select * from t; // {id=1,a=1}
  • Session B: insert into t select 2;
  • Session B: commit;
  • Session A: select * from t; // {id=1,a=1}{id=2,a=2}
• 更新丢失：略，任何隔离级别更新时加锁不会发生。

5.死锁

• 解决方法：将任何等待化为回滚；超时；等待图(wait-for graph)检测
• innodb_lock_wait_timeout默认为50s。

事务(transaction)

1.特性ACID

• 原子性 Atomicity：redo log实现；要么都做，要么都不做
• 一致性 Consistency：undo log MVCC实现；逻辑上正确
• 隔离型 Isolation：undo log MVCC/lock实现；concurrency control / serializability / locking
• 持久性 Durability：redo log实现；数据库宕机时通过redo log来恢复

2.事务的实现

• Redo Log：保证事务原子性和持久性
  • 物理日志，记录的是页的物理修改操作。
  • redo块(block)大小和磁盘扇区一样，都是512B。
  • innodb_flush_log_at_trx_commit=0：事务提交时不fsync，等待main thread
  • innodb_flush_log_at_trx_commit=1：事务提交时必须调用一次fsync(性能最差/默认)
  • innodb_flush_log_at_trx_commit=2：事务提交时写入文件系统缓存；与0的区别：MySQL宕机不会丢数据；0和2操作系统宕机都会丢数据。
  • redo log vs binlog
    • redo log速度比binlog速度快很多
    • binlog：用来恢复recovery，复制replication(更精简，利于网络间传送)，审计audit(分析判断是否有注入攻击)
    • binlog产生于数据库上层；redo log产生于存储引擎
    • binlog记录逻辑日志，对应SQL语句；redo log是物理格式日志，记录每个页的修改
      • 比如表CREATE TABLE t (a INT, b INT, PRIMAR KEY(a), KEY(b))
      • SQL语句：INSERT INTO t SELECT 1, 2;
      • Redo Log：
        • page(2, 3), offset 32, value 1,2 // 聚集索引
        • page(2, 4), offset 64, value 2 // 辅助索引
    • binlog只在事务提交完成后写入磁盘；redo log每个事务对应多条日志
• Undo Log：保证事务一致性
  • 逻辑日志
  • 帮助事务回滚
    • 对于delete，执行insert
    • 对于insert，执行delete
    • 对于update，执行相反update
  • 实现MVCC
    • 记录历史操作
    • delete不会删除数据，会先打上flag。purge进程完成最终清理记录。
• LSN：Log Sequence Number，日志序列号
  • Log sequence number：当前LSN
  • Log flushed up to：刷新到redo log文件系统的LSN，数据库宕机，OS没宕机可从此恢复
  • Last checkpoint at：刷新到磁盘的LSN

3.隔离级别

• Read Uncommitted => 不加锁；问题：脏读(读到未提交的行)
• Read Committed => 乐观锁；undo log MVCC实现，读取被锁定行，最新一份快照数据；问题：不可重复读(值前后读取不一样，被其他事务修改)
• Repeatable Read => 乐观锁；undo log MVCC实现，读取被锁定行，事务开始时的数据；问题：幻读(有新的值插入，被其他事务新增)
• Serializable => 加悲观锁S Lock或间隙锁Gap Lock / Next-Key Lock

4.隔离级别说明

• InnoDB存储引擎在Repeatable Read隔离级别下，使用Next-Key Lock，避免幻读，效果上等于Serializable。
• 事务隔离级别越低，事务请求的锁越少或保持锁的时间越短。

5.使用习惯

• 自动提交Auto Commit默认打开。
• 在显示开启事务后，MySQL会自动执行SetAutoCommit=0的命令，并在Commit或Rollback结束一个事务后执行SetAutoCommit=1。
• 子事务提交并不会马上生效，除非父事务已经提交。
• 树中任意一个事务的回滚会引起它的所有子事务一起回滚。
• InnoDB使用Repeatable Read隔离级别，所以Select默认使用乐观锁；只有select ... for update 和 select ... in share mode的时候才会使用悲观锁。

复制(replication)

1.注意事项

• RelayLog：中继日志
• 上述架构有缺陷：如果drop table / drop database无法恢复，因此还需配合快照。
• 事务提交流程：
  • master事务提交 -> write binlog -> slave replicate binlog -> innodb redo log commit
  • 在replicate之后数据库宕机会造成主从不一致
  • master事务提交 -> innodb redo log prepare (sync) -> write binlog -> ...

RAID

1.RAID介绍

• Redundant Array of Independent Disks，独立磁盘冗余数组
• 基本思想：把多个相对便宜的磁盘组合起来，成为一个磁盘组，使性能达到一个价格昂贵容量大的硬盘。
• RAID 0: 它将两个以上的磁盘并联起来，成为一个大容量的磁盘。在存放数据时，分段后分散存储在这些磁盘中，因为读写时都可以并行处理，所以在所有的级别中，RAID 0的速度是最快的。但是RAID 0既没有冗余功能，也不具备容错能力，如果一个磁盘（物理）损坏，所有数据都会丢失。
• RAID 1: 两组以上的N个磁盘相互作镜像，在一些多线程操作系统中能有很好的读取速度，理论上读取速度等于硬盘数量的倍数。
• RAID 10：多组RAID 1，然后以RAID 0的方式组合。