MySQL技术内幕InnoDB存储引擎 电子版 mysql技术innodb存储引擎 第4版 pdf

文章目录

• 表

• 一、索引组织表(当表中有多个非空唯一索引时，InnoDB存储引擎将选择建表时第一个定义的非空唯一索引为主键。如果还没有，使用一个6字节的rowId作为主键)
• 二、InnoDB逻辑存储结构

• 1、表空间(共享表空间ibdata1，如果一张表放在一个单独表空间，则放数据、索引和插入缓冲页)

• 例子

• 2、段(数据段为叶子节点，索引段为非叶子节点)
• 3、区(由连续页组成，1个区1MB)
• 4、页(InnoDB磁盘管理的最小单位)

• 三、InnoDB行记录格式

• 1、Compact行记录格式

• 注意(NULL除了有NULL标识位，实际不存储任何空间，每行有两个隐藏列，存事务ID和回滚指针列)
• 例子

• 2、Redundant行记录格式
• 3、行溢出数据
• 4、Compressed和Dynamic行记录格式
• 5、CHAR的行结构存储(char定义时候的长度是字符长度，不是字节长度，不同字符所占字节可能会不同)

• 四、InnoDB数据页结构

• 1、File Header
• 2、Page Header
• 3、Infimum和Supremum Record
• 4、User Record和Free Space
• 5、Page Directory(存放记录的相对位置)

• 注意(索引找到的是页，在通过页目录进行二叉查找)

• 6、File Trailer

• 五、Named File Formats机制
• 六、约束

• 1、数据完整性(通过约束机制保证表有一个主键、满足域类型、保证表之间关系)

• InnoDB中的几种约束

• 2、约束的创建和查找(主键名默认PRIMARY，Unique约束默认名为列名)

• 外键约束

• 3、约束和索引的区别(创建主键索引和创建唯一索引和创建约束是一样的)
• 4、对错误数据的约束(默认会waring，不会报错)
• 5、ENUM和SET约束
• 6、触发器(TRIGGER，insert等命令之前或之后自动调用)和约束
• 7、外键约束

• 七、视图

• 1、视图的作用
• 2、物化视图

• 八、分区表

• 1、分区概述
• 2、分区类型

• 1、RANGE分区(最常用)
• 2、LIST分区
• 3、HASH分区
• 4、KEY分区(使用MySQL提供的函数分区)
• 5、COLUMNS分区

• 3、子分区(在原本分区基础上再分区)
• 4、分区中的NULL值
• 5、分区和性能

表

一、索引组织表(当表中有多个非空唯一索引时，InnoDB存储引擎将选择建表时第一个定义的非空唯一索引为主键。如果还没有，使用一个6字节的rowId作为主键)

在InnoDB存储引擎中，表都是根据主键组织存放的，这种存储方式的表称为索引组织表。

在InnoDB存储引擎中，每个表都有主键，如果在创建表的时候没有显示创建主键，则InnoDB存储引擎会按如下方式选择或创建主键：

• 首先判断表中是否有非空的唯一索引，如果有，则该列即为主键。
• 如果不符合上述条件，InnoDB存储引擎自动创建一个6字节大小的指针。

当表中有多个非空唯一索引时，InnoDB存储引擎将选择建表时第一个定义的非空唯一索引为主键。

下面创建了一个表，这个表有4列，其中b,c,d列建立了唯一索引，但是b列允许为NULL。

下面通过SQL判断哪个是主键，_rowid可以显示表的主键，所以d是主键，因为d是第一个创建的：

_rowid只能查看单个列为主键的情况，对于多个列为主键就不行了。

二、InnoDB逻辑存储结构

从InnoDB存储引擎的逻辑存储结构看，所有数据被逻辑放在一个空间，成为表空间。
表空间又由段(Segment)、区(extent)、页(page)组成。页在一些文档中有时称为块。

1、表空间(共享表空间ibdata1，如果一张表放在一个单独表空间，则放数据、索引和插入缓冲页)

表空间是InnoDB存储引擎结构的最高层，所有的数据都存在表空间。默认情况下，InnoDB有一个共享表空间ibdata1，所有的数据都存在这个表空间中，如果启用了参数innodb_file_per_table，则每张表的数据会单独放在一个表空间中。

