首页 / MYSQL / MySQL--RDS下的分区表实践

MySQL--RDS下的分区表实践

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL--RDS下的分区表实践，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7419字，纯文字阅读大概需要11分钟。

内容图文

　　实践背景
　　
　　项目中有的表空间太大，且行数太多，故决定对一些表进行分库分表。再研究选型方案的时候发现常用的一些分库分表的解决方案对业务代码修改较多，故决定采用MySQL的分区方案。
　　
　　其实在我个人看来，分区表就是MySQL帮我们实现了底层的分库分表，不需要涉及业务代码的修改，不需要关注分布式事务。因为就访问数据库而言，逻辑上还是只有一个表，但是实际上确有多个物理分区对象组成，会根据具体的分区规则查询具体的分区。
　　
　　介绍一下这次实践的表，表空间大小172G，1亿2千万条记录。
　　
　　数据库版本：RDS MySQL 5.6
　　
　　工具：阿里云DTS
　　
　　一、为什么分区？
　　
　　优点：
　　
　　对已过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据，它的效率远比DELETE高
　　
　　在where子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率
　　
　　例如下面语句：
　　
　　SELECT * FROM t PARTITION（p0，p1）WHERE c <5 仅选择与WHERE条件匹配的分区p0和p1中的记录
　　
　　涉及聚合函数SUM()、COUNT()的查询时，会在每个分区上并行处理
　　
　　分区把原本一个表的数据存储在多个物理磁盘上，实现了更高的IOPS
　　
　　缺点：
　　
　　无法使用外键，不支持全文索引（现在应该也没什么公司还在用外键吧）
　　
　　分区键设计不太灵活，如果不走分区键，很容易出现全表锁
　　
　　开发写一个SQL，不清楚mysql是怎么玩的
　　
　　二、RANGE分区
　　
　　目前MySQL支持范围分区（RANGE），列表分区（LIST），哈希分区（HASH）以及KEY分区四种。
　　
　　本文是以范围分区（RANGE）对时间进行的分区的，故我就简单介绍一下RANGE分区。更多分区类型详见官方文档MySQL 5.6 分区类型
　　
　　基于一个给定连续区间的列值，根据区间分配分区。最常见的是基于时间字段。其实基于分区的列最好是整型，如果日期型的可以使用函数转换为整型。MySQL 5.6支持的分区函数
　　
　　本例中使用TO_DAYS函数
　　
　　CREATE TABLE members (
　　
　　id VARCHAR(25) NOT NULL,
　　
　　firstname VARCHAR(25) NOT NULL,
　　
　　lastname VARCHAR(25) NOT NULL,
　　
　　username VARCHAR(16) NOT NULL,
　　
　　email VARCHAR(35),
　　
　　joindate DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
　　
　　PRIMARY KEY (id,joindate) USING BTREE,
　　
　　KEY idx_joindate (joindate) USING BTREE
　　
　　)ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
　　
　　PARTITION BY RANGE (TO_DAYS(joindate)) (
　　
　　PARTITION p0 VALUES LESS THAN (TO_DAYS('1960-01-01')),
　　
　　PARTITION p1 VALUES LESS THAN (TO_DAYS('1970-01-01')),
　　
　　PARTITION p2 VALUES LESS THAN (TO_DAYS('1980-01-01')),
　　
　　PARTITION p3 VALUES LESS THAN (TO_DAYS('1990-01-01')),
　　
　　PARTITION p4 VALUES LESS THAN MAXVALUE
　　
　　);
　　
　　PS:像例子中的如果你有主键或唯一索引，你必须把你的分区键也加上，其中joindate就是分区键，要不创建会失败！
　　
　　PS:像上面加了LESS THAN MAXVALUE，后面就不能新加分区了！！！
　　
　　示例：
　　
　　如下查询就会落在定义的p2分区内的索引上。故在查询的时候带上你的分区键就会走对应分区查询数据，如果你的条件跨越多个分区进行聚合函数SUM()、COUNT()的查询时，它会在每个分区上并行处理。如果没有带分区键查询就会全表查询。
　　
　　<Border BorderBrush="{TemplateBinding BorderBrush}" BorderThickness="{TemplateBinding BorderThickness}" Background="{TemplateBinding Background}" CornerRadius="3" SnapsToDevicePixels="true">
　　
　　<VisualStateManager.VisualStateGroups>
　　
　　<VisualStateGroup x:Name="ExpansionStates">
　　
　　<VisualStateGroup.Transitions>
　　
　　<VisualTransition GeneratedDuration="0:0:0.3">
　　
　　<VisualTransition.GeneratedEasingFunction>
　　
　　<QuarticEase EasingMode="EaseOut"/>
　　
　　</VisualTransition.GeneratedEasingFunction>
　　
　　</VisualTransition>
　　
　　</VisualStateGroup.Transitions>
　　
　　<VisualState x:Name="Expanded"/>
　　
　　<VisualState x:Name="Collapsed">
　　
　　<Storyboard>
　　
　　<DoubleAnimationUsingKeyFrames Storyboard.TargetProperty="(UIElement.Opacity)" Storyboard.TargetName="ExpandableContentControl">
　　
　　<EasingDoubleKeyFrame KeyTime="www.chenghylpt.com" Value="0"/>
　　
　　</DoubleAnimationUsingKeyFrames>
　　
　　<DoubleAnimationUsingKeyFrames Storyboard.TargetProperty="Percentage" Storyboard.TargetName="ExpandableContentControl">
　　
　　<EasingDoubleKeyFrame KeyTime="www.renheyL.com" Value="0"/>
　　
　　</DoubleAnimationUsingKeyFrames>
　　
　　</Storyboard>
　　
　　</VisualState>
　　
　　</VisualStateGroup>
　　
　　</VisualStateManager.VisualStateGroups>
　　
　　<DockPanel>
　　
　　<ToggleButton x:Name="HeaderSite" ContentTemplate="{TemplateBinding HeaderTemplate}" ContentTemplateSelector="{TemplateBinding HeaderTemplateSelector}" Content="{TemplateBinding Header}" DockPanel.Dock="Top" Foreground="{TemplateBinding Foreground}" FontWeight="{TemplateBinding FontWeight}" FocusVisualStyle="{StaticResource ExpanderHeaderFocusVisual}" FontStyle="{TemplateBinding FontStyle}" FontStretch="{TemplateBinding FontStretch}" FontSize="{TemplateBinding FontSize}" FontFamily="{TemplateBinding FontFamily}" HorizontalContentAlignment=www.feironggw.cn"{TemplateBinding HorizontalContentAlignment}" IsChecked="{Binding IsExpanded, Mode=TwoWay, RelativeSource={RelativeSource TemplatedParent}}www.ztylegw.cn" Margin="1" MinWidth="0" MinHeight="0" Padding="{TemplateBinding Padding}" Style="{StaticResource ExpanderDownHeaderStyle}" VerticalContentAlignment="{TemplateBinding VerticalContentAlignment}"/>
　　
　　<Primitives:ExpandableContentControl x:Name="ExpandableContentControl" HorizontalContentAlignment="{TemplateBinding HorizontalContentAlignment}"www.yifa5yl.com VerticalContentAlignment="{TemplateBinding VerticalContentAlignment}"
　　
　　Margin="{TemplateBinding Padding}" ClipToBounds="True"www.zbzxyL12.com>
　　
　　<ContentPresenter x:Name=www.chengmingdl.com"ExpandSite" DockPanel.Dock="Bottom" Focusable="false" HorizontalAlignment="{TemplateBinding HorizontalContentAlignment}" VerticalAlignment="{TemplateBinding VerticalContentAlignment}"/>
　　
　　</Primitives:ExpandableContentControl>
　　
　　</DockPanel>
　　
　　</Border>
　　
　　<ControlTemplate.Triggers>
　　
　　<Trigger Property="IsExpanded" Value="false">
　　
　　<Setter Property="IsHitTestVisible" TargetName="ExpandableContentControl" Value="False"/>
　　
　　</Trigger>
　　
　　...
　　
　　</ControlTemplate.Triggers>
　　
　　我在迁移完数据进行查询的时候发现一个特别有意思的现象，同一条SQL如果分区键的时间区间不一样，它会根据rows行数少的走不同的范围索引。至于它底层是怎么实现的我就没去研究了
　　
　　三、分区管理
　　
　　简单介绍了下范围分区，接下来说一下对分区常用的一下操作。
　　
　　分区管理包括对于分区的增加，删除，以及查询。更多详见官方文档MySQL 分区管理
　　
　　1.增加分区
　　
　　对于RANGE和LIST分区：
　　
　　alter table table_name add partition (partition p0 values ...(exp))
　　
　　#例
　　
　　ALTER TABLE members ADD PARTITION (TO_DAYS(www.hengxyul.com'2021-03-01'));
　　
　　2.删除分区
　　
　　删除了分区，同时也将删除该分区中的所有数据。如果删除了分区导致分区不能覆盖所有值，那么插入数据的时候会报错。
　　
　　alter table table_name drop partition p0;
　　
　　3.查询有多少个分区
　　
　　SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'members';
　　
　　四、数据迁移
　　
　　前面说了那么多概念，我说一下本次把大表数据迁移到分区表的过程。
　　
　　为什么会选择DTS呢？因为它可以不停机迁移数据，支持全量迁移和增量迁移，对原表影响不大。
　　
　　迁移过程如下：
　　
　　首先在RDS的同一个实例里面新建了一张同结构的分区表
　　
　　使用DTS创建迁移任务，迁移时候不要选择结构钱谦益，只选择全量+增量迁移
　　
　　然后还需要编辑下目标库表名，也就是做下映射从A->B的迁移
　　
　　停掉写入数据的任务，当任务队列为空时，等待几分钟暂停并结束迁移任务
　　
　　最后修改表名，完成数据迁移和切换（我在测试环境修改分区表名要一些时间，但RDS修改表名是秒改）
　　
　　参考官方文档：MySQL 5.6 分区
　　
　　以上纯属个人观点，如有不对欢迎指正。

