mysql in partition_MySQL Partition分区扫盲

最新推荐文章于 2021-12-06 06:00:00 发布

好端端

最新推荐文章于 2021-12-06 06:00:00 发布

阅读量381

点赞数

文章标签： mysql in partition

本文链接：https://blog.csdn.net/weixin_28834765/article/details/113133925

版权

本文介绍了MySQL的Partition功能，从5.1.3版本开始支持，包括RANGE、LIST、HASH、KEY四种分区类型，其中RANGE最为常用。通过实例展示了如何创建、管理分区，并解释了分区对查询性能的提升。文章提到了分区的限制，如主键必须包含分区字段，且只能通过整数字段或返回整数的表达式进行分区。此外，文中还探讨了不同场景下选择不同分区类型的原则，例如在主从结构中，主服务器可能更适合使用HASH分区。最后，讨论了分区的一些高级操作，如分区重建和新增分区。

摘要由CSDN通过智能技术生成

MySQL从5.1.3开始支持Partition，你可以使用如下命令来确认你的版本是否支持Partition：

mysql> SHOW VARIABLES LIKE '%partition%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| have_partitioning | YES |

+-------------------+-------+

MySQL支持 RANGE，LIST，HASH，KEY 分区类型，其中以RANGE最为常用：

CREATE TABLE foo (

id INT NOT NULL AUTO_INCREMENT,

created DATETIME,

PRIMARY KEY( id, created )

) ENGINE=INNODB

PARTITION BY RANGE (TO_DAYS(created)) (

PARTITION foo_1 VALUES LESS THAN ( TO_DAYS('2009-01-01') ),

PARTITION foo_2 VALUES LESS THAN ( TO_DAYS('2010-01-01') )

)

partition表达式中用于分区的条件，一定要是数值型的字段(比如 id) 或者能返回数值的函数，如 TO_DAYS() , MONTH() 这样的函数

即便创建完分区，也可以在后期管理，比如说添加一个新的分区：

ALTER TABLE foo ADD PARTITION (

PARTITION foo_3 VALUES LESS THAN ( TO_DAYS('2011-01-01') )

)

或者删除一个分区L: ALTER TABLE FOO DROP PARTITION foo_3;

通过检索 information_schema 数据库，能看到我们刚刚创建的分区信息：

SELECT * FROM information_schema.PARTITIONS WHERE PARTITION_NAME IS NOT NULL

此时，打开MySQL的数据目 SHOW VARIABLES LIKE 'datadir'

如果MySQL配置设置了 Innodb file per table 为 ON 的话，由于上面定义的是 InnoDB，则会发现：

foo#p#foo_1.ibd

foo#p#foo_2.ibd

如果创建的是MyISAM表类型的话，则会发现：

foo#P#foo_1.MYD

foo#P#foo_1.MYI

foo#P#foo_2.MYD

foo#P#foo_2.MYI

由此可知通过分区，MySQL会把数据保存到不同的数据文件里，同时索引也是分区的，相对未分区的表来说，分区后单独的数据文件和索引文件的大小都明显降低，效率则明显提升。为了验证这一点，我们做如下实验：

INSERT INTO `foo` (`id`, `created`) VALUES ( 1, '2008-01-02 00:00:00' ) , ( 2, '2009-01-02 00:00:00' );

然后执行SQL: EXPLAIN PARTITIONS SELECT * FROM foo WHERE created = '2008-01-02';

会看到MySQL仅仅在foo_1分区执行这条查询。理论上效率肯定会快一些，至于具体多少，就看数据量了。实际应用分区的时候，我们还可以通过 DATA DIRECTORY 和 INDEX DIRECTORY 选项把不同的分区分散到不同的磁盘上，从而进步一提高系统的IO吞吐量。

重要提示：使用分区功能之后，相关查询最好都用EXPLAIN PARTITIONS过一遍，确认分区是否生效。

