rikako-note/mysql/mysql文档/mysql_表.md

• 表
  • 索引组织表
  • innodb逻辑存储结构
    • 表空间
      • innodb_file_per_table
    • 段（segment）
    • 区（Extent）
    • 页（Page）
    • 行
  • innodb行记录格式
    • Compact
      • 变长字段长度列表
      • NULL标志位
      • 记录头信息
    • 行溢出数据
    • dynamic
    • char存储结构
  • innodb数据页结构
    • File Header
    • Infimum和Supremum record
    • user record 和 free space
    • page directory
      • B+树索引
    • File Trailer
      • 完整性校验
  • 分区表
    • partition keys & primary keys & unique keys
      • 表中不存在唯一索引
      • 后续向分区表添加唯一索引
      • 对非分区表进行分区
    • 分区类型
      • RANGE
        • information_schema.partitions
        • 看select语句查询了哪些分区
        • 插入超过分区范围的数据
        • 向分区表中添加分区
        • 向分区表中删除分区
      • LIST
      • HASH
        • 新增HASH分区
        • 减少HASH分区
      • LINEAR HASH
      • KEY & LINEAR KEY
      • COLUMNS
        • range columns
        • list columns
    • 子分区
    • 分区中的NULL值
    • 分区和性能
      • 不分区
      • 按id进行分区
    • 在表和分区之间交换数据

表

索引组织表

innodb存储引擎中，表都是根据主键顺序组织存放的，这种存储方式被称为索引组织表（index organized table）。在innodb存储引擎表中，每张表都有主键（primary key），如果在创建表时没有显式指定主键，那么innodb会按照如下方式创建主键：

• 首先判断表中是否存在非空的唯一索引（unique not null）字段，如果有，则其为主键
• 如果不存在非空唯一索引，那么innodb会自动创建一个6字节大小的指针作为主键

如果有多个非空唯一索引，innodb存储引擎将会选择第一个定义的非空唯一索引作为主键。

innodb逻辑存储结构

在innodb的存储逻辑结构中，所有的数据都被逻辑存放在表空间（table space）中。表空间则由段（segement），区（extent），页（page）组成。

组成如图所示：

表空间

表空间为innodb存储引擎逻辑结构的最高层，所有数据都存放于表空间中。innodb存在一个默认的共享表空间ibdata1，在开启innodb_file_per_table参数后，每张表内的数据可以单独存放到一个表空间。

innodb_file_per_table

innodb_file_per_table参数启用会导致每张表的数据、索引、插入缓冲bitmap页存放到单独的文件中；但是其他数据，例如回滚（undo）信息，插入缓冲页，系统事务信息，double write buffer等还是存放在默认的共享表空间中。

段（segment）

如上图所示，表空间是由段（segment）所组成的，常见的段分为数据段，索引段，回滚段等。

在innodb存储引擎中，数据即索引，索引即数据。数据段即为B+树的叶子节点(Leaf node segment),索引段即为B+树的非叶子节点(Non-leaf node segment)。

区（Extent）

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

在新建表时，新建表的大小为96KB，小于一个Extent的大小1MB，因为每个段Segmenet开始时，都会有至多32个页大小的碎片页，等使用完这些页后才会申请64个连续页作为Extent。

都与一些小表或是undo这样的段，可以在开始时申请较少的空间，节省磁盘容量开销。

页（Page）

innodb存储引擎中页的大小默认为16KB，默认的页大小可以通过innodb_page_size参数进行修改。通过该参数，可以将innodb的默认页大小设置为4K，8K。

页是innodb磁盘管理的最小单位，在innodb中，常见的页有:

• 数据页（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存储引擎是面向行的，数据按行进行存放。每个页中至多可以存放16KB/2 - 200行的记录，即7992行记录。

innodb行记录格式

innodb存储引擎以行的形式进行存储，可以通过show table status like '{table_name}'的语句来查询表的行格式，示例如下：

