AI 助理
备案控制台
开发者社区 数据库 关系型数据库 文章 正文

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

2017-11-21 11323
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB 分布式版，标准版 2核8GB
推荐场景：
使用PolarDB-X与Flink搭建实时数据大屏
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介： MySQL分区表概述随着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
文章标签：
云数据库 RDS MySQL 版
关系型数据库
MySQL
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
db匠
目录
相关文章
爱心发电丶
|
7月前
|
前端开发 JavaScript 程序员
WordPress免费主题：Document，让阅读变得更加方便
“Document”的主题以方便记、方便查、简约为核心设计理念，适合用作文档类博客。功能包括文章目录导航、黑夜/白天模式切换、代码高亮与一键复制、自定义小部件等。支持SEO优化及多种前端交互效果。提供Github和Gitee开源地址，用户可下载后上传至WordPress主题目录并启用。主题持续更新中
爱心发电丶
402 58
云皓@
|
7月前
|
存储 SQL 监控
ClickHouse 应用剖析：设计理念、机制与实践
ClickHouse 是一款高性能的列式数据库管理系统，主要用于实时的大数据分析场景。它由俄罗斯 Yandex 公司开源于 2016 年，在网页日志分析、物联网监控、广告计费等领域有广泛应用。ClickHouse 通过列式存储、向量化执行和分布式架构，实现对海量数据的快速查询分析。本文将介绍 ClickHouse 的设计理念，以及在实际使用中如何处理数据删除更新、冷热数据分离等问题，并提供常见配置的调优建议和异常问题的处理方法。
云皓@
899 14
ClickHouse 应用剖析：设计理念、机制与实践
草帽lufei
|
JSON 前端开发 JavaScript
前端上传文件前校验文件数据
该文介绍了如何在Vue项目中实现批量导入Excel数据的校验。使用Element UI的`el-upload`组件上传文件，通过FileReader读取内容，结合XLSX库解析Excel为JSON。解析过程包括将二进制数据转换为workbook对象，提取worksheet并转化为JSON。之后，遍历JSON数据进行字段校验，若发现空值则记录错误。提供的Demo展示了选择Excel文件后控制台显示校验结果。技术栈包括vue 2.6.14、element ui 2.15.14和xlsx 0.17.0。建议将此类功能封装为通用组件以复用。
草帽lufei
495 2
前端上传文件前校验文件数据
vohelon
|
人工智能 自然语言处理 程序员
用通义灵码@workspace和@terminal做项目代码熟悉和新需求实现的体验
来和大家分享一下，我用通义灵码@workspace和@terminal做项目代码熟悉和新需求实现的体验。
vohelon
633 2
派大鑫的工作室
|
安全 NoSQL 关系型数据库
内网扫描器Fscan
内网扫描器Fscan
派大鑫的工作室
486 0
奔跑的数据
|
12月前
|
数据采集 JSON JavaScript
如何通过PHP爬虫模拟表单提交，抓取隐藏数据
本文介绍了如何使用PHP模拟表单提交并结合代理IP技术抓取京东商品的实时名称和价格，特别是在电商大促期间的数据采集需求。通过cURL发送POST请求，设置User-Agent和Cookie，使用代理IP绕过限制，解析返回数据，展示了完整代码示例。
奔跑的数据
264 3
如何通过PHP爬虫模拟表单提交，抓取隐藏数据
1436047922066202
|
存储 SQL 数据挖掘
虚拟化数据恢复—VMware虚拟机vmdk文件被误删除的数据恢复案例
虚拟化数据恢复环境： 某品牌服务器（部署VMware EXSI虚拟机）+同品牌存储（存放虚拟机文件）。 虚拟化故障： 意外断电导致服务器上某台虚拟机无法正常启动。查看虚拟机配置文件发现这台故障虚拟机除了磁盘文件以外其他配置文件全部丢失，xxx-flat.vmdk磁盘文件和xxx-000001-delta.vmdk快照文件还在。管理员联系VMware工程师寻求帮助。VMware工程师尝试新建一个虚拟机来解决故障，但发现ESXi存储空间不足。于是将故障虚拟机下的xxx-flat.vmdk磁盘文件删除，然后重建一个虚拟机并且分配固定大小的虚拟磁盘。
1436047922066202
579 9
游客qsxez56gggwqy
|
Web App开发 JSON 网络协议
网络扫描
网络扫描
游客qsxez56gggwqy
236 0
蓝易云
|
存储 算法 关系型数据库
探索MySQL递归查询，优雅的给树结构分页!
总结起来，对于MySQL中的树结构数据，递归查询结合预排序遍历树算法可以实现优雅的分页，但需要注意性能优化和数据更新的问题。这项技术提供了一种高效处理层级数据的工具，使得开发者可以在复杂的数据结构下实现直观和可靠的数据查询。
蓝易云
812 1
土木林森
|
缓存 负载均衡 算法
"揭秘！阿里云视觉智能开放平台人脸1vn搜索慢？轻松几招，QPS飙升，让你的应用快如闪电，用户体验秒变VIP级享受！"
【8月更文挑战第14天】在数字浪潮中，人脸识别技术广泛应用于安全监控到个性化服务等领域。阿里云视觉智能开放平台凭借其强大算法和服务模式成为行业翘楚。面对人脸1:Vn搜索响应慢的问题，不仅可通过增加QPS优化，还需从参数调整、缓存机制、并行处理及算法硬件升级等方面综合施策，以实现搜索速度与准确性的双重提升。
土木林森
1278 2