MySQL技术内幕InnoDB存储引擎学习笔记第四章表

ゝ一世哀愁。 2023-01-06 11:39 123阅读 0赞

InnoDB引擎表中，每张表都有一个主键，如果创建表时没有显式定义主键，则：  
1.首先看表中是否有非空的唯一索引，如果有，则该列为主键。  
2.否则自动创建一个6字节大小的指针作为主键。

InnoDB所有数据都逻辑地放在一个表空间中，表空间又由段、区、页（块）组成。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70]  
如果启用了innodb\_file\_per\_table参数，每张表的表空间中存放的只是数据、索引和插入缓冲，其他数据如Undo信息、系统事务信息、二次写缓冲等还是存放在原来的共享表空间中。因此，即使启用了此参数，共享表空间大小还是会增大。

在打开innodb\_file\_per\_table参数后，先检查共享表空间大小：  
![在这里插入图片描述][20210115150816169.png]  
ls的-h选项可显示更人性化阅读的文件大小。紧接着产生Undo操作：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 1]  
可见还未commit时共享表空间大小增加到114MB。之后rollback事务再查看共享表空间大小：  
![在这里插入图片描述][20210115151356925.png]  
可见还是114MB，InnoDB不会在rollback时收缩此表空间，但会判断Undo信息是否还需要，如不需要，则会将这些空间标为可用空间，供下次Undo使用。

表空间由各个段组成，常见的段有数据段、索引段、回滚段等，因为InnoDB引擎表是索引组织的，因此数据即索引，索引即数据，数据段是B+树的叶节点，而索引段是B+树的非叶节点。

Innodb引擎对段的管理由引擎自身完成，与Oracle自动段空间管理（ASSM）相似，但InnoDB没有手动段空间管理（MSSM）方式。

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

区是64个连续的页组成的，每个页大小16KB，每个区大小1MB，对于数据段，InnoDB引擎最多每次可以申请4个区。启用innodb\_file\_per\_table后，创建的表默认大小96KB，这小于一个区的最小大小1MB，这是由于每个段开始时，先有32个页大小的碎片页来存放数据，这些页使用完后才是64个连续页的申请。

innodb\_file\_per\_table参数开启时，创建一个空表，查看该表的表空间文件大小：

CREATE TABLE t1(
        col1 INT NOT NULL AUTO_INCREMENT,
        col2 VARCHAR(7000);
        PRIMARY KEY(col1)
    ) ENGINE = INNODB;

之后查看该表所属表空间的大小：

![在这里插入图片描述][20210116163017553.png]  
页是InnoDB磁盘管理的最小单位，一页16KB（Oracle和SQL server的块是8KB），且不可更改（改源码可以）。

常见页类型有：  
1.数据页（B-tree Node）。  
2.Undo页。  
3.系统页。  
4.事务数据页。  
5.插入缓冲位图页。  
6.插入缓冲空闲列表页。  
7.未压缩的二进制大对象页。  
8.压缩的二进制大对象页。

InnoDB引擎是面向行的，数据的存放按行存，每个页最多允许存放16KB/2-200=7992行记录，即每行最小2字节，200字节是每个页预留的大小。

MySQL infobright引擎按列存放数据，这对于数据仓库下分析类SQL语句的执行和数据压缩有好处。

InnoDB表从物理上看，由共享表空间、日志文件组（Redo文件组）、表结构定义文件（frm文件，任何表结构文件都以frm为后缀，与引擎无关）组成，如果启用了参数innodb\_file\_per\_table，则表还会独立地产生一个表空间文件，其中存放了数据、索引、表的内部数据字典信息。

MySQL 5.1中的InnoDB引擎提供了Compact（MySQL 5.0中就有了，在5.1中是默认格式）和Redundant两种存放行记录的格式，后者是为兼容之前版本保留的，在源码中是用PHYSICAL RECORD（NEW STYLE）和PHYSICIAL RECORD（OLD STYLE）来区分两者的。可通过以下命令查看表当前使用的行格式：

SHOW TABLE STATUS LIKE 'mytest%';

运行它：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 2]  
指定表的行格式：

CREATE TABLE tblName(
        col1 INT PRIMATY KEY
    ) ROW_FORMAT = COMPACT;

Compact行记录的设计目标是高效存放数据，一个页中存放的行数据越多，性能越高。  
![在这里插入图片描述][20210117191219121.png]  
变长字段长度列表中按列顺序的逆序存放变长字段长度。列表中每个列长度由两字节表示，因此变长字段varchar最大长度为65535字节。

NULL标志位指示了此行数据是否有列是NULL值，如第2、3列值为NULL，则NULL标志位值为6（110）。后边的列数据字段中，如果有列值为NULL，则不占用列数据，即NULL值除了占有NULL标志位，实际存储不占有任何空间。

记录头（record header）信息字段固定占用5字节，共40位，以下是每一位的含义：  
![在这里插入图片描述][20210117203019553.png]  
![在这里插入图片描述][20210117203028148.png]  
列数据n（n为1、2、3…）字段存放每列的数据，但还有两个隐藏列，事务ID列和回滚指针列，大小分别是6字节和7字节，若InnoDB没有定义主键，每行还会增加一个6字节的ROWID列。

例子：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 3]  
之后打开mytest.ibd（`hexdump -C -v mytest.ibd > mytest.txt`，-C选项含义为输出规范的十六进制和ASCII码，-v选项显示所有重复的数据，默认重复的数据显示为\*），可发现如下内容：  
![在这里插入图片描述][20210117205059923.png]  
行记录从0000c078开始，以下是各二进制位的解释：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 4]  
![在这里插入图片描述][20210118162322147.png]  
字段长度偏移列表字段中元素是各列数据的尾后字节相对于第一列数据开头的偏移量，按列的逆序排列。

Redundant记录头信息字段固定占48位，以下为各位解释：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 5]  
从上表的n\_fields值可知，在Redundant行格式下，一个表中最多支持1023行。

Redundant列数据字段也有三列隐藏列。

Redundant行格式对于VARCHAR类型的NULL值不占用任何存储空间，CHAR类型的NULL值需要占用空间（如CHAR(2)类型列的NULL值也占用`2*当前字符集中单个字符所占字节数`的字节大小）。

InnoDB可将一条记录中某些数据存放到真正的数据页之外，作为行溢出数据，一般BLOB、LOB、VARCHAR可能会有行溢出数据。

相对于Oracle的VARCHAR2最大能存4000字节、SQL server最大能存8000字节，MySQL的VARCHAR最大能存65535字节，但创建VARCHAR(65535)类型列时会报错，因为还有别的开销，实际能存放的长度为65532字节。此外，MySQL官方手册中的65535长度指的是一张表中所有VARCHAR列最大长度总和，如所有VARCHAR列长度总和大于此值，则无法创建表。

一般数据存放在数据页中，但发生行溢出时，只会将部分数据（768字节，不管这一字段数据有多长）存在数据页中，这部分数据后跟一个偏移量，指向剩下的行溢出数据，溢出的行数据存放在页类型为Uncompress BLOB Page的页中：  
![在这里插入图片描述][20210120151852230.png]  
多少长度的VARCHAR数据存放在数据页取决于一页中是否能存放下两条数据。InnoDB表是索引组织的，即B+树结构，因此每个页中至少要有两个行记录，否则，在极端情况下每个页只有一个记录时，整个B+树会退化成链表形式。因此当页中只能存放下一条记录时，InnoDB引擎会自动将部分行数据存放到溢出页中。在以下情况：  
![在这里插入图片描述][20210119151240595.png]  
9000长度的变长字段可以放在一个页（16K）中，但不能保证两条记录都能存放到一个页中，因此上例仍然会使用BLOB页。如果一个页中至少能放入两行数据，则varchar就不会放到BLOB页中。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 6]  
如上图，表中只有一个变长字段的情况下，所有数据都存在数据页的阈值是8098。

