[沫忘录]MySQL InnoDB引擎

逻辑存储结构

InnoDB采用 “表、段，区， 页、行” 这样的层级结构进行存储。

• **表空间(tablespace)**ibd文件，主要用于存储记录、索引等数据，一个mysql实例可有多个表空间，甚至能通过innodb_file_per_table此参数为每张表都申请一个表空间。

• 段(segment)，分为数据段(leaf node segment)，索引段(Non-leaf node segment)和回滚段(rollback segment)。
  Innodb的所有数据是基于索引组织的，所以数据段即B+树的叶子节点，索引段即为B+树的非叶子节点。

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

• 页(page)，InnoDB存储引擎磁盘管理的最小单元，每个页默认16KB。 为了保证页的连续性以提高磁盘IO的效率，InnoDB储存引擎每次先从磁盘申请4-5个区。
  表中的记录和索引这些数据都是在页中存储的。

• (row)，InnoDB储存引擎数据即按行进行存放的，即行里可以存储多个字段。

  row中有三个隐藏字段：

  • Trx_id(DB_TRX_ID) 记录修改某条记录的事务的Id
  • Roll_pointer(DB_ROLL_PTR) 每次对某条记录进行修改时，都会将旧版本记录写入undo log日志中。这个字段如指针般指向该旧记录的地址。
  • row_id(DB_ROW_ID) 当表结构没有指定主键时，便会自动生成该字段，以作为隐藏主键。

架构

内存结构

• buffer pool: 缓冲池是主内存中的一个区域, 里面缓存从磁盘读取的数据。再执行增删改查时，会先操作缓冲池中的数据(如若缓冲池没有，则从磁盘中加载数据到内存中)。当数据操作完成后，不会立即写入磁盘，而是以固定频率将已操作的数据同时写回磁盘，降低磁盘IO。

  缓冲区以Page页为单位，底层采用链表数据结构管理Page。而page主要分为3类：

  • free page: 空闲page, 未曾使用。
  • clean page: 被使用page, 数据没有被修改过。
  • dirty page: 脏页, 被使用的page，数据有被修改过，与磁盘数据不一致。

• change Buffer: 更改缓冲区(主要针对于非唯一二级索引页，这些数据如果直接从磁盘中查找并读取到buffer pool中，效率是非常低的)。在执行DML语句时，如果这些数据Page没有在buffer page中，则不会直接操作磁盘，而是将数据变更这一操作缓存在change buffer中，当执行DQL语句时会顺便将所需数据读取到buffer pool中时，再对相应数据进行merge操作。

• adaptive hash index: 自适应hash索引, 用于优化对buffer pool数据的查询。当数据能够使用hash索引时, innoDB储存引擎能够自动将B+树索引优化为hash索引，所以称其为自适应hash。

  设置参数： adaptive_hash_index

• log buffer: 日志缓冲区，用来保存要写入到磁盘中的log日志数据(redo log、undo log)，默认大小为16M，日志缓冲区的日志会定期刷新到磁盘中。如果需要增删改的事务有许多行，则增加日志缓冲区可以减少log buffer溢出而产生的磁盘IO。

  设置参数：

  innodb_log_buffer_size：缓冲区大小

  innodb_flush_log_at_trx_commit: 日志刷新到磁盘时机

  1: 日志在每次事务提交时写入并刷新到磁盘。

  0: 每秒将日志写入并刷新到磁盘一次。

  2: 日志在每次事务提交后写入，并每秒刷新到磁盘一次。

磁盘结构

• system tablespace: 系统表空间是change buffer的存储区域。如果表是在系统表空间而不是在每个表文件或通用表空间中创建，它可能包含表和索引数据。

  change buffer的额外说明： 当InnoDB处于空闲或即将关闭时，才会将change buffer上的数据更改合并到buffer pool，为防止系统或change buffer崩溃，innodb引擎会定期将change buffer中的数据持久到系统表空间。当然如果系统崩溃时，change buffer中的数据还没有写入系统表空间，那么change buffer中数据就从redo log中恢复。

• file-per-table tablespace: 每个表的文件表空间包含单个InnoDB表的数据和索引，并储存在文件系统上的单个数据文件中。

  设置参数：innodb_file_per_table

• general tablespace: 通用表空间，需要通过create tablespace语法创建通用表空间。而后在创建表时，可以指定该表空间。

  #创建表空间
  create tablespace 表空间名 add datafile '表空间储存的文件(.ibd文件)' engine = innodb
  
  #使用表空间
  create table ... tablespace 表空间名;
  

