Mysql 分区表介绍

2018-04-24 01:032940原创mysql编辑删除

本文链接：https://www.cndba.cn/leo1990/article/2748

1 概述

什么是分区，分区呢就是把一张表的数据分成N多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上。

2 分区的优点：

1）分区可以在一个表中存储比单个磁盘或文件系统分区上的数据更多的数据。
2）通过删除仅包含该数据的分区（或多个分区），通常可以轻松地从分区表中删除失去其有用性的数据。相反，添加新数据的过程在某些情况下可以通过添加一个或多个新分区来专门存储该数据而大大方便。
3）由于满足给定WHERE子句的数据只能存储在一个或多个分区上，因此可自动排除搜索中剩余的分区，因此可以大大优化某些查询。由于可以在创建分区表后更改分区，因此可以重新组织数据以增强频繁查询，这些查询在首次设置分区方案时可能并不经常使用。这种排除不匹配的分区（以及它们包含的任何行）的功能通常称为 分区修剪。有关更多信息，请参见第22.4节“分区修剪”。
另外，MySQL 5.7支持查询显式分区选择。例如， SELECT * FROM t PARTITION (p0,p1) WHERE c < 5在分区仅选择那些行p0和 p1该匹配WHERE 条件。在这种情况下，MySQL不会检查表的其他分区t; 当你已经知道你想要检查的分区时，这可以大大加快查询速度。选择分区还支持数据修改语句 DELETE， INSERT， REPLACE， UPDATE，和LOAD DATA， LOAD XML。有关更多信息和示例，请参阅这些语句的描述。
通常与分区相关的其他好处包括以下列表中的好处。这些功能目前尚未在MySQL分区中实现，但在我们的优先级列表中处于首位。
4）涉及聚合函数的查询（如 SUM()和） COUNT()可以很容易地并行化。这种查询的一个简单例子可能是 SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id;。通过 “ 并行化 ”，我们的意思是可以在每个分区上同时运行查询，并且仅通过对所有分区获得的结果求和即可获得最终结果。
5）凭借在多个磁盘上传播数据，实现更高的查询吞吐量。

3 分区类型

3.1 范围分区

按范围分区的表以这样的方式进行分区，即每个分区包含分区表达式值位于给定范围内的行。范围应该是连续的但不重叠，并且使用VALUES LESS THAN操作符来定义 。
SQL 代码：

 

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( YEAR(joined) ) (
    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
);

3.2 列表分区

MySQL中的列表分区在很多方面类似于范围分区。在分区的分区中，每个分区必须显式地定义。这两种分区之间的主要区别在于，在列表分区中，每个分区都是根据一组价值列表中的一个列值的成员的成员来定义和选择的，而不是在一组相邻的值范围内。这是通过PARTITION BY LIST(expr)来完成的，expr是一个列值或基于列值的表达式，返回一个整数值，然后通过（value_listt）中的值来定义每个分区，其中value_list是一个逗号分隔的整数列表。
SQL 代码：

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

3.3 COLUMNS 分区

接下来的两节讨论 COLUMNS 分区，这些分区是变体 RANGE和LIST 分区。COLUMNS分区允许在分区键中使用多列。所有这些列都考虑到了将行放入分区以及确定哪些分区要在分区修剪中检查匹配行的目的。

3.3.1 RANGE COLUMNS分区

范围列分区与范围分区类似，但允许您使用基于多个列值的范围定义分区。另外，您可以使用整数类型以外的列来定义范围。

RANGE COLUMNS分区RANGE在以下几个方面与分区有很大不同：
RANGE COLUMNS 不接受表达式，只接受列的名称。

RANGE COLUMNS 接受一列或多列。
RANGE COLUMNS分区基于元组之间的比较 （列值列表），而不是标量值之间的比较。RANGE COLUMNS分区中行的放置也基于元组之间的比较; 这部分将在后面进一步讨论。

RANGE COLUMNS分区列不限于整数列; 字符串 DATE和 DATETIME列也可以用作分区列。
SQL 代码：

 

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

3.3.2 LIST COLUMNS分区

MySQL 5.7支持LIST COLUMNS分区。这是一种LIST分区的变体， 它支持使用多个列作为分区键，而将除整数类型以外的数据类型的列用作分区列; 你可以使用字符串类型 DATE，和 DATETIME列。
SQL 代码：

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

3.4 HASH分区

分区依据HASH主要用于确保数据在预定数量的分区中的均匀分布。使用范围或列表分区，您必须明确指定要存储给定列值或列值集合的分区; 使用散列分区，MySQL会为您处理此问题，并且您只需根据要进行散列的列值和分区表的分区数量指定列值或表达式即可。

您还可以使用返回整数的SQL表达式 expr。例如，您可能希望根据员工被聘用的年份进行分区。这可以如下所示完成：
SQL 代码：

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;

3.5 Key分区

按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的 哈希函数是由MySQL 服务器提供。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的 哈希函数，这些函数是基于与PASSWORD()一样的运算法则。
SQL 代码：

 

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

3.6 子分区

子分区 - 也称为 复合分区 - 是分区表中每个分区的进一步划分。
SQL 代码：
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-is进一步分成2子分区。实际上，整个表格被分成多个 3 * 2 = 6分区。但是，由于该PARTITION BY RANGE条款的作用，其中前两项仅存储purchased列中值小于1990的那些记录。
注意事项：
1）每个分区必须具有相同数量的子分区。
2）如果您使用SUBPARTITION分区表的任何分区显式定义任何子分区 ，则必须全部定义它们。
3）每个SUBPARTITION子句必须包含（至少）子分区的名称。否则，您可以为子分区设置任何所需的选项，或允许它为该选项采用其默认设置。
4）子分区名称在整个表格中必须是唯一的。