如果启用了innodb_file_per_table的参数，需要注意的是每张表的表空间存放的只是数据、索引和插入缓冲Bitmap页，其他类数据，如回滚信息、插入缓冲索引页、系统事务信息，两次血缓冲等还是放在原来的共享表空间内。所以即使一张表存放在一个表空间，但是共享表空间的大小还是不断增大的。

例子

对比共享表空间大小

共享表空间ibdata1的大小为58M，接着模拟undo的操作，并把存储引擎改成InnoDB。

set autocommit=0表示用户需要显式提交事务，说明产生了大量的undo操作的语句。

如果rollback，共享表空间的大小不会缩小，但是会自动判断这些undo信息是否还需要，如果不需要，会将这些空间标记为可用空间，供下次undo使用。

2、段(数据段为叶子节点，索引段为非叶子节点)

表是由段组成的，常见的段有数据段、索引段、回滚段等，InnoDB存储引擎表是索引组织的。那么数据段是B+树的叶子节点，索引段为B+树的非叶子节点。回滚段在后面讲解。

3、区(由连续页组成，1个区1MB)

区由页组成，每个区大小为1MB。区中也是连续的，InnoDB一次从磁盘申请4-5个区，在默认情况下，InnoDB存储引擎页的大小为16kb，即64个连续页。
InnoDB1.2.x版本新加了参数innodb_page_size，通过该参数可以将默认的页的大小设置为2k、4k、8k，因此每个区对应页的数量应该为512，256、128.

虽然区的大小为1MB，但是不能说明表的大小最小为1MB，因为可以使用碎片页来存放数据。

4、页(InnoDB磁盘管理的最小单位)

InnoDB存储引擎中，页的大小默认为16kb，从InnoDB1.2.x版本开始，可以通过参数innodb_page_size进行设置。

三、InnoDB行记录格式

InnoDB提供了Compact和Redundant两种格式来存在行记录数据。
Redundant格式为兼容之前的版本而保留的，在MySQL5.1版本中，默认为Compact行格式，用户可以通过show table status like 'table_name’来查看当前表使用的行格式，row_format属性表示当前使用的行记录结构类型。

1、Compact行记录格式

英 [kəmˈpækt , ˈkɒmpækt]adj. 小型的; 袖珍的; 紧凑的; 体积小的;

一个页中存放的行数据越多，性能越高。

Compact行记录是在MySQL5.0中引入的，其设计目标是高效存储数据。

Compact行记录格式的首部是一个非null变长字段长度列表，并且其是按照列的顺序存放的。

• 如果列的长度小于255字节，用一个字节表示
• 如果大于255字节，用2字节表示

这变长字段的长度不超过2字节，这就是为什么varchar类型的最大长度限制为65535。个空间可能有多个，可能是根据有多少个varchar字段来定的

变长字段后面的第二个部分是NULL标识位，表示了该行数据中是否有NULL值，有则用1表示。

再下面是头部信息，固定是5字节(40位)，每位的含义如下：

注意(NULL除了有NULL标识位，实际不存储任何空间，每行有两个隐藏列，存事务ID和回滚指针列)

• NULL除了有NULL标识位，实际不存储任何空间
• 每行数据有两个隐藏列，存放事务ID和回滚指针列，分别占用6字节和7字节的大小
• 如果InnoDB没有定义主键，每行还会增加一个6字节的rowid列。

例子

t1、t2、t4是varchar类型的即变长的，t3为固定长度类型char

插入一个数据，打开空间文件mytest.ibd。

2、Redundant行记录格式

略

3、行溢出数据

略

4、Compressed和Dynamic行记录格式

略

5、CHAR的行结构存储(char定义时候的长度是字符长度，不是字节长度，不同字符所占字节可能会不同)

通常理解varchar是变长长度的字符类型，char是固定长度的字符类型。

从MySQL4.1版本开始，CHAR(N)中的N指的是字符的长度，而不是之前版本的字节长度。
也就是说在不同的字符集下，char类型列内部存储的可能不是定长的数据，要看字符怎么编码，每个字符的编码长度可能是不同的。

在上面的例子中，表j的字符集是GBK，插入了‘ab’和‘我们’，查看所占字节：

‘ab’占2字节，‘我们’占4字节。

对于UTF-8下char(10)类型的列，其最小可以存储10字节的字符，最大可以存储30字节的字符。

对于多字节字符编码的char数据类型的存储，InnoDB存储引擎在内部将其视为变长字符类型。

