浅识MySQL数据库分区

浅识MySQL数据库分区

任志娟

（天津滨海职业学院，天津  300451）

摘要：在大型的数据应用场景下，MySQL作为一个关系型数据库管理系统（RDBMS）是非常受欢迎的。然而，MySQL在处理大量数据时会遇到瓶颈，为了解决这个问题，数据库的分区是一种有效的解决方案。

关键词：分区；分区类型；分区优缺点；分区表的原理

MySQL数据库分区是一种将大型数据表分割成多个较小的、更易于管理和查询的技术。通过对数据表进行分区，可以提高查询性能、数据管理和维护的效率。

一、分区概述

在MySQL数据库中，如果一个表的数据量太大，不仅查找数据效率低，而且难以找到一块集中的存储空间来存放。为了解决这个问题，MySQL推出了分区的功能。分区是在物理层面将一个表分割成许多个小块进行存储，这样在查找数据时，就不需要进行全表查找了，只需要知道这条数据所在的块号，然后根据块号直接定位后，去相应的块号找即可。另外，分区也可以将数据分配到不同的磁盘，以解决磁盘放不下整个表的问题。

MySQL从5.1版本开始支持数据库表的分区。分区就是指按照一定的规则，将一个表分解成多个小的、易于管理的部分。也就是说，分区是将一个大表根据条件分割成若干小表。

二、MySQL分区类型

以下是一些常见的 MySQL 分区类型：

1）Range 分区：根据特定的数值范围将数据分成不同的分区，Range分区是基于一个给定连续区间范围，把数据分配到不同的分区。

2）List 分区：根据指定的列值列表将数据分配到不同的分区。List分区类似于Range分区，区别在于List分区是基于列值匹配一个离散值集合中的某个值来选择分区，而Range分区是基于给定连续区间范围来选择分区。另外，List分区使用前提是分区所依据的字段的值不增加，且值的个数比较少。

3）Hash 分区：使用哈希函数将数据均匀地分布到不同的分区中。Hash分区是基于用户定义的表达式返回值来选择分区，该表达式对要插入到表中的行的列值进行哈希计算。

4）Key 分区：根据特定的列或列组合进行分区。Key分区类似于Hash分区，但Hash分区允许用户使用自定义表达式，而Key分区不允许，它需要使用MySQL提供的Hash函数，同时Hash分区只支持整数类型，而Key分区支持Blob和Text等其他类型。

5）Composite 分区：组合使用以上多种分区方式。

三、MySQL分区优缺点

在下面的场景中，分区可以起到非常大的作用：

1）表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他都是历史数据。

2）分区表的数据更容易维护。如果想批量删除大量数据可以使用清除整个分区的方式，另外，还可以对一个独立分区进行优化、检查、修复等操作。

3）分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。

4）可以使用分区表来避免某些特殊的瓶颈，如Innodb的单个索引的互斥访问，ext3文件系统的inode锁竞争等。

5）如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。

6）优化查询，在where字句中包含分区列时，可以只使用必要的分区来提高查询效率，同时在涉及sum()和count()这类聚合函数的查询时，可以在每个分区上面并行处理，最终只需要汇总所有分区得到的结果。

分区优点很多，但分区本身也有一些限制，情况如下：

1）一个表最多只能有1024个分区，MySQL5.6之后支持8192个分区。

2）在MySQL5.1中分区表达式必须是整数，或者是返回整数的表达式，在5.5之后，某些场景可以直接使用字符串列和日期类型列来进行分区。

3）如果分区字段中有主键或者唯一索引列，那么所有主键列和唯一索引列都必须包含进来，如果表中有主键或唯一索引，那么分区键必须是主键或唯一索引。

4）分区表中无法使用外键约束。

5）MySQL数据库支持的分区类型为水平分区，并不支持垂直分区，因此，MySQL数据库的分区中索引是局部分区索引，一个分区中既存放了数据又存放了索引，而全局分区是指的数据库放在各个分区中，但是所有的数据的索引放在另外一个对象中。

6）目前MySQL不支持空间类型和临时表类型进行分区，不支持全文索引。

四、分区表的原理

分区表是由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样，所有的底层表都必须使用相同的存储引擎。分区表的索引只是在各个底层表上各自加上一个相同的索引，从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

在分区表上可以进行数据操作。做select查询时，分区层先打开并锁住所有的底层表，优化器判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。做insert写入一条记录操作时，分区层打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应的底层表。做delete删除一条记录操作时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作。当做update更新一条数据操作时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，然后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。

虽然每个操作都会打开并锁住所有的底层表，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁，如：Innodb，则会在分区层释放对应的表锁，这个加锁和解锁过程与普通Innodb上的查询类似。

当然子分区在建立的时候，也需要注意以下几个问题：

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

2）只要在一个分区表的任何分区上使用subpartition来明确定义任何子分区，就必须在所有分区上定义子分区，不能漏掉一些分区不进行子分区。

3）每个subpartition子句必须包括子分区的一个名字。

4）子分区的名字必须是唯一的，不能在一张表中出现重名的子分区。

5）MySQL数据库的分区总是把null当作比任何非null更小的值，这和数据库中处理null值的order by操作是一样的，升序排序时null总是在最前面，因此对于不同的分区类型，MySQL数据库对于null的处理也各不相同。对于Range分区，如果向分区列插入了null，则MySQL数据库会将该值放入最左边的分区，注意，如果删除分区，分区下的所有内容都从磁盘中删掉了，null所在分区被删除，null值也就跟着被删除了。在List分区下要使用null，则必须显式地定义在分区的散列值中，否则插入null时会报错。Hash和Key分区对于null的处理方式和Range,List分区不一样，任何分区函数都会将null返回为0。

五、MySQL数据库分区示例

要在 MySQL 中创建分区表，可以使用CREATE TABLE语句，并指定分区的相关信息。例如，以下是一个使用 Range 分区的示例：

CREATE TABLE student (

    id INT NOT NULL,

    name VARCHAR(50) NOT NULL,

    register_date DATE NOT NULL

) ENGINE = InnoDB

PARTITION BY RANGE (YEAR(register_date)) (

    PARTITION s0 VALUES LESS THAN (1980),

    PARTITION s1 VALUES LESS THAN (2000),

    PARTITION s2 VALUES LESS THAN MAXVALUE

);

在上述示例中，根据register_date列的年份进行 Range 分区，将数据分成三个分区：s0包含1980年及以前的数据s1包含1980年到2000年之间的数据，s2包含2000年及以后的数据。

六、总结

MySQL数据库分区可以在查询时提供一些优势，例如可以只扫描相关分区而不是整张表，从而提高查询速度。但需要注意的是，分区并不总是适用于所有情况，并且在设计分区策略时需要仔细考虑数据分布、查询模式和维护成本等因素。

作者简介：任志娟（1971-），天津市人，天津滨海职业学院教师，副教授，主要从事数据库、程序设计等方面的教学和研究。