show table status like 'demo_t1'

Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
demo_t1	InnoDB	10	Dynamic	0	0	16384	0	32768	0	1	2025-01-30 15:18:57	null	null	utf8mb4_0900_ai_ci	null

上述示例中表的row_format为dynamic。

Compact

在使用Compact行记录格式时，一个页中存放数据越多，其性能越高。

Compact格式下行记录的存储格式如下：

变长字段长度列表

NULL标志位

记录头信息

列1数据

列2数据

......

变长字段长度列表

Compact行记录格式其首部是一个非Null变长字段长度列表，其按照列的顺序逆序放置，变长列的长度为：

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

变长字段的长度不能小于2字节，因为varchar类型最长长度限制为65535。

NULL标志位

NULL标志位为bitmap，代表每一列是否为空。如果行中存在n个字段可为空，那么NULL标志位部分的长度为ceiling(n/8)。

记录头信息

record header，固定占用5字节，其中，record header的各bit含义如下所示：

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

除了上述3个部分之外，其他部分就是各个列的实际值。

在Compact格式中，NULL除了占有NULL标志位外，不占用任何实际空间。

每行数据中，除了有用户自定义的列外，还存在两个隐藏列，即事务id列和回滚指针列，长度分别为6字节和7字节。

如果innodb表没有自定义主键，每行还会增加一个rowid列。

行溢出数据

innodb存储引擎可能将一条记录中某些数据存储在真正的存储数据页面之外。一般来说，blob、lob这类大对象的存储位于数据页面之外。

当行数据的大小特别大，导致一个页无法存放2条行数据时，innodb会自动将占用空间大的blob字段或varchar字段值放到额外的uncompressed blob page中。

dynamic

dynamic格式几乎和compact格式相同，但是对于每个blob字段，其存储只消耗20字节用于存储指针。

而对于Compact格式，其会在blob格式中存储768字节的前缀字节。

char存储结构

char(n)字段中n代表字符长度而非字节长度，故而在不同字符集下，char类型字段的内部存储可能不是定长的。

例如，在utf8字符集下，ab和我们两个字符串，其字符数都是2个，但是ab其占用2字节，而我们占用4字节，即使同样是char(2)类型的字符串，其占用字节数量仍然有可能不同。

innodb数据页结构

innodb中页是磁盘管理的最小结构，页类型为B-tree Node的页存放的即是表中行的实际数据。

innodb数据页由如下7个部分组成：

• File Header（文件头）
• Page Header（页头）
• Infimum和Supremum Records
• user records（用户记录，即行记录）
• free space（空闲空间）
• page directory（页目录）
• file trailer（文件结尾信息）

file header，page header，file trailer的大小是固定的，分别为38，56，8字节，这些空间用于标记页的一些信息，例如checksum，数据页所在B+树的层数等。

File Header

File Header用于记录页的一些头信息，由8个部分组成，共占38字节：

名称	大小	说明
FIL_PAGE_SPACE_OR_CHKSUM	4	代表页的checksum值
FIL_PAGE_OFFSET	4	表空间中页的偏移位置
FIL_PAGE_PREV	4	当前页的上一个页，B+树决定叶子节点为双向列表
FIL_PAGE_NEXT	4	当前页的下一个页
FIL_PAGE_LSN	8	代表该页最后被修改的日志序列位置LSN
FIL_PAGE_TYPE	2	存储引擎页类型
FIL_PAGE_FILE_FLUSH_LSN	8	该值仅在系统表空间的一个页中定义，代表文件至少被更新到了该LSN值，对于独立的表空间，该值为0
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID	4	代表页属于哪个表空间

Infimum和Supremum record

在innodb存储引擎中，每个数据页都有两行虚拟的行记录，用于限定记录边界。Infimum是比该页中所有主键值都要小的值，Supremum是比任何可能值都要大的值。这两个值在页创建时被建立，并且在任何情况下都不会被删除。

user record 和 free space

user reocrd代表实际存储行记录中的内容，free space则是代表空闲空间，同样是链表数据结构，在一条记录被删除后，该空间会被加入到空闲列表中。

page directory

