mysql分区表_我的学习教程

概念

表的不同部分作为单独的表存储在不同的位置。用户选择的完成数据划分的规则称为分区功能，在MySQL中可以是模数与一组范围或值列表的简单匹配，内部哈希函数或线性哈希函数。该函数根据用户指定的分区类型进行选择，并将用户提供的表达式的值作为其参数。此表达式可以是列值，作用于一个或多个列值的函数或一组一个或多个列值，具体取决于所使用的分区类型。
数据库分区的一种非常常见的用法是按日期分区数据。
分区名称不区分大小写
无论使用哪种分区，分区总是在创建时自动按顺序编号，并以0开头。当在分区表中插入新行时，正是这些分区号用于标识正确的分区。例如，如果你的表使用4个分区，这些分区编号0， 1，2，和 3。对于RANGE和 LIST分区类型，有必要确保为每个分区号定义一个分区。对于HASH分区，用户提供的表达式的求值必须大于的整数值0。对于KEY 分区时，MySQL服务器内部使用的哈希函数会自动解决此问题。
指定表的分区数时，该值必须表示为无前导零的正非零整数文字，不允许小数部分
水平分区：表的不同行可以分配给不同的物理分区
垂直分区：表的不同列被分配给不同的物理分区，MySQL 5.7不支持垂直分区

优点

分区使在一个表中存储的数据比在单个磁盘或文件系统分区中存储的数据更多。
通常，通过删除仅包含该数据的一个或多个分区，可以轻松地从分区表中删除失去其用途的数据。相反，在某些情况下，通过添加一个或多个用于专门存储该数据的新分区，可以大大简化添加新数据的过程。
满足以下条件的某些查询可以大大优化：满足给定WHERE子句的数据只能存储在一个或多个分区上，这会自动从搜索中排除任何剩余的分区。由于可以在创建分区表之后更改分区，因此您可以重新组织数据以增强在首次设置分区方案时可能不经常使用的频繁查询。这种排除不匹配分区（以及因此包含的任何行）的能力通常称为分区修剪
MySQL支持显式的分区选择查询。例如， SELECT * FROM t PARTITION (p0,p1) WHERE c < 5仅选择那些在分区p0和p1中匹配的WHERE 条件。在这种情况下，MySQL不检查table的任何其他分区；当您已经知道要检查的分区时，这可以大大加快查询速度。

分区类型

RANGE分区

分区表达式为：每个分区包含分区表达式值位于给定范围内的行。范围应该是连续的，但不能重叠，并且是使用VALUES LESS THAN运算符定义的；如果不指定VALUES LESS THAN MAXVALUE并且插入列的值超过了所有分区允许的值插入语句将会发生错误。在MySQL 5.7中，TIMESTAMP列不允许使用除UNIX_TIMESTAMP外的任何其他涉及值的表达式

store_id小于6的在p0中，大于6小于11的在p1中，大于11小于16的在p2中，大于16的在p3中，如果不指定VALUES LESS THAN MAXVALUE指定分区，那么大于16的值将会发生错误

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    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
);

