MySQL（十三）：分区表（ Partitioning Table）

最新推荐文章于 2023-07-14 19:32:40 发布

IT码客

最新推荐文章于 2023-07-14 19:32:40 发布

阅读量2.9k

点赞数 3

分类专栏：【数据库】MySQL 文章标签： mysql 分区表数据库

本文链接：https://blog.csdn.net/u010647035/article/details/106374248

版权

【数据库】MySQL 专栏收录该内容

10 篇文章 9 订阅

订阅专栏

文章目录

1、MySQL 分区简介

1.1、什么是分区表

分区表就是将表数据通过指定的规则存储在不同的文件中，这些文件可以在单个磁盘或多个磁盘上。分区后，逻辑上还是一张数据表，对分区表进行读写时，数据库会自动找到请求的分区。

1.2、分区表优点

在日常的工作中，我们会遇到一张表里面保存了千万或上亿的记录。当对数据进行操作时，经常会携带多个where条件，但是这样仍会对数据库的造成了很大压力，很难达到我们期望的效果。
即使我们删除了一些数据，但底层的数据文件并没有变小。针对这些问题，使用分区表便可以迎刃而解了。

1、单个分区表可以存储更多的数据

一个数据表通过分区可以将数据存储在多个磁盘或多个文件中，与单个磁盘或文件系统分区相比，可以存储更多的数据。

2、可以灵活删除和新增数据

通过删除一个或多个分区来删除无用数据，使删除变得更简单。相反，在某些情况下添加新数据的过程可以通过添加一个或多个新分区来专门存储该数据而得到极大的便利。

3、MySQL支持显式的分区选择查询

例如，SELECT * FROM t PARTITION（p0，p1）WHERE c <5 仅选择分区p0和p1中与WHERE条件匹配的那些行。
在这种情况下，MySQL不会检查表t的其他分区。当已经知道要检查的分区时，这可以大大加快查询速度。
数据修改语句DELETE，INSERT，REPLACE，UPDATE和LOAD DATA，LOAD XML也支持分区选择。

4、可以很容易使用并行处理

涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。
例如，SELECT username, COUNT (address) as address_num FROM tb_user GROUP BY username。
该查询语句可以在多个分区上同时进行，通过汇总各个分区结果得到最终数据。

5、通过跨多个磁盘来分散数据查询，可以获得更大的查询吞吐量。

6、在创建分区表后可以更改，因此可以重新组织数据，以调整为合适的分区方案。

1.3、分区表缺点

1、目前应用中的大部分表都有主键，受限于分区表达式的规则，往往需要重新创建表结构，可能会影响现有业务。

2、分区表的实现机制有额外的开销，当分区表很多时，开销会越来越大。“根据实际经验对于大多数系统100个左右的分区是没问题的”摘自《高性能MySQL》。

3、打开并锁住所有底层表的成本可能很高，当查询分区表的时候，MySQL需要打开并锁住所有的底层表，这是分区表的另外一个开销。

4、维护分区的成本可能会很高，新增、删除分区速度会很快。但是重组或ALERT分区表时，会先创建一个临时分区，然后将数据复制到其中，再删除原分区。

2、MySQL 分区类型

为简单起见，以下示例中的表不使用任何键。如果表具有任何唯一键，则该表的分区表达式中使用的每一列都必须是唯一键（包括主键）的一部分。

MySQL 8.0中包含以下可用的分区类型：

（1）RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。

（2）LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

（3）HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

（4）KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值

（5）复合分区：基于RANGE/LIST 类型的分区表中每个分区的再次分割。子分区可以是 HASH/KEY等类型。

数据库分区的一种非常常见的用法是按日期分区。某些数据库系统支持显式日期分区，而MySQL在8.0中未实现。但是，在MySQL中可以创建基于DATE，TIME或DATETIME列或基于使用此类列的表达式的分区。

通过KEY或LINEAR KEY进行分区时，可以将DATE，TIME或DATETIME列用作分区列，而无需对列值进行任何修改。

2.1、范围分区（RANGE）

按范围分区的表的分区方式是，基于属于一个给定连续区间的列值，把多行分配给分区。最常见的是基于时间字段. 基于分区的列最好是整型，如果日期型的可以使用函数转换为整型。

范围应该是连续的，但不能重叠，并使用VALUES LESS THAN运算符定义。

2.1.1、基于时间间隔的分区方案。

如果希望在MySQL 8.0中基于时间范围实现分区方案，则有两个选择：

2.1.1.1、按 RANGE 对表进行分区

按 RANGE 对表进行分区，对于分区表达式，要使用对 DATE、TIME、DATETIME 列操作返回整数值的函数，如下所示：

CREATE TABLE `employees_range_date` (
  `emp_no` INT NOT NULL,
  `birth_date` DATE NOT NULL,
  `first_name` VARCHAR(14) NOT NULL,
  `last_name` VARCHAR(16) NOT NULL,
  `gender` ENUM('M','F') NOT NULL,
  `hire_date` DATE NOT NULL
) 
PARTITION BY RANGE(YEAR(hire_date))(
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

插入数据：

INSERT INTO employees_range_date SELECT * FROM employees;

查看查询的执行计划：

EXPLAIN SELECT * FROM employees_range_date WHERE hire_date >= '1986-06-26' AND hire_date <='1990-12-01'; 

    id  select_type  table                 partitions  type    possible_keys  key     key_len  ref       rows  filtered  Extra        
------  -----------  --------------------  ----------  ------  -------------  ------  -------  ------  ------  --------  -------------
     1  SIMPLE       employees_range_date  p3,p4       ALL     (NULL)         (NULL)  (NULL)   (NULL)  299298     11.11  Using where

从执行计划中的partitions的内容来看，只查询了p3,p4 两个分区。

p4 是一个默认分区，所有大于 1990 的记录都会在这个分区。MAXVALUE 是一个无穷大的值。p4 是一个可选分区。如果在定义表的没有指定的这个分区，当我们插入大于 1990 的数据的时，会收到一个错误。

在 MySQL 8.0 中，还可以使用UNIX_TIMESTAMP（）函数根据TIMESTAMP列的值按RANGE对表进行分区，如以下示例所示：

CREATE TABLE `employees_range_timestamp` (
  `emp_no` INT NOT NULL,
  `birth_date` DATE NOT NULL,
  `first_name` VARCHAR(14) NOT NULL,
  `last_name` VARCHAR(16) NOT NULL,
  `gender` ENUM('M','F') NOT NULL,
  `hire_date` TIMESTAMP NOT NULL
) 
PARTITION BY RANGE(UNIX_TIMESTAMP(hire_date))(    
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('1960-12-31 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('1970-12-31 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('1980-12-31 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('1990-12-31 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2020-12-31 00:00:00') )
);

插入数据：

mysql> INSERT INTO employees_range_timestamp SELECT emp_no,birth_date,first_name,last_name,gender,hire_date FROM employees;

Query OK, 300024 rows affected (2 min 9.72 sec)
Records: 300024  Duplicates: 0  Warnings: 0

查看查询的执行计划：

mysql> EXPLAIN SELECT * FROM employees_range_timestamp WHERE hire_date >= '1986-06-26' AND hire_date <='1990-12-01'; 
+----+-------------+---------------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table                     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees_range_timestamp | p3         | ALL  | NULL          | NULL | NULL    | NULL | 190059 |    11.11 | Using where |
+----+-------------+---------------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

2.1.1.2、按 RANGE COLUMNS 对表分区

使用DATE或DATETIME列作为分区列，按RANGE COLUMNS分区表。如下所示：

CREATE TABLE `employees_range_columns` (
  `emp_no` INT NOT NULL,
  `birth_date` DATE NOT NULL,
  `first_name` VARCHAR(14) NOT NULL,
  `last_name` VARCHAR(16) NOT NULL,
  `gender` ENUM('M','F') NOT NULL,
  `hire_date` DATE NOT NULL
) 
PARTITION BY RANGE COLUMNS(hire_date)(    
    PARTITION p0 VALUES LESS THAN ('1960-12-31 00:00:00'),
    PARTITION p1 VALUES LESS THAN ('1970-12-31 00:00:00'),
    PARTITION p2 VALUES LESS THAN ('1980-12-31 00:00:00'),
    PARTITION p3 VALUES LESS THAN ('1990-12-31 00:00:00'),
    PARTITION p4 VALUES LESS THAN ('2020-12-31 00:00:00')
);

插入数据：

mysql> INSERT INTO employees_range_columns SELECT emp_no,birth_date,first_name,last_name,gender,hire_date FROM employees;
Query OK, 300024 rows affected (15.91 sec)
Records: 300024  Duplicates: 0  Warnings: 0

查看查询计划：

mysql> EXPLAIN SELECT * FROM employees_range_columns WHERE hire_date >= '1970-06-26' AND hire_date <='2020-12-01';
+----+-------------+-------------------------+-------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table                   | partitions  | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------------------------+-------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees_range_columns | p1,p2,p3,p4 | ALL  | NULL          | NULL | NULL    | NULL | 299495 |    11.11 | Using where |
+----+-------------+-------------------------+-------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

注意：RANGE COLUMNS 不支持使用 DATE 或 DATETIME 以外的日期类型的分区列。

2.2、列表分区（LIST）

MySQL中的列表分区在很多方面类似于范围分区。与按RANGE进行分区一样，必须明确定义每个分区。两种类型的分区之间的主要区别在于，在列表分区中，每个分区都是基于一组值列表中的一个而不是一组连续范围中的列值的成员来定义和选择的。
这是通过使用PARTITION BY LIST（expr）
（其中expr是列值或基于列值的表达式）并返回整数值，然后通过VALUES IN（value_list）定义每个分区来完成的，其中value_list是一个以逗号分隔的整数列表。

CREATE TABLE t_list (
    a INT NOT NULL,    
    b INT NOT NULL
)
PARTITION BY LIST(b ) (
    PARTITION p0 VALUES IN (3,5,6,9,17),
    PARTITION p1 VALUES IN (1,2,10,11,19,20)
);

插入数据:

insert into `t_list` (`a`, `b`) values('1','1');
insert into `t_list` (`a`, `b`) values('2','2');
insert into `t_list` (`a`, `b`) values('3','3');
insert into `t_list` (`a`, `b`) values('5','5');
insert into `t_list` (`a`, `b`) values('6','6');
insert into `t_list` (`a`, `b`) values('9','9');
insert into `t_list` (`a`, `b`) values('17','17');

查看查询计划：

mysql> EXPLAIN SELECT * FROM t_list WHERE b<10;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_list | p0,p1      | ALL  | NULL          | NULL | NULL    | NULL |    7 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

2.3、HASH分区（HASH）

通过HASH进行分区主要用于确保在预定数量的分区之间均匀分布数据。对于范围或列表分区，必须明确指定将给定列值或一组列值存储在哪个分区中；
对于HASH分区，您只需要根据要散列的列值以及要将分区表划分的分区数来指定列值或表达式。

要使用HASH分区对表进行分区，必须在 CREATE TABLE 语句后附加 PARTITION BY HASH（expr）子句，其中expr是返回整数的表达式。
如果是列的名称，该列的类型必须是MySQL的整数类型之一。
此外，您需要在此后加上 PARTITIONS num，其中num是一个正整数，表示表将被划分的分区数。

根据指定列值和指定分区数进行分区，示例中根据员工号将数据表划分为4个分区：

CREATE TABLE `employees_hash` (
  `emp_no` INT NOT NULL,
  `birth_date` DATE NOT NULL,
  `first_name` VARCHAR(14) NOT NULL,
  `last_name` VARCHAR(16) NOT NULL,
  `gender` ENUM('M','F') NOT NULL,
  `hire_date` DATE NOT NULL
) PARTITION BY HASH(emp_no)
PARTITIONS 4;

（1）HASH 分区可以不用指定 PARTITIONS 子句，如上文中的PARTITIONS 4，则默认分区数为1。

（2）不允许只写PARTITIONS，而不指定分区数。

（3）同RANGE分区和LIST分区一样，PARTITION BY HASH (expr)子句中的expr返回的必须是整数值。

（4）HASH分区的底层实现其实是基于MOD函数。比如，对于下表

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY HASH( YEAR(col3) )
    PARTITIONS 4;

如果你要插入一个col3为 ‘2005-09-15’ 的记录，则分区的选择是根据以下值决定的：

MOD(YEAR('2005-09-01'),4)
=  MOD(2005,4)
=  1

2.3.1、LINEAR HASH 分区

MySQL 还支持线性HASH，这与常规HASH的不同之处在于，线性HASH使用线性二乘幂算法，而常规HASH则使用散列函数值的模数。

CREATE TABLE `employees_linear_hash` (
  `emp_no` INT NOT NULL,
  `birth_date` DATE NOT NULL,
  `first_name` VARCHAR(14) NOT NULL,
  `last_name` VARCHAR(16) NOT NULL,
  `gender` ENUM('M','F') NOT NULL,
  `hire_date` DATE NOT NULL
) PARTITION BY LINEAR HASH( emp_no )
PARTITIONS 4;

（1）查找下一个大于2的幂。我们称这个值为V；可以计算为：

V = POWER(2, CEILING(LOG(2, num)))

假设num为13。则LOG（2,13）为3.7004397181411。CEILING（3.7004397181411）为4，而V = POWER（2,4）为16。

（2）N = F(column_list) & (V - 1)

（3）当N> = num时：设定 V = V / 2，设置N = N＆（V-1）

假设使用以下语句创建使用线性哈希分区并具有6个分区的表t1：

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY LINEAR HASH( YEAR(col3) )
    PARTITIONS 6;

现在假设您要在t1中插入两个记录，它们的col3列值为’2003-04-14’和’1998-10-19’。其中第一个的分区号如下：

V = POWER(2, CEILING( LOG(2,6) )) = 8
N = YEAR('2003-04-14') & (8 - 1)
   = 2003 & 7
   = 3

3> = 6 为FALSE：记录存储在分区 3 中

计算第二条记录所在的分区号，如下所示：

V = 8
N = YEAR('1998-10-19') & (8 - 1)
  = 1998 & 7
  = 6

(6 >= 6 是 TRUE:继续执行)

N = 6 & ((8 / 2) - 1)
  = 6 & 3
  = 2

(2 >= 6 是 FALSE: 记录存储在分区号 2 中)

它的优点是在数据量大的场景，比如TB级，增加、删除、合并和拆分分区会更快，缺点是，相对于HASH分区，它数据分布不均匀的概率更大。

2.4、KEY 分区（KEY）

KEY分区类似于 HASH 分区，不同点如下：

（1）KEY分区允许多列，而HASH分区只允许一列。

（2）如果在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。

（3）KEY分区对象必须为列，而不能是基于列的表达式。

（4）KEY分区和HASH分区的算法不一样，PARTITION BY HASH (expr)，MOD取值的对象是expr返回的值，而PARTITION BY KEY (column_list)，基于的是列的MD5值。

如果没有将列名指定为分区键，则使用表的主键（如果有）。

CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;

如果没有主键，但是有一个唯一键，则将唯一键用于分区键：

CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;

与其他分区类型不同，用于KEY分区的列不限于整数或NULL值。例如，以下CREATE TABLE语句有效：

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

分区键不支持带有索引前缀的列。这意味着CHAR，VARCHAR，BINARY和VARBINARY列可以在分区键中使用，只要它们不使用前缀即可。因为必须在索引定义中为BLOB和TEXT列指定前缀，所以不能在分区键中使用这两种类型的列。

在MySQL 8.0.21之前，创建，更改或升级分区表时允许使用前缀的列，即使它们未包含在表的分区键中也是如此；

在MySQL 8.0.21和更高版本中，不赞成这种行为，并且当使用一个或多个此类列时，服务器会显示适当的警告或错误。

对于 key 分区表，无法执行ALTER TABLE DROP PRIMARY KEY，因为这样做会产生错误ERROR 1466（HY000）：分区功能字段列表中的字段未在表中找到。

2.5、子分区（Subpartitioning）

子分区（也称为复合分区）是对分区表中每个分区的进一步划分。例如以下CREATE TABLE语句：

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

表ts具有3个RANGE分区。这些分区（p0，p1和p2）中的每一个都进一步分为2个子分区。实际上，整个表分为3 * 2 = 6个分区。

注意事项：

（1）每个分区必须具有相同数量的子分区。

（2）如果在分区表的任何分区上使用SUBPARTITION明确定义了任何子分区，则必须定义全部分区。以下语句将失败：

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s2,
            SUBPARTITION s3
        )
    );

（3）每个SUBPARTITION子句必须（至少）包括子分区的名称。否则，您需要为子分区设置所需的选项，或者允许其采用该选项的默认设置。

（4）子分区名称在整个表中必须唯一。例如，以下CREATE TABLE语句有效：

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        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
        )
);

