MySQL分区表的基本入门教程

前言

在最近的项目中，我们需要保存大量的数据，而且这些数据是有有效期的，为了提供查询效率以及快速删除过期数据，我们选择了MySQL的分区机制。把数据按照时间进行分区。

分区类型

  • Range分区：最为常用，基于属于一个给定连续区间的列值，把多行分配给分区。最常见的是基于时间字段. 基于分区的列最好是整型，如果日期型的可以使用函数转换为整型。
  • List分区：LIST分区和RANGE分区类似，区别在于LIST是枚举值列表的集合，RANGE是连续的区间值的集合。
  • Hash分区：基于给定的分区个数，将数据分配到不同的分区，HASH分区只能针对整数进行HASH，对于非整形的字段只能通过表达式将其转换成整数。
  • Key分区：KEY分区其实跟HASH分区差不多，不同点如下：
    • KEY分区允许多列，而HASH分区只允许一列。
    • 如果在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。
    • KEY分区对象必须为列，而不能是基于列的表达式。
    • KEY分区和HASH分区的算法不一样，PARTITION BY HASH (expr)，MOD取值的对象是expr返回的值，而PARTITION BY KEY (column_list)，基于的是列的MD5值。

分区命令

创建分区

CREATE TABLE `access_log` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `access_time` datetime NOT NULL,
 PRIMARY KEY (`id`,`access_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(access_time))
(PARTITION p1 VALUES LESS THAN (to_days(20190101)) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (to_days(20190102)) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (to_days(20190103)) ENGINE = InnoDB) */;

创建后可以看到，每个分区都会对应1个ibd文件


分区表

新增分区

alter table access_log add partition(
  partition p4 values less than (to_days('20190105'))
);

删除分区

alter table access_log drop partition p1;

拆分分区

alter table access_log reorganize partition p4 into(
  -> partition s0 values less than(to_days('20190104')),
  -> partition s1 values less than(to_days('20190105'))
  -> );

合并分区

alter table access_log reorganize partition s0,s1 into (
  partition p4 values less than (to_days('20190105'))
);

注意事项

  • MySQL分区中如果存在主键或唯一键，则分区列必须包含在其中（否则判断主键或唯一时，需要扫描所有分区）
  • 分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量NOT NULL
  • 最大分区数目不能超过1024
  • 不支持外键
  • 只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列
  • 分区表不影响自增列

常见问题

  • A PRIMARY KEY must include all columns in the table's partitioning function：这样的话判断主键是否唯一就可以在单个分区内部完成，否则就需要跨所有的分区
  • MAXVALUE can only be used in last partition definition：RANGE表分区后不能带MAXVALUE分区，否则无法增加分区。或者就只能重新分区了
alter table access_log partition by range(to_days(access_time))(
partition p1 values less than (to_days('20191202')),
partition p2 values less than (to_days('20191203')),
  partition po values less than (maxvalue)
)
  • Table has no partition for value 737425：因为分区的范围没有包含所有可能的记录的值

参考

总结

到此这篇关于MySQL分区表基本入门教程的文章就介绍到这了,更多相关MySQL分区表内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

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

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

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

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

  • MySQL优化之分区表

    MySQL优化之分区表

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

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

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

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

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

  • 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的分区机制.把数据按照时间进行分区. 分区类型 Range分区:最为常用,基于属于一个给定连续区间的列值,把多行分配给分区.最常见的是基于时间字段. 基于分区的列最好是整型,如果日期型的可以使用函数转换为整型. List分区:LIST分区和RANGE分区类似,区别在于LIST是枚举值列表的集合,RANGE是连续的区间值的集合. Hash分区:基于给定的分区个数,将数据

  • ASP.NET与MySQL数据库简明图示入门教程

    ASP.NET与MySQL数据库简明图示入门教程

    作者: CRYSTAL编译 在ASP时代,如果我们要建立一个数据库驱动的web站点,那么你可以选择环很多钱的微软SQL SERVER数据库或者选择要花很多时间来寻找达到性能和稳定性统一的ACCESS数据库,但在.NET时代你有另一种选择,那就是:MySQL数据库 什么是MySQL数据库? MySQL数据库是一种开放源代码的数据库,通过获得授权来保持源代码的官方支持,同时可以自由修改源代码,目前许多公司和组织都采用了这种数据库.对此详细信息您可以访问MySQL的官方站点. 第一步 下载和安装 与大

  • MySQL入门教程（七）之视图

    MySQL入门教程（七）之视图

    相关阅读: MySQL入门教程(五)之表的创建.修改和删除 视图是从一个或多个表中导出来的虚拟表.视图就像一个窗口,通过这个窗口可以看到系统专门提供的数据. 1.视图简介 1.1 视图的含义 视图是从一个或多个表中导出来的虚拟表,还可以从已经存在的视图的基础上定义. 数据库中只存放视图的定义,而不存放视图中的数据,这些数据仍存放在原来的表中.一旦表中的数据发生变化,显示在视图中的数据也会相应变化. MySQL的视图并不支持输入参数的功能,即交互性上较差,但对于变化不是很大的操作,使用视图可以很大

  • 21分钟 MySQL 入门教程

    21分钟 MySQL 入门教程

    21分钟 MySQL 入门教程 目录 一.MySQL的相关概念介绍 二.Windows下MySQL的配置 配置步骤 MySQL服务的启动.停止与卸载 三.MySQL脚本的基本组成 四.MySQL中的数据类型 五.使用MySQL数据库 登录到MySQL 创建一个数据库 选择所要操作的数据库 创建数据库表 六.操作MySQL数据库 向表中插入数据 查询表中的数据 更新表中的数据 删除表中的数据 七.创建后的修改 添加列 修改列 删除列 重命名表 删除整张表 删除整个数据库 八.附录 修改 root

  • MySQL基础入门教程之事务

    MySQL基础入门教程之事务

    目录 引言 1.事务操作 1.1 未控制事务 1.2 控制事务一 1.3 控制事务二 2.事务的四大特性 3.并发事务问题 4.事务隔离级别 总结 引言 事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败. 就比如: 张三给李四转账1000块钱,张三银行账户的钱减少1000,而李四银行账户的钱要增加1000. 这一组操作就必须在一个事务的范围内,要么都成功,要么都失败. 正常情况: 转账这个操作, 需

  • 微信公众平台开发入门教程(图文详解)

    微信公众平台开发入门教程(图文详解)

    在这篇入门教程中,我们假定你已经有了PHP语言程序.MySQL数据库.计算机网络通讯及XML语言基础.如果你还没有,那么请先学习相关知识. 我们将使用微信公众账号方倍工作室(账号:pondbaystudio,二维码在最底部)作为讲解的例子. 这篇入门教程将引导你完成如下任务: 创建百度云平台应用启用微信公众平台开发模式获取订阅.文字.图片.语音.视频消息回复文本.图文及音乐消息程序开发 创建百度云应用 申请账号 登录http://developer.baidu.com/bae ,使用邮箱或者手机

  • mybatis原理概述入门教程

    本文我将要介绍一下mybatis的框架原理,以及mybatis的入门程序,实现用户的增删改查,她有什么优缺点以及mybatis和hibernate之间存在着怎么样的关系,希望对小伙伴们有帮助,不足之处,还请多多指教. 什么是mybatis? MyBatis 本是apache的一个开源项目iBatis, 2010年这个项目由apache software foundation 迁移到了google code,并且改名为MyBatis .2013年11月迁移到Github. MyBatis 是支持定

  • OpenStack云计算快速入门教程(1)之OpenStack及其构成简介

    OpenStack云计算快速入门教程(1)之OpenStack及其构成简介

    该教程基于Ubuntu12.04版,它将帮助读者建立起一份OpenStack最小化安装.我是五岳之巅,翻译中多采用意译法,所以个别词与原版有出入,请大家谅解.我不是英语专业,我觉着搞技术最重要的就是理解,而不是四级和考研中那烦人的英译汉,所以我的目标是忠于原意.通俗表达,Over.英文原文在这里(http://docs.openstack.org/es@***/openstack-compute/starter/content/ ,请将ex@***中的@去掉,CU屏蔽的F词),下面步入正题: 第

  • PHP入门教程之日期与时间操作技巧总结(格式化,验证,获取,转换,计算等)

    本文实例讲述了PHP日期与时间操作技巧.分享给大家供大家参考,具体如下: Demo1.php <?php //验证时间 //checkdate() 1.月份 2.日 3.年 //checkdate() 判断这个日期是否是合法的日期 //不合法的日期,试一试 if(checkdate(7,16,2010)){ echo '这个日期是合法有效的'; }else{ echo '这个日期是非法的.'; } ?> Demo2.php <?php //date -- 格式化一个本地时间/日期 //d

  • PHP入门教程之图像处理技巧分析

    本文实例讲述了PHP图像处理.分享给大家供大家参考,具体如下: Demo1.php <?php //一般生成的图像可以是 png,jpg,gif,bmp //jpeg,wbmp //第一步,设置文件MIME类型,输出类型 text/html 类型是网页类型,默认可以不写 //将输出类型改成图像流 header('Content-Type:image/png;'); //第二步,创建一个图形区域,图像背景 //有两种创建方式,资源类型,一般要加上 @ 符号,防止出错 //imagecreatetr

随机推荐