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

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

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

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

數據表（日志）是這樣的：

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

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

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

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小時），因此循環(huán)調用存儲過程時應記錄操作時間和執(zhí)行過程中的參數等；

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(); -- 記錄當前分區(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(); -- 操作結束時間
        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處理這種任務（除非迫不得已），如果能將能處理過程放在大數據平臺上處理，速度會更快，而且元數據管理會相對專業(yè)。

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

您可能感興趣的文章: