如何使用分区处理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


猜你喜欢
- MD5(Message-Digest Algorithm 5) 模块用于计算信息密文(信息摘要),得出一个128位的密文。sha模块跟md5
- Python 队列Queue 队列是一种先进先出(FIFO)的数据类型, 新的元素通过 入队 的方式添加进 Queue 的末尾, 出队 就是
- vue Eslint校验代码报错在使用脚手架创建Vue项目时,一般会安装Eslint插件,这个主要是校验代码格式和规范用的,但是它很有点让人
- 环境 centos7 python3pytesseract只是tesseract-ocr的一种实现接口。所以要先安装tesseract-oc
- 我们一般在Excel里面是使用数据连接属性里面写sql语句,或者vba里面利用ado组件执行sql语句。新版的Excel里面带上了Power
- 本文较为深入的探究了php中in_array函数用法。分享给大家供大家参考。具体如下:今天突然想到php中的in_array函数有个其怪的用
- 前言在SQL Server中,我们知道一个SELECT语句执行过程中只会申请一些意向共享锁(IS) 与共享锁(S), 例如我使用SQL Pr
- css+div做的菜单:一个主显示层,别的列表都隐藏着,用js函数设置列表的显示和隐藏。分别用到了两个函数,函数实现的效果是一样的,一个是参
- 前言最近在工作中遇到一个问题,在创建数据库后连接数据库的时候居然报错了,错误代码是Access denied for user 'r
- 本文实例讲述了JavaScript中callee和caller的区别与用法。分享给大家供大家参考,具体如下:1.callee在函数的内部,有
- 假设我有一个DataFrame(df)如下:name age idmike 10 1tony 14 2lee 20 3现在我想把id 放到最
- 本文实例讲述了Java实现基于JDBC操作mysql数据库的方法。分享给大家供大家参考,具体如下:package main;import j
- 〇、前言文件上传/下载接口与普通接口类似,但是有细微的区别。如果需要发送文件到服务器,例如:上传文档、图片、视频等,就需要发送二进制数据,上
- #-*- coding: UTF-8 -*-'''Created on 2013-12-5@author: good
- 今天我们来到了循环队列这一节,之前的文章中,我介绍过了用python自带的列表来实现队列,这是最简单的实现方法。但是,我们都知道,在列表中删
- 随着互联网的快速发展和数据交换的广泛应用,各种数据格式的处理成为软件开发中的关键问题。JSON 作为一种通用的数据交换格式,在各种应用场景中
- 一、将对象转为json字符串json.dumps:将 Python 对象编码成 JSON 字符串json.loads:将已编码的 JSON
- <%@ Page Language="VB" %> <!DOCTYPE html PUBLIC &qu
- template代码:<template> <div class="hello"> <ul
- Selenium 封装了现成的文件上传操作。但是随着现代前端框架的发展,文件上传的方式越来越多样。而有一些文件上传的控件,要做自动化控制会更