mysql大量删除数据后表空间处理

根据年删除大量数据

复制1.rpt_analyte_item表
select * from rpt_analyte_item where YEAR(createTime) like '2018%' limit 10,5
select count(*) from rpt_analyte_item where YEAR(createTime) like '2018%';
delete from rpt_analyte_item where YEAR(createTime) like '2018%';
select count(*) from rpt_analyte_item where YEAR(createTime) like '2019%';
delete from rpt_analyte_item where YEAR(createTime) like '2019%';
2.sys_results表
select count(*) from sys_results where id <17000000;
delete  from sys_results where id <17000000;

复制mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lims-prod          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use lims-prod;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from rpt_analyte_item where YEAR(createTime) like '2018%';
+----------+
| count(*) |
+----------+
|   675259 |
+----------+
1 row in set (10.02 sec)

mysql> delete from rpt_analyte_item where YEAR(createTime) like '2018%';
Query OK, 675259 rows affected (1 min 6.70 sec)

mysql> select count(*) from rpt_analyte_item where YEAR(createTime) like '2019%';
+----------+
| count(*) |
+----------+
|  9019714 |
+----------+
1 row in set (15.22 sec)

mysql> delete from rpt_analyte_item where YEAR(createTime) like '2019%';
Query OK, 9019714 rows affected (9 min 31.89 sec)

mysql> select count(*) from sys_results where id <17000000;
+----------+
| count(*) |
+----------+
|  9839370 |
+----------+
1 row in set (2 min 44.05 sec)

mysql> delete  from sys_results where id <17000000;
Query OK, 9839370 rows affected (20 min 57.12 sec)

空间释放处理

复制select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
order by data_size DESC;

复制mysql> alter table rpt_analyte_item engine=innodb;
Query OK, 0 rows affected (1 min 42.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sys_results engine=innodb;
Query OK, 0 rows affected (3 min 1.72 sec)
Records: 0  Duplicates: 0  Warnings: 0

执行后空间情况