MySQL分区表的正确使用方法
作者:听风。 发布时间:2024-01-29 01:51:26
MySQL分区表概述
我们经常遇到一张表里面保存了上亿甚至过十亿的记录,这些表里面保存了大量的历史记录。 对于这些历史数据的清理是一个非常头疼事情,由于所有的数据都一个普通的表里。所以只能是启用一个或多个带where条件的delete语句去删除(一般where条件是时间)。 这对数据库的造成了很大压力。即使我们把这些删除了,但底层的数据文件并没有变小。面对这类问题,最有效的方法就是在使用分区表。最常见的分区方法就是按照时间进行分区。
分区一个最大的优点就是可以非常高效的进行历史数据的清理。
1. 确认MySQL服务器是否支持分区表
命令:
show plugins;
2. MySQL分区表的特点
在逻辑上为一个表,在物理上存储在多个文件中
HASH分区(HASH)
HASH分区的特点
根据MOD(分区键,分区数)的值把数据行存储到表的不同分区中
数据可以平均的分布在各个分区中
HASH分区的键值必须是一个INT类型的值,或是通过函数可以转为INT类型
如何建立HASH分区表
以INT类型字段 customer_id为分区键
CREATE TABLE `customer_login_log` (
`customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
`login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户登录时间',
`login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
`login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户登录日志表'
PARTITION BY HASH(customer_id) PARTITIONS 4;
以非INT类型字段 login_time 为分区键(需要先转换成INT类型)
CREATE TABLE `customer_login_log` (
`customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
`login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户登录时间',
`login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
`login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户登录日志表'
PARTITION BY HASH(UNIX_TIMESTAMP(login_time)) PARTITIONS 4;
customer_login_log 表如果不分区,在物理磁盘上文件为
customer_login_log.frm # 存储表原数据信息
customer_login_log.ibd # Innodb数据文件
如果按上面的建HASH分区表,则有五个文件
customer_login_log.frm
customer_login_log#P#p0.ibd
customer_login_log#P#p1.ibd
customer_login_log#P#p2.ibd
customer_login_log#P#p3.ibd
演示
使用起来和不分区是一样的,看起来只有一个数据库,其实有多个分区文件,比如我们要插入一条数据,不需要指定分区,MySQL会自动帮我们处理
查询
范围分区(RANGE)
RANGE分区特点
根据分区键值的范围把数据行存储到表的不同分区中
多个分区的范围要连续,但是不能重叠
默认情况下使用VALUES LESS THAN属性,即每个分区不包括指定的那个值
如何建立RANGE分区
如果没有定义p3分区,当插入的customer_id大于29999时会报错,定义了则超过的数据都存入p3中
RANGE分区的适用场景
分区键为日期或是时间类型 (可以使得各个分区表的数据比较均衡,如果按上面的例子中以整型id为分区键,假如活跃用户集中在10000-19999之间,则p1中的数据量就会比其他分区的数据量大很多,这就失去了分区的意义;而且按时间类型分区,如果要按时间顺序进行数据的归档,则只需要对某一个分区进行归档就可以了)
所有查询中都包括分区键(避免跨分区查询)
定期按分区范围清理历史数据
LIST分区
LIST分区的特点
按分区键取值的列表进行分区
同范围分区一样,各分区的列表值不能重复
每一行数据必须能找到对应的分区列表,否则数据插入失败
如何建立LIST分区
如果插入一条login_type为10的数据行,则会报错
3. 如何为登录日志表(customer_login_log)分区
业务场景
用户每次登录都会记录customer_login_log日志
用户登录日志保存一年,1年后可以删除或者归档
登录日志表的分区类型及分区键
使用RANGE分区
以login_time为分区键
分区后的用户登录日志表
按年份分区存储,所以用YEAR函数进行了转化
CREATE TABLE `customer_login_log` (
`customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
`login_time` DATETIME NOT NULL COMMENT '用户登录时间',
`login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
`login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(login_time))(
PARTITION p0 VALUES LESS THAN (2017),
PARTITION p1 VALUES LESS THAN (2018),
PARTITION p2 VALUES LESS THAN (2019)
)
插入并查询数据
查询指定表中的分区数据情况
SELECT table_name,partition_name,partition_description,table_rows FROM
information_schema.`PARTITIONS` WHERE table_name = 'customer_login_log';
再插入2条18年的日志,会存入p2表中
之前说过建立分区表时,最好建立一个MAXVALUE的分区,这里之所以没有建立,是为了数据维护的方便,如果我们建立了MAXVALUE分区,很容易忽视一个问题,当我们2019年有的数据插入时,会自动存入那个MAXVALUE分区中,之后在做数据维护时会不方便,所以没有建立MAXVALUE分区
而是通过计划任务的方式,在每年年底的时候增加这个分区,比如我们现在在2018年年底,我们需要在日志表中为2019年建立日志分区,否则2019年的日志都会插入失败
我们可以通过下面语句
增加分区
ALTER TABLE customer_login_log ADD PARTITION (PARTITION p3 VALUES LESS THAN(2020))
增加分区,并插入数据
删除分区
假如我们现在要删除2016年到2017年间一年的数据,因为我们已经做了分区,所以只需要通过一条语句,删除p0分区即可
ALTER TABLE customer_login_log DROP PARTITION p0;
可以发现p0分区已被删除,且2016年的日志全部被清除了
归档分区历史数据
我们可能有另一种需求对数据进行归档
Mysql版本>=5.7,归档分区历史数据非常方便,提供了一个交换分区的方法
分区数据归档迁移条件:
MySQL>=5.7
结构相同
归档到的数据表一定要是非分区表
非临时表;不能有外键约束
归档引擎要是:archive
建表并交换分区
CREATE TABLE `arch_customer_login_log` (
`customer_id` INT unsigned NOT NULL COMMENT '登录用户ID',
`login_time` DATETIME NOT NULL COMMENT '用户登录时间',
`login_ip` INT unsigned NOT NULL COMMENT '登录IP',
`login_type` TINYINT NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB ;
ALTER TABLE customer_login_log
exchange PARTITION p1 WITH TABLE arch_customer_login_log;
可以发现,原customer_login_log表中的2017年的数据(p1分区中的数据)已转移到了arch_customer_login_log表中,但是p1分区未删除,只是数据转移了,所以我们还需要执行DROP命令删除分区,以免有数据插入其中
将归档数据的存储引擎改为归档引擎
最后我们将归档数据的存储引擎改为归档引擎,命令为
ALTER TABLE customer_login_log ENGINE=ARCHIVE;
使用归档引擎的好处是:它比Innodb所占用的空间更少,但是归档引擎只能进行查询操作,不能进行写操作
4. 使用分区表的主要事项
结合业务场景选择分区键,避免跨分区查询
对分区表进行查询最好在WHERE从句中包含分区键
具有主键或唯一索引的表,主键或唯一索引必须是分区键的一部分(这也是为什么我们上面分区时去掉了主键登录日志id(login_id)的原因,不然就无法按照上面的按年份进行分区,所以分区表其实更适合在MyISAM引擎中)
关于MyISAM和Innodb的索引区别
1.关于自动增长
myisam引擎的自动增长列必须是索引,如果是组合索引,自动增长可以不是第一列,他可以根据前面几列进行排序后递增。
innodb引擎的自动增长咧必须是索引,如果是组合索引也必须是组合索引的第一列。
2.关于主键
myisam允许没有任何索引和主键的表存在,
myisam的索引都是保存行的地址。
innodb引擎如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见)
innodb的数据是主索引的一部分,附加索引保存的是主索引的值。
3.关于count()函数
myisam保存有表的总行数,如果select count(*) from table;会直接取出出该值
innodb没有保存表的总行数,如果使用select count(*) from table;就会遍历整个表,消耗相当大,但是在加了wehre 条件后,myisam和innodb处理的方式都一样。
4.全文索引
myisam支持 FULLTEXT类型的全文索引
innodb不支持FULLTEXT类型的全文索引,但是innodb可以使用sphinx插件支持全文索引,并且效果更好。(sphinx 是一个开源软件,提供多种语言的API接口,可以优化mysql的各种查询)
5.delete from table
使用这条命令时,innodb不会从新建立表,而是一条一条的删除数据,在innodb上如果要清空保存有大量数据的表,最 好不要使用这个命令。(推荐使用truncate table,不过需要用户有drop此表的权限)
6.索引保存位置
myisam的索引以表名+.MYI文件分别保存。
innodb的索引和数据一起保存在表空间里。
来源:https://www.cnblogs.com/huchong/p/10231719.html
猜你喜欢
- 一、Python简介1.python介绍Python由荷兰数学和计算机科学研究学会的Guido van Rossum 于1990 年代初设计
- 一、picklepickle模块用来实现python对象的序列化和反序列化。通常地pickle将python对象序列化为二进制流或文件。&n
- 方法一:插入断点,Debug运行在欲查看变量值的语句前,插入断点,Debug运行。之后,就在Debug面板下,可以查看各变量值,然后还可按F
- 本文实例为大家分享了python3实现人脸识别的具体代码,供大家参考,具体内容如下第一种:import cv2import numpy as
- 你知道(X)HTML中最多余的标签中是什么吗?在我看来就是这个<a>标签,不错,就是每个网站使用最多的超级链接标签<a&g
- 需要分件html源代码 此例中的被抓取的html源代码如下 <p align=left>2004年8月24日星期二;白天:晴有时
- 突然发现自己对Web前端技术掌握得很少很少,就是自己最感兴趣的XHTML+CSS部分知道也不算多。在XHTML 1.1规定的诸多元素中,我平
- 搞一个图形化界面还是挺酷的,是吧 安装库什么的应该不用多说了吧。。一般来说会让你把 designer.exe(编辑图形化界面的东西,跟vb差
- 什么是协程协程是python种一种实现多任务的方式,他是一种比线程更加小的单元,占用更小的执行单元(资源),为啥说他是一个执行单元,因为他自
- 前段时间做一个小项目碰到了一个导航制作的方式然后突然想到曾经很久以前看到的梯形状的不规则导航,就尝试做了一下。结果碰到了几个问题,后来在同事
- Python socket C/S结构的聊天室应用服务端:#!/usr/bin/env python#coding:utf8 import
- 学习PHP就不得不提MySQL,虽然有phpMyadmin这样的工具可以图形化操作数据库,但我还是想借学习PHP的机会使用下命令行方式操作数
- 1、给定的日期格式相同,则使用简单的比较运算符来比较日期。<?php//声明两个日期、初始化$date1 = "2021-1
- 在开发网站的过程中,404,500错误,是不可避免产生的。一旦产生了这种错误,很多可以通过web server 来处理。比如使用 apach
- 1. auth介绍Django 自带一个用户验证系统。它负责处理用户账号、组、权限和基于cookie的用户会话。认证系统由以下部分
- 结果然后直接放源码:import cv2 as cvsource = cv.imread("zhaopian.jpg")
- 一、介绍Python:python代码解释器,用于编译.py代码,python可以单独安装,本次环境配置目的用于解决计算机视觉处理,因此选用
- 一、实现创建文件夹和日志#!/usr/bin/env python# -*- coding:utf-8 -*-# Author: nulig
- 来自 * 我们的大脑通常最多能感知三维空间,超过三维就很难想象了。尽管是三维,理解起来也很费劲,所以大多数情况下都使用二维平面。不过,我们
- 考虑下述Python代码片段。对文件中的数据进行某些操作,然后将结果保存回文件中:with open(filename) as f:&nbs