MySQL思考-存储引擎(InnoDB)

一 MySQL体系架构

MySQL Server组成部分

	Connection Pool :连接池组件
	Management Services & Utilities :管理服务和工具组件
	Parser： 查询分析器组件
	Optimizer:  优化器组件
	Caches & Buffers： 缓冲池组件
	Pluggable Storage Engines：存储引擎
	File System : 文件系统

1.1 各层之间解释

1. 连接层

完成一些连接处理、授权认证。

2. 服务层

SQL分析和优化、确定是否使用索引。

3. 引擎层

负责MySQL中数据的存储和提取。(MySQL中引擎是插拔式)

4. 存储层

主要将数据存储到文件系统上，并完成和存储引擎之间交互。

1.2 InnoDB和MyISAM之间比较

MySQL中支持存储引擎比较多，最常用两种InnoDB、MyISAM。

1. 对比
   

二 InnoDB的剖析

2.1 主要模块介绍

2.1.1 缓冲池

1. 为什么要有缓冲池

InnoDB是基于磁盘存储，访问物理磁盘和内存速度相差很大，为了弥补二者I/O效率差值，把经常使用的数据加到缓存池中，避免每次进行磁盘IO

2. 缓冲池中能存储什么？

索引页、数据页、undo页、插入缓存、自适应hash、锁信息。

3. 缓冲池在读取、更新数据流程

读取

数据库读取页数据时，先判断缓冲池是否有相同页。如果有直接返回，没有则从磁盘中读取页放到缓冲池中，再返回。如果下次再读相同页时，直接缓存池返回。

更新

对数据库中的页发生修改时，先修改缓冲池中的页，再以一定规律刷新到磁盘上，保证了缓冲池中和磁盘中数据一致。页从缓冲池刷新到磁盘中的操作并不是每次更新都执
行，而是通过checkpoint机制刷新回到磁盘。

4. InnoDB内存

查询缓冲池大小命令

show variables like 'innodb_buffer_pool_size';

更改缓冲池内存大小命令

执行命令  vi /etc/my.conf
在打开的配置文件中加入配置  innodb_buffer_pool_size=268435456

查询缓冲池实例个数命令

InnoDB允许多个缓冲实例，根据页的哈希值分配到不同的缓冲实例中，从而减少数据库资源竞争，提升并发能力。

show variables like `innodb_buffer_pool_instances`

2.1.2 后台线程

1. Master Thread

负责将缓冲池中的数据异步刷新到磁盘中，包含脏页的刷新、合并插入缓存、undo页面回收。

2. IO Thread

InnoDB中使用大量的AIO处理IO请求，极大提高数据库性能。IO Thread主要负责这些IO请求回调。

3. Purge Thread

主要用于回收事务已经提交的udo log。(在事务提交后，undo log可能不用了，用它回收)

4. Pager Cleaner Thread

协助Master Thread刷新脏页到磁盘的线程，减轻Master Thread 的压力。

2.1.3 文件(了解)

1. .frm文件

表结构定义

2. 系统表空间

系统表空间是InnoDB数据字典，二次写缓冲区，更改缓冲区和撤消日志的存储区。

4. 独占表空间

innodb中设置了参数 innodb_file_per_table 为 1/ON，则会将存储的数据、索引等信息单独
存储在一个独占表空间，因此也会产生一个独占表空间文件（ibd）

5. redo log

介绍

重做日志，用于恢复已经提交事务的页修改。比如：发生修改并已提交事务，但是缓存池没有将数据同步到磁盘中，系统崩溃。当数据库恢复后使用redo log进行数据恢复。

在磁盘中名称

redo log在磁盘中保存的名称为 ib_logfile0，ib_logfile1。

6. bin log

二进制日志，包含表结构变更、DDL、DML。

7. 其他

错误日志、慢查询日志、查询日志

2.2 InnoDB的逻辑存储结构

1. 表空间

是InnoDB逻辑结构的最高层，大部分数据都存在于共享表空间ibdata1中。如果用
户启用了参数 innodb_file_per_table ，则每张表都会有一个表空间（xxx.ibd）,里面存放表
中的数据、索引和插入缓存Bitmap页。其他的数据如undo log、插入缓存索引页、系统事务信息、
二次写缓存都是在共享表空间中。

