MySQL: InnoDB (2) – Exocoetus

内容隐藏

1 文件

1.6.3 REDO LOG 和 BIN LOG 区别

2.3.2 Redundant行记录格式（略）

2.3.3 行溢出数据

2.3.4 Compressed和Dynamic行记录格式

2.4.3 最大最小记录（Infimum, Supremum）

2.4.4 用户记录（User Record）

2.4.5 空闲空间（Free Space）

2.4.6 Page Directory

2.4.7 File Trailer（“首尾呼应”）

2.5 Named File Formats 机制

文件

参数文件：告诉 MySQL 实例启动时在哪里可以找到数据库文件，并指定初始化参数
日志文件：记录 MySQL 实例对某种条件做出响应时写入的文件
Socket 文件：用 UNIX 域套接字方式进行连接时需要的文件
pid 文件：MySQL 实例的进程ID文件
MySQL 表结构文件：用来存放 MySQL 表结构定义文件
存储引擎文件：每个引擎都有自己的存储文件来保存数据

参数文件

MySQL 实例启动时，数据库读取配置参数文件，用来寻找数据库的各种文件所在位置以及指定某些初始化参数。默认情况下会按照一定的顺序在指定位置进行读取。

参数

可以把数据库参数看成一个键值对。

MySQL 数据库中的参数可以分为两类：

动态参数
静态参数

动态参数可以在实例运行中进行修改。（通过 SET 命令）

静态在整个生命周期都不允许修改。

global 整个生命周期中生效
session 在会话中修改生效

SET @@global.system_var_name=expr

SET @@session.system_var_name=expr

日志文件

日志文件是给人看的：）

帮助 DBA 对 MySQL 数据库的运行状态诊断，帮助进行数据库优化。

错误日志：错误、警告信息
二进制日志：记录对 MySQL 数据库执行更改的所有操作｛路径：datadir｝
- 数据恢复
- 复制（主从同步）
- 审计（判断 SQL 注入攻击）
慢查询日志：帮助定位可能存在问题的 SQL 语句（默认不启动）
查询日志：记录所有对 MySQL 数据库请求的信息｛文件名：主机.log｝

# 错误日志路径
SHOW VARIABLES LIKE 'log_error'\G;

# 显示 binlog（示例）
SHOW BINLOG EVENTS IN 'mysqld.000008'\G;

# 查询 binlog 所在路径
SHOW VARIABLES LIKE 'datadir'

# 单个二进制日志的最大值
max_binlog_size

# 二进制日志缓冲区大小(基于 session)
binlog_cache_size

# 每写入缓冲多少次就同步到磁盘（1 表示同步写磁盘）
sync_binlog

# 设置为 1 时确保二进制日志和InnoDB存储引擎数据文件的同步
innodb_support_xa

# 使用缓冲写二进制日志的次数
binlog_cache_use

# 使用临时文件写二进制日志的次数
binlog_cache_disk_use

# 需要写入哪些库的日志
binlog-do-db

# 需要忽略写入那些库的日志
binlog-ignore-db

# slave 是否把 master 的二进制日志写入自己的二进制日志文件
log-slave-update

# 记录二进制日志的格式(版本 5.1 后)
# 动态参数
# STATEMENT 记录日志的逻辑 SQL 语句
# ROW 记录表的行更改情况（可以将事务隔离从RR -> RC，提高并发）
# MIXED 默认采用 SQL 语句，以下情况采取 ROW：
#     1. 引擎为 NDB ，DML 操作都会采取 ROW
#     2. 使用 UUID(), USER(), CURRENT_USER(), FOUND_ROWS(), ROW_COUNT()等不确定函数
#     3. 使用 INSERT DELAY 语句
#     4. 使用用户定义函数（类似不确定）
#     5. 使用临时表

binlog_format
SET SESSION binlog_format = 'ROW';（ROW 会带来容量的增加）

# 慢查询日志阈值
SHOW VARIABLES LIKE 'long_query_time'\G;

# 是否开启慢查询
SHOW VARIABLES LIKE 'log_slow_queries'\G;

# 是否记录未使用索引的 SQL 语句
SHOW VARIABLES LIKE 'log_queries_not_using_indexes'\G;

# 每分钟允许记录到慢查询日志且未使用索引的 SQL 的次数（版本 5.6.5 后）
'log_throttle_queries_not_using_indexes'

