学习笔记之MySQL分区

0x00 为什么要进行数据库分区

为了在特定的SQL操作中减少数据读写总量以缩减响应时间,提升SQL操作效率的一种技术解决方案。

0x01 数据库分区分类

按数据行列分类

  • 水平分区:将同一数据表中不同行的记录分配到不同的物理文件中
  • 垂直分区:将同一数据表中不同列的记录分配到不同的物理文件中

按数据与索引关系分类

  • 局部分区:一个分区中既存放数据又存放索引
  • 全局分区:数据存放在各个分区中,而所有索引存在同一对象中
MySQL数据库目前仅支持水平分区 / 局部分区

0x02 MySQL支持的分区类型

  • RANGE分区:一种范围分区,将数据基于一个给定的连续区间进行分区
  • LIST分区:与RANGE分区类似,但分区面向散列值
  • HASH分区:根据用户自定义表达式的返回值进行分区,返回值不能为负
  • KEY分区:根据MySQL数据库提供的哈希函数进行分区

备注:如果需要分区的表中存在主键或唯一索引时,分区列必须是唯一索引的组成部分。

1、RANGE分区

创建RANGE分区示例:

1
2
3
4
5
6
7
8
CREATE TABLE t1(
id INT(4),
name VARCHAR(20)
) ENGINE=INNODB
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20)
);

备注:当对某表进行了分区时,分区列的值必须严格遵守分区的定义,当插入一个分区定义中不存在的值时,MySQL数据库会抛出一个异常。

RANGE分区的分区列值必须是整型或者使用MySQL内部函数进行转化,例如:YEAR() TO_DAYS() 等,对于RANGE分区查询时,查询优化器只能对YEAR() TO_DAYS() TO_SECONDS() UNIX_TIMESTAMP() 等函数进行优化选择,所以在使用RANGE分区查询时,请合理使用转化函数。

当在数据库中使用了RANGE分区时,我们应该尽可能的优化SQL查询语句,使SQL查询语句尽可能的能够命中查询优化器的Partition Pruning(分区修剪)功能,尽可能的扫描最少的分区表。

2、LIST分区

创建LIST分区示例:

1
2
3
4
5
6
7
8
CREATE TABLE t1(
a INT(4),
b INT(4)
) ENGINE=INNODB
PARTITION BY LIST (b) (
PARTITION p0 VALUES IN (1, 3, 5, 7, 9),
PARTITION p1 VALUES IN (0, 2, 4, 6, 8)
);

LIST分区因为是离散定义,所以只能对每个分区列定义指定的值。

当插入值不在分区定义中时,MySQL同样会报出异常。

备注:在使用INSERT插入多个行数据的过程中遇到存在未定义的列值时,MyISAM和InnoDB存储引擎处理方式不同,因为MyISAM不支持事务性,所以会将未定义列值之前的数据正确插入。而InnoDB存储引擎将此视为一个事务,所以只要多个行数据中存在未定义值时,则全部不会插入。

3、HASH分区

创建HASH分区示例:

1
2
3
4
5
6
CREATE TABLE t_hash(
a INT,
b DATETIME
) ENGINE=INNODB
PARTITION BY HASH (YEAR(b))
PARTITION 4;

其中最后一句PARTITION 4中,4为此次HASH分区的分区数量,如果不包括该句,则默认分为一个分区。

其实示例中的方式是一种不合适的哈希分区方式,因为这种方式不能将数据均匀的放置到每一个分区中,在HASH分区中,HASH算法的选择是极其重要的,哈希算法的好坏决定着我们的数据的均匀性。

4、KEY分区

创建KEY分区示例:

1
2
3
4
5
6
CREATE TABLE t_hash(
a INT,
b DATETIME
) ENGINE=INNODB
PARTITION BY KEY (b)
PARTITION 4;

KEY分区与HASH分区不同之处在于KEY分区使用MySQL数据库提供的函数进行分区,无需用户自行指定哈希函数。优点在于无需考虑你自定义的哈希函数是否能够达到良好的均匀性,劣势在于没有自定义的更符合自己的使用场景。

0x03 RANGE分区与HASH分区的优劣性对比

RANGE分区与HASH分区应该是MySQL数据库分区中使用最多的两种方式,操作简单,效果明显。而这两种分区方式分别都有哪些优劣性呢?

  • RANGE分区

    优点:

    1)规则简单 2)数据均衡性较好 3)比较容易扩展

    劣势:

    1)请求不一定均衡

  • HASH分区

    优点:

    1)规则简单 2)数据均衡性良好 3)请求均衡性较好

    劣势:

    1)不容易扩展,扩展时,可能需要进行数据迁移

0x04 结束

本文纯属学习笔记。