page directory（页目录）中存放了记录的相对位置，这些记录指针被称为目录槽（directory slots）。innodb中槽是一个稀疏目录，一个槽中可能包含多个记录，记录Infimum的n_owned总为1，记录Supremum的n_owned取值范围为[1,8]，其他用户记录的n_owned为[4,8]。当记录被插入或删除时，需要对槽进行分裂或平衡的维护操作。

在slots中，记录按照索引键值顺序存放，可以通过二叉查询迅速找到记录的指针。

由于在innodb存储引擎中，page directory是稀疏目录，二叉查找结果只是一个粗略的结果，innodb存储引擎必须通过record header中的next_record来继续查找相关记录。

B+树索引

如上图所示，innodb中的数据是按照索引来进行组织的。但是，通过B+树索引，其无法直接查询到指定的记录，其只能查询到记录所位于的页。

例如，在上图示例中，其在查询ID=32的时，只能查询到该记录位于page 17中。

在B+树的叶子节点，每个页的File Header中都存有指向前一个页和后一个页的指针，故而每个页之间是通过双向列表结构来维护的；但是对于页中的记录，记录与记录之间维维护的时单向列表的关系。

对于Compact或Dynamic行格式的页，其每条记录的record header中都包含一个next_record字段指向下一条记录。故而，位于同一个页中的记录可以单向访问。

但是，在一个页内查找某条记录时，沿着单向链表进行查找其效率很低。故而，page中的数据时机被分为了多个组，被分为的组构成了一个subdirectory，故而，通过子目录能够缩小查询范围，提高查询性能。

page directory是一个能够存储多个slots的部分，每个slot中存储了group中最后一条记录的相对位置。假设slot中最大一条记录为r，那么group中记录的条数被存储在r记录record header的n_owned字段中。

group中record的数量约束如下：

• infimum group中records数量限制为[1,8]
• supremum group中records数量限制为[1,8]
• 其他group中records限制为[4,8]

page directory生成过程如下所示：

• 最开始时，页中只有infimum和supremum两条记录，它们分别属于两个group。page directory中有两个slots指向这两条记录，两个slot的n_owned都为1
• 后续，当新的记录被插入页时，系统会查找page directory中主键值大于待插入记录的第一个slot。slot对应最大记录的n_owned字段会增加1，代表group中新插入了记录，直到group中的记录数量到达8
• 当新纪录被插入到的group中记录数大于8时，group中的记录被分为2个group，一个group包含4条记录，另一个group包含5条记录。该过程将会向page directory中新增一个新的slot
• 当记录被删除时，slot最最大一条记录的n_owned将会减少1，当n_owned字段小于4时，会触发再平衡操作，平衡后的page directory满足上述要求

page directory中的slots数量如page header中的PAGE_N_DIR_SLOTS所示。

一旦innodb中页包含page directory后，其会通过二分查找快速的定位slot，并且从group中最小记录开始，通过next_record指针来遍历页中的记录列表。这样能够快速的定位记录位置。

File Trailer

完整性校验

在innodb中，页的大小为16KB，可能和磁盘的扇区大小不同。通常磁盘的扇区大小为512字节，故而在写入一个页到磁盘中时，需要分32个扇区进行写入。

在写入一个页时，可能会发生宕机场景，这时，一个页只写入了一部分，可能会发生脏写。此时，可以通过double write buffer机制对脏写的页进行恢复。

在一个页被写入到磁盘中时，首先会被写入的是File Header的FIL_PAGE_SPACE_OR_CHKSUM,该部分是page的checksum。

innodb设置了File Trailer部分来校验page是否被完全写入到磁盘中，File Trailer中只包含一个FIL_PAGE_END_LSN部分，占用8字节，前4字节代表该页的checksum值，后4字节和File Header中的FIL_PAGE_LSN相同，代表最后一次修改该页关联的LSN位置。将上述两个字段和File Header中的FIL_PAGE_SPACE_OR_CHKSUM和FIL_PAGE_LSN值进行比较，查看是否一致，从而保证页的完整性。