到底应该采用哪种分区类型呢？通常来说使用range类型是个不错的选择，不过也不尽然，比如说在主从结构中，主服务器由于很少使用SELECT查询，所以在主服务器上使用range类型的分区通常并没有太大意义，此时使用hash类型的分区相对更好一些，假设使用PARTITION BY HASH(id) PARTITIONS 10，那么当插入新数据时，会根据id把数据平均分散到各个分区上，由于文件小，所以效率高，更新操作会变得更快。

分区虽然很爽，但目前的实现还有很多限制：

主键或唯一索引必须包含分区字段: 如 PRIMARY KEY(id, created)

很多时候，使用了分区就不要再使用主键，否则可能影响性能。

只能通过int类型的字段或者返回int类型的表达式来分区：通常使用YEAR或TO_DAYS等函数。

每个表最多1024个分区：不可能无限制的扩展分区，而且过度使用分区往往会消耗大量系统内存。

采用分区的表不支持外键：相关的约束逻辑必须通过程序来实现

LIST 类型

CREATE TABLE category (

cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT ''

)

PARTITION BY LIST (cid) (

PARTITION p0 VALUES IN (0,4,8,12)

DATA DIRECTORY = '/data0/data'

INDEX DIRECTORY = '/data1/idx',

PARTITION p1 VALUES IN (1,5,9,13)

DATA DIRECTORY = '/data2/data'

INDEX DIRECTORY = '/data3/idx',

PARTITION p2 VALUES IN (2,6,10,14)

DATA DIRECTORY = '/data4/data'

INDEX DIRECTORY = '/data5/idx',

PARTITION p3 VALUES IN (3,7,11,15)

DATA DIRECTORY = '/data6/data'

INDEX DIRECTORY = '/data7/idx'

);

分成4个区，数据文件和索引文件单独存放

[ 重建分区 ]

RANGE 分区重建:

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));

将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。

LIST 分区重建:

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));

将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。

HASH/KEY 分区重建:

ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;

用 REORGANIZE 方式重建分区的数量变成2，在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。

[ 新增分区 ]

ALTER TABLE `results` PARTITION BY RANGE ( MONTH(created) )

( PARTITION p0 VALUES LESS THAN (1),

PARTITION p1 VALUES LESS THAN (2) ,

PARTITION p2 VALUES LESS THAN (3) ,

PARTITION p3 VALUES LESS THAN (4) ,

PARTITION p4 VALUES LESS THAN (5) ,

PARTITION p5 VALUES LESS THAN (6) ,

PARTITION p6 VALUES LESS THAN (7) ,

PARTITION p7 VALUES LESS THAN (8) ,

PARTITION p8 VALUES LESS THAN (9) ,

PARTITION p9 VALUES LESS THAN (10) ,

PARTITION p10 VALUES LESS THAN (11),

PARTITION p11 VALUES LESS THAN (12),

PARTITION P12 VALUES LESS THAN MAXVALUE

);

默认分区限制分区字段必须是主键(PRIMARY KEY ) 的一部分

mysql Partition(分区)初探

mysql Partition(分区)初探表数据量大的时候一般都考虑水平拆分,即所谓的sharding.不过mysql本身具有分区功能,可以实现一定程度的水平切分. mysql是具有MERG ...

MySQL partition分区I

http://blog.csdn.net/binger819623/article/details/5280267 一. 分区的概念二. 为什么使用分区?(优点)三. ...

MYSQL之水平分区----MySQL partition分区I(5&period;1)

一. 分区的概念二. 为什么使用分区?(优点) 三. 分区类型四. 子分区五. 对分区进行修改(增加.删除.分解.合并) 六 ...

MySQL PARTITION 分区

MySQL HASH分区 http://www.cnblogs.com/chenmh/p/5644496.html RANGE分区:http://www.cnblogs.com/chenmh/p/56 ...

mysql partition分区

