Mysql Innodb 技术内幕读书笔记

Inndb存储引擎

1. mvcc 多版本并发控制 -> 高并发性
2. innodb 实现了四种隔离机制，默认可重复读
3. next-key-locking 避免幻读
4. 插入缓冲
5. 二次写
6. 自适应hash
7. 预读

Myisam与innodb的区别

区别主要包括：
1. 不支持事务和表锁设计
2. 支持全文索引
3. 存储引擎只缓存索引，数据的缓存交给操作系统处理。

连接数据库实例的通信方式

1. 一个数据库实例表现为单进程多线程（数据库和数据库实例不是同一个概念）
2. 进程与进程间的通信主要有：tcp/ip、命名管道和共享内存、unix套接字
3. tcp/ip：非本机的客户端（client）向服务端（server）的请求
4. 命名管道+共享内存：两个需要通信的进程在同一个机器上，Mysql需要修改配置
5. unix套接字：Linux+两个进程在同一台机器上

Innodb体系架构

后台线程的主要作用是负责刷新内存块的数据，使其保持最新；同时还负责数据库的脏页刷新与异常恢复：
1. Master thread：数据异步刷新至磁盘、保证数据一致性、脏页刷新、合并插入缓冲、undo页回收
2. IO Thread：linux系统大量使用了AIO来处理IO请求，线程可分为 - 插入缓冲IO、日志IO、多个读IO与多个写IO
3. Purge Thread：单独负责undo页的回收，减轻Master的负担
4. Page Cleaner Thread：单独负责脏页刷新，减轻Master的负担

Innodb存储引擎有多个内存块，可认为组成了一个大的内存块，内存主要负责维护线程/进程需要访问的数据结构、数据缓存的存储以及修改、重做日志缓存（redo log）
1. 缓冲池：因为数据库文件是存储在硬盘上面的，磁盘与内存读取存在很大的速度差异，所以需要缓冲技术来提高数据库的整体性能；缓冲池所缓存的包括：数据页+索引页、插入缓冲、自适应hash索引、锁信息、数据字典信息。其中对数据页+索引页的管理是通过LRU改进算法midpoint insertion strategy（新访问的数据不是直接放在表头，默认放在LRU列表的5/8处），这样的做法为了降低某些sql造成的热点页的淘汰，如全表扫描；同时如果索引页被修改了之后，该页被称为脏页，此时会被存放在Flush列表，Flush列表里的脏页通过数据库check point机制实现脏页的刷新，与LRU列表互不影响，换言之一个页可以同时存在与LRU与Flush中。
2. 重做日志缓冲：重做日志文件的主要目的是恢复实例或者介质失败后的恢复，或者可以作为一种维护备用数据库的方法来完成故障恢复。Innodb会将需要重做的日志放在此缓冲中，然后按照一定的频率将其刷新到日志文件中：1. Master每一秒会将重做日志缓冲刷新到日志文件；2. 每个事务提交会触发重做日志缓冲刷新到日志文件；3. 当重做日志缓冲的剩余容量小于1/2，会被刷新到日志文件。
3. 额外内存池：每个缓冲池中的帧缓冲，以及对应的缓冲控制对象，如LRU、锁、等待信息。

check point机制（缓冲池脏页刷新至磁盘）

首先在修改缓冲池的页时，innodb会遵循Write Ahead Log策略（即先写重做日志，再修改页），以宕机带来的数据恢复的问题。
check point主要解决一下问题：
1. 缩短数据库恢复时间：当发生宕机后，数据库不必重做所有日志，只用根据check point后的重做日志，将脏页刷新至磁盘上。
2. 缓冲池不够用，将脏页刷新到磁盘上：在前面缓冲池中提到，数据页+索引页是通过LRU算法进行管理，若被淘汰的页是脏页，会强制产生check point，将脏页刷新至磁盘上。
3. 重做日志不可用时，将脏页刷新到磁盘上：Innodb对重做日志的设计是循环而非持续增长，即当重做日志文件中的某部分不再被使用，那么新的重做日志可以覆盖掉此部分。相反，当重做日志文件不存在可被覆盖的时候，会强制产生check point，将脏页刷新至磁盘上。

