第40期：MySQL 分割槽表案例分享

愛可生雲資料庫發表於2022-03-23
MySql

基於時間類分割槽我之前寫過實現篇、細節篇。今天來繼續分享一下時間類分割槽的真實案例：某家網際網路公司資料庫系統的表調優過程。

問題與背景：

  1. 單張表資料量太大，每天會產生 10W 條記錄，一年就是 3650W 條記錄，
  2. 對這張表的查詢 95% 都是在某一天或者幾天內，過濾區間最大不超過一個月。比如在2019年3月1日、2019年4 月20 日或者是2019年5月1日和2019年5月5日這個時間段內。偶爾會涉及到跨月、跨年查詢，但是頻率很低。
  3. 記錄保留10年。也就是單表3.6億條記錄，單表太大，不便於管理，後期如果單表損壞，修復也難。
  4. 單表查詢效能很差，對歷史資料刪除效能也很差。

基於以上需求分析後得出結論：

  1. 查詢過濾的資料範圍相對比較集中，不是那麼分散；要同時考慮過期資料清理效能問題。
  2. 考慮把表拆分為10張新表，一張是當前表，剩餘9張是歷史歸檔表；當前表存放最近兩年的資料，每到年底遷移老舊資料到歷史表進行歸檔，並且對過期歷史資料進行清理。
  3. 考慮對部分過濾場景使用 MySQL 分割槽表，非常適合 95% 的查詢；可以使用分割槽置換功能把資料移到歷史表。
  4. 分割槽錶帶來幾個好處： 一是查詢效能提升；二是管理方便，過期資料直接快速清理；三是對應用透明，暫時不需要應用改程式碼。

接下來看看錶的優化過程：

由於隱私考慮，不方便貼原始表結構，這裡用結構簡化的示例表來看下優化過程。原始表為 pt_old ，縮減欄位個數到3，記錄數縮減10倍為 3650W ，每年365W（客戶原來欄位有30個，記錄數3.6億），記錄範圍從2011年到2020年，剛好十年的資料。

(localhost:ytt)<mysql>show create table pt_old\G
*************************** 1. row ***************************
       Table: pt_old
Create Table: CREATE TABLE `pt_old` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `r1` int DEFAULT NULL,
  `log_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_log_date` (`log_date`)
) ENGINE=InnoDB AUTO_INCREMENT=64306811 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

(localhost:ytt)<mysql>select min(log_date),max(log_date),count(*) from pt_old;
+---------------+---------------+----------+
| min(log_date) | max(log_date) | count(*) |
+---------------+---------------+----------+
| 2011-01-01    | 2020-12-31    | 36500000 |
+---------------+---------------+----------+
1 row in set (21.14 sec)

先匯出原始表資料（按照年匯出10份資料），後期直接匯入到新分割槽表，執行以下指令碼：

root@ytt-unbuntu:/home/ytt/scripts# cat pt_export 
#!/bin/sh
for i in `seq 2011 2020` 
do 
    {
        mysql -D ytt -e "select * from pt_old where log_date between '$i-01-01' and '$i-12-31' into outfile '/var/lib/mysql-files/pt_$i.csv' fields terminated by ',' " 
    } &
done
wait
root@ytt-unbuntu:/home/ytt/scripts# ./pt_export
root@ytt-unbuntu:/var/lib/mysql-files# ls -sihl
總用量 788M
5767677 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:39 pt_2011.csv
5775332 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:42 pt_2012.csv
5775334 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:42 pt_2013.csv
5774596 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:42 pt_2014.csv
5775335 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:42 pt_2015.csv
5775333 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:42 pt_2016.csv
5775329 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:42 pt_2017.csv
5775330 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:42 pt_2018.csv
5775336 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:42 pt_2019.csv
5775331 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:42 pt_2020.csv

分別以年為粒度，建立10張表，其中表 pt_2020 為分割槽表：

root@ytt-unbuntu:/home/ytt/scripts# for i in `seq 2011 2020`;do mysql -e"use ytt;create table pt_$i like pt_old;";done;

由於 MySQL 分割槽表硬性規定，分割槽鍵必須為主鍵或者主鍵的一部分，把時間欄位加到主鍵裡。

(localhost:ytt)<mysql>alter table pt_2020 drop primary key, add primary key (id,log_date);
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

給表 pt_2020 新增分割槽（有可能存放當年以及去年的資料，因此要按照天來分割槽，並且分成兩年，這樣到了新的一年，就直接把老舊資料遷移出去），修改下之前的儲存過程如下：

DELIMITER $$

USE `ytt`$$

