MySQL · 最佳实践 · 分区表基本类型-阿里云开发者社区

开发者社区> 技术小能手> 正文

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

简介:
+关注继续查看

MySQL分区表概述

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

分区类型

目前MySQL支持范围分区(RANGE),列表分区(LIST),哈希分区(HASH)以及KEY分区四种。下面我们逐一介绍每种分区:

RANGE分区

基于属于一个给定连续区间的列值,把多行分配给分区。最常见的是基于时间字段. 基于分区的列最好是整型,如果日期型的可以使用函数转换为整型。本例中使用to_days函数

CREATE TABLE my_range_datetime(
 id INT,
 hiredate DATETIME
) 
PARTITION BY RANGE (TO_DAYS(hiredate) ) (
 PARTITION p1 VALUES LESS THAN ( TO_DAYS('20171202') ),
 PARTITION p2 VALUES LESS THAN ( TO_DAYS('20171203') ),
 PARTITION p3 VALUES LESS THAN ( TO_DAYS('20171204') ),
 PARTITION p4 VALUES LESS THAN ( TO_DAYS('20171205') ),
 PARTITION p5 VALUES LESS THAN ( TO_DAYS('20171206') ),
 PARTITION p6 VALUES LESS THAN ( TO_DAYS('20171207') ),
 PARTITION p7 VALUES LESS THAN ( TO_DAYS('20171208') ),
 PARTITION p8 VALUES LESS THAN ( TO_DAYS('20171209') ),
 PARTITION p9 VALUES LESS THAN ( TO_DAYS('20171210') ),
 PARTITION p10 VALUES LESS THAN ( TO_DAYS('20171211') ),
 PARTITION p11 VALUES LESS THAN (MAXVALUE) 
); 

p11是一个默认分区,所有大于20171211的记录都会在这个分区。MAXVALUE是一个无穷大的值。p11是一个可选分区。如果在定义表的没有指定的这个分区,当我们插入大于20171211的数据的时候,会收到一个错误。

我们在执行查询的时候,必须带上分区字段。这样可以使用分区剪裁功能

mysql> insert into my_range_datetime select * from test; 
Query OK, 1000000 rows affected (8.15 sec)
Records: 1000000 Duplicates: 0 Warnings: 0

mysql> explain partitions select * from my_range_datetime where hiredate >= '20171207124503' and hiredate<='20171210111230'; 
+----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | my_range_datetime | p7,p8,p9,p10 | ALL | NULL | NULL | NULL | NULL | 400061 | Using where |
+----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.03 sec) 

注意执行计划中的partitions的内容,只查询了p7,p8,p9,p10三个分区,由此来看,使用to_days函数确实可以实现分区裁剪。

上面是基于datetime的,如果是timestamp类型,我们遇到上面问题呢?

事实上,MySQL提供了一种基于UNIX_TIMESTAMP函数的RANGE分区方案,而且,只能使用UNIX_TIMESTAMP函数,如果使用其它函数,譬如to_days,会报如下错误:“ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed”。

而且官方文档中也提到“Any other expressions involving TIMESTAMP values are not permitted. (See Bug #42849.)”。

下面来测试一下基于UNIX_TIMESTAMP函数的RANGE分区方案,看其能否实现分区裁剪。

针对TIMESTAMP的分区方案

创表语句如下:

CREATE TABLE my_range_timestamp (
 id INT,
 hiredate TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(hiredate) ) (
 PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-02 00:00:00') ),
 PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-03 00:00:00') ),
 PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-04 00:00:00') ),
 PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-05 00:00:00') ),
 PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-06 00:00:00') ),
 PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-07 00:00:00') ),
 PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-08 00:00:00') ),
 PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-09 00:00:00') ),
 PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-10 00:00:00') ),
 PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2017-12-11 00:00:00') )
); 

插入数据并查看上述查询的执行计划

mysql> insert into my_range_timestamp select * from test;
Query OK, 1000000 rows affected (13.25 sec)
Records: 1000000 Duplicates: 0 Warnings: 0

mysql> explain partitions select * from my_range_timestamp where hiredate >= '20171207124503' and hiredate<='20171210111230';
+----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | my_range_timestamp | p7,p8,p9,p10 | ALL | NULL | NULL | NULL | NULL | 400448 | Using where |
+----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec) 

同样也能实现分区裁剪。

在5.7版本之前,对于DATA和DATETIME类型的列,如果要实现分区裁剪,只能使用YEAR() 和TO_DAYS()函数,在5.7版本中,又新增了TO_SECONDS()函数。

LIST 分区

LIST分区

LIST分区和RANGE分区类似,区别在于LIST是枚举值列表的集合,RANGE是连续的区间值的集合。二者在语法方面非常的相似。同样建议LIST分区列是非null列,否则插入null值如果枚举列表里面不存在null值会插入失败,这点和其它的分区不一样,RANGE分区会将其作为最小分区值存储,HASH\KEY分为会将其转换成0存储,主要LIST分区只支持整形,非整形字段需要通过函数转换成整形.

create table t_list( 
  a int(11), 
  b int(11) 
  )(partition by list (b) 
  partition p0 values in (1,3,5,7,9), 
  partition p1 values in (2,4,6,8,0) 
  ); 

Hash 分区

