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

更新時間：2021年06月16日 15:14:44 作者：godzla

mysql在查詢上千萬級數(shù)據(jù)的時候，通過索引可以解決大部分查詢優(yōu)化問題。但是在處理上億數(shù)據(jù)的時候，應(yīng)該怎么解決，本文就是用分區(qū)來優(yōu)化一下，感興趣的一起來了解一下

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

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

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

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

1）建表引擎選擇Innodb。由于數(shù)據(jù)是按月分區(qū)的，我們將該月分區(qū)的數(shù)據(jù)單獨copy出來，源表為myisam引擎，因我們可能需要過濾部分?jǐn)?shù)據(jù)，涉及到篩選的字段又沒有索引，使用myisam引擎加索引的速度會比較慢；
2）按日分區(qū)。將copy出來的表加好索引后（約2-4個小時），過濾掉無用的數(shù)據(jù)，同時再次新生成一張表，抽取json中需要的字段，并對該表按日分區(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）對上面生成的表按每日進(jìn)行聚合或者其他操作，并將結(jié)果存儲到臨時表中，盡量使用存儲過程加工數(shù)據(jù)，由于加工相對復(fù)雜而且耗時較多（跑一次存儲過程需要大概1-2小時），因此循環(huán)調(diào)用存儲過程時應(yīng)記錄操作時間和執(zhí)行過程中的參數(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ū)操作起始時間
        
  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é)束時間
        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）對上述生成的結(jié)果進(jìn)行整理加工。

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

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

您可能感興趣的文章: