MySQL分区

绝地灬酷狼 2022-02-13 13:11 232阅读 0赞

## 一分区概述 ##

### 1简介 ###

Mysql 5.1版本开始支持分区。分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库而言，从逻辑上讲，只有一个表或索引，但是在物理上这个表或者索引可能由多个物理分区组成。

Mysql数据库支持水平分区，不支持垂直分区。Mysql数据库的分区是局部分区索引。

水平分区指将同一个表不同行的记录分配到不同的物理文件中。

垂直分区值将同一个表不同列的记录分配到不同的物理文件中。

局部分区索引指一个分区中既存放数据有存放索引。

全局分区索引指数据存放在各个分区中，但是所有数据的索引存放在一个对象中。

### 2分区表的优点 ###

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

和单个磁盘或者文件系统相比，可以存储更多数据。

优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。

分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。

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

### 3分区表的限制 ###

在5.6.7之前的版本，一个表最多有1024个分区；从5.6.7开始，一个表最多可以有8192个分区。

MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

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

### 4分区注意事项 ###

无论是何种类型的分区，如果表中存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分。

如果表中没有指定主键或者唯一索引，可以指定任何列作为分区列。

唯一索引允许是Null的，并且分区列只要是唯一索引的一个组成部分即可。

## 二分区类型 ##

### 1分区类型 ###

RANGE分区：按照列值的区间范围分区。

LIST分区：与RANGE类似，区别是LIST面向的是离散的值。

HASH分区：根据用户自定义的表达式进行分区，表达式返回值不能是负数。

KEY分区：根据Mysql数据库提供的哈希函数进行分区。

Mysql5.5开始支持RANGE COLUMNS分区和LIST COLUMNS分区；

注意：在MySQL5.1版本中，RANGE,LIST,HASH分区要求分区键必须是INT类型，或者通过表达式返回INT类型。但KEY分区的时候，可以使用其他类型的列（BLOB，TEXT类型除外）作为分区键。

### 2RANGE分区 ###

以下脚本，是将user表根据age进行分区，p0中的user.age <18，p1中的user.age >=18 and user.age <30，p2中的user.age >=30 and user.age < 60，p3中的user.age >=60。

CREATE TABLE user (
        id INT NOT NULL,
        name VARCHAR(30) not null,
        sex int(11) not null,
        age int(11) not null
    )
    PARTITION BY RANGE (age) (
        PARTITION p0 VALUES LESS THAN (18),
        PARTITION p1 VALUES LESS THAN (30),
        PARTITION p2 VALUES LESS THAN (60),
        PARTITION p3 VALUES LESS THAN MAXVALUE
    );
    alter table user add index ix_age(age) ;

### 3LIST分区 ###

以下搅拌将user按照性别分区，男性放在male分区中，女性放在female分区中。

CREATE TABLE user (
        id INT NOT NULL,
        name VARCHAR(30) not null,
        sex int(11) not null,
        age int(11) not null
    )
    PARTITION BY LIST(sex) (
        PARTITION male VALUES IN (1),
        PARTITION female VALUES IN (2)
    );

### 4HASH分区 ###

以下脚本通过hash(name)%4进行分区.

CREATE TABLE user (
        id INT NOT NULL,
        name VARCHAR(30) not null,
        sex int(11) not null,
        age int(11) not null
    )
    PARTITION BY HASH(name)
    PARTITIONS 4
    ;

### 5KEY分区 ###

KEY分区类似于HASH分区，KEY分区的哈希函数是由MySQL服务器提供。Key分区与Hash分区很相似，只是Hash函数不同，定义时把Hash关键字替换成Key即可。MySQL簇（Cluster）使用函数MD5()来实现KEY分区；

CREATE TABLE user (
        id INT NOT NULL,
        name VARCHAR(30) not null,
        sex int(11) not null,
        age int(11) not null
    )
    PARTITION BY KEY(name)
    PARTITIONS 4
    ;

### 6分区中的NULL值 ###

Mysql分区时，视NULL值比任何非NULL的值都小。

## 三分区与性能 ##

假设表的数据里为1000W，通过主键划分10个分区，在这种情况下我们分析一下分区对性能的影响。

对于SELECT \* FROM TABLE WHERE PK=\#PK\#查询：

假设100w和1000w行的数据构成的B+树都是2层，因为分区前后都是进行2次IO即可找到目标数据，所以分区对性能没有影响。

假设1000w数据的B+树是3层，100w数据的B+树是2层，因为分区前需要3次IO找到目标数据，分区后需要2次IO就可以找到目标数据，可以提升查询性能。

对于SELECT \* FROM TABLE WHERE KEY=\#KEY\#查询：

分区前需要2-3次IO即可找到目标数据；分区之后即便是每个分区进行2次IO就能找到数据，但是因为会遍历10个分区表，所以一共需要20次IO。性能会急剧下降。

因为不当的分区方式或查询方式会导致多分区全表扫描，性能急剧降低。如果要分区，查询时务必带上分区字段作为条件，并且尽可能降低每个sql语句扫描的分区数目。

对于OLTP类型的应用而言，查询的场景一般都是多种多样的，所以需要非常谨慎的使用分区。