DROP PROCEDURE IF EXISTS `sp_add_partition_pt_current`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_add_partition_pt_current`(
IN f_year_start YEAR,
IN f_year_end YEAR,
IN f_tbname VARCHAR(64)
)
BEGIN
DECLARE v_days INT UNSIGNED DEFAULT 365;
DECLARE v_year DATE DEFAULT '2011-01-01';
DECLARE v_partition_name VARCHAR(64) DEFAULT '';
DECLARE v_log_date DATE;
DECLARE i,j INT UNSIGNED DEFAULT 1;
  SET @stmt = '';
  SET @stmt_begin = CONCAT('ALTER TABLE ',f_tbname,' PARTITION BY RANGE COLUMNS(log_date)(');
  SET i = f_year_start;
  WHILE i <= f_year_end DO
    SET v_year = CONCAT(i,'-01-01');
    SET v_days = DATEDIFF(DATE_ADD(v_year,INTERVAL 1 YEAR),v_year);
    SET j = 1;
    WHILE j <= v_days DO
      SET v_log_date = DATE_ADD(v_year,INTERVAL j DAY);
      SET v_partition_name = CONCAT('p',i,'_',LPAD(j,3,'0'));
      SET @stmt = CONCAT(@stmt,'PARTITION ',v_partition_name,' VALUES LESS THAN(''',v_log_date,'''),');
      SET j = j + 1;
    END WHILE;
    SET i = i + 1;
  END WHILE;
  SET @stmt_end = 'PARTITION p_max VALUES LESS THAN (maxvalue))';
  SET @stmt = CONCAT(@stmt_begin,@stmt,@stmt_end);
  PREPARE s1 FROM @stmt;
  EXECUTE s1;
  DROP PREPARE s1;
  SELECT NULL,NULL,NULL INTO @stmt,@stmt_begin,@stmt_end;
END$$

DELIMITER ;


(localhost:ytt)<mysql>call sp_add_partition_pt_current(2020,2021,'pt_2020');
Query OK, 1 row affected (42.11 sec)

分別匯入原始資料：2020年的資料匯入表 pt_2020 ，其他資料匯入到歷史表 pt_2011 到 pt_2019 。

root@ytt-unbuntu:/home/ytt/scripts# cat pt_import 
#!/bin/sh
for i in `seq 2011 2020` 
do 
    {
        mysql -D ytt -e "load data infile '/var/lib/mysql-files/pt_$i.csv' into table pt_$i fields terminated by ',' " 
    } &
done
wait
root@ytt-unbuntu:/home/ytt/scripts# ./pt_import

更改表 p_2020 為當前表：

(localhost:ytt)<mysql>alter table pt_2020 rename to pt_current;
Query OK, 0 rows affected (0.12 sec)

接下來我們要驗證表改造後效能是否符合預期：

第一，查詢效能分割槽表要有優勢。

第二，分割槽表的管理、運維效率也要相應提升。

如果這兩點都達到要求，就可以直接把分割槽表改名為原始表，原始表刪除。

先來驗證查詢效能是否有提升：
第一條查詢：查詢'2020-03-01' 當天的記錄

基於資料是否被快取，這裡每個查詢我執行兩次。基於原始表 pt_old，第一次查詢時間為1分鐘1.7秒，第二次為0.03秒；基於分割槽表 pt_current ，第一次查詢時間為0.02秒，第二次為0.01秒。如果僅對比第一次查詢時間，分割槽表查詢效能大幅提升；第二次來講，相差不多，但分割槽表查詢效能依然領先。

(localhost:ytt)<mysql>select * from pt_old where log_date = '2020-03-01';
...
9593 rows in set (1 min 1.70 sec)
-- 第二次
9593 rows in set (0.03 sec)


(localhost:ytt)<mysql>select * from pt_current where log_date = '2020-03-01';
...
9593 rows in set (0.02 sec)
-- 第二次
9593 rows in set (0.01 sec)
第二條查詢：查詢2020年年底最後5天的記錄

依然每條查詢執行兩次。基於原始表pt_old的查詢時間第一次為2分鐘42.21秒，第二次為0.13秒；基於分割槽表 pt_current 的查詢時間第一次為0.07秒，第二次為0.01秒。兩次查詢結果，分割槽表效能的提升都很明顯。

(localhost:ytt)<mysql>select * from pt_old where log_date in ('2020-12-27','2020-12-28','2020-12-29','2020-12-30','2020-12-31');
...
30097 rows in set (2 min 42.21 sec)
...
-- 第二次
30097 rows in set (0.13 sec)


(localhost:ytt)<mysql>select * from pt_current where log_date in ('2020-12-27','2020-12-28','2020-12-29','2020-12-30','2020-12-31');
...
30097 rows in set (0.07 sec)
...
-- 第二次
30097 rows in set (0.01 sec)

現在來看下管理與運維效能是否有提升？

既然用分割槽表，就會涉及到一個很棘手的問題：每到年底，如何調整分割槽表來適應新增記錄？MySQL並沒有直接的方法， 不過我們可以利用預設分割槽 p_max 來手工擴容。

來看下錶 p_current 的分割槽資料：

(localhost:ytt)<mysql>select left(partition_name,5) p,sum(table_rows) cnt from information_schema.partitions where table_name = 'pt_current' group by leftt(partition_name,5);
+-------+---------+
| p     | cnt     |
+-------+---------+
| p2020 | 3641722 |
| p2021 |       0 |
| p_max |       0 |
+-------+---------+
3 rows in set (0.02 sec)

目前只有2020年有資料，2021年沒有資料，到2021年末記錄則會自動加入到分割槽 p_max 裡。所以應該在2022年1月1日凌晨前得把2020整年的資料挪出去變為 pt_2020 ，並把2022年的分割槽定義加進去。

那依照我們的分析，我再來寫一個自動擴充分割槽的儲存過程，可以配合OS的JOB或者MySQL的EVENT來自動執行，程式碼如下：

DELIMITER $$

USE `ytt`$$

DROP PROCEDURE IF EXISTS `sp_autoextend_partition_pt_current`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_autoextend_partition_pt_current`(
IN f_year YEAR
)
BEGIN
    DECLARE v_days INT UNSIGNED DEFAULT 365;
    DECLARE v_days_interval DATE DEFAULT '2018-12-31';
    DECLARE i INT UNSIGNED DEFAULT 1;
    SET @stmt = '';
    SET v_days =  DATEDIFF(CONCAT(f_year+1,'-01-01'),CONCAT(f_year,'-01-01'));
     
    SET @stmt_begin = 'ALTER TABLE pt_current REORGANIZE PARTITION p_max into(';
        WHILE i <= v_days DO
           SET v_days_interval = DATE_ADD(CONCAT(f_year,'-01-01'),INTERVAL i DAY);
           SET @stmt = CONCAT(@stmt,'PARTITION p',f_year,'_',LPAD(i,3,"0"),' VALUES LESS THAN (''',v_days_interval,'''),');     
           SET i = i + 1;        
        END WHILE;    
    SET @stmt_end = 'PARTITION p_max VALUES LESS THAN (maxvalue))';
        SET @stmt = CONCAT(@stmt_begin,@stmt,@stmt_end);
        PREPARE s1 FROM @stmt;
        EXECUTE s1;
        DROP PREPARE s1;
        SET @stmt = NULL;
        SET @stmt_begin = NULL;
        SET @stmt_end = NULL;    
    END$$

DELIMITER ;

現在來擴充2022年的分割槽資料：

(localhost:ytt)<mysql>call sp_autoextend_partition_pt_current(2022);
Query OK, 0 rows affected (14.55 sec)

接下來每年底需要做的事情就是把去年的資料挪走，並且刪除舊分割槽定義，新增新的一年分割槽定義。

現在時間到了2022年，那先給 pt_current 插入2021年的資料（真實環境裡，這部分資料是已經存在的）：

(localhost:ytt)<mysql>insert into pt_current (r1,log_date) select r1,date_add(log_date,interval 1 year) from pt_current;
Query OK, 3641722 rows affected (2 min 28.75 sec)
Records: 3641722  Duplicates: 0  Warnings: 0

(localhost:ytt)<mysql>select left(partition_name,5) p,sum(table_rows) cnt from information_schema.partitions where table_name = 'pt_current' group by left(partition_name,5);
+-------+---------+
| p     | cnt     |
+-------+---------+
| p2020 | 3641722 |
| p2021 | 3641726 |
| p2022 |       0 |
| p_max |       0 |
+-------+---------+
4 rows in set (0.02 sec)

再把2020年的資料挪到歷史表：（由於分割槽表中每年的分割槽數目較多，為了寫法方便，這裡我沒有用分割槽置換功能。）

(localhost:ytt)<mysql>create table pt_2020 like pt_old;
Query OK, 0 rows affected (0.05 sec)

(localhost:ytt)<mysql>insert into pt_2020 select * from pt_current where log_date between '2020-01-01' and '2020-12-31';
Query OK, 3641722 rows affected (1 min 12.54 sec)
Records: 3641722  Duplicates: 0  Warnings: 0

刪除過期資料：

(localhost:ytt)<mysql>SELECT CONCAT('alter table ytt.pt_current drop partition ',partition_name,';') FROM information_schema.`PARTITIONS`  WHERE table_schema = 'ytt' AND table_name = 'pt_current'  AND partition_name like 'p2020%' into outfile '/var/lib/mysql-files/drop_expire_partition_2020.sql';
Query OK, 366 rows affected (0.00 sec)

mysql> \. /var/lib/mysql-files/drop_expire_partition_2020.sql
Query OK, 0 rows affected (0.83 sec)
Records: 0  Duplicates: 0  Warnings: 0

...

Query OK, 0 rows affected (0.82 sec)
Records: 0  Duplicates: 0  Warnings: 0

...

需要注意： 分割槽定義一定要有規則，這樣有利於後期清理過期資料。

關於 MySQL 的技術內容，你們還有什麼想知道的嗎？趕緊留言告訴小編吧！

相關文章