四、InnoDB数据页结构

FIle Header、Page Header、File Trailer大小是固定的，分别为38、56、8字节，用来标记该页的一些信息，如CheckSum，谁也所在B+树层数等。
User Records、Free Space、Page Directory这些部分为实际的行记录存储空间，因此大小是动态的。

1、File Header

2、Page Header

3、Infimum和Supremum Record

在InnoDB中，数据页中有两个虚拟的行记录，用来限定记录的边界，Infimum记录是比该页中任何主键值都要小的值，Supremum指比任何可能打的值还要大的值。

4、User Record和Free Space

实际存储的内容，Free Space指的是空闲空间，同样是链表数据结果，在一条记录被删除之后，该空间被加入到空闲链表中。

5、Page Directory(存放记录的相对位置)

页目录中存放了记录的相对位置(注意存放的是页相对位置，而不是偏移量)，有些时候这些记录指针被称为Slots(槽)或目录槽，与其他数据库系统不同的是，在InnoDB中并不是每个记录拥有一个槽，InnoDB的槽是一个稀疏目录，即一个槽中有多个记录。伪记录Infumum的n_owned的值总是1，记录Supremum的n_owned的取值范围为[1,8].其他记录的o_owned的取值范围是[4,8]。
o_owned是槽中记录的数量?

注意(索引找到的是页，在通过页目录进行二叉查找)

B+树索引本身不能找到具体的一条记录，能找到的只是记录所在的页，数据库把页加载到内存中，然后通过Page Directory进行二叉查找，只不过二叉查找的时间复杂度很低，同时在内存中很快，通常会忽略这部分查找所用的时间。

6、File Trailer

略

五、Named File Formats机制

略

六、约束

1、数据完整性(通过约束机制保证表有一个主键、满足域类型、保证表之间关系)

关系型数据库本身能保证存储数据的完整性，关系型数据库提供了约束机制来保证数据的完整性：
一般来说，数据完整性有以下三种形式：

• 实体完整性保证表中有一个主键。(可以使用Primary Key或Unique Key或编写触发器)
• 域完整性保证数据每列的值满足特定的条件
  1、选择合适的数据类型保证一个数据值满足特定类型
  2、外键约束
  3、编写触发器
  4、还可以使用DEFAULT约束作为强制域完整性的一个方面。
• 参照完整性保证两张表之间的关系

InnoDB中的几种约束

• Primary Key
• Unique Key
• Foreign Key
• Default
• NOT NULL

2、约束的创建和查找(主键名默认PRIMARY，Unique约束默认名为列名)

约束的创建方式：

• 表建立的时候创建
• 利用alter table命令来进行创建约束

对于主键而言，其约束名为PRIMARY，对于Unique而言，默认约束名称和列名一样，当然也可以人为指定Unique Key约束的名字。

下面是简单的创建表的语句，表上有一个主键和一个唯一键：

通过information_schema架构下的表TABLE_CONSTRAINTS来查看当前数据库的约束信息

从上图可以看出，主键约束名为PRIMARY，唯一索引默认约束名和列名相同。

下面是创建唯一索引：

外键约束

为了创建Foreign Key，必须创建另外一个表。

如下：

3、约束和索引的区别(创建主键索引和创建唯一索引和创建约束是一样的)

创建Primary Key和Unique Key索引和创建约束的方法一样。
那么约束和索引有什么区别呢？

• 用户创建了唯一索引就创建了唯一的约束
• 约束用来保证数据完整性，索引是数据结构。既有逻辑上的概念，还代表着物理存储的方式。

4、对错误数据的约束(默认会waring，不会报错)

在某些默认设置下，MySQL数据库允许非法的或不正确的数据的插入或者更新，或者在内部将其转化为一个合法值。

如果给a表加入一行，id为null，date为2019-02-30，数据库不会报错。而是显示waring，需要设置参数sql_mode，设置为STRICT_TRANS_TABLES。

5、ENUM和SET约束

如果表有规定域可以使用ENUM进行约束。

虽然上面sex插入不是ENUM里面的数据之一，但是只是报了警告，如果要报错，还是要设置sql_mode参数。

6、触发器(TRIGGER，insert等命令之前或之后自动调用)和约束

触发器的作用是在执行INSETRT、DELETE和UPDATE命令之前或者之后自动调用SQL命令或存储过程。