对于BLOB列的表，如果一行数据量足够小（两行能放在同一页中），则数据会全部存放在数据段，但既然我们使用了BLOB列，大多数情况下存的行数据还是会发生行溢出，此时数据页只会保存前768个字节，剩下的内容保存在BOLB页中。

InnoDB Plugin版本引入了新的文件格式（即页格式），之前版本支持的Compact和Redundant格式称为Antelope文件格式，新的文件格式称为Barracuda。

Barracuda文件格式下拥有两种新的行记录格式Compressed和Dynamic。这两种新格式对于BLOB数据采用了完全的行溢出方式，在数据页中只存放20个字节的指针，指向存放该列数据的BLOB页。

Compressed行记录的另一个功能是存储在其中的行数据会以zlib算法进行压缩，对于BLOB、TEXT、VARCHAR这类大长度类型的数据能进行非常有效的存储。

从MySQL 4.1开始，CHAR(N)的N指的是字符长度而非之前版本的字节长度，即不同字符集下，CHAR的内部存储的不是定长的数据。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 7]  
如上图，set names 'xxx’命令可以使character\_set\_client（客户端使用的字符集）、character\_set\_connection（连接数据库字符集，如一个查询会被服务器从character\_set\_client转换到character\_set\_connection，当查询执行时，查询会被服务器从character\_set\_connection转换到列字符集）、character\_set\_results（查询结果会从列字符集转换到character\_set\_results，可见查询请求比查询返回多了一次字符集转换）参数值统一。

查看上表中各行a字段长度：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 8]  
![在这里插入图片描述][20210120155726752.png]  
可见一个中文字符占用了两字节。查看内部十六进制存储：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 9]  
总结：对于多字节字符编码的CHAR类型列，不再代表固定字节长度的字符串，如UTF-8编码下CHAR(10)列最小存储10字节字符，最大存储30字节字符。因此，对于多字节字符编码的CHAR类型的存储，InnoDB引擎在内部将其视为变长字符，会在每行的变长字符长度列表中记录CHAR类型的长度。而对于未能占满CHAR的列还是以0x20（空格）填充。

表中行实际数据存放在B树节点类型页中。

以下关于InnoDB数据页结构的描述并没有在MySQL官方手册中提及，是作者看源码了解的，同时参考了比较久远的其他人的分析，可能已经不准确了。

InnoDB数据页由以下部分组成：  
1.文件头。大小固定。  
2.页头。大小固定。  
3.Infimun+Supremum Records。  
4.用户记录，即行记录。  
5.空闲空间。  
6.页目录。  
7.文件结尾信息。大小固定。

![截图][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 10]  
File Header、Page Header、File Trailer标识此页的一些信息，如Checksum、数据所在索引层等。

File Header用来记录页的一些头信息，大小38字节：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 11]  
File Header每个组成部分的作用：  
1.FIL\_PAGE\_SPACE\_OR\_CHKSUM：MySQL 4.0.14前，该值表示页属于哪个表空间，因为如果我们没有开启innodb\_file\_per\_table，共享表空间中存放了许多页，此版本后，该值表示页的checksum值。  
2.FIL\_PAGE\_OFFSET：表空间中页的偏移值。  
3.FIL\_PAGE\_PREV、FIL\_PAGE\_NEXT：该页的前一个及后一个页。B+树特性决定了叶子节点必须是双向列表。  
4.FIL\_PAGE\_LSN：表示该页最后被修改的日志序列位置LSN（Log Sequence Number）。  
5.FIL\_PAGE\_TYPE：页的类型，有以下几种：  
![在这里插入图片描述][20210121130917121.png]  
![在这里插入图片描述][20210121130924404.png]  
6.FIL\_PAGE\_FILE\_FLUSH\_LSN：该值仅在数据文件中的一个页中定义，代表文件至少被更新到了该LSN值。  
7.FIL\_PAGE\_ARCH\_LOG\_NO\_OR\_SPACE\_ID：从MySQL 4.1开始，该值表示页属于哪个表空间。

Page Header用来记录数据页的状态信息，大小为56字节：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 12]  
Page Header每部分作用：  
1.PAGE\_N\_DIR\_SLOTS：在页目录中的槽数。  
2.PAGE\_HEAD\_TOP：堆中第一个记录的指针。  
3.PAGE\_N\_HEAP：堆中记录数。  
4.PAGE\_FREE：指向空闲列表的首指针。  
5.PAGE\_GARBAGE：已删除记录的字节数，即行记录结构中，delete flag为1的记录大小总数。  
6.PAGE\_LAST\_INSERT：最后插入记录的位置。  
7.PAGE\_DIRECTION：最后插入的方向，可能的取值为PAGE\_LEFT（0x01）、PAGE\_RIGHT（0x02）、PAGE\_SAME\_REC（0x03）、PAGE\_SAME\_PAGE（0x04）、PAGE\_NO\_DIRECTION（0x05）。  
8.PAGE\_N\_DIRECTION：一个方向连续插入记录的数量。  
9.PAGE\_N\_RECS：该页中记录的数量。  
10.PAGE\_MAX\_TRX\_ID：修改过当前页的最大事务ID，该值仅在Secondary Index（非聚簇索引）定义。  
11.PAGE\_LEVEL：当前页在索引树中的位置，0x00代表叶节点。  
12.PAGE\_INDEX\_ID：当前页属于哪个索引ID。  
13.PAGE\_BTR\_SEG\_LEAF：B+树叶节点中，文件段的首指针位置，该值仅在B+树的Root页中定义。  
14.PAGE\_BTR\_SEG\_TOP：B+树的非叶子节点中，文件段的首指针位置，该值仅在B+树的Root页中定义。

InnoDB引擎中，每个数据页中有两个虚拟的行记录，用来限定记录边界，Infimun记录是比该页中任何主键值都要小的值，Supremum是比任何可能大的值还要大的值。这两个值在页创建时被建立，任何情况下都不会被删除。在Compact和Redundant行格式下，两者占用的字节数各不相同。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 13]  
Free Space指空闲空间，是链表结构，当一条记录被删除后，该空间会被加入空闲链表中。

Page Directory（页目录）中存放了页中记录的相对位置（可根据页目录在页中快速查找记录），这些记录指针称为Slots（槽）或目录槽。与其他数据库系统不同的是，InnoDB不是每个记录都有一个槽，而是一个槽中可能有多条记录，最少4条，最多8条。槽中数据按键顺序存放，这样可以利用二叉查找快速找到记录指针。

如果一个页中有记录（‘i’, ‘d’, ‘c’, ‘b’, ‘e’, ‘g’, ‘l’, ‘h’, ‘f’, ‘j’, ‘k’, ‘a’），每个槽中有4条记录，则会有3个槽，其中内容可能是（‘a’, ‘e’, ‘i’），因此二叉查找后InnoDB可能还需recorder header中的next\_record继续查找相关记录，这同时解释了recorder header中n\_owned值的含义，该值表示前向还有多少条记录归属这个槽，即该槽中还有多少记录可查找。

