详解MySQL分区表

前言:

分区是一种表的设计模式,通俗地讲表分区是将一大表,根据条件分割成若干个小表。但是对于应用程序来讲,分区的表和没有分区的表是一样的。换句话来讲,分区对于应用是透明的,只是数据库对于数据的重新整理。本篇文章给大家带来的内容是关于MySQL中分区表的介绍及使用场景,有需要的朋友可以参考一下,希望对你有所帮助。

1.分区的目的及分区类型

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)
  -> );
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.分区注意事项及适用场景

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

  • 分区字段必须是整数类型或解析为整数的表达式。
  • 分区字段建议设置为NOT NULL,若某行数据分区字段为null,在RANGE分区中,该行数据会划分到最小的分区里。
  • MySQL分区中如果存在主键或唯一键,则分区列必须包含在其中。
  • Innodb分区表不支持外键。
  • 更改sql_mode模式可能影响分区表的表现。
  • 分区表不影响自增列。

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

4.分区表为什么不常用

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

  • 分区字段的选择有限制。
  • 若查询不走分区键,则可能会扫描所有分区,效率不会提升。
  • 若数据分布不均,分区大小差别较大,可能性能提升也有限。
  • 普通表改造成分区表比较繁琐。
  • 需要持续对分区进行维护,比如到了6月份前就要新增6月份的分区。
  • 增加学习成本,存在未知风险。

总结:

本文较为详细的介绍了MySQL分区相关内容,如果想使用分区表的话,建议提早做好规划,在初始化的时候即创建分区表并制定维护计划,使用得当还是比较方便的,特别是有历史数据归档需求的表,使用分区表会使归档更方便。当然,关于分区表的内容还有很多,有兴趣的同学可以找找官方文档,官方文档中有大量示例。

以上就是详解MySQL分区表的详细内容,更多关于MySQL分区表的资料请关注我们其它相关文章!

(0)

