分区表的使用

26/07/2016 00:30:56MySQL python1 12,337字数 3702阅读12分20秒阅读模式

1.1. 场景

在每个系统中都会生成一些日志，往往有些日志都是需要记录的，比如：生成订单的过程的一些记录、某个账号活动的一些信息。这样一来每天生成的日志会很多，而且还会生成到数据库中。

像这些记录信息在某段时间过后就没用了。为了节省空间资源，和产品确认这些信息只保留3个月的。3个月之前的都可以删了。文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

1.2. 使用普通表记录这些数据

一般情况下大家都知道创建一些日志表存放这些数据，之后就在每个月初去删除三个月以前的日志记录（不管用手动人工执行删除或使用定时任务）。文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

这种方法看上去很可行的。当是，使用delete删除3月以前的数据，其实磁盘的空间是没有人减少的。懂得的人会去做Optimize、或者从新导入导出数据。可是像这种优化和导入导出的方法在数据量大的时候是很不可行的。因此，就会陷入尴尬的境地。文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

1.3. 使用partition table（分区表）

使用partition table的方法来存储这些数据就很有优势了。我们只要在每个月初创建下一个月的分区，在删除3月前的分区就好了。由于每个分区都是存储在不同的表空间文件中(这里使用的不是共享表空间模式)。所以在删除了分区之后空间会直接的释放出来。文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

提示：可以使用压缩比高的TokuDB引擎，该引擎的数据压缩会比原来的小5-10倍。文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

1.4. 使用普通表和partition table对比演示

-- 创建测试使用数据库
CREATE DATABASE test_partition;

使用普通表的演示

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表占用磁盘空间大小文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

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月的数据（这里我们删除一月份的数据）文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

DELETE FROM normal_table WHERE create_time < '2016-02-01 00:00:00';

继续查看现在的磁盘空间文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

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文件的空间大小应该会减少才对，可是并不是想象的那样。它的空间大小还是不变的。文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

这时候你需要让空间稍稍的释放出来就需要使用优化表命令文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

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

可以看到空间被释放出来了。文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

使用partition table

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();

查看使用磁盘的容量情况文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

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

上面我们可以看到分区表的结构是每一个分区有着自己的表空间。文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

现在我们删除1月份的数据，这里我们只需要删除p1分区就好。文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

-- 在删除分区前应该先创建下一个月的分区
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

可以看到只要我们删除表空间就能直接的释放磁盘空间。文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

1.5. 总结

上面的演示可以看到在保存日志之类的数据使用partition是在合适不过的了，不仅能节省空间还很方便我们的维护。文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

昵称：HH
QQ：275258836
ttlsa群交流沟通(QQ群②：6690706 QQ群③：168085569 QQ群④：415230207（新）微信公众号：ttlsacom)文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

感觉本文内容不错，读后有收获？文章源自运维生存时间-https://www.ttlsa.com/mysql/partition-table-use-example/

逛逛衣服店，鼓励作者写出更好文章。

我的微信

微信公众号

扫一扫关注运维生存时间公众号，获取最新技术文章~

分区表的使用

1.1. 场景

1.2. 使用普通表记录这些数据

1.3. 使用partition table（分区表）

1.4. 使用普通表和partition table对比演示

1.5. 总结

kingshard: Go语言开发的高性能MySQL Proxy

MySQL管理工具MySQL Utilities — 搜索数据库对象

一台主机部署多个mysqld实例配置

MySQL基线测试