使用年份表达式分区

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

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('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

使用场景

分区键为日期或是时间类型
搜索查询中都包括分区键
定期按分区范围清理历史数据
当满足以下一个或多个条件时，RANGE分区特别有用

想要或需要删除“旧”数据，只需ALTER TABLE employees DROP PARTITION p0; 删除与在1991年前的所有行
要使用包含日期或时间值或包含其他系列值的列
经常运行直接取决于用于对表进行分区的列的查询。例如，当执行诸如EXPLAIN SELECT COUNT(*) FROM employees WHERE separated BETWEEN '2000-01-01' AND '2000-12-31' GROUP BY store_id的查询时，MySQL可以快速确定仅需要扫描分区p2，因为其余分区不能包含满足该WHERE子句的任何记录

RANGE COLUMNS分区

这种分区是RANGE分区的一种变体。
通过RANGE COLUMNS进行分区，可以使用多个列来定义分区范围。可以使用非整数类型的列来定义范围
RANGE COLUMNS 不接受表达式，仅接受列名。RANGE COLUMNS 接受一个或多个列的列表
分区列列表和值列表中定义每个分区的元素必须以相同的顺序出现。另外，值列表中的每个元素必须与列列表中的相应元素具有相同的数据类型。但是，分区列列表和值列表中列名称的顺序不必与CREATE TABLE语句中表列定义的顺序相同。与使用分区表一样RANGE，您可以使用MAXVALUE来表示一个值，以使插入给定列的任何合法值始终小于该值
因为不同的字符集和排序规则具有不同的排序顺序，所以RANGE COLUMNS在使用字符串列作为分区列时，使用的字符集和排序规则可能会影响按给定行划分的表的哪个分区存储在其中。
指定多列为分区列时只有当插入语句中的所有分区列的值都大于指定分区列的值才存入下一个分区
允许的数据类型显示在以下列表中
所有整数类型：TINYINT， SMALLINT， MEDIUMINT， INT （INTEGER），和 BIGINT
DATE和 DATETIME。
字符串类型：CHAR， VARCHAR， BINARY，和 VARBINARY。
DECIMAL，FLOAT，TEXT BLOB不支持作为分区列，不支持使用date或DATETIME以外的日期或时间类型的分区列

CREATE TABLE table_name
PARTITIONED BY RANGE COLUMNS(column_list) (
    PARTITION partition_name VALUES LESS THAN (value_list)[,
    PARTITION partition_name VALUES LESS THAN (value_list)][,
    ...]
)
column_list:
    column_name[, column_name][, ...]

value_list:
    value[, value][, ...]

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
    PARTITION p0 VALUES LESS THAN ('1960-01-01'),
    PARTITION p1 VALUES LESS THAN ('1970-01-01'),
    PARTITION p2 VALUES LESS THAN ('1980-01-01'),
    PARTITION p3 VALUES LESS THAN ('1990-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

mysql> CREATE TABLE rcx (
         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)
     );

mysql> INSERT INTO rx VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME,TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'rx';
+--------------+----------------+------------+
| TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS |
+--------------+----------------+------------+
| p            | p0             |          0 |
| p            | p1             |          3 |
+--------------+----------------+------------+

以下语句将会发生错误

CREATE TABLE rc4 (
    a INT,
    b INT,
    c INT
)
PARTITION BY RANGE COLUMNS(a,b,c) (
    PARTITION p0 VALUES LESS THAN (0,25,50),
    PARTITION p1 VALUES LESS THAN (10,20,100),
    PARTITION p2 VALUES LESS THAN (10,30,50)
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
 );

 mysql> CREATE TABLE rcf (
    ->     a INT,
    ->     b INT,
    ->     c INT
    -> )
    -> PARTITION BY RANGE COLUMNS(a,b,c) (
    ->     PARTITION p0 VALUES LESS THAN (0,25,50),
    ->     PARTITION p1 VALUES LESS THAN (20,20,100),
    ->     PARTITION p2 VALUES LESS THAN (10,30,50),
    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
    ->  );
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

LIST分区

每个分区是根据一组值列表中的列值的成员关系来定义和选择的；这是通过使用PARTITION by LIST(expr)来实现的，其中expr是一个列值或一个基于列值的表达式并返回一个整数值，然后通过(value_list)中的值来定义每个分区，其中value_list是一个用逗号分隔的整数列表。
LIST分区很多方面类似于RANGE分区，必须明确定义每个分区。
如果INSERT语句中的列值与分区列值不匹配将会报错

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    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)
);

使用场景：用户每次登陆都会记录customer_login_log日志，用户登陆日志保存一年，一年后删除

LIST COLUMNS分区

