mysql ---- innodb-1- 体系结构、文件、表

mysql的体系结构

连接池组件
管理服务和工具组件
sql接口组件
查询分析器组件
优化器组件
缓冲组件
插件式的存储引擎
物理文件

Mysql 区别预其他数据库最重要的特点就是插件式的表存储引擎

1 存储引擎简易介绍：

1.1 innoDb存储引擎

支持事务, 主要面向在线事务处理(OLTP online transaction processing)方面的应用

特点: 行锁设计, 支持外键, 非锁定读

innoDb存储引擎将数据放在一个逻辑的表空间中, 将每个innodb存储引擎的表单独放到一个独立的ibd文件中.

功能:
- next-key locking策略避免幻读
- 插入缓存insert buffer
- 二次写double write
- 自适应哈希索引 adaptive hash index
- 预读 read ahead
- 通过多版本并发控制（MVCC）来获得高并发性
存储
- 采用聚集(clustered), 每张表都按照主键的顺序存储, 如果没有显示的指定主键, innodb引擎或为每一行数据生成一个6字节的rowid作为主键

1.1.2 MyISAM存储引擎

官方提供的引擎, 对于一些OLAP( online analytical processing在线分析处理)操作速度快.

特点: 不支持事务,支持表锁,支持全文索引
构成: MYD和MYI, MYD用来存放数据文件, MYI用来存放索引文件. (可以通过myisampack 来压缩数据文件, 压缩后只读)
数据量支持
- 5.0版本之前, 默认表大小 5G (如果要修改,需要调整: MAX_ROW和AVG_ROW_LENGTH 属性)
- 5.0后, 默认支持256T

1.3 NDB存储引擎

集群存储引擎

特点: 数据全部放在内存中(5.1版本后,可以将非索引数据放在磁盘上), 因此主键查找(primary key lookup)的速度快,并通过添加NDB数据存储节点(data node) 可以线性的提供数据库性能, 是高可用/高性能的集群系统
注意: NDB存储引擎的链接操作(join)是在mysql数据层完成的, 而不是在存储引擎层完成的, 这意味着链接操作需要巨大的网络开销, 因此查询慢

1.1.4 Memory存储引擎

特点: 数据在内存中, 非常适合用于存储临时数据的临时表,以及数据仓库的纬度表, 默认使用hash索引
只支持表锁,并发性差. 不支持text和blob类型
存储varchar时按照char定长方式进行

1.5 Archive存储引擎

非常适合存储归档数据，如：日志信息

特点: 只支持insert和select操作， 5.1版本开始支持索引
使用zlib算法将行数局（row）进行压缩存储
使用行锁来实现高并发插入操作（但本身并不是事务安全的存储引擎，其设计目标主要是提供高速的插入和压缩功能）

1.6 Federated存储引起

表并不存储数据，只是指向一台远程mysql数据库服务器上的表（不支持异构数据库表）

1.7 Maria存储引擎

新开发的引擎，目标时取代原有的MyISM存储引擎

2 InnoDb体系架构

体系架构：innodb有多个内存块，这些内存块组成了一个大的内存池
- 维护所有进程/线程需要访问的多个内部数据结构
- 缓存磁盘上的数据，方便快速的读，并且对磁盘文件进行修改之前在这里缓存
- 重做日志（redo log）缓冲
后台线程的主要作用是负责刷新内存池中的数据，保证缓存池中的内存缓存是最近的数据，此外，将已修改的数据文件刷新到磁盘文件，同时保证在数据库发生异常下InnoDb能够恢复到正常运行状态。

2.1 后台线程

组成共7个线程
- 4个IO thread (insert buffer thread、 log thread、 read thread、 write thread)
- 1个master thread
- 1个锁lock监控线程
- 1个错误监控线程
IO thread的数量由配置文件中的inoodb_file_io_thread参数控制，默认为4 （linux平台下不可用）

2.2 内存

InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理