默认情况下，在innodb每次从磁盘读取page时，都会检查页面的完整性。

分区表

innodb存储引擎支持分区功能，分区过程将一个表或索引分为多个更小、更可管理的部分。

对于访问数据库的应用而言，逻辑上的一个表或索引，其物理上可能由多个物理分区组成，每个分区都是独立的对象，既可以独自进行处理，又可以作为一个更大对象的一部分进行处理。

mysql仅支持水平分区（将同一张表中的不同行记录分配到不同的物理文件中），并不支持垂直分区（将同一张表中的不同列分配到不同的物理文件中）。

目前mysql支持如下几种类型的分区：

• RANGE: 行数据基于一个给定连续区间的列值被放入分区
• LIST: 和RANGE类似，但LIST分区面对的是更加离散的值
• HASH: 根据用户自定义的表达式返回值来进行分区，返回值不能为负数
• KEY:根据mysql提供的哈希函数来进行分区

不论创建何种类型的分区，如果表中存在唯一索引或主键，分区列必须是唯一索引的一个组成部分。

partition keys & primary keys & unique keys

对于分区表而言，所有在partition expression中使用到的col，其必须被包含在分区表所有的唯一索引中。

所有唯一索引中包含主键索引。

正确建立分区表的声明示例如下：

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

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


CREATE TABLE t7 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;

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


create table t2_partition (
	col1 int ,
	col2 date,
	col3 int null,
	col4 int null,
	unique index `idx_t2_partition_cols` (col1, col2,col3),
	primary key (col2, col1)
) 
partition by hash(col1) 
partitions 4;

表中不存在唯一索引

如果表中没有唯一索引（也未定义primary key），那么上述要求并不会生效，可以在partition expression中使用任意cols。

后续向分区表添加唯一索引

如果想要向分区表中添加唯一索引，那么新增的唯一索引中必须包含partition expression中所有的列。

对非分区表进行分区

可以参照如下示例对非分区表进行分区：

CREATE TABLE np_pk (
        id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(50),
        added DATE,
        PRIMARY KEY (id)
);

可以按id进行分区

ALTER TABLE np_pk
        PARTITION BY HASH(id)
        PARTITIONS 4;

分区类型

RANGE

RANGE为较常见的分区类型，如下示例为创建RANGE类型分区表的示例：

create table t1 (
    id bigint auto_increment not null,
    tran_date datetime(6) not null default now(6) on update now(6),
    primary key (id, tran_date)
) engine=innodb
partition by range (year(tran_date)) (
    partition part_y_let_2024 values less than (2025),
    partition part_y_eq_2025 values less than (2026)
    );

上述创建了表t1，t1主键为(id, tran_date)，且按照tran_date字段的年部分进行分区，分区类型为RANGE。

表t1的分区为两部分，分区1为tran_date位于2024或之前年份的分区part_y_let_2024，分区2是tran_date位于2025年的分区part_y_eq_2025。

information_schema.partitions

如果要查看表的分区情况，可以查询information_schema.partitions表，示例如下

select * from information_schema.partitions where table_schema = 'learn_innodb' and table_name = 't1';

查询结果如下所示：

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME	PARTITION_ORDINAL_POSITION	SUBPARTITION_ORDINAL_POSITION	PARTITION_METHOD	SUBPARTITION_METHOD	PARTITION_EXPRESSION	SUBPARTITION_EXPRESSION	PARTITION_DESCRIPTION	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	CREATE_TIME	UPDATE_TIME	CHECK_TIME	CHECKSUM	PARTITION_COMMENT	NODEGROUP	TABLESPACE_NAME
def	learn_innodb	t1	part_y_eq_2025	null	2	null	RANGE	null	year(`tran_date`)	null	2026	0	0	16384	0	0	0	2025-02-08 01:05:53	null	null	null		default	null
def	learn_innodb	t1	part_y_let_2024	null	1	null	RANGE	null	year(`tran_date`)	null	2025	0	0	16384	0	0	0	2025-02-08 01:05:53	null	null	null		default	null

