如何使用分區(qū)處理MySQL的億級(jí)數(shù)據(jù)優(yōu)化

mysql在查詢(xún)上千萬(wàn)級(jí)數(shù)據(jù)的時(shí)候，通過(guò)索引可以解決大部分查詢(xún)優(yōu)化問(wèn)題。但是在處理上億數(shù)據(jù)的時(shí)候，索引就不那么友好了。

數(shù)據(jù)表（日志）是這樣的：

• 表大?。?T，約24億行；
• 表分區(qū)：按時(shí)間分區(qū)，每個(gè)月為一個(gè)分區(qū)，一個(gè)分區(qū)約2-3億行數(shù)據(jù)（40-70G左右）。

由于數(shù)據(jù)不需要全量處理，經(jīng)過(guò)與需求方討論后，我們按時(shí)間段抽樣一部分?jǐn)?shù)據(jù)，比如抽樣一個(gè)月的數(shù)據(jù)，約3.5億行。
數(shù)據(jù)處理的思路：

1）建表引擎選擇Innodb。由于數(shù)據(jù)是按月分區(qū)的，我們將該月分區(qū)的數(shù)據(jù)單獨(dú)copy出來(lái)，源表為myisam引擎，因我們可能需要過(guò)濾部分?jǐn)?shù)據(jù)，涉及到篩選的字段又沒(méi)有索引，使用myisam引擎加索引的速度會(huì)比較慢；
2）按日分區(qū)。將copy出來(lái)的表加好索引后（約2-4個(gè)小時(shí)），過(guò)濾掉無(wú)用的數(shù)據(jù)，同時(shí)再次新生成一張表，抽取json中需要的字段，并對(duì)該表按日分區(qū)。

CREATE TABLE `tb_name` (
  `id_`,
  ...,
  KEY `idx_1` (`create_user_`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='應(yīng)用日志'
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）對(duì)上面生成的表按每日進(jìn)行聚合或者其他操作，并將結(jié)果存儲(chǔ)到臨時(shí)表中，盡量使用存儲(chǔ)過(guò)程加工數(shù)據(jù)，由于加工相對(duì)復(fù)雜而且耗時(shí)較多（跑一次存儲(chǔ)過(guò)程需要大概1-2小時(shí)），因此循環(huán)調(diào)用存儲(chǔ)過(guò)程時(shí)應(yīng)記錄操作時(shí)間和執(zhí)行過(guò)程中的參數(shù)等；

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(); -- 記錄當(dāng)前分區(qū)操作起始時(shí)間
        
  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(); -- 操作結(jié)束時(shí)間
        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）對(duì)上述生成的結(jié)果進(jìn)行整理加工。

總的來(lái)說(shuō)，處理過(guò)程相對(duì)繁瑣，而且產(chǎn)生了很多中間表，對(duì)關(guān)鍵步驟還需要記錄操作流程的元數(shù)據(jù)，這對(duì)SQL處理的要求會(huì)比較高，因此不建議使用MySQL處理這種任務(wù)（除非迫不得已），如果能將能處理過(guò)程放在大數(shù)據(jù)平臺(tái)上處理，速度會(huì)更快，而且元數(shù)據(jù)管理會(huì)相對(duì)專(zhuān)業(yè)。

到此這篇關(guān)于如何使用分區(qū)處理MySQL的億級(jí)數(shù)據(jù)優(yōu)化的文章就介紹到這了,更多相關(guān)MySQL 億級(jí)數(shù)據(jù)優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！