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

阅读量：6947 次

发布时间：2019-06-27

本文共 12598 字，大约阅读时间需要 41 分钟。

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:42

mytable.frm

-rw-------

1 oracle oinstall

391466975232 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即为上条命令查到的

truncate有索引的表和没有索引的表速度一样吗？

我们程序的需求是从其他系统采集数据到我们系统中，然后再进行处理。

每天晚上凌晨先truncate掉我们系统中的表，然后采集当天的数据到数据库。采集方式是用ctl控制文件方法导出db文件再把db文件导入我们数据库的方法，不是直接insert。

采集到我们系统中的表有20张，数据量为1000W以上的大概有八张，我把这20张表称为原始表。我们用这20张原始表通过SQL查询语句与对应的业务需求做成10张报表（结果表）。

因为有几张表数据量很大，所以查询起来的时候速度很慢，所以想到了建立索引的方式来提高效率。

               SQL code 
                                                 
insert
into
结果表1 
select
col1,col2,col3... 
from
原始表1，原始表2 
where
XXX 
and
XXX....
                
              

上面是我们生成报表的的类似语句。

那么问题来了：

1，我所了解的索引一般是建立在不常变更的表上面的，但是我们的表每天都会被truncate，这样是不是会影响效率？truncate有索引和没索引的表速度一样么？

2，虽然我们的数据不是用insert到数据库的，导入数据会因为表有索引会对导入速度有影响么？顺便问下，如果insert到数据库，因为存在索引，是不是效率很低？

3，有没有什么其他好办法来提高效率。

回答：

1，truncate没索引的表快

2，索引会降低表导入速度

3，把文件放在在数据库服务器上用数据库自带工具导入（如果能保证逻辑正确，导入前删掉索引触发器主键之类的，导完之后在建索引触发器主键）

索引会导致插入数据变慢，tuncata估计影响不大，它将将表drop之后再重建的，关于楼上每次删除索引再加上我认为也不合适，因为你加索引的时候照样也是要费很多时间的

1 truncate 属于物理层的断开表结构文件与表数据文件的关系，有没有索引对该操作没有影响

2 索引会影响导入数据，建议先导入所有数据后再建立索引

1.最好是先干掉索引，再truncate table，然后重建索引

2.可以采用reuse storage方式，逐步回收extents

TRUNCATE table慢和表的STORAGE参数以及INDEX storage参数设置有关，看一下表和索引的DDL语句

truncate table后，oracle会回收表和其表中所在的索引到initial 大小，也就是初始分配的segments大小，你的附件中的initia都是256K，也不大，可能truncate时，oracle对表分区和local 索引回收工作导致，然后修改相应的位图上的标识等！

x先干掉索引，然后再turncate table ，最后加索引,估计好一点

MySQL删除数据Delete与Truncate语句使用比较

作者：字体：[增加减小] 类型：转载时间：2012-09-12

在MySQL数据库中，DELETE语句和TRUNCATE TABLE语句都可以用来删除数据，但是这两种语句还是有着其区别的，下文就为您介绍这二者的差别所在

空mysqll表内容常见的有两种方法：一种delete，一种是truncate 。不带where参数的delete语句可以删除mysql表中所有内容，使用truncate table也可以清空mysql表中所有内容。效率上truncate比delete快，但truncate删除后不记录mysql日志，不可以恢复数据。

其语法结构为：

复制代码代码如下:

TRUNCATE [TABLE] tbl_name

这里简单的给出个示例，

我想删除 friends 表中所有的记录，可以使用如下语句：

复制代码代码如下:

truncate table friends;

delete的效果有点像将mysql表中所有记录一条一条删除到删完，而truncate相当于保留mysql表的结构，重新创建了这个表，所有的状态都相当于新表。

语法：

delete语句的定义。

复制代码代码如下:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name  
[WHERE where_definition]  
[ORDER BY ...]  
[LIMIT row_count]  

MySQL的这些语法都和口语类似，你要指出你想从哪个表删除数据，还有删除哪些数据，这就够了。就像写记叙文的时候，时间、地点、人物、环境、情节几要素必不可少一样。

示例是最形象，最能说明问题的。所以，不整没用的了，直接进入主题。按照上面的语法结构，我想删除 firends 表中所有 user_name 等于 simaopig 的记录，

就可以使用如下SQL语句：

复制代码代码如下:

delete from friends where user_name = 'simaopig';delete

注意事项：

从语法结构中，我们就可以看出，和 update 语法一样，我们是可以省略 where 子句的。不过这是一个很危险的行为。因为如果不指定 where 子句，delete 将删除表中所有的记录，而且是立即删除，即使你想哭都没有地方，也没有时间，因为你需要马上和主管承认错误，并且立即找出MySQL日志，来回滚记录。不过一旦你有过一次这样的经历，我相信这一定是印象深刻的。

奇怪的是我在innodb上以及myisam上大数据量下，索引文件极大的情况下，几乎不可能删除成功！留待解决。

