MYSQL磁盘整理实践-京东云开发者社区

### 1 磁盘整理背景

- **结转产生空白空间**：结转数据中，大量delete 操作的同时会产生空白空间。
- **空白空间无法复用**：空白空间在插入操作中无法彻底复用，造成数据存储位置不连续。物理存储顺序与理论排序顺序不同，久而久之就产生大量磁盘碎片。
- **传统清理磁盘碎片**：采用ALTER TABLE XXXX ENGINE = INNODB 语句进行清理，该操作会暂时锁住表,而且数据量越大,耗费的时间也越长。
- **分区表清理碎片**：将表结构提前进行分区规划或上云过程中重组表结构，在清理过程中无性能抖动、平滑、且工作量小。

### 2 磁盘整理比对

![](//img1.jcloudcs.com/developer.jdcloud.com/fddc86fd-9c0b-455e-a8dd-6e1b4901080020220216134951.png)

### 3 分区表磁盘整理效果

2021-10-25 15：21：00对已结转数据分区执行drop操作，释放碎片空间

![](//img1.jcloudcs.com/developer.jdcloud.com/40afa7b0-175f-4974-8d66-b11aa3a7b75620220216135004.png)

同时段库存操作ump性能平稳，无明显波动

![](//img1.jcloudcs.com/developer.jdcloud.com/bef003e0-05a8-4470-87f0-ee6a3d0cb2b920220216135508.png)

### 4 存量表改造为分区表实现路径

主从切换：新增从实例，从实例改造成分区表，进行主从切换。
采用重命名方式切换：例如将表warehouse_stock_flow表重命名处理

- 新建分区表：warehouse_stock_flow_new（新建成分区表）

```
CREATE TABLE `warehouse_stock_flow_new` (
	`id` BIGINT (20) NOT NULL AUTO_INCREMENT COMMENT '主键',
	`biz_no` VARCHAR (200) NOT NULL COMMENT '单号',
	`biz_type` INT (11) NOT NULL COMMENT '业务类型',
	`stock_type` INT (11) NOT NULL COMMENT '库存类型',
	`seller_no` VARCHAR (100) NOT NULL COMMENT '商家编号',
	`dept_no` VARCHAR (100) NOT NULL COMMENT '事业部编号',
	`goods_no` VARCHAR (100) NOT NULL COMMENT '商品编码',
	`create_time` datetime NOT NULL COMMENT '创建时间',
	`update_time` datetime DEFAULT NULL COMMENT '更新时间',
    --省略
	PRIMARY KEY (`id`,`create_time`),
	KEY `idx_create_time` (`create_time`) USING BTREE,
	KEY `idx_biz_no` (`biz_no`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 5229508096 DEFAULT CHARSET = utf8 COMMENT = '库存流水表' PARTITION BY RANGE COLUMNS (create_time)(
	PARTITION p0
	VALUES
		LESS THAN ('2020-06-01') ENGINE = INNODB,
		PARTITION p1
	VALUES
		LESS THAN ('2020-08-01') ENGINE = INNODB,
		PARTITION p2
	VALUES
		LESS THAN ('2020-10-01') ENGINE = INNODB,
		PARTITION p3
	VALUES
		LESS THAN ('2020-12-01') ENGINE = INNODB,
		PARTITION p4
	VALUES
		LESS THAN ('2021-02-01') ENGINE = INNODB,
		PARTITION p5
	VALUES
		LESS THAN ('2021-04-01') ENGINE = INNODB,
		PARTITION p6
	VALUES
		LESS THAN ('2021-06-01') ENGINE = INNODB,
		PARTITION p7
	VALUES
		LESS THAN ('2021-08-01') ENGINE = INNODB,
		PARTITION p8
	VALUES
		LESS THAN ('2021-10-01') ENGINE = INNODB,
		PARTITION p9
	VALUES
		LESS THAN ('2021-12-01') ENGINE = INNODB,
		PARTITION p10
	VALUES
		LESS THAN ('2022-02-01') ENGINE = INNODB,
		PARTITION p11
	VALUES
		LESS THAN ('2022-04-01') ENGINE = INNODB,
		PARTITION p12
	VALUES
		LESS THAN ('2022-06-01') ENGINE = INNODB,
		PARTITION p13
	VALUES
		LESS THAN ('2022-08-01') ENGINE = INNODB,
		PARTITION p14
	VALUES
		LESS THAN ('2022-10-01') ENGINE = INNODB,
		PARTITION p15
	VALUES
		LESS THAN ('2022-12-01') ENGINE = INNODB,
		PARTITION p16
	VALUES
		LESS THAN ('2023-02-01') ENGINE = INNODB,
		PARTITION p17
	VALUES
		LESS THAN ('2023-04-01') ENGINE = INNODB,
		PARTITION p18
	VALUES
		LESS THAN ('2023-06-01') ENGINE = INNODB,
		PARTITION p19
	VALUES
		LESS THAN ('2023-08-01') ENGINE = INNODB,
		PARTITION p20
	VALUES
		LESS THAN ('2023-10-01') ENGINE = INNODB,
		PARTITION p21
	VALUES
		LESS THAN ('2023-12-01') ENGINE = INNODB,
		PARTITION p22
	VALUES
		LESS THAN ('2024-02-01') ENGINE = INNODB,
		PARTITION p23
	VALUES
		LESS THAN ('2024-04-01') ENGINE = INNODB,
		PARTITION p24
	VALUES
		LESS THAN ('2024-06-01') ENGINE = INNODB,
		PARTITION p25
	VALUES
		LESS THAN ('2024-08-01') ENGINE = INNODB,
		PARTITION p26
	VALUES
		LESS THAN ('2024-10-01') ENGINE = INNODB,
		PARTITION p27
	VALUES
		LESS THAN ('2024-12-01') ENGINE = INNODB,
		PARTITION p28
	VALUES
		LESS THAN ('2025-02-01') ENGINE = INNODB,
		PARTITION p29
	VALUES
		LESS THAN ('2025-04-01') ENGINE = INNODB,
		PARTITION p30
	VALUES
		LESS THAN ('2025-06-01') ENGINE = INNODB,
		PARTITION p31
	VALUES
		LESS THAN ('2025-08-01') ENGINE = INNODB,
		PARTITION p32
	VALUES
		LESS THAN ('2025-10-01') ENGINE = INNODB,
		PARTITION p33
	VALUES
		LESS THAN ('2025-12-01') ENGINE = INNODB
) 
```

- 执行重命名命令：将原始表warehouse_stock_flow重命名为warehouse_stock_flow_his，将新表warehouse_stock_flow_new重命名为warehouse_stock_flow即:

```sql
RENAME TABLE warehouse_stock_flow TO warehouse_stock_flow_his;
RENAME TABLE warehouse_stock_flow_new TO warehouse_stock_flow
```

- 大数据平台抽数任务：warehouse_stock_flow_his和warehouse_stock_flow抽取并集数据。
- 业务层：研发需结合实际业务，兼容整改。
- 加快结转warehouse_stock_flow_his数据，将其drop掉释放碎片空间。

### 5 总结

数据量大的业务表应设计成分区表方便磁盘碎片整理，降低维护成本和业务影响；历史存量大表可以通过重名方式或者新增从实例，从实例设计成分区表，进行主从切换改造成分区表；在日常运维工作中，应对碎片进行定期清理，保证数据库有稳定的性能和充足的空间。

------------

###### 自猿其说Tech-京东物流技术发展部
###### 作者：李佳华