如何使用分區處理MySQL的億級數據優化

Posted on by

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處理這種任務（除非迫不得已），如果能將能處理過程放在大數據平臺上處理，速度會更快，而且元數據管理會相對專業。

到此這篇關於如何使用分區處理MySQL的億級數據優化的文章就介紹到這瞭,更多相關MySQL 億級數據優化內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：