MySQL 5.7提供了对LIST COLUMNS分区的支持。这是LIST分区的一种变体，使您可以将整数类型以外的其他类型的列用于分区列，以及将多个列用作分区键。
它允许将多个列用作分区键，并且可以将整数类型以外的数据类型的列用作分区列；
允许的数据类型显示在以下列表中
所有整数类型：TINYINT， SMALLINT， MEDIUMINT， INT （INTEGER），和 BIGINT
DATE和 DATETIME。
字符串类型：CHAR， VARCHAR， BINARY，和 VARBINARY。
DECIMAL，FLOAT，TEXT BLOB不支持作为分区列，不支持使用date或DATETIME以外的日期或时间类型的分区列

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', 'Högsby', 'Mönsterås'),
    PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
    PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
    PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
);

HASH分区

分区依据HASH主要用于确保在预定数量的分区之间均匀分布数据。对于RANGE或LIST分区，必须明确指定将给定列值或一组列值存储在哪个分区中；使用HASH分区，MySQL会为您解决这一问题，您只需要基于要散列的列值和要划分的分区表的分区数来指定列值或表达式。表达式必须返回一个非恒定的，非随机的整数值
使用HASH partitioning对表进行分区时，需要在CREATE table语句后附加一个partition BY HASH (expr)子句，其中expr是一个返回整数的表达式。这可以是MySQL的整数类型之一的列的名称。此外，您很可能希望在此之后加上分区num，其中num是一个正整数，表示表要分成的分区数。不指定num则默认为1个分区

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

最有效的哈希函数是对单个表列进行操作，并且该列的值随列值的增加或减少保持一致，因为这允许对分区范围进行“修剪”。也就是说，表达式越随它所基于的列的值而变化，MySQL使用表达式进行哈希分区的效率就越高。
例如，date_col是date类型的列，那么TO_DAYS(date_col)表达式将直接随date_col的值而变化，因为对于date_col值的每次变化，表达式的值都会以一致的方式变化。表达式YEAR(date_col)相对于date_col的方差不像TO_DAYS(date_col)那样直接，因为不是date_col中的每一个可能的变化都会在YEAR(date_col)中产生等效的变化。尽管如此，YEAR(date_col)是一个很好的哈希函数的候选者，因为它直接随date_col的一部分而变化，而且date_col的变化不可能产生与YEAR(date_col)不成比例的变化。
列值与表达式的值的曲线越接近方程式所描绘的直线，则表达式越适合哈希。表达式越非线性，它倾向于产生的分区之间的数据分布就越不均匀
从理论上讲，也可以对涉及多个列值的表达式进行修剪，但是要确定哪种表达式合适则可能非常困难且耗时。因此，不建议特别使用涉及多列的哈希表达式。

计算插入数据所在的分区

当使用HASH分区时，MySQL根据表达式的结果的模数来决定使用num分区中的哪个分区。换句话说，对于给定表达式expr，存储记录的分区号是N，其中N = MOD(expr, num)

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

LINEAR HASH分区

MySQL 5.7还支持HASH分区的变体称为线性哈希的分区，该变体采用更复杂的算法来确定插入分区表中的新行的位置
线性散列使用线性二乘幂算法，而常规散列则使用散列函数值的模数
通过线性哈希进行分区的优势在于，可以更快，更快速地添加，删除，合并和分割分区，这在处理包含大量数据（兆兆字节）的表时可能是有益的。缺点是，与使用常规哈希分区获得的分布相比，数据不太可能在分区之间均匀分布。

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

计算插入数据所在的分区

找出比num大2的下一个幂V
V = POWER(2, CEILING(LOG(2,6))) = 8
N = YEAR('2003-04-14') & (8 - 1)
   = 2003 & 7
   = 3

KEY分区