check point类型：
1. Sharp CheckPoint：将全部的脏页都刷新到磁盘上，只会出现在数据库关闭的情况下。
2. Fuzzy CheckPoint：出现情况大概一下几种：

1. Master CheckPoint：由于master线程每秒或每十秒会刷新一定比例的脏页回磁盘，此过程异步，不会阻塞用户查询线程

2. Flush_LRU_List CheckPoint：由于Innodb需要在LRU保留一定数量的空闲页，而且检查空闲页数量发生在用户查询的过程中，所以会阻塞用户的查询线程，倘若没有足够的空闲页，那么会在LRU尾部进行淘汰（淘汰直至空闲页数量满足要求），如果淘汰的是脏页，则发生checkpoint并且刷新到磁盘

3.  Async/Sync Flush CheckPoint：重做日志不可用时，将部分脏页刷新到磁盘上，发现重做日志不可用同样在用户查询线程中，故会阻塞用户查询线程

4. Dirty_Page_Too_Much CheckPoint：当缓冲池中脏页的数量达到或超过一定比例，则刷新一部分脏页回磁盘

注意：前面的提及到的Page Cleaner线程同样会检查2、3中的情况，此操作不阻塞用户查询线程。

Master Thread

Master Thread 1.0.x之前的版本：

master线程是几个后台线程里面优先级最高的线程，其由几个循环组成：
1. 主循环loop：大部分操作都在主循环中进行 - 其中包括两大部分操作和每1秒操作和每10秒操作，主循环可到下面三种循环
2. 后台循环 background loop：若当前没有用户活动或者数据库关闭
3. 刷新循环 flush loop：不断刷新100个脏页直至符合条件，若没有操作则切换至suspend线程
4. 暂停循环 suspend loop：挂起，等待用户活动

主循环每1秒操作：
1. 重做日志缓冲刷新到磁盘上，即使这个事务未提交（总是），所以再大的事务提交的时间也非常短。
2. 合并插入缓冲（可能），前1秒内IO操作是否小于5次，如果是，可认为IO压力比较小，可进行插入缓冲合并
3. 至多刷新100个脏页从缓冲池到磁盘（可能），和Fuzzy CheckPoint第四种情况相似，如果缓冲池中超过一定比例脏页（innodb_max_dirty_pages_pct），Innodb会刷新100个脏页到磁盘
4. 如果没有用户活动，切换至后台循环（可能）

主循环每10秒操作：
1. 刷新100个脏页从缓冲池到磁盘（可能），Innodb会判断过去10秒内IO操作是否小于200次，如果是，可认为当前拥有足够的磁盘IO操作能力，然后会刷新100个脏页到磁盘
2. 合并至多5个插入缓冲（总是）
3. 将重做日志缓冲刷新到磁盘（总是）
4. 删除无用的undo页（总是，full purge）
5. 刷新100个或者10个脏页到磁盘（总数），和Fuzzy CheckPoint第四种情况相似，如果缓冲池中超过一定比例脏页（innodb_max_dirty_pages_pct），Innodb会刷新100个脏页到磁盘，否则刷新10个

注意：重做日志和脏页的刷新都是有缓冲池的，无论在哪种条件下，重做日志缓冲都会每秒和每10秒刷新，保证数据最新。

后台循环执行的操作：
1. 删除undo页（总是，full purge）
2. 合并20个插入缓冲（总是）
3. 跳回主循环（总是）
4. 跳至刷新循环（可能），不断刷新100个脏页直至符合条件

Master Thread 1.2.x之前的版本：

由于硬盘技术高速发展，Innodb在主循环的硬编码限制了IO的性能，所有进行了以下修改:
1. 设定参数innodb_io_capacity，合并插入缓冲的数量为innodb_io_capacity*5%，脏页刷新的数量为innodb_io_capacity
2. 参数innodb_max_dirty_pages_pct默认值从90%修改至75%
3. 设定参数innodb_adaptive_flushing（脏页自适应刷新），通过判断产生redo log的速度决定脏页刷新速度
4. 设定参数innodb_purge_batch_size，每次full purge回收undo页的个数可自己设置。

Master Thread 1.2.x版本：
分离出page cleaner线程，减轻主线程压力，提高并发

Innodb 关键特性

