【Mysql技术内幕InnoDB存储引擎】笔记

1.体系结构及存储引擎

组成部分

• 连接池组件
• 管理服务和工具组件
• SQL接口组件
• 查询分析器组件
• haun缓存组件
• 插件式存储引擎
• 物理文件

连接mysql

• TCP/IP
• 命名管道/共享内存
• Unix域套接字

2.InnoDB存储引擎

体系架构

后台线程

1个master thread

主循环

• 每秒钟操作

  • 日志缓冲刷新到磁盘，即使这个事务还没有提交(总是).
  • 合并插入缓冲(可能)
  • 至多刷新100个InnoDB的缓冲池中的脏页到磁盘(可能)
  • 如果没有用户活动，切换到background loop(可能)

• 每10秒钟操作

  • 刷新100个脏页到磁盘(可能)
  • 合并至多5个插入缓冲(总是)
  • 将日志缓冲刷新到磁盘(总是)
  • 删除无用的undo页(总是)
  • 刷新100个或者10个脏页到磁盘(总是)
  • 产生一个检查点(总是)

后台循环

• 删除无用的undo页(总是)
• 合并20个插入缓冲(总是)
• 跳回到主循环(总是)
• 不断刷新100个页，直到符合条件(可能，跳转到flush loop中完成)

刷新循环

暂停循环

4个IO thread

>insert buffer thread,
log thread,
read thread,
write thread

1个锁监控线程

1个错误监控线程

内存

• 缓冲池(buffer pool)

  最大块内存，存放各种数据的缓存。innoDB将数据库文件按页(16k/页)读取到缓存池，然后按最近最少LRU算法保留在缓冲池中的缓存数据。如果数据库文件需要修改，首先修改再缓冲池中的页，(脏页),然后再按一定频率将脏页刷新到文件。

  数据页类型：索引页，数据页，undo页，插入缓冲，自适应哈希索引，innoDB存储的锁信息，数据字典信息。

• 重做日志缓冲池(redo log buffer)

  将重做日志信息先放入这个缓冲区，然后按一定频率刷新到重做日志文件。

• 额外内存池(additional memory pool)

插入缓存(insert buffer)

先判断插入的非聚集索引页是否在缓冲池中，如果在，则直接插入，否则，先放入一个插入缓冲区，再以一定的频率执行插入缓冲和非聚集索引页子节点的合并操作。

• 索引是辅助索引
• 索引不是唯一的

  如果是唯一的，又会去查找索引页的情况，出现离散读。

两次写(double write)

自适应哈希索引

3.文件

日志文件

错误日志

慢查询日志

• long_query_time 默认10秒
• 运行时间>long_query_time会被记录

查询日志

所有请求信息，不论是否正确执行

二进制日志

• 恢复
• 复制

套接字文件

• pid文件
• 表结构定义文件 *.frm
• innoDB存储引擎文件
• 表空间文件
• 重做日志文件

4.表

表空间

表空间>段(segment)>区(extent)>页(page) 

段

• 数据段(B+树的页节点)
• 索引段(非页节点)
• 回滚段

区

64个连续页，每个页16k，每个区1M

页

16k

• 数据页
• Undo页
• 系统页
• 事务数据页
• 插入缓冲位图页
• 插入缓冲空闲列表页
• 未压缩的二进制大对象页
• 压缩的二进制大对象页

行

• 每个页最多存放16k/2-200行 约等于7992行

innoDB行记录格式

compact行记录格式

如果一个页中存放的行数据越多，性能越高

redundant行记录格式

行溢出数据

• 实际varchar最大65532
• Uncompressed BLOB Page
• 由于是B+树，每页至少应该有2个记录，当只能存放一条时，将行数据放到溢出页中

InnoDB数据页结构

4.7 约束

数据完整性

实体完整性

• Primary key
• Unique key

域完整性

• Foreign key
• Default

参照完整性

4.7.6 触发器

最多为一个表建立5个触发器，Insert,Update,Delete,Before,After

4.7.7 外键

4.8 视图

4.9 分区表

