根据年删除大量数据
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
执行后空间情况