1. 插入缓冲
2. 二次写
3. 自适应hash索引
4. 异步IO
5. 刷新邻接页

插入缓冲 Insert Buffer

Innodb 1.0.x 之前版本insert buffer插入缓冲

插入缓存属于缓冲池的一部分，它缓冲的对象是 - 辅助非唯一索引
首先Innodb的主键索引是“聚集”的，辅助索引是“非集聚”的。具体可参考以下链接
因为对于有序的主键索引来说，顺序读写性能比较好，而辅助索引由于非唯一且不确定顺序，在某些情况会出现随机读写的情况，性能较差。

Innodb使用插入缓冲的条件：
1. 索引是辅助索引
2. 索引是非唯一的

插入缓冲指的是对于符合条件的索引的插入，会先写到缓冲区的插入缓冲中，然后按照一定的频率合并到物理索引上面。

不足：
1. 会出现宕机恢复慢的情况，因为有很多数据没有合并到物理索引中。
2. 在写密集的条件下，会过多占用缓冲池内存，默认最大可占有1/2

Innodb 1.0.x 版本change buffer改变缓冲
改变缓冲是插入缓冲的升级版，即增删改所有操作都会缓冲起来，再以一定的频率合并到物理索引中。
包括一下三部分：
1. insert buffer
2. update buffer
3. delete buffer

Innodb 1.2.x 版本change buffer改变缓冲
引入参数innodb_change_buffer_max_size限制change buffer最大所占比例，默认25%，即1/4

插入缓存实现原理

相关数据结构：
1. Insert Buffer是B+树结构，存放于全局共享表空间。节点结构：space（标记着唯一的表id）+marker+offset/page_no（页在表中的偏移量）
2. Insert Buffer Bitmap结构，表示的是指定page_no辅助索引页（物理索引页）的存放情况：

IBUFF_BITMAP_FREE：表示该辅助索引页的可用空间数量 - 0表示无剩余可用空间、1表示大于1/32、2表示大于1/16、3表示大于1/8
IBUF_BITMAP_BUFFERED：表示该辅助索引页（物理索引页）有记录缓存在于插入缓冲B+树中：1表示有

合并插入缓冲的条件：
1. 辅助索引页（物理索引页）被读取到缓冲池：正常select查询会先检查插入缓冲Bitmap页，检查IBUF_BITMAP_BUFFERED - 辅助索引页是否有记录缓存在于Insert Buffer B+树中，如果有则合并至辅助索引页。
2. 辅助索引页无剩余空间了：若合并Insert Buffer 后该页小于1/32可用空间 - 说明Insert Buffer 不能再攒记录了，要不没空间再进行合并了，所以强制合并至辅助索引页，保证成功。
3. Master Thread 每秒或者每10秒合并一定比例数量的辅助缓冲页

合并插入缓冲过程
即便Insert Buffer B+是个有序的结构，即（space，page_no），但innodb选择页时并非有序（每次space和page都有可能不同）：
Innodb会随机选择Insert Buffer B+树的一个页，然后读取该页space与所需要的一定数量的同space的页。此算法在复杂情况下有更好的公平性。

二次写 Double Write

二次写带来的是对数据页可靠的保证，解决了部分写失效的问题。
当数据页部分写入的时候，数据库宕机了，若不存在备份的情况下，可能会导致数据丢失。
重做日志redo log的记录是对页的物理操作，即如果出现了页写入的过程中发生宕机，那么现在物理磁盘的页已经被破坏了，对破坏的物理磁盘页进行重做日志，也是没有任何意义的，所以，我们需要一个页的副本，在发生部分写失效时，根据此副本和重做日志还原该页，再使用重做日志进行数据重做。

二次写 Double Write由两部分组成：
1. 内存中的Double Buffer，大小为2M
2. 物理磁盘的Double Wirte，共享表空间中连续128个页，2个区（簇），大小为2M
共享表空间和独立表空间

二次写的过程：
1. Innodb对缓冲池中的脏页进行刷新的时候，不会立即写入磁盘，而是先使用memcpy函数复制至Double Buffer
2. 等到Double Buffer满了之后，Innodb通过2次每次将1M的数据顺序拷贝至Double Write，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题，注意这里效率不会太低，因为这里的写入是顺序写
3. 等到Double Write写入完成后，Innodb将Double Buffer中的页，离散的写入磁盘各个表空间中