B+树索引本身不能找到具体的一条记录，只是找到该记录所在的页，并把页加载到内存，然后通过Page Directory进行二叉查找，这部分查找在内存中进行且时间复杂度很低，因此这段时间可被忽略。

为保证页能完整写入磁盘（写入过程中发生磁盘损坏、机器宕机等情况时），InnoDB引擎为每个页设置了File Trailer部分，其中只包含一个字段FIL\_PAGE\_END\_LSN，占8字节，前4字节表示该页checksum值，后4字节和File Header中的FIL\_PAGE\_LSN字段相同，可通过这两个值与File Header中的FIL\_PAGE\_SPACE\_OR\_CHKSUM和FIL\_PAGE\_LSN进行比较，来保证此页的完整性。

创建一张表以供具体查看数据页内部结构：  
![在这里插入图片描述][20210123150848481.png]  
创建一个向上表中插入多行数据的存储过程：

DELIMITER $$
    
    CREATE PROCEDURE load_t(count int unsigned)
    BEGIN
    SET @c = 0;
    WHILE @c < count do
    INSERT INTO t SELECT NULL, REPEAT(CHAR(97 + RAND() * 26), 10);
    SET @c=@c+1;
    END WHILE;
    END;
    $$
    
    DELIMITER ;

如上，rand函数返回0~1中的一个随机数，运行以上存储过程向表中插入100条数据：

CALL load_t(100);

查看表中记录：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 14]  
二进制方式查看该表数据页：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 15]  
首先分析File Header的38个字节：  
1.52 1b 24 00是数据页的checksum值。  
2.00 00 00 03是页的偏移量。  
3.ff ff ff ff表示前一个页，由于只有一个页，因此是该值。  
4.ff ff ff ff表示后一个页，由于只有一个页，因此是该值。  
5.00 00 00 0a 6a e0 ac 93是页最后被修改的LSN。  
6.45 fb是页类型，表示数据页。  
7.00 00 00 00 00 00 00暂时不管该值。  
8.00 00 00 dc是表空间的SPACE ID。

接着先看最后的File Trailer部分（最后8字节）：  
1.95 ae 5d 39是checksum值，该值通过checksum函数与File Header部分的checksum值进行比较。  
2.6a e0 ac 93与File Header部分中的值的后4字节相等。

接着分析Page Header部分：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 16]  
PAGE\_N\_DIR\_SLOTS=0x001a，表示Page Directory有26个槽（每个槽占2字节），这52字节内容存放在0x0000ffc4到0x0000fff7：  
![在这里插入图片描述][20210123153435329.png]  
PAGE\_HEAP\_TOP=0x0dc0，代表空闲空间开始位置的偏移量，此数据页开始位置是0xc0000（16K\*3），即0xc000+0x0dc0=0xcdc0处开始，观察此位置：  
![在这里插入图片描述][20210123153736489.png]  
可见此位置确实是最后一行结束的位置，剩下部分都是空闲空间了。

PAGE\_N\_HEAP=0x8066，表示堆中记录数，行格式为Compact时，此值的初始值为0x8002，行格式为Redundant时，此值的初始值为2，这些初始值个位都是2表示页中初始时就有了Infimun和Supremum伪记录行，0x8066-0x8002=0x64，即该页中有100行记录。

PAGE\_FREE=0x0000，表示删除的记录数。

PAGE\_GARBAGE=0x0000，表示删除的记录字节为0。

PAGE\_LAST\_INSERT=0x0da5，表示页最后插入的位置的页内偏移量，查看最后插入的0xc0000+0x0da5=0xcda5位置：  
![在这里插入图片描述][20210123155411318.png]  
可见该位置确实是最后插入的a列值为100的行记录，此处指向的是行记录的内容，而非行记录的变长字段长度列表位置。

PAGE\_DIRECTION=0x0002，表示PAGE\_DIRECTION的方向向右，因为表是通过自增长的方式进行行记录的插入。

PAGE\_N\_DIRECTION=0x0063，表示一个方向连续插入记录的数量，因为我们以自增长方式插入了100条记录，因此该值为99。

PAGE\_N\_RECS=0x0064，表示该页中的记录数为100，该值与PAGE\_N\_HEAP包含两个伪行记录且通过有符号方式记录（PAGE\_N\_HEAP值为0x8066，最高位表示负号）不同。

PAGE\_LEVEL=0x00，代表该页为叶子节点，因为数据量较少，因此当前B+树索引只有一层。B+树叶子层数据页的该值总是0x00。

PAGE\_INDEX\_ID=0x00000000000001，表示索引ID。

接下来是行记录数据：  
![在这里插入图片描述][20210123160708342.png]  
从0xc06e到0xc077存放的是两个伪行记录，InnoDB引擎将伪行记录设为只有一个列，类型为Char(8)。整理伪行记录：  
![在这里插入图片描述][20210123160838690.png]  
分析两个伪行记录的recorder header部分，最后两个字节位00 1c表示下一个记录相对于当前位置的偏移量，结果为0xc063+0x001c=0xc07f，查看该位置：  
![在这里插入图片描述][20210123162302224.png]  
将该位置二进制位整理可得：  
![在这里插入图片描述][20210123162337550.png]  
![在这里插入图片描述][2021012316234286.png]  
recorder header的最后两个字节记录下一行记录的页偏移量，这样可以得到该页中所有的行记录；而通过page header的PAGE\_PREV和PAGE\_NEXT可以知道上一个页和下一个页的位置，这样就能找到整张表的所有行记录数据。

Page Directory范围为0x0000ffc4到0x0000fff7，其中内容如下：  
![在这里插入图片描述][20210124133026410.png]  
其中内容是逆序存放的，每个槽两个字节，可见00 63是最初行的页内相对位置（即0x0063），而00 70是最后一行记录的页内相对位置（实际位置即0xc070），这就是之前的Infimun和supremum的伪行记录。Page Directory中的数据是按主键顺序存放的，并且是槽是稀疏的，如想找到一行，还需recorder header中的n\_owned（向前还有几条记录属于该行），如想找主键列a的值为5的行，通过二叉查找Page Directory的槽，找到记录的页内相对位置为00 e5，之后找行记录的实际位置0xc0e5：  
![在这里插入图片描述][20210124151202311.png]  
但此位置的行的主键a列的值为4，不是要查找的5，但前面有5个字节的recorder header（04 00 28 00 22），第4~8位表示n\_owned，值为4，表示此槽中向前还有四个记录，通过recorder header中的最后两个字节0x0022可找到下一个记录的位置（相对于当前记录位置，即0x0022+0xc0e5=0xc107，这是主键位5的记录。

随着InnoDB引擎的发展，会出现新的页数据结构支持新的功能特性，比如InnoDB Plugin提供的新的支持表压缩的页数据结构，新的页数据结构与之前版本的页并不兼容，因此从InnoDB Plugin开始，InnoDB引擎通过Named File Formats机制解决不同版本下页结构兼容性问题。

InnoDB Plugin将之前版本的文件格式定义为Antelope，将这个版本支持的文件格式定义为Barracuda。新的文件格式总是包含之前版本的页格式：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 17]  
未来InnoDB引擎还将引入新的文件格式，文件格式的名称取自动物的名字。

