MySQL分区总结

时间:2022-09-23 19:32:53

MySQL支持RANGE,LIST,HASH和KEY四种分区。其中,每个分区又都有一种特殊的类型。对于RANGE分区,有RANGE COLUMNS分区。对于LIST分区,有LIST COLUMNS分区。对于HASH分区,有LINEAR HASH分区。对于KEY分区,有LINEAR KEY分区。具体如下:

RANGE分区

RANGE即范围分区,根据区间来判断位于哪个分区,譬如,在下例中,如果store_id小于6,则新增或修改的记录会被分配到p0分区,如果大于6小于11,则记录会被分配到p1分区,依次类推。类似于编程语言中的if ... elseif ...语句。

格式如下:

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

注意:

1. RANGE分区的返回值必须为整数。

2. PARTITION p3 VALUES LESS THAN MAXVALUE 是非必需的。

RANGE COLUMNS分区

RANGE COLUMNS是RANGE分区的一种特殊类型,它与RANGE分区的区别如下:

1. RANGE COLUMNS不接受表达式,只能是列名。而RANGE分区则要求分区的对象是整数。

2. RANGE COLUMNS允许多个列,在底层实现上,它比较的是元祖(多个列值组成的列表),而RANGE比较的是标量,即数值的大小。

3. RANGE COLUMNS不限于整数对象,date,datetime,string都可作为分区列。

格式如下:

CREATE TABLE rcx (
a INT,
b INT,
c CHAR(3),
d INT
)
PARTITION BY RANGE COLUMNS(a,d,c) (
PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),
PARTITION p2 VALUES LESS THAN (15,30,'sss'),
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);

同RANGE分区类似,它的区间范围必须是递增的,有时候,列涉及的太多,不好判断区间的大小,可采用下面的方式进行判断:

mysql> SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);
+-----------------+-----------------+-----------------+
| (5,10) < (5,12) | (5,11) < (5,12) | (5,12) < (5,12) |
+-----------------+-----------------+-----------------+
| 1 | 1 | 0 |
+-----------------+-----------------+-----------------+
1 row in set (0.07 sec)

关于RANGE COLUMNS的更多说明,可参考MySQL官方文档:

http://dev.mysql.com/doc/refman/5.6/en/partitioning-columns-range.html

LIST分区

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

LIST COLUMNS分区

LIST COLUMNS分区同样是LIST分区的一种特殊类型,它和RANGE COLUMNS分区较为相似,同样不接受表达式,同样支持多个列支持string,date和datetime类型。

格式如下:

CREATE TABLE customers_1 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)
PARTITION BY LIST COLUMNS(renewal) (
PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',
'2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',
'2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',
'2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',
'2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
);

多列格式如下:

CREATE TABLE customers_2 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)
PARTITION BY LIST COLUMNS(city,last_name,first_name) (
PARTITION pRegion_1 VALUES IN (('Oskarshamn', 'Högsby', 'Mönsterås'),('Nässjö', 'Eksjö', 'Vetlanda')),
PARTITION pRegion_2 VALUES IN(('Vimmerby', 'Hultsfred', 'Västervik'),('Uppvidinge', 'Alvesta', 'Växjo'))
);

HASH分区

和RANGE,LIST分区不同的是,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;

注意:

1. HASH分区可以不用指定PARTITIONS子句,如上文中的PARTITIONS 4,则默认分区数为4。

2. 不允许只写PARTITIONS,而不指定分区数。

3. 同RANGE分区和LIST分区一样,PARTITION BY HASH (expr)子句中的expr返回的必须是整数值。

4. HASH分区的底层实现其实是基于MOD函数。譬如,对于下表

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY HASH( YEAR(col3) )
PARTITIONS 4;

如果你要插入一个col3为“2005-09-15”的记录,则分区的选择是根据以下值决定的:

MOD(YEAR('2005-09-01'),4)
= MOD(2005,4)
= 1

LINEAR HASH分区

LINEAR HASH分区是HASH分区的一种特殊类型,与HASH分区是基于MOD函数不同的是,它基于的是另外一种算法。

格式如下:

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;

说明:

1. 它的优点是在数据量大的场景,譬如TB级,增加、删除、合并和拆分分区会更快,缺点是,相对于HASH分区,它数据分布不均匀的概率更大。

2. 具体算法,可参考MySQL的官方文档

http://dev.mysql.com/doc/refman/5.6/en/partitioning-linear-hash.html

KEY分区

KEY分区其实跟HASH分区差不多,不同点如下:

1. KEY分区允许多列,而HASH分区只允许一列。

2. 如果在有主键或者唯一键的情况下,key中分区列可不指定,默认为主键或者唯一键,如果没有,则必须显性指定列。

3. KEY分区对象必须为列,而不能是基于列的表达式。

4. KEY分区和HASH分区的算法不一样,PARTITION BY HASH (expr),MOD取值的对象是expr返回的值,而PARTITION BY KEY (column_list),基于的是列的MD5值。

格式如下:

