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/)原创文章,自由传播,文章转载请声明, 多谢。

MYSQL的分区操作

DROP TABLE IF EXISTS PartitionTest; #创建表 CREATE TABLE PartitionTest ( pid INT N...
  • soldierluo
  • soldierluo
  • 2014年03月26日 16:32
  • 748

Mysql中分区表的使用总结

当数据量非常大时(表的容量到达GB或者是TB),如果仍然采用索引的方式来优化查询,由于索引本生的消耗以及大量的索引碎片的产生,查询的过程会导致大量的随机I/O的产生,在这种场景下除非可以很好的利用覆盖...
  • linlzk
  • linlzk
  • 2016年06月08日 15:54
  • 2099

MySQL水平分区表实际操作总结

原文链接:http://shuailan.iteye.com/blog/1056247 MySQL分区表操作代码 (本案例按月分区):        1. 创建分区表  CR...
  • boli1020
  • boli1020
  • 2013年12月24日 11:05
  • 3789

不建议mysql分区表

缘起:业内进行一些技术交流的时候也更多的是自己分库分表,而不是使用分区表。   解决什么问题? 回答:当mysql单表的数据库过大时,数据库的访问速度会下降,“数据量大”问题的常见解决方案是“水...
  • qq_19707521
  • qq_19707521
  • 2017年03月01日 11:01
  • 4385

数据切分——Mysql分区表的建立及性能分析

Mysql的安装方法可以参考:         http://blog.csdn.net/jhq0113/article/details/43812895                 Mys...
  • jhq0113
  • jhq0113
  • 2015年03月24日 16:44
  • 18664

MYSQL数据库创建表分区

MYSQL数据库通过日期创建表分区,日期需要使用日期函数(年月日时分秒) 1.用日期创建分区事例 DROP TABLE IF EXISTS `t_tfm_city_trafficindex...
  • superhoy
  • superhoy
  • 2016年09月13日 17:35
  • 3664

mysql 非分区表转化成分区表

1.创建分区表 CREATE TABLE `app_visitor_logger2` ( `item_id` INT(11) NOT NULL AUTO_INCREMENT, `page_title`...
  • kongdeqian1988
  • kongdeqian1988
  • 2017年01月06日 17:53
  • 433

大数据下的mysql--简单分区分表

一,什么是mysql分表,分区 什么是分表,从表面意思上看呢,就是把一张表分成N多个小表,具体请看mysql分表的3种方法 什么是分区,分区呢就是把一张表的数据分成N多个区块,这些区块可以在同一个磁盘...
  • xvshu
  • xvshu
  • 2016年01月15日 18:35
  • 8173

mysql分区表,循环分区,触发器自动删除

在网络上看到一篇之前跟我在新大陆公司业务差不多的关于分区表的建立与处理办法,回忆了这一块知识,觉得有必要记录下来,所以转载了一下http://blog.chinaunix.net/uid-240869...
  • u014001866
  • u014001866
  • 2016年10月19日 10:58
  • 555

故障案例--mysql5.5分区表的一个坑

故障现象 db每隔一段时间就异常重启,查看DB错误日志的错误日志Database was not shut down normally相关的信息,而查看/var/log/message并没有发现什么异...
  • cug_jiang126com
  • cug_jiang126com
  • 2016年07月21日 17:19
  • 4583
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL 分区表以及操作
举报原因:
原因补充:

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