回收InnoDB表空间_MySQL-mysql教程-PHP中文网

回收InnoDB表空间_MySQL

php中文网

发布： 2016-06-01 13:29:16

原创

1176人浏览过

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">
                        <a class="aritcle_card_img" href="/xiazai/code/10225">
                            <img src="https://img.php.cn/upload/webcode/000/000/019/176114160648406.jpg" alt="小生淘宝客程序打折程序">
                        </a>
                        <div class="aritcle_card_info">
                            <a href="/xiazai/code/10225">小生淘宝客程序打折程序</a>
                            <p>淘宝客打折系统，集成了jssdk模块，增加了seo优化功能，更有利于搜索引擎收录 1程序上传到服务器空间 2开启服务器 3打开安装地址：http://您的域名/install.php 4如果不能安装请确保数据库里的表全部删除 5进入后台地址：http://您的域名/main.php   默认用户名和密码都是admin   6测试数据时可以导入 test文件夹里的test.sql文件 到数据库，或者</p>
                            <div class="">
                                <img src="/static/images/card_xiazai.png" alt="小生淘宝客程序打折程序">
                                <span>0</span>
                            </div>
                        </div>
                        <a href="/xiazai/code/10225" class="aritcle_card_btn">
                            <span>查看详情</span>
                            <img src="/static/images/cardxiayige-3.png" alt="小生淘宝客程序打折程序">
                        </a>
                    </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;

+----+------+-----------+-----------+---------+------+-------------------+------------------+

+----+------+-----------+-----------+---------+------+-------------------+------------------+

+----+------+-----------+-----------+---------+------+-------------------+------------------+

mysqladmin debug结果如下

[plain]

Thread database.table_name Locked/Waiting Lock_type

6 employees.t Locked - read Read lock without concurrent inserts

用optimize缺点显而易见：读锁，特别当你有大表时性能恐怕会深受影响