# 将慢查询日志记录放入表中 (版本 5.1 后)
SHOW CREATE TABLE mysql.slow_log\G;

# 查看慢查询日志输出格式(动态全局)，默认 FILE，设为 TABLE 后可查询 slow_log 表
SHOW VARIABLES LIKE 'log_output'\G;

# 查看 slow_log 表
SELECT * FROM mysql.slow_log\G;

# InnoDB: 将超过指定IO次数的 SQL 语句记录到slow_log中
'log_query_io'

# InnoDB: 启动 slow log 的方式
# 0 不记录 SQL 记录到 slow log
# 1 根据运行时记录 SQL 到 slow log
# 2 根据逻辑 IO 次数记录 SQL 到 slow log
# 3 根据运行时间和 逻辑 IO 次数记录 SQL 到 slow log
'slow_query_type'

套接字文件

UNIX 本地连接 MySQL 可以采用 UNIX 套接字。

套接字文件一般在 /tmp 目录下，名称为 mysql.sock 。

# 路径查询
SHOW VARIABLES LIKE 'socket'\G;

pid文件

MySQL 实例启动时，会将自己的进程ID写入一个文件中，该文件为 pid文件。

该文件可由 pid_file 控制，默认位于数据库目录下，文件名为：主机名.pid

# 路径查询
SHOW VARIABLES LIKE 'pid_file'\G;

表结构定义文件

MySQL 有一个 frm 为后缀名的文件，这个文件记录了该表的表结构定义。

frm 还用来存放视图的定义，如用户创建了一个 v_a 视图，那么对应地会产生一个 v_a.frm 文件，用来记录视图的定义，该文件是文本文件，可以直接使用 cat 命令进行查看。

#cat v_a.frm

InnoDB存储引擎文件

表空间文件

InnoDB采用将存储的数据按表空间（tablespace）进行存放的设计。在默认配置下会有一个初始大小为10MB，名为 ibdata1 的文件。该文件就是默认的表空间文件（tablespace file）。

# 基于InnoDB存储引擎的表的数据都会记录到共享表空间中
innodb_data_file_path

# 将每个基于InnoDB存储引擎的表产生一个独立表空间
# 单独的表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息
# 其余信息还是存放在默认的表空间中
innodb_file_per_table

重做日志文件

用于实例或介质失败后的恢复。

# 指定每个重做日志文件的大小
innodb_log_file_size

# 指定了日志文件组中重做日志文件的数量
innodb_log_files_in_group

# 指定了日志镜像文件组的数量
innodb_mirrored_log_groups

# 指定了日志文件组所在路径
innodb_log_group_home_dir

重做日志文件不能设置得太大，如果设置得很大，在恢复时可能需要很长的时间；另一方面又不能设置得太小了，否则可能导致一个事务的日志需要多次切换重做日志文件。重做日志文件太小会导致频繁地发生 async -checkpoint，导致性能的抖动。

REDO LOG 和 BIN LOG 区别

二进制日志会记录所有与 MySQL 数据库有关的日志记录，包括 InnoDB、MyISAM、Heap 等其他存储引擎的日志。而 InnoDB 存储引擎的重做日志只记录有关该存储引擎本身的事务日志。

记录的内容不同，无论用户将二进制日志文件记录的格式设为 STATEMENT 还是 ROW，又或者是 MIXED，其记录的都是关于一个事务的具体操作内容，即该日志是逻辑日志。而 InnoDB 存储引擎的重做日志文件记录的是关于每个页（Page）的更改的物理情况。

写入的时间也不同，二进制日志文件仅在事务提交前进行提交，即只写磁盘一次，不论这时该事务多大。而在事务进行的过程中，却不断有重做日志条目（redo entry）被写入到重做日志文件中。

redo_log_type

space

page_no

redo_log_body

重做日志条目结构

redo_log_type：占用 1 字节，重做日志类型
space：表空间 ID，采用压缩方式，可能小于 4 字节
page_no：页的偏移量，采用压缩方式
redo_log_body：重做日志的数据部分，恢复时需要调用函数解析

重做日志并不是直接写，而是先写入重做日志缓冲，再写入日志文件（内部循环写）。