总结
不过truncate命令快规快，却不像delete命令那样对事务处理是安全的。因此，如果我们想要执行truncate删除的表正在进行事务处理，这个命令就会产生退出并产生错误信息。

Check DB Server环境.

1 察看内存以及缓存

top - 07:55:56 up 256 days, 4:53, 3 users, load average: 0.00, 0.09, 0.22

Tasks: 175 total, 1 running, 174 sleeping, 0 stopped, 0 zombie

Cpu0 : 2.2%us, 0.9%sy, 0.0%ni, 96.4%id, 0.4%wa, 0.0%hi, 0.0%si, 0.0%st

Cpu1 : 2.4%us, 1.1%sy, 0.0%ni, 96.0%id, 0.4%wa, 0.0%hi, 0.0%si, 0.0%st

Cpu2 : 2.3%us, 0.9%sy, 0.0%ni, 96.3%id, 0.4%wa, 0.0%hi, 0.0%si, 0.0%st

Cpu3 : 2.2%us, 0.9%sy, 0.0%ni, 96.4%id, 0.4%wa, 0.0%hi, 0.0%si, 0.0%st

Mem: 32750360k total, 32472008k used, 278352k free, 271932k buffers

Swap: 4194296k total, 505844k used, 3688452k free, 3554324k cached

2 察看系统版本

[novamysqladminlt@nucltmapc04db01 ~]$ cat /etc/issue

Red Hat Enterprise Linux Server release 6.3 (Santiago)

Kernel \r on an \m

3 察看操作系统位数

[xxxxx@xxxxxx~]$ ldd /sbin/mii-tool

linux-vdso.so.1 => (0x00007fff9b1ff000)

libselinux.so.1 => /lib64/libselinux.so.1 (0x0000003ec2400000)

libc.so.6 => /lib64/libc.so.6 (0x0000003ec0c00000)

libdl.so.2 => /lib64/libdl.so.2 (0x0000003ec0800000)

/lib64/ld-linux-x86-64.so.2 (0x0000003ec0400000)

4 察看磁盘

[xxx@xxx~]$ vmstat 1

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----

r b swpd free buff cache si so bi bo in cs us sy id wa st

0 0 505928 274840 272104 3558732 0 0 3 58 0 0 2 1 96 0 0

0 0 505928 274732 272104 3558772 0 0 0 112 1036 1438 1 1 98 0 0

1 2 505928 274732 272104 3558812 0 0 0 1796 960 1276 1 2 96 2 0

1 0 505928 269112 272104 3558836 0 0 0 5564 2005 1861 12 5 83 1 0

0 0 505928 274484 272104 3558864 0 0 0 112 1568 1969 11 2 87 0 0

0 0 505928 274484 272104 3558892 0 0 0 128 924 1221 1 2 97 0 0

1 0 505928 274468 272104 3558916 0 0 0 96 1563 1268 8 3 89 0 0

5 查看io资源

[xxx@xxx~]$ iostat -x 1