启用参数skip_innodb_doublewrite，将会禁止二次写。
1. 如果服务器文件系统本身提供了控制部分写失效的防范机制，可以启用
2. 通过主从结构对数据进行备份，可以启用

自适应Hash索引 AHI

AHI 根据缓冲池中的B+树进行构造。
Innodb存储引擎会自动根据访问的频率和模式来自动为某些热点页建立Hash索引
注意：范围查询不能使用Hash索引

异步 IO AIO

优势：
1. IO 并行：IO 操作可以并行发出，不必等待前面的IO完成
2. IO Merge操作：将多个IO合并成一个IO，提高IOPS的性能

查询（space,page_no）:(8,6)+(8,7)+(8,8)，每个页16k
原先需要三个IO操作完成，Innodb会判断这三个页是连续的，因此只发送一个查询IO查询48k的页

Native AIO只支持Window和Linux，Mac OSX不支持
在Innodb存储引擎中，read ahead方式的读取、脏页的刷新、磁盘的写入都是有AIO方式完成

刷新邻近页

当刷新一个脏页的时候，Innodb存储引擎会检测该页所在区的所有页，如果都是脏页，使用AIO的IO Merge，一起刷新至磁盘

注意问题：
1. 一个不怎么脏的页，相对与一个被修改多次很脏的页，刷新是否值得。
2. 固态硬盘有着较高的IOPS，建议不开启。（IOPS (Input/Output Operations Per Second)，即每秒进行读写（I/O）操作的次数，多用于数据库等场合，衡量随机访问的性能）

Innodb关闭和开启

参数innodb_fast_shutdown
- 0 表示Mysql正常关闭时，需要完成全部的full purge（删除undo页）、insert buffer merge（插入缓冲合并）以及所有脏页刷新会磁盘
- 1 表示Mysql正常关闭时，需要完成所有脏页刷新会磁盘
- 2 表示Mysql正常关闭时，什么都不用完成，而是将日志写入日志文件中，下次启动进行恢复操作（使用kill命令可使用设置此参数进行启动）

参数innodb_force_recovery，数值大包含数值小的影响
- 1 忽略检查到的corrupt表
- 2 阻止Master Thread 运行，例如阻止full purge
- 3 不进行事务回滚
- 4 不进行插入缓冲合并操作
- 5 不查看撤销（undo）日志，未提交的事务会视作已提交
- 6 不进行前滚操作

Innodb提供的约束

1. Primary Key
2. Unique Key
3. Foregin Key（Mysiam不支持）
4. Default
5. Not Null

外键约束的DDL操作：
1. cascade：更新父表的同时也更新子表
2. set null：更新父表的同时，将子表相应数据设为null
3. no action：不允许进行操作，抛出错误
4. restrict：默认，Mysql中其功能与no action相同

Innodb触发器

触发器的作用实在执行insert、delete和update命令之前和之后自动调用

视图

• 有命名的虚表，由sql查询来定义
• 可更新视图
• 物化视图（Mysql不支持，Oracle支持，该视图不是虚拟表，而是实际存在的表，用于预先计算并保存多表的链接【Join】或聚集【group by】等耗时操作，从而快速得到结果，Mysql可以通过手动编写增量代码或者使用触发器，定时器实现）

分区表

分区的可能给某些sql带来性能上的提升，主要用于数据库高可用性的管理
sql优化器分区修剪，对特定范围的查询只查特定的分区。
- Range 分区
- List 分区
- [Linear] Hash 分区
- [Linear] key 分区
- Columns 分区

子分区：Mysql对与range和list分区还可以进行hash和key分区，每个子分区的数量必须相同

分区中Null值的处理：
Mysql总是将Null值视为比Null值要小，所以一般Range分区会在左边插入数据

• OPTL（在线事务处理）：100W行的数据建出来的索引和1000W行数据建出来的B+索引的高度差不多，没必要进行优化
• OPAL（在线分析处理）：如果需要扫描一年的数据，可以采取分区的形式读出来，分区修剪

Innodb存储引擎索引

• B+树索引
• 全文索引
• Hash索引（自适应，不能人为干预，优点：随机读取最快；缺点：排序和范围查询效率低）

