在搞mha的时候，看到了一个介绍linux上使用硬连接快速删除大文件的方法，以前还真没注意过。测试如下：
在创建测试表的时候，遇到了下面的错误
mysql> insert into test select * from test;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
If you’re running an operation on a large number of rows within a table that uses the InnoDB storage engine, you might see this error:
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
MySQL is trying to tell you that it doesn’t have enough room to store all of the row locks that it would need to execute your query. The only way to fix it for sure is to adjust innodb_buffer_pool_size and restart MySQL. By default, this is set to only 8MB, which is too small for anyone who is using InnoDB to do anything.
If you need a temporary workaround, reduce the amount of rows you’re manipulating in one query. For example, if you need to delete a million rows from a table, try to delete the records in chunks of 50,000 or 100,000 rows. If you’re inserting many rows, try to insert portions of the data at a single time
from:http://blog.chinaunix.net/uid-25266990-id-3293445.html
这个错误会导致事务回滚。我的饿测试环境是事务非自动提交，第二次创建测试表的时候在创建测试表的时候，每次insert后都commit下，没有在出现上面的报错，但是发现commit随着buffer中脏数据的数量增大而变慢（mysql中所有的操作写入log buffer，不会后台自动刷新，数量超过log buffer后写入临时表中，commit的时候会将所有的log写入磁盘），这个跟oracle不同，oracle中commit的速度是个很快的操作，跟数据量无关。
mysql> insert into test select * from test;
Query OK, 5193728 rows affected (1 min 12.79 sec)
Records: 5193728 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (9.93 sec)
mysql> insert into test select * from test;
Query OK, 10387456 rows affected (2 min 19.96 sec)
Records: 10387456 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (11.56 sec)
mysql> insert into test select * from test;
Query OK, 20774912 rows affected (4 min 25.23 sec)
Records: 20774912 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (19.37 sec)
创建了2个相同数据量大小的表，对比测试下：
ll
-rw-rw—-. 1 my3333 mysql 17702 May 15 16:51 test2.frm
-rw-rw—-. 1 my3333 mysql 32585547776 May 15 17:23 test2.ibd
-rw-rw—-. 1 my3333 mysql 17702 May 15 15:53 test.frm
-rw-rw—-. 1 my3333 mysql 32585547776 May 15 16:40 test.ibd
mysql> drop table test;
Query OK, 0 rows affected (1.85 sec)
[root@hz-10-200-142-43 test]# ln test2.ibd test2.idbbak
[root@hz-10-200-142-43 test]# ll
-rw-rw—-. 1 my3333 mysql 17702 May 15 16:51 test2.frm
-rw-rw—-. 2 my3333 mysql 32585547776 May 15 17:23 test2.ibd
-rw-rw—-. 2 my3333 mysql 32585547776 May 15 17:23 test2.idbbak
mysql> drop table test2;
Query OK, 0 rows affected (1.08 sec)
看到使用硬链接的方式删除，时间上是少了0.8s。