MySQL技术内幕：InnoDB存储引擎读书笔记

引言

本书介绍InnoDB的体系结构和工作原理，并结合InnoDB的源代码讲解了它的内部实现机制。
why比what重要
通过阅读本书，你将理解InnoDB存储引擎是如何工作的，它的关键特性的功能和作用是什么，以及如何正确地配置和使用这些特性。

第一章 介绍MySQL体系结构和对比存储引擎

实例是一个mysql server进程
数据库是一个物理操作系统文件

MySQL的体系结构

• 连接池组件
• SQL接口组件
• 查询分析器组件
• 优化器组件
• 缓冲组件
• 插件式存储引擎
• 物理文件

Innodb
Innodb是表存储引擎，支持事务，面向在线事务处理，特点是行锁设计，支持外键，没锁定读（MVCC）。
next-key lock避免幻读、插入缓冲、二次写、自适应哈希索引、预读等高性能和高可用
对于表的存储采用聚集（按主键的大小顺序排放）的方式存储数据，使用非聚集的方式存放索引。

MyISAM
表锁，无事务，全文索引 面向OLAP

其他存储引擎忽略先不看。

第二章 InnoDB存储引擎

完整支持ACID事务、行锁设计、支持MVCC、支持外键

InnoDB体系架构
InnoDB有多个内存块，可以认为这些内存块组成了一个大的内存池。

• 维护所有进程/线程需要访问的多个内部数据结构
• 缓存磁盘上的数据，对磁盘文件的数据修改之前在这里缓存
• 重做日志（redo log）缓冲。记录的是物理页的存放内容

后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中内存缓存的是最近的数据。把已修改的数据文件刷到磁盘文件，保证数据库发送异常情况下InnoDB恢复到正常状态。

线程
InnoDB的后台线程有master thread、IO thread、锁监控线程、错误监控线程。IO线程数由innodb_file_io_threads控制
（在master线程几乎实现所有的功能）
IO线程的类型有：insert buffer thread、log thread、read thread、write thread

master thread
该线程主要执行几个循环：主循环（loop）、后台循环（background loop）、刷新循环（flush loop）、暂停循环（suspend loop），master线程根据数据库的允许状态在这些循环中切换。

每秒一次的操作：

• 日志缓冲刷新到磁盘，即使这个事务还没有提交（再大的事务commit也很快）
• 合并插入缓冲（根据IO决定）
• 刷新最多100个缓冲池的脏页到磁盘（根据脏页比例来判断）
• 没有用户活动，切换到background loop

每10秒的操作：

• 刷新脏页
• 合并插入缓冲
• 刷新重做日志
• 删除无用的undo日志
• 产生一个检查点：把脏页刷新到文件，这有恢复的时候执行的redo log就少了。

后台循环主要也是在做刷新日志文件，删除无用的undo，合并插入缓存

purge thread
用来回收使用并分配的undo页。

page cleaner thread
把脏页的刷新放到了单独的线程

IO thread
使用AIO来处理写IO请求，提供数据库的性能。IO thread是负责这些IO 请求的的回调

内存
InnoDB存储引擎内存由以下几部分组成：

• 缓冲池：占最大块内存的部分，用来存放各种数据的缓存。InnoDB的存储引擎的工作方式总是将数据库文件按页读取到缓冲池，LRU淘汰数据。数据需要修改时，先修改缓冲池的页，修改后变成脏页，再按刷盘机制把脏页刷新到文件
• 重做日志缓冲池
• 额外的内存池:管理缓冲池的对象内存从此处申请

缓冲池
缓冲池中缓冲的数据页：索引页，数据页，undo页，插入缓冲，自适应哈希索引，InnoDB存储的锁信息，数据字典信息。
通过checkpoint把脏页刷新到文件
lru list：控制缓存的淘汰
free list：空闲缓存
flush list：需要刷新的list

重做日志缓存区
保证每秒的事务log小于8m

额外的内存池
额外的内存池:管理缓冲池的对象内存从此处申请

checkpoint
把脏页刷到文件。降低redo log的恢复时间。回收重做日志。

