mysql mariadb 删除表中的数据时数据库变大

删除表中数据以前

[root@RM uar3]# du -sh *
3.3G    apache-tomcat-7.0.54
150M    instalRM4UAR
0       mariadb
903M    mariadb-5.5.33a-linux-x86_64
64G     mariadb-data
4.2G    realmonitor
8.0K    rm_netelement_s1u.sql
40K     switchFlowReportWeb
0       tomcat

删除表数据

delete from nagios_statehistory where state_time < '2017-01-01 00:00:00';
delete from alarm_info where alarm_time < '2017-01-01 00:00:00';
delete from switchflowout where CreateTime < '1483200000';
delete from switchflowin where CreateTime < '1483200000';

删除表数据后

[root@RM uar3]# du -sh *
3.3G    apache-tomcat-7.0.54
150M    instalRM4UAR
0       mariadb
903M    mariadb-5.5.33a-linux-x86_64
70G     mariadb-data
4.2G    realmonitor
8.0K    rm_netelement_s1u.sql
40K     switchFlowReportWeb
0       tomcat

DELETE只是将数据标识位删除，并没有整理数据文件，当插入新数据后，会再次使用这些被置为删除标识的记录空间。
最简单的：optimize table phpernote_article;对表进行优化
优化时出现下面问题

mysql> optimize table nagios_statehistory;
+---------------------------------+----------+----------+----------------------------------------------------------------------------------------------------+
| Table                           | Op       | Msg_type | Msg_text                                                                                           |
+---------------------------------+----------+----------+----------------------------------------------------------------------------------------------------+
| realmonitor.nagios_statehistory | optimize | Error    | Table './realmonitor/nagios_statehistory' is marked as crashed and last (automatic?) repair failed |
| realmonitor.nagios_statehistory | optimize | Error    | Table 'nagios_statehistory' is marked as crashed and last (automatic?) repair failed               |
| realmonitor.nagios_statehistory | optimize | error    | Corrupt                                                                                            |
+---------------------------------+----------+----------+----------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

修复数据表操作：
停止数据库

[root@RM uar3]# /etc/init.d/mariadb stop
Shutting down MySQL..
1、service mysqld stop;
2、cd /var/lib/mysql/db_name/
3、myisamchk -r tablename.MYI (修复单张数据表)
myisamchk -r *.MYI (修复所有数据表)

[root@RM realmonitor]# myisamchk -r nagios_statehistory.MYI
- recovering (with sort) MyISAM-table 'nagios_statehistory.MYI'
Data records: 807753
- Fixing index 1
Data records: 1042062
[root@RM realmonitor]# /etc/init.d/mariadb start
Starting MySQL...                                          [  OK  ]
mysql> optimize table nagios_statehistory;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: realmonitor

+---------------------------------+----------+----------+----------+
| Table                           | Op       | Msg_type | Msg_text |
+---------------------------------+----------+----------+----------+
| realmonitor.nagios_statehistory | optimize | status   | OK       |
+---------------------------------+----------+----------+----------+
1 row in set (3.64 sec)

　　

　　

　　

　　

　　

转载于:https://www.cnblogs.com/idvcn/p/8556739.html