MySQL 分区表也不错

原创 CuiHulong 2021-10-12

3133

1.分区表目的

MySQL数据库中的数据是以文件形式存在磁盘上的ibd文件。如果一张表的数据量太大的话，查找数据就会变的很慢，比如：在linux系统下vim命令打开30G的文件，基本打不开或无线等待中。数据库操作也是基于文件系统的。

为了解决这个问题，可以在物理上将这一张表对应的文件，分割成许多个小块。查找一条数据时，就不用全部查找了，只要知道这条数据在哪一文件下，就可以快速的检索。

表分区是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

MySQL中的物理文件里分区和子分区的文件名里分隔符创建。如# p#和# sp#

那怎样的场景中，分区可以起到非常大的作用：

1.表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他都是历史数据
2.分区表的数据更容易维护，如：想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作
3.分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备
4.可以使用分区表来避免某些特殊的瓶颈，如：innodb的单个索引的互斥访问，ext3文件系统的inode锁竞争等
5.如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好
6.优化查询，在where字句中包含分区列时，可以只使用必要的分区来提高查询效率，同时在涉及sum()和count()这类聚合函数的查询时，可以在每个分区上面并行处理，最终只需要汇总所有分区得到的结果。

2.分区类型

MySQL 8.0版本中可用的分区类型目前有5种，但可以归纳为3种。

1）RANGE 范围分区：这种类型的分区根据位于给定范围内的列值将行分配给分区。