组成
- 缓冲池（buffer pool） --- innodb_buffer_pool_size （从InnoDB1.0.x版本开始，允许设置多个缓冲池实例，即参数innodb_buffer_pool_instances）
  
  缓冲池是占最大块内存的部分，工作方式：将数据库文件按页（每页16k）读到缓冲池，然后按照最少使用（LRU）的算法来保留其中的数据
  
  如果要修改数据，总是先修改缓冲池中的页（修改后即为脏页），即缓冲池中的数据和磁盘上的数据不一致，这时数据库会通过checkpoint机制将脏页刷新回磁盘，而Flush列表中的数据即为脏页列表（脏页既存在与LRU列表，也存在与Flush列表，LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘，二者不影响）
  - 包含的数据页类型
    - 索引页
    - 数据页
    - undo页
    - 插入缓存（insert buffer）
    - 自适应hash索引（adaptive hash index）
    - Innodb存储的锁信息（lock info）
    - 数据字典信息（data dictionary）
    - 等
  - LRU List/ Free List / Flush List
    缓冲池占很大的一片内存区域，那么如何管理呢？通常来说，数据库中的缓冲池是通过LRU算法来管理的，即频繁使用的页在LRU列表的前端，最少使用的页在LRU列表的尾部。当缓冲池不能存放新读取到的页时，将首先释放LRU列表中尾端的页。
    在InnoDB引擎中，缓冲池中页的大小为16kb，同样使用LRU算法对缓冲池进行管理。稍有不同的是对LRU算法进行了优化。即在LRU列表中加入了midpoint位置，即新读取到的页并不是直接放入到LRU列表的首部，而是放入到midpoint位置（在默认配置下，midpoint在LRU列表的5/8处。该大小由参数innodb_old_blocks_pct控制，该值默认为37，即尾部的3/8处）。
    在InnoDB引擎中，把midpoint之后的列表称为old列表，之前的成new列表（可以简单的理解new列表中的数据为热点数据）
    InnoDB引擎从1.0.x版本开始支持压缩页功能，即将原本16kb的页压缩成1kb、2kb、4kb、8kb。由于页的大小发生了变化，LRU列表页发生了变化，对于非16kb的页，通过unzip_LRU列表来进行管理，通过show engine innodb status来观察（LRU的数量包含unzip_LRU的数量）。
    unzip_LRU是如何分配内存的呢？（录入申请4kb的内存）
    1. 检查4kb的unzip_LRU列表是否有空闲页，若有则直接使用
    2. 没有则检查8kb的unzip_LRU列表
    3. 若能得到空闲页，则将之分成两个4kb，并将其加入到4kb的unzip_LRU列表
    4. 若不能得到，则从LRU列表中申请一个16kb的页，将其分成1个8kb的页、2个4kb的页，分别存入对应的unzip_LRU列表
```
      -- 通过information_schema架构下的INNODB_BUFFER_LRU观察unzip_LRU的情况
      select * from information_schema.innodb_buffer_lru where compressed_size <> 0;
```
- 重做日志缓冲池（redo log buffer） --- innodb_log_buffer_size
  InnoDB引擎首先将重做日志信息放入这个缓冲区，然后以一定的频率将其刷新到重做日志文件。重做日志缓冲一般不需要多大，因为一般情况下每秒都会讲重做日志缓冲刷新到日志文件。（默认为8MB）
  以下三种情况会刷新到文件：
  1. Master Thread每一秒将重做日志缓冲刷新到文件
  2. 每个事务提交
  3. 当重做日志缓冲池剩余空间小于1/2时
- 额外的内存池（additional memory pool）--- innodb_additional_men_pool_size

checkpoint 技术
目的是为了解决以下几个问题：
1. 缩短数据库的回复时间
2. 缓冲池不够用时，刷新脏页
3. 重做日志不可用时，刷新脏页

2.3 master thread

innodb存储引擎的主要工作都是在一个单独的线程master thread 中完成的。

master thread 优先级最高

组成
- 主循环（loop）
  
  大多数的操作都是在loop中，其操作分为两大部分：每秒钟的操作和每十秒钟的操作
```
void master_thread() {
loop;
for(int i = 0; i < 10; i++){
	// do thing once per second
	// sleep 1 second if necessary
}
// do things once per ten seconds
goto loop;
}
```
  loop循环主要通过sleep来实现（每秒和每10秒并不是精确的）
  - 每秒操作：
    - 重做日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）
    - 合并插入缓冲（可能）
    - 至多刷新100个innodb的缓存池中的脏页到磁盘（可能）
    - 如果没有用户操作，切换到background loop（可能）
  - 每十秒操作：
    - 刷新100个脏页到磁盘（可能）
    - 合并至多5个插入缓冲（总是）
    - 将日志缓冲刷新到磁盘（总是）
    - 删除无用的undo页（总是）
    - 刷新100个或者10个脏页到磁盘（总是）
    - 产生一个检查点（总是）
- 后台循环（background loop）
- 刷新循环（flush loop）
- 暂停循环（suspend loop）

2.4 关键特性

包括：插入缓冲、两次写（double write）、自适应哈希索引（adaptive hash index）、异步io（Async IO）、刷新邻接页（Flush neighbor page）

2.4.1 插入缓冲

2.4.1.1 Insert Buffer

insert buffer和数据页一样，也是物理页的一个组成部分