• undo tablespace: 撤销表空间，MySQL实例在初始化时会自动创建这两个默认的undo表空间，用于储存undo log日志。

• temporary tablespaces: innndb使用会话临时表空间和全局临时表空间来储存用户创建的临时表等数据。

• double buffer files: 双写缓冲区，innodb引擎将数据页从buffer pool刷新到磁盘前，先将数据写入双写缓冲区文件中，使系统异常时便于恢复。(储存在.dblwr文件中)

• redo log: 重做日志, 是实现事务持久性的关键。该日志文件由两部分组成：重做日志缓冲(redo log buffer)以及重做日志文件(redo log)。当事务提交后会将缓存中的所有修改日志信息储存到该日志中去。用于在刷新脏页到磁盘时发生错误时，进行数据的恢复。

后台线程

• master thread: 核心后台线程，负责调度其他线程，还负责将缓冲区的数据同步到磁盘中，保持数据的一致性，还包括脏页的刷新，合并插入缓存，undo页的回收。

• IO thread: 在InnoDB储存引擎中大量使用了AIO(异步非阻塞IO)来处理IO请求，这样能极大得提高数据库的性能，而IO thread主要负责这些IO请求的回调。

  

• purge thread: 主要用于回收事务已经提交了的undo log。在事务提交后undo log便可能不在用了，故使用该线程进行回收。

• Page Cleaner Thread: 协助Master thread刷新脏页到磁盘的线程，它可以减轻Master Thread的工作压力，减少阻塞。

事务原理

事务有4个特性：原子性，一致性，持久性和隔离性。其中原子性、一致性和持久性依赖于redo.log和undo.log文件，而隔离性则依赖于锁和MVCC。

redo log

重做日志，记录事务提交时数据页的物理修改，主要用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲(redo log buffer)以及重做日志文件(redo log file),前者于内存中，后者于磁盘中。

每当事务提交后，缓冲中的redo log会刷新到磁盘中，当在脏页刷新到磁盘发生错误时，能够根据redo log进行数据恢复，当然我们也可以每次事务提交都刷新脏页，但效率较低。因为日志采用追加写入磁盘，顺序IO效率高，而脏页刷新到磁盘则采用随机IO,效率低。

undo log

回滚日志，用于记录修改前的日志，作用包括两个：提供回滚和MVCC(多版本并发控制)。

undo log 和redo log记录物理日志不一样，它是逻辑日志。即每当执行DML语句时，它会记录一条相反的语句用于数据的恢复。

Undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。

Undo log储存：undo log采用端的方式进行管理和记录，存放在回滚段中，内含1024个undo log segment。

MVCC多版本并发控制

MVCC全称Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使读写操作没有冲突，其中快照读操作为MySQL实现MVCC提供了一个非阻塞读功能。

快照读：简单的select（不加锁）即快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁的非阻塞读。

• Read Committed: 每次select，都生成一个快照读。
• Repeatable Read: 开启事务后第一个select语句才是快照读的地方。
• Serializable: 快照读会退化成当前读。

当前读：读取当前最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。加锁读，DML语句都是当前读。

MVCC的具体实现，还要依赖于数据库记录中的三个隐式字段，undo log日志和readView。

隐藏字段

即之前提到的row中的三个隐藏字段。
其中 DB_TRX_ID和DB_ROLL_PTR是实现MVCC的关键。

undo log版本链

当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可立即删除。

而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，因此不会立即删除。

不同事务或相同事务对同一条记录进行修改，会导致该记录的undo log生成一条记录版本链表，链表的头部是最新的旧记录，链表的尾部是最早的旧记录。

readview

ReadView(读视图)是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交的)id。
每个事务在执行第一条select语句时会生成对应的readview

ReadView中包含四个核心字段：

• m_ids：当前活跃的事务ID集合
• min_trx_id：最小活跃事务ID
• max_trx_id：预分配事务ID，当前最大事务ID+1（事务自增）
• creator_trx_id：ReadView创建者的事务ID

版本数据访问规则

我们通常会使用trx_id进行来判断读事务该读取哪条记录。
trx_id 即undo_log中最新记录的DB_trx_id（最后一次进行数据修改的事务id）

tip: 当没有显示声明事务时，DML语句会隐式的创建事务id