mysql>CREATE TABLE employees (
    id INT NOT NULL,
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
mysql>CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2021-01-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2021-10-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

复制

2）LIST列表分区：分区是根据匹配一组离散值中的一个列来选择

mysql>CREATE TABLE employees (
    id INT NOT NULL,
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);
复制

3）HASH散列分区：将根据用户定义表达式返回的值选择分区，该表达式对要插入到表中的行中的列值进行哈希操作。HASH分区主要是为了确保数据在预定数量的分区之间均匀分布。PARTITIONS num，其中num是一个正整数，表示要将表划分成的分区数，如下面4个ibd文件。

mysql>CREATE TABLE employees (
    id INT NOT NULL,
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

#函数处理
mysql>CREATE TABLE employees (
    id INT NOT NULL,
    hired DATE NOT NULL DEFAULT '1970-01-01',
    job_code INT,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

#线性hash
mysql>CREATE TABLE employees (
    id INT NOT NULL,
    hired DATE NOT NULL DEFAULT '1970-01-01',
    job_code INT,
    store_id INT
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 4;

复制

备注：
MySQL也支LINEAR HASH，它与常规HASH的不同之处是，线性哈希使用线性的2次幂算法，而常规哈希使用哈希函数值的模。就说通过2的取模方式
比如:
数字137的2进制表示为10001001.
将这种2进制表示写成2的次幂的和的形式，令次幂高的排在前面，可得到如下表达式：
137=2⁷⁺²3+2^0

线性散列（LINEAR HASH）进行分区
优点是，分区的添加、删除、合并和拆分速度要快得多，这在处理包含大量(tb)数据的表时非常有用。
缺点是，与使用常规散列分区获得的分布相比，数据不太可能在分区之间均匀分布。

4）KEY关键字分区。

这种类型的分区类似于HASH分区，除了只提供一个或多个要计算的列，并且MySQL服务器使用自己的内部哈希函数，该函数基于与PASSWORD()相同的算法。NDB集群为此使用MD5()。

KEY只接受一个包含零个或多个列名的列表。用作分区键的任何列必须包含表的部分或全部主键(如果表有主键的话)。如果没有指定列名作为分区键，则使用表的主键。

mysql>CREATE TABLE tm1 (
    s1 CHAR(32)
)
PARTITION BY KEY(s1)
PARTITIONS 10;

复制

5）COLUMNS分区：

COLUMNS分区是RANGE和LIST分区的变体。列分区允许在分区键中使用多个列。
多列range分区，分区的所有多列必须是主键或则唯一键或无主键唯一键

mysql>CREATE TABLE prt_column_range (
         a INT,
         b INT,
         c CHAR(3),
         d INT
     )
     PARTITION BY RANGE COLUMNS(a,d,c) (
         PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
         PARTITION p1 VALUES LESS THAN (10,20,'mmm'),
         PARTITION p2 VALUES LESS THAN (15,30,'sss'),
         PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
     );

复制

支持： TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), and BIGINT,DATE and DATETIME,CHAR、VARCHAR、BINARY和VARBINARY。
不支持：TEXT ， BLOB，DECIMAL ， FLOAT

6）子分区(复合分区)

是对已分区表中的每个分区的进一步划分.
分区是第一阶梯是按RANGE或LIST分区，第二阶梯子分区使用HASH或KEY分区。
例子：
ts有3个RANGE分区。每个分区(p0、p1和p2)被进一步划分为3个子分区。实际上，整个表被划分为3 * 3 = 9个分区。但是，由于PARTITION BY RANGE子句的操作，其中前2个只存储购买列中值小于1990的记录。

range分区+ hash子分区
mysql>CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 3 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );



#list分区 + key子分区
mysql>CREATE TABLE employees (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY LIST(store_id)
    SUBPARTITION BY KEY( store_id )
    SUBPARTITIONS 2 (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
); 

复制

备注：
subpartitions必须使用HASH或KEY分区。只能对RANGE和LIST分区进行子分区;不能对HASH和KEY分区进行子分区。

3.分区表操作

对于目前支持的分区操作如下：

partition_option: {
    ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | DISCARD PARTITION {partition_names | ALL} TABLESPACE
  | IMPORT PARTITION {partition_names | ALL} TABLESPACE
  | TRUNCATE PARTITION {partition_names | ALL}
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH | WITHOUT} VALIDATION]
  | ANALYZE PARTITION {partition_names | ALL}
  | CHECK PARTITION {partition_names | ALL}
  | OPTIMIZE PARTITION {partition_names | ALL}
  | REBUILD PARTITION {partition_names | ALL}
  | REPAIR PARTITION {partition_names | ALL}
  | REMOVE PARTITIONING
复制

1）ADD 分区：

对于没有设置下线上线的分区是可以通过ADD命令行添加，反而是无法使用add命令

CREATE TABLE tb2 (
    id INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
复制

提示错误信息：

mysql> ALTER TABLE tb2 add  PARTITION(
          PARTITION p1 VALUES LESS THAN (11) );
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

复制

2）DROP 分区：

DROP PARTITION可用于删除一个或多个RANGE或LIST分区。删除实际数据的同时，删除分区文件，变更表结构。不适用于使用NDB存储引擎，不支持IF [not] EXISTS。

mysql>CREATE TABLE t1 (
    id INT,
    year_col INT
)
PARTITION BY RANGE (year_col) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (1999)
);

ALTER TABLE t1 DROP PARTITION p0, p1;
复制

3）TRUNCATE PARTITION ：

要从选定的只是删除行，不会改变表本身的定义

mysql>ALTER TABLE t1 TRUNCATE PARTITION p0;
mysql>ALTER TABLE t1 TRUNCATE PARTITION p1, p3;
mysql>ALTER TABLE t1 TRUNCATE PARTITION ALL;  #所有表分区

#要验证是否删除了行，有时候会存在不准确情况
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
        FROM INFORMATION_SCHEMA.PARTITIONS;

复制

4）COALESCE 减少分区数量分区：

COALESCE PARTITION可用于按HASH或KEY分区的表，以按数量减少分区数量

mysql>CREATE TABLE t2 (
    name VARCHAR (30),
    started DATE
)
PARTITION BY HASH( YEAR(started) )
PARTITIONS 6;

#要将t2使用的分区数量从6减少到4，可以使
mysql>ALTER TABLE t2 COALESCE PARTITION 4;

复制

5）REORGANIZE重组分区：

重组分区;新建分区表，把原有的数据复制到新的分区，之后删除之前分区。
ALTER TABLE … REORGANIZE PARTITION

mysql>CREATE TABLE members (
    id INT,
    create_dt DATE
)
PARTITION BY RANGE( YEAR(create_dt) ) (
    PARTITION p0 VALUES LESS THAN (1980),
    PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN (2021)
);


#p0分区 分成n0和n1分区
ALTER TABLE members
    REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1970),
        PARTITION n1 VALUES LESS THAN (1980)
);

复制

6）REMOVE PARTITIONING

允许在不影响表或其数据的情况下删除表的分区，数据插入到新的ibd保留，表结构里删除分区

mysql>CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (50),
        PARTITION p1 VALUES LESS THAN (100),
        PARTITION p2 VALUES LESS THAN (150),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

mysql>INSERT INTO e VALUES (1669, "Jim", "Smith"), (337, "Mary", "Jones"), (16, "Frank", "White"), (2005, "Linda", "Black");

mysql>ALTER TABLE e REMOVE PARTITIONING;