从缓冲写入磁盘时，是按512个字节，也就是一个扇区的大小进行写入。扇区是写入的最小单位，可以保证写入必定是成功的，因此在重做日志的写入过程中不需要有 doublewrite。

主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件中，不论事务是否已经提交。另一个触发写磁盘的过程是由参数 innodb_flush_log_at_trx_commit 控制，表示在提交（commit）操作时，处理重做日志的方式。

innodb_flush_log_at_trx_commit 有效值：

0：提交事务时，不将事务的重做日志写入磁盘上的日志文件
1：在执行 commit 时将重做日志缓冲同步写到磁盘，即伴有fsync的调用
2：将重做日志异步写到磁盘，即写到文件系统的缓存中，不能完全保证在执行 commit 时必定写入重做日志文件

为了保证事务的持久性，必须将 innodb_flush_log_at_trx_commit 置为 1。

设置为2时，当 MySQL 数据库发生宕机而操作系统及服务器并没有发生宕机时，由于此时未写入磁盘的事务日志保存在文件系统缓存中，当恢复时同样能保证数据不丢失。

表

索引组织表

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

使用表中的非空唯一索引（有多个时选择第一个定义的索引）
如果没有，InnoDB 存储引擎自动创建一个6字节大小的指针。

InnoDB逻辑存储结构

所有数据都被逻辑地存放在一个空间中，称之为表空间 Tablespace：

段 Segment
区 Extent
页 Page（或称 Block）

表空间

InnoDB 存储引擎逻辑结构的最高层。

在默认情况下 InnoDB 存储引擎有一个共享表空间 ibdata1，即所有数据都存放在这个表空间内。

如果用户启用了参数 innodb_file_per_table，则每张表内的数据可以单独放到一个表空间内，需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲 Bitmap页，其他如回滚（undo）信息，插入缓冲索引页、系统事务信息，二次写缓冲（Double write buffer）等还是存放在原来的共享表空间内。

段

常见的段有数据段、索引段、回滚段等。InnoDB存储引擎表是索引组织的，因此数据即索引，索引即数据。那么数据段即为B+树的叶子节点（Leaf node segment），索引段即为B+树的非索引节点（Non-leaf node segment）。

在InnoDB存储引擎中，对段的管理都是由引擎自身所完成，DBA不能也没有必要对其进行控制。

区

区是由连续页组成的空间，在任何情况下每个区的大小都为 1MB。为了保证区中页的连续性，InnoDB 存储引擎一次从磁盘申请 4～5 个区。在默认情况下，InnoDB 存储引擎页的大小为 16KB，即一个区中一共有 64 个连续的页。

InnoDB 1.0.x版本开始引入压缩页，即每个页的大小可以通过参数 KEY_BLOCK_SIZE 设置为2K、4K、8K，因此每个区对应页的数量就应该为512、256、128。

在每个段开始时，先用32个页大小的碎片页（fragment page）来存放数据，在使用完这些页之后才是连续页的申请。对于一些小表，或者是 undo 这类的段，可以在开始时申请较少的空间，节省磁盘容量的开销。

页

页是InnoDB磁盘管理的最小单位。在InnoDB存储引擎中，默认每个页的大小为16KB。

从InnoDB 1.2.x版本开始，可以通过参数 innodb_page_size 将页的大小设置为4K、8K、16K。若设置完成，则所有表中页的大小都为 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）
压缩的二进制大对象页（compressed BLOB Page）

行

InnoDB存储引擎是面向行的（row-oriented），也就说数据是按行进行存放的。

每个页存放的行记录是有硬性定义的，最多允许存放16KB/2-200行的记录，即7992行记录。

InnoDB行记录格式

MySQL 5.1版本中，默认设置为Compact行格式。

# 查看当前表使用的行格式，其中row_format属性表示当前所使用的行记录结构类型
SHOW TABLE STATUS LIKE 'table_name'\G;

Compact行记录格式

变长字段长度列表

NULL标志位

记录头信息

列1数据

列2数据

……

变长字段长度列表：非NULL、按照列的顺序逆序放置、列长度<255字节？1字节 : 2字节

NULL标志位：长度一般为 1 字节，指示该行数据是否有 NULL 值。（记录那些列为 NULL，因而存储时 NULL 不占用空间）

同样逆序排列，但是 1 字节只有 8 bit，如果超过了 8 个列为 NULL 该怎么办呢？