内容总结

以上是互联网集市为您收集整理的MySQL--RDS下的分区表实践全部内容，希望文章能够帮你解决MySQL--RDS下的分区表实践所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/898356.html

来源：【匿名】

【上一篇】MySQL优化第一天【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【MySQL--RDS下的分区表实践】教程文章相关的互联网学习教程文章

MySQL分区表例子——List分区

列表分区(List分区)这里假设表中有一个sale_item_type 字段，数据类型为INT 型当sale_item_type 为1，3，5的时候，作为一个分区当sale_item_type 为2，4，6的时候，作为另一个分区当sale_item_type 为7，8的时候，作为最后一个分区初期分区定义mysql> CREATE TABLE sale_data2 ( -> sale_date DATETIME NOT NULL, -> sale_item_type INT NOT NULL , -> sale_money DECIMAL(10,2) NOT NULL -> ) -> PART...

MySQL5.7传输表空间——迁移分区表

Mysql 传输表空间--将InnoDB分区表复制到另一个实例（二）实验环境：（都是mysql5.7）源库：192.168.2.200 mysql5.7.16 zhangdb下的emp_2分区表的目标库：192.168.2.100 mysql5.7.18 test下（将zhangdb的emp表，导入到目标库的test schema下）--：在源数据库中创建测试分区表emp_2，然后导入数据MySQL [zhangdb]> CREATE TABLE emp_2(id BIGINT unsigned NOT NULL AUTO_INCREMENT,x VARCHAR(500) NOT NULL,y ...

Mysql5.6—分区表及独享表空间

1、先停止Mysql [root@localhost ~]# /etc/init.d/mysqld stop Shutting down MySQL.[确定] 2、修改 /etc/my.cnf配置文件 [root@localhost ~]# vi /etc/my.cnf [mysqld] innodb_file_per_table = 1 3、启动Mysql [root@localhost ~]# /etc/init.d/mysqld start Starting MySQL..[确定] 4、连接到Mysql数据库 [root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end wit...

详解MySQL分区表【代码】【图】

当数据库数据量涨到一定数量时，性能就成为我们不能不关注的问题，如何优化呢？常用的方式不外乎那么几种：　　1、分表，即把一个很大的表达数据分到几个表中，这样每个表数据都不多。　　　　优点：提高并发量，减小锁的粒度　　　　缺点：代码维护成本高，相关sql都需要改动　　2、分区，所有的数据还在一个表中，但物理存储数据根据一定的规则存放在不同的文件中，文件也可以放到另外磁盘上　　　　优点：代码维护量小，基本不...

Mysql临时表和分区表【代码】【图】

临时表与内存表内存表，指的是使用Memory引擎的表，建表语法是create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。除了这两个特性看上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表临时表，可以使用各种引擎类型。如果是使用InnoDB引擎或者MyISAM引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用Memory引擎。临时表特性建表语法是create te...

MySQL 分区表各个分区的行数

分区的信息是记录在information_schema.partitions 这个表里的。它不能直接定位行所在的分区，但它可查到每个分区中有多少行。例子： select partition_name as part,partition_expression as expr,partition_description as descr,table_rows from information_schema.partitions where table_schema= schema() and table_name=‘t‘;MySQL 分区表各个分区的行数标签：本文系统来源：http://www.cnblogs.com/JiangLe/p/4491462.h...

Mysql5.6—分区表及独享表空间

[root@localhost ~]# /etc/init.d/mysqld stop Shutting down MySQL.[确定] 2、修改 /etc/my.cnf配置文件 [root@localhost ~]# vi /etc/my.cnf [mysqld] innodb_file_per_table = 1 3、启动Mysql [root@localhost ~]# /etc/init.d/mysqld start Starting MySQL..[确定] 4、连接到Mysql数据库 [root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your...

MySQL之高级特性---分区表【代码】

CREATE TABLE sales (order_date DATETIME NOT NULL,-- Other columns omitted ) ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) ( PARTITION p_2010 VALUES LESS THAN (2010), PARTITION p_2011 VALUES LESS THAN (2011), PARTITION p_2012 VALUES LESS THAN (2012), PARTITION p_catchall VALUES LESS THAN MAXVALUE );PARTITION 分区子句可以使用各种函数。但有一个要求，表达式返回的值要是一个确定的整数，且不能是一个...

