第40期(MySQL 分区表案例分享)

第40期(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)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)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)alter table pt_2020 drop primary key, add primary key (id,log_date); Query OK, 0 rows affected (0.29 sec) Records: 0Duplicates: 0Warnings: 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)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)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)select * from pt_old where log_date = '2020-03-01'; ... 1 row in set (9.99 sec)(localhost:ytt)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)select count(*) from pt_old where log_date = '2020-03-01'; ... 40408 rows in set (0.63 sec)(localhost:ytt)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)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)call sp_autoextend_partition_pt_current(2022); Query OK, 0 rows affected (14.55 sec)

接下来每年底需要做的事情就是把去年的数据挪走,并且删除旧分区定义,添加新的一年分区定义。
现在时间到了2022年,那先给 pt_current 插入2021年的数据(真实环境里,这部分数据是已经存在的):
(localhost:ytt)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: 3641722Duplicates: 0Warnings: 0(localhost:ytt)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)create table pt_2020 like pt_old; Query OK, 0 rows affected (0.05 sec)(localhost:ytt)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: 3641722Duplicates: 0Warnings: 0

【第40期(MySQL 分区表案例分享)】删除过期数据:
(localhost:ytt)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: 0Duplicates: 0Warnings: 0...Query OK, 0 rows affected (0.82 sec) Records: 0Duplicates: 0Warnings: 0...

需要注意的点: 分区定义一定要有规则,这样有利于后期清理过期数据。 关于 MySQL 的技术内容,你们还有什么想知道的吗?赶紧留言告诉小编吧!
第40期(MySQL 分区表案例分享)
文章图片

    推荐阅读