我们在实际工作中经常遇到像会员表的这种表。并没有明显可以分区的特征字段。但表数据有非常庞大。为了把这类的数据进行分区打散mysql 提供了hash分区。基于给定的分区个数,将数据分配到不同的分区,HASH分区只能针对整数进行HASH,对于非整形的字段只能通过表达式将其转换成整数。表达式可以是mysql中任意有效的函数或者表达式,对于非整形的HASH往表插入数据的过程中会多一步表达式的计算操作,所以不建议使用复杂的表达式这样会影响性能。

Hash分区表的基本语句如下:

CREATE TABLE my_member (
 id INT NOT NULL,
 fname VARCHAR(30),
 lname VARCHAR(30),
 created DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT,
 store_id INT
)
PARTITION BY HASH(id)
PARTITIONS 4; 

注意:

  1. HASH分区可以不用指定PARTITIONS子句,如上文中的PARTITIONS 4,则默认分区数为1。
  2. 不允许只写PARTITIONS,而不指定分区数。
  3. 同RANGE分区和LIST分区一样,PARTITION BY HASH (expr)子句中的expr返回的必须是整数值。
  4. HASH分区的底层实现其实是基于MOD函数。譬如,对于下表

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4; 如果你要插入一个col3为“2017-09-15”的记录,则分区的选择是根据以下值决定的:

MOD(YEAR(‘2017-09-01’),4) = MOD(2017,4) = 1

LINEAR HASH分区

LINEAR HASH分区是HASH分区的一种特殊类型,与HASH分区是基于MOD函数不同的是,它基于的是另外一种算法。

格式如下:

CREATE TABLE my_members (
 id INT NOT NULL,
 fname VARCHAR(30),
 lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT,
 store_id INT
)
PARTITION BY LINEAR HASH( id )
PARTITIONS 4; 

说明: 它的优点是在数据量大的场景,譬如TB级,增加、删除、合并和拆分分区会更快,缺点是,相对于HASH分区,它数据分布不均匀的概率更大。

KEY分区

KEY分区其实跟HASH分区差不多,不同点如下:

  1. KEY分区允许多列,而HASH分区只允许一列。
  2. 如果在有主键或者唯一键的情况下,key中分区列可不指定,默认为主键或者唯一键,如果没有,则必须显性指定列。
  3. KEY分区对象必须为列,而不能是基于列的表达式。
  4. KEY分区和HASH分区的算法不一样,PARTITION BY HASH (expr),MOD取值的对象是expr返回的值,而PARTITION BY KEY (column_list),基于的是列的MD5值。

格式如下:

CREATE TABLE k1 (
 id INT NOT NULL PRIMARY KEY, 
 name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2; 

在没有主键或者唯一键的情况下,格式如下:

CREATE TABLE tm1 (
 s1 CHAR(32)
)
PARTITION BY KEY(s1)
PARTITIONS 10; 

总结:

  1. MySQL分区中如果存在主键或唯一键,则分区列必须包含在其中。
  2. 对于原生的RANGE分区,LIST分区,HASH分区,分区对象返回的只能是整数值。
  3. 分区字段不能为NULL,要不然怎么确定分区范围呢,所以尽量NOT NULL

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
分区表 自动添加
背景: 数据较多,切考虑到后期维护,希望能够自动添加分区 过程  间隔分区 oracle 11g 新的特性 CREATE T...
640 0
【Mysql】Mysql数据表区分大小写问题解决方案
原因 由于有的环境下,对数据表的大小写是明确区分的,但是我在导出的时候的数据表全部都是小写的,这个时候需要对环境进行配置 解决 vim /etc/my.cnf 在[mysqld]下加入一行:lower_case_table_names=1 service mysq...
669 0
MSSQL · 最佳实践 · 使用混合密钥实现列加密
摘要 在SQL Server安全系列专题的上两期月报分享中,我们分别分享了:如何使用对称密钥实现SQL Server列加密技术和使用非对称密钥加密方式实现SQL Server列加密。本期月报我们分享使用混合密钥加密方式实现SQL Server列加密技术,最大限度减少性能损失,最大程度保护用户数据安全。
1549 0
表格存储 SQL 数据类型详解
本文主要介绍 Tablestore SQL中的数据类型与 MySQL 数据类型之间的映射关系。 ​ ## 背景介绍 ### Tablestore 数据类型 Tablestore 中的数据类型支持如下表所示,其中主键列支持的数据类型包括String、Integer和Binary,属性列支持的数据类型包括String、Integer、Double、Boolean和Binary。 - 主键列支持的数
49 0
分区表 区间分区 散列分区 列表分区
oracle 11g 增加了新的分区类型,总结一下目前之前的分区表 区间分区 散列分区 列表分区 区间分区: create table gh_range_example( id varchar2(100), range_date date not n...
613 0
PostgreSQL 最佳实践 - 在线逻辑备份与恢复介绍
背景 PostgreSQL 逻辑备份, 指在线备份数据库数据, DDL以SQL语句形式输出, 数据则可以以SQL语句或者固定分隔符(row格式)的形式输出. 备份时不影响其他用户对备份对象的DML操作. 本文主要介绍一下PostgreSQL提供的逻辑备份工具pg_dump, p
3656 0
MSSQL - 最佳实践 - 使用SSL加密连接
--- title: MSSQL - 最佳实践 - 使用SSL加密连接 author: 风移 --- # 摘要 在SQL Server安全系列专题月报分享中,往期我们已经陆续分享了:[如何使用对称密钥实现SQL Server列加密技术](http://mysql.taobao.org/monthly/2018/08/03/)、[使用非对称密钥实现SQL Server列加密](http:/
2526 0
+关注
技术小能手
云栖运营小编~
7208
文章
9
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载