before

必要的准备

首先准备一张表，搞一些测试数据：

sql

-- MYSQL version 5.7.20

DROP DATABASE IF EXISTS pp;
CREATE DATABASE pp CHARSET=utf8mb4 collate utf8mb4_bin;
USE pp;
-- SET AUTOCOMMIT=0;

DROP TABLE IF EXISTS pressure;

CREATE TABLE pressure(
id INT,
num INT,
k1 CHAR(2),
k2 CHAR(4),
dt TIMESTAMP
);

DELIMITER //
CREATE PROCEDURE rand_data(IN num INT)
BEGIN
DECLARE str CHAR(62) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE str2 CHAR(2);
DECLARE str4 CHAR(4);
DECLARE i INT DEFAULT 0;
WHILE i<num DO
SET str2=CONCAT(SUBSTRING(str,1+FLOOR(RAND()*61),1),SUBSTRING(str,1+FLOOR(RAND()*61),1));
SET str4=CONCAT(SUBSTRING(str,1+FLOOR(RAND()*61),2),SUBSTRING(str,1+FLOOR(RAND()*61),2));
SET i=i+1;
INSERT INTO pressure VALUES (i,FLOOR(RAND()*num),str2,str4,NOW());
END WHILE;
END //
DELIMITER ;

call rand_data(1000000);
commit;

创建数据需要一些时间，那么创建成功，此时的数据表应该有：

sql

[pp]>select count(*) from pressure;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.35 sec)

那么这一百万条数据的磁盘占用情况：

sql

[pp]>select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') as '磁盘占用' from information_schema.tables where table_schema='pp' AND table_name='pressure';
+----------+
| 磁盘占用 |
+----------+
| 45.58M   |
+----------+
1 row in set (0.00 sec)

删除一半的数据：

delete from pp.pressure where id > 500000;

我们再来看磁盘占用：

sql

[pp]>select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') as '磁盘占用' from information_schema.tables where table_schema='pp' AND table_name='pressure';
+----------+
| 磁盘占用 |
+----------+
| 24.56M   |
+----------+
1 row in set (0.00 sec)

before ​

必要的准备 ​

before

必要的准备