类似于按HASH分区，除了在HASH分区采用用户定义的表达式的情况下，用于KEY分区的HASH函数由MySQL服务器提供
与其他分区类型不同，用于分区依据的列KEY不限于整数或NULL值
KEY只接受零个或多个列名的列表。如果表具有一个主键，则用作分区键的任何列都必须包含表的主键的部分或全部。如果没有将列名指定为分区键，则使用表的主键（如果有）
以下 CREATE TABLE语句在MySQL 5.7中有效：

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

如果没有主键，但是有一个唯一键，则将唯一键用于分区键，唯一键必须定位为not null

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

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

KEY分区不支持带有索引前缀的列。这意味着，CHAR、VARCHAR、BINARY和VARBINARY列可以在分区键中使用，只要它们不使用前缀;因为必须为索引定义中的BLOB和TEXT列指定前缀，所以不可能在分区键中使用这两种类型的列。在MySQL 5.7中，当创建、修改或升级分区表时，允许使用前缀的列，即使它们不包含在表的分区键中。这在MySQL 5.7中是一个已知的问题，在MySQL 8.0中已经解决了，在这里这种允许的行为是不赞成的，并且当试图在这些情况下使用这些列时，服务器会显示适当的警告或错误

LINEAR KEY分区

使用LINEAR KEY分区的影响与LINEAR HASH分区的影响相同，分区号是使用2的幂数算法而不是取模算法得出的

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

子分区

子分区（也称为复合分区）是分区表中每个分区的进一步划分
在MySQL 5.7中，可以对通过RANGE或LIST进行分区的表进行子分区。子分区可以使用HASH或KEY分区

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

这些分区中的每个分区p0，p1和p2进一步分为2个子分区。实际上，整个表分为多个 3 * 2 = 6分区。但是，由于该PARTITION BY RANGE子句的作用，其中的前2个仅将值小于1990的记录存储在该purchased列中。
SUBPARTITION BY HASH和 SUBPARTITION BY KEY一般遵循相同的语法规则PARTITION BY HASH和 PARTITION BY KEY分别。唯一的例外是SUBPARTITION BY KEY（不同于PARTITION BY KEY）当前不支持默认列，因此即使表具有显式主键，也必须指定用于此目的的列。

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

以上语法必须遵循以下原则

每个分区必须具有相同数量的子分区
如果SUBPARTITION在分区表的任何分区上使用显式定义任何子分区，则必须在所有分区中显示定义子分区
每个SUBPARTITION子句必须（至少）包括子分区的名称。否则，您可以为子分区设置任何所需的选项，或允许其采用该选项的默认设置。
子分区名称在整个表中必须唯一

将每个子分区分配到不同的磁盘

CREATE TABLE ts (id INT, purchased DATE)
    ENGINE = MYISAM
    PARTITION BY RANGE(YEAR(purchased))
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0a
                DATA DIRECTORY = '/disk0'
                INDEX DIRECTORY = '/disk1',
            SUBPARTITION s0b
                DATA DIRECTORY = '/disk2'
                INDEX DIRECTORY = '/disk3'
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s1a
                DATA DIRECTORY = '/disk4/data'
                INDEX DIRECTORY = '/disk4/idx',
            SUBPARTITION s1b
                DATA DIRECTORY = '/disk5/data'
                INDEX DIRECTORY = '/disk5/idx'
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s2a,
            SUBPARTITION s2b
        )
    );

当使用NO_DIR_IN_CREATE 的SQL模式时，分区定义中不允许使用数据目录和索引目录选项。在MySQL 5.7中，定义子分区时也不允许使用这些选项

分区处理NULL值

MySQL中的分区不会禁止NULL作为分区表达式的值，不管它是列值还是用户提供的表达式的值。尽管允许使用NULL作为表达式的值，否则表达式必须产生一个整数，但重要的是要记住，NULL不是一个数字。MySQL的分区实现将NULL视为小于任何非NULL值，正如ORDER BY所做的那样。
NULL在不同类型的分区之间对的处理会有所不同

使用RANGE分区处理NULL

如果将行插入到按RANGE分区的表中，这样用于确定分区的列值为NULL，则该行将插入到最低的分区中

