MySQL分区技术详解-优快云博客

本文链接：https://blog.youkuaiyun.com/scholar_man/article/details/48288159

mysql由5.1版本开始支持分区,虽然还是 not production-ready,但也是值得期待的一个新特性,关于分区,mysql的手册已经讲得比较详细,以下内容只是从手册的摘抄:

分区的一些优点：

1)与单个磁盘或文件系统分区相比，可以存储更多的数据。

2)对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区来很方便地实现。

3)一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE 语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。

4)涉及到例如SUM() 和 COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。

5)通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

mysql支持的分区类型

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
)；

LIST 分区：类似RANGE分区,它们的主要区别在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个集合，而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,
    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),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
)；

如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“INSERT”查询将失败并报错

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

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

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

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

线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“PARTITION BY” 子句中添加“LINEAR”关键字，如下面所示：

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

KEY 分区：按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL 服务器提供。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；

对于使用其他存储引擎的表，服务器使用其自己内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则

通过线性KEY分割一个表也是可能的。下面是一个简单的例子：

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

子分区:子分区是分区表中每个分区的再次分割

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,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000)
        (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE
        (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

子分区可以用于特别大的表，在多个磁盘间分配数据和索引。假设有6个磁盘，分别为/disk0， /disk1， /disk2等。现在考虑下面的例子：

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 s0
                DATA DIRECTORY = '/disk2/data'
                INDEX DIRECTORY = '/disk2/idx',
            SUBPARTITION s1
                DATA DIRECTORY = '/disk3/data'
                INDEX DIRECTORY = '/disk3/idx'
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE
        (
            SUBPARTITION s0
                DATA DIRECTORY = '/disk4/data'
                INDEX DIRECTORY = '/disk4/idx',
            SUBPARTITION s1
                DATA DIRECTORY = '/disk5/data'
                INDEX DIRECTORY = '/disk5/idx'
        )
    )；

分区管理

从一个按照RANGE或LIST分区的表中删除一个分区，可以使用带一个DROP PARTITION子句的ALTER TABLE命令来实现

ALTER TABLE tr DROP PARTITION p2;

由“ALTER TABLE ... DROP PARTITION”语句引起的、从表中删除的行数并没有被服务器报告出来

如果希望改变表的分区而又不丢失数据，使用“ALTER TABLE ... REORGANIZE PARTITION”语句

一个REORGANIZE PARTITION语句也可以用来合并相邻的分区。可以使用如下的语句恢复成员表到它以前的分区：

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
PARTITION p0 VALUES LESS THAN (1970)
)；

使用“REORGANIZE PARTITION”拆分或合并分区，没有数据丢失。在执行上面的语句中，MySQL 把保存在分区s0和s1中的所有数据都移到分区p0中。

“REORGANIZE PARTITION”的基本语法是：

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO (partition_definitions)；

不能使用与从按照RANGE或LIST分区的表中删除分区相同的方式来从HASH或KEY分区的表中删除分区。但是，可以使用“ALTER TABLE ... COALESCE PARTITION”命令来合并HASH或KEY分区。

COALESCE不能用来增加分区的数量,要增加顾客表的分区数量从12到18，使用“ALTER TABLE ... ADD PARTITION”,具体如下：

ALTER TABLE clients ADD PARTITION PARTITIONS 18；

===================================================================

MySQL5.1提供的分区技术，令人对其充满希望。

根据官方文档，分区技术能够极大的帮助DBA人员。支持的分区模式包括：

Range

List

Hash

Key

----

安装配置记录：

wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.16-beta-linux-i686-icc-glibc23.tar.gz/from/http://mysql.cdpa.nsysu.edu.tw/

tar -xzvf mysql-5.1.16-beta-linux-i686-icc-glibc23.tar.gz

mv mysql-5.1.16-beta-linux-i686-icc-glibc23 /usr/local/mysql-5.1

修改启动脚本，启动

检查当前版本是否支持分区：

SHOW VARIABLES LIKE '%partition%';

测试带分区的表：(From 手册)

CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE(YEAR(purchased))
(
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005)
);

INSERT INTO tr VALUES
(1, 'desk organiser', '2003-10-15'),
(2, 'CD player', '1993-11-05'),
(3, 'TV set', '1996-03-10'),
(4, 'bookcase', '1982-01-10'),
(5, 'exercise bike', '2004-05-09'),
(6, 'sofa', '1987-06-05'),
(7, 'popcorn maker', '2001-11-22'),
(8, 'aquarium', '1992-08-04'),
(9, 'study desk', '1984-09-16'),
(10, 'lava lamp', '1998-12-25');

从服务器上可以看到文件结构为：

10.10.82.83 [testuser]$ l
total 404M
-rw-rw---- 1 mysql mysql 8.5K 2007-03-14 15:26:29 tr.frm
-rw-rw---- 1 mysql mysql 32 2007-03-14 15:26:29 tr.par
-rw-rw---- 1 mysql mysql 68 2007-03-14 15:26:48 tr#P#p0.MYD
-rw-rw---- 1 mysql mysql 1.0K 2007-03-14 15:26:48 tr#P#p0.MYI
-rw-rw---- 1 mysql mysql 48 2007-03-14 15:26:48 tr#P#p1.MYD
-rw-rw---- 1 mysql mysql 1.0K 2007-03-14 15:26:48 tr#P#p1.MYI
-rw-rw---- 1 mysql mysql 44 2007-03-14 15:26:48 tr#P#p2.MYD
-rw-rw---- 1 mysql mysql 1.0K 2007-03-14 15:26:48 tr#P#p2.MYI
-rw-rw---- 1 mysql mysql 84 2007-03-14 15:26:48 tr#P#p3.MYD
-rw-rw---- 1 mysql mysql 1.0K 2007-03-14 15:26:48 tr#P#p3.MYI