2. 段

表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB存储引擎是基于索引组织
的，因此数据即是索引，索引即数据。数据段就是B+树的叶子节点， 索引段即为B+树的非叶子节点。
InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制。

3. 区

区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一
个区中一共有64个连续的页。

4. 页

页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。
为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。

5. 行

InnoDB 存储引擎是面向行的（row-oriented)，也就是说数据是按行进行存放的，每个页存放的行
记录也是有硬性定义的，最多允许存放 16KB/2-200 行，即 7992 行记录。

2.3 checkpoint

1. 介绍

由于日常的DML语句操作时，首先操作的是缓冲池，并没有直接写入到磁盘，这有可能会导致内存中的
数据与磁盘中的数据产生不一致的情况，而与磁盘中数据不一致的页我们成为"脏页"。 而
checkpoint的工作，就是将内存中的脏页，在一定条件下刷新到磁盘。

提交事务流程

如果在从缓冲池将页数据刷新到磁盘的过程中发生宕机，那么数据就无法恢复了；为了避免这种情况的
发生，采用了Write Ahead Log(WAL)策略，即当事务提交时，先写重做日志(redo log)，再修改
缓冲池数据页，最后通过Checkpoint刷新到磁盘（事务提交会触发checkpoint）。这样正在执行的
事务，因为存在日志都可以被恢复，没有日志的事务还没有执行也不会丢失数据。

2. 作用

缩短数据恢复时间

当数据库发生宕机时，数据库不用重做所有的日志，因为Checkpoint之前的页都已经刷新会磁盘了，
故数据库只需要重做Checkpoint之后的日志就好，这样就大大缩短了恢复时间。

缓冲池不够用时，需要先将脏页数据刷新到磁盘中

redo log大小是固定的, 当前的InnoDB引擎中， 重做日志的设计都是循环使用的，并不是无限增
大的。

重做日志不可用时，刷新脏页到磁盘

当缓冲池不够用时, 根据LRU算法溢出最近最少使用的页, 如果此页是脏页,则强制执行
Checkpoint, 刷新脏页到磁盘。

write pos表示日志当前记录的位置，当ib_logfile_1写满后，会从ib_logfile_0从头开始记
录；check point表示将日志记录的修改写进磁盘，完成数据落盘，数据落盘后checkpoint会将日
志上的相关记录擦除掉，即write position ->checkpoint 之间的部分是redo log空着的部
分，用于记录新的记录，checkpoint->write position 之间是redo log待落盘的数据修改记
录。当write postion追上checkpoint时，得先停下记录，先推动checkpoint向前移动，空出位
置记录新的日志。

4. 分类

Sharp Checkpoint

Sharp Checkpoint 发生在数据库关闭时，将所有的脏页都刷新回磁盘，这是默认的工作方式，参
数：innodb_fast_shutdown=1。

Fuzzy Checkpoint

在InnoDB存储引擎运行时，使用Fuzzy Checkpoint进行页刷新，只刷新一部分脏页。

2.4 数据库发生插入、修改数据过程

1. 插入缓存

行记录的插入顺序一般是按照主键递增的顺序进行插入的，插入聚集索引一般是顺序的，不需要磁盘的随机读取
，插入操作一般很快完成。

不可能每张表上只有一个聚集索引，在更多的情况下，一张表上有多个非聚集的辅助索引（secondary index）
对于非聚集索引，叶子节点的插入不再是顺序的了。这时就需要离散地访问非聚集索引页，插入性能在这里变低了。

对于非聚集索引的插入或更新操作，先判断插入的非聚集索引页是否在缓冲池中。如果在，则直接插入；如果不在，
则先放入一个插入缓冲区中然后再以一定的频率执行插入缓冲和非聚集索引叶子节点的合并操作，这时通常能将多
个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对非聚集索引执行插入和修改操作的性能。

2. 两次写

为什么引出两次写