使用LIST分区处理NULL

当且仅当其中一个分区使用包含NULL的值列表定义时，使用LIST进行分区的表才允许空值；相反，一个由LIST分区的表如果没有在值列表中显式地使用NULL，就会拒绝分区表达式的行

使用HASH和KEY分区处理NULL

NULL对于由HASH或KEY分区的表，其处理方式有所不同。在这些情况下，任何产生NULL值的分区表达式都将被视为其返回值为零。我们可以通过检查创建按散列分区的表并使用包含适当值的记录填充表对文件系统的影响来验证这一行为

分区管理

RANGE和LIST分区

CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (1980),
    PARTITION p1 VALUES LESS THAN (1990),
    PARTITION p2 VALUES LESS THAN (2000)
);

查询分区2的内容

SELECT * FROM tr PARTITION (p2);

删除指定分区并删除分区中的数据，可以删除RANGE或LIST分区，不能删除HASH或KEY分区

ALTER TABLE tr DROP PARTITION p2;

添加一个新RANGE分区，只能在分区列表最顶端增加新分区

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));

添加一个新LIST分区，新分区不能包含老分区列表中的值

ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

重组分区，将一个分区拆分为两个分区,不会丢失数据

ALTER TABLE members
    REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1970),
        PARTITION n1 VALUES LESS THAN (1980)
);

重组分区，将多个分区合并为多个分区,不会丢失数据

ALTER TABLE tbl_name
    REORGANIZE PARTITION partition_list
    INTO (partition_definitions);
ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
    PARTITION m0 VALUES LESS THAN (1980),
    PARTITION m1 VALUES LESS THAN (2000)
);

ALTER TABLE ... REORGANIZE PARTITION针对于RANGE和LIST的关键点：

PARTITION用于确定新分区方案的选项应遵循与CREATE TABLE语句所使用的规则相同的规则。新的RANGE分区方案不能有任何重叠范围。一个新的LIST 分区方案不能有任何重叠的值集。
partition_definitions列表中分区的组合应与partition_list中命名的组合分区相同的范围或整体值集。
对于RANGE分区的表，您只能重组相邻的分区。您不能跳过范围分区。
您不能用于REORGANIZE PARTITION更改表使用的分区类型（例如，您不能将RANGE 分区更改为HASH分区）。您也不能使用此语句更改分区表达式或列。要完成这两项任务中的任何一项而无需删除并重新创建表，可以使用ALTER TABLE ... PARTITION BY ...

更改分区类型或分区表达式

ALTER TABLE members
    PARTITION BY HASH( YEAR(dob) )
    PARTITIONS 8;

HASH和KEY分区管理

CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;

修改分区数量，只能将分区数量减少，如果指定数量大于表定义的分区数量将报错

ALTER TABLE clients COALESCE PARTITION 4;

添加分区数量

ALTER TABLE clients ADD PARTITION PARTITIONS 6;

交换分区

将分区中的行与其他表中的行进行交换

ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt;

要求

表nt本身没有分区。
表nt不是临时表。
表pt和nt的结构在其他方面是相同的。
表nt不包含任何外键引用，其他表也不包含任何引用nt的外键。
在nt中没有行位于p的分区定义边界之外。如果使用了WITHOUT VALIDATION选项，则不适用此条件。
对于InnoDB表，两个表使用相同的行格式。InnoDB表的行格式可以通过查询INFORMATION_SCHEMA.INNODB_SYS_TABLES来确定。
nt没有任何使用数据目录选项的分区。这个限制在MySQL 5.7.25及以后的版本中被取消了。

