MYSQL: 数据库分区分表

 

什么是分区?

 

　　分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放。
分区后，表还是一张表，但数据散列到多个位置了。app读写的时候操作的还是表名字，db自动去组织分区的数据。

　　1. 水平分区(Horizontal Partitioning)
这种形式分区是对表的行进行分区，所有在表中定义的
　　列在每个数据集中都能找到，所以表的特性依然得以保持。

　　举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。

　　2. 垂直分区(Vertical Partitioning)
这种分区方式一般来说是通过对表的垂直划分来减少目标表
　　的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。

　　举个简单例子：一个包含了大 text 和 BLOB 列的表，这些 text 和 BLOB 列又不经常被访问，
　　这时候就要把这些不经常使用的 text 和 BLOB 了划分到另一个分区，在保证它们数据相关性
　　的同时还能提高访问速度。

mysql 5.6之前:
mysql> SHOW VARIABLES LIKE '%partition%';

mysql5.6及以采用后:
mysql> show plugins;
partition active

 

1.RANGE 分区

　　基于属于一个给定连续区间的列值，把多行分配给分区。这些区间要连续且不能相互重叠，

CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
nickname VARCHAR(30) NOT NULL DEFAULT "",
sex INT(1) NOT NULL DEFAULT 0,
PRIMARY KEY(id)
)DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY RANGE(id)(
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (3000),
PARTITION p4 VALUES LESS THAN (4000),
PARTITION p5 VALUES LESS THAN (8000),
PARTITION p6 VALUES LESS THAN MAXVALUE
);
查看分区信息： select * from information_schema.partitions where table_schema='test' and table_name='users'\\G;
分区中查询数据：mysql> select * from test.users partition(p0);
新增分区: alter table test.users add partition (partition partionname values less than (n));
删除分区：ALTER TABLE test.users DROP PARTITION p3;

 

2.LIST 分区　　

　　类似于按 RANGE 分区，区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择。
　　LIST 分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，
　　其中“value_list”是一个通过逗号分隔的整数列表。
　　要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表，一个地区包含多家商店
CREATE TABLE tblist (
id INT NOT NULL,
store_id INT
)
PARTITION BY LIST(store_id) (
PARTITION pEast VALUES IN (1,5,6),
PARTITION pWest VALUES IN (2,7,8),
PARTITION pNorth VALUES IN (3,9,10),
PARTITION pSouth VALUES IN (4,11,12)
);
假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录(行)可以使用查询“ALTER TABLE employees DROP PARTITION pWest;”来进行删除

 

3.HASH 分区

　　这种模式允许 DBA 通过对表的一个或多个列的 Hash Key 进行计算，最后通过这个 Hash 码不同数值对应的数据区域进行分区。
hash 分区的目的是将数据均匀的分布到预先定义的各个分区中，保证各分区的数据量大致一致.
在 RANGE 和 LIST 分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中;
而在 HASH 分区中，MYSQL 自动完成这些工作，用户所要定一个列值或者表达式，以及指定被分区的表将要被分割成的分区数量。
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01'
)
PARTITION BY HASH(id)
PARTITIONS 4;

 

4. KEY分区

和HASH分区类似，只是HASH分区允许使用用户自定义表达式，而KEY分区不允许使用用户自定义表达式，需要使用MySQL服务器提供的HASH函数，同时HASH分区只支持整数分区，而KEY分区支持使用除了BLOB,TEXT之外其他类型列作分区键。
与HASH分区不同，创建key分区时候可以不指定分区键，默认会选择主键作为分区键，在没有主键情况下会选择非空唯一键作分区键，下面例子便是使用id作为分区键(虽然没有明确指出)