（5）分区表的所有分区必须具有相同数量的子分区。一旦创建了表，就无法更改子分区。

3、MySQL分区如何处理NULL

在MySQL中进行分区并不能避免 NULL作为分区表达式的值，无论它是列值还是用户提供的表达式的值。请务必记住NULL不是数字。 MySQL的分区实现将NULL视为小于任何非NULL值，就像ORDER BY一样。

（1）LIST 分区，如果枚举列表里面不存在null值会插入失败

（2）RANGE 分区会将 NULL 作为最小分区值存储

（3）HASH 和 KEY 分区会将其转换成0存储

4、MySQL 分区管理

4.1、查看分区表

mysql> SHOW CREATE TABLE `employees_range_timestamp`;
+---------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+---------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees_range_timestamp | CREATE TABLE `employees_range_timestamp` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (unix_timestamp(`hire_date`))
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (31449600) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (347068800) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (662601600) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (1609372800) ENGINE = InnoDB) */ |
+---------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4.2、增加分区

mysql> ALTER TABLE employees_range_timestamp ADD PARTITION ( PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2030-12-31 00:00:00') ) );
Query OK, 0 rows affected (0.65 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.3、删除分区

mysql> ALTER TABLE employees_range_timestamp DROP PARTITION p5;
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.4、重建分区

这与删除存储在分区中的所有记录，然后重新插入它们的效果相同。这对于碎片整理很有用。

ALTER TABLE t1 REBUILD PARTITION p0, p1;

4.5、优化分区

如果从分区中删除了大量行，或者对具有可变长度行的分区表（即具有VARCHAR，BLOB或TEXT列）进行了许多更改，则可以使用ALTER TABLE … 优化分区以回收任何未使用的空间并对分区数据文件进行碎片整理。

ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;

在指定分区上使用 OPTIMIZE PARTITION 等效于在该分区上运行CHECK PARTITION，ANALYZE PARTITION和REPAIR PARTITION。

某些MySQL存储引擎（包括InnoDB）不支持按分区优化。在这种情况下，ALTER TABLE … OPTIMIZE PARTITION 将分析并重建整个表，并发出适当的警告。改为使用 ALTER TABLE … REBUILD PARTITION 和 ALTER TABLE … ANALYZE PARTITION 可以避免此问题。

4.6、分析分区

读取并存储分区key的分布。

ALTER TABLE t1 ANALYZE PARTITION p3;

4.7、修复分区

可以修复损坏的分区

ALTER TABLE t1 REPAIR PARTITION p0,p1;

通常，当分区包含重复的键错误时，REPAIR PARTITION 会失败。您可以将此选项与 ALTER IGNORE TABLE 一起使用，在这种情况下，由于存在重复键而无法移动的所有行将从分区中删除。

以上每个语句还支持关键字ALL来代替分区名称列表。使用ALL会使该语句作用于表中的所有分区。

可以使用 ALTER TABLE … TRUNCATE PARTITION 截断分区。该语句可用于删除一个或多个分区中的所有行，其方式与 TRUNCATE TABLE 删除表中的所有行的方式几乎相同。

ALTER TABLE … TRUNCATE PARTITION ALL 会截断表中的所有分区。

5、获取有关分区信息的方式

（1）使用 SHOW CREATE TABLE 语句查看用于创建分区表的分区语句。

（2）使用 SHOW TABLE STATUS 语句确定表是否已分区。

（3）查询 INFORMATION_SCHEMA.PARTITIONS 表。

（4）使用语句 EXPLAIN SELECT 可以查看给定 SELECT 使用的分区。

5.1、show create table 表名

mysql> SHOW CREATE TABLE `employees_range_timestamp`;
+---------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+---------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees_range_timestamp | CREATE TABLE `employees_range_timestamp` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (unix_timestamp(`hire_date`))
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (31449600) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (347068800) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (662601600) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (1609372800) ENGINE = InnoDB) */ |
+---------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5.2、show table status

查看表是不是分区表

mysql> SHOW TABLE STATUS LIKE '%employees_range_timestamp%';
+---------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| 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 |
+---------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| employees_range_timestamp | InnoDB |      10 | Dynamic    | 299690 |             59 |    17907712 |               0 |            0 |   8388608 |           NULL | 2020-05-26 09:27:39 | NULL        | NULL       | utf8mb4_0900_ai_ci |     NULL | partitioned    |         |
+---------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.10 sec)

