truncate/drop表非常慢，怎么办？用硬链接，极速体验

     这个这个，我必须花巨大篇幅，记录下今天清空表记录的英雄壮举，可知道一个drop操作，执行了一下午啊一下午，这是要急出翔的节奏。。呵呵，下面开始

 

我的需求：某表因历史原因，积压了1亿条记录，约占360G空间。我要清掉它，就是这么简单。

尝试1：作为DB小菜，首先想到的，当然是delete命令。于是欢快的执行了delete from mytable; 知道吗？一杯茶都喝完了，它还没有执行完。我的尊严受到了挑战，捉急了，开始google。

尝试2：好，换用truncate命令。truncate table mytable; 知道吗？第二坏茶喝完了，它还没有执行完。快急出翔了，继续google。

尝试3：好，干脆drop表好了。drop table mytable; 知道吗？第三杯茶喝完了，它还没有执行完。这下快吓尿了，这是什么情况。。。赶快找大牛问

          当然，drop之前别忘了先备份一下表结构，一会儿drop完了还得重新建表，那得多麻烦呀，嘿嘿

          create table mytable_bak like mytable; // 备份

          drop table mytable;                      // 删表 

 

          alter table mytable_bak rename to mytable;  // 重新命名

尝试4：大牛说，不妨改一下这两个开关，可以加速drop。于是，又等了10min，窗外雨都下停了，还是没有执行完。。。

        show variables like '%lazy%';

        show variables like '%file_per%';

        set  global innodb_lazy_drop_table=1;  // 默认值是0

         set global innodb_file_per_table=OFF; //默认值是ON

尝试5：又找了另一位大牛，这下得解救了，翻身农奴了，拜啊，三柱香，牛！想知道是怎么做的吗？我知道你想，嘻嘻，别着急，是这样的，建硬链接

          在DB server上，找到mytable表对应的文件，我的是/data/mysql3306/data/mydatabase/。

          在这个目录下，我们可以看到以下记录，真的是390G！吓死银呀！ 

          -rw------- 1 oracle oinstall  46672 Aug 30 15:42mytable.frm 

          -rw------- 1 oracle oinstall391466975232 Aug 30 15:42 mytable.ibd 

          以上记录中，1表示该文件只有一个链接（没有另外的人链接到它，要删就是真的删文件本身了哦），怪不得我执行truncate/drop这么慢，原来背后就是在删这个东东呀！

          那怎么办呢？能不能绕过，不删文件本身，先快速把表drop掉该多好呀。那么建硬链接ln可以完成。

         ln mytable.ibd  mytable.ibd.h

         ln mytable.frm  mytable.frm.h

         相当于一个文件被两个索引链接着，要删就是只删链接，而不是删文件本身了，直到只有一个人链接它，才会是真的删呢。

          这时再执行drop表的动作，别提多快了，oh my god，快到惊人，mytable.ibd瞬间它不见了，不见了！

          最后别忘了把那个大大的“真文件”手工删删掉 

         rm -f mytable.ibd.h

         rm -f mytable.frm.h

 

          所以，同学们，乡亲们，最最亲爱的屌丝们，我痛完了，也絮叨完了，希望你疼的时候能看到这篇博客，帮你节约哪怕一杯茶的时间，也值了。共勉！

 

 

 

 

         正题说完了，说点题外话，一些mysql常用命令：

【1】如何查看表记录数、所占空间等。这个在巨表面前，用select count(*) from mytable 神马的都弱爆了，用这个：

root@information_schema 04:24:23>SELECT TABLE_NAME,TABLE_ROWS,DATA_LENGTH FROM TABLES WHERE TABLE_SCHEMA='mydatabase' AND TABLE_NAME='mytable';

【2】如何查看mysql连接情况，哪些用户连着，分别用了多少连接数

root@information_schema 01:31:12>select substr(host,1,locate(':',host)-1),user, count(*) from processlist group by substr(host,1,locate(':',host)-1), user order by count(*) desc, host desc;

【3】如何断开这些连接呢？

root@(none) 01:26:08>show processlist;  // 看到相关连接的id

root@(none) 01:26:08>kill id;                   // 断掉连接，id即为上条命令查到的