MySQL分区与传统的分库分表

Hash分区主要用来确保数据在预先确定数目的分区中平均分布，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 HASH(store_id)
PARTITIONS 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( YEAR(hired) )
PARTITIONS 4;

Hash分区也存在与传统Hash分表一样的问题，可扩展性差。MySQL也提供了一个类似于一致Hash的分区方法－线性Hash分区，只需要在定义分区时添加LINEAR关键字，如果对实现原理感兴趣，可以查看官方文档。

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;

Key分区

按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的 哈希函数是由MySQL
服务器提供。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的
哈希函数，这些函数是基于与PASSWORD()一样的运算法则。

Key分区与Hash分区很相似，只是Hash函数不同，定义时把Hash关键字替换成Key即可，同样Key分区也有对应与线性Hash的线性Key分区方法。

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

另外，当表存在主键或唯一索引时可省略Key括号内的列名，Mysql将按照主键－唯一索引的顺序选择，当找不到唯一索引时报错。

子分区

子分区是分区表中每个分区的再次分割。创建子分区方法：

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

和

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0
                DATA DIRECTORY = '/disk0/data'
                INDEX DIRECTORY = '/disk0/idx',
            SUBPARTITION s1
                DATA DIRECTORY = '/disk1/data'
                INDEX DIRECTORY = '/disk1/idx'
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2
                DATA DIRECTORY = '/disk2/data'
                INDEX DIRECTORY = '/disk2/idx',
            SUBPARTITION s3
                DATA DIRECTORY = '/disk3/data'
                INDEX DIRECTORY = '/disk3/idx'
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4
                DATA DIRECTORY = '/disk4/data'
                INDEX DIRECTORY = '/disk4/idx',
            SUBPARTITION s5
                DATA DIRECTORY = '/disk5/data'
                INDEX DIRECTORY = '/disk5/idx'
        )
    );

需要注意的是：每个分区的子分区数必须相同。如果在一个分区表上的任何分区上使用SUBPARTITION来明确定义任何子分区，那么就必须定义所有的子分区，且必须指定一个全表唯一的名字。

分区表的使用及查询优化

根据实际情况选择分区方法

对现有表分区的原则与传统分表一样。

传统的按照增量区间分表对应于分区的Range分区，比如对表的访问多是近期产生的新数据，历史数据访问较少，则可以按一定时间段（比如年或月）或一定数量（比如100万）对表分区，具体根据哪种取决于表索引结构。分区后最后一个分区即为近期产生的数据，当一段时间过后数据量再次变大，可对最后一个分区重新分区（REORGANIZE PARTITION）把一段时间（一年或一月）或一定数量（比如100万）的数据分离出去。

传统的散列方法分表对应于分区的Hash／Key分区，具体方法上面已经介绍过。

查询优化

分区的目的是为了提高查询效率，如果查询范围是所有分区那么就说明分区没有起到作用，我们用explain partitions命令来查看SQL对于分区的使用情况。

一般来说，就是在where条件中加入分区列。

比如表salaries结构为：

mysql> show create table salariesG;
*************************** 1. row ***************************
       Table: salaries
Create Table: CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (year(from_date))
(PARTITION p1 VALUES LESS THAN (1985) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (1986) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (1987) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (1988) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (1989) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (1991) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (1992) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (1993) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (1994) ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p12 VALUES LESS THAN (1996) ENGINE = InnoDB,
 PARTITION p13 VALUES LESS THAN (1997) ENGINE = InnoDB,
 PARTITION p14 VALUES LESS THAN (1998) ENGINE = InnoDB,
 PARTITION p15 VALUES LESS THAN (1999) ENGINE = InnoDB,
 PARTITION p16 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p17 VALUES LESS THAN (2001) ENGINE = InnoDB,
 PARTITION p18 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!