InnoDB物理行中null值的存储的推断与验证

答案：以 8 为单位，满 8 个 NULL 字段就多 1 个字节，高位用 0 补齐。

记录头信息：固定 5 字节（40 bit）

名称	大小（bit）	描述
()	1	未知
()	1	未知
deleted_flag	1	该行是否已被删除
min_rec_flag	1	为1，如果该记录是预先被定义为最小的记录
n_owned	4	该记录拥有的记录数
heap_no	13	索引堆中该条记录的排序记录
record_type	3	记录类型，000表示普通，001表示B+树节点指针 010表示 Infimum，011表示 Supremum 1xx表示保留
next_record	16	页中下一条记录的相对位置

之后是存储的各个列的数据。NULL 除了占有标志位，实际存储不占用空间。

每行数据除了用户定义的列外，还有两个隐藏列，事务ID列和回滚指针列，分别为6字节和7字节的大小。

若 InnoDB表没有定义主键，每行还会增加一个6字节的 rowid 列。

Redundant行记录格式（略）

Redundant是MySQL 5.0版本之前 InnoDB 的行记录存储方式，这里略过了哈。

行溢出数据

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

因为存在其他开销，所以创建 VARCHAR 长度为 65535 的表会报错。MySQL官方手册中定义的 65535 长度是指所有VARCHAR 列的长度总和，如果列的长度总和超出这个长度，依然无法创建。

即使能存放 65532 个字节，但 InnoDB存储引擎的页为 16KB，即 16384 字节，怎么存放 65532 字节呢？

一般情况下，InnoDB存储引擎的数据都是存放在页类型为 B-tree Node 中。但是当发生行溢出时，数据存放在页类型为Uncompress BLOB 页中。

存储过长的数据时，数据页面其实只保存了 VARCHAR 的一部分前缀（prefix）数据，之后是偏移量，指向行溢出页，也就是前面用户看到的 Uncompressed BLOB Page。

InnoDB 存储引擎表是索引组织的，即 B+Tree 的结构，这样每个页中至少应该有两条行记录。如果页中只能存放下一条记录，那么 InnoDB存储引擎会自动将行数据存放到溢出页中。

如果可以在一个页中至少放入两行数据，那 VARCHAR 类型的行数据就不会存放到 BLOB 页中去。

对于 TEXT 或 BLOB 的数据类型，用户总是以为它们是存放在 Uncompressed BLOB Page 中的，其实这也是不准确的。是放在数据页中还是 BLOB 页中，和前面讨论的 VARCHAR 一样，至少保证一个页能存放两条记录。

Compressed和Dynamic行记录格式

InnoDB 1.0.x版本开始引入了新的文件格式——Barracuda。Barracuda文件格式下拥有两种新的行记录格式：Compressed和Dynamic。

新的两种记录格式对于存放在BLOB中的数据采用了完全的行溢出的方式，存储在 Compressed 行记录格式中的行数据会以 zlib 的算法进行压缩，因此对于 BLOB、TEXT、VARCHAR 这类大长度类型的数据能够进行非常有效的存储。

CHAR的行结构存储

从MySQL 4.1版本开始，CHAR（N）中的N指的是字符的长度，而不是之前版本的字节长度。也就说在不同的字符集下，CHAR 类型列内部存储的可能不是定长的数据。

对于多字节的字符编码，CHAR 类型不再代表固定长度的字符串了。例如，对于 UTF-8 下 CHAR（10）类型的列，其最小可以存储 10 字节的字符，而最大可以存储 30 字节的字符。InnoDB 存储引擎在内部将其视为变长字符类型，这也就意味着在变长长度列表中会记录 CHAR 数据类型的长度。

InnoDB数据页结构

InnoDB 数据页解析本章基本参考此文。

File Header（文件头）[38字节]
Page Header（页头）[56字节]
Infimun 和 Supremum Records
User Records（用户记录，即行记录）
Free Space（空闲空间）
Page Directory（页目录）
File Trailer（文件结尾信息）[8字节]

File Header

