Jamzy Wang

life is a struggle,be willing to do,be happy to bear~~~

可扩展系统设计之数据库分区分表

2015-01-21 10:20

原创声明:本作品采用知识共享署名-非商业性使用 3.0 版本许可协议进行许可,欢迎转载,演绎,但是必须保留本文的署名(包含链接),且不得用于商业目的。

随着数据库表中数据日积月累越来越多,数据库会越来越大,表记录数也会达到千万甚至亿级别,数据库表的访问效率下降明显,导致外层应用的访问效率非常差,访问时间急剧上升。此时就必须使用数据切分来解决这个瓶颈了。

数据切分(partition)就是指通过某种特定的条件,将存放在同一个数据库中的数据分散存放到多个数据库上面,以达到分散单台设备负载的效果。数据切分本质上就是“大表拆小表”,根据切分规则的类型,可以分为两种模式:

  • 垂直切分

将有N个字段的一个大表拆分为若干个只有几个字段(通常为两到三个)的小表。

  • 水平切分

将有N条记录(N通常很大比如几千万,过亿)的一个大表拆分为若干个只有K条记录的小表(K在几十万数量级)。

本文将重点讲解MySQL中的垂直切分和水平切分。

垂直切分

垂直切分(vertical partition)就是要把表按模块划分到不同数据库或不同表中。通俗的讲,垂直切分就是:

将一个具有较多字段的表(大表)切分为具有较少字段的几个表(小表) 小表之间可以通过某个字段可以做join(一般都是某个唯一的id)

垂直切分能起到较好作用的前提是大量的访问并不需要访问表中的所有字段,只需要访问表中的个别字段。从应用系统模块角度讲,不同的模块只需要使用到表中的个别字段,把大表拆分为小表可以极大的减少数据库的读写时间消耗。

当然,很难有系统能够做到所有模块使用的表完全独立,根本不需要访问对方的表,或者不需要将两个模块的表进行join操作。这种情况下,就必须根据实际的应用场景来进行评估权衡。迁就应用程序就需要将待join的表的相关模块存放在同一个数据库表中,或者让应用程序做更多的事情——完全通过模块接口取得不同数据库表中的数据,然后在程序中完成join操作。

假设有如下这个products表:

