如何使用分区处理MySQL的亿级数据优化
作者:godzla 发布时间:2024-01-21 04:49:01
标签:mysql,亿级,数据
mysql在查询上千万级数据的时候,通过索引可以解决大部分查询优化问题。但是在处理上亿数据的时候,索引就不那么友好了。
数据表(日志)是这样的:
表大小:1T,约24亿行;
表分区:按时间分区,每个月为一个分区,一个分区约2-3亿行数据(40-70G左右)。
由于数据不需要全量处理,经过与需求方讨论后,我们按时间段抽样一部分数据,比如抽样一个月的数据,约3.5亿行。
数据处理的思路:
1)建表引擎选择Innodb。由于数据是按月分区的,我们将该月分区的数据单独copy出来,源表为myisam引擎,因我们可能需要过滤部分数据,涉及到筛选的字段又没有索引,使用myisam引擎加索引的速度会比较慢;
2)按日分区。将copy出来的表加好索引后(约2-4个小时),过滤掉无用的数据,同时再次新生成一张表,抽取json中需要的字段,并对该表按日分区。
CREATE TABLE `tb_name` (
`id_`,
...,
KEY `idx_1` (`create_user_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='应用日志'
PARTITION BY RANGE(to_days(log_time_)) (
PARTITION p1231 VALUES LESS THAN (737425),
PARTITION p0101 VALUES LESS THAN (737426),
PARTITION p0102 VALUES LESS THAN (737427),
PARTITION p0103 VALUES LESS THAN (737428),
PARTITION p0104 VALUES LESS THAN (737429),
......
);
3)对上面生成的表按每日进行聚合或者其他操作,并将结果存储到临时表中,尽量使用存储过程加工数据,由于加工相对复杂而且耗时较多(跑一次存储过程需要大概1-2小时),因此循环调用存储过程时应记录操作时间和执行过程中的参数等;
delimiter $$
create procedure proc_name(param varchar(50))
begin
declare start_date date;
declare end_date date;
set start_date = '2018-12-31';
set end_date = '2019-02-01';
start transaction;
truncate tmp_talbe;
commit;
while start_date < end_date do
set @partition_name = date_format(start_date, '%m%d');
set @start_time = now(); -- 记录当前分区操作起始时间
start transaction;
set @sqlstr = concat(
"insert into tmp_talbe",
"select field_names ",
"from tb_name partition(p", @partition_name,") t ",
"where conditions;"
);
-- select @sqlstr;
prepare stmt from @sqlstr;
execute stmt;
deallocate prepare stmt;
commit;
-- 插入日志
set @finish_time = now(); -- 操作结束时间
insert into oprerate_log values(param, @partition_name, @start_time, @finish_time, timestampdiff(second, @start_time, @finish_time));
set start_date = date_add(start_date, interval 1 day);
end while;
end
$$
delimiter ;
4)对上述生成的结果进行整理加工。
总的来说,处理过程相对繁琐,而且产生了很多中间表,对关键步骤还需要记录操作流程的元数据,这对SQL处理的要求会比较高,因此不建议使用MySQL处理这种任务(除非迫不得已),如果能将能处理过程放在大数据平台上处理,速度会更快,而且元数据管理会相对专业。
来源:https://blog.csdn.net/godzla/article/details/88310811
0
投稿
猜你喜欢
- 要使用多个定界符拆分字符串:使用 re.split() 方法,例如 re.split(r',|-', my_str)。re.
- python字典怎么排序?定义一个字典类型mydict = {2: '小路', 3: '黎明', 1:
- tornado中的协程是如何工作的协程定义Coroutines are computer program components that g
- TFTP文件传输功能:1、获取文件列表2、上传文件3、下载文件4、退出第一部分,TftpServer部分。①导入相关模块from socke
- 1、简介AI 聊天机器人使用自然语言处理 (NLP) 来帮助用户通过文本、图形或语音与 Web 服务或应用进行交互。聊天机器人可以理解自然人
- 生命游戏的算法就不多解释了,百度一下介绍随处可见。因为网上大多数版本都是基于pygame,matlab等外部库实现的,二维数组大多是用num
- 1.导入模块import cv2 as cvimport numpy as np 2.OpenCV绘图大致步骤OpenCV 图形绘制步骤(1
- 启发式搜索在人工智能中起着关键作用。在本章中,您将详细了解它。AI中的启发式搜索的概念启发式是一个经验法则,它引导我们找到可能的解决方案。人
- 和很多语言一样,Python中也分为简单赋值、浅拷贝、深拷贝这几种“拷贝”方式。在学习过程中,一开始对浅拷贝理解很模糊。不过经过一系列的实验
- tkinter 是 Python 的标准 GUI 库。Python 使用 tkinter 可以快速的创建 GUI 应用程序。由于 tkint
- 如果你在文件夹里有很多视频,并且文件夹里还有文件夹,文件夹里的文件夹也有视频,怎么能逐个读取并且保存。。所以我写了个代码用了os,walk,
- 队、栈和链表一样,在数据结构中非常基础一种数据结构,同样他们也有各种各样、五花八门的变形和实现方式。但不管他们形式上怎么变,队和栈都有其不变
- PHP 跳转,即重定向浏览器到指定的 URL,是一个很常见的功能。这种功能也有一些细节性的要求,比如等待多少秒以后跳转,用不用JavaScr
- Python数据库接口支持非常多的数据库,你可以选择适合你项目的数据库:GadFlymSQL MySQL PostgreSQL Micros
- 本文实例讲述了MySQL游标概念与用法。分享给大家供大家参考,具体如下:1、游标的概念(Cursor)一条sql,对应N条资源,取出资源的接
- Python中的切片知识.在Python中,切片(slice)是对序列型对象(如list, string, tuple)的一种高级索引方法。
- 在并不知道DIV浮动层的宽度,高度的情况下,如何使浮动层在任何分辩率的情况下居中呢! 下面使用了JAVASCRIPT来
- 目录GIL 的起源尝试消除 GIL积重难返为什么 Python3 一开始时不去除 GIL最后的话我们知道,在 CPython 中,有一个全局
- YUI Compressor 压缩 JavaScript 的内容包括:移除注释移除额外的空格细微优化标识符替换(Identifier Rep
- 一.雅黑设计理念 雅黑字体是为微软公司设计的屏幕显示汉字。它具有个性独特、结体优美、识别性强、块状效果好、显示清晰等优点。在当今数字化时代更