CREATE TABLE k1 (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;

在没有主键或者唯一键的情况下,格式如下:

CREATE TABLE tm1 (
s1 CHAR(32)
)
PARTITION BY KEY(s1)
PARTITIONS 10;

LINEAR KEY分区

同LINEAR HASH分区类似。

格式如下:

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

总结:

1. MySQL分区中如果存在主键或唯一键,则分区列必须包含在其中。

2. 对于原生的RANGE分区,LIST分区,HASH分区,分区对象返回的只能是整数值。

3. RANGE COLUMNS,LIST COLUMNS,KEY,LINEAR KEY分区对象只能是列,不能是基于列的表达式。

MySQL分区总结的更多相关文章

  1. Atitit 分区后的查询&&num;160&semi;&&num;160&semi;mysql分区记录的流程与原理

    Atitit 分区后的查询  mysql分区记录的流程与原理 1.1.1. ibd是MySQL数据文件.索引文件1 1.2. 已经又数据了,如何分区? 给已有的表加上分区 ]1 1.3. 分成4个区, ...

  2. Mysql 分区处理NULL的得方式

    MySQL分区处理NULL值得方式 一般情况下,MySQL的分区把NULL当做零值,或者一个最小值进行处理 对于range分区 create table test_null( id int ) par ...

  3. mysql分区及实例演示

    一.为什么要分区? 需求:大数据.解决方案:分而治之,更细一点即为.将大表和大索引分为一个更小的操作单元 在mysql中,分区允许将表.索引和索引编排表细分为更小的单元.分区后,每个分区有自己单独的名 ...

  4. mysql分区

    <?php /* 分区 目录 18.1. MySQL中的分区概述 18.2. 分区类型 18.2.1. RANGE分区 18.2.2. LIST分区 18.2.3. HASH分区 18.2.4. ...

  5. Mysql分区技术

    注:分区的语法可以看手册中有详细的写法和例子: show plugins; 此命令查看可有partition这个选项,有则mysql支持分区,没有的话,就可以升级一下mysql 实时监控一个命令执行情 ...

  6. 十五、mysql 分区之 分区管理

    1.mysql分区处理分区字段NULL值的方式 1.range分区null被当作最小值处理 2.list分区null值必须被枚举出来,否则将出错 3.hash/key分区 null值当作0处理 2.R ...

  7. MySQL分区技术 (一)

    4:MySQL 分区技术(是mysql 5.1以版本号后開始用->是甲骨文mysql技术团队维护人员以插件形式插入到mysql里面的技术) 眼下,针对海量数据的优化主要有2中方法: 1:大表拆成 ...

  8. Mysql 分区详解

    详见:http://blog.yemou.net/article/query/info/tytfjhfascvhzxcyt120 一.什么是表分区通俗地讲表分区是将一大表,根据条件分割成若干个小表.m ...

  9. MySQL 分区建索引

    200 ? "200px" : this.width)!important;} --> 介绍 mysql分区后每个分区成了独立的文件,虽然从逻辑上还是一张表其实已经分成了多张 ...

随机推荐

  1. 跨域之同源策略 Same-origin policy

    同源策略是浏览器中最基本的隔离潜在恶意文件的安全策略,他限制了来自不同源(origin)的文档或脚本之间的相互作用. 何谓同源 在跨域之URL中介绍过一个URL的标准格式如下: 协议类型://服务器地 ...

  2. MySQL中利用外键实现级联删除、更新

    MySQL支持外键的存储引擎只有InnoDB,在创建外键的时候,要求父表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引.在创建索引的时候,可以指定在删除.更新父表时,对子表进行的相应操作 ...

  3. 使用SpringAop 验证方法参数是否合法

    (原文地址:http://blog.****.net/is_zhoufeng/article/details/7683194) 1.依赖包    aspectjweaver.jar 其中Maven的配 ...

  4. PHP 向 MySql 中数据修改操作时,只对数字操作有效,非数字操作无效,怎么办?

    问题描述:   用PHP向MySql数据库中修改数据,实现增删改(数据库能正确连接) 经测试,代码只能对数字进行正常的增删改操作,非数字操作无效   但要在课程名称中输入中文,应该如果修改呢?   存 ...

  5. poj 3744 Scout YYF I &lpar;矩阵&rpar;

    Description YYF -p. Here is the task, given the place of each mine, please calculate the probality t ...

  6. 【Java数据结构学习笔记之二】Java数据结构与算法之栈&lpar;Stack&rpar;实现

      本篇是java数据结构与算法的第2篇,从本篇开始我们将来了解栈的设计与实现,以下是本篇的相关知识点: 栈的抽象数据类型 顺序栈的设计与实现 链式栈的设计与实现 栈的应用 栈的抽象数据类型   栈是 ...

  7. Linux设备驱动模型之platform&lpar;平台&rpar;总线详解

    /********************************************************/ 内核版本:2.6.35.7 运行平台:三星s5pv210 /*********** ...

  8. 人生苦短之---认识Python

    认识 Python 人生苦短,我用 Python —— Life is short, you need Python 目标 Python 的起源 为什么要用 Python? Python 的特点 Py ...

  9. Jenkins CLI 命令详解

    笔者在前文<通过 CLI 管理 Jenkins Server>中介绍了如何通过 SSH 或客户端命令行的方式管理 Jenkins Server,限于篇幅,前文主要的目的是介绍连接 Jenk ...

  10. python 调用 C语言函数

    python可以直接调用C语言的函数,本文记录用ctypes调用c语言的方法. test.c #include <stdio.h> int test(char *temp) { print ...