我们知道， 主键是行唯一的标识符，在应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引一般是顺序的，不需要磁盘的随机读取。比如：

>mysql create table t (id int auto_increment, name varchar(20), primary key(id));

id是自增长的，这意味着当执行插入操作时，id列会自动增长，页中的行记录按id执行顺序存放。

一般情况下不需要随机读取另一页执行记录的存放。这种情况下插入操作一般很快能完成。

但是，如果表中存在一个非聚集的辅助索引（secondary index）。比如：按照name这个字段查找，并且name不是唯一的，建表语句如下：

>mysql create table t(id int auto_increment, name varchar(20), primary key(id), key(name))

这样就产生了一个非聚集的并且不是唯一的索引。在插入时，数据页的存放还是按主键id的执行顺序存放，但是对于非聚集索引，叶子节点的插入不是顺序的了。这时就需要离散的访问非聚集索引页，插入性能在这里就变低了。

插入缓存对于非聚集索引的插入Insert和更新update操作，不是一次直接插入到索引页种，而是先判断插入的非聚集索引页是否在缓冲池中，如果存在，则先入InesrtBuffer 对象中，然后再以一定的频率执行插入缓冲和非聚集索引页子节点的合并操作，这样就大大提高了性能。

插入缓冲的使用要满足两个条件：

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

存在的问题： 在写密集的情况下，会占用大量的缓冲池内存（innodb_buffer_poll），默认最大可以占用1/2的内存

2.4.1.2 Change Buffer

InnoDB从1.0.x 版本开始引入了Change Buffer，可将其视为Insert Buffer 的升级版，从这个版本开始，InnoDB引擎可以对DML操作--INSERT/DELETE/UPDATE都进行缓冲，即：Insert Buffer、Delete Buffer、Purge Buffer

和Insert Buffer 一样，ChangeBUffer 适用于非唯一的辅助索引。

对一条 UPDATE 操作可以分为两个过程：

将记录标记为删除（Delete Buffer 对应 UPDATE 操作的第一个过程）
真正将记录删除（Purge Buffer 对应 UPDATE 的第二个过程）

2.4.1.3 Insert Buffer 的内部实现

Insert Buffer 使用场景：非唯一索引的插入操作

Insert Buffer 的数据结构是一棵B+树（在 Mysql4.1版本之前，每个表都有一个对应的B+树，现在版本中，全局只有1个B+树，负责对所有的辅助索引的缓存，这个B+树存放在共享表空间中（ibdata1））

Insert Buffer 是一棵B+树，因此是由叶子节点和非叶子节点构成。非叶子节点存放的是查询的 search key（键值）：

search key 一共占用9个字节。

space 表示带插入记录所在表的表空间 id（在 Innodb 引擎中，每个表都有一个唯一的 spaceid），占4个字节

marker是用来兼容老版本的 Insert Buffer，占1个字节

offset表示页所在的偏移量，占4个字节

插入过程：

当一个辅助索引要插入到页（space，offset）时，如果该页不在缓冲池中，那么 InnoDB 引擎首先更具上述规则构造一个 search key，接下来将这条记录插入的 Insert Buffert 中（插入并不是直接插入的，需要根据以下规则进行构造）：

space、marker、page_no 字段和之前一样，占用9个字节。matadata 占用4个字节。因此同样的记录，InsertBuffer 中的记录比原记录多额外的13字节的开销。

启用 Insert Buffer 索引后，辅助索引页（space，page_no）中的记录可能被插入到 Insert Buffer 的B+树中，所以为了保证每次 Merge Insert Buffer成功，需要有一个特殊的页用来标记每个辅助索引页的可用空间（也就是 Insert Buffer Bitmap）。

每个 Insert Buffer Bitmap 页用来跟踪16384个辅助索引页，也就是256个区（extent），每个 Insert Buffer Bitmap 页都在16384个页的第二个页中。

2.4.1.4 Merge Insert Buffer

Merge Insert Buffer 的操作发生的几种情况：

辅助索引页被读取到缓冲池
Insert Buffer Bitmap 页追踪到该辅助索引页已无可用空间
Master Thread

2.4.2 两次写

插入缓存带来的是性能，两次写带来的是可靠性

写失效（partial page wirte）：数据库正在写页时，数据库宕机，此时页（16k）只写了一部分

double write：在执行重做日志之前，我们需要一个副本，当写失效发生时，先通过副本来还原页，然后再进行重做。

由两部分组成：一部分是内存中的doublewrite buffer，大小为2MB；另一部分时物理磁盘上共享表空间（ib_datafile）中连续的128个页（128*16k=2MB），即两个区（extent）。

