百度360必应搜狗淘宝本站头条
nodejsbufferorg.jsoupsetnocountoncv2.bitwise_nottime.strptime
当前位置：网站首页 > 编程字典 > 正文

第40期:MySQL 分区表案例分享（mysql分区表的坑）

toyiye 2024-07-09 23:09 12 浏览 0 评论

基于时间类分区我之前写过实现篇、细节篇。今天来继续分享一下时间类分区的真实案例：某家互联网公司数据库系统的表调优过程。

问题与背景：

  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 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！

相关推荐

为何越来越多的编程语言使用JSON（为什么编程）

JSON是JavascriptObjectNotation的缩写，意思是Javascript对象表示法，是一种易于人类阅读和对编程友好的文本数据传递方法，是JavaScript语言规范定义的一个子...

何时在数据库中使用 JSON（数据库用json格式存储）

在本文中，您将了解何时应考虑将JSON数据类型添加到表中以及何时应避免使用它们。每天?分享?最新?软件?开发?，Devops，敏捷?，测试?以及?项目?管理?最新?，最热门?的?文章?，每天?花?...

MySQL 从零开始:05 数据类型（mysql数据类型有哪些,并举例）

前面的讲解中已经接触到了表的创建，表的创建是对字段的声明，比如：上述语句声明了字段的名称、类型、所占空间、默认值和是否可以为空等信息。其中的int、varchar、char和decimal都...

JSON对象花样进阶（json格式对象）

一、引言在现代Web开发中，JSON（JavaScriptObjectNotation）已经成为数据交换的标准格式。无论是从前端向后端发送数据，还是从后端接收数据，JSON都是不可或缺的一部分。...

深入理解 JSON 和 Form-data（json和formdata提交区别）

在讨论现代网络开发与API设计的语境下，理解客户端和服务器间如何有效且可靠地交换数据变得尤为关键。这里，特别值得关注的是两种主流数据格式：...

JSON 语法（json 语法 priority）

JSON语法是JavaScript语法的子集。JSON语法规则JSON语法是JavaScript对象表示法语法的子集。数据在名称/值对中数据由逗号分隔花括号保存对象方括号保存数组JS...

JSON语法详解（json的语法规则）

JSON语法规则JSON语法是JavaScript对象表示法语法的子集。数据在名称/值对中数据由逗号分隔大括号保存对象中括号保存数组注意：json的key是字符串,且必须是双引号，不能是单引号...

MySQL JSON数据类型操作（mysql的json）

概述mysql自5.7.8版本开始，就支持了json结构的数据存储和查询，这表明了mysql也在不断的学习和增加nosql数据库的有点。但mysql毕竟是关系型数据库，在处理json这种非结构化的数据...

JSON的数据模式（json数据格式示例）

像XML模式一样，JSON数据格式也有Schema，这是一个基于JSON格式的规范。JSON模式也以JSON格式编写。它用于验证JSON数据。JSON模式示例以下代码显示了基本的JSON模式。{"...

前端学习——JSON格式详解（后端json格式）

JSON(JavaScriptObjectNotation)是一种轻量级的数据交换格式。易于人阅读和编写。同时也易于机器解析和生成。它基于JavaScriptProgrammingLa...

什么是 JSON:详解 JSON 及其优势（什么叫json）

现在程序员还有谁不知道JSON吗？无论对于前端还是后端，JSON都是一种常见的数据格式。那么JSON到底是什么呢？JSON的定义...

PostgreSQL JSON 类型:处理结构化数据

PostgreSQL提供JSON类型，以存储结构化数据。JSON是一种开放的数据格式，可用于存储各种类型的值。什么是JSON类型？JSON类型表示JSON(JavaScriptO...

JavaScript:JSON、三种包装类（javascript 包）

JOSN:我们希望可以将一个对象在不同的语言中进行传递，以达到通信的目的，最佳方式就是将一个对象转换为字符串的形式JSON（JavaScriptObjectNotation）-JS的对象表示法...

Python数据分析 只要1分钟 教你玩转JSON 全程干货

Json简介：Json，全名JavaScriptObjectNotation,JSON(JavaScriptObjectNotation(记号、标记))是一种轻量级的数据交换格式。它基于J...

比较一下JSON与XML两种数据格式?（json和xml哪个好）

JSON（JavaScriptObjectNotation）和XML（eXtensibleMarkupLanguage）是在日常开发中比较常用的两种数据格式，它们主要的作用就是用来进行数据的传...

取消回复欢迎 你 发表评论:

请填写验证码
一周热门
最近发表
标签列表