执行ALTER TABLE…EXCHANGE PARTITION不会在已分区的表或要交换的表上调用任何触发器。
交换表中的任何AUTO_INCREMENT列都被重置。
一个ALTER TABLE EXCHANGE PARTITION语句中只有一个分区或一个子分区可以与一个非分区表交换。要交换多个分区或子分区，请使用多个 ALTER TABLE EXCHANGE PARTITION语句。EXCHANGE PARTITION可能无法与其他ALTER TABLE选项结合使用
在未分区表中找到的任何行都必须满足将它们存储在目标分区中的条件。否则，该语句将失败
如果对表进行了分区，则只能与未分区的表交换该表的一个子分区，而不是整个分区
MySQL使用的表结构的比较非常严格。分区表和未分区表的数目，顺序，名称以及列和索引的类型必须完全匹配。此外，两个表必须使用相同的存储引擎

修饰符

WITHOUT VALIDATION：交换未分区表的分区时，不会执行逐行验证，可以减少大量数据交换时的时间
WITH VALIDATION：默认行为，交换未分区表的分区时，执行逐行验证

分区维护

分区维护时使用ALL关键字将使语句应用于所有分区

截断分区，删除分区中的所有数据

ALTER TABLE ... TRUNCATE PARTITION p0;
ALTER TABLE ... TRUNCATE PARTITION ALL;

重建分区：删除存储在分区中的所有记录，这对于碎片整理很有用。

ALTER TABLE t1 REBUILD PARTITION p0, p1;
ALTER TABLE t1 REBUILD PARTITION ALL;

优化分区：回收任何未使用的空间和整理分区数据文件

ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;
ALTER TABLE t1 OPTIMIZE PARTITION ALL;

分析分区：读取并存储分区的密钥分布

ALTER TABLE t1 ANALYZE PARTITION p3;
ALTER TABLE t1 ANALYZE PARTITION ALL;

修复分区：这将修复损坏的分区，可以修复交换分区时不匹配的行；当分区包含重复的键错误时失败；在MySQL 5.7.2和更高版本中，指定IGNORE选项将从分区中删除由于存在重复键而无法移动的所有行

ALTER TABLE t1 REPAIR PARTITION p0,p1;
ALTER IGNORE TABLE t1 REPAIR PARTITION p0,p1;
ALTER TABLE t1 REPAIR PARTITION ALL;

检查分区：检查分区中的错误，在MySQL 5.7.2和更高版本中，指定IGNORE选项将会返回分区中发现重复键冲突的每一行的内容。只报告表的分区表达式中列的值

ALTER TABLE trb3 CHECK PARTITION p1;
ALTER IGNORE TABLE trb3 CHECK PARTITION p1;
ALTER TABLE trb3 CHECK PARTITION ALL;

分区选择

SELECT、DELETE、INSERT、REPLACE、UPDATE、LOAD DATA、LOAD XML可以自己选择分区
使用PARTITION ( partition_name, ...)指定分区列表，分区不存在将报错，当指定分区时仅在指定的分区查找数据

SELECT * FROM employees PARTITION(p1, p2);
SELECT e.id AS 'Employee ID', CONCAT(e.fname, ' ', e.lname) AS Name, s.city AS City, 
    d.name AS department
     FROM employees AS e JOIN stores PARTITION(p1) AS s ON e.store_id=s.id  JOIN departments
     PARTITION (p0) AS d ON e.department_id=d.id
    ORDER BY e.lname;
DELETE FROM employees PARTITION (p0, p1) WHERE fname LIKE 'j%';
UPDATE employees PARTITION (p0) SET store_id = 2 WHERE fname = 'Jill';
INSERT INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 1, 3);

分区表达式中仅允许以下列表中的MySQL函数

ABS()
CEILING()
DATEDIFF()
DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
EXTRACT()
FLOOR()
HOUR()
MICROSECOND()
MINUTE()
MOD()
MONTH()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
TO_SECONDS()
UNIX_TIMESTAMP()（带 TIMESTAMP列）
WEEKDAY()
YEAR()
YEARWEEK()
分区修剪支持
TO_DAYS()
TO_SECONDS()
YEAR()
UNIX_TIMESTAMP()

分区修剪

查找指定分区的数据与扫描表中的所有分区相比，查找匹配行所花费的时间和精力可能要少得多。这种“切掉” 不需要的分区被称为修剪。当优化器在执行此查询时可以使用分区修剪，对于包含相同列定义和数据的未分区表，查询的执行速度可以比同一查询快一个数量级。
只要WHERE条件可以减少到以下两种情况之一，优化器就可以执行修剪：

partition_column = constant
partition_column IN (constant1, constant2, ..., constantN)

在第一种情况下，优化器仅对给定值的分区表达式求值，确定哪个分区包含该值，然后仅扫描该分区。在许多情况下，等号可以与另一个算术比较来代替，包括<，>， <=，>=，和 <>。使用某些查询WHERE子句中也可以利用BETWEEN分区修剪。
在第二种情况下，优化器为列表中的每个值评估分区表达式，创建匹配分区的列表，然后仅扫描此分区列表中的分区。

验证

是否支持分区

mysql> SHOW PLUGINS;
+------------+----------+----------------+---------+---------+
| Name       | Status   | Type           | Library | License |
+------------+----------+----------------+---------+---------+
| partition  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
+------------+----------+----------------+---------+---------+

mysql> SELECT
    ->     PLUGIN_NAME as Name,
    ->     PLUGIN_VERSION as Version,
    ->     PLUGIN_STATUS as Status
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_TYPE='STORAGE ENGINE';
+--------------------+---------+--------+
| Name               | Version | Status |
+--------------------+---------+--------+
| partition          | 1.0     | ACTIVE |
+--------------------+---------+--------+

命令

查看表的所有分区中数据量

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';

删除表分区，但不删除数据

ALTER TABLE es2 REMOVE PARTITIONING;

将表空间从一个正在运行的MySQL服务器实例复制到另一个正在运行的实例，或者在同一个实例上执行恢复操作变得很容易。这两个选项都接受一个或多个以逗号分隔的分区名的列表

ALTER TABLE tbl_name DISCARD PARTITION {partition_names | ALL} TABLESPACE
ALTER TABLE tbl_name IMPORT PARTITION {partition_names | ALL} TABLESPACE

在MySQL 5.7.9及以后的版本中，将使用旧的通用分区处理程序创建的已分区InnoDB表升级到MySQL 5.7.6及以后版本中使用的InnoDB本地分区。同样从MySQL 5.7.9开始，mysql_upgrade实用程序检查这些分区的InnoDB表，并尝试将它们升级到本地分区，这是其正常操作的一部分。
没有使用InnoDB本地分区处理程序的已分区InnoDB表不能在MySQL 8.0或更高版本中使用。ALTER TABLE ... UPGRADE PARTITIONING在MySQL 8.0及以上版本不支持;因此，在将MySQL安装升级到MySQL 8.0或更高版本之前，任何使用通用处理程序的分区InnoDB表都必须升级到InnoDB本地处理程序。

ALTER TABLE tbl_name UPGRADE PARTITIONING

注意

将表与非本地分区一起使用会产生 ER_WARN_DEPRECATED_SYNTAX警告。在MySQL 5.7.17到5.7.20中，服务器会在启动时自动执行检查以识别使用非本地分区的表。对于找到的任何内容，服务器将一条消息写入其错误日志。使用 --disable-partition-engine-check选项可以禁用此检查。在MySQL 5.7.21和更高版本中，不执行此检查；如果希望服务器使用通用分区处理程序检查表使用--disable-partition-engine-check=false来启动服务器。
从MySQL 5.7.17开始，当用于给定表的存储引擎预计将提供本机分区处理程序，MySQL服务器中的通用分区处理程序已弃用，并在MySQL 8.0中删除。

限制