当缓冲池中的脏页刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先拷贝到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次，每次写入1MB到共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。再完成doublewrite页的写入后，再将doublewrite buffer中的页写入各个表空间文件中，此时写入是离散的。

通过命令show global status like 'innodb_dblwr_%'可以看到 double write 的运行情况：

可以看到 double write 一共写了6325194个页，但实际写入次数为100399，基本符合64：1.如果系统高峰时 Innodb_dblwr_pages_writtens：innodb_dblwr_writes 远小于64：1时，说明系统写入压力不高。

2.4.3 自适应哈希索引（Adaptive Hash Index， AHI）

hash 是一种查询非常块的方法，时间复杂度 O(1)，B+树的查询复杂度基于树的高度，一般生产环境中，B+树的高度一般为3_{4层，顾需要3}4次查询

innodb存储引擎会监控对上索引的查找，如果观察到建立hash索引可以带来速度的提升，则建立哈希索引

自适应hash索引通过缓冲池的B+树构造而来的，因此建立的速度很块，而且不需要对整个表都建立，InnoDb会自动根据访问的频率和模式为某些页建立hash索引

2.4.4 异步 IO

为了提高磁盘操作性能，当前数据库系统都采用异步 IO 的方式来处理磁盘操作.

在 Innodb 存储引擎中，read ahead、脏页的刷新（即磁盘写入操作）都是有 AIO 完成。

-- 查看是否开启native io
show variables like 'innodb_use_native_io';

2.4.5 刷新邻接页（Flush Neighbor Page）

原理：当刷新一个页时，Innodb 引擎会检测该页所在的区（extent，64个页）的所有页，如果是脏页，那么一起刷新。

好处：通过 AIO 可以将多个 IO 写入操作合并为一个 IO 操作

参数：innodb_flush_neighbors

注意：对于传统机械硬盘建议开启该特性，对于固态硬盘，建议关闭（0）

2.5 启动、关闭和恢复

参数

innodb_fast_shutdown
- 0: 代表关闭时，innodb需要完成所有的full purge和merge insert buffer（耗时）
- 1:默认值，不需要完成上述的操作，但是在缓冲池的一些数据还是会刷新到磁盘
- 2:不需要完成full purge和merge insert buffer，也不刷缓冲池中的数据，而是将日志都写入日志文件，这样不会有任何事务丢失，但是mysql在下次启动时，回执行恢复操作（recovery）
Innodb_force_recovery，影响恢复行为
- 0：默认，默认会在需要恢复的时候执行恢复行为
- 其他1~6

2.6 InnoDB Plugin = 新版本的InnoDB存储引擎

3 文件

参数文件：mysql实例启动时在哪里找到数据库文件，并且指定某些初始化参数
日志文件：用来记录mysql实例对某些条件做出响应时写入的文件，如：错误日志文件、二进制文件、慢查询文件、查询日志文件
socket文件：使用Unix域套接字连接mysql时需要的文件
pid文件
表结构文件： ddl文件
存储引擎文件

3.1 参数文件

3.1.1 参数类型

动态参数

可以在mysql运行期间修改

修改使用set

set [global|session] system_var_name；
select @@session.read_buffer_size \G;

静态参数

在运行期间不可修改

3.2 日志文件

包括：
- 错误日志
- 慢查询日志
- 查询日志
- 二进制日志

3.2.1 错误日志

错误日志文件对Mysql的启动、运行、关闭过程进行了记录

show variables like 'log_error';  -- 查询错误日志文件的路径

3.2.2 慢查询日志

-- 慢查询时间阈值  大于时间阈值的会记录慢日志
show variables like 'long_query_time';

-- 启动慢查询日志（默认不启动）
show variables like 'slow_query_log';

-- 运行的sql没有使用索引，开启改参数后 也会记录慢日志
show variables like 'log_queries_not_using_indexes';

分析慢查询日志 mysqldumpslow命令

mysql5.1 开始可以将慢查询日志记录到一张表中 mysql.slow_log(该表默认使用的引擎时CSV，可以修改为MyISM)

-- 参数log_output指定了慢查询的输出格式， 默认为FILE, 可以将它设置为TABLE,然后慢查询日志进入slow_log表
show variables like 'log_output';
set global log_output = 'TABLE';

-- 修改引擎
alter table slow_log engine=myisam;

3.2.3 查询日志

记录了所有对Mysql数据库的请求信息，不论这些请求是否正确执行

默认文件名：主机名.log

日志也可以进表：general_log

3.2.4 二进制文件

记录了对数据库执行更改的所有操作（不包括select和show这类操作）

默认不启动

