我们都知道数据是重要的,一些企业需要历史数据是可用的。问题是,对大型表的查询如果不适当优化表现不佳。我们得到了许多客户的请求管理服务清洗/存档大表,为了实现它,我们使用pt-archiver。
最近,我们收到一个请求存档一个大表,和客户担心停机时间在档案和性能问题。
我们提出了一个解决方案使用pt-archive客户档案表。这个想法是为了档案旧数据到其他表和保持最新的数据对当前表以最小的性能问题。所有的数据仍将是可用的,并且可以随时查询。
在这个博客中,我将不能解释如何使用pt-archiver,但我们将讨论pt-archiver的一个用例。
如果您不熟悉pt-archiver,请参考“想要存档表吗?使用Pe雷竞技下载官网rcona工具包pt-archive”;它解释了pt-archiver如何工作和各种pt-archiver参数。
我们将测试数据档案价值保持100天的数据与几个简单的步骤演示。
注意:这个方法使用时间戳数据类型来过滤数据。
- 创建了两个虚拟表。
- 在源表中插入记录。
- 档案记录使用- -条件从源到目标表/业务需求。
- 重命名表。
- 添加Pt-archiver cron。
这个图更能说明这个过程。
还记得- - - - - -在这个例子中日期和定下的条件只是一个参考。使用存档条件pt-archiver按业务需求。
让我们创建一个源表并插入记录使用mysql_random_data_load:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
创建
表
:
创建
表
”
博客
”
(
”
id
”
长整型数字
(
20.
)
无符号
不
零
AUTO_INCREMENT
,
”
created_at
”
时间戳
零
默认的
零
,
”
updated_at
”
时间戳
零
默认的
零
,
”
deleted_at
”
时间戳
零
默认的
零
,
主
关键
(
”
id
”
)
,
关键
”
blogpost_created_at_index
”
(
”
created_at
”
)
)
引擎
=
InnoDB
默认的
字符集
=
latin1
1
行
在
集
(
0.00
证券交易委员会
)
|
让我们创建目标表;
(使用下表仅供演示目的)
|
1
2
3
|
创建
表
如果
不
存在
blogpost_new
就像
博客
;
查询
好吧
,
0
行
影响
(
0.02
证券交易委员会
)
|
创建以下触发使用pt-online-schema-change
为什么触发器?
任何修改在原表数据副本将反映在新表,因为pt-online-schema-change创建触发器原始表更新相应的新表中的行。
(出于演示的目的,我添加了从pt-online-schema-change创建的触发器测试运行)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20.
21
22
23
24
25
26
27
28
|
#事件:删除
分隔符
/
/
创建
触发
”
pt_osc_test_blogpost_del
”
后
删除
在
”
测试
”
。
”
博客
”
为
每一个
行
开始
声明
继续
处理程序
为
1146年
开始
结束
;
删除
忽略
从
”
测试
”
。
”
blogpost_new
”
在哪里
”
测试
”
。
”
blogpost_new
”
。
”
id
”
< =
>
老
。
”
id
”
;
结束
/
/
分隔符
;
/
/
#事件:更新
分隔符
/
/
创建
触发
”
pt_osc_test_blogpost_upd
”
后
更新
在
”
测试
”
。
”
博客
”
为
每一个
行
开始
声明
继续
处理程序
为
1146年
开始
结束
;
删除
忽略
从
”
测试
”
。
”
_blogpost_new
”
在哪里
!
(
老
。
”
id
”
< =
>
新
。
”
id
”
)
和
”
测试
”
。
”
blogpost_new
”
。
”
id
”
< =
>
老
。
”
id
”
;
取代
成
”
测试
”
。
”
_blogpost_new
”
(
”
id
”
,
”
created_at
”
,
”
updated_at
”
,
”
deleted_at
”
)
值
(
新
。
”
id
”
,
新
。
”
created_at
”
,
新
。
”
updated_at
”
,
新
。
”
deleted_at
”
)
;
结束
/
/
分隔符
;
/
/
#事件:插入
分隔符
/
/
创建
触发
”
pt_osc_test_blogpost_ins
”
后
插入
在
”
测试
”
。
”
博客
”
为
每一个
行
开始
声明
继续
处理程序
为
1146年
开始
结束
;
取代
成
”
测试
”
。
”
blogpost_new
”
(
”
id
”
,
”
created_at
”
,
”
updated_at
”
,
”
deleted_at
”
)
值
(
新
。
”
id
”
,
新
。
”
created_at
”
,
新
。
”
updated_at
”
,
新
。
”
deleted_at
”
)
;
结束
分隔符
;
/
/
|
验证所有触发器创建了。
|
1
|
选择
*
从
INFORMATION_SCHEMA
.TRIGGERS
在哪里
TRIGGER_SCHEMA
=
“测试”
和
EVENT_OBJECT_TABLE
在
(
“博客”
)
G
|
输出应该类似
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
|
触发
_的名字
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
|
pt_osc_test_blogpost_ins
|
|
pt_osc_test_blogpost_upd
|
|
pt_osc_test_blogpost_del
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
3
行
在
集
(
0.01
证券交易委员会
)
|
使用pt-archiver复制过去100天的数据。验证与排练,
(屏幕会话可以用来执行pt-archiver如果表很大的大小)。
|
1
2
3
4
5
6
7
|
pt
- - - - - -
存储服务器
- - -
源
h
=
本地主机
,
D
=
测试
,
t
=
博客
,
我
=
blogpost_created_at_index
- - -
桌子
h
=
本地主机
,
D
=
测试
,
t
=
blogpost_new
- - -
在哪里
“created_at > = date_sub (curdate(),间隔100天)”
- - -
没有
- - - - - -
删除
- - -
没有
- - - - - -
检查
- - - - - -
列
- - -
限制
=
10000年
- - -
进步
=
10000年
- - -
没有
- - - - - -
检查
- - - - - -
字符集
- - -
干
- - - - - -
运行
|
输出应该是这样的:
|
1
2
3
4
5
|
选择
/
*
!
40001年
SQL_NO_CACHE
*
/
”
id
”
,
”
created_at
”
,
”
updated_at
”
,
”
deleted_at
”
从
”
测试
”
。
”
博客
”
力
指数
(
”
blogpost_created_at_index
”
)
在哪里
(
created_at
> =
date_sub
(
curdate
(
)
,
时间间隔
One hundred.
一天
)
)
订单
通过
”
created_at
”
限制
10000年
选择
/
*
!
40001年
SQL_NO_CACHE
*
/
”
id
”
,
”
created_at
”
,
”
updated_at
”
,
”
deleted_at
”
从
”
测试
”
。
”
博客
”
力
指数
(
”
blogpost_created_at_index
”
)
在哪里
(
created_at
> =
date_sub
(
curdate
(
)
,
时间间隔
One hundred.
一天
)
)
和
(
(
(
吗?
是
零
和
”
created_at
”
是
不
零
)
或
(
”
created_at
”
>
吗?
)
)
)
订单
通过
”
created_at
”
限制
10000年
插入
成
”
测试
”
。
”
blogpost_new
”
(
”
id
”
,
”
created_at
”
,
”
updated_at
”
,
”
deleted_at
”
)
值
(
吗?
,
吗?
,
吗?
,
吗?
)
|
让我们执行pt-archiver:
(以下Pt-archiver将价值100天的数据复制到_new表和触发器将最新的_new表)。
|
1
2
3
4
5
6
7
|
pt
- - - - - -
存储服务器
- - -
源
h
=
本地主机
,
D
=
测试
,
t
=
博客
,
我
=
blogpost_created_at_index
- - -
桌子
h
=
本地主机
,
D
=
测试
,
t
=
blogpost_new
- - -
在哪里
“created_at > = date_sub (curdate(),间隔100天)”
- - -
没有
- - - - - -
删除
- - -
没有
- - - - - -
检查
- - - - - -
列
- - -
限制
=
10000年
- - - - - -取代
- - -
进步
=
10000年
- - -
没有
- - - - - -
检查
- - - - - -
字符集
|
pt-archiver输出应该是这样的:
|
1
2
3
4
5
6
7
8
9
|
时间
运行
数
2022年
- - - - - -
09年
- - - - - -
28 t23
:
50
:
20.
0
0
2022年
- - - - - -
09年
- - - - - -
28 t23
:
50
:
44
24
10000年
2022年
- - - - - -
09年
- - - - - -
28 t23
:
51
:
08年
48
20000年
2022年
- - - - - -
09年
- - - - - -
28 t23
:
51
:
25
65年
27590年
|
pt-archiver完成后,检查条件pt-archiver的代码:
|
1
|
回声
美元
吗?
|
(应该是0)
下一步是检查行插入一个新表,比较它与原来的表。
注意:以下测试的结果只是例子;使用正确的日期或条件:
|
1
2
3
4
5
6
7
8
9
10
11
|
选择
马克斯
(
created_at
)
从
测试
.blogpost_new
;
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
|
马克斯
(
created_at
)
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
|
2022年
- - - - - -
27
- - - - - -
09年
02
:
11
:
00
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - - - - -
+
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
选择
数
(
1
)
从
测试
.blogpost
在哪里
created_at
< =
date_sub
(
“2022-27-09”
,
时间间隔
One hundred.
一天
)
;
+
- - -
- - -
- - -
- - -
- - -
+
|
数
(
1
)
|
+
- - -
- - -
- - -
- - -
- - -
+
|
65366年
|
+
- - -
- - -
- - -
- - -
- - -
+
1
行
在
集
(
0.02
证券交易委员会
)
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
选择
数
(
1
)
从
测试
.blogpost_new
在哪里
created_at
> =
date_sub
(
“2022-09-26”
,
时间间隔
One hundred.
一天
)
;
+
- - -
- - -
- - -
- - -
- - -
+
|
数
(
1
)
|
+
- - -
- - -
- - -
- - -
- - -
+
|
23758年
|
+
- - -
- - -
- - -
- - -
- - -
+
1
行
在
集
(
0.01
证券交易委员会
)
|
为什么重命名表?
这个想法是为了保持100天的新表中的数据和重命名它作为源表,和原始表_归档文件前缀的所有数据,包括最后的100天。
|
1
|
重命名
表
博客
来
blogpost_archive
,
blogpost_new
来
博客
;
|
它应该是这样的:
|
1
2
3
4
5
6
7
8
9
10
11
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
|
Tables_in
_测试
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
|
博客
|
|
blogpost_archive
|
+
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
+
|
使用pt-online-schema-change创建的触发器。
|
1
2
3
4
5
|
下降
触发
如果
存在
测试
.test_blogpost_ins
;
下降
触发
如果
存在
测试
.test_blogpost_upd
;
下降
触发
如果
存在
测试
.test_blogpost_del
;
|
一旦完成表重命名,blogpost表的数据复制到_archive表。
让我们添加pt-archiver命令cron,使流程自动化。(建议创建脚本,使用以下文档命令,和测试它。)
|
1
2
3
4
5
6
7
|
pt
- - - - - -
存储服务器
- - -
源
h
=
本地主机
,
D
=
测试
,
t
=
博客
,
我
=
blogpost_created_at_index
- - -
桌子
h
=
本地主机
,
D
=
测试
,
t
=
blogpost_archive
- - -
在哪里
“created_at > = date_sub (curdate(),间隔100天)”
- - -
没有
- - - - - -
删除
- - -
没有
- - - - - -
检查
- - - - - -
列
- - -
限制
=
10000年
- - -
进步
=
10000年
- - - - - -取代
b
=
0
,
l
=
是的
- - - - - -散装
- - - - - -
插入
- - -
没有
- - - - - -
检查
- - - - - -
字符集
>
dev
>
零
2
>
1
>
路径
/
来
/
日志
|
现在是时候检查如果cron成功。
检查是否已经复制到行删除test.blogpost_archive表,删除了test.blogpost:
|
1
|
选择
数
(
1
)
从
测试
.blogpost_archive
在哪里
created_at
< =
date_sub
(
“2022-09-02”
,
时间间隔
One hundred.
一天
)
;
|
验证的blogpost表有100天的数据,和下面的查询应该返回0行:
|
1
|
选择
数
(
1
)
从
测试
.blogpost
在哪里
created_at
< =
date_sub
(
“2022-09-02”
,
时间间隔
One hundred.
一天
)
;
|
希望你发现这个用例的pt-archiver帮助当你需要清洗/归档大型表!
订阅
登录
0评论
