如何运用MySQL分区表
发布时间：2022-01-17 13:10:44 所属栏目：MySql教程 来源：互联网
导读：这篇文章给大家介绍如何使用MySQL分区表，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。 分区是一种表的设计模式，通俗地讲表分区是将一大表，根据条件分割成若干个小表。但是对于应用程序来讲，分区的表和没有分区的表是一样的。
|
这篇文章给大家介绍如何使用MySQL分区表，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。 分区是一种表的设计模式，通俗地讲表分区是将一大表，根据条件分割成若干个小表。但是对于应用程序来讲，分区的表和没有分区的表是一样的。换句话来讲，分区对于应用是透明的，只是数据库对于数据的重新整理。 MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时候，优化器根据分区定义过滤那些没有我们需要的数据的分区，这样查询就可以无需扫描所有分区，只需要查找包含需要数据的分区即可。 分区的另一个目的是将数据按照一个较粗的粒度分别存放在不同的表中。这样做可以将相关的数据存放在一起，另外，当我们想要一次批量删除整个分区的数据也会变得很方便。 下面简单介绍下四种常见的分区类型： RANGE分区：最为常用，基于属于一个给定连续区间的列值，把多行分配给分区。最常见的是基于时间字段。 LIST分区：LIST分区和RANGE分区类似，区别在于LIST是枚举值列表的集合，RANGE是连续的区间值的集合。 HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。 KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。 上述四种分区类型中，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) -> ); # 查看某个分区的数据 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.分区注意事项及适用场景 其实分区表的使用有很多限制和需要注意的事项，参考官方文档，简要总结几点如下： 分区字段必须是整数类型或解析为整数的表达式。 分区字段建议设置为NOT NULL，若某行数据分区字段为null，在RANGE分区中，该行数据会划分到最小的分区里。 MySQL分区中如果存在主键或唯一键，则分区列必须包含在其中。 Innodb分区表不支持外键。 更改sql_mode模式可能影响分区表的表现。 分区表不影响自增列。 从上面的介绍中可以看出，分区表适用于一些日志记录表。这类表的特点是数据量大、并且有冷热数据区分，可以按照时间维度来进行数据归档。这类表是比较适合使用分区表的，因为分区表可以对单独的分区进行维护，对于数据归档更方便。 （编辑：宿迁站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