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) */

（编辑：厦门网）

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