Innodb内存结构

InnoDB的关键特性

• 插入缓冲
• 自适应hash索引
• 异步IO
• 两次写
• 刷新领接页

插入缓冲
对于非唯一非聚集索引的插入和更新，是创建或者更新插入缓冲中的insert buffer对象。再按一定频率刷新到文件

change buffer
与插入缓冲类似，提供了delete buffer和purge buffer

两次写
把页写入磁盘并不是原子性的，当写到一半时宕机。这种情况称为部分写失效。
先写到临时页，保证有一个待写页的副本。

自适应哈希索引
根据访问频率对b+树的某些热点页建立hash索引。

异步IO
提高IO性能，减少线程等待。

刷新领接表
查找页所在区的所有页是否需要刷新，一起刷新可用利用AIO提高性能。

第三章 文件

表结构定义文件
文本文件，每个表都有一个.frm文件。

重做日志文件

第四章 表

逻辑存储结构：数据存放在表空间。由段 区 页组成

常见的段有数据段、索引段、回滚段

默认情况下所有表的数据都存放在共享表空间下。当开启了innodb_file_per_table，那么每张表的部分类型的数据会存放到每张表的表空间。
每张表的表空间存放数据类型：数据、索引、插入缓冲bitmap页。
每张表的其他数据还是存放在共享表空间比如：回滚（undo）信息，插入缓冲索引页、系统事务信息、二次写缓冲等

表空间不会回收但是会重复利用。
数据段是B+树的叶子节点
索引段是B+树的非索引节点

常见的页类型

Innodb是面向行的数据库。还是Google Big Table是面向列的数据库。

MySQL的分区支持以下几种类型：
Range：一定连续区间的列值被放入分区
list：离散的值
hash：根据用户自定义的表达式来分区
key：根据mysql数据库的hash函数来分区

上面四种类型的分区，数据必须是整型的，如果不是整型，那么需要year()等函数转化为整型。

columns类型的分区支持以下的数据类型：

• 所有的整型类型
• 日期类型
• 字符串类型
  

分区是把数据按列的规则把数据聚集在一个分区中。

使用分区表的场景是：查询的条件中必须带分区列。
限制：

• 一张表最多只能1024个分区
• 分区表无法对非分区列建立唯一索引
• 分区表无法使用外键
• 当无法使用行锁时，会锁住全部分区
• 分区列必须是唯一索引的

优点：

• 可能降低了B+树的高度，减少了查询的IO

第五章 索引与算法

索引类型：B+树索引、全文索引、哈希索引。
按主键顺序存放的是聚集索引，其他是非聚集索引

查看表的索引

show index from table_name

• seq_in_index:联合索引中该列的位置
• collation：A表示按B+树存储在索引，NULL表示以hash桶存放索引数据
• cardinality（基数）：列中唯一条目的个数

cardinality值很关键，优化器会根据这个值来判断是否使用这个索引，但是这个值不是实时更新的。可以使用analyze table table_name来更新这个值。

MySQL5.6版本之后支持online DDL操作，允许辅助索引创建的同时，运行其他CRUD的DML操作。

• 辅助索引的创建
• 改变自增值
• 添加或删除外键约束
• 列的重命名

Innodb存储引擎碰到表在进行online ddl，会把dml的操作日志写入到一个缓存中。等到索引创建完成后重做到表上。 缓存的大小由innodb_online_alter_log_max_size控制。默认是128m

show index from table会触发cardinality的采样，cardinality是通过对8个页的数据采样得到的，每次选取的8个页，因此每次cardinality的值都不一样

覆盖索引：即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用辅助索引不需要查询整行记录的信息，因此可以减少大量IO操作。select count(*) from us_log 就会选择此表上的非聚集索引

使用辅助索引存在回表问题，当回表的量在大于表数据的20%左右时会使用主键索引，或者全部扫描

使用index hit，有两种语法select count(*) from us_log use index(us_log_phone_password) 和 explain select count(*) from us_log force index(us_log_phone_password);