5.3、查询 information_schema.partitions 表

查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息

mysql> SELECT
    -> partition_name part,
    -> partition_expression expr,
    -> partition_description descr,
    -> table_rows
    -> FROM
    -> information_schema.PARTITIONS
    -> WHERE
    -> table_schema = SCHEMA ()
    -> AND table_name = 'employees_range_timestamp';
+------+-----------------------------+------------+------------+
| part | expr                        | descr      | TABLE_ROWS |
+------+-----------------------------+------------+------------+
| p0   | unix_timestamp(`hire_date`) | 0          |          0 |
| p1   | unix_timestamp(`hire_date`) | 31449600   |          0 |
| p2   | unix_timestamp(`hire_date`) | 347068800  |          0 |
| p3   | unix_timestamp(`hire_date`) | 662601600  |     190059 |
| p4   | unix_timestamp(`hire_date`) | 1609372800 |     109631 |
+------+-----------------------------+------------+------------+
5 rows in set (0.00 sec)

5.4、explain partitions select

查看执行计划，是否命中分区及哪些分区等。

mysql> EXPLAIN SELECT * FROM employees_range_timestamp WHERE hire_date >= '1970-06-26' AND hire_date <='2020-12-01'; 
+----+-------------+---------------------------+-------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table                     | partitions  | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------------------------+-------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees_range_timestamp | p1,p2,p3,p4 | ALL  | NULL          | NULL | NULL    | NULL | 299690 |    11.11 | Using where |
+----+-------------+---------------------------+-------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

