空间约束一直是没完没了的和艰苦的过程对我们许多人来说,尤其是在系统有大量的事务和数据增长超过数百GBs在几天内。在这个博客中,我将分享一个解决方案来消除这种空间和从表中删除行几秒钟无论大小的表不会造成任何额外的数据库上的负载使用表分区。
第一种方法为删除行,任何人的思想是在SQL中使用删除查询。假设,一个想要从一个表中删除行,比一个查询等操作是这样的:
|
1
|
删除
从
工资
在哪里
from_date
<
DATE_SUB(现在(),时间间隔
1
一年);
|
上面的查询非常简单但有几个事项:
- 服务器业务将呈现指数级增长,可能会影响服务器上通常的交通。
- 加快上面的查询我们需要创建一个适当的索引,查询可以最小执行时间和减少对服务器性能的影响。
- 如果我们使用binlog_format行,大量本日志将被创建可能阻塞I / O服务器和需要额外的清理。
- 即使删除所有的行,空间不会被释放。MySQL不会缩小表空间和存储不会被释放到文件系统。释放文件系统,我们需要重新创建表通过运行分析或改变。
为了解决这个问题的方法之一是使用Percona雷竞技下载官网pt-archiver。有一个优秀的博客你可以遵循mysql数据档案的破坏最小。但文档过程需要时间,因为它还考虑系统负载,副本滞后和指定参数节流归档过程不影响正在进行的交通。
我在这里提出的是使用MySQL分区,这是一种更快的方法。
分区是什么?
在MySQL中,InnoDB存储引擎一直支持表空间和MySQL服务器的概念,甚至引入分区前,可以配置为使用不同的物理目录来存储不同的数据库。分区将这个概念进一步,允许用户保存的部分表根据用户定义的规则。用户选择规则的数据可分为称为分区功能,这可能是一个简单的规则对一组范围或值列表,一个内部哈希函数,或一个线性哈希函数。
当我们分区表数据文件是跨越多个分区较小的数据文件。操作我们针对特定范围的数据,不会影响整个表只有一个数据文件是感动。
没有分区的表:
|
1
2
|
centos
:
员工
* # * # ls lh的薪水
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
104克
10月
11
05年
:
47
工资
。
炎症性肠病
|
表分区:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20.
|
centos
:
员工
* # * # ls lh的薪水
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
9.0克
10月
11
05年
:
47
工资
# P # p01.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
9.0克
10月
11
05年
:
47
工资
# P # p02.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
10克
10月
11
05年
:
47
工资
# P # p03.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
11 g
10月
11
05年
:
47
工资
# P # p04.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
11 g
10月
11
05年
:
47
工资
# P # p05.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
12克
10月
11
05年
:
47
工资
# P # p06.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
12克
10月
11
05年
:
47
工资
# P # p07.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
13克
10月
11
05年
:
47
工资
# P # p08.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
14 g
10月
11
05年
:
47
工资
# P # p09.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
14 g
10月
11
05年
:
47
工资
# P # p10.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
15克
10月
11
05年
:
47
工资
# P # p11.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
15克
10月
11
05年
:
47
工资
# P # p12.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
16克
10月
11
05年
:
47
工资
# P # p13.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
16克
10月
11
05年
:
47
工资
# P # p14.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
17 g
10月
11
05年
:
47
工资
# P # p15.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
17 g
10月
11
05年
:
47
工资
# P # p16.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
16克
10月
11
05年
:
47
工资
# P # p17.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
13克
10月
11
05年
:
47
工资
# P # p18.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
96米
10月
11
05年
:
45
工资
# P # p19.ibd
|
分区将如何帮助在快速删除行和释放空间?
档案旧分区表中的数据,我们将创建一个空表,是相同的原始表的结构,但不像原始表有多个分区。创建这个表之后我们将交换新创建的空表的原始表的分区在几秒钟。
在这个例子中,我们使用一个表分区的基础上一个日期范围。
|
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
29日
30.
31日
32
33
|
mysql>
显示
创建
表
salariesG
* * * * * * * * * * * * * * * * * * * * * * * * * * *1。
行
* * * * * * * * * * * * * * * * * * * * * * * * * * *
表:工资
创建
表:
创建
表
“工资”(
列出的
int(11)
非空,
“工资”
int(11)
非空,
“from_date”
日期
非空,
“to_date”
日期
非空,
“response_code”
团
非空,
主键
('列出',' from_date ')
)
引擎
=
InnoDB
默认的
字符集
=latin1
/ * !505年00分区BY RANGE COLUMNS(from_date)
(分区p01值小于(1985-12-31)引擎= InnoDB,
分区p02值小于(1986-12-31)引擎= InnoDB,
分区p03值小于(1987-12-31)引擎= InnoDB,
分区p04值小于(1988-12-31)引擎= InnoDB,
分区p05值小于(1989-12-31)引擎= InnoDB,
分区p06值小于(1990-12-31)引擎= InnoDB,
分区p07值小于(1991-12-31)引擎= InnoDB,
分区p08值小于(1992-12-31)引擎= InnoDB,
分区p09值小于(1993-12-31)引擎= InnoDB,
分区p10值小于(1994-12-31)引擎= InnoDB,
分区侯值小于(1995-12-31)引擎= InnoDB,
分区p12值小于(1996-12-31)引擎= InnoDB,
分区p13值小于(1997-12-31)引擎= InnoDB,
分区好值小于(1998-12-31)引擎= InnoDB,
分区p15值小于(1999-12-31)引擎= InnoDB,
分区p16值小于(2000-12-31)引擎= InnoDB,
分区p17值小于(2001-12-31)引擎= InnoDB,
分区p18值小于(2002-12-31)引擎= InnoDB,
分区p19值小于(MAXVALUE)引擎= InnoDB) * /
1
行
在
集
(0.00sec)
|
这个表的大小大约是105 GB。
|
1
2
3
4
5
6
7
8
|
mysql>
选择
table_schema,table_name,table_rows,
轮(data_length/1024年/1024年/1024年)DATA_MB,
轮(index_length/1024年/1024年/1024年)INDEX_GB,
轮(data_free/1024年/1024年/1024)FREE_MB,
轮(data_length/1024年/1024年/1024年)+轮(index_length/1024年/1024年)+轮(data_free/1024年/1024年/ 1024)TOTAL_MB
从
information_schema。表
在哪里
table_schema =“员工”
和
table_name =“工资”;
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +
|table_schema|table_name|table_rows|DATA_GB|INDEX_GB|FREE_GB|TOTAL_GB |
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +
|员工&,
,
|工资
,
|,
,
2845404|
,
,
105年|,
,
,
,
0|
,
,
,
0|,
,
,105年|
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +
1
行
在
集
(0.00sec)
|
我们将创建一个表,分区表相同,但我们会从这个新创建的表删除分区。
|
1
2
3
4
5
6
|
mysql>
创建
表
salaries_swap_p3
就像
工资;
查询好吧,0
行
影响(0.23sec)
mysql>
改变
表
salaries_swap_p3删除分区;
查询好吧,0
行
影响(0.32sec)
记录:0&,副本:0&,
警告:0
|
在这个时刻,这个分区表的所有数据,占用空间在文件系统。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql>
选择数(*)
从
工资
分区
(3);
+ - - - - - - - - - - - +
|
数(*)|
+ - - - - - - - - - - - +
|,
,
57395年|
+ - - - - - - - - - - - +
1
行
在
集
(0.00sec)
mysql>
选择数(*)
从
salaries_swap_p3;
+ - - - - - - - - - - - +
|
数(*)|
+ - - - - - - - - - - - +
|,
,
,
,
0|
+ - - - - - - - - - - - +
1
行
在
集
(0.02sec)
|
空间保留的文件系统:
|
1
2
3
4
|
(
根
@
知识产权
- - - - - -
172年
- - - - - -
31日
- - - - - -
83年
- - - - - -
227年
员工
]
# ls -lrth * 3 * *工资
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
96 k
11月
21
03
:
54
salaries_swap_p3
.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
8.5 k
11月
21
03
:
56
salaries_swap_p3
.frm
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
10克
11月
21
03
:
56
工资
# P # p03.ibd
|
交换分区,我们需要执行一个查询,需要元数据锁在桌子上,几乎瞬间完成。
|
1
|
mysql>
改变
表
工资交换
分区
3
与
表
salaries_swap_p3;
|
由于此操作需要元数据锁和非常快,它是建议在低流量运行这个操作时间。
|
1
2
3
4
5
6
7
|
mysql>
显示
processlist;
+ - - - + - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
|Id|
用户
|
主机
|db
|命令|
时间
|状态
|信息
|Rows_sent|Rows_examined|
+ - - - + - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
|4|根|本地主机|员工|查询
|
0|等待
为
表
元数据
锁
|
改变
表
工资交换
分区
3
与
表
salaries_swap_p3|
0|
0|
+ - - - + - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
1
行
在
集
(0.00sec)
|
交换后,新表的所有数据分区的分区表是空的。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql>
选择数(*)
从
工资
分区
(3);
+ - - - - - - - - - - - +
|
数(*)|
+ - - - - - - - - - - - +
|
0|
+ - - - - - - - - - - - +
1
行
在
集
(0.00sec)
mysql>
选择数(*)
从
salaries_swap_p3;
+ - - - - - - - - - - - +
|
数(*)|
+ - - - - - - - - - - - +
|
57395年|
+ - - - - - - - - - - - +
1
行
在
集
(0.02sec)
|
空间释放文件系统:
|
1
2
3
4
|
(
根
@
知识产权
- - - - - -
172年
- - - - - -
31日
- - - - - -
83年
- - - - - -
227年
员工
]
# ls -lrth * 3 * *工资
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
10克
11月
21
03
:
54
salaries_swap_p3
.ibd
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
8.5 k
11月
21
03
:
56
salaries_swap_p3
.frm
- - - - - -
rw
- - - - - -
r
- - -
- - -
- - - - - -
。
1
mysql
mysql
96 k
11月
21
03
:
56
工资
# P # p03.ibd
|
现在,您可以继续降低交换表没有锁定的主要表或也可以使用这种技术来加速下降:加快你的MySQL表下降。
|
1
2
|
mysql
>
下降
表
如果
存在
salaries_swap_p3
;
查询
好吧
,
0
行
影响
(
5.23
证券交易委员会
)
|
结论
我们所做的是使用MySQL partitioning-related函数来回答客户的挑战清除数据比x天。但是,分区不是一刀切的解决方案,分区有自己的警告。如果有很多二级索引表和搜索查询不仅限于分区键,查询性能恶化可能成倍增长。如果分区表不是一个选择,pt-archiver是一个伟大的工具,它会自动删除语句在数据库以最小的监控。raybet雷竞技竞猜在线官网
进一步的参考:
https://dev.mysql.com/doc/refman/5.7/en/partitioning.html
https://dev.mysql.com/doc/refman/5.7/en/partitioning-management-exchange.html
想要存档表吗?使用Pe雷竞技下载官网rcona工具包pt-archiver
订阅
登录
0评论