1
2
3
4
5
6
7
8
9
CREATE TABLE `products` (
  `productID` int(20) unsigned NOT NULL,
  `productCode` int(20) NOT NULL DEFAULT '0',
  `name` varchar(30) NOT NULL DEFAULT '',
  `description` text NOT NULL DEFAULT '',
  `quantity` int(20) unsigned NOT NULL DEFAULT '0',
  `price` decimal(20,2) NOT NULL DEFAULT '99999.99',
   PRIMARY KEY (`productID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

假设在这个products表中一般只需要查询productCodenamequantityprice这几个字段,但是 description 字段查询不频繁,但是这个字段是text类型,占用内存比较多。当这个表中的记录数达到千万级别、亿级别时,query操作会有大量的磁盘IO(即使InnoDB会为productID建立索引,但是索引本身的空间与维护成本都会很高),查询将会非常耗时。

用垂直切分来优化查询的方法是将这个products表切分为两个小表products_info_basic表、products_info_desc表。

products_info_basic 表结构如下(相比于products表只少了一个description字段):

1
2
3
4
5
6
7
8
CREATE TABLE `products_info_basic` (
  `productID` int(20) unsigned NOT NULL,
  `productCode` int(20) NOT NULL DEFAULT '0',
  `name` varchar(30) NOT NULL DEFAULT '',
  `quantity` int(20) unsigned NOT NULL DEFAULT '0',
  `price` decimal(20,2) NOT NULL DEFAULT '99999.99',
   PRIMARY KEY (`productID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

products_info_desc 表结构如下:

1
2
3
4
5
CREATE TABLE `products_info_desc` (
  `productID` int(20) unsigned NOT NULL,
  `description` text NOT NULL DEFAULT '',
   PRIMARY KEY (`productID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

此处输入图片的描述

拆分为两个表后,在products_info_basic表上查询productCodenamequantityprice这几个字段在效率上将会极大的提高。

垂直切分的优点:

1
2
3
数据库表的切分简单明了,切分规则明确;
应用程序模块清晰明确,整合容易;
数据维护方便易行,容易定位;

垂直切分缺点:

1
2
3
4
5
部分表关联无法在数据库级别完成,需要在程序中进行;
对于访问极其频繁且数据量超大的表仍然存在性能瓶颈,不一定能满足要求;
事务处理相对复杂;
切分达到一定程度之后,扩展性会受到限制;
过度切分可能会导致系统过于复杂而难以维护;

水平切分

水平分割(horizontal partition)。假设有一个表,表中已经有了1亿条记录,那么此时如果通过select去查询,必定会效率低下。为了降低单表的读写IO压力,通过水平分割,将这个表分成10个页,同时生成一个总表,记录各个页的信息,那么假如我查询一条id=100的记录,它不再需要全表扫描,而是通过总表找到该记录在哪个对应的页上,然后再去相应的页做检索,这样就降低了IO压力。

此处输入图片的描述

这里再介绍一个概念——sharding(分片),很多时候sharding 被等同于水平分区(horizontal partitioning)。本人的理解,sharding确切的是指能够跨数据库或者跨物理机器,而horizontal partition 基本上是数据对象级别的处理,比如表和索引的分区,每个子数据集上能够有不同的物理存储属性,但是还是单个数据库范围内的操作,比如MySQL中的horizontal partition。下面也将用MySQL为例介绍horizontal partition。

在MySQL中分区是逻辑层面进行了水平分割,对于应用程序来说,它仍是一张表。分区就是把一张表的数据分成N多个区块,这些区块可以在同一个磁盘上,也可以在不同的磁盘上。

在MySQL中可以通过 show plugins 命令查看数据库是否支持分区技术,下图是在MySQL5.5中的执行结果:

此处输入图片的描述

从上图最后一行中我们可以看到数据库支持partition。

水平切分的切分标准常见的有:

1
2
3
4
按照数据范围分,比如1-100万一个表,100-200万又是一个表;
按照时间顺序来切分,比如一年的数据归到一张表中等;
按照地域范围来分,比如按照地市来分,每个或多个地市一个库等;
按照某种计算公式来切分,比较简单的比如取模的方式

Mysql中支持hash、range、list、key等分区类型。

1
2
3
4
RANGE分区:基于一个给定连续区间的列值,把多行分配给分区。
HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算
LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。

建立分区的语法如下:

1
2
3
4
5
6
7
CREATE TABLE tbl (
    col1 VARCHAR(10) NOT NULL,
    col2 VARCHAR(10) NOT NULL,
    ...
)
PARTITION BY partition_type(col1)
PARTITIONS size;
  • 按照Range分区
1
2
3
4
5
6
7
CREATE TABLE tbl (
        ...
)
PARTITION BY RANGE( MONTH(col1) ) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (13),
);
  • 按照List分区
1
2
3
4
5
6
7
CREATE TABLE tbl (
    ...
    )
PARTITION BY LIST(MONTH(val))(
PARTITION p0 VALUES IN (1,3,5,7,9,11),
PARTITION p1 VALUES IN (2,4,6,8,10,12)
);
  • 按照Hash分区
1
2
3
4
5
CREATE TABLE tbl (
    ...
)
PARTITION BY mod(col1, 5)
PARTITIONS 5;
  • 按照Key分区
1
2
3
4
5
CREATE TABLE tbl (
    ...
)
PARTITION BY KEY(col1)
PARTITIONS size;
  • Sub-Partition
1
2
3
4
5
6
7
CREATE TABLE tbl (id INT, hire DATE)
    PARTITION BY RANGE(YEAR(hire))
    SUBPARTITION BY HASH(MONTH(hire))
    SUBPARTITIONS 2 (
    PARTITION p0 VALUES LESS THAN (1980),
    PARTITION p1 VALUES LESS THAN (2010)
);

也可以对已经存在的表进行分区:

1
mysql> ALTER TABLE tbl PARTITION BY KEY(col1) PARTITIONS 5;

水平切分的优点:

  • 表关联基本能够在数据库端全部完成;
  • 不会存在某些超大型数据量和高负载的表遇到瓶颈的问题;
  • 应用程序端整体架构改动相对较少;
  • 事务处理相对简单;
  • 只要切分规则能够定义好,扩展性一般不会受到限制;

水平切分的缺点:

  • 切分规则相对复杂,很难抽象出一个能满足整个数据库的切分规则;
  • 后期的维护难度有所增加,人为手工定位数据较困难;
  • 应用系统各模块耦合度非常高,可能会对后面数据的迁移切分造成一定的困难。
  • 若切分不合理,会造成数据表的冷热不均现象。

垂直与水平联合切分

由上面可知垂直切分能更清晰化模块划分,区分治理,水平切分能解决大数据量性能瓶颈问题。本节将结合垂直切分和水平切分的优缺点,进一步完善整体架构,并提高系统的扩展性。

在实际的应用场景中,除了那些负载并不是太大、业务逻辑相对简单的系统可以通过上面两种切分方法之一来解决扩展性问题,但是大部分的业务逻辑复杂、系统负载大的系统,都无法通过上面任何一种数据的切分方法来实现较好的扩展性。这就需要将上述两种方法结合使用,不同的场景使用不同的切分方法。

MySQL中分区与分表的区别

  • 实现方式上

1) mysql的分表是真正的分表,一张表分成很多表后,每一个小表都是完整的一张表,都对应三个文件,一个.MYD数据文件,.MYI索引文件,.frm表结构文件。

2) 分区不一样,一张大表进行分区后,他还是一张表,不会变成二张表,但是他存放数据的区块变多了。

  • 数据处理上

1) 分表后,数据都是存放在分表里,总表只是一个外壳,存取数据发生在一个一个的分表里面。

2) 分区,不存在分表的概念,分区只不过把存放数据的文件分成了许多小块,分区后的表,还是一张表。数据处理还是由自己来完成。

  • 提高性能上

1) 分表后,单表的并发能力提高了,磁盘I/O性能也提高了。因为查询一次所花的时间变短了,如果出现高并发的话,总表可以根据不同的查询,将并发压力分到不同的小表里面。本来一个非常大的.MYD文件现在也分摊到各个小表的.MYD中去了,因此对于磁盘IO压力也降低了。

2) mysql提出了分区的概念,我觉得就想突破磁盘I/O瓶颈,想提高磁盘的读写能力,来增加mysql性能。 在这一点上,分区和分表的侧重点不同,分表重点是存取数据时,如何提高mysql并发能力上;而分区呢,则是如何突破磁盘的读写能力,从而达到提高mysql性能的目的。

  • 实现的难易度上

1) 分表的方法有很多,用merge来分表,是最简单的一种方式。这种方式根分区难易度差不多,并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。

2) 分区实现是比较简单的,建立分区表,跟建平常的表没什么区别,并且对开代码端来说是透明的。

Ref

Comments