作用
- 恢复（recovery），某些数据恢复需要二进制文件，如：当一个数据库全备文件恢复后，可以通过二进制文件日志进行point-in-time的恢复
- 复制（replication），和远程数据库（slave或者standby）进行实时同步
- 审计（audit）
设置
通过配置参数log-bin[=name]可以启动二进制，如不指定name，则默认二进制文件名为主机名，后缀名为二进制日志的序列号，所在路径为数据库所在目录
```
-- 数据库所在目录
show variables like 'datadir';		
```
相关参数
- max_binlog_size
  
  单个二进制文件的最大值，超过该值，则产生新的二进制日志文件，后缀名+1 默认大小为1 073 741 824（1GB）
- binlog_cache_size
  
  当使用事务存储引擎（如innoDB）时，所有未提交（uncommitted）的二进制日志会被记录到一个缓存中，等该事务提交时直接将缓冲中的二进制日志写入到二进制日志文件，该缓冲的大小有binlog_cache_size控制（基于session，每个线程都会分配一个二进制日志缓冲），默认为32kb
  
  可以通过show global status like 'binlog_cache%';查看使用临时文件写二进制的次数
- sync_binlog 等于1时，控制同步写入二进制日志
- binglog_do_db 哪些表写二进制日志文件
- binglog_ignore_db 哪些表不写二进制日志文件
- log_slave_update 从库是否写二进制文件（主-> 从 -> 从第一个从需要开启log_slave_update）
- binlog_format
  - STATEMENT 语句
  - ROW, 同常情况下设置为row，可以为数据库的恢复和复制带来更好的可靠性
  - MIXED
查看

binlog_format为statement时：直接使用 mysqlbinlog --start-position=*** test.000004

如果为row时，需要加上参数-vv

3.3 套接字文件

Unix系统下连接mysql可以使用Unix域套接字方式，这种方式需要套接字文件（socket）

show variables like 'socket'\G;

3.4 pid文件

mysql实例启动时，会将自己的进程ID写入一个文件中---即pid文件，该文件可有参数pid_file控制

默认路径位于数据库目录下，文件名为主机名.pid

show variables like 'pid_file';

3.5 表结构定义文件

因为mysql插件式存储引擎的体系， mysql对于数据的存储是按照表的，所以每个表都有有与之对应的文件。

无论采用何种引擎，都会有一个frm为后缀的文件，该文件记录了表结构定义（如果有视图，也会有对应的frm文件）

frm文件可以直接cat查看

3.6 Innodb存储引擎文件

每个存储引擎都有自己独有的文件

表空间文件

默认配置下，会有一个初始大小为10M，名为ibdata1的文件。该文件就是默认的表空间文件（tablespace file），可以通过参数innodb_data_file_path进行设置 innodb_data_file_path=datafile_spec1[;datafile_spec2]

也可以使用多个文件组成一个表空间，同时指定文件的属性
```
-- 以下有两个表空间文件，如果两个文件位于不同的磁盘上，则可以提升一定的性能   autoextend(自动增长)
innode_data_file_path=ibdata1:20M;ibdata2:20M:autoextend
```
设置innodb_data_file_path后，对于所有基于Innodb存储引擎的表数据都会记录到改文件内。

如果设置innodb_file_per_table，可以将每个基于Innodb引擎的表单独产生一个表空间，文件名为表名.ibd （这些单独的表空间仅存储了该表的数据、索引、插入缓冲等信息，其余信息还放在默认表空间中）
重做日志文件

默认情况下会有两个文件，ib_logfile0和ib_logfile1。称为Innodb存储引擎的日志文件（更准确应该校重做日志文件）。记录了对于innodb存储引擎的事务日志。

每个innodb存储引擎至少有一个重做日志文件组（group），每个文件组下至少有两个重做日志文件，如默认的ib_logfile0和ib_logfile1.

为了提高可靠性可以设置多个镜像日志组（mirrored log groups），将不同的文件组放在不同的磁盘上
- 影响参数
  - Innodb_log_file_size 指定重做日志文件的大小
  - Innodb_log_files_in_group 日志文件组中重做日志文件的个数，默认为2
  - Innodb_mirrored_log_groups 日志镜像文件组的数量默认为1，表示没有镜像组
  - innodb_log_group_home_dir 日志文件组路径

4 表

分析Innodb存储引擎的物理存储特征---数据是如何组织和存放的。（简单来说：表就是关于特定实体的数据集合，这也是关系型数据库的核心）

4.1 InnoDB存储引擎表类型

在InnoDB存储引擎表中，如果在创建表时没有显式的定义主键（primary key）， 则InnoDB存储引擎会按照以下方式选择或创建主键：