B+树索引

1. B+树索引并不能直接找到指定键值的数据，而是先找到指定键值所在的页（缓冲池内的数据页），然后加载进内存，最后在内存中查询，最后的到要查询的数据。
2. B+索引在数据库中具有高扇出性，因此在数据库中高度一般不超过2~4层
3. Innodb的B+树索引还可以分为聚集索引和辅助索引，最大的不同是叶子结点是否存在一整行完整的记录数据。

聚集索引：

• Innodb存储引擎是索引组织表，即表中的数据按照主键的顺序存放，而聚集索引就是按照每张表的主键构造的一棵B+数，同时叶子结点存放的是一整行的记录数据，也将叶子结点称为数据页，多个数据页之间通过双向链表进行连接，所以逻辑上是连续的，但物理上不连续（连续维护开销大）。
• 由于实际数据也只能按照一颗B+树排序，因此每张表只能有一个聚集索引。
• 一般情况下，查询优化器倾向于使用聚集索引（主键），因为能够快速的定位到指定的数据页，然后只需要一次页的扫描就能获取到指定记录。
• 优点：按照主键排序不需要额外filesort、范围查询。

辅助索引（非聚集索引）：

• 叶子节点不包含行记录的全部数据，只包含键值和相对于的书签（就是聚集索引的索引键，即主键）。
• 查询辅助索引时，先查询辅助索引得到聚集索引的索引值，再去聚集索引查询指定数据页，然后加载进内存检索数据。
• MS SQL Server和Myisam存储引擎采用堆表的方式，即无论主键索引还是辅助索引都是非聚集的。
• Fast Index Creation（FIC）和在线架构改变（OSC）：

Fast Index Creation：
Mysql 5.5之前版本，对于索引的创建和删除操作过程：
1. 创建临时表
2. 将数据从原表导入临时表
3. 删除原表
4. 重命名临时表

这样做造成的问题是，索引维护的耗时长，容易导致长时间的服务不可用

Innodb 1.0.x版本支持Fast Index Creation(只针对辅助索引，主键索引同样需要临时表操作)：
1. 辅助索引创建：在数据表中增加S锁（只能读不能写），如果创建期间有大量写操作，同样会造成不可用。
2. 辅助索引删除：直接在视图上更新，将辅助索引的空间标记为可用，同时删除所有与辅助索引相关的信息。


facebook 最早实现的osc主要流程是创建临时表，将原表数据分片输出至外部文件，然后导入新表，最后交换表名字，期间的所有DDL操作（insert，update，delete）都会存储在另外一张表上，方便重建表时进行回放操作。

Mysql 5.6版本开始支持online DDL操作：
1. 辅助索引创建与删除
2. 改变自增长值
3. 添加或删除外键约束
4. 列的重命名
以上的操作都允许进行INSERT,UPDATE和DELETE的DML操作。

alter table add xxx 
alagorithm = [default|copy(创建临时表)|inplace(不创建临时表)]
lock = [default|none(不加锁，并发最高)|shared(阻塞写)|exclusive(阻塞所有读写)]

Online DDL原理：
将DML操作写入一个缓存(innodb_online_alter_log_max_size)中，完成索引创建后再重做应用到表上

注意：sql优化其不会选择正在创建的索引

Cardinality 统计值

Cardinality 统计值越高，代表该字段取值范围越大，具有很高的选择性，适合建索引

如何在线统计 Cardinality 值，首先他是个预估值，不是准确值，其次在生产环境中数据库也不允许实时统计：

• 统计 Cardinality 值，一般发生在INSERT和UPDATE两个操作中
• 表中1/6的数据发生了变化
• 发生变化的次数超过2 000 000 000次

计算 Cardinality 值过程：

1. 取得B+树的叶子结点的数量，记为A
2. 随机取得B+树索引中的8个叶子结点，统计每个叶子结点的不同记录的个数，记为P1,P2,P3….
3. 计算 Cardinality 值：Cardinality=(p1+…p8)*A/8

注意，由于叶子结点的选取是随机的，所有Cardinality 不是固定的，除非A的数量小于等于8，怎么选都会选到被计算的8个叶子结点。

联合索引

如果存在单索引和联合索引让SQL优化器选择，SQL优化器会选择单索引，因为理论上每一页能存下更多数据。

