mysql分区、分表学习

ゝ一纸荒年。 2022-06-02 09:35 277阅读 0赞

> 现在学习mysql表分区，百度了资料，特整理一下，原文章 [https://my.oschina.net/ydsaky...][https_my.oschina.net_ydsaky...]

--------------------

## 分区 ##

### 一、什么是表分区 ###

通俗地讲表分区是将一大表，根据条件分割成若干个小表。mysql5.1开始支持数据表分区了。  
如：某用户表的记录超过了600万条，那么就可以根据入库日期将表分区，也可以根据所在地将表分区。当然也可根据其他的条件分区。

使用表分区技术对客户端没有影响相当于所有的数据还是存放在一张表中，但是相对于mysql内部来讲，却是将数据拆分存放在不同位置中，就好比一个文件夹下文件过多需要整理存放在不同子文件夹中一样。

### 二、为什么要对表进行分区 ###

为了改善大型表以及具有各种访问模式的表的可伸缩性，可管理性和提高数据库效率。  
分区的一些优点包括：

1.  与单个磁盘或文件系统分区相比，可以存储更多的数据。
2.  对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。
3.  一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。
4.  通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

### 三、分区类型 ###

1.  RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。
2.  LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
3.  HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
4.  KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

> RANGE和LIST属于条件算法，KEY和HASH属于取余算法

### 四、各分区详细介绍 ###

#### 1.range分区 ####

CREATE TABLE employees ( id INT NOT NULL store_id int ) partition BY RANGE (store_id) ( partition p0 VALUES LESS THAN (6), partition p1 VALUES LESS THAN (11), partition p2 VALUES LESS THAN (16), partition p3 VALUES LESS THAN MAXVALUE );

*  这样p0-p5的店员会保存在p0，p6-p10的店员会保存在p1，以此类推。MAXVALUE为常量表示最大的可能的整数值。
 *  这里最值得注意的限制是MySQL 必须能够计算表达式的返回值作为LESS THAN (<)比较的一部分；因此，表达式的值不能为NULL。
 *

RANGE分区在如下场合特别有用：

1.  当需要删除一个分区上的“旧的”数据时,只删除分区即可。如果你使用上面最近的那个例子给出的分区方案，你只需简单地使用 “ALTER TABLE employees DROP PARTITION p0；”来删除所有在店铺0的雇员相对应的所有行。对于有大量行的表，这比运行一个如“DELETE FROM employees WHERE store\_id <= 6；”这样的一个DELETE查询要有效得多。
2.  经常运行直接依赖于用于分割表的列的查询。例如，当执行一个如“SELECT COUNT(\*) FROM employees WHERE store\_id<6 GROUP BY store\_id；”这样的查询时，MySQL可以很迅速地确定只有分区p0需要扫描，这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。

#### 2.list分区 ####

假定有20个音像店，分布在4个有经销权的地区，如下表所示：

要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表，可以使用下面的“CREATE TABLE”语句：

CREATE TABLE employees ( id INT NOT NULL store_id int ) partition BY LIST(store_id)( partition pNorth VALUES IN (8,9,10), partition pEast VALUES IN (1,2,3,4), partition pWest VALUES IN (5,6,7), partition pSouth VALUES IN (11,12) )；

这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如，假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录（行）可以使用查询“ALTER TABLE employees DROP PARTITION pWest；”来进行删除，它与具有同样作用的DELETE （删除）查询“DELETE query DELETE FROM employees WHERE store\_id IN (4,12,13,14,18)；”比起来，要有效得多。

【要点】：如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“INSERT”查询将失败并报错。例如，假定LIST分区的采用上面的方案，下面的查询将失败：

INSERT INTO employees VALUES(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);

这是因为“store\_id”列值21不能在用于定义分区pNorth, pEast, pWest,或pCentral的值列表中找到。要重点注意的是，LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。

#### 3.hash分区 ####

CREATE TABLE employees ( id INT NOT NULL store_id int ) PARTITION BY HASH(store_id) PARTITIONS 4；

hash分区的目的是将数据均匀的分布到预先定义的各个分区中，保证各分区的数据数量大致一致。

#### 4.key分区 ####

CREATE TABLE employees ( id INT NOT NULL store_id int ) PARTITION BY key(store_id) PARTITIONS 4；

类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

### 五、管理分区语法 ###

**取余:key,hash**

增加分区数量