• range分区
• list分区
• key分区
• columns分区

5.索引与算法

InnoDB存储引擎索引

B+树

B+树插入操作

页节点满 索引节点满 操作

NO	NO	直接插入
Yes	No	拆分页节点，将中间的节点放入索引页中，小于中间节点的记录放左边，大于中间节点的放右边
Yes	No	查分页节点，小于中间节点的记录放左边，大于等于中间节点的记录放右边，拆分索引页，小于中间节点的放左边，大于中间节点放右边，中间节点放上一层索引页

B+树删除

Leaf Page Below Fill Factor Index Page Below Fill Factor 操作

No	No	直接删除页节点，如果该节点还是index Page节点，用该节点的右节点代替dai
Yes	No	合并叶节点及其兄弟节点，同时更新index Page
Yes	No	合并叶节点及其兄弟节点，更新indexPage，合并Index Page及其兄弟节点

5.5.1 聚集索引

每张表只能有一个聚集索引，，按每张表的主键构造一颗B+树

5.5.2 辅助索引(非聚集索引)

叶节点包含一个书签，记录指向主键索引的指针,然后通过主键索引找到完整记录。

5.6.1 什么时候使用非聚集树索引

• 字段离散度大
• 获取行数少

如果行数覆盖多，会走全表扫描，原因:非聚集索引后还需要找聚集索引，这个过程随机读

5.6.2 顺序读，随机读，预读取

随机预读取

当一个区(64个连续页)中13个页也在缓冲区中，并在LRU列表前端，则将剩余的所有页预读到缓冲区

线性预读取

如果一个区中的24个页都被顺序的访问，则会读取下一个区的所有页。

5.6.3 辅助索引优化

5.6.4 联合索引

• 不能直接使用非第一个字段开始的索引

索引(a,b)， b=xxxx不能使用

• 可以对第二个键值排序

order by b 可以减少一次排序

5.7 哈希算法

哈希表查询缓冲池中的页

6.锁

6.2 InnoDB中的锁

6.2.1 锁的类型

• 共享锁 SLock
• 排它锁 XLock
• 意向共享锁 ISLock
• 意向排它锁 IXLock

6.2.2 非锁定读操作

• 正在执行delete update操作的数据，会去读取行的一个快照数据
• 快照通过Undo段实现
• MVCC 多版本并发控制

在RC，RR下对于快照的定义不同，RC下总是读取被锁定行的最新一份快照数据，RR下总是读取事务开始时的版本

6.3 锁的算法

• Record Lock 单行锁
• Gap Lock 间隔锁，锁定一个范围，但不包含记录本身
• Next-Key Lock： Gap+Record 锁定一个范围，并且锁定记录本身。

6.4 锁问题

6.4.1 丢失更新

• 加排他锁 （select ** for update）

6.4.2 脏读

6.4.3 不可重复读

6.5 阻塞

6.6 死锁

6.7 锁升级

将当前锁的粒度降低，把一个表1000行锁升级为页锁，页锁升级为表锁

7.事务

• A 原子性 atomicity
• C 一致性 consistency
• I 隔离性 isolation
• D 持久性 durability

7.2 事务的实现

7.2.1 redo

先写日志，再写数据

• 重做日志

7.2.2 undo

• undo段，位于共享表空间
• master thread中回收undo页

7.3 事务控制语句

• Start Transaction | begin
• COMMIT
• ROLLBACK
• savepoint
• release savepoint
• rollback to
• set transaction

7.4 隐式提交sql语句

• DDL语句
• 隐式修改mysql架构的操作：create user，drop user，grant，rename user，revoke，set password
• 管理语句

7.6 事务隔离级别

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ 默认
• SERIALIZABLE

SERIALIZABLE

对每个SELECT语句自动加上LOK IN SHARE MODE

8.备份与恢复

• Hot backup
• Cold backup
• Warm backup

9.性能调优

• OLTP

online Transaction Processing 在线事务处理 日常事物处理应用

• OLAP

online Analytical Processing 在线分析处理 多用于数据仓库或数据集市