MySQL5.0对触发器的实现还不是非常完善，但是从5.1开始，触发器已经相对稳定。

创建触发器的命令是CREATE TRIGGER。只有具备super权限的才能执行这个命令。

一个表最多创建6个触发器，insert、update、delete的before和after各定义一个。当前MySQL只支持for each row的触发方式，即按每行记录进行触发。通过触发器可以实现一些数据库本身不支持的一些特性，如对于传统CHECK约束的支持，物化视图，高级复制、审计等特性；

7、外键约束

MyISAM不支持外键，InnoDB支持，外键的定义如下：

用户可以在创建表的时候添加外键，也可以在表创建后通过alter table来添加。

一般来说，称被引用的表为父表，引用的表是子表。

外键定义的时候的ON DELETE和ON UPDATE表示对父表进行DELETE和UPDATE操作的时候，对子表所做的操作，可定义的字表的操作有：

• CASCADE
  父表进行插入或删除的时候，子表数据进行相应操作。
• SET NULL
  父表进行插入或删除的时候，子表数据被更新为NULL。
• NO ACTION
  父表进行插入或删除的时候，抛出错误。
• RESTRICT(默认设置)
  父表进行插入或删除的时候，抛出错误。

在MySQL中NO ACTION和RESTRICT功能是相同的。
对于参照完整性约束，外键能起到非常好的作用，但是对于数据的导入操作，外键往往导致在外键约束的检查上花费大量时间。

七、视图

视图是命名的虚表，由一个SQL查询来定义，可当作表使用，没有实际物理存储。

1、视图的作用

视图主要作用之一是用作一个抽象装置，对于一些程序，使用视图可以使得不关心基表结构。

虽然视图是基表的一个虚拟表，但是用户可以对某些视图进行更新操作，本质就是通过视图更新基本表。

修改视图的时候，需要满足创建视图时候的条件。

比如

如果插入id为20的值。

可以看到上面并没有报错，但是查视图中数据的时候并没有数据，也就是实际没插入也没报错。可以通过设置WITH CHECK OPTION选项：

可以看到上面，插入不满足条件的时候会报错。

show tables命令会显示出数据库下所有的表，虽然索引是虚表，也会被显示出来，如果只想看到架构下的基表，可以通过information_schema架构下的TABLE表来查询，并搜索表类型为BASE TABLE的表，SQL语句如下：

2、物化视图

物化视图使视图不是虚表，而是根据基表实际存在的实表。

物化视图可用于预先计算并保存多表的链接或聚集(GROUP BY)等耗时较多的SQL操作结果，这样在执行复杂操作的查询时，就可以避免进行这些耗时的操作。

Oracle物化视图的创建方式：

• BUILD IMMEDIATE(创建视图时生成数据)
• BUILD DEFERRED(创建视图的时候不生成数据)

物化视图的刷新模型，物化视图的刷新是指当基表发生DML操作后，什么时候采用什么方式和基表进行同步：

• ON DEMAND(用户需要的时候)
• ON COMMIT(对基表DML操作提交的时候刷新)

刷新的方法：

• FAST(增量刷新)
• COMPLETE(完全刷新)
• FORCE(刷新时会去判断是否可以进行快速刷新，如果可以采用快速FAST方式)
• NEVER(不进行任何刷新)

八、分区表

1、分区概述

分区不是在存储引擎层完成的，因此不是只有InnoDB存储引擎支持分区，还有MyISAM、NDB等都支持。
分区的过程是将一个表或索引分解为更小、更可管理的部分。

• 一个表或索引可能由数十个物理分区组成。
• 每个分区都是独立处理的对象，可以独自处理

MySQL支持水平分区，不支持垂直分区。
MySQL的分区是局部分区索引，一个分区中既存放了数据又存放了索引，而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中，MySQL现在不支持全局分区。

查看是否启动分区功能的命令：

• show variables like ‘%partition%’;
• show plugins;

分区主要用于数据库高可用性的管理，可能会给某些SQL性能带来提升。但是如果一昧使用分区，不知道其如何工作，也不清楚你的应用如何使用分区，极有可能带来负面影响。

MySQL数据库分区类型：

• RANGE分区：将给定连续区间的列值放入区间
• LIST分区：和RANGE类似，只是LIST分区面向离散的值。
• HASH分区：根据用户自定义的表达式的返回值进行分区
• KEY分区：根据MySQL数据库提供的哈希函数来进行分区