覆盖索引

• Innodb 1.0以上支持
• 覆盖索引的意思是能在辅助索引搜索得到结果，就不会选择取聚集索引里进行搜索，因为辅助索引的大小远小于聚集索引，例如count(*)操作

优化器不使用辅助索引的情况

通过辅助索引查询数据，会先从辅助索引查出bookmark，然后在聚集索引中查出具体记录
如果涉及到大范围(一般20%)的查询，辅助索引的批量随机读的效率要低于聚集索引的全表顺序读，所有不使用辅助索引
机械硬盘->顺序读
固态硬盘->随机读性能高，可考虑Force index
强制使用索引 select * from xxx force index(xx)

根据索引查询优化

• MRR （Multi-Range Read 优化）
• ICP（Index Condition PushDown 优化）

MRR：
使用辅助索引进行查询的时候，减少随机读，将随机读整理成为顺序读，提高查询效率
好处：

1. 使随机访问比较有顺序：在查询辅助索引的时候，先根据查询的结构，按照主键排序，然后再去聚集索引里面查找指定的记录（避免随机读，而是有顺序的访问）
2. 减少缓冲页的淘汰的次数：因为访问比较有顺序，即相邻的数据读取的时机必定相邻，不会重复读入相同的数据页。
3. 批量处理对键值的操作
4. MRR还可以将范围查询条件进行拆分，如key1>=100 and key1<=200 and key2 = 1000，会被拆分成（100，1000）、（101，1000）…..，这样的好处是及时过滤掉无用的数据，若不拆分查询，首先会先查询key1符合的数据集，然后用key2的条件进行过滤和筛选。若无用数据数量很巨大的时候，（大表）会提高一定的查询性能。

ICP（针对辅助索引是联合索引的索引）：
作用：在某些查询下，将部分where条件放在数据存储层上，大大减少了上层sql对数据的索取，提高了数据库的性能
若现有联合索引（key1,key2,key3）
如查询条件：key1 = 100 and key2 like “%200%” and key4 like “%200%”
1. 若不支持针对联合索引的where条件下推，由于联合索引不支持%开头的模糊查询，所以只能先查出key1的结果，然后再从前面的结果来慢慢筛选。
2. 那么如果下推查询条件，会在取出索引时，同时进行key1和key2的where条件过滤，再去获取记录，通过提前筛选记录，减少数据传输的性能开销。

注意：并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中,还是要读取整表的记录到server端做where过滤

Innodb自适应Hash索引

不能人为干预，采用里链表方式解决冲突，只能用来等值查询。
innodb_adaptive_hash_index

全文索引

全文索引通常使用倒排索引实现：

普通数据字段：
Documentid，Text（文本）

倒排索引：
WordId，Word（单词），Document（该单词在哪些document中出现了，以及出现的位置）

Innodb全文索引原理：
Word（Innodb在此字段设有索引，提高搜索效率）、ilist（等价于Document）

Innodb全文索引实现：
倒排索引需要将word存在一个表中，这个表称为Auxiliary Table（辅助表），此表为持久化的表，存放于磁盘。
还有一个概念称为FTS Cache Index（全文检索索引），结构为红黑树，按照word和ilist进行排序，他的作用类似与插入缓存的作用，降低更新磁盘的频率。

每次对全文索引进行查询前，FTS会将对应的word字段合并到Auxiliary，然后再进行查询。
对于Innodb而言，每次事务的提交会将分词写入至FTS，然后通过批量延时更新写入到磁盘中，上述操作只发生在事务提交时。
对于每次事务的提交，插入分词的操作都会完成，但是删除分词的操作只是删除FTS缓存中的记录，实际上Auxiliary并不会删除，而是将documentId另外存放在Deleted Auxiliary Table中。
因此随着应用的允许，索引所占内存会越来越大，Innodb允许手动清理Deleted Auxiliary Table的操作：optimize table

若optimize table需要清理的文档数量特别多，会占用很多的时间，降低并发的性能，所以还可以通过参数控制清理文档的数量。

Innodb全文索引还存在一下限制：
1. 全表只能有一个全文索引
2. 多列组成的全文索引列要求字符集和排序规则相同
3. 只支持英文