名称	大小（字节）	说明
FIL_PAGE_SPACE_OR_CHKSUM	4	主要用来存储数据页的 checksum
FIL_PAGE_OFFSET	4	对应数据页的 page number，每个表空间从0开始，即这个值乘以数据页的大小就可以得到数据页在文件中的起始偏移量
FIL_PAGE_PREV	4	指向前一个数据页
FIL_PAGE_NEXT	4	指向后一个数据页（注意，这里的前后是指按照用户记录排序的先后顺序，也是逻辑顺序）
FIL_PAGE_LSN	8	当前数据页最新被修改的lsn。 InnoDB redolog 幂等的特性就依赖此字段。在奔溃恢复应用日志阶段，如果发现 redolog 的 lsn 小于等于这个值，就不需要再次应用 redolog 了
FIL_PAGE_TYPE	2	当前页面是哪种类型的数据页，见下表
FIL_PAGE_FILE_FLUSH_LSN	8	仅在表空间的第一个页中定义，代表文件至少被更新到了该 lsn 值（记录ibdata成功刷到磁盘的 lsn）
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID	4	表空间 ID

名称	十六进制	解释
FIL_PAGE_INDEX	0x45BF	B+树节点
FIL_PAGE_UNDO_LOG	0x0002	Undo Log 页
FIL_PAGE_INODE	0x0003	索引节点
FIL_PAGE_IBUF_FREE_LIST	0x0004	Insert Buffer 空闲列表
FIL_PAGE_TYPE_ALLOCATED	0x0000	该页为最新分配
FIL_PAGE_IBUF_BITMAP	0x0005	Insert Buffer 位图
FIL_PAGE_TYPE_SYS	0x0006	系统页
FIL_PAGE_TYPE_TRX_SYS	0x0007	事务系统数据
FIL_PAGE_FSP_HDR	0x0008	File Space Header
FIL_PAGE_TYPE_XDES	0x0009	扩展描述页
FIL_PAGE_TYPE_BLOB	0x000A	BLOB 页

Page Header

名称	大小（字节）	说明
PAGE_N_DIR_SLOTS	2	表示数据页中数据目录的个数。一个新建的空数据页，就有2个目录，分别指向最大记录和最小记录。一个非空的数据页中，第一个目录指向最小记录，最后一个目录指向最大记录。当增加目录的时候，会递增这个值。
PAGE_HEAP_TOP	2	指向数据页中的空闲空间的起始地址。大于这个地址的且小于数据目录的空间都是未分配的，可以被后续使用。但是由于空闲记录链表(PAGE_FREE)的存在，小于这个地址的也可能被重用。
PAGE_N_HEAP	2	目前已经被使用空间中的记录数量。包括正常的记录和已经被删除(放入 PAGE_FREE 中)的记录。在创建新的空页时候，默认被置为2，即最大和最小记录。最高位被用来标记这个数据页是否存了新格式的记录。
PAGE_FREE	2	删除记录的链表，记录被删除，会放到这个链表头上。如果这个页上有记录要插入，可以先从这里分配空间。，如果空间不够，才从空闲地址(PAGE_HEAP_TOP)分配。
PAGE_GARBAGE	2	已删除记录的字节数，行记录结构中delete flag为1的记录的大小的总数。
PAGE_LAST_INSERT	2	指向最近一个被插入的记录，主要用来加速后续插入操作。
PAGE_DIRECTION	2	最后一个记录插入的方向，目前就两个方向，从左边插入和从右边插入。
PAGE_N_DIRECTION	2	同一个方向连续插入的记录数。
PAGE_N_RECS	2	该页中记录的数量，不包括最大和最小记录。与`PAGE_N_HEAP`不同，如果记录被标记为delete-marked，这个值就会递减。
PAGE_MAX_TRX_ID	8	修改此数据页的当前最大事务ID。
PAGE_LEVEL	2	当前页在索引树中的位置，0x00代表叶节点（第 0 层）。
PAGE_INDEX_ID	2	索引页的索引ID。
PAGE_BTR_SEG_LEAF	10	B+树数据页叶节点所在段的 segment header。
PAGE_BTR_SEG_TOP	10	B+树数据页非叶节点所在段的 segment header。

最大最小记录（Infimum, Supremum）

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

Infimum 记录是比该页中任何主键值都要小的值，Supremum 指比任何可能大的值还要大的值。

这两个值在页创建时被建立，并且在任何情况下不会被删除。（便于页内操作）

用户记录（User Record）

