以下论述均假定innodb_file_per_table开启
先用常规optimize回收
[plain]
<pre name=code class=plain>mysql> select count（*） from t;
++
| count（*） |
++
| |
++
[mysql@even employees]$ ls alh tibd
rwrw mysql dba M : tibd
mysql> delete from t limit ;
mysql> select count（*） from t;
++
| count（*） |
++
| |
++
[mysql@even employees]$ ls alh tibd
rwrw mysql dba M : tibd
mysql> optimize table t;
+++++
| Table | Op | Msg_type | Msg_text |
+++++
| employeest | optimize | note | Table does not support optimize doing recreate + analyze instead |
| employeest | optimize | status | OK |
+++++
[mysql@even employees]$ ls alh tibd
rwrw mysql dba M : tibd</pre><br><br>
来看下这种方法的缺陷
[plain]
mysql> show processlist;
+++++++++
| Id | User | Host | db | Command | Time | State | Info |
+++++++++
| | root | localhost | NULL | Query | | NULL | show processlist |
| | root | localhost | employees | Query | | copy to tmp table | optimize table t |
+++++++++
mysqladmin debug结果如下
[plain]
Thread databasetable_name Locked/Waiting Lock_type
employeest Locked read Read lock without concurrent inserts
用optimize缺点显而易见读锁特别当你有大表时性能恐怕会深受影响
这里推荐使用percona公司ptonlineschemachange避免加锁
[plain]
$ ptonlineschemachange uroot poracle alter ENGINE=InnoDB D=employeest=t execute
执行pt工具时有可能发生的类似错误
[plain]
Cannot chunk the original table There is no good index and the table is oversized
这是因为被作用的表需要含有主键或者唯一索引这或许也能成为这款工具的小bug吧
