史上最简单 MySQL 分区

青旅半醒 2022-04-22 01:52 207阅读 0赞

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

通俗地讲表分区是将一大表，根据条件分割成若干个小表。
    mysql5.1开始支持数据表分区了。

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

为了改善大型表以及具有各种访问模式的表的可伸缩性，可管理性和提高数据库效率。

## 三、分区的优点： ##

1、单个磁盘或文件系统分区相比，可以存储更多的数据。
    
    2、提高数据管理性，对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。
       	相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
    
    3、提高查询效率，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，
    	这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，
    	所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
    
    4、通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

## 四、分区类型： ##

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

## 五、详情： ##

1、RANGE 分区：  
基于属于一个给定连续区间的列值，把多行分配给分区。

这些区间要连续且不能相互重叠，使用 VALUES LESS THAN 操作符来进行定义。

1、整数值分区：
    PARTITION BY RANGE (字段名) (
        PARTITION 分区名 VALUES LESS THAN (整数值),
        PARTITION 分区名 VALUES LESS THAN (11),
        PARTITION 分区名 VALUES LESS THAN MAXVALUE	# 表示最大的可能的整数值
    );
    
    2、按日期分区：
    PARTITION BY RANGE (YEAR (字段名)) (				# YEAR() 日期-年
        PARTITION 分区名 VALUES LESS THAN (年),
        PARTITION 分区名 VALUES LESS THAN (1996),
        PARTITION 分区名 VALUES LESS THAN MAXVALUE
    );

2、LIST 分区：  
类似于按 RANGE 分区，区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择。

LIST 分区通过使用 “PARTITION BY LIST(expr)” 来实现，其中 “expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过 “VALUES IN (value\_list)” 的方式来定义每个分区，其中 “value\_list” 是一个通过逗号分隔的整数列表。 注：在MySQL 5.1中，当使用 LIST 分区时，有可能只能匹配整数列表。

PARTITION BY LIST (字段名)
        PARTITION 分区名 VALUES IN (值, 值, ...),
        PARTITION 分区名 VALUES IN (1, 3, 5, 100),
        PARTITION 分区名 VALUES IN (7, 8, 10)
    );

3、HASH 分区：  
基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含 MySQL 中有效的、产生非负整数值的任何表达式。

要使用 HASH 分区来分割一个表，要在 CREATE TABLE 语句上添加一个 “PARTITION BY HASH (expr)” 子句，其中 “expr” 是一个返回一个整数的表达式。它可以仅仅是字段类型为 MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一个 “PARTITIONS num” 子句，其中 num 是一个非负的整数，它表示表将要被分割成分区的数量。

1、HASH：
    	PARTITION BY HASH (字段名)
    	PARTITIONS 4;							# PARTITIONS 值;
    	
    2、LINER HASH：
    	PARTITION BY LINEAR HASH(YEAR(字段))		# YEAR() 日期-年
    	PARTITIONS 4;							# PARTITIONS 值;

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

PARTITION BY LINEAR KEY (字段名)
    PARTITIONS 3;								# PARTITIONS 值;

在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。

## 六、删除分区： ##

ALTER TABLE 表名 DROP PARTITION 分区名;