用户所有插入的记录都存放在这里，默认情况下记录跟记录之间没有间隙，但是如果重用了已删除记录的空间，就会导致空间碎片。每个记录都有指向下一个记录的指针，但是没有指向上一个记录的指针。

记录按照主键顺序排序，即，用户可以从数据页最小记录开始遍历，直到最大的记录，这包括了所有正常的记录和所有被 delete-marked 记录，但是不会访问到被删除的记录（PAGE_FREE）。

空闲空间（Free Space）

从 PAGE_HEAP_TOP 开始，到最后一个数据目录，这之间的空间就是空闲空间，都被重置为0，当用户需要插入记录时候，首先在被删除的记录的空间中查找，如果没有找到合适的空间，就从这里分配。

空间分配给记录后，需要递增 PAGE_IN_RECS 和 PAGE_N_HEAP。

Page Directory

用户的记录是从低地址向高地址扩展，而数据目录则相反。在数据页被初始化的时候，就会数据页最后（当然在 checksum 之前）创建两个数据目录，分别指向最大和最小记录。之后插入新的数据的时候，需要维护这个目录。

数据目录（或页目录）中存放了记录的相对位置。有些时候这些记录指针称为 Slots 或 Directory Slots。与其他数据库系统不同的是，在 InnoDB 中并不是每个记录拥有一个槽，InnoDB存储引擎的槽是一个稀疏目录（sparse directory），即一个槽中可能包含多个记录。

举个例子：

目前有一组记录，包括 2 条虚拟记录（infimum 和 supremum），但不包括放入 Fage Free 的记录。

将这些记录分成几个组（按索引键值顺序存放），最后一条记录（最大的那条）的头信息中的 n_owned 属性表示该组内共有几条记录。

每个组的最后一条记录在页中的偏移量（该记录的真实数据与页面中第 0 个字节之间的距离）被单独提取出来，顺序存储在数据目录。

因此B+树索引本身并不能找到具体的一条记录，能找到只是该记录所在的页。数据库把页载入到内存，然后通过 Page Directory 进行二分查找确定该记录所在分组的槽，然后遍历槽（next_record，遍历链表）去找出对应记录。

File Trailer（“首尾呼应”）

为了检测页是否已经完整地写入磁盘（如可能发生的写入过程中磁盘损坏、机器关机等），InnoDB存储引擎的页中设置了File Trailer部分。

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 值进行比较，看是否一致（checksum 的比较需要通过 InnoDB 的 checksum 函数来进行比较，不是简单的等值比较），以此来保证页的完整性（not corrupted）。

Named File Formats 机制

用于解决不同版本下页结构兼容性的问题。

约束

数据完整性

约束提供了一条强大而简易的途径来保证数据库中数据的完整性。

数据完整性可以通过三种方式进行保障：

主键
唯一索引
触发器

约束的创建和查找

约束的创建可以采用以下两种方式：

表建立时就进行约束定义
利用 ALTER TABLE 命令来进行创建约束

对 Unique Key（唯一索引）的约束，可以通过命令 CREATE UNIQUE INDEX 来建立。

对于主键约束而言，其默认约束名为 PRIMARY，而对于 Unique Key 约束而言，默认约束名和列名一样。

Foreign Key 约束会有一个神秘的默认名称。

约束和索引的区别

当用户创建了一个唯一索引就创建了一个唯一的约束。

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

对错误数据的约束

在某些默认设置下，MySQL 允许非法的或不正确的数据的插入或更新，又或者可以在数据库内部将其转化为一个合法的值，如向 NOT NULL 的字段插入一个 NULL 值，MySQL数据库会将其更改为 0 再进行插入，因此数据库本身没有对数据的正确性进行约束。

可以设置参数 sql_model 为 STRICT_TRANS_TABLES，对输入值的合法性进行约束。

ENUM 和 SET 约束

MySQL 数据库不支持传统的 CHECK 约束，但是通过 ENUM 和 SET 类型可以解决部分这样的约束需求。

对于传统 CHECK 约束支持的连续值的范围约束或更复杂的约束，ENUM 和 SET 类型还是无能为力，需要通过触发器来实现。

触发器与约束

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

最多可以为一个表建立 6 个触发器，即分别为 INSERT、UPDATE、DELETE 的 BEFORE 和 AFTER 各定义一个。BEFORE 和 AFTER 代表触发器发生的时间，表示是在每行操作的之前发生还是之后发生。

