文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

如何使用MySQL分区表

2024-04-02 19:55

关注

这篇文章给大家介绍如何使用MySQL分区表,内容非常详细,感兴趣的小伙伴们可以参考借鉴,希望对大家能有所帮助。

分区是一种表的设计模式,通俗地讲表分区是将一大表,根据条件分割成若干个小表。但是对于应用程序来讲,分区的表和没有分区的表是一样的。换句话来讲,分区对于应用是透明的,只是数据库对于数据的重新整理。

MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时候,优化器根据分区定义过滤那些没有我们需要的数据的分区,这样查询就可以无需扫描所有分区,只需要查找包含需要数据的分区即可。

分区的另一个目的是将数据按照一个较粗的粒度分别存放在不同的表中。这样做可以将相关的数据存放在一起,另外,当我们想要一次批量删除整个分区的数据也会变得很方便。

下面简单介绍下四种常见的分区类型:

上述四种分区类型中,RANGE分区即范围分区是最常用的。RANGE分区的特点是多个分区的范围要连续,但是不能重叠,默认情况下使用VALUES LESS THAN属性,即每个分区不包括指定的那个值。

2.分区操作示例

本节内容以RANGE分区为例,介绍下分区表相关的操作。

# 创建分区表
mysql> CREATE TABLE `tr` (
    ->   `id` INT, 
    ->   `name` VARCHAR(50), 
    ->   `purchased` DATE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> PARTITION BY RANGE( YEAR(purchased) ) (
    -> PARTITION p0 VALUES LESS THAN (1990),
    -> PARTITION p1 VALUES LESS THAN (1995),
    -> PARTITION p2 VALUES LESS THAN (2000),
    -> PARTITION p3 VALUES LESS THAN (2005),
    -> PARTITION p4 VALUES LESS THAN (2010),
    -> PARTITION p5 VALUES LESS THAN (2015)
    -> );
Query OK, 0 rows affected (0.28 sec)
# 插入数据
mysql> INSERT INTO `tr` VALUES
    ->     (1, 'desk organiser', '2003-10-15'),
    ->     (2, 'alarm clock', '1997-11-05'),
    ->     (3, 'chair', '2009-03-10'),
    ->     (4, 'bookcase', '1989-01-10'),
    ->     (5, 'exercise bike', '2014-05-09'),
    ->     (6, 'sofa', '1987-06-05'),
    ->     (7, 'espresso maker', '2011-11-22'),
    ->     (8, 'aquarium', '1992-08-04'),
    ->     (9, 'study desk', '2006-09-16'),
    ->     (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

创建后可以看到,每个分区都会对应1个ibd文件。上面创建语句还是很好理解的,在此分区表中,通过YEAR函数取出DATE日期中的年份并转化为整型,年份小于1990的存储在分区p0中,小于1995的存储在分区p1中,以此类推。请注意,每个分区的定义顺序是从最低到最高。为了防止插入的数据因找不到相应分区而报错,我们应该及时创建新的分区。下面继续展示关于分区维护的其他操作。

# 查看某个分区的数据
mysql> SELECT * FROM tr PARTITION (p2);
+------+-------------+------------+
| id   | name        | purchased  |
+------+-------------+------------+
|    2 | alarm clock | 1997-11-05 |
|   10 | lava lamp   | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)
# 增加分区
mysql> alter table tr add partition(
    -> PARTITION p6 VALUES LESS THAN (2020)
    -> );
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 拆分分区
mysql> alter table tr reorganize partition p5 into(
    ->   partition s0 values less than(2012),
    ->   partition s1 values less than(2015)
    -> );
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 合并分区
mysql> alter table tr reorganize partition s0,s1 into ( 
    ->     partition p5 values less than (2015) 
    -> );
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 清空某分区的数据
mysql> alter table tr truncate partition p0;
Query OK, 0 rows affected (0.11 sec)
# 删除分区
mysql> alter table tr drop partition p1;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 交换分区
# 先创建与分区表同样结构的交换表
mysql> CREATE TABLE `tr_archive` (
    ->   `id` INT, 
    ->   `name` VARCHAR(50), 
    ->   `purchased` DATE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.28 sec)
# 执行exchange交换分区 
mysql> alter table tr exchange PARTITION p2 with table tr_archive;
Query OK, 0 rows affected (0.13 sec)
3.分区注意事项及适用场景

其实分区表的使用有很多限制和需要注意的事项,参考官方文档,简要总结几点如下:

从上面的介绍中可以看出,分区表适用于一些日志记录表。这类表的特点是数据量大、并且有冷热数据区分,可以按照时间维度来进行数据归档。这类表是比较适合使用分区表的,因为分区表可以对单独的分区进行维护,对于数据归档更方便。

4.分区表为什么不常用

在我们项目开发中,分区表其实是很少用的,下面简单说明下几点原因:

如果想使用分区表的话,建议提早做好规划,在初始化的时候即创建分区表并制定维护计划,使用得当还是比较方便的,特别是有历史数据归档需求的表,使用分区表会使归档更方便。

关于如何使用MySQL分区表就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