文档章节

MySQL——RDS下的分区表实践

剧与
 剧与
发布于 07/24 17:30
字数 1502
阅读 1572
收藏 30

实践背景

      项目中有的表空间太大,且行数太多,故决定对一些表进行分库分表。再研究选型方案的时候发现常用的一些分库分表的解决方案对业务代码修改较多,故决定采用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()的查询时,它会在每个分区上并行处理。如果没有带分区键查询就会全表查询。

explain partitions select * from members WHERE joindate BETWEEN '1970-02-03' AND '1970-02-04';

 

 

我在迁移完数据进行查询的时候发现一个特别有意思的现象,同一条SQL如果分区键的时间区间不一样,它会根据rows行数少的走不同的范围索引。至于它底层是怎么实现的我就没去研究了

 

三、分区管理

      简单介绍了下范围分区,接下来说一下对分区常用的一下操作。

      分区管理包括对于分区的增加,删除,以及查询。更多详见官方文档MySQL 分区管理

    1.增加分区

      对于RANGE和LIST分区

alter table table_name add partition (partition p0 values ...(exp))

#例
ALTER TABLE members ADD PARTITION (TO_DAYS('2021-03-01'));

    2.删除分区

      删除了分区,同时也将删除该分区中的所有数据。如果删除了分区导致分区不能覆盖所有值,那么插入数据的时候会报错。

alter table table_name drop partition p0; 

   3.查询有多少个分区

SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'members';

四、数据迁移

  前面说了那么多概念,我说一下本次把大表数据迁移到分区表的过程。

  为什么会选择DTS呢?因为它可以不停机迁移数据,支持全量迁移和增量迁移,对原表影响不大。

 

  迁移过程如下:

  1.   首先在RDS的同一个实例里面新建了一张同结构的分区表
  2.   使用DTS创建迁移任务,迁移时候不要选择结构钱谦益,只选择全量+增量迁移
  3.   然后还需要编辑下目标库表名,也就是做下映射从A->B的迁移
  4.   停掉写入数据的任务,当任务队列为空时,等待几分钟暂停并结束迁移任务
  5.   最后修改表名,完成数据迁移和切换(我在测试环境修改分区表名要一些时间,但RDS修改表名是秒改)

 

参考官方文档:MySQL 5.6 分区

以上纯属个人观点,如有不对欢迎指正。

© 著作权归作者所有

剧与

剧与

粉丝 15
博文 47
码字总数 41334
作品 0
成都
后端工程师
私信 提问
加载中

评论(1)

空白的纸飞机
空白的纸飞机
Hello~关注了您分享的技术文章,很棒!我是腾讯云云+社区的小编,我们诚挚邀请您加入云+社区,与我们众多开发者一起交流分享技术内容。这个是我们云+社区【腾讯云自媒体分享计划】入驻流程和权益介绍的地址:https://cloud.tencent.com/developer/support-plan。了解更多详情请联系我~微信:18374981632,我们对您的加入充满期待。
HAWQ取代传统数仓实践(二)——搭建示例模型(MySQL、HAWQ)

一、业务场景 本系列实验将应用HAWQ数据库,为一个销售订单系统建立数据仓库。本篇说明示例的业务场景、数据仓库架构、实验环境、源和目标库的建立过程、测试数据和日期维度的生成。后面陆续...

wzy0623
2017/05/11
0
0
HAWQ取代传统数仓实践(四)——定期ETL(Sqoop、HAWQ)

一、变化数据捕获(CDC) 初始装载只在数据仓库开始使用前执行一次,然而,必须要周期性地执行装载源数据过程。与初始装载不同,定期装载一般都是增量的,并且需要捕获并且记录数据的变化历史...

wzy0623
2017/05/12
0
0
玩转大数据系列之一:数据采集与同步

数据的采集和同步,是先将数据从设备、或者本地数据源采集、同步到阿里云上,然后在阿里云上对数据进行分析和处理,最终完成您的业务要求。本文向您介绍阿里云各产品的数据采集和同步的操作实...

阿里云云栖社区
01/07
227
0
PostgreSQL 类微博FEED系统 - 设计与性能指标

标签 PostgreSQL , feed , 微博 , 推送 , 分区 , 分片 , UDF , 挖掘 , 文本挖掘 背景 类微博系统,最频繁用到的功能: 之前写过一篇《三体高可用PCC大赛 - facebook微博 like场景 - 数据库设...

德哥
2018/04/18
0
0
探索乐视MySQL数据库架构与运维实践

一、乐视数据库概述 1、数据库平台介绍 我们数据库部门的数据库种类较多:有MySQL、Oracle、MongoDB、Redis等。从另一角度,可分为传统的DB (MySQL、MongoDB..) 及云数据库RDS。其中,MySQL...

English0523
2017/09/02
0
0

没有更多内容

加载失败,请刷新页面

加载更多

3_数组

3_数组

行者终成事
29分钟前
3
0
经典系统设计面试题解析:如何设计TinyURL(二)

原文链接:https://www.educative.io/courses/grokking-the-system-design-interview/m2ygV4E81AR 编者注:本文以一道经典的系统设计面试题:《如何设计TinyURL》的参考答案和解析为例,帮助...

APEMESH
今天
7
0
使用logstash同步MySQL数据到ES

概述   在生成业务常有将MySQL数据同步到ES的需求,如果需要很高的定制化,往往需要开发同步程序用于处理数据。但没有特殊业务需求,官方提供的logstash就很有优势了。   在使用logstas...

zxiaofan666
今天
10
0
X-MSG-IM-分布式信令跟踪能力

经过一周多的鏖战, X-MSG-IM的分布式信令跟踪能力已基本具备, 特点是: 实时. 只有要RX/TX就会实时产生信令跟踪事件, 先入kafka, 再入influxdb待查. 同时提供实时sub/pub接口. 完备. 可以完整...

dev5
今天
7
0
OpenJDK之CyclicBarrier

OpenJDK8,本人看的是openJDK。以前就看过,只是经常忘记,所以记录下 图1 CyclicBarrier是Doug Lea在JDK1.5中引入的,作用就不详细描述了,主要有如下俩个方法使用: await()方法,如果当前线...

克虏伯
今天
8
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部