参数innodb\_file\_format用来指定文件格式，查看当前InnoDB引擎使用的文件格式：  
![在这里插入图片描述][20210124152420944.png]  
参数innodb\_file\_format\_check用来检测是否支持当前InnoDB引擎文件格式，默认值为ON，如果出现不支持的文件格式，可能会在错误日志文件中看到如下错误信息：  
![在这里插入图片描述][202101241540523.png]  
关系数据库系统和文件系统的一个不同点是前者能保证存储数据的完整性，不需要应用程序控制。几乎所有关系型数据库都提供了约束机制来保证数据库中的数据完整性，数据完整性有三种形式：  
1.实体完整性：保证表中有一个主键，InnoDB引擎中可通过定义Primary Key或Unique Key约束保证实体完整性。还可以通过编写一个触发器保证完整性。  
2.域完整性：保证数据的值满足特定条件，InnoDB引擎可通过以下途径保证域完整性：  
（1）选择合适的数据类型确保一个数据值满足特定条件。  
（2）外键约束。  
（3）触发器。  
（4）DEFAULT约束。  
3.参照完整性：保证两张表之间的关系，InnoDB引擎支持外键，可定义外键强制参照完整性，也可通过触发器强制执行。

InnoDB提供了以下约束：Primary Key、Unique Key、Foreign Key、Default、NOT NULL。

约束可在建表时定义，也可以在建表后ALTER TABLE定义。Unique Key约束也可以通过CREATE UNIQUE INDEX建立。主键约束的默认约束名为PRIMARY KEY，而Unique Key的默认约束名和列名一样。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 18]  
给已经存在的表定义唯一约束：

ALTER TABLE u ADD UNIQUE KEY uk_id_card(id_card);

运行它：  
![在这里插入图片描述][20210124160555156.png]  
![在这里插入图片描述][20210124160602263.png]  
创建外键约束：  
![在这里插入图片描述][20210125082228237.png]  
可通过information\_schema库下的TABLE\_CONSTRAINTS表查看某个表上的约束：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 19]  
对于外键约束，还可查看表REFERENTIAL\_CONSTRAINTS了解详细外键属性：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 20]  
![在这里插入图片描述][20210125082830417.png]  
当创建一个主键索引时，就创建了一个唯一约束。

默认，MySQL允许非法或不正确的数据的插入或更新，内部会将其转化为一个合法的值，如INT NOT NULL列插入NULL值时，会将其改为0再插入。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 21]  
![在这里插入图片描述][20210125084931337.png]  
上图可见当给NOT NULL列插入NULL值或插入非法值（上例中日期非法）时，MySQL都没有报错，而是警告。可将参数sql\_mode改为以下值，用来严格审核输入的值：  
![在这里插入图片描述][2021012508542580.png]  
MySQL不支持传统CHECK约束，但可通过ENUM和SET类型解决部分这样的约束需求：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 22]  
可见，对于第二条插入还是警告而非报错，如想进行严格的输入检查，还需将sql\_mode参数值设为STRICT\_TRANS\_TABLES。

使用以上方法只限于对离散数值的约束，对于传统CHECK约束支持的连续值的范围约束或更复杂的约束，ENUM和SET无能为力，此时需要通过触发器实现约束。

触发器可以在增删改命令之前或之后自动调用SQL命令或存储过程。只有具有Super权限的用户才能执行以下创建触发器的命令：

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

以上语句中trigger\_stmt表示trigger\_statement（触发器语句）。

最多可为一个表建立六个触发器。当前MySQL只支持FOR EACH ROW的触发方式，即按每行记录进行触发，不支持如DB2的FOR EACH STATEMENT的触发方式。

以下是使用触发器的例子，有一张消费表，用户每次购买物品都会给用户的钱数减去一个金额，如果有人做了类似减去一个负值的操作，这样用户的钱没减少反而会增加。以下是消费表的定义：

CREATE TABLE usercash(
        userid int,
        cash   int unsigned not null
    );

向表中插入一个用户：

INSERT INTO usercash 
    SELECT 1, 1000;

消费时减去一个负值：

UPDATE usercash
    SET cash = cash - (-20)
    WHERE userid = 1;

虽然以上操作可以完成，但逻辑上是错误的，可通过触发器约束这个逻辑行为。先创建一个记录减去负数行为的表：

CREATE TABLE usercash_err_log(
        userid     int           not null,
        old_cash   int unsigned  not null,
        new_cash   int unsigned  not null,
        user       varchar(30),
        time       datetime
    );

创建一个触发器：

DELIMITER $$
    CREATE TRIGGER tgr_usercash_update BEFORE UPDATE ON usercash
    FOR EACH ROW
    BEGIN
    	IF NEW.cash - OLD.cash > 0 THEN
    	    INSERT INTO usercash_err_log 
    	    SELECT OLD.userid, OLD.cash, NEW.cash, user(), now();
    	    SET NEW.cash = OLD.cash;    -- 非法输入，将新值设为原来值
    	END IF;
    END;
    $$
    DELIMITER ;

删除消费表中测试用户：

DELETE FROM usercash;

重新插入一条用户信息：

INSERT INTO usercash 
    SELECT 1, 1000;

消费时减去一个负值：

UPDATE usercash
    SET cash = cash - (-20)
    WHERE userid = 1;

查看消费表内容：  
![在这里插入图片描述][20210125183259771.png]  
查看错误记录表内容：  
![在这里插入图片描述][20210125183351387.png]  
外键用来保证参照完整性，MyISAM引擎本身不支持外键，对于外键的定义只是起到注释作用。InnoDB引擎完整支持外键约束，外键定义如下：

[CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...)
    REFERENCES table_name(index_col_name, ...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]
    reference_option
    RESTRICT|CASCADE|SET NULL|NO ACTION

创建两张表演示外键：

CREATE TABLE parent(
        id INT NOT NULL,
        PRIMARY KEY(id)
    ) ENGINE = INNODB;
    
    CREATE TABLE child(
        id              INT,
        parent_id       INT,
        INDEX par_ind(parent_id),
        FOREIGN KEY(parent_id) REFERENCES parent(id)
    ) ENGINE = INNODB;

一般，被引用的表称为父表，引用的表称为子表。外键定义为ON DELETE和ON UPDATE表示父表做DELETE和UPDATE操作时子表所做的操作，可定义的子表操作有：  
1.CASCADE：当父表发生DELETE或UPDATE操作时，相应的子表中的数据也被DELETE或UPDATE。  
2.SET NULL：当父表发生DELETE或UPDATE操作时，相应的子表中的数据被更新为NULL值，子表中相应外键列必须允许NULL值。  
3.NO ACTION：当父表发生DELETE或UPDATE操作时，抛出错误，不允许此类操作发生。  
4.RESTRICT：当父表发生DELETE或UPDATE操作时，抛出错误，不允许此类操作发生。这是默认的外键设置。

Oracle中有延时检查的外键约束，而目前MySQL约束都是即时检查的，NO ACTION来源于标准SQL，含义为延时检查的RESTRICT，因此在MySQL中NO ACTION和RESTRICT的作用相同。

Oracle中外键通常被忽视的地方是外键列没有加索引，而InnoDB引擎在外键建立时会自动对该列加一个索引，这与SQLserver的做法一样，可以很好避免外键列上无索引导致的死锁问题的产生。

导入数据时会花费大量时间在外键约束检查上，MySQL外键是即时检查的，导入的每一行都会进行外键检查，可在导入数据时忽视外键的检查：

SET foreign_key_checks = 0;
    -- 导入数据....
    SET forrign_key_checks = 1;

视图是命名的虚表，它由一个查询定义，可当做表使用。与持久表不同的是视图中的数据没有物理表现形式。

视图主要用途之一是用做一个抽象装置，程序不用关心基表结构。视图一定程度起到安全层的作用。

MySQL 5.0开始支持视图，创建视图语法如下：

