1.1. 场景
在每个系统中都会生成一些日志,往往有些日志都是需要记录的,比如:生成订单的过程的一些记录、某个账号活动的一些信息。这样一来每天生成的日志会很多,而且还会生成到数据库中。
像这些记录信息在某段时间过后就没用了。为了节省空间资源,和产品确认这些信息只保留3个月的。3个月之前的都可以删了。
1.2. 使用普通表记录这些数据
一般情况下大家都知道创建一些日志表存放这些数据,之后就在每个月初去删除三个月以前的日志记录(不管用手动人工执行删除或使用定时任务)。
这种方法看上去很可行的。当是,使用delete删除3月以前的数据,其实磁盘的空间是没有人减少的。懂得的人会去做Optimize、或者从新导入导出数据。可是像这种优化和导入导出的方法在数据量大的时候是很不可行的。因此,就会陷入尴尬的境地。
1.3. 使用partition table(分区表)
使用partition table的方法来存储这些数据就很有优势了。我们只要在每个月初创建下一个月的分区,在删除3月前的分区就好了。由于每个分区都是存储在不同的表空间文件中(这里使用的不是共享表空间模式)。所以在删除了分区之后空间会直接的释放出来。
提示:可以使用压缩比高的TokuDB引擎,该引擎的数据压缩会比原来的小5-10倍。
1.4. 使用普通表和partition table对比演示
1
2
|
--创建测试使用数据库
CREATE
DATABASE
test_partition
;
|
- 使用普通表的演示
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
|
USE
test_partition
;
--创建非
partition表
CREATE
TABLE
normal_table
(
id
BIGINT
NOT
NULL
AUTO_INCREMENT
,
name
VARCHAR
(
50
)
NOT
NULL
,
context
VARCHAR
(
50
)
NOT
NULL
,
create_time
DATETIME
NOT
NULL
,
PRIMARY
KEY
(
id
)
)
;
--构造和月份有关的数据
INSERT
INTO
normal_table
VALUES
(
NULL
,
REPEAT
(
'x'
,
50
)
,
REPEAT
(
'y'
,
50
)
,
'2016-01-01 00:00:00'
)
,
(
NULL
,
REPEAT
(
'x'
,
50
)
,
REPEAT
(
'y'
,
50
)
,
'2016-02-01 00:00:00'
)
,
(
NULL
,
REPEAT
(
'x'
,
50
)
,
REPEAT
(
'y'
,
50
)
,
'2016-03-01 00:00:00'
)
;
DROP
PROCEDURE
insert_normal_table
;
DELIMITER
//
CREATE
PROCEDURE
insert_normal_table
(
)
BEGIN
DECLARE
i
INT
;
SET
i
=
0
;
WHILE
i
<
22
DO
INSERT
INTO
normal_table
SELECT
NULL
,
name
,
context
,
create_time
FROM
normal_table
;
SET
i
=
i
+
1
;
END
WHILE
;
END
//
DELIMITER
;
CALL
insert_normal_table
(
)
;
|
查看normal_table表占用磁盘空间大小
1
2
3
|
ll
-
h
-
rw
-
r
--
--
-
1
mysql
mysql
8.5K
Apr
2
15
:
36
normal_table
.
frm
-
rw
-
r
--
--
-
1
mysql
mysql
1.8G
Apr
2
15
:
56
normal_table
.
ibd
|
上面可以看到表的数据是1.8G。这时候按需求我们删除前3月的数据(这里我们删除一月份的数据)
1
|
DELETE
FROM
normal_table
WHERE
create_time
<
'2016-02-01 00:00:00'
;
|
继续查看现在的磁盘空间
1
2
3
|
ll
-
h
-
rw
-
r
--
--
-
1
mysql
mysql
8.5K
Apr
2
15
:
36
normal_table
.
frm
-
rw
-
r
--
--
-
1
mysql
mysql
1.8G
Apr
2
16
:
01
normal_table
.
ibd
|
理想的情况下应该是normal_table.ibd文件的空间大小应该会减少才对,可是并不是想象的那样。它的空间大小还是不变的。
这时候你需要让空间稍稍的释放出来就需要使用优化表命令
1
2
3
4
|
OPTIMIZE
TABLE
normal_table
;
ll
-
h
-
rw
-
r
--
--
-
1
mysql
mysql
8.5K
Apr
2
16
:
35
normal_table
.
frm
-
rw
-
r
--
--
-
1
mysql
mysql
1.4G
Apr
2
16
:
36
normal_table
.
ibd
|
可以看到空间被释放出来了。
- 使用partition table
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
34
35
36
|
USE
test_partition
;
--创建
partition表
CREATE
TABLE
partition_table
(
id
BIGINT
NOT
NULL
AUTO_INCREMENT
,
name
VARCHAR
(
50
)
NOT
NULL
,
context
VARCHAR
(
50
)
NOT
NULL
,
create_time
INT
NOT
NULL
,
INDEX
idx
$
partition_table
$
id
(
id
)
)
PARTITION
BY
RANGE
(
create_time
)
(
PARTITION
p1
VALUES
LESS
THAN
(
UNIX_TIMESTAMP
(
'2016-01-31 23:59:59'
)
)
,
PARTITION
p2
VALUES
LESS
THAN
(
UNIX_TIMESTAMP
(
'2016-02-29 23:59:59'
)
)
,
PARTITION
p3
VALUES
LESS
THAN
(
UNIX_TIMESTAMP
(
'2016-03-31 23:59:59'
)
)
)
;
--创建数据
INSERT
INTO
partition_table
VALUES
(
NULL
,
REPEAT
(
'x'
,
50
)
,
REPEAT
(
'y'
,
50
)
,
UNIX_TIMESTAMP
(
'2016-01-01 00:00:00'
)
)
,
(
NULL
,
REPEAT
(
'x'
,
50
)
,
REPEAT
(
'y'
,
50
)
,
UNIX_TIMESTAMP
(
'2016-02-01 00:00:00'
)
)
,
(
NULL
,
REPEAT
(
'x'
,
50
)
,
REPEAT
(
'y'
,
50
)
,
UNIX_TIMESTAMP
(
'2016-03-01 00:00:00'
)
)
;
DROP
PROCEDURE
insert_partition_table
;
DELIMITER
//
CREATE
PROCEDURE
insert_partition_table
(
)
BEGIN
DECLARE
i
INT
;
SET
i
=
0
;
WHILE
i
<
22
DO
INSERT
INTO
partition_table
SELECT
NULL
,
name
,
context
,
create_time
FROM
partition_table
;
SET
i
=
i
+
1
;
END
WHILE
;
END
//
DELIMITER
;
CALL
insert_partition_table
(
)
;
|
查看使用磁盘的容量情况
1
2
3
4
5
|
ll
-
h
-
rw
-
r
--
--
-
1
mysql
mysql
8.5K
Apr
2
17
:
06
partition_table
.
frm
-
rw
-
r
--
--
-
1
mysql
mysql
700M
Apr
2
17
:
12
partition_table
#P#p1.ibd
-
rw
-
r
--
--
-
1
mysql
mysql
700M
Apr
2
17
:
12
partition_table
#P#p2.ibd
-
rw
-
r
--
--
-
1
mysql
mysql
700M
Apr
2
17
:
13
partition_table
#P#p3.ibd
|
上面我们可以看到分区表的结构是每一个分区有着自己的表空间。
现在我们删除1月份的数据,这里我们只需要删除p1分区就好。
1
2
3
4
5
6
7
8
9
10
11
12
|
--在删除分区前应该先创建下一个月的分区
ALTER
TABLE
partition_table
ADD
partition
(
partition
p4
VALUES
LESS
THAN
(
UNIX_TIMESTAMP
(
'2016-04-30 23:59:59'
)
)
)
;
--删除一月份的分区
ALTER
TABLE
partition_table
DROP
partition
p1
;
ll
-
h
-
rw
-
r
--
--
-
1
mysql
mysql
8.5K
Apr
2
17
:
20
partition_table
.
frm
-
rw
-
r
--
--
-
1
mysql
mysql
700M
Apr
2
17
:
12
partition_table
#P#p2.ibd
-
rw
-
r
--
--
-
1
mysql
mysql
700M
Apr
2
17
:
13
partition_table
#P#p3.ibd
-
rw
-
r
--
--
-
1
mysql
mysql
112K
Apr
2
17
:
26
partition_table
#P#p4.ibd
|
可以看到只要我们删除表空间就能直接的释放磁盘空间。
1.5. 总结
上面的演示可以看到在保存日志之类的数据使用partition是在合适不过的了,不仅能节省空间还很方便我们的维护。