• trx_id == creator_trx_id 当前记录数据是本事务修改，可以读取该记录
• trx_id < min_trx_id 当前记录数据对应事务的已提交，可以读取该数据。
• trx_id > max_trx_id 当前记录数据是在readview生成之后记录到undo_log日志中的，不能访问该记录。
• trx_id 不在m_ids集合中，且以上条件都不满足，则说明该记录对应的事务已提交，可以读取该记录数据。

如果当前记录不能读，那么就会根据回滚指针查找上一个版本的记录进行以上判断。

不同的隔离级别，生成的ReadView的时机不同：

• READ COMMITTED：在事务中每执行一次快照读时都会生成ReadView。

• REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

MySQL管理

系统数据库

• mysql 储存MySQL服务器正常运行所需要的各种信息(时区，主从，用户，权限等)。
• information_schema 提供了访问数据库元数据的各种表和视图，包含数据库，表，字段类型及访问权限等。
• performance_schema 为mysql服务器提供一个底层监控功能，主要用于收集数据库服务器性能参数和mysql运行时的运行状态(比如锁，二进制日志等)。
• sys 包含了一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图。

MySQL客户端工具

命令行SQL执行参数

在命令终端上，在启动mysql时可以使用**-e**参数直接执行SQL语句，而不用进入mysql客户端执行。对于一些批处理脚本，这种方式尤其方便。

mysql [option] 数据库名 -e 待执行SQL语句

mysqladmin

mysqladmin是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等

mysqladmin –help 查看命令帮助文档

使用该工具时，需要连接mysql，因此要使用-u，-p参数
-u 指定连接mysql的用户

-p 指定连接mysql的密码

create 数据库名 创建数据库

drop 数据库名 删除指定数据库

mysqlbinlog

由于服务区生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式。就会使用到mysqlbinlog日志管理工具。

mysqlbinlog [options] log-file1 [log-file2...]
option:
-d 数据库名		#指定待操作的数据库名称
-o offset	   #忽略掉日志中的前n行
-f file_name   #将输出文本输出到指定文件
-s 			   #只显示简单格式信息，省略掉一些信息
--start-datetime=开始时间 --stop-datetime=结束时间  #指定日期间隔内的所有日志
--start-position=起始位置 --stop-position=解锁位置  #指定位置间隔内的所有日志

mysqlshow

mysqlshow客户端对象查找工具，用来很快地查找存在哪些数据库，表，字段或索引

终端使用该工具仍需要-u，-p参数

mysqlshow [options][db_name][table_name][col_name]
--count		#显示数据库及表的统计信息(数据库，表均可不指定)
-i			#显示指定数据库或指定表的状态信息

mysqldump

mysqldump客户端工具主要用来备份数据库或在不同数据库间进行数据迁移。
备份内容会包含创建表及插入表的SQL语句。

#语法
#备份数据库或里面的指定表
mysqldump [options] db_name [table...] [> 备份目标SQL文件]
#备份整个数据库
mysqldump [options] --database/-B db1[db2...] [> 备份目标SQL文件]
#备份当前连接里所有数据库
mysqldump [options] --all-databases/-A [> 备份目标SQL文件]

#连接选项：
-u usename		#指定用户名
-p password		#指定密码
-h host			#指定服务器id或域名
-p port			#指定连接端口
#输出选项：
--add-drop-database	#在创建数据库前删除旧数据库
--add-drop-table	#在创建表前删除旧表，默认开启，关闭加
--skip-add-drop-table
-n					#不包含数据库的创建语句
-t					#不包含表的创建语句
-d					#不包含数据，即有生成表结构的语句
-T 备份目标目录		#自动生成两个文件，一个建表的.sql文件，一个包含数据的.txt文件

mysqlimport/source

#通过txt文件导入数据(和mysqldump保存的txt文件格式一致),命令行终端执行
mysqlimport [option] 导入目标数据库 数据文本文件
#示例
mysqlimport -uroot -p2143 testdb /mysql信任目录/city.txt

#通过sql文件建表和导入数据, mysql终端执行
source /root/xxx.sql

两个文件，一个建表的.sql文件，一个包含数据的.txt文件

#### mysqlimport/source

```bash
#通过txt文件导入数据(和mysqldump保存的txt文件格式一致),命令行终端执行
mysqlimport [option] 导入目标数据库 数据文本文件
#示例
mysqlimport -uroot -p2143 testdb /mysql信任目录/city.txt

#通过sql文件建表和导入数据, mysql终端执行
source /root/xxx.sql