CREATE 
    [OR REPLACE]
    [ALGORITHM = {
       UNDEFINED|MERGE|TEMPTABLE}]
    [DEFINER = {
       USER|CURRENT_USER}
    [SQL SECURITY {
       DEFINER|INVOKER}]
    VIEW view_name [(column_list)]
    AS select_stmt
    [WITH [CASCADED|LOCAL] CHECK OPTION]

虽然视图是虚拟表，但我们仍能对某些视图进行更新，即通过视图的定义更改基表，称可进行更新操作的视图为可更新视图。视图定义中的WITH CHECK OPTION指对于可更新的视图，更新的值是否需要检查。

视图例子，先创建一个基表：

CREATE TABLE t(
        id int
    );

创建基于上表的视图：

CREATE VIEW v_t
    AS 
    	SELECT * 
    	FROM t 
    	WHERE id < 10;

向视图中插入数据：

INSERT INTO v_t
    SELECT 20;

运行它：  
![在这里插入图片描述][20210126180254903.png]  
插入成功，之后查看视图中的数据：

SELECT *
    FROM v_t;

运行它：  
![在这里插入图片描述][20210126180351417.png]  
但并不能查到数据，但表中此时可以查询到此数据，接下来更改视图定义，加上WITH CHECK OPTION：

ALTER VIEW v_t
    AS
        SELECT * 
        FROM t
        WHERE id < 10
    WITH CHECK OPTION;

再向视图中插入数据：  
![在这里插入图片描述][20210126180713776.png]  
此时MySQL会对更新视图的数据进行检查，不满足视图定义条件的将会抛出异常。

SHOW TABLES命令也会显示出视图：  
![在这里插入图片描述][20210126180834157.png]  
如不想显示视图，可通过information\_schema架构下的tables表查询：

SELECT * 
    FROM information_schema.tables
    WHERE table_type = 'BASE TABLE' AND table_schema = database();    -- database函数返回当前使用的数据库名称

视图的元数据信息存储在information\_schema下的views表，其中含视图定义者、定义内容、是否可更新视图等信息：

SELECT *
    FROM information_schema.views 
    WHERE table_schema = database();

运行它：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 23]  
Oracle数据库支持物化视图，物化视图不是基于基表的虚表，而是实际存在的实表，可用于预先计算并保存表联结、聚集等耗时较多的操作的结果。SQLserver中称这种视图为索引视图。

Oracle数据库中有两种创建物化视图的方式：  
1.BUILD IMMEDIATE：默认创建方式，创建物化视图时就生成数据。  
2.BUILD DEFERRED：创建时不生成数据，以后根据需要再生成。

查询重写指当对物化视图的基表进行查询时，Oracle会自动判断能否通过物化视图得到结果，如果能，可避免复杂操作直接从计算好的物化视图中读取数据。

当物化视图的基表完成某些DML操作后，物化视图有以下时机和基表进行同步：  
1.ON DEMAND：在用户需要时进行刷新（用户决定何时要刷新）。  
2.ON COMMIT：对基表DML操作的同时进行刷新。

物化视图有四种刷新方式：  
1.FAST：增量刷新，只刷新自上次刷新以后进行的修改。  
2.COMPLETE：对整个物化视图进行完全的刷新。  
3.FORCE：Oracle在刷新时会判断是否可以进行快速刷新，如果可以，则使用FAST模式，否则使用COMPLETE方式。  
4.NEVER：不对物化视图进行刷新。

MySQL不支持物化视图，但可通过一些机制实现物化视图的功能，如要创建一个ON DEMAND的物化视图，我们可通过定时将数据导入另一张表实现，如我们有以下订单表：

CREATE TABLE orders (
        order_id          INT UNSIGNED   NOT NULL AUTO_INCREMENT,
        product_name      VARCHAR(30)    NOT NULL,
        price             DECIMAL(8, 2)  NOT NULL,
        amount            SMALLINT       NOT NULL,
        primary key(order_id)
    ) ENGINE = InnoDB;

向表中插入数据：

INSERT INTO orders 
    VALUES (NULL, 'CPU', 135.5, 1),
           (NULL, 'CPU', 125.6, 3),
           (NULL, 'Memory', 48.2, 3);

接着建立一张模拟物化视图的表，用来统计每件物品的信息：

CREATE TABLE orders_mv (
        product_name   VARCHAR(30)    NOT NULL,
        price_sum      DECIMAL(8, 2)  NOT NULL,
        amount_sum     INT            NOT NULL,
        price_avg      FLOAT          NOT NULL,
        orders_cnt     INT            NOT NULL,
        UNIQUE KEY(product_name)
    );

向物化视图中插入数据：

INSERT INTO orders_mv
    SELECT product_name, SUM(price), SUM(amount), AVG(price), COUNT(*)
    FROM orders
    GROUP BY product_name;

物化视图以\_mv结尾可让DBA理解这张表作用。用户需要时可清空物化视图表并重新导入数据即实现了ON DEMAND物化视图，这是完全刷新方式，要实现FAST刷新方式需要记录上次统计时order\_id的位置。

如想实现ON COMMIT的物化视图，Oracle中是使用物化视图日志实现的，MySQL中可通过触发器实现：

DELIMITER $$
    
    CREATE TRIGGER tgr_orders_insert
    AFTER INSERT ON orders
    FOR EACH ROW
    BEGIN
        SET @old_price_sum = 0;
        SET @old_amount_sum = 0;
        SET @old_price_avg = 0;
        SET @old_orders_cnt = 0;
    
        SELECT IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0), IFNULL(orders_cnt, 0)
        FROM orders_mv
        WHERE product_name = NEW.product_name
        INTO @old_price_sum, @old_amount_sum, @old_price_avg, @old_orders_cnt;
    
        SET @new_price_sum = @old_price_sum + NEW.price;
        SET @new_amount_sum = @old_amount_sum + NEW.amount;
        SET @new_orders_cnt = @old_orders_cnt + 1;
        SET @new_price_avg = @new_price_sum / @new_orders_cnt;
    
        -- REPLACE INTO语句会先检查表中是否有重复行，如有，删除该行数据再插入，该语句用于没有唯一约束的表时不会检查重复，而是直接插入，会造成重复数据
        REPLACE INTO orders_mv
        VALUES (NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_orders_cnt);
    END;
    $$
    
    DELIMITER ;

此时再向订单表中插入数据：

INSERT INTO orders
    VALUES (NULL, 'SSD', 299, 3),
           (NULL, 'Memory', 47.9, 5);

物化视图表就能实时更新：  
![在这里插入图片描述][20210127184211604.png]  
MySQL对于查询重写就无法实现了。

分区功能不是在存储引擎层完成的，因此不仅是InnoDB引擎支持分区。InnoDB、MyISAM、NDB等引擎支持分区。CSV、FEDERATED、MERGE等引擎不支持分区。

MySQL 5.1开始支持分区，此过程是将表或索引分解为多个更小、更可管理的部分， 逻辑上只有一个表或索引，但在物理上这个表或索引可能由数十个物理分区组成，每个分区都是独立的对象，可以独自处理，也可作为更大对象的一部分处理。

MySQL支持水平分区（将一张表中的不同行存入不同物理文件），不支持垂直分区（将一张表中的不同列存入不同物理文件）。MySQL的分区是局部分区索引，即一个分区中既存放了数据又存放了索引。

查看当前数据库是否启用了分区功能，MySQL 5.6.1之前可使用以下命令查询：

SHOW VARIABLES LIKE 'have_partitioning';