排序规则（以下相关性按照降序排序）：
1. word是否出现在文档中
2. word在文档中出现的数量
3. word在索引列出现的数量
4. 多少个文档包含此word

Innodb锁机制

Innodb在很多场景需要锁的机制：如表数据修改，缓冲池LRU队列的修改等等
- Innodb实现的是行级锁，而且不需要锁升级，即因为持有一个锁和多个锁的开销是相同的
- Mysaim实现的是表锁，并发插入性能较差
- SQL Server的2005之前的版本实现的是页锁，页锁对于表锁来说，并发能有更大的提高，但是对于热点数据页无能为力
- SQL Server的2005之后的版本支持乐观并发和悲观并发，乐观并发是指使用行级锁进行锁定，但是锁的开销会随这锁的数量增多，于是自动升级成悲观并发的表锁
- Oracle和Innodb实现类似

Innodb里lock和latch的区别：
1. latch被称为轻量级锁（自旋和等待），因为其要求锁定的时间非常短，否则应用的性能会很差，主要实现的形式是读写锁和互斥量，没有死锁检测机制。主要的作用是保证并发操作临界资源的正确性。
2. lock锁定的是数据库的对象，如表、页、行，一般在数据库commit和rollback的时候释放，一般情况下有死锁检测机制。

共享锁（S读）和排他锁（X写），都是行级锁:
1. 针对某一行数据，共享锁之间能够兼容
2. 针对某一行数据，排它锁和其他的锁不能兼容

意向锁，为表级锁，目的是为了表明某个事务正在锁定一行或者将要锁定一行：
1. 意向共享锁（IS）：想要读取某张表的某几行数据
2. 意向排他锁（IX）：想要写入某张表的某几行数据

意向锁的作用：
Innodb中存在着行级锁和表锁
1. 行级锁：对一行记录加锁，只影响一条记录。通常用在DML语句中，如INSERT, UPDATE, DELETE等。
2. 表级锁：对整个表加锁，影响标准的所有记录。通常用在DDL语句中，如DELETE TABLE,ALTER TABLE等。
3. 意向锁，为了解决行级锁和表级锁的冲突，例如说你正在修改某一行的数据（行级锁定，此时另外一个事务需要对表的结构进行更改，此时行级锁和表级锁的锁定对象不同，于是需要一个表级锁 - 意向锁来解决这个冲突，阻塞表级的修改，直到行级锁的释放）

注意：
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。
（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。
（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁
（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

一致性非锁定读和MVCC（undo页）

作用和上图所示，当数据库的某行数据被加了X锁，一致性非锁定读不会因为X锁而被阻塞，它回去读一个快照的数据，对于不同的隔离级别，快照数据的定义不一样：
Innodb的可重复读、提交读两种隔离级别支持一致性非锁定读：
1. 可重复读的快照数据：事务开始时的那个版本
2. 提交读的快照数据：最新的那个版本

MVCC：每行记录有多个版本，这种技术称为多版本并发控制技术
同样对于某些查询，只能说一致性锁定读。
在InnoDB中，会在每行数据后添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。 在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。 在可重复读事务隔离级别下：

1. SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。
2. INSERT时，保存当前事务版本号为行的创建版本号
3. DELETE时，保存当前事务版本号为行的删除版本号
4. UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行

通过MVCC，虽然每行记录都需要额外的存储空间，更多的行检查工作以及一些额外的维护工作，但可以减少锁的使用，大多数读操作都不用加锁，读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，也只锁住必要行。

注意：多个锁的维护使用锁定队列

自增长与锁

1. 自增长最初的版本使用特殊的表锁结构，锁会在每次插入完成后释放，注意不是事务完成，但是锁的开销比较大。
2. 新版本还支持还有一种自增长的机制，使用轻量级互斥量进行并发控制。

外键和锁

Innodb对于外键列，如果没有显式进行加索引，他会自动为外键列增加索引。
对于外键值的更新和插入，首先会先去读取父表的记录（一致性锁定读），再进行插入操作。

（不可重复读）行锁的3种算法

1. Record Lock：只锁定单个行记录
2. Gap Lock：间隙锁，锁定某个区间，但是不包括记录本身
3. Next-Key Lock：锁定某个区间，包括记录本身
   Next-Key Lock：用来锁定不存在的记录，防止其他事务对当前事务的干扰，保证每个事务都能安全插入查询

