分区表的使用
亿网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是在合适不过的了，不仅能节省空间还很方便我们的维护。
文章转载来自：trustauth.cn