MySQL 分区表原理及使用详解【代码】

分区查询 1）查询某张表一共有多少个分区 mysql> select -> partition_name,-> partition_expression,-> partition_description,-> table_rows-> from -> INFORMATION_SCHEMA.partitions-> where-> table_schema=‘test‘-> and table_name = ‘emp‘; +----------------+----------------------+-----------------------+------------+ | partition_name | partition_expression | partition_description | table_r...

MySQL分区表【代码】【图】

CREATE TABLE sales (id INT AUTO_INCREMENT,amount DOUBLE NOT NULL,order_day DATETIME NOT NULL,PRIMARY KEY(id, order_day) ) ENGINE=Innodb PARTITION BY RANGE(YEAR(order_day)) (PARTITION p_2010 VALUES LESS THAN (2010),PARTITION p_2011 VALUES LESS THAN (2011),PARTITION p_2012 VALUES LESS THAN (2012),PARTITION p_catchall VALUES LESS THAN MAXVALUE);这段语句表示将表内数据按照order_dy的年份范围进行分区,201...

MySQL分区表管理【代码】

但是如果e2里面的数据不能够满足分区p0的要求的话,切分区就会失败.1737 - Found a row that does not match the partition只有再指定不验证的时候才不会报错ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;WITHOUT VALIDATION 指定的时候效率会更高,因为不再做逐行验证了. 子分区和没分区的表进行切换 1:假设创建一个分区表,带有子分区> CREATE TABLE es (-> id INT NOT NULL,-> fname VARCHAR...

MySQL5.5的分区表

MySQL 分区表原理及数据备份转移实战

1、分区表含义分区表定义指根据可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。 2、分区表优点...

MySQL 分区表探究细节《整理自官档》

1. Date Time分区When partitioning by KEY or LINEAR KEY, youcan use a DATE, TIME, or DATETIME column as thepartitioning column without performing any modification of the column value. Forexample, this table creation statement is perfectly valid in MySQL:如果考虑不转换列值，可采取key进行分区。CREATE TABLE members(firstname VARCHAR(25)NOT NULL,lastname VARCHAR(25)NOT NULL,username VARCHAR(16)NOT NU...

Mysql --- 分区表【代码】【图】

TABLE IF EXISTS p_user; CREATE TABLE p_user(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(225),login_date DATETIME )ENGINE=INNODB PARTITION BY RANGE (id) ( PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20), PARTITION p2 VALUES LESS THAN (30), PARTITION p3 VALUES LESS THAN MAXVALUE ) 分区表p_user，小于10的id存储在p0分区中，10到20之间的id存储在p1中，以此类推，大于30的id存储在p3...

首页 / MYSQL / MySQL--RDS下的分区表实践

MySQL--RDS下的分区表实践

内容导读

内容图文

内容总结

内容备注

内容手机端

【MySQL--RDS下的分区表实践】教程文章相关的互联网学习教程文章

MySQL分区表例子——List分区

MySQL5.7传输表空间——迁移分区表

Mysql5.6—分区表及独享表空间

详解MySQL分区表【代码】【图】

Mysql临时表和分区表【代码】【图】

MySQL 分区表各个分区的行数

Mysql5.6—分区表及独享表空间

MySQL之高级特性---分区表【代码】

MySQL 分区表原理及使用详解【代码】

MySQL分区表【代码】【图】

MySQL分区表管理【代码】

MySQL5.5的分区表

MySQL 分区表原理及数据备份转移实战

MySQL 分区表探究细节《整理自官档》

Mysql --- 分区表【代码】【图】

MYSQL - 相关标签

分区表 - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程