Linux 2.6.32-279.14.1.el6.x86_64 (nucltmapc04db01.abn-sjl.ea.com) 08/28/2013 _x86_64_ (4 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

2.30 0.00 0.98 0.44 0.02 96.26

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util

vda 0.04 10.93 0.16 12.11 23.96 465.30 39.87 0.07 5.51 1.98 2.42

dm-0 0.00 0.00 0.00 0.01 0.04 0.09 9.79 0.00 2.88 0.84 0.00

dm-1 0.00 0.00 0.02 0.03 0.19 0.26 8.00 0.00 10.82 0.36 0.00

dm-2 0.00 0.00 0.00 0.13 0.07 1.01 8.34 0.00 2.01 0.75 0.01

dm-3 0.00 0.00 0.00 0.35 0.04 2.81 8.08 0.01 16.10 0.31 0.01

dm-4 0.00 0.00 0.00 0.01 0.00 0.05 8.00 0.00 1.87 0.39 0.00

dm-5 0.00 0.00 0.00 0.05 0.00 0.43 8.00 0.00 2.52 0.33 0.00

dm-6 0.00 0.00 0.00 0.01 0.02 0.06 9.89 0.00 4.01 0.90 0.00

dm-7 0.00 0.00 0.00 0.00 0.00 0.00 7.96 0.00 0.61 0.56 0.00

dm-8 0.00 0.00 0.00 0.00 0.00 0.00 8.00 0.00 1.80 1.37 0.00

dm-9 0.00 0.00 0.00 0.13 0.00 1.02 8.00 0.00 1.89 0.72 0.01

dm-10 0.00 0.00 0.17 22.33 23.59 459.57 21.48 0.05 2.28 1.07 2.40

dm-11 0.00 0.00 0.00 0.00 0.00 0.00 8.00 0.00 1.37 0.77 0.00

dm-12 0.00 0.00 0.00 0.00 0.00 0.00 7.96 0.00 0.99 0.85 0.00

avg-cpu: %user %nice %system %iowait %steal %idle

1.26 0.00 1.26 0.00 0.00 97.47

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util

vda 0.00 17.00 1.00 7.00 8.00 192.00 25.00 0.01 0.75 0.38 0.30

dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-8 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-9 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-10 0.00 0.00 1.00 24.00 8.00 192.00 8.00 0.02 0.92 0.12 0.30

dm-11 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-12 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

avg-cpu: %user %nice %system %iowait %steal %idle

9.55 0.00 4.02 0.00 0.25 86.18

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util

vda 0.00 19.00 0.00 7.00 0.00 208.00 29.71 0.00 0.14 0.14 0.10

dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-8 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-9 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-10 0.00 0.00 0.00 26.00 0.00 208.00 8.00 0.01 0.27 0.04 0.10

dm-11 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-12 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

详解MySQL中DROP,TRUNCATE 和DELETE的区别

不同点:

1. truncate和 delete只删除数据不删除表的结构(定义)

drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的

存储过程/函数将保留,但是变为invalid状态.

2.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,

执行的时候将被触发.

truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发

trigger.

3.delete语句不影响表所占用的extent, 高水线(high watermark)保持原位置不动

显然drop语句将表所占用的空间全部释放

truncate 语句缺省情况下见空间释放到 minextents个 extent,除非使用reuse storage; truncate

会将高水线复位(回到最开始).

4.速度,一般来说: drop> truncate > delete

5.安全性:小心使用drop 和truncate,尤其没有备份的时候.否则哭都来不及.

使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大.

想删除表,当然用drop

想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还

是用delete.

如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据/

实际应用：

delete from table; //删除所有数据

truncate table; //将auto_increatement调制从0开始，实际就是从1开始

truncate和delete对于回滚的区别

2014-11-07 10:58:02

标签：

（1）delete在事务中可以回滚

mysql>select * from t;

+----+

| id |

+----+

| 1 |

| 2 |

+----+

2 rows inset (0.00 sec)

mysql>begin;

Query OK,0 rows affected (0.00 sec)

mysql>delete from t where id=2;

Query OK,1 row affected (0.04 sec)

mysql>select * from t;

+----+

| id |

+----+

| 1 |

+----+

1 row inset (0.00 sec)

mysql>rollback;

Query OK,0 rows affected (0.01 sec)

mysql>select * from t;

+----+

| id |

+----+

| 1 |

| 2 |

+----+

2 rows inset (0.00 sec)

（2）truncate在事务中不能回滚

mysql>begin;

Query OK,0 rows affected (0.00 sec)

mysql>truncate table t;

Query OK,0 rows affected (0.03 sec)

mysql>rollback;

Query OK,0 rows affected (0.00 sec)

mysql>select * from t;

Empty set(0.00 sec)

mysql>

转 MSSQL、MySQL 数据库删除大批量千万级百万级数据的优化

(2014-10-26 15:06:22)

标签：

SQL Server

上面删除1.6亿条记录，不能用Truncate（因为只是删除其中少部分数据）。

经过实验，每次删除400万条要花1.5 - 3小时，而且是越到后面越慢，正常的话，需要大约102个小时，大约4天半时间。这在生产环境下是不能接受的。

经过一个处理之后，我每次删除400万条记录花5 - 6分钟，删除全部1.6亿条记录花了4 - 5个小时！

为什么？？

每次删除记录，数据库都要相应地更新索引，这是很慢的IO操作，而且后面索引碎片越来越多，就更慢，这就是为什么一开始只花1.5小时，后面要3小时才能删除400万条记录的原因。

删除之前，做个完整备份。

我在删除前先保存当前索引的DDL，然后删除其索引，
然后根据使用的删除条件建立一个临时的索引（这是提高速度的另外一个重要原因！）
开始删除操作，完成之后再重建之前的索引。

如果需要保留的数据比较少的话，可以把要保留的数据备份出来。在drop表。重新创建，先不要急着创建索引、主键，把数据导回去，然后在建索引、约束之类的。

记得在删除的时候不要在记录日志的模式下面，否则日志文件就要爆了。

2、在

My SQL

数据库使用中，有的表存储数据量比较大，达到每天三百万条记录左右，此表中建立了三个索引，这些索引都是必须的，其他程序要使用。

由于要求此表中的数据只保留当天的数据，所以每当在凌晨的某一时刻当其他程序处理完其中的数据后要删除该表中昨天以及以前的数据，使用delete删除表中的上百万条记录时，MySQL删除速度非常缓慢每一万条记录需要大概4分钟左右，这样删除所有无用数据要达到八个小时以上，这是难以接受的。

查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的，于是删除掉其中的两个索引后测试，发现此时删除速度相当快，一百万条记录在一分钟多一些，可是这两个索引其他模块在每天一次的数据整理中还要使用，于是想到了一个折中的办法：

在删除数据之前删除这两个索引，此时需要三分钟多一些，然后删除其中无用数据，此过程需要不到两分钟，删除完成后重新创建索引，因为此时数据库中的数据相对较少，约三四十万条记录(此表中的数据每小时会增加约十万条)，创建索引也非常快，约十分钟左右。这样整个删除过程只需要约15分钟。对比之前的八个小时，大大节省了时间。