1. 首先表中是否有非空的唯一索引（Unique not null）， 如果有，则该列为主键
2. 不符合时，InnoDB存储引擎会自动创建一个大小为6字节的指针

4.2 InnoDB逻辑存储结构

InnoDB存储引擎的表，所有数据都被逻辑的存放在一个空间中，也就是表空间（tablespace），表空间又由段（segment）、区（extend）、页（page）组成。页在有的文档里边也称块（block）。

4.2.1 表空间

表空间可以看做时InnoDB存储引擎逻辑结构的最高层，所有数据都存储在表空间中。默认情况下InnoDB由一个共享表空间ibdata1，即所有数据都放在这个表空间内。如果启用了参数innodb_file_per_table，则每张表内的数据可以单独放在一个表空间内（ibd文件， 该文件内只是存放数据、索引和插入缓冲）

4.2.2 段

创建的段：数据段、索引段、回滚段等（前边说过InnoDB存储引擎时索引组织的，因此数据即索引，索引即数据， 那么数据段即为B+树页节点， 索引段即为B+树的非索引节点）

不是每个对象都有段，表空间是由分散的页和段组成。

4.2.3 区

区是由64个连续的页组成，每个页大小为16kb，即每个区大小为1MB。对于大的数据段，InnoDB存储引擎最多每次可以申请4个区，以此来保证顺序性能。

问题：在启用参数innodb_file_per_table后，创建的表默认大小为96kb。区是64个连续的页，那创建的表大小至少应该时1MB才对？

	是因为在每个段开始时，现有32个页大小的碎片页来存放数据，当这些页使用完之后，才是64个连续页的申请。

4.2.4 页

页是InnoDB磁盘管理的最小单位。默认每个页大小为16kb。从 Innodb1.2.x 版本开始，可以通过参数 innodb_page_size 设置页的大小

常见的页：
- 数据页（B-tree node）
- Undo页（Undo log page）
- 系统页（system page）
- 事务数据页（transaction system page）
- 插入缓冲位图页（insert buffer bitMap）
- 插入缓冲空闲列表页（insert buffer free list）
- 未压缩的二进制大对象页（uncompressed blob page）
- 压缩的二进制大对象页（compress blob page）

4.2.5 行

InnoDB存储引擎是面向行的，也就是说数据的存放按行进行存放。每个页存放的行记录也是由硬性规定的，最多允许存放16kb / 2 - 200行记录，即7992行。（16*1024/2 - 200）

-- 7992怎么算的
每个记录最少2个字节，每个页预留200字节（预留为规定）  因此：16*1024/2 - 200
-- 每个页至少两行记录  最小记录和最大记录，用来区分边界

Innodb 引擎提供两种行记录格式：compact（默认）、redundant

可以通过show table status like 'mytest%'查看和记录格式

4.3 InnoDB 物理存储结构

从物理意义上来看，InnoDB表是共享表空间、日志文件组（redo文件组）、表结构定义文件组成。

若将innodb_file_per_table设置为on，则每个独立的产生一个表空间文件，以ibd结尾，数据、索引、表的内部数据字典信息保存在这个独立的表空间文件中。

表结构定义文件以frm结尾，这个是和存储引擎无关的，任何存储引擎的表结构定义文件都一样。

4.4 InnoDB行记录格式

4.3 InnoDB 行记录格式

InnoDB存储引擎数据记录是以行的形式存储的（意味着页中保存着表中一行行的数据）

show table status like 'table_name' -- 查看表记录的存储格式

页的存储格式
- Compact（目前使用最多的）
- Redundant

4.3.1 Compact行记录格式

Compact行记录是在mysql5.0版本之后引入的，目标是高效的存储数据（简单来说一个页中存放的行数据越多，性能也就越高）

从上图可以看出compact行记录方式：

变长字段长度列表

Compact行记录格式的首部是一个非NULL变长字段长度列表，并且是按照列的顺序逆序放置的，长度为：
- 若列长度小于255字节（byte），用1字节表示
- 若大于255字节，用2字节表示
变长字段的长度不能超过2字节，这是因为mysql数据库中varchar类型的最大长度限制为65535.
NULL标志位

指示了该行数据中是否有NULL值，有则用1表示

该部分所占字节为1字节
记录头信息（record header）

固定占用5字节（40位（bit））

列表*数据

NULL列数据不占该部分任何空间。

除了用户定义的数据列之外还有两个隐藏列，事务id列和回滚指针列，分别占6字节和7字节大小。若InnoDB没有定义主键，还会有一个6字节的rowid列

4.3.2 Redundant行记录格式

4.3.3 行溢出数据

InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。一般认为BLOB、LOB这类的大对象类型的存储会把数据存放在数据页面之外。但是BLOB可以不将数据放在溢出页面，而且即便是VARCHAR列数据类型，依然有可能被存放为行溢出数据。

Mysql数据库的VARCHAR类型可以存放65535字节，但是实际上只能存放65532字节（还有别的开销）。（如果在建表时没有将SQL_MODE设置为严格模式的话，当创建65535字节的列时，仍然是可以创建成功的， 因为mysql会将varchar(65535)转变为text类型）

需要注意的是：varchar(65532) 默认是IatinL是可以的，但是当字符类型是GBK或UTF-8时，是不能创建的

上边这个是因为，VARCHAR(N) 中的N指的是字符的长度，而文档中说VARCHAR类型最大支持的65535，单位是字节。-
注意mysql手册中定义的65535字节长度，说的是所有varchar列的长度总和，如果列的总和超过，也不能创建
即便能够存储65532字节， InnoDB引擎的页为16kb（也就是16384字节），怎样存放65535字节呢?

一般情况下，InnoDB引擎的数据都是放在页类型为B-tree node中，但是，当发生行溢出时，数据存放在页类型为Uncompress BLOB页中。（数据页中只存放了前缀（768字节的前缀），之后就是偏移量，指向行溢出页，也就是Uncompress BLOB page）

4.3.4 Compressed 和 Dynamic 行记录格式

InnoDB 1.0.x版本开始引入了新的文件格式（file format， 可以理解为新的页格式）。 以前支持的Compact和Redundant格式称为Antelope格式， 新的文件格式称为Barracuda文件格式。

新的文件格式对于存放在BLOB中的数据都采用了完全的行溢出的方式，即在数据页中值存放20字节的指针，实际的数据都存放在Off page中，而旧版本会在数据页中存放768个前缀字节

Antelope
- Compact
- Redundant
Barracuda
- Compressed
- Dynamic

Compressed行记录格式的另一个功能：存放在其中的行数据会以zlib的算法进行压缩

4.3.5 CHAR的行记录结构

通常理解VARCHAR存储变长长度的字符类型， CHAR存储固定长度的字符类型。

Mysql4.1 版本之后CHAR(N)中的N指定的是字符长度，而不是之前版本的字节长度，也就是说，在不同的字符集下，CHAR类型列内部存储的可能不是定长的数据

例如：

create table a (
    c CHAR(2)
) engine=innodb charset=gbk;
insert into a select '我们';
insert into a select 'ab';

-- 字符集是GBK，分别插入了‘我们’和‘ab’， 实际存储的‘我们’占4个字节， ‘ab'占2个字节

对于多字节编码的CHAR数据类型的存储，InnoDB引擎在内部会视为变长字符类型，也就是说在变长长度列表中会记录CHAR数据类型的长度。

4.4 InnoDB数据页结构

页是InnoDB存储引擎管理数据库的最小磁盘单位。 页类型为B-tree Node的页存放的即是表中行的实际数据。

InnoDB数据页由7部分组成：

File Header （文件头）
Page Header（页头）
Infimun和Supermum Records
User Records（用户记录，即行记录）
Free Space（空闲空间）
Page Directory（页目录）
File Trailer（文件结尾信息）

其中Filer Header、Page Header、File Trailer大小是固定的，分别为38/56/8字节，这些用来标记该页的一些信息，如Checksum，数据页所在的B+数索引的层数。

User Records、Free Space、PageDIrectory这些部分为实际的行记录粗出空间，大小是动态的

4.4.1 File Header

用来记录一些数据页的一些头信息, 38字节（38B）

记录数据页的状态信息，14部分组成，占56字节

4.4.3 Infimum 和 Supremum Record

在InnoDB引擎中，每个数据页中有两个虚拟的行记录，用来限定记录的边界。

Infimum记录是比该页中任何记录都小的值

Supremum指比任何可能大的值还要大的值

这两个值在页创建时被建立，并且任何情况下不会被删除。

4.4.4 User Record 和 Free Space

User Record：实际存储记录的内容

Free Space：空闲空间，同样也是链表数据结构（在一条数据被删除后，该空间会被加入到空闲空间中）

4.4.5 Page Directory

页目录中存放了记录的相对位置，有时候这些记录指针称为Slots（槽）或者目录槽（Directory Slots）。

与其他数据库系统不同，在InnoDB中并不是每个记录都拥有一个槽，InnoDB存储引擎的槽是一个稀疏目录，即一个槽中可能包含多个记录。当记录被插入或删除时需要对槽进行分裂或平衡的维护操作