使用Multi-Range Read优化，目的是为了减少磁盘的随机访问，改为顺序访问。这对IO密集型SQL查询语句可带来性能极大的提示。适用于range,ref,eq-ref类型的查询。把索引中的rowId读到内存，排序后访问聚集索引。可以在执行计划中看到using index condition和using MRR的字眼。可以通过show variables like 'optimizer_switch'

使用Index Condition Pushdown（ICP）优化。支持range、ref、eq_ref、ref_or_null类型的查询。当使用ICP优化时，可以在extra看到using index condition提示。MYSQL数据库在取出索引的同时判断是否可以进行where条件的过滤，也就是将where的部分过滤操作放在了存储引擎层。减少查看数据行的IO次数。

第六章 锁

锁的兼容性

查看当前锁请求的信息：show engine innodb status

一致性非锁定读：使用MVCC技术，读取undo log中的快照数据。在RR隔离级别下，都是一致性非锁定读
一致性锁定读：使用select * from us lock in share mode和select * from us for update

自增长与锁
自增长的简单实现方式是：select max(id)+1 from table_name for update 通过锁表的方式或者最大的id。
显然上面的方式有严重的性能问题。因此还有一种方式是在内存中维护一个计数器，使用互斥量（mutex）在内存中操作。

自增列锁模式：默认是1，对于普通插入使用内存锁，对于批量插入使用auto-inc locking

行锁的三种算法
record lock：单个行记录上的锁
gap lock：间隙锁（防止幻读），锁定一个范围，但不包含记录本身。
next-key lock：Gap lock + Record lock，锁定一个范围，并且锁定记录本身。如果是唯一索引会降级为record lock。

表结构和数据

select * from z where b=3 for update 
-- 会锁住主键索引中id是5的行（记录锁）。会给（1-3）加上gap lock和3这条记录加上record lock 和给（3-6）加上gap lock

会阻塞

可以通过

锁问题：脏读、不可重复读、更新丢失。
读未提交的事务隔离级别会产生脏读，读已提交的事务隔离级别可以避免脏读问题，但是会有不可重复读问题。
可重复读的隔离级别可以避免不可重复读问题，但是无法避免更新丢失问题。使用串行化避免更新丢失问题。

阻塞问题：一个事务等待另一个事务释放资源的过程就称之为阻塞。

死锁：当两个或者两个以上的事务在执行过程中，出现互相等待资源的现在，称之为死锁。
使用wait-for graph检测死锁，需要锁的信息链表，事务等待链表

Innodb不存在锁升级问题，使用页作为锁的管理单元，并采用位图方式，占用内存较少。

第七章 事务

事务的四个特性：
A:原子性，事务内的sql要么一起成功要么一起失败。redo log保证
C:一致性。事务执行完后，数据从一个状态到另一个状态。 undo log
I:隔离性。使用锁来实现。
D:持久性 当事务写入成功后，那么数据不会丢失。redo log

redo log

基本概念
包括两部分，内存中的redo log buffer和redo log file。
使用使用force log at commit机制（会调用操作系统的fsync，把文件缓存刷盘），当事务提交时，先将重做日志文件进行持久化，保证了事务的持久性。重做日志包括redo log和undo log。
redo log基本是顺序写，undo log存在随机读写。

innodb提供了innodb_flush_log_at_trx_commit用来控制重做日志刷新到磁盘的策略。一共有三种选项0、1、2。0代表：事务提交时不写入重做日志，等待每秒的写入。1表示事务提交时调用fsync。2表示事务提交时，仅将重做日志写入到文件系统中重做日志文件的缓冲区

bin log是二进制日志，记录的是原始的sql语句，是MySQL层面的，用来主从同步和Point-in-time的恢复。
与redo log的差别：

特性	redo log	bin log
写入的时机	每个影响数据的sql都会在执行时写入redo log	在事务提交时写入
写入的内容	对数据页的物理修改	记录原始的sql语句
作用	恢复事务数据，保证D和A。	用于数据恢复和主从同步

log block
重做日志和缓存都是以块的方式保存，称为重做日志块，每块大小为512k，与一个扇区相同，因此不需要double write技术。