运行它：  
![在这里插入图片描述][20210128181817907.png]

MySQL 5.6.1之后可使用以下方式查询：

SHOW plugins;

运行它：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 24]  
分区不一定会使数据库变快，分区对于某些SQL语句性能可能会带来提高，但分区主要用于高可用性，利于数据库管理。

MySQL支持的分区类型：  
1.RANGE分区：行数据基于属于一个给定连续区间的列值放入分区。MySQL 5.5开始支持RANGE COLUMNS的分区。  
2.LIST分区：与RANGE分区类似，但LIST分区面向的是离散值。MySQL 5.5开始支持LIST COLUMNS的分区。  
3.HASH分区：根据用户自定义的表达式的返回值进行分区，返回值不能为负数。  
4.KEY分区：根据MySQL提供的哈希函数进行分区。

不管分区类型，如果表中存在主键或唯一索引，分区列必须是唯一索引的一个组成部分，以下创建分区语句错误：

CREATE TABLE t1(
        col1   INT   NOT NULL,
        col2   DATE  NOT NULL,
        col3   INT   NOT NULL,
        col4   INT   NOT NULL, 
        UNIQUE KEY (col1, col2)
    ) PARTITION BY HASH(col3)
      PARTITIONS 4;

改正以上代码：

CREATE TABLE t1(
        col1   INT   NOT NULL,
        col2   DATE  NOT NULL,
        col3   INT   NOT NULL,
        col4   INT   NOT NULL, 
        UNIQUE KEY (col1, col2)
    ) PARTITION BY HASH(col1)    -- 此处也可以使用col2
      PARTITIONS 4;

但如果建表时没有指定主键、唯一索引，可指定任一列为分区列，以下SQL正确：

CREATE TABLE t1(
        col1   INT   NOT NULL,
        col2   DATE  NOT NULL,
        col3   INT   NOT NULL,
        col4   INT   NOT NULL,
        KEY (col4)    -- 没有此索引也能成功运行
    ) PARTITION BY HASH(col1)
      PARTITIONS 4;

创建RANGE分区：

CREATE TABLE t (
        id INT
    ) ENGINE = InnoDB
      PARTITION BY RANGE(id) (
      PARTITION p0 VALUES LESS THAN (10),
      PARTITION p1 VALUES LESS THAN (20));

此时表不再由一个ibd文件组成，而是建立分区时的各个分区ibd文件组成：  
![在这里插入图片描述][20210128183522328.png]  
查看表分区的具体信息，如是什么分区、分区内现有多少行：

SELECT *
    FROM information_schema.partitions 
    WHERE table_schema = database() AND table_name = 't';

当向表t中插入不在分区范围内的行时，会报错。可通过对分区添加一个MAXVALUE（相当于正无穷）值的分区：

ALTER TABLE t
    ADD PARTITION (
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

此时，所有大于等于20的值将放入p2分区。

以下是RANGE分区的例子，根据年分区存放销售表的销售记录，创建该表：

CREATE TABLE sales (
        money     INT UNSIGNED NOT NULL,
        date      datetime
    ) ENGINE = innodb
      PARTITION BY RANGE (YEAR(date)) (
      PARTITION p2008 VALUES LESS THAN (2009),
      PARTITION p2009 VALUES LESS THAN (2010),
      PARTITION p2010 VALUES LESS THAN (2011));

这样便于管理表，如想删除2008年的数据，不需要执行delete语句，只需删除2008年数据所在分区：

ALTER TABLE sales 
    DROP PARTITION p2008；

这样做的另一个好处是可加快某些查询，如想查询2008年整年的销售额：

EXPLAIN PARTITIONS 
    SELECT *
    FROM sales
    WHERE date >= '2008-01-01' AND date <= '2008-12-31';

运行它：  
![在这里插入图片描述][20210129174824287.png]  
可见它只搜索了p2008分区。而如果SQL是这样的：

EXPLAIN PARTITIONS 
    SELECT *
    FROM sales
    WHERE date >= '2008-01-01' AND date < '2009-01-01';

书上的运行结果中要搜索的分区是p2008和p2009，但我使用MySQL 5.6.24还是只搜索p2008，如果将第二个条件改为’2009-01-02’就会搜索两个分区了，应该是新版本优化。

另一种按每年每月进行的分区：

CREATE TABLE sales1 (
        money     INT UNSIGNED NOT NULL,
        date      datetime
    ) ENGINE = innodb
      PARTITION BY RANGE (YEAR(date) * 100 + MONTH(date)) (
      PARTITION p201001 VALUES LESS THAN (201002),
      PARTITION p201002 VALUES LESS THAN (201003),
      PARTITION p201003 VALUES LESS THAN (201004));

此时如进行如下查询：

EXPLAIN PARTITIONS 
    SELECT *
    FROM sales1
    WHERE date >= '2010-01-01' AND date <= '2010-01-31';

运行它：  
![在这里插入图片描述][20210129175710978.png]  
会发现它对三个分区都进行了搜索，原因是对于RANGE分区的查询，优化器只能对YEAR()、TO\_DAYS()、TO\_SECONDS()、UNIX\_TIMESTAMP()函数进行优化，因此可将分区函数改为TO\_DAYS()，此函数返回参数日期是一年中的第几天：

CREATE TABLE sales2 (
        money     INT UNSIGNED NOT NULL,
        date      datetime
    ) ENGINE = innodb
      PARTITION BY RANGE (TO_DAYS(date)) (
      PARTITION p201001 VALUES LESS THAN (TO_DAYS('2010-02-01')),
      PARTITION p201002 VALUES LESS THAN (TO_DAYS('2010-03-01')),
      PARTITION p201003 VALUES LESS THAN (TO_DAYS('2010-04-01')));

此时就能只搜索一个分区了：  
![在这里插入图片描述][20210129180308600.png]  
LIST分区与RANGE分区相似，只是分区列的值是离散的，创建一个LIST分区表：

CREATE TABLE t (
        a INT,
        b INT
    ) ENGINE = innodb
      PARTITION BY LIST(b) (
      PARTITION p0 VALUES IN (1, 3, 5, 7, 9),
      PARTITION p1 VALUES IN (0, 2, 4, 6, 8));

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

在用INSERT插入多个行数据的过程中遇到分区未定义的值时，MyISAM引擎会将之前的行数据都插入，但之后的数据不会插入；InnoDB引擎将其视为一个事务，不会插入任何数据。

HASH分区的目的是将数据均匀分布到预定义的各个分区中（实际是否均匀分布取决于用户提供的哈希函数的质量），HASH分区不用像RANGE和LIST分区一样明确指定一个给定的列值或列值集合应该保存在哪个分区中，MySQL会自动完成这些工作，你所要做的就是指定一个列值或表达式（能返回一个整数即可）、分区数量。

创建HASH分区表：

CREATE TABLE t_hash (
        a INT,
        b DATETIME
    ) ENGINE = innodb
      PARTITION BY HASH(YEAR(b))
      PARTITIONS 4;

以上HASH分区表有4个分区，如果没有PARTITIONS子句，默认的分区数量是1。YEAR(b)可被替换为任意一个返回整数的表达式。

如我们插入以下数据：

INSERT INTO t_hash
    SELECT 1, '2010-04-01';

查看数据被放到了哪个分区中：

SELECT table_name, partition_name, table_rows
    FROM information_schema.partitions
    WHERE table_schema = database() AND table_name = 't_hash';

运行它：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 25]  
实际确定分区过程为`MOD(YEAR('2010-04-01'), 4) = 2`。按年选择分区并不能将数据均匀分到各个分区中，如果对于类似自增长的主键进行HASH分区，则可以很好地将数据平均分布。

