MySQL 分区表以及操作

原创 2017年07月24日 15:23:55

对现有表进行分表, 对过期数据进行归档等操作。

分表的一般 参考(只是参考): 表体积大于2g,简单查询表数据超过1000w行,复杂查询表超过200w行。
两种分表的思路

  • 横行分表 比较常见的是按时间切分
  • 纵向分表 对于字段的冷热程度区分很明显的情况

注意的点

  • 分表之前要搞清楚数据库引擎,数据现有的量,多大磁盘空间,多少行
  • 分表之前 需要备份数据
  • 分区之后,索引和数据都会分区,无法进行单独的设置
  • 适合有历史归档的情况, 热点数据都集中在最后的行中
  • 分区表无法使用外键约束
  • 每个存储引擎会有一些具体的实现,可能不同

请谨慎使用

分区表

分区表是数据库层面的实现,应用层基本不用关心。一些限制

  • 5.6.7版本之前 最多有1024个分区,之后的版本可以使用 8192个分区,最新版本的mysql会有很多优化
  • 不能使用外键约束
  • 主表的所有唯一索引(包括主键)都必须包含分区字段。文档解释
  • 插入频繁的数据,使用范围为分区条件的不要设置太多分区(100以内),查询分区也有消耗

查看是否支持分区表

SHOW VARIABLES LIKE "%partition%";

还有一个,如果您的表之前使用自增id,直接alter成分区表,可能需要把分区字段加到主键中

ALTER TABLE  `auth_user` DROP PRIMARY KEY , ADD PRIMARY KEY ( `id`, `date_joined`);

下面摘抄网络, 以 5.6 版本为基准吧,5.7分区表还没看,看到说有变化。

RANGE分区

根据范围分区,范围应该连续但是不重叠,使用PARTITION BY RANGE, VALUES LESS THAN关键字。不使用COLUMNS关键字时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
);

根据TIMESTAMP范围:

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

这里还需要有 分区的删除,增加等操作

添加COLUMNS关键字可定义非integer范围及多列范围,不过需要注意COLUMNS括号内只能是列名,不支持函数;多列范围时,多列范围必须呈递增趋势:

根据DATE、DATETIME范围:

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
    PARTITION p0 VALUES LESS THAN ('1960-01-01'),
    PARTITION p1 VALUES LESS THAN ('1970-01-01'),
    PARTITION p2 VALUES LESS THAN ('1980-01-01'),
    PARTITION p3 VALUES LESS THAN ('1990-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

根据多列范围:

CREATE TABLE rc3 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (0,10),
    PARTITION p1 VALUES LESS THAN (10,20),
    PARTITION p2 VALUES LESS THAN (10,30),
    PARTITION p3 VALUES LESS THAN (10,35),
    PARTITION p4 VALUES LESS THAN (20,40),
    PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
 );

List分区

根据具体数值分区,每个分区数值不重叠,使用PARTITION BY LIST、VALUES IN关键字。跟Range分区类似,不使用COLUMNS关键字时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),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

数值必须被所有分区覆盖,否则插入一个不属于任何一个分区的数值会报错。

mysql> CREATE TABLE h2 (
    ->   c1 INT,
    ->   c2 INT
    -> )
    -> PARTITION BY LIST(c1) (
    ->   PARTITION p0 VALUES IN (1, 4, 7),
    ->   PARTITION p1 VALUES IN (2, 5, 8)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1525 (HY000): Table has no partition for value 3

当插入多条数据出错时,如果表的引擎支持事务(Innodb),则不会插入任何数据;如果不支持事务,则出错前的数据会插入,后面的不会执行。
可以使用IGNORE关键字忽略出错的数据,这样其他符合条件的数据会全部插入不受影响。

mysql> TRUNCATE h2;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM h2;
Empty set (0.00 sec)

mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
Query OK, 3 rows affected (0.00 sec)
Records: 5  Duplicates: 2  Warnings: 0

mysql> SELECT * FROM h2;
+------+------+
| c1   | c2   |
+------+------+
|    7 |    5 |
|    1 |    9 |
|    2 |    5 |
+------+------+
3 rows in set (0.00 sec)

与Range分区相同,添加COLUMNS关键字可支持非整数和多列。

Hash分区

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将按照主键-唯一索引的顺序选择,当找不到唯一索引时报错

分区表使用

在线改动和测试

如果有downtime,采用临时表的方式,新建一个 和原表一样的分区表,然后进行插入工作,最后进行修改名称。 如果有主从,感觉还是影响很大呀。

加一个小例子把,对已经存在表添加分区

mysql> create table orders (id int, st int, whatever varchar(10), primary key (id));
Query OK, 0 rows affected (0.06 sec)

mysql> ALTER TABLE orders DROP PRIMARY KEY, ADD PRIMARY KEY(id, st);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE orders PARTITION BY LIST(st) (
    ->           PARTITION p0 VALUES IN (20,10),
    ->           PARTITION p1 VALUES IN (0,-10)
    -> );
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

文章地址 http://blog.csdn.net/orangleliu/article/details/57088338
MySQL对数据表已有表进行分区表

版权声明:本文为orangleliu (http://blog.csdn.net/orangleliu/)原创文章,自由传播,文章转载请声明, 多谢。

相关文章推荐

OpenResty 限制下载速度

http://blog.donatas.net/blog/2017/07/25/limit-bandwidth-openresty/ Nginx 有两个非常棒的模块用来限制响应(responses)的...

[django]禁用 Django Rest Framework 的 Browsable API

如果您用django来开发 restful 接口一定听说过或者使用过 Django Rest Framework 这个项目(简称DRF), 它可以帮助django用户方面快捷的开发 restful a...

Oracle数据库分区表操作方法

  • 2009年02月15日 19:52
  • 42KB
  • 下载

ORACLE分区表的概念及操作

  • 2013年12月10日 11:25
  • 44KB
  • 下载

Oracle 分区表和索引操作

摘要:在大量业务数据处理的项目中,能考虑使用分区表来提高应用系统的性能并方便数据管理,本文周详介绍了分区表的使用。  在大型的企业应用或企业级的数据库应用中,要处理的数据量通常能达到几十到几百GB,有...

生产中使用和管理MySQL分区表

  • 2017年03月13日 12:27
  • 206KB
  • 下载

ORACLE分区表操作

ORACLE分区表的操作应用 摘要:在大量业务数据处理的项目中,可以考虑使用分区表来提高应用系统的性能并方便数据管理,本文详细介绍了分区表的使用。   在大型的企业应用或企业级的数据库应用中,要处理...

MYSQL分区表测试

  • 2010年09月25日 15:46
  • 82KB
  • 下载

分区表的基本操作

分区表的基本操作 事例: SQL> create table t_part (id int,col2 int,col3 int)    2  partition by range (id)    3 ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL 分区表以及操作
举报原因:
原因补充:

(最多只允许输入30个字)