共有三部分组成：日志块头（12字节），日志块尾（8字节），日志内容（492字节）。

log group
重做日志组，其实只有一组

重做日志格式

LSN
是Log Sequence Number的缩写，表示的是日志序列号。
每个页中会记录刷新时的LSN。
恢复时，只需要重新执行最后刷新的LSN到最新的LSN之间的数据。

恢复
redo log是二进制日志，记录的是物理修改。因此是幂等的，而且恢复速度大于bin log

undo log

在修改数据之前，把未修改的数据保存到undo log，用于一致性非锁定度和事务回滚时的恢复
存放在数据库内部的一个特殊段中，这个段称为undo段。undo段位于共享表空间

undo是逻辑日志。对于insert语句增加一条delete的sql，delete语句增加一条insert的sql。update增加一条反向语句.

purge

在事务中删除一行记录，并不会立刻删除行，只会把行记录的delete_flag置为1，等待后续的purge thread做正在的清理。
update操作也类似。

group commit

多个commit一起fsync

第八章 备份与恢复

按备份的方法分为热备（online 备份）、冷备（offline 备份）、温备（对当前允许的数据库有影响，增加全局读锁实现备份数据一致性）

按备份后的文件内容分为：逻辑备份（逻辑sql语句）和裸文件备份（物理文件）

按备份数据库的内容分为：完全备份（完整的备份）、增量备份（在上一次的基础上备份）、日志备份（对二进制的日志备份，主要百科Mysql的数据库复制,主从。）

逻辑备份
使用mysqldump工具可以完成数据库的转存，用于不同数据库之间的升迁。
执行mysql -uroor -p <test_backup.sql可以完成mysqldump的恢复

二进制日志的备份
使用mysqlbinlog binlog.00000001 |mysql -u root -p test可以恢复数据库

热备
使用ibbackup和xtrabackup可以完成热备，第三方工具

增量备份
xtrabackup可以实现增量备份

复制

Mysql的复制主要分为三个步骤进行：
1.主服务器把数据更改记录到bin log
2.从服务器同步bin log并写入到自己的relay log中
3.从服务器重做中继日志中的日志，把更改应用到自己的数据库，完成一次数据备份

这个过程是异步实时的。

当从节点复制延迟较久时，可以增大IO线程、SQL线程的个数，加快复制速度。
使用show slave status

第九章 性能调优

Mysql是可以使用多核CPU的。 一般机器的配置可以调整innodb_read_io_threads和innodb_write_io_threads的值跟cpu核数相同。

机器的内存可以用来缓存索引和数据。

内存越大缓存索引越多，查询效率越高。
插入buffer越大肯定插入的性能越好。

硬盘的影响

主要是随机写、随机读、顺序写和顺序读四个性能指标

对于普通的机械硬盘，顺序访问的速度远高于随机访问的速度。因为机械盘读取需要磁头旋转和定位，顺序消耗的时间最少。

固态硬盘的内部是闪存，属于电子设备，没有磁头。所以随机访问的速度远远大于机械硬盘。

固态和机械硬盘的延时

RAID

RAID：redundant array of independent disks。是把多个相对便宜的硬盘组合起来，称为一个磁盘数组，比肩大磁盘。 多个硬盘是一个逻辑扇区，操作系统会认为是一个硬盘。

RAID的作用：

• 增强数据集成度
• 增强容错功能
• 增加处理量或容量

raid0：把多个磁盘组成一个逻辑磁盘。利用率100%，容错率0%
raid1：把两个磁盘组成镜像磁盘，利用率50%，容错率50%
在上述两种基础上，出来了raid10和raid01。区别在于是先组成镜像再分区，还是先分区再组成镜像。
一般来说是raid10比较好

write back vs write through
回写是数据写入到raid卡的缓存就返回，只要raid有备份电源，那么缓存中的数据就是安全的，可以保证数据的一致性
只写是不开启raid卡的缓存，写入磁盘才算成功。

本文地址：https://blog.csdn.net/wengfuying5308/article/details/107145319