MySQL5.5 分区简述

    随着数据库应用规模的扩展，需要管理的数据规模也越来越大，普通的数据库查询优化机制在某些情况下已不能再满足性能的要求了。利用数据库分区技术，可以有效地减少 I/O 的数量，提升系统的查询性能。
 

1.什么是分区？

    数据库分区是一种物理数据库设计技术，DBA 和数据库建模人员对其相当熟悉。虽然分区技术可以实现很多效果，但其主要目的是在特定的 SQL 操作中减少数据库读/写的总量以缩减响应的时间。

    分区（Partitioning）是数据库优化的一种方式。简单来说就是将同一数据表中的记录通过特定的算法进行分离，分别保存在不同的数据表中，甚至可以部署在不同的物理磁盘上，分散对同一张数据表的操作，提高访问的速度。

    举个例子：db_blog 数据库存储了用户发表的博客内容，主要数据都在tb1_posts表中，它的结构如下所示：

 

     我们按照 user_id%10 的规则将它分以下为10个数据表：

     tb1_posts_0
     tb1_posts_1
     tb1_posts_2
     tb1_posts_3
     tb1_posts_4
     tb1_posts_5
     tb1_posts_6
     tb1_posts_7
     tb1_posts_8
     tb1_posts_9

     这样，当tb1_posts表的记录数目是上百万级时，它实际上是分布在10个分区表中，每一张表的记录数保持在相对较少的数量，有利于减少查询的时间，避免对同一张表的频繁读写，从而为数据库减少不必要的开销。

 

数据表分布在不同的物理硬盘上（水平分区）

    分区主要有两种形式：水平分区与垂直分区。
 
    （1）水平分区
     水平分区（Horizontal Partitioning）是对表的行进行分区，通过这样的方式使得不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（一个或多个分区）。所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。例如，一个包含10年发票记录的表可以分为10个不同的分区，每个分区都包含其中一年的记录（这里具体使用的分区方式一定要通过某个属性列来分割，譬如这里使用的列是年份）。
 
    （2）垂直分区
     垂直分区（Vertical Partitioning）是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。例如，一个包含了大 TEXT 和 BLOB 列的表，因为这些TEXT和BLOB列不经常被访问，所以这时就要把这些不经常使用的 TEXT 和 BLOB 列划分到另一个分区，以保证它们在数据库相关的同时还能提高访问速度。

2.分区类型

• RANGE分区(RANGE Partitioning)

 

    这种算法是指按照分区索引字段的范围进行分区，比如我们可以将user_id为1～10000的记录存储在一个分区中，而将10001～20000的用户存储在另一个分区中，以此类推。

 

    再引用官方的一个例子：创建 RANGE 分区表的语法

 

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
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、p1、p2、p3 表示分区的名称，MAXVALUE 表示最大的可能的整数值。

• List分区（LIST Partitioning）

 

    这种算法将对分区索引字段的每个可能的结果创建一个分区映射关系，这个映射关系将会非常庞大。

 

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

 

地区	商店ID 号
北区	3, 5, 6, 9, 17
东区	1, 2, 10, 11, 19, 20
西区	4, 12, 13, 14, 18
中心区	7, 8, 15, 16

  

    用 List 分区语法如下：

CREATE TABLE employees (

    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),--数据和索引放在默认的位置

    PARTITION pEast VALUES IN (1,2,10,11,19,20)

DATA DIRECTORY = '/disk0/data'

   INDEX DIRECTORY = '/disk0/idx',--把数据和索引放在不同的磁盘上
    PARTITION pWest VALUES IN (4,12,13,14,18)

DATA DIRECTORY = '/disk1/data'

   INDEX DIRECTORY = '/disk1/idx',--当然，也可以是不同的物理硬盘上，如果有多个物理硬盘的话

    PARTITION pCentral VALUES IN (7,8,15,16)

DATA DIRECTORY = '/disk2/data'

   INDEX DIRECTORY = '/disk2/idx'

 );

• Hash分区（Hash Partitioning）

   刚才我们通过 user_id%10 来实现分区便是这种算法，它非常容易实现。

   例如，下面的语句创建了一个使用基于"store_id"列进行哈希处理的表，该表被分成了4个分区：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

    它有4个分区，MySQL将通过用户函数（HASH(store_id)）返回的结果的模数来确定使用哪个编号（N）的分区来保存每条记录。在上例中，N 是这样计算的：

MOD(7,4)  -- 7表示的是 store_id
=  MOD(7,4) -- 取模运算
=  3 -- 表示第4个分区

   哈希函数适用只对单个表列进行计算，并且它的值随列值进行一致地增大或减小，表达式值和它所基于的列的值变化越接近，MySQL就可以越有效地使用该表达式来进行HASH分区。
   换句话说，如果列值与表达式值之比的曲线图越接近由等式"y=nx"（其中n为非零的常数）描绘出的直线，则该表达式越适合于哈希。这是因为，表达式的非线性越严重，分区中数据产生非均衡分布的趋势也将越严重。

   LINEAR HASH分区（LINEAR HASH Partitioning）

   线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数。

   按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（terabytes）数据的表。它的缺点在于，与使用常规HASH分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 4;

    通过以上可知，分区通过设置任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数。在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。函数根据用户指定的分区类型来选择，把用户提供的表达式的值作为参数。该表达式可以是一个整数列值，或一个作用在一个或多个列值上并返回一个整数的函数。这个表达式的值传递给分区函数，分区函数返回一个表示那个特定记录应该保存在哪个分区的序号。

3.分区的约束和限制

    如果分区表使用的是 InnoDB  存储引擎，那么 DATA DIRECTORY （数据目录）和 INDEX DIRECTORY （索引目录）配置选项无效。

       在 Windows 平台下，不管使用的是 InnoDB 存储引擎还是 MyISAM 存储引擎， DATA DIRECTORY （数据目录）和 INDEX DIRECTORY （索引目录）配置选项也是无效的。

   很多时候，使用分区就不要再使用主键，否则可能影响性能。

   只能通过 int 类型的字段或者返回 int 类型的表达式来分区。

    每个表最多 1024 个分区，不可能无限制扩展分区，而且过度使用分区往往会消耗大量系统内存。

   采用分区的表不支持外键，相关的约束逻辑必须通过程序来实现。

   分区后，可能会造成索引失效，需要验证分区可行性。