不论创建何种类型的分区，如果表中存在主键或者唯一索引的时候，分区必须是唯一索引的一个组成部分，否则会报错。

如果没有指定主键、唯一索引，可以指定任何一列作为分区。

2、分区类型

1、RANGE分区(最常用)

下面创建了一个id列的区间分区表。当id小于10的时候，数据插入p0分区，当id大于10小于20的时候，数据插入p1分区。

查看表在此磁盘的物理文件，启动分区之后，表不再由一个ibd文件组成，而是由建立分区时的各个ibd文件组成，如下面的t#P#p0.ibd，t#P#p1.ibd

插入数据：

insert into t select 9;
insert into t select 10;
insert into t select 15;

因为表t根据id进行分区，所以数据是根据列id的值范围存放在不同的物理文件中的，可以通过查询information_schema架构下的PARTITIONS表来查看每个分区的具体信息：

• TABLE_ROWS列反映了每个分区中记录的数量。
• PARTITION_METHOD表示分区的类型，这里显示的是RANGE。

对于一个表，由于我们定义了分区，因此对于插入的值应该严格遵守分区的定义。当插入一个不在分区中的值的时候，MySQL会抛出异常。

添加MAXVALUE值的分区表，可以理解为正无穷。

RANGE分区主要用于日期列的分区，例如销售类的表。可以根据年来区分存放销售记录。

这样创建的好处，便于对sales这张表的管理，如果要删除2008年的数据，不需要执行delete from sales where date>=‘2008-01-01’ and date<=‘2008-12-31’;可以直接删除分区。

explain partitions select * from sales where date>='2008-01-01' and date<='2008-12-31';

通过explain partitions可以发现，上述语句中，SQL优化器只要搜索p2008分区，而不会搜索所有的分区-称为Partition Pruning(分区修剪)，所以查询速度大大提升。

如果上面的SQL语句改成date<'2019-01-01’则会去搜索两个分区。

优化器只能对YEAR()、TO_DAYS()、TO_SECONDS()、UNIX_TIMESTAMP()这类函数进行优化选择。

2、LIST分区

LIST和Range相似，只不过分区列的值是离散的，而不是连续的。

如果插入的值不在分区的定义中，MySQL会抛出异常。

3、HASH分区

HASH分区的目的是将数据均匀的分布到预先定义的各个分区中。
在RANGE和LIST中，必须明确指定一个给定的列值或者列值集合应该保存在哪个分区中，而在HASH分区中，MySQL自动完成这个工作，用户所要做的只是基于将要进行哈希分区的列指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

使用HASH分区分割一个表，要在create table语句上添加一个“PARTITION BY HASH(expr)”子句。其中expr是返回一个整数的表达式。

例子

如果b为2010-04-01,则YEAR(b)返回2010。再和4进行取余，得到2。因此放入p2分区。

4、KEY分区(使用MySQL提供的函数分区)

KEY分区和HASH分区类似，不同之处在于HASH分区使用用户定义的函数进行分区。KEY分区使用MySQL数据库提供的函数进行分区

5、COLUMNS分区

上面讲的四种分区，数据必须是整形，如果不是，进行转化。
MySQL5.5支持COLUMNS分区可以直接使用非整形的数据进行分区，分区根据类型直接比较而得。
COLUMNS分区支持一下类型的数据类型：

• 所有的整型类型，如INT、SMALLINT、TINYINT、BIGINT。FLOAT和DECIMAL不支持
• 日期类型，如DATE和DATETIME，其余不支持。
• 字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT不支持

3、子分区(在原本分区基础上再分区)

子分区就是在分区的基础上再进行分区，有时也叫这种分区为复合分区。

MySQL数据库允许在RANGE和LIST的分区上再进行HASH或KEY的子分区。

4、分区中的NULL值

MySQL允许对NULL做分区，MySQL数据库的分区总是视NULL值小于任何的一个非NULL值，这和MySQL中处理NULL值的ORDER BY操作是一样的。

• 在RANGE中插入NULL，放在最左边的分区。
• LIST中要使用NULL，要显示指出哪个分区中存放NULL值，否则报错
• HASH和KEY对于NULL的处理和RANGE、LIST不一样，任何分区函数都会将含有NULL值的记录返回0.

5、分区和性能

略