外键约束（不怎么用）

被引用的表为父表，引用的表称为子表。

CASCADE：父表发生 DELETE 或 UPDATE 操作时，对相应的子表中的数据也进行相同操作
SET NULL：子表中的数据被更新为 NULL 值（必须允许为 NULL）
NO ACTION：抛出错误，不允许这类操作发生
RESTRICT：抛出错误，不允许这类操作发生（没错，你没有看错，两个效果一模一样）

在其他数据库中，如 Oracle 数据库，有一种称为延时检查（deferred check）的外键约束，即检查在 SQL 语句运行完成后再进行。而目前 MySQL 数据库的外键约束都是即时检查（immediate check），因此从上面的定义可以看出，在 MySQL 数据库中 NO ACTION 和 RESTRICT 的功能是相同的。

视图、物化视图

就那样，略。

分区表

分区概述

MySQL数据库在5.1版本时添加了对分区的支持。分区的过程是将一个表或索引分解为多个更小、更可管理的部分。从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。

MySQL 数据库支持的分区类型为水平分区，不支持垂直分区。

MySQL 数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。

当前MySQL数据库支持以下几种类型的分区：

RANGE 分区：行数据基于属于一个给定连续区间的列值被放入分区
LIST 分区：和 RANGE 分区类型，只是 LIST 分区面向的是离散的值
HASH 分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数
KEY 分区：根据 MySQL 数据库提供的哈希函数来进行分区（与 HASH 分区类似）

不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。如果建表时没有指定主键，唯一索引，可以指定任何一个列为分区列。

RANGE 分区

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

例如：启用分区后，创建一个 id 列的区间分区表。当 id 小于 10 时，数据插入 p0 分区，大于 10 小于 20 时，插入 p2 分区。这时，表不再由一个 ibd 文件组成了，而是由建立分区时的各个分区 ibd 文件组成，如 t#P#p0.ibd，t#P#p1.ibd。

因为表 t 根据列 id 进行分区，所以数据是根据列 id 的值的范围存放在不同的物理文件中的。

LIST 分区

LIST 分区和 RANGE 分区非常相似，只是分区列的值是离散的。

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));

HASH 分区

HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据数量大致都是一样的。

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

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

COLUMNS 分区

MySQL 5.5 版本开始支持 COLUMNS 分区，可视为 RANGE 分区和 LIST 分区的一种进化。

COLUMNS 分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转化为整型。此外，RANGE COLUMNS 分区可以对多个列的值进行分区。

COLUMNS 分区支持以下的数据类型：

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

对于日期类型的分区，我们不再需要 YEAR() 和 TO_DAYS() 函数了，而直接可以使用 COLUMNS。

子分区

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

这部分目前仅了解，目前分库分表比较常用。

分区中的 NULL

MySQL数据库允许对NULL值做分区，但是处理的方法与其他数据库可能完全不同。MYSQL 数据库的分区总是视 NULL 值视小于任何的一个非 NULL 值，这和 MySQL 数据库中处理 NULL 值的 ORDER BY 操作是一样的。因此对于不同的分区类型，MySQL 数据库对于 NULL 值的处理也是各不相同。

对于 RANGE 分区，如果向分区列插入了 NULL 值，则 MySQL 数据库会将该值放入最左边的分区
在 LIST 分区下要使用 NULL 值，则必须显式地指出哪个分区中放入 NULL 值，否则会报错
HASH 分区的任何分区函数都会将含有 NULL 值的记录返回为0

在表和分区间交换数据

MySQL 5.6开始支持 ALTER TABLE … EXCHANGE PARTITION 语法。

该语句允许分区或子分区中的数据与另一个非分区的表中的数据进行交换。如果非分区表中的数据为空，那么相当于将分区中的数据移动到非分区表中。若分区表中的数据为空，则相当于将外部表中的数据导入到分区中。

使用条件：

要交换的表需和分区表有着相同的表结构，但是表不能含有分区
在表中的数据必须在交换的分区定义内
被交换的表中不能含有外键，或者其他的表含有对该表的外键引用
用户除了需要 ALTER、INSERT 和 CREATE 权限外，还需要 DROP 的权限

细节：

使用该语句时，不会触发交换表和被交换表上的触发器
AUTO_INCREMENT 列将被重置