分区表达式不允许存储过程、存储函数、UDF、插件、用户定义的变量
允许在分区表达式中使用算术操作符+、-和*。然而，结果必须是一个整数值或NULL(除非在LINEAR KEY分区的情况下）。还支持DIV操作符，不允许使用/操作符。位运算符|，&，^，<<，>>和~不允许出现在分区表达式中。
在创建分区表之后更改SQL模式都可能导致此类表的行为发生重大变化，并且很容易导致数据损坏或丢失。由于这些原因，强烈建议您不要在创建分区表之后更改服务器SQL模式。
性能注意事项

应该确保large_files_support已启用并且open_files_limit已正确设置。对于使用MyISAM存储引擎的分区表增加myisam_max_sort_file_size 可能会提高性能；InnoDB通过启用innodb_file_per_table可以使涉及表的分区和重新分区操作更加有效。
对于MyISAM分区表，对于每个打开的表，MySQL均对每个分区使用2个文件描述符。这意味着您需要更多的文件描述符来对分区MyISAM表执行操作

通常，对表执行分区操作的进程对表进行写锁定。从此类表中读取的数据不受影响
MyISAM表的分区操作、查询和更新操作通常比InnoDB或NDB表更快。
对于不使用NDB存储引擎的表，最大分区数为8192。此数目包括子分区
InnoDB分区表不支持外键，InnoDB定义的分区表不得包含外键引用
ALTER TABLE ... ORDER BY仅导致每个分区内的行排序
分区表不支持FULLTEXT索引或搜索
不能在分区表中使用具有空间数据类型（例如POINT或GEOMETRY）的列
临时表无法分区
无法对日志表进行分区
分区键必须是整数列或解析为整数的表达式。不能使用ENUM列的表达式。列或表达式值也可以为空。此限制有两个例外：
当通过[LINEAR] KEY进行分区时，可以使用除TEXT或BLOB以外的任何有效MySQL数据类型的列用作分区键，因为MySQL的内部键哈希函数会从这些类型中生成正确的数据类型
过RANGE COLUMNS或LIST COLUMNS进行分区时，可以使用字符串，DATE，和 DATETIME列。
分区键可能不是子查询，即使该子查询解析为整数值或NULL
KEY分区不支持列索引前缀
子分区必须使用HASH或KEY分区。只有RANGE和LIST分区可以再分区；HASH和KEY分区不能再分区
DATA DIRECTORY和INDEX DIRECTORY与分区表一起使用时受到以下限制

表级DATA DIRECTORY和INDEX DIRECTORY选项将被忽略
在Windows上，MyISAM表的单个分区或子分区不支持DATA DIRECTORY和INDEX DIRECTORY选项。但是可以将DATA DIRECTORY用于InnoDB表的单个分区或子分区。

分区表的分区表达式中使用的所有列都必须是该表可能具有的每个唯一键的一部分。表上的唯一键必须是分区表达式中的列
分区表与MERGE存储引擎不兼容。使用MERGE存储引擎的表无法分区。分区表不能合并
FEDERATED存储引擎不支持表分区
CSV存储引擎不支持表分区
InnoDB不支持将多个磁盘用于子分区
KEY（包括 LINEAR KEY）分区是NDB存储引擎支持的唯一分区类型
在MySQL 5.7中，同一分区表的所有分区都必须使用相同的存储引擎。

mysql分区表

概念

优点

分区类型

RANGE分区

使用场景

RANGE COLUMNS分区

LIST分区

LIST COLUMNS分区

HASH分区

计算插入数据所在的分区

LINEAR HASH分区

计算插入数据所在的分区

KEY分区

LINEAR KEY分区

子分区

将每个子分区分配到不同的磁盘

分区处理NULL值

使用RANGE分区处理NULL

使用LIST分区处理NULL

使用HASH和KEY分区处理NULL

分区管理

RANGE和LIST分区

HASH和KEY分区管理

交换分区

要求

修饰符

分区维护

分区选择

分区表达式中仅允许以下列表中的MySQL函数

分区修剪

验证

命令

注意

限制

CATALOG

标签