CREATE TABLE k1 (
id INT NOT NULL,
name VARCHAR(20),
UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;

上面的 RANGE、LIST、HASH、KEY 四种分区中，分区的条件必须是整形，如果不是整形需要
　　通过函数将其转换为整形。

 

5. columns 分区

　　mysql-5.5 开始支持 COLUMNS 分区，可视为 RANGE 和 LIST 分区的进化，COLUMNS 分区可以
　　直接使用非整形数据进行分区。COLUMNS 分区支持以下数据类型：
　　所有整形，如 INT SMALLINT TINYINT BIGINT。FLOAT 和 DECIMAL 则不支持。
　　日期类型，如 DATE 和 DATETIME。其余日期类型不支持。
　　字符串类型，如 CHAR、VARCHAR、BINARY 和 VARBINARY。BLOB 和 TEXT 类型不支持。
　　COLUMNS 可以使用多个列进行分区。
　　

什么是分表?

　　分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，每个表都对应三
　　个文件，MYD 数据文件，.MYI 索引文件，.frm 表结构文件。这些表可以分布在同一块磁盘
　　上，也可以在不同的机器上。app 读写的时候根据事先定义好的规则得到对应的表名，然后
　　去操作它。

　　将单个数据库表进行拆分，拆分成多个数据表，然后用户访问的时候，根据一定的算法(如
　　用 hash 的方式，也可以用求余(取模)的方式)，让用户访问不同的表，这样数据分散到多
　　个数据表中，减少了单个数据表的访问压力。提升了数据库访问性能。分表的目的就在于此，
　　减小数据库的负担，缩短查询时间。

 

1.垂直切分

　　垂直切分是指数据表列的拆分，把一张列比较多的表拆分为多张表
　　通常我们按以下原则进行垂直拆分:
　　把不常用的字段单独放在一张表;
　　把 text，blob(binary large object，二进制大对象)等大字段拆分出来放在附表中;
　　经常组合查询的列放在一张表中;
　　垂直拆分更多时候就应该在数据表设计之初就执行的步骤，然后查询的时候用 jion 关键起来即可。

2.水平切分
　　水平拆分是指数据表行的拆分，把一张的表的数据拆成多张表来存放。
　　水平拆分原则
　　通常情况下，我们使用 hash、取模等方式来进行表的拆分
　　比如一张有 400W 的用户表 users，为提高其查询效率我们把其分成 4 张表 users1，users2，users3，users4
　　通过用 ID 取模的方法把数据分散到四张表内 Id%4= [0,1,2,3]
　　然后查询,更新,删除也是通过取模的方法来查询
　　部分业务逻辑也可以通过地区，年份等字段来进行归档拆分;
　　进行拆分后的表，这时我们就要约束用户查询行为。比如我们是按年来进行拆分的,这个时
　　候在页面设计上就约束用户必须要先选择年,然后才能进行查询。

对于数据的分区存储和分表存储，mysql分表和分区有什么区别呢？

　　1、实现方式上
　　a) mysql的分表是真正的分表，一张表分成很多表后，每一个小表都是完整的一张表，都对应三个文件，一个.MYD 数据文件，.MYI 索引文件，.frm 表结构文件。
　　b) 分区不一样，一张大表进行分区后，他还是一张表，不会变成二张表，但是他存放数据的区块变多了

　　2、数据处理上
　　a)分表后，数据都是存放在分表里，总表只是一个外壳，存取数据发生在一个一个的分表里面。
　　b)分区呢，不存在分表的概念，分区只不过把存放数据的文件分成了许多小块，分区后的表呢，还是一张表，数据处理还是由自己来完成。

　　3、提高性能上
　　a)分表后，单表的并发能力提高了，磁盘 I/O 性能也提高了。并发能力为什么提高了呢，因为查寻一次所花的时间变短了，如果出现高并发的话，总表可以根据不同的查询，将并发压力分到不同的小表里面。
　　b)mysql 提出了分区的概念，主要是想突破磁盘I/O瓶颈，想提高磁盘的读写能力，来增加mysql性能。

　　分区和分表的测重点不同
分表重点是存取数据时，如何提高 mysql并发能力上。
分区重点是如何突破磁盘的读写能力，从而达到提高mysql性能的目的。

　　4、实现的难易度上
　　a)分表的方法有很多，用 merge 来分表，是最简单的一种方式。这种方式跟分区难易度差不多，并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。
　　b)分区实现是比较简单的，建立分区表，根建平常的表没什么区别，并且对开代码端来说是透明的。

对于数据的分区存储和分表存储，mysql 分表和分区有什么联系？

　　1.都能提高mysql的性高，在高并发状态下都有一个良好的表现。
　　2.分表和分区不矛盾，可以相互配合的。
大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式。
访问量不大，但是表数据很多的表，我们可以采取分区的方式等。
　　3.分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的 union 关系。
　　4.表分区相对于分表，操作方便，不需要创建子表。