说明创建分区成功。

测试分区管理，删除：

ALTER TABLE tr DROP PARTITION p2;

添加：

ALTER TABLE tr ADD PARTITION (PARTITION p2 VALUES LESS THAN (2000));

注意：

1，删除分区后，分区内的数据也不可恢复的删除了。

2，只有range和list分区可以进行无数据影响的添加操作。其他种类的分区，不要进行分区管理。或者是，先把数据全导出，改变分区结构后再导入。

3，作为分区依据的列值是可以修改的，修改后会转移到对应分区中。（这点很有意义哟～）

下面是实战：

CREATE TABLE `user` (
`id` int(11) not null auto_increment,
`cn` varchar(22) not null default '',
`nickname` varchar(32) not null default '',
`genderid` tinyint(1) unsigned default null,
`artnum` int(8) unsigned default '0',
`elitenum` int(8) unsigned default '0',
`commnum` int(11) not null default '0',
`delnum` int(8) unsigned default '0',
`loginnum` int(8) unsigned default '1',
`linetime` int(8) unsigned default '0',
`lastdate` datetime default null,
`power` int(10) not null default '0',
`score` int(8) default '0',
`inputdate` datetime default null,
`finallydate` datetime default null,
`ad3` tinyint(4) not null default '0',
primary key (`id`),
key `nickname` (`nickname`),
key `finallydate` (`finallydate`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312 AUTO_INCREMENT=4589157
PARTITION BY RANGE(id)
(
PARTITION p5 VALUES LESS THAN (5000000),
PARTITION p6 VALUES LESS THAN (6000000),
PARTITION p7 VALUES LESS THAN (7000000),
PARTITION p8 VALUES LESS THAN (8000000),
PARTITION p9 VALUES LESS THAN (9000000),
PARTITION p10 VALUES LESS THAN (10000000),
PARTITION p11 VALUES LESS THAN (11000000),
PARTITION p12 VALUES LESS THAN (12000000),
PARTITION p13 VALUES LESS THAN (13000000),
PARTITION p14 VALUES LESS THAN (14000000),
PARTITION p15 VALUES LESS THAN (15000000),
PARTITION p16 VALUES LESS THAN (16000000),
PARTITION p0 VALUES LESS THAN MAXVALUE
);

关入400万数据，性能对比：

（待补充）

方案二，使用Key进行划分

create table `user2` (
`cn` varchar(22) not null default '',
`nickname` varchar(32) not null default '',
`genderid` tinyint(1) unsigned default null,
`artnum` int(8) unsigned default '0',
`elitenum` int(8) unsigned default '0',
`commnum` int(11) not null default '0',
`delnum` int(8) unsigned default '0',
`loginnum` int(8) unsigned default '1',
`linetime` int(8) unsigned default '0',
`lastdate` datetime default null,
`power` int(10) not null default '0',
`score` int(8) default '0',
`inputdate` datetime default null,
`finallydate` datetime default null,
`ad3` tinyint(4) not null default '0',
primary key (`cn`),
key `nickname` (`nickname`),
key `finallydate` (`finallydate`)
) engine=myisam default charset=gb2312
partition by key(cn)
partitions 3;

增加分区数：ALTER TABLE user2 ADD PARTITION PARTITIONS 10;

减少分区数：ALTER TABLE user2 COALESCE PARTITION 6;

增加和减少分区时，数据并不丢失。

一个不很方便的地方在于，当划分分区时，有unique的列时可能会拒绝操作。

另外，关于性能方面。range,list划分会对提高性能有帮助，但是作用多大，还是跟具体应用有关的。可以肯定的是，不会比我们传统上用程序逻辑将数据分散到多个表的方法效率更高，只是更方便而已。对于hash,key的划分，需要消耗大量计算时间，如果设计不当，可能还会造成性能的下降。所以，这也是我没有立即写性能对比数据的原因。

以后在实际应用中，采用分区技术确实能够有效提高性能得到验证后，再回来补充性能部分的内容。

～～呵呵～～

=================================================================

mysql按时间字段进行分区，后跨分区查询

MYSQL版本:MySQL5.1.7-noinstall-beta(win32)
--建表语句
DROP TABLE IF EXISTS MD_TEST;
CREATE TABLE MD_TEST
(
STAT_TIME DATETIME NOT NULL,
VALUE INT UNSIGNED NOT NULL
)
PARTITION BY RANGE (DAYOFYEAR(STAT_TIME)) (
PARTITION p1 VALUES LESS THAN (1),
PARTITION p2 VALUES LESS THAN (2),
PARTITION p3 VALUES LESS THAN MAXVALUE);
--插入测试数据库
INSERT INTO `md_test` (`STAT_TIME`, `VALUE`) VALUES
  ('2007-01-01',100),
  ('2007-01-02',200),
  ('2007-01-21',120);
--跨分区查询
SELECT STAT_TIME,SUM(VALUE) FROM md_test
WHERE STAT_TIME>=DATE'2007-01-01' AND STAT_TIME<=DATE'2007-01-03'
GROUP BY STAT_TIME

mysql的分区