来源：自学PHP网 时间：2015-04-16 10:51 作者： 阅读:次
[导读] 回收InnoDB表空间以下论述均假定innodb_file_per_table开启先用常规optimize回收：[plain]pre name=code class=plainmysql select count(*) from t; +----------+ | count(*) | +----------+...
|
回收InnoDB表空间
以下论述均假定innodb_file_per_table开启
先用常规optimize回收：
[plain]
<pre name="code" class="plain">mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 1200096 |
+----------+
[mysql@even employees]$ ls -alh t.ibd
-rw-rw---- 1 mysql dba 72M 10-08 17:39 t.ibd
mysql> delete from t limit 800000;
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 400096 |
+----------+
[mysql@even employees]$ ls -alh t.ibd
-rw-rw---- 1 mysql dba 72M 10-08 17:41 t.ibd
mysql> optimize table t;
+-------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+----------+----------+-------------------------------------------------------------------+
| employees.t | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| employees.t | optimize | status | OK |
+-------------+----------+----------+-------------------------------------------------------------------+
[mysql@even employees]$ ls -alh t.ibd
-rw-rw---- 1 mysql dba 29M 10-08 17:43 t.ibd</pre><br><br>
来看下这种方法的缺陷
[plain]
mysql> show processlist;
+----+------+-----------+-----------+---------+------+-------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-----------+---------+------+-------------------+------------------+
| 5 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 6 | root | localhost | employees | Query | 64 | copy to tmp table | optimize table t |
+----+------+-----------+-----------+---------+------+-------------------+------------------+
mysqladmin debug结果如下
[plain]
Thread database.table_name Locked/Waiting Lock_type
6 employees.t Locked - read Read lock without concurrent inserts
用optimize缺点显而易见：读锁，特别当你有大表时性能恐怕会深受影响
这里推荐使用percona公司：pt-online-schema-change，避免加锁
[plain]
$ pt-online-schema-change -uroot -poracle --alter "ENGINE=InnoDB" D=employees,t=t --execute
执行pt工具时有可能发生的类似错误：
[plain]
Cannot chunk the original table There is no good index and the table is oversized
这是因为被作用的表需要含有主键或者唯一索引，这或许也能成为这款工具的小bug吧
|
自学PHP网专注网站建设学习,PHP程序学习,平面设计学习，以及操作系统学习
京ICP备14009008号-1@版权所有www.zixuephp.com
网站声明：本站所有视频，教程都由网友上传，站长收集和分享给大家学习使用，如由牵扯版权问题请联系站长邮箱904561283@qq.com
