bitsCN.com
回收innodb表空间
以下论述均假定innodb_file_per_table开启
先用常规optimize回收：
[plain]
mysql> select count(*) from t; </p><p>+----------+ </p><p>| count(*) | </p><p>+----------+ </p><p>| 1200096 | </p><p>+----------+ </p><p> </p><p>[mysql@even employees]$ ls -alh t.ibd </p><p>-rw-rw---- 1 mysql dba 72M 10-08 17:39 t.ibd </p><p> </p><p>mysql> delete from t limit 800000; </p><p> </p><p>mysql> select count(*) from t; </p><p>+----------+ </p><p>| count(*) | </p><p>+----------+ </p><p>| 400096 | </p><p>+----------+ </p><p> </p><p>[mysql@even employees]$ ls -alh t.ibd </p><p>-rw-rw---- 1 mysql dba 72M 10-08 17:41 t.ibd </p><p> </p><p> </p><p>mysql> optimize table t; </p><p>+-------------+----------+----------+-------------------------------------------------------------------+ </p><p>| Table | Op | Msg_type | Msg_text | </p><p>+-------------+----------+----------+-------------------------------------------------------------------+ </p><p>| employees.t | optimize | note | Table does not support optimize, doing recreate + analyze instead | </p><div class="aritcle_card flexRow">
<div class="artcardd flexRow">
<a class="aritcle_card_img" href="/xiazai/code/10225" title="小生淘宝客程序打折程序"><img
src="https://img.php.cn/upload/webcode/000/000/019/176114160648406.jpg" alt="小生淘宝客程序打折程序" onerror="this.onerror='';this.src='/static/lhimages/moren/morentu.png'" ></a>
<div class="aritcle_card_info flexColumn">
<a href="/xiazai/code/10225" title="小生淘宝客程序打折程序">小生淘宝客程序打折程序</a>
<p>淘宝客打折系统，集成了jssdk模块，增加了seo优化功能，更有利于搜索引擎收录 1程序上传到服务器空间 2开启服务器 3打开安装地址：http://您的域名/install.php 4如果不能安装请确保数据库里的表全部删除 5进入后台地址：http://您的域名/main.php 默认用户名和密码都是admin 6测试数据时可以导入 test文件夹里的test.sql文件 到数据库，或者</p>
</div>
<a href="/xiazai/code/10225" title="小生淘宝客程序打折程序" class="aritcle_card_btn flexRow flexcenter"><b></b><span>下载</span> </a>
</div>
</div><p>| employees.t | optimize | status | OK | </p><p>+-------------+----------+----------+-------------------------------------------------------------------+ </p><p> </p><p>[mysql@even employees]$ ls -alh t.ibd </p><p>-rw-rw---- 1 mysql dba 29M 10-08 17:43 t.ibd
来看下这种方法的缺陷
[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吧
bitsCN.com
