mysql 亿级别秒查_mysql亿级数据查询方法说明

mysql在查询上千万级数据的时候，通过索引可以解决大部分查询优化问题。但是在处理上亿数据的时候，需要用到的东西就超出索引的范围了。

数据表(日志)是这样的：

表大小：1T，约24亿行；

表分区：按时间分区，每个月为一个分区，一个分区约2-3亿行数据(40-70G左右)。

操作日志含有json字段。

经过与需求方讨论后，我们决定只取某一个月的数据作为样本，该样本的数据行数约3.5亿行。

数据处理的思路：

1)由于数据是按月分区的，我们将该月分区的数据单独copy出来，源表为myisam引擎，因我们可能需要过滤部分数据，涉及到筛选的字段又没有索引，使用myisam引擎加索引的速度会比较慢，建议目标表用innodb引擎；

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)对上述生成的结果进行整理加工。

总的来说，过程是相对繁琐，而且产生了很多中间表，如果能用hive来查询尽量不用mysql。