MySQL分区

2022/4/10 19:13:22

编程Tag： mysql null id int partition 分区表分区

本文主要是介绍MySQL分区，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

如果需要从非常大的表中查询出某一段时间的记录，而这张表中包含很多年的历史数据，数据是按照时间排序的，此时应该如何查询数据呢？
因为数据量巨大，肯定不能在每次查询的时候都扫描全表。考虑到索引在空间和维护上的消耗，也不希望使用索引，即使使用索引，会发现会产生大量的碎片，还会产生大量的随机IO，但是当数据量超大的时候，索引也就无法起作用了，此时可以考虑使用分区来进行解决。
全量扫描数据，不要任何索引
使用简单的分区方式存放表，不要任何索引，根据分区规则大致定位需要的数据为止，通过使用where条件将需要的数据限制在少数分区中，这种策略适用于以正常的方式访问大量数据。
索引数据，并分离热点
如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在内存中，这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效的使用缓存。
本文中的案例来自MySQL官网

1 概述

1.1 分区表的优势

分区可以将数据存储在一个表中，而不是单个磁盘或文件系统分区上的数据。
分区表的数据更容易维护。批量删除大量数据可以使用清除整个分区的方式；对一个独立分区进行优化、检查、修复等操作；
MySQL支持指定分区查询。例如，SELECT * FROM t PARTITION (p0,p1) WHERE c < 5选择在分区p0和p1中查询与WHERE条件匹配的行。在这种情况下，MySQL不会查询所有的分区；当我们已经知道数据在哪个分区时，这可以大大加快查询速度。数据修改语句DELETE、INSERT、REPLACE、UPDATE和LOAD DATA、LOAD XML也支持分区选择。

1.2 分区表的限制

一个表最多只能有1024个分区，在5.7版本的时候可以支持8196个分区；
如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
分区表无法使用外键约束
表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据；
分区表的数据更容易维护。批量删除大量数据可以使用清除整个分区的方式；对一个独立分区进行优化、检查、修复等操作；
分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备；
可以使用分区表来避免某些特殊的瓶颈（）

1.3 分区表原理

分区表由多个相关的底层表实现，这个底层表也是由句柄对象标识，我们可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引知识在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。分区表的操作按照以下的操作逻辑进行：
select查询
当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。
insert操作
当写入一条记录的时候，分区层先打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应底层表。
delete操作
当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作。
update操作
当更新一条记录时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录再哪个分区，然后取出数据并更新，再判断更新后的数据应该再哪个分区，最后对底层表进行写入操作，并对源数据所在的底层表进行删除操作
有些操作时支持过滤的，例如，当删除一条记录时，MySQL需要先找到这条记录，如果where条件恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉，这对update同样有效。如果是insert操作，则本身就是只命中一个分区，其他分区都会被过滤掉。mysql先确定这条记录属于哪个分区，再将记录写入对应得曾分区表，无须对任何其他分区进行操作。
虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁，例如innodb，则会在分区层释放对应表锁。

2 分区类型

2.1 范围分区

根据列值在给定范围内将行分配给分区。每个分区都包含行数据且分区的表达式在给定的范围内，分区的范围应该是连续的且不能重叠，可以使用values、less、than运算符来定义。
比如我们正常创建一张没有分区商店员工表

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

我们可以按商店ID进行分区，如下所示：

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

上面我们将商店分为4个区，分区情况说明如下：

分区	商店ID范围
P0	1~5
P1	6~10
P2	11~15
P3	16~21

注意：每个分区都是按从最低到最高顺序定义的。这是PARTITION BY RANGE语法的要求。

我们可以通过查看分区表创建的数据文件验证分区情况，如果不知道表数据存放位置可以使用show variables like 'data%' 查看，下图是没有使用分区创建的表：

删除之前创建的表后重新通过分区创建的磁盘数据情况如下图：

上面的分区创建存在一个小问题，如果商店增加到21个，会存在什么问题？

mysql> insert into employees values(100,"aa","bb","2020-01-01","2030-01-01",202021,20);
Query OK, 1 row affected (0.00 sec)

mysql> insert into employees values(101,"aa2","bb2","2020-01-01","2030-01-01",2020,21);
ERROR 1526 (HY000): Table has no partition for value 21
mysql>

通过上面的错误信息发现，当插入超过分区范围数据时，会报ERROR 1526 (HY000): Table has no partition for value 21错误，因为服务器不知道将其放置在哪里。可以使用less than maxvalue来避免此种情况，修改后的创建分区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 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
);

MAXVALUE表示store_id列值大于或等于16（定义的最高值）的行都存储在分区p3中。

刚我们按商店ID将员工信息进行了表分区，也可以根据实际业务情况进行范围分区，比如按员工岗位代码（即job_code列值的范围）对表进行分区。例如，两位数的岗位代码用于普通（店内）员工，三位数的代码用于办公室和支持人员，四位数的代码用于管理职位，可以使用以下语句创建分区表：

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 (job_code) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (10000)
);

还可以根据年月日等进行按需分区，下面建表语句是按照年进行分区：

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

2.2 列表分区

类似于按范围分区，区别在于列表分区是基于列值匹配一个离散值集合中的某个值来进行选择。
比如，员工表中的商店ID，分为不同的运营区域。区域信息如下

下面是按照区域进行员工表的

区域	商店ID
北	3,5,6,9,17
东	1、2、10、11、19、20
西	4、12、13、14、18
中	7、8、15、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,
    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)
);

注意：在MySQL 5.7中，在按列表分区时，必须是整数列表。

2.3 列分区

mysql从5.5开始支持column分区，可以认为i是range和list的升级版，在5.5之后，可以使用column分区替代range和list，但是column分区只接受普通列不接受表达式。具体案例可参考官网。

2.4 Hash分区

将指定的字段进行哈希计算后，存放到不同分区表中。分区数量必须指定或通过表达式获取，这个值必须是大于0的整数。比如按商店ID将员工表分成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(store_id)
PARTITIONS 4;

也可以通过员工受雇年份进行Hash分区:

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;

2.5 Key分区
类似于hash分区，Key分区的哈希算法由MySQL服务器提供，key分区指定的列必须是主键或非空唯一键。
比如按主键分区：

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;

2.5 子分区（复合分区）

复合分区是分区表中每个分区的进一步划分，在分区的基础之上，再进行分区后存储。例如：
比如将员工受雇年份进行范围分区，分成3个分区表，再根据天进行Hash分成2个区，一共是分成6个分区表（3*2）:

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(hired) )
    SUBPARTITION BY HASH( TO_DAYS(hired) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
);

注意：

每个分区必须具有相同数量的子分区;
如果分区表的任何分区上使用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
        )
    );

上面的分区中p1没有定义子分区。

每个子SUBPARTITION子句必须（至少）包含子分区的名称；
子分区名称必须在整个表中是唯一的。

这篇关于MySQL分区的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！