为分区表预置如下数据

insert into t1(tran_date) values ('2023-01-01'), ('2024-12-31'), ('2025-01-01');

可以看到分区表中数据分布如下：

select partition_name, table_rows from information_schema.partitions where table_schema = 'learn_innodb' and table_name = 't1';

PARTITION_NAME	TABLE_ROWS
part_y_eq_2025	1
part_y_let_2024	2

易知('2023-01-01'), ('2024-12-31')数据位于part_y_let_2024分区，故而该分区存在两条数据；而('2025-01-01')数据位于part_y_let_2024分区，该分区存在一条数据。

看select语句查询了哪些分区

数据分布可以通过如下方式验证：

explain  select * from t1 where tran_date >= '2023-01-01' and tran_date <= '2024-12-31';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	part_y_let_2024	index	PRIMARY	PRIMARY	16	null	2	50	Using where; Using index

explain select * from t1 where tran_date >= '2025-01-01'

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	part_y_eq_2025	index	PRIMARY	PRIMARY	16	null	1	100	Using where; Using index

插入超过分区范围的数据

如果尝试向分区表中插入超过所有分区范围的数据，会执行失败，示例如下：

insert into t1(tran_date) values ('2026-01-01');

由于分区表t1中现有分区最大只支持2025年的分区，故而当尝试插入2026年分区时，会抛出如下错误：

[2025-02-08 01:32:51] [HY000][1526] Table has no partition for value 2026

向分区表中添加分区

mysql支持向分区表中添加分区，语法如下

alter table t1 add partition (
    partition part_year_egt_2026 values less than maxvalue
    );

添加分区后，mysql支持tran_date大于等于2026的分区，执行如下语句

insert into t1(tran_date) values ('2026-01-01'), ('2027-12-31'), ('2028-01-01');

之后，再次分区分区中的数据分区，结果如下

PARTITION_NAME	TABLE_ROWS
part_y_eq_2025	1
part_y_let_2024	2
part_year_egt_2026	3

information.partitions表中，table_rows字段可能存在不准确的情况，如果想要获取准确的值，需要执行analyze table {schema}.{table_name}语句

向分区表中删除分区

当想要删除分区表中现存分区时，可以通过执行如下语句

alter table t1 drop partition part_year_egt_2026;

在删除分区后，分区中的数据也会被删除，执行查询语句后

select * from t1;

可知part_year_egt_2026被删除后分区中数据全部消失

id	tran_date
1	2023-01-01 00:00:00.000000
2	2024-12-31 00:00:00.000000
3	2025-01-01 00:00:00.000000

LIST

LIST分区类型和RANGE分区类型非常相似，但是LIST分区列的值是离散的，RANGE分区列的值是连续的。

创建LIST类型分区表的示例如下：

create table p_list_t1 (
    id bigint not null auto_increment,
    area smallint not null,
    primary key (id, area)
)
partition by list (area) (
    partition p_ch_beijing values in (1),
    partition p_ch_hubei values in (2),
    partition p_ch_jilin values in (3)
    );

之后可向p_list_t1表中预置数据，执行语句如下：

insert into p_list_t1(area) values (1),(1), (2), (3), (3);

此时，数据分布如下：

PARTITION_METHOD	PARTITION_NAME	TABLE_ROWS
LIST	p_ch_beijing	2
LIST	p_ch_hubei	1
LIST	p_ch_jilin	2

如果想要向分区表中插入不位于现存分区中的值，那么插入语句同样会执行失败，示例如下

insert into p_list_t1(area) values (4);

由于目前没有area为4的分区，故而插入语句执行失败，报错如下

[2025-02-09 20:30:03] [HY000][1526] Table has no partition for value 4

HASH

HASH分区方式是将数据均匀分布到预先定义的各个分区中，期望各个分区中散列的数据数量大致相同。

在通过hash来进行分区时，建表时需要指定一个分区表达式，该表达式返回整数。

创建hash分区的示例如下：