MySQL还支持LINEAR HASH分区，它使用更复杂的算法确定新插入行的分区，以下是创建LINEAR HASH分区表的例子：

CREATE TABLE t_linear_hash (
        a INT,
        b DATETIME
    ) ENGINE = innodb
      PARTITION BY LINEAR HASH(YEAR(b))
      PARTITIONS 4;

此时数据库使用以下方法确定新插入行的分区：  
1.先计算`V = POWER(2, CEILING(LOG(2, partitionNum))) = 4`，其中CEILING函数返回比参数大的最小整数，LOG函数返回以2为底partitionNum的对数。  
2.所在分区为`N = YEAR('2010-04-01') & (V - 1) = 2`。

LINEAR HASH分区优点是增加、删除、合并、拆分分区更加快捷，有利于处理含大量数据的表，缺点在于比HASH分区得到的数据分布更不均衡。

KEY分区与HASH分区相似，但KEY分区不允许使用用户自定义的函数进行分区。KEY分区使用MySQL提供的函数进行分区，NDB Cluster引擎使用MD5函数分区，其他引擎使用MySQL内部的哈希函数分区。

创建KEY分区表：

CREATE TABLE t_key (
        a INT,
        b DATETIME
    ) ENGINE = innodb
      PARTITION BY KEY (b)
      PARTITIONS 4;

可在KEY分区中使用关键字LINEAR，作用和LINEAR HASH分区相同。

RANGE、LIST、HASH、KEY四种分区的分区条件必须是整型，如果不是整型，应通过函数将其转化为整型，如YEAR、TO\_DAYS、MONTH等函数。MySQL 5.5开始支持COLUMNS分区，可视为RANGE、LIST分区的一种进化，COLUMNS分区可直接使用非整型的数据进行分区，且RANGE COLUMNS分区可对多个列进行分区。

COLUMNS分区支持的数据类型：  
1.所有整型类型，包括INT、SMALLINT、TINYINT、BIGINT，而FLOAT、DECIMAL不支持。  
2.日期类型，包括DATE、DATETIME，其余日期类型不支持。  
3.字符串类型，包括CHAR、VARCHAR、BINARY、VARBINARY，不支持BLOB、TEXT类型。

创建LIST COLUMNS分区表：

CREATE TABLE customers_1 (
        first_name    VARCHAR(25),
        last_name     VARCHAR(25),
        street_1      VARCHAR(30),
        street_2      VARCHAR(30),
        city          VARCHAR(15),
        renewal       DATE
    ) 
    PARTITION BY LIST COLUMNS (city) (
    PARTITION pRegion_1 VALUES IN ('Oskarshamn', 'Hogsby', 'monsteras'),
    PARTITION pRegion_2 VALUES IN ('Vimmerby', 'Hultsfred', 'Vastervik')
    );

创建RANGE COLUMNS分区表，可对多个列进行分区：

CREATE TABLE rcx (
        a INT,
        b INT,
        c CHAR(3),
        d INT
    )
    PARTITION BY RANGE COLUMNS (a, b, c) (
    PARTITION p0 VALUES LESS THAN (5, 10, 'ggg'),
    PARTITION p1 VALUES LESS THAN (10, 20, 'mmmm')
    );

MySQL 5.5之前的RANGE和LIST分区可被RANGE COLUMNS和LIST COLUMNS分区进行很好地代替。

子分区是在分区基础上再分区，也称这种分区为复合分区。MySQL允许在RANGE和LIST分区上再进行HASH或KEY的子分区：

CREATE TABLE ts (
        a   INT,
        b   date
    ) ENGINE = innodb
      PARTITION BY RANGE (YEAR(b))
      SUBPARTITION BY HASH (TO_DAYS(b))
      SUBPARTITIONS 2 (    -- 每个分区中含2个子分区
          PARTITION p0 VALUES LESS THAN (1990),
          PARTITION p1 VALUES LESS THAN (2000),
          PARTITION p2 VALUES LESS THAN MAXVALUE
      );

以上表定义先根据b列进行了一次分三个区的RANGE分区，又对每个RANGE分区进行了一次分两个子分区的HASH分区，因此最终有6个分区。在创建子分区时可显式指出各个子分区的名称：

CREATE TABLE ts (
        a   INT,
        b   date
    ) ENGINE = innodb
      PARTITION BY RANGE (YEAR(b))
      SUBPARTITION BY HASH (TO_DAYS(b)) (
          PARTITION p0 VALUES LESS THAN (1990) (
              SUBPARTITION s0,
              SUBPARTITION s1
          ),
          PARTITION p1 VALUES LESS THAN (2000) (
              SUBPARTITION s2,
              SUBPARTITION s3
          ),
          PARTITION p2 VALUES LESS THAN MAXVALUE (
              SUBPARTITION s4,
              SUBPARTITION s5
          )
      );

建立子分区时，每个子分区的数量必须相同。如果一个分区表上使用`SUBPARTITION subpartitionName`显式定义子分区名，那么就必须这样定义所有子分区。每个子分区显式定义时的SUBPARTITION子句必须包含子分区名称。同一张表中所有子分区共享命名空间，不能重复命名子分区。

子分区可用于特别大的表，在多个磁盘间分别分配数据和索引，以下建表语句假设有四个磁盘，分别为/disk/0~/disk/3：

CREATE TABLE ts (
        a INT,
        b DATE
    ) ENGINE = myisam
      PARTITION BY RANGE(YEAR(b))
      SUBPARTITION BY HASH(TO_DAYS(b)) (
          PARTITION p0 VALUES LESS THAN (2000) (
              SUBPARTITION s0
              DATA DIRECTORY = '/disk0/data'
              INDEX DIRECTORY = '/disk0/idx',
              
              SUBPARTITION s1
              DATA DIRECTORY = '/disk1/data'
              INDEX DIRECTORY = '/disk1/idx'
          )
          
          PARTITION p1 VALUES LESS THAN (2010) (
              SUBPARTITION s2
              DATA DIRECTORY = '/disk2/data'
              INDEX DIRECTORY = '/disk2/idx',
              
              SUBPARTITION s3
              DATA DIRECTORY = '/disk3/data'
              INDEX DIRECTORY = '/disk3/idx'
          )
      );

MySQL允许对NULL值分区，且总是将NULL值视为小于任何一个非NULL值，这和ORDER BY排序时的顺序是一样的。

MySQL不同分区类型对于NULL值的处理不同，对于RANGE分区，MySQL会将该值当作小于任何非NULL的值选择分区，比如以下表：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 26]  
插入b为NULL的值时选择的分区为p0，因此，删除p0分区时，删除的是b小于10的记录和b为NULL的记录。

LIST分区时，要显式指出哪个分区存放NULL值，否则插入NULL值相当于插入不存在的分区值，从而报错：  
![在这里插入图片描述][20210131224951940.png]  
对于HASH和KEY分区，会将NULL值返回为0，不管分区函数是什么。

数据库应用分两类，OLTP（在线事务处理）和OLAP（在线分析处理）。对于OLAP应用，分区可以提高查询性能，因为OLAP应用需要频繁扫描一张很大的表，分区后扫描相应的分区即可。对于OLTP应用，大部分是通过索引返回几条记录，一般B+树需要2~3次磁盘IO即可返回记录，B+树可以很好地完成工作而不需要分区，且设计不好的分区会带来严重的性能问题。