(转) 自5.1开始对分区(Partition)有支持 = 水平分区(根据列属性按行分)=举个简单例子:一个包含十年发票记录的表可以被分区为十个不同的分区,每个分区包含的是其中一年的记录. === 水 ...

mysql的partition分区

前言:当一个表里面存储的数据特别多的时候,比如单个.myd数据都已经达到10G了的话,必然导致读取的效率很低,这个时候我们可以采用把数据分到几张表里面来解决问题.方式一:通过业务逻辑根据数据的大小通过 ...

mysql表分区 partition

表分区 partition 当一张表的数据非常多的时候,比如单个.myd文件都达到10G, 这时,必然读取起来效率降低. 可不可以把表的数据分开在几张表上? 1: 从业务角度可以解决.. (分表,水平 ...

mysql的分区和分表

分区分区就是把一个数据表的文件和索引分散存储在不同的物理文件中. mysql支持的分区类型包括Range.List.Hash.Key,其中Range比较常用: RANGE分区:基于属于一个给定连续区 ...

MySQL表分区技术

MySQL表分区技术 MySQL有4种分区类型: 1.RANGE 分区 - 连续区间的分区 - 基于属于一个给定连续区间的列值,把多行分配给分区: 2.LIST 分区 - 离散区间的分区 - 类似于按 ...

随机推荐

Jenkins的一个bug-同时build一个项目两次导致失败

我们有一个job A, A只是配置了一些参数,它会去触发模板job B. 我一开始点击构建A, 马上发现参数配置不对,于是撤消了构建,但是我没有发现B已经被触发,我重新配置参数,然后再次构建A,这个时 ...

HTML5树叶飘落动画

查看效果:http://keleyi.com/keleyi/phtml/css3/15.htm 请使用Chrome浏览器查看本效果. html源代码: &l ...

maven dependencies 里面的包怎么导出

进入工程pom.xml 所在的目录下,输入以下命令:mvn dependency:copy-dependencies -DoutputDirectory=lib更简单的 mvn dependency: ...

IOS第七天(6&colon;UiTableView编辑模式, 拖动位置 ,滑动删除)

**********UiTableView编辑模式, 拖动位置 ,滑动删除 #import "HMViewController.h" @interface HMViewContro ...

Ubuntu查看磁盘空间命令(转载)

linux中df命令的功能是用来检查linux服务器的文件系统的磁盘空间占用情况.可以利用该命令来获取硬盘被占用了多少空间,目前还剩下多少空间等信息. 1．命令格式: df [选项] [文件] 2．命 ...

Application、Session、Cookie、ViewState的特性

http://blog.csdn.net/zyw_anquan/article/details/7664132 Application的特性: 存储的物理位置:服务器端内存. 存储的类型限制:任意 ...

Visual Studio的性能测试工具

vs果然是宇宙最强大的IDE,这句话我经常挂在嘴边,反正觉得它挺强大整个听技术经理说性能测试,然后我就觉得宇宙最强大的IDE应该也有测试工具吧,那么我就百度了一下,又看看vs的选项,果然真有一个性 ...

Merge OUTPUT 高级用法综合写的一个MergeTab的存储过程

因为工作中常用到合并两张表中的数据,主要是写下来给自己备忘,T-SQL 中 MERGE 的用法 WHEN MATCHED THEN UPDATE -- 中加了后面要更新的列是否都相等,如果相等就没 ...

[Alpha阶段]测试报告

[Alpha]阶段测试报告在测试过程中发现的BUG 在最后的测试阶段中,我们不可避免的遇到了各种各样的BUG.虽然大多数都不是严重的BUG,但是这些细枝末节的问题的堆积,依然会很大程度上降低用户 ...

常见模块(五) random模块

random随机函数中的常用方法 1.random.random 返回一个介于左闭右开[0.0, 1.0)区间的浮点数 print(random.random()) 2.random.randrang ...

好端端

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