create table p_hash_t1 (
    id bigint not null auto_increment,
    content varchar(256),
    primary key (id)
)
partition by hash (id)
partitions 4;

上述ddl创建了一个按照id列进行hash分区的分区表，分区表包含4个分区。

预置数据sql如下：

insert into p_hash_t1(content) values ('asahi'), ('maki'), ('katahara');

预置数据后数据分布如下

PARTITION_METHOD	PARTITION_NAME	TABLE_ROWS
HASH	p0	0
HASH	p1	1
HASH	p2	1
HASH	p3	1

预置的三条数据id分别为1, 2, 3，被hash放置到分区p1, p2, p3中。

当使用hash分区方式时，例如存在4个分区，对于待插入数据其分区表达式的值为2，那么待插入数据将会被插入到2%4 = 2，第3个分区中(0,1,2分区，排第三)，也就是p2。

可以通过explain select * from p_hash_t1 where id = 2;语句来进行验证，得到如下结果。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	p_hash_t1	p2	const	PRIMARY	PRIMARY	8	const	1	100	null

新增HASH分区

对于hash分区方式，可以通过如下方式来新增分区：

alter table p_hash_t1 add partition partitions 3;

其会新增3个分区，执行后分区数量为7。

此时，原本分区中的数据会被重新散列到新分区中

减少HASH分区

对于hash分区，在尝试减少分区数量的同时，通常不希望删除任何数据，故而无法使用drop。

故而，可以使用coalesce，其只会减少分区数量，不会删除数据。

示例如下：

alter table p_hash_t1 coalesce partition 4;

上述语句会将分区数量减少4个，故而减少后分区数量为3。

LINEAR HASH

创建linear hash分区表的方式和hash类似，可以通过如下方式

create table p_linear_hash_t1 (
    id bigint not null auto_increment,
    content varchar(256),
    primary key(id)
)
partition by linear hash (id)
partitions 5;

相较于hash分区，在使用linear hash分区时，添加、删除、合并、拆分分区将会变得更加快捷，但是相比于hash分区，其数据分区可能会不太均匀。

KEY & LINEAR KEY

KEY分区方式和HASH分区方式类似，但是HASH分区采用用户自定义的函数进行分区，而KEY分区方式则是采用mysql提供的函数进行分区。

创建key分区表的示例如下：

create table p_key_t1 (
    id bigint not null auto_increment,
    content varchar(256),
    primary key(id)
)
partition by key(id)
partitions 5;

key分区方式同样可以使用linear进行修饰，效果和hash分区方式类似。

对于key分区，可以指定除了integer之外的col类型，而range, list, hash等分区类型只支持integer类型的col。

COLUMNS

COLUMNS分区方式为RANGE和LIST分区的变体。

COLUMNS分区方式支持在分区时使用多个列，在插入新数据或查询数据决定分区时，所有的列都会被考虑。

COLUMNS分区方式分为如下两种

• RANGE COLUMNS
• LIST COLUMNS

上述两种方式都支持非整数类型的列，COLUMNS支持的数据类型如下：

• 所有integers类型（DECIMAL, FLOAT则不受支持）
• DATE和DATETIME类型
• 字符串类型：CHAR, VARCHAR, BINARY, VARBINARY(TEXT, BLOB类型不受支持)

range columns

创建range columns分区表的示例如下：

create table p_range_columns_t1 (
    id bigint not null auto_increment,
    name varchar(256),
    born_ts datetime(6),
    primary key (id, born_ts)
)
partition by range columns (born_ts) (
    partition p0 values less than ('1900-01-01'),
    partition p1 values less than ('2000-01-01'),
    partition p2 values less than ('2999-01-01')
    );

list columns

创建list columns分区表的示例则如下：

create table p_list_columns_t1 (
    id bigint not null auto_increment,
    province varchar(256),
    city varchar(256),
    primary key (id, province, city)
)
partition by list columns (province, city) (
    partition p_beijing values in (('china', 'beijing')),
    partition p_wuhan values in (('hubei', 'wuhan'))
    );