B+数索引本省并不能找到具体的一条记录，能找到的只是该记录所在的数据页。数据库把页加载到内存，然后通过Page Directory再进行二叉查找

4.4.6 File Trailer

作用：检验页是否已经完整的写入磁盘

4.6 约束

4.6.1 数据完整性

关系型数据库系统和文件系统的一个不同点就是，关系型数据库本身能保证存储数据的完整性。

数据完整性有以下三证形式：

实体完整性

保证表中有一个主键
域完整性

保证每列的值满足特定的条件，域完整性可以通过以下几种途径类保证：
```
- 选择合适的数据类型
- 外键约束
- 编写触发器
- DEFAULT约束
```
参照完整性

保证两个表之间的关系

对于InnoDB本身来说，提供了以下几种约束：

primary key
unique key
foreign key
default
not null

4.6.2 约束的创建和查找

约束的创建：

	1. 表建立时进行定义
	2. alter table进行创建

对于Unique key（唯一约束），可以通过命令CREATE UNIQUE INDEX来建立。

4.6.3 约束和索引的区别

当用户创建了一个唯一索引就是创建了一个唯一约束。但是约束和索引概念是有所不同的，约束更是一个逻辑概念，用来保证数据的完整性，而索引是一个数据结构，既有逻辑概念，在数据库中还代表着物理存储的方式。

4.6.4 对错误信息的约束

默认情况下，mysql数据库允许非法的或不正确的数据插入或更新，又或者可以在数据库内部将其转换为一个合法的值， 如：向NOT NULL的字段插入一个NULL，mysql数据库会将其转换成0再进行插入。

如果想要报错，而不是警告的话，需要设置sql_mode，用来严格审核参数

通过设置sql_mode为STRICT_TRANS_TABLES

4.6.5 ENUM和SET约束

4.6.6 触发器约束

触发器作用：在执行INSERT/DELETE/UPDATE命令之前或之后自动调用sql命令或存储过程。

触发器的创建命令是：CREATE TRIGGER，只有具备Super权限的Mysql数据库用户才可以执行：

CREATE 
[DEFINER = [user | CURRENT_USER]]
TRIGGER trigger_name BEFORE|ALTER INSERT|UPDATE|DELETE
ON tbl_name FOR EACH ROW trigger_stmt

最多可以为一个表创建6个触发器，即分别是insert、update、delete的before和after各定义一个。

4.6.7 外键约束

外键用来保证参照完整性。mysql的myisam本身不支持外键，对于外键的定义只是起到了一个注释的作用。

4.7 视图

视图（VIew）是一个命名的虚表，它是由一个sql查询来定义，可以当做表使用。

4.8 分区表

mysql支持的几种分区：

RANGE分区，行记录基于属于一个给定连续区间的列值被放入分区
LIST分区，和RANGE分区类似，只是LIST分区面向的是离散的值
HASH分区，根据用户自定义的表达式的返回值来进行分区
KEY分区，根据mysql数据库提供的hash函数进行分区

注意：如果表中存在主键或唯一索引时，分区列必须是主键或唯一索引的一部分。（否则不能创建分区）

分区函数：year() / to_days()/ to_seconds() / unix_timestamp()

4.8.1 RANGE 分区

create table t(
	id int primary key
) engine=innodb
partition by range(id)(
	partition p0 values less than(10),
  partition p1 values less than(20),
  partition p2 values less maxvalue
);
-- maxvalue 理解为正无穷
-- 删除分区
alter table t drop partition p0; -- 会将所有小于10的数据全部删除

4.8.2 LIST 分区

create table t(
	id int primary key,
  b int
) engine=innodb
partition by list(id) (
	partition p0 values in (1,3,5,7,8),
  partition p1 values in (2,4,6,8,10)
);

4.8.3 HASH 分区

create table t (
	id int,
  b datetime

) engine=innodb
partition by hash(year(b))
partitions 4;

4.8.4 KEY 分区

create table t(
	a int,
  b datetime
) engine=innodb
partition by key(b)
partitions 4;

4.8.5 COLUMNS 分区

range、list、hash、key 分区条件：数据必须是整型，如果不是则需要转化为整型。

mysql5.5版本开始支持 COLUMNS 分区，可以直接使用非整型的数据进行分区

支持的数据类型：

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

-- 对于日期类型 不需要使用 year()或者 to_days()函数了
create table t(
	a int,
  b datetime
) engine=innodb
partition by range columns(b) (
	partition p0 values less than ('2020-01-01'),
  partition p1 values less than ('2021-01-01')
)

posted @ 2021-01-17 13:09 xj-record 阅读(312) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

xj-record

日常记录