Mysql>SHOW CREATE TABLE e;
| CREATE TABLE `e` (
  `id` int NOT NULL,
  `fname` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `lname` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB;
复制

7）EXCHANGE PARTITION用表交换分区和子分区

就是把分区或子分区中的任何现有行移动到非分区表中，确保分区表和非分区字段一致。

mysql>ALTER TABLE e EXCHANGE PARTITION p3 WITH TABLE e2;

复制

8）其他分区命令

分区命令：	说明
REBUILD PARTITION	重建分区;这与删除存储在分区中的所有记录，然后重新插入它们具有相同的效果
OPTIMIZE PARTITION	优化分区，一个分区中删除了大量的行，优化分区回收任何未使用的空间，并整理分区数据文件的碎片
ANALYZE PARTITION	当分区包含重复的键错误时，ALTER IGNORE TABLE重复键都将从分区中删除
REPAIR PARTITION	修复损坏的分区
CHECK PARTITION	检查分区

InnoDB目前不支持分区优化;ALTER TABLE……OPTIMIZE PARTITION会重新构建和分析整个表

4.分区限制:

1）禁止结构:

分区表不允许使用一下结构：

存储过程、存储函数、可加载函数或插件。
声明变量或用户变量。
InnoDB存储引擎的分区表不支持外键
分区表不支持FULLTEXT索引
无法对临时表进行分区
分区键必须是整数列或解析为整数的表达式。不能使用使用ENUM列的表达式
键分区不支持列索引前缀。

2）服务器的SQL模式

许多MySQL函数和操作符的结果可能会根据服务器SQL模式而改变，
因此，在创建分区表之后的任何时候更改SQL模式都可能导致此类表的行为发生重大变化，并且很容易导致数据损坏或丢失。由于这些原因，强烈建议创建分区表之后不要更改服务器SQL模式。
1.RANGE 范围分区：这种类型的分区根据位于给定范围内的列值将行分配给分区。

3）分区键、主键和唯一键

分区表上有主键和唯一键必须在分区键上。也可以使用函数。

的分区表达式中使用的所有列必须是表可能拥有的每个唯一键的一部分。
换句话说，表上的每个唯一键必须使用表分区表达式中的每个列

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;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered)

复制

如果一个表没有唯一键或主键——那么这个限制就不适用，只要列类型与分区类型兼容，就可以在分区表达式中使用任何一列或多列。

出于同样的原因，以后不能向分区表添加惟一的键，除非该键包含表分区表达式使用的所有列。考虑如下所示创建的分区表:

创建分区之后对于另一个键创建主键，报错：

mysql>CREATE TABLE t_no_pk (c1 INT, c2 INT)
        PARTITION BY RANGE(c1) (
            PARTITION p0 VALUES LESS THAN (10),
            PARTITION p1 VALUES LESS THAN (20),
            PARTITION p2 VALUES LESS THAN (30),
            PARTITION p3 VALUES LESS THAN (40)
        );
.
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c2);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).

复制

备注：分区键创建之后，不要尝试去替换和更改主键和唯一键

3）存储引擎分区限制

在MySQL 8.0中，分区支持实际上不是由MySQL服务器提供的，而是由自身表存储引擎或本地分区处理程序提供的。在MySQL 8.0中，只有InnoDB存储引擎提供了本地分区处理程序。MySQL集群的NDB存储引擎也提供了本地分区支持。

如MyISAM。尝试使用不提供本地分区支持的存储引擎创建分区表时，使用ER_CHECK_NOT_IMPLEMENTED会失败。

InnoDB存储引擎无法禁用分区支持。

NDB表中每个分区可以存储的固定大小数据的最大容量为128tb。之前，这是16gb。
CREATE TABLE和ALTER TABLE语句将导致用户分区的NDB表不满足以下两个要求中的任何一个或两个都是不允许的，并且失败并报错:

该表必须有一个显式的主键。
表分区表达式中列出的所有列必须是主键的一部分。
例外。如果使用空列列表(即使用PARTITION BY KEY()或PARTITION BY LINEAR KEY())创建用户分区的NDB表，则不需要显式的主键

6）升级分区表

在执行升级时，必须转储并重新加载按KEY分区的表。
使用非InnoDB存储引擎的分区表不能从MySQL 5.7及以前版本升级到MySQL 8.0及以上版本;
必须转换成InnoDB使用ALTER TABLE…ENGINE。

7）NULL处理

MySQL中的分区不会禁止将NULL作为分区表达式的值，无论它是列值还是用户提供的表达式的值。尽管允许使用NULL作为表达式的值，但必须记住NULL不是一个数字，这一点很重要。MySQL的分区实现将NULL视为小于任何非NULL值

RANGE分区处理NULL。如果插入NULL列值，则该行将插入到最低的分区中
LIST分区的表，如果在值列表中没有显式地使用NULL，则会拒绝导致分区表达式为NULL值的行为
HASH和KEY分区处理NULL。NULL的处理方式略有不同。任何产生NULL值的分区表达式都会被视为其返回值为0

5.SQL语句对分区表的影响：