很多人认为含有1000万行的表是一张非常巨大的表，因此要采用分区，如分10个区，每个区中100万条数据，但100万和1000万行数据构成的B+树层次都是一样的，可能都是两层，那么这样的分区不会带来性能的提高，即使1000万行的B+树高度为3，100万行的B+树高度为2，那么这样可以减少一次磁盘IO，但表中并不是只有分区的这一列，如果查询其他列，需要扫描所有10个分区，即使每个分区的查询开销减少到2次IO，也需要20次IO，而不分区时，每个索引列的IO都是2~3次，这样反而降低了性能。

即使以自增长主键进行HASH分区，也不能保证数据的均匀，因为插入的自增长ID并非总是连续的，如果该自增列的事务被回滚了，则该值不会再次使用。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70]: /images/20221119/1fa8eca8d7f044a5bf6f33ce0de10efa.png
[20210115150816169.png]: /images/20221119/3b52fe16cb704fd9b11aa1a13589fc71.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 1]: /images/20221119/ae2ab24682f040748fb435d6e84961bd.png
[20210115151356925.png]: /images/20221119/43d9ebe9814d43cb86c273ed4bc378e5.png
[20210116163017553.png]: /images/20221119/fa3ca1c5aa574fe3ba53c23e5136aed2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 2]: /images/20221119/fecbb2b9143740f4a09c744246d1bddd.png
[20210117191219121.png]: /images/20221119/e4ee228b32454055b816ae82ea29aa1d.png
[20210117203019553.png]: /images/20221119/27e1dbb67d25405f80fa4816f8857a11.png
[20210117203028148.png]: /images/20221119/26dad7a1527848e5bfcef3817e95f161.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 3]: /images/20221119/4997f14d1010497da7d128dc5366caac.png
[20210117205059923.png]: /images/20221119/94e7f6e485d9486fbd8fa21f2559f468.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 4]: /images/20221119/652a42455cf040f7874610bf9164307f.png
[20210118162322147.png]: /images/20221119/8f8f633dc69441c6af0f271ffa2da829.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 5]: /images/20221119/a4d1000feb534b689dcc96f9e903f277.png
[20210120151852230.png]: /images/20221119/16b0415c2de9452a98fe8b91fae55068.png
[20210119151240595.png]: /images/20221119/e8425ae9613648279aae1425dad50060.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 6]: /images/20221119/63d0cbd6e8354e899a56e7bb8ba06fdf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 7]: /images/20221119/e5a1d9ff34c94d459ce62b012b41596c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 8]: /images/20221119/e478d7d41f524be788f8e9d51fe7ef5e.png
[20210120155726752.png]: /images/20221119/37e2e24f996a484c8010992cdf13272e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 9]: /images/20221119/8471b3719ac543808462ed076b468b4e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 10]: /images/20221119/436cc18b17c94784a6cbd1b3bdce8ec1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 11]: /images/20221119/5207114013694c22b6e5503331ab89ca.png
[20210121130917121.png]: /images/20221119/6dff346141ed472da19fd6194e2ed616.png
[20210121130924404.png]: /images/20221119/932c2262adbf4d5c95102ba668208d39.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 12]: /images/20221119/25e442019f9b48699c4f5da1698b092e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 13]: /images/20221119/67a594f906e94c29b4e1c2a00cb34baa.png
[20210123150848481.png]: /images/20221119/9fb4d36fe9ca447caceae73e8d5c9bb3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 14]: /images/20221119/745e70934d7b4b059e6c42a617a1369c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 15]: /images/20221119/6d84ee4c58e74fabbc65569467901ba1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 16]: /images/20221119/ac4b8071757246d78d9a21ec288afc0f.png
[20210123153435329.png]: /images/20221119/c37226aaf97547d8ada9197853e5805e.png
[20210123153736489.png]: https://img-blog.csdnimg.cn/20210123153736489.png
[20210123155411318.png]: https://img-blog.csdnimg.cn/20210123155411318.png
[20210123160708342.png]: https://img-blog.csdnimg.cn/20210123160708342.png
[20210123160838690.png]: https://img-blog.csdnimg.cn/20210123160838690.png
[20210123162302224.png]: https://img-blog.csdnimg.cn/20210123162302224.png
[20210123162337550.png]: https://img-blog.csdnimg.cn/20210123162337550.png
[2021012316234286.png]: https://img-blog.csdnimg.cn/2021012316234286.png
[20210124133026410.png]: https://img-blog.csdnimg.cn/20210124133026410.png
[20210124151202311.png]: https://img-blog.csdnimg.cn/20210124151202311.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 17]: https://img-blog.csdnimg.cn/20210124152103217.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw,size_16,color_FFFFFF,t_70
[20210124152420944.png]: https://img-blog.csdnimg.cn/20210124152420944.png
[202101241540523.png]: https://img-blog.csdnimg.cn/202101241540523.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 18]: https://img-blog.csdnimg.cn/20210124160349514.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw,size_16,color_FFFFFF,t_70
[20210124160555156.png]: https://img-blog.csdnimg.cn/20210124160555156.png
[20210124160602263.png]: https://img-blog.csdnimg.cn/20210124160602263.png
[20210125082228237.png]: https://img-blog.csdnimg.cn/20210125082228237.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 19]: https://img-blog.csdnimg.cn/20210125082712819.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 20]: https://img-blog.csdnimg.cn/20210125082824612.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw,size_16,color_FFFFFF,t_70
[20210125082830417.png]: https://img-blog.csdnimg.cn/20210125082830417.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 21]: https://img-blog.csdnimg.cn/2021012508492174.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw,size_16,color_FFFFFF,t_70
[20210125084931337.png]: https://img-blog.csdnimg.cn/20210125084931337.png
[2021012508542580.png]: https://img-blog.csdnimg.cn/2021012508542580.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 22]: https://img-blog.csdnimg.cn/20210125090057172.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw,size_16,color_FFFFFF,t_70
[20210125183259771.png]: https://img-blog.csdnimg.cn/20210125183259771.png
[20210125183351387.png]: https://img-blog.csdnimg.cn/20210125183351387.png
[20210126180254903.png]: https://img-blog.csdnimg.cn/20210126180254903.png
[20210126180351417.png]: https://img-blog.csdnimg.cn/20210126180351417.png
[20210126180713776.png]: https://img-blog.csdnimg.cn/20210126180713776.png
[20210126180834157.png]: https://img-blog.csdnimg.cn/20210126180834157.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 23]: https://img-blog.csdnimg.cn/20210126181728528.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw,size_16,color_FFFFFF,t_70
[20210127184211604.png]: https://img-blog.csdnimg.cn/20210127184211604.png
[20210128181817907.png]: https://img-blog.csdnimg.cn/20210128181817907.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 24]: https://img-blog.csdnimg.cn/20210128181305703.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw,size_16,color_FFFFFF,t_70
[20210128183522328.png]: https://img-blog.csdnimg.cn/20210128183522328.png
[20210129174824287.png]: https://img-blog.csdnimg.cn/20210129174824287.png
[20210129175710978.png]: https://img-blog.csdnimg.cn/20210129175710978.png
[20210129180308600.png]: https://img-blog.csdnimg.cn/20210129180308600.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 25]: https://img-blog.csdnimg.cn/2021013015032251.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 26]: https://img-blog.csdnimg.cn/20210131224742523.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw,size_16,color_FFFFFF,t_70
[20210131224951940.png]: https://img-blog.csdnimg.cn/20210131224951940.png