锁会在事务结束的时候进行释放
1. 如果该列是聚集索引的主键列（单列唯一索引）：Record Lock
2. 如果该列是聚集索引的主键列（多列唯一索引）：Next-Key Lock
2. 如果该列是辅助索引的索引列：

Record Lock（锁定聚集索引的单列唯一索引）| Next-Key Lock（锁定聚集索引的多列唯一索引） 
Next-Key Lock（锁定辅助索引的索引区间） 
Gap Lock（锁定辅助索引的索引下一个区间，为了解决幻像问题，即当前事务可以读到其他事务的数据）

注意：辅助索引得去找聚集索引实现可重复读事务，MVCC版本只存在与聚集索引中

脏读（未提交读）：
当前事务读到其他事务未提交的数据

不可重复读（提交读 - 幻像问题）：
当前事务读到其他事务已提交的数据（大部分情况下可以接受）

丢失更新（串行化）：
当前事务的更新被其他事务的更新覆盖掉
理论上数据库不会出现，因为更新之前会持有锁
逻辑上会出现：

事务T1查询一行数据，显示给用户1
事务T2查询同样数据，显示给用户2
用户1修改数据，T1提交
用户2修改数据，T2提交

用户1的操作被用户2覆盖掉

隔离级别 脏读（Dirty Read） 不可重复读（NonRepeatable Read） 幻读（Phantom Read）
未提交读（Read uncommitted） 可能 可能 可能
已提交读（Read committed） 不可能 可能 可能
可重复读（Repeatable read） 不可能 不可能 可能
可串行化（Serializable ） 不可能 不可能 不可能

异常回滚

Innodb存储引擎在大部分的情况下都不会对异常进行回滚
很危险：会话B因为会话A的阻塞导致事务超时异常，但是不会对事务进行rollback或者commit，需要手动处理。

死锁

Innodb死锁处理：
wait-for graph（等待图），数据库要求存储两种信息，锁的信息链表，事务等待链表

然后构建一个事务相互的关系图

然后死锁的主动检测是通过DFS算法来寻找死锁回路

锁升级

Innodb对页采用位图的形式保存锁，一个锁和多个锁的维护开销差不多，而且位图能保存更多的锁信息，相反SqlServer对记录进行加锁，所以锁的占用信息增长更快。
Innodb的行锁是通过给索引项加锁实现的，这就意味着只有通过索引条件检索数据时，innodb才使用行锁，否则使用表锁。在数据处理中，当操作的索引列数据较多时，行锁也将会升级，导致非索引项的操作也会处于锁等待状态。

事务类型

1. 扁平事务
2. 带保存点的扁平事务，可恢复到任意保存点
3. 链事务，带保存点的扁平事务类似，区别是只能恢复到上一个保存点
4. 分布式事务
5. 嵌套事务（Innodb不支持）

事务的实现

事务的4个性质：原子性（A），隔离性（I），一致性（C），持久性（D）

事务提交时，必须等待操作写入redo日志文件进行持久化，才算commit操作完成
Innodb日志：
1. redo log：用来保证是事务持久化（物理日志，存放在日志文件中，顺序写，用来恢复数据库，一般不会进行读操作）
2. undo log：用来保证事务回滚和MVCC（逻辑日志，存放在共享表空间中，随机读写）

每次写入redo日志文件，都会调用fsync操作，而fsync操作的性能取决于磁盘的性能，所以磁盘的性能决定了事务的提交的速度
group commit：批量写入redo日志后，再进行fsync，缺点恢复数据库时可能丢失数据

事务提交后不能直接删除该事务的undo log，因为mvcc需要通过undo log来查询之前版本的信息，所以事务提交后，会将事务放入链表（history list）中，是否能够删除，取决于purge线程判断（数据是否被其他事务引用）。

1. delete：不会直接删除记录，而是将delete flag标记为1，而记录最终的删除，实在purge操作进行时。
2. update：先delete再insert

备份类型

1. 热备（不影响数据库运行）
2. 冷备（数据库停止拷贝数据库文件）
3. 暖备（对数据库进行加锁，然后进行备份）