6、MySQL分区注意事项

（1）日期时间函数的优化：
TO_DAYS（），YEAR（）和TO_SECONDS（）函数经过优化，可在分区中使用。还可以使用返回整数或NULL值的日期和时间函数，例如WEEKDAY（），DAYOFYEAR（）或MONTH（）。

（2）分区标识： 分区始终按顺序编号，创建时会自动从0开始。使用分区号插入行以标识每一行的位置。例如，如果将一个表划分为四个，则MySQL将使用分区号0、1、2和3来标识每个分区。

（3）命名约定： 分区名称应遵循用于表和数据库的相同的MySQL命名约定。值得注意的是，分区名称不区分大小写。如果您尝试为同一个表命名两个分区，即“ myVertabeloPart”和“ myvertabelopart”，则会收到以下错误消息：
错误1488（HY000），重复的分区名称myvertabelopart

（4）数据和索引目录： 可以在创建过程中将分区分配给特定目录。您可以使用CREATE TABLE语句的PARTITION子句的DATA DIRECTORY和INDEX DIRECTORY选项来执行此操作。

（5）存储引擎： 同一表的分区必须使用相同的存储引擎。如果对MyISAM表进行分区，则所有分区都将是MyISAM。 InnoDB也是如此。

（6）索引分区： 分区适用于表中的所有数据和索引。您不能仅对数据进行分区，而不对索引进行分区，反之亦然。您也不能仅对表的一部分进行分区。

（7）外键： 分区的InnoDB表不支持外键。这对数据完整性的影响非常重要。您不能在分区表中添加外键（指向另一个表）。相反，如果表具有外键，则将无法对其进行分区。另外，非分区表不能具有指向分区表列的外键列。

（8）分区列： 分区表达式中使用的所有列必须是分区表中唯一键或主键的一部分。

7、参考文献

关注

3
点赞
踩
18

收藏

觉得还不错? 一键收藏
0
评论
MySQL（十三）：分区表（ Partitioning Table）

文章目录1、MySQL 分区简介1.1、什么是分区表1.2、分区表优点1.3、分区表缺点2、MySQL 分区类型2.1、范围分区（RANGE）2.1.1、基于时间间隔的分区方案。2.1.1.1、按 RANGE 对表进行分区2.1.1.2、按 RANGE COLUMNS 对表分区2.2、列表分区（LIST）2.3、HASH分区（HASH）2.3.1、LINEAR HASH 分区2.4、KEY 分区（KEY）2.5、子分区（Subpartitioning）3、MySQL分区如何处理NULL4、MySQL 分区管
复制链接

扫一扫