通过range columns和list columns，可以很好的替代range和list分区，而无需将列值都转换为整型。

子分区

子分区代表在分区的基础上再次进行分区，mysql允许在range和list分区的基础上再次进行hash或key的子分区，示例如下：

create table p_list_columns_t1 (
    id bigint not null auto_increment,
    province varchar(256),
    city varchar(256),
    primary key (id, province, city)
)
partition by list columns (province, city)
subpartition by key(id)
subpartitions 3
(
    partition p_beijing values in (('china', 'beijing')),
    partition p_wuhan values in (('hubei', 'wuhan'))
);

上述示例先按照list columns分区方式进行了分区，然后在为每个分区都按key分了三个子分区，查看分区和子分区详情可以通过如下语句：

select partition_name, partition_method, subpartition_name, subpartition_method from information_schema.partitions where table_name = 'p_list_columns_t1';

PARTITION_NAME	PARTITION_METHOD	SUBPARTITION_NAME	SUBPARTITION_METHOD
p_wuhan	LIST COLUMNS	p_wuhansp0	KEY
p_wuhan	LIST COLUMNS	p_wuhansp1	KEY
p_wuhan	LIST COLUMNS	p_wuhansp2	KEY
p_beijing	LIST COLUMNS	p_beijingsp0	KEY
p_beijing	LIST COLUMNS	p_beijingsp1	KEY
p_beijing	LIST COLUMNS	p_beijingsp2	KEY

分区中的NULL值

mysql允许对null值做分区，但mysql数据库中的分区总是视null值小于任何非null的值，该逻辑和order by处理null值的逻辑一致。

故而，在使用不同的分区类型时，对于null值的处理逻辑如下：

• 对于range类型分区，当插入null值时，其会被放在最左侧的分区中
• 对于list类型的分区，如果要插入null值，必须在分区的values in (...)表达式中指定null值，否则插入语句将会报错
• hash和key的分区类型，将会将null值当作0来散列

分区和性能

对于OLTP类型的应用，使用分区表时应该相当小心，因为分区表可能会带来严重的性能问题。

例如，对于包含1000w条数据的表t，如果包含主键索引id和非主键索引code，分区和不分区，其性能分析如下

不分区

如果不对表t进行分区，那么根据id（唯一主键索引）或code（非unique索引）进行查询，例如select * from t where id = xxx或select * from t where code = xxx时，可能只会进行23次磁盘io（1000w数据构成的B+树其层高为23）。

按id进行分区

如果对表t按照id进行hash分区，分为10个分区，那么：

• 对于按id进行查找的语句select * from t where id = xxx
  • 将t按id分为10个区后，如果分区均匀，那么每个分区数据大概为100w，对分区的查询开销大概为2次磁盘io
  • 根据id的查询只会在一个分区内进行查找，故而磁盘io会从3次减少为2次，可以提升查询效率
• 对于按code进行查找的语句select * from t where code = xxx
  • 对于按code进行查找的语句，需要扫描所有的10个分区，每个分区大概需要2次磁盘io，故而总共的磁盘io大约为20次

在使用分区表时，应尽量小心，不正确的使用分区将可能会带来大量的io，造成性能瓶颈

在表和分区之间交换数据

mysql支持在表分区和非分区表之间交换数据：

• 在非分区表为空的场景下，相当于将分区中的数据移动到非分区表中
• 在表分区为空的场景下，相当于将非分区表中的数据移动到表分区中

在表和分区之间交换数据，可以通过alter table ... exchange partition语句，且必须满足如下条件：

• 要交换的非分区表和分区表必须要拥有相同的结构，但是，非分区表中不能够含有分区
• 非分区表中的数据都要位于表分区的范围内
• 被交换的表中不能含有外键，或是其他表含有被交换表的外键引用
• 用户需要拥有alter, insert, create, drop权限
• 使用alter table ... exchange parition语句时，不会触发交换表和被交换表上的触发器
• auto_increment将会被重置

exchange partition ... with table的示例如下：

alter table p_range_columns_t exchange partition p_2024 with table np_t