相关推荐

  • MySQL最佳实践之分区表基本类型

    MySQL分区表概述 随着MySQL越来越流行,Mysql里面的保存的数据也越来越大.在日常的工作中,我们经常遇到一张表里面保存了上亿甚至过十亿的记录.这些表里面保存了大量的历史记录. 对于这些历史数据的清理是一个非常头疼事情,由于所有的数据都一个普通的表里.所以只能是启用一个或多个带where条件的delete语句去删除(一般where条件是时间). 这对数据库的造成了很大压力.即使我们把这些删除了,但底层的数据文件并没有变小.面对这类问题,最有效的方法就是在使用分区表.最常见的分区方法就是按

  • 通过实例学习MySQL分区表原理及常用操作

    1.分区表含义 分区表定义指根据可以设置为任意大小的规则,跨文件系统分配单个表的多个部分.实际上,表的不同部分在不同的位置被存储为单独的表.用户所选择的.实现数据分割的规则被称为分区函数,这在MySQL中它可以是模数,或者是简单的匹配一个连续的数值区间或数值列表,或者是一个内部HASH函数,或一个线性HASH函数. 分表与分区的区别在于:分区从逻辑上来讲只有一张表,而分表则是将一张表分解成多张表   2.分区表优点 1)分区表更容易维护.对于那些已经失去保存意义的数据,通常可以通过删除与那些数据

  • MySQL分区表的基本入门教程

    前言 在最近的项目中,我们需要保存大量的数据,而且这些数据是有有效期的,为了提供查询效率以及快速删除过期数据,我们选择了MySQL的分区机制.把数据按照时间进行分区. 分区类型 Range分区:最为常用,基于属于一个给定连续区间的列值,把多行分配给分区.最常见的是基于时间字段. 基于分区的列最好是整型,如果日期型的可以使用函数转换为整型. List分区:LIST分区和RANGE分区类似,区别在于LIST是枚举值列表的集合,RANGE是连续的区间值的集合. Hash分区:基于给定的分区个数,将数据

  • MySQL优化之分区表

    当数据库数据量涨到一定数量时,性能就成为我们不能不关注的问题,如何优化呢? 常用的方式不外乎那么几种: 1.分表,即把一个很大的表达数据分到几个表中,这样每个表数据都不多. 优点:提高并发量,减小锁的粒度 缺点:代码维护成本高,相关sql都需要改动 2.分区,所有的数据还在一个表中,但物理存储数据根据一定的规则存放在不同的文件中,文件也可以放到另外磁盘上 优点:代码维护量小,基本不用改动,提高IO吞吐量 缺点:表的并发程度没有增加 3.拆分业务,这个本质还是分表. 优点:长期支持更好 缺点:代码

  • MySQL分区表的正确使用方法

    MySQL分区表概述 我们经常遇到一张表里面保存了上亿甚至过十亿的记录,这些表里面保存了大量的历史记录. 对于这些历史数据的清理是一个非常头疼事情,由于所有的数据都一个普通的表里.所以只能是启用一个或多个带where条件的delete语句去删除(一般where条件是时间). 这对数据库的造成了很大压力.即使我们把这些删除了,但底层的数据文件并没有变小.面对这类问题,最有效的方法就是在使用分区表.最常见的分区方法就是按照时间进行分区. 分区一个最大的优点就是可以非常高效的进行历史数据的清理. 1.

  • Mysql分区表的管理与维护

    改变一个表的分区方案只需使用alter table 加 partition_options 子句就可以了.和创建分区表时的create table语句很像. 创建表 CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THA

  • MySQL分区表的最佳实践指南

    前言: 分区是一种表的设计模式,通俗地讲表分区是将一大表,根据条件分割成若干个小表.但是对于应用程序来讲,分区的表和没有分区的表是一样的.换句话来讲,分区对于应用是透明的,只是数据库对于数据的重新整理.本篇文章给大家带来的内容是关于MySQL中分区表的介绍及使用场景,有需要的朋友可以参考一下,希望对你有所帮助. 1.分区的目的及分区类型 MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据.在执行查询的时候,优化器根据分区定义过滤那些没有我们需要的数据的分区,这

  • Rails中使用MySQL分区表一个提升性能的方法

    MySQL 的分区表是一种简单有效的处理极大数据表的特性,通过它可以使应用程序几乎很少改动就能达成对极大数据表的高效处理,但由于 Rails ActiveRecord 设计上一些惯例,可能导致一些数据处理不能利用分区表特性,反而变得很慢,在使用分区表过程中一定要多加注意. 下面以一个例子来说明.在 light 系统中,有一张数据表是 diet_items, 主要字段是 id, schedule_id, meal_order food_id, weight, calory 等等,它的每一条记录表示

  • mysql使用教程之分区表的使用方法(删除分区表)

    MySQL使用分区表的好处: 1,可以把一些归类的数据放在一个分区中,可以减少服务器检查数据的数量加快查询.2,方便维护,通过删除分区来删除老的数据.3,分区数据可以被分布到不同的物理位置,可以做分布式有效利用多个硬盘驱动器. MySQL可以建立四种分区类型的分区: RANGE 分区:基于属于一个给定连续区间的列值,把多行分配给分区. LIST 分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择.  www.jb51.net HASH分区:基于用户

  • MySQL分区表的局限和限制详解

    禁止构建 分区表达式不支持以下几种构建: 存储过程,存储函数,UDFS或者插件 声明变量或者用户变量 可以参考分区不支持的SQL函数 算术和逻辑运算符 分区表达式支持+,-,*算术运算,但是不支持DIV和/运算(还存在,可以查看Bug #30188, Bug #33182).但是,结果必须是整形或者NULL(线性分区键除外,想了解更多信息,可以查看分区类型). 分区表达式不支持位运算:|,&,^,<<,>>,~ . HANDLER语句 在MySQL 5.7.1之前的分区表不

随机推荐