1）分区表DDL的操作实现依赖于文件系统操作，这意味着这些操作的速度受到文件系统类型和特征、磁盘速度、交换空间、操作系统的文件处理效率、MySQL服务器选项和与文件处理相关的变量等因素的影响。特别是，应该确保启用了large_files_support，并正确设置了open_files_limit。启用innodb_file_per_table可以使涉及InnoDB表的分区和重分区操作更加高效。
2）对于MySQL来说优化SQL基础原则就是走索引。对于分区表来说基于一个相对简单的概念“不要扫描可能没有匹配值的分区”与扫描表中的所有分区相比，查找匹配行所花费的时间和精力要少得多。
第一种情况：优化器只需计算给定值的分区表达式，确定哪个分区包含该值，然后只扫描这个分区。
第二种情况：优化器为列表中的每个值计算分区表达式，创建一个匹配分区列表，然后只扫描这个分区列表中的分区。
3）对于DML语句SELECT、DELETE和UPDATE语句第一步操作是分区层先打开并锁定住所有的底层表，之后进行对应的操作。这样底层表句柄文件时需要在缓存里。对于过多的文件句柄来说，mysql处理能力有限。mysql 文件建议不要操作3000
4）范围查询分区表，必走第一个分区，所以建议把第一个分区作为空的分区https://bugs.mysql.com/bug.php?id=49754

6.总结

SQL语句里一定要走分区键

当分区表操作不当导致访问所有分区时，会导致严重的性能问题，影响MySQL整体性能。
若查询不走分区键，则可能会扫描所有分区，效率不会提升。
若数据分布不均，分区大小差别较大，可能性能提升也有限。

维护分区的成本可能很高

当分区表第一次被访问时，无论该次访问需要操作多少个分区，都需要访问该分区表上所有分区，导致性能问题。当分区表上分区数量较大时，可能会因为打开文件数量超过参数open_file_limit限制而出错。（操作系统支持65535文件）
在对分区表进行维护时，需要同时维护原分区文件和新分区文件，如将分区表由100分区扩展至101分区时，需要2 * 100 + 2 * 101=402个文件描述符。
DDL操作需要锁定所有分区，导致所有分区上操作都被阻塞。当表数据量较大时，对分区表进行DDL或其他运维操作难度大风险高。
分区表的数量，不易过多
最大分区数目建议不能超过1024，对于整张表分区的表，不易一次性创建太多分区表。
比如：可以实现每星期7天，10天或则一个月创建一次分区表的方式
分区表的清理
数据应该定期进行清理，按照实际需要保留数据，对于数据清理，确保表没有进行访问，业务低峰期进行操作。

深度总结

了解到了分区表的特性，是否可以无条件的使用。当然是否定的。MYSQL本身轻量级，对于大批量数据来说提供的性能有限。如果可以个人建议按天按月分表应该更适合。

mysql 墨力计划

最后修改时间：2025-02-18 15:37:59

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

3人已赞赏

文章被以下合辑收录

专家专栏 | 崔虎龙（共162篇）

云和恩墨开源MySQL技术顾问，长期服务于数据中心（银行，金融、游戏、物流）行业，熟悉数据中心运营管理的流程及规范、自动化运维等方面。擅长MySQL，Redis，MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自我学习通过了MySQL OCP5.6 和 MySQL OCP5.7认证证书。

1.分区表目的
2.分区类型
- 1）RANGE 范围分区：这种类型的分区根据位于给定范围内的列值将行分配给分区。
- 2）LIST列表分区：分区是根据匹配一组离散值中的一个列来选择
- 3）HASH散列分区：将根据用户定义表达式返回的值选择分区，该表达式对要插入到表中的行中的列值进行哈希操作。HASH分区主要是为了确保数据在预定数量的分区之间均匀分布。PARTITIONS num，其中num是一个正整数，表示要将表划分成的分区数，如下面4个ibd文件。
- 4）KEY关键字分区。
- 5）COLUMNS分区：
- 6）子分区(复合分区)
3.分区表操作
- 1）ADD 分区：
- 2）DROP 分区：
- 3）TRUNCATE PARTITION ：
- 4）COALESCE 减少分区数量分区：
- 5）REORGANIZE重组分区：
- 6）REMOVE PARTITIONING
- 7）EXCHANGE PARTITION用表交换分区和子分区
- 8）其他分区命令
4.分区限制:
- 1）禁止结构:
- 2）服务器的SQL模式
- 3）分区键、主键和唯一键
- 3）存储引擎分区限制
- 6）升级分区表
- 7）NULL处理
5.SQL语句对分区表的影响：
6.总结
- SQL语句里一定要走分区键
- 维护分区的成本可能很高
- 深度总结