数据库宕机了，需要用重写日志防止数据丢失。如果物理页完全是未写入状态，可以用重做日志恢复。但是如果物理页前面2K写了新的数据，
但是后面2K还是旧的数据，则种情况下就无法使用重做日志恢复了。

两次写的原理

在写物理页时，并不是直接写到真正的物理页上去，而是先写到一个临时页上去，临时页写完后，再写物理页。这样一来：
A. 如果写临时页时宕机了，物理页还是完全未写之前的状态，可以用重做日志恢复。
B. 如果写物理页时宕机了，则可以使用临时页来恢复物理页。

两次写的过程

InnoDB中共享表空间中划了2M的空间，叫做double write，专门存放临时页。
InnoDB还从内存中划出了2M的缓存空间，叫做double write buffer，专门缓存临时页。
每次写物理页时，先写到double write buffer中，然后从double write buffer写到double
write上去。最后再从double write buffer写到物理页上去。

4. 自适应hash索引(不需要人工作指定，系统根据情况自动完成)

InnoDB索引是B+树，可以使用范围查找。而对于Hash索引则只能满足 =，<>,in查询，不能使用范围查询。

这是因为B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般为3-4层，故需要3-4次
查询。而 Hash 索引在进行数据检索的时候效率非常高，通常只需要 O(1) 的复杂度，。InnoDB存储引擎
会监控对表上各索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引。

什么时候创建索引

如果某个数据经常被访问，当满足一定条件的时候，就会将这个数据页的地址存放到 Hash 表中。而且自适应 Hash 索引
只保存热数据（经常被使用到的数据），并非全表数据。因此数据量并不会很大，因此自适应 Hash 也是存放到缓冲池中，
这样也进一步提升了查找效率。

查看命令

show rariables like `%adaptive_hash_index`

5. 异步IO

为了提高磁盘的操作性能，在InnoDB存储引擎中使用异步非阻塞AIO的方式来操作磁盘。

如果用户发出的是一条索引扫描的查询，那么这条SQL查询语句可能需要扫描多个索引页，也就是需要进行多次的IO操作。
发出一个IO请求后立即再发出另一个IO请求，当全部的IO请求发送完毕后，等待所有的IO操作完成，

6. 刷新临接页

InnoDB提供刷新临近页功能：当刷新一脏页时，同时检测所在区(extent)的所有页，如果有脏页则
一并刷新，好处则是通过AIO特性合并写IO请求，缺点则是有些页不怎么脏也好被刷新，而且频繁的更
改那些不怎么脏的页又很快变成脏页，造成频繁刷新。对于固态磁盘则考虑关闭此功能（将
    设置为0）。

三 InnoDB的事务

3.1 什么是事务

1. 概述

事务可以由一条sql组成也可以由多条sql组成。在一组事务中，这组sql要么全部成功，要么全部失败。

2. 事务特性(ACID属性)
   

3.2 事务隔离级别

1. 并发事务带来问题
   
2. 事务隔离级别
   
   备注

MySQL默认隔离级别是可重复读。

3.3 事务的实现(redo log、undo log)

1. 如何实现事务实持久性(redo log)

重做日志缓冲(redo log buffer)和重做日志文件(redo log)。事务提交后会将所有信息存放到该日志中。用于刷新脏页到磁盘中、发生错误，用于数据恢复。

一个事务中sql

start transaction;
select balance from bank where name="Tom";
-- 生成 重做日志 balance=8000
update bank set balance = balance - 2000;
-- 生成 重做日志 account=2000
update finance set account = account + 2000;
commit;

上面sql执行流程

2. 事务回滚(undo log)

用于记录数据被修改前的信息。然后发生错误才进行回滚。

假如由于系统错误或者rollback操作而回滚的话可以根据undo log的信息来进行回滚到没被修改前的状态

四 引擎选择(InnoDB、MyISAM)

4.1 InnoDB

支持事务，在并发条件下要求数据的一致性。除了新增和查询外还包含很多删除和编辑。

4.2 MyISAM

如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，
那么选择这个存储引擎是非常合适的。如：日志、用户评价、足迹等信息的存储