我们在对表进行分区设计时,需要明白的是,它是一种对数据进行物理分区的数据管理方式

什么场景下应该使用分区表?这有时像是一个哲学问题,因为不同的业务场景使用的数据库肯定技术肯定是不同的,另外对表进行分区的同时,要配合着数据库的表结构设计、索引设计等不同技术一同来做到最优实现。

这篇文章,将会结合 MySQL 数据库介绍分区表的使用、注意事项以及有哪些误区,希望对大家学习分区表的相关知识能提供一点帮助,从而在自己的业务实践表分区的最优设计。

如何进行表分区?

我们可以从一个算法来熟悉它,就是“分区函数”,分区表就是把N张具有相同物理表结构的表通过分区函数组成一张逻辑大表。那么 MySQL 数据库有RANGE、LIST、HASH、KEY、COLUMNS 这些分区函数。

有了分区函数,还要指定在哪些表列上应用分区函数,也就是所谓的“分区列”。

需要特别注意的是,对 MySQL 表进行分区设计时,进行分区计算的列必须是联合主键的一部分,否则我们创建分区表时会报错,参见下面的SQL示例:

CREATE TABLE t (

  a INT,

  b INT,

  c DATETIME(6),

  d VARCHAR(32),

  e INT,

  PRIMARY KEY (a,b)

)

partition by range columns(c) (

  PARTITION p0000 VALUES LESS THAN ('2019-01-01'),

  PARTITION p2019 VALUES LESS THAN ('2020-01-01'),

  PARTITION p2020 VALUES LESS THAN ('2021-01-01'),

  PARTITION p9999 VALUES LESS THAN (MAXVALUE)

);

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).

在创建表 t 时,给列 a、b 设置了联合主键,然后根据列 c(时间列)进行拆分数据,这样MySQL就会根据时间范围把不同数据放到不同分区中。

理论上这样没问题,但我们可以看到出错了,报错信息的意思是:分区表的主键一定要包含分区函数的列。

基于该错误提示,我们需要稍微改下创建表的SQL,让主键也包含列 c,参见如下SQL语句:

CREATE TABLE t (

  a INT,

  b INT,

  c DATETIME,

  d VARCHAR(32),

  e INT,

  PRIMARY KEY (a,b,c),

  KEY idx_e (e)

)

partition by range columns(c) (

  PARTITION p0000 VALUES LESS THAN ('2019-01-01'),

  PARTITION p2019 VALUES LESS THAN ('2020-01-01'),

  PARTITION p2020 VALUES LESS THAN ('2021-01-01'),

  PARTITION p9999 VALUES LESS THAN (MAXVALUE)

);

这回可以正常建表了,执行完SQL后,我们可以在MySQL物理存储硬盘上看到p0000、p2019、p2020、p9999这四个分区对应的 ibd 文件:

t#p#p0000.ibd  t#p#p2019.ibd  t#p#p2020.ibd  t#p#p9999.ibd

当我们往表 t insert 不同时间c列的数据时,这些数据就会落在不同的分区里,最终也就是放在不同的 ibd 文件里了。

因此我们可以这样理解:MySQL 中的分区表是把一张表拆成了多张独立的小表,并且这些表有自己的索引,逻辑上看还是一张表,但物理上它们的数据是存储在不同文件中的。

由此,又出现一个新问题,分区表和普通表的索引有区别吗?分区表是如何实现唯一索引的?

分区表的唯一索引有什么要注意的地方?

当我们对MySQL数据库中的表进行分区后,就会出现“局部”和“全局”的概念,对于分区表的索引,都是局部索引,没有全局。

这就代表,每个分区里的索引都是独立的。

所以我们要在分区表上建唯一索引时,需要把所有分区列加上,否则唯一索引会添加失败。

还是拿表 t 举例,在列 d 上增加一个唯一索引:

ALTER TABLE t ADD UNIQUE KEY idx_d(d);

ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).

毫无疑问,出错了,因为我们的分区列是 c,结合分区表的唯一索引必须包含分区函数中所有列的准则。我们修改下创建唯一索引的SQL:

ALTER TABLE t ADD UNIQUE KEY idx_d(d,c);

这回没问题了。

分区表一定能带来性能提升吗?

先说答案:NO!

一般根据业务场景,有两类数据库应用,一类是OLTP(在线事务处理),如博客、电子商务、网游等;另一类是OLAP(在线分析处理),如数据仓库、数据集市。

分区只能提升OLAP应用的查询性能,为什么?

因为OLAP应用的特征是需要频繁查询扫描全表,如果分区了,只需要扫描对应分区的数据即可。

相反OLTP应用大多是通过索引返回表里若干记录,绝大部分场景不会从一张大表中拿取超过10%的数据。

我们可以结合MySQL的B+索引特性,来分析下表分区的查询效率。

假设我们给上表 t insert 1000w条数据,那此时B+树的高度为3,若将表 t 分为10个分区,则每张小表100W条数据的B+树高度则是2。

若查询用到的索引在分区列上,则从原表2到3次IO可以提升到2次IO。

但若查询用到的索引在其它列上,则最坏的情况可能需要10 * 2 = 20次IO。

所以,分区能不能带来查询性能提升,得看不同的业务系统需求和表设计