乐趣区

关于mysql:第40期MySQL-分区表案例分享

jiezi

基于工夫类分区我之前写过实现篇、细节篇。明天来持续分享一下工夫类分区的实在案例：某家互联网公司数据库系统内的分区表调优过程。

问题与背景：

  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)

接下来咱们要验证下革新前的预期成果：

第一，查问性能分区表要大幅晋升。

第二，分区表的治理、运维效率也要大幅晋升。

如果这两点都达到要求，就能够间接把分区表改名为原始表，原始表删除。

先来验证查问性能是否有大幅晋升：
第一条查问：查问当天的记录

以下基于表 pt_old 执行工夫为 9.99 秒，而基于表 pt_current 执行工夫为 0.02 秒； 分区表比一般表工夫上快 500 倍。

(localhost:ytt)<mysql>select * from pt_old where log_date = '2020-03-01';
...
1 row in set (9.99 sec)

(localhost:ytt)<mysql>select count(*) from pt_current where log_date = '2020-03-01';
...
9593 rows in set (0.02 sec)
第二条查问：查问截至 2020 年年底最近 5 天的记录

最终执行工夫 pt_old 为 0.53 秒，pt_current 为 0.02 秒， 分区表比原始表快 20 多倍。

(localhost:ytt)<mysql>select count(*) from pt_old where log_date = '2020-03-01';
...
40408 rows in set (0.63 sec)

(localhost:ytt)<mysql>select count(*) from pt_current where log_date = '2020-03-01';
...
40408 rows in set (0.02 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 的技术内容，你们还有什么想晓得的吗？连忙留言通知小编吧！

退出移动版