InnoDB delete from xxx速度暴慢原因

step1,一个简单的联系人表

CREATE TABLE `contact784` (
    `cid` bigint AUTO_INCREMENT NOT NULL,
    `uid` bigint NOT NULL,
    `email` varchar(128) NOT NULL,
    `name` varchar(64) NOT NULL,
    `mobile` varchar(16)  NULL,
    `atime` timestamp NULL,
    `type` enum('BLACK','WHITE','NORMAL') NOT NULl default 'NORMAL',
    `info` text NULL,
    `memo` varchar(1024)  NULL,
     PRIMARY key(`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT = 100;
ALTER TABLE `contact784` ADD UNIQUE INDEX uniq_uid_email(`uid`,`email`);

step2,插入了100W数据：

# -*- coding: utf-8 -*-  
#@author [e-mail]

import MySQLdb
import random
import string
import threading
import time

domains = ['org','com.cn','qq.com','yahoo.com','163.com','com','cn','sina.cn','sina.com']
host = "localhost"
user = "xx"
pwd = "xx"
db = "t3"

def getRandomValue():
  email = ""
  s = ""
  for x in range(random.randint(1,10)):
    s += random.choice(string.letters)
  b = list(s)
  domain = ''.join(b)+"."+random.choice(domains)
  email = s+"@"+domain
  return email,s


def insert(count):
  conn=MySQLdb.connect(host=host,user=user,passwd=pwd,db=db) 
  cursor=conn.cursor()
  for cid in xrange(count):
    uid = random.randint(1000000000,9999999999)
    email,name = getRandomValue()
    sql = "insert into contact784(uid,email,name) values (%d,'%s', '%s')" %(uid,email,name)
    n=cursor.execute(sql) 
  cursor.close()
  conn.commit ()
  conn.close()


if __name__=='__main__':

  start = time.clock()
  for i in range(100):
    worker = threading.Thread(target = insert(10000))
    worker.start()
  end = time.clock()
  print "elsaped:%s" %(end-start)

step3,要重新单线程插入，需要把数据清空.
因为python多线程由于GIL的关系，实际上上面的100个线程只产生了一个连接，需要测试一下纯单线程插入是不是要快些:)

执行：delete from contact784
半小时没有执行完毕！

诊断方式：
1，iostat ，top等查看磁盘io很大
2，inotifywatch发现io的事件非常多

原因：在大表上使用delete from 清空一个表是非常慢的。因为InnoDB必须处理表中的每一行，根据InnoDB的事务设计原则，首先需要把“删除动作”写入“事务日志”，然后写入实际的表。所以，清空大表的时候，最好直接drop table然后重建。
注:
在delete from 执行的过程中：
用：select count(*) from contact784;发现表的数据量一直是100行
用:explain select count(*) from contact784;可以发现数量一直在减少，显示当前

784是是因为前面这个文章的原因“
http://hanyh.iteye.com/blog/431323
”

评论

12 楼 zl_131 2009-08-10  

我记得innodb与MyISAM的区别就是这个,你的表有外键,删除肯定有效率问题,不过他在删除的时间好像是删除一条记录后就COMMIT一次,需要把表AUTO COMMIT设置一下,还有innodb在执行COUNT()命今时锁表,效率也很慢,建议使用MyISAM,就算有外键你可以通过关联进行查询,MyISAM查询速度很快,如果没有大批量的更新数据需要.

11 楼 mikeandmore 2009-08-08  

icefishc 写道

whaosoft 写道

drop table然后重建。  都比 delete from 快吗 有什么根据吗????

这个是真的.  drop 是DDL不需要事务日志。  这两个差的还是很多的， 试一下就知道了。

re
因为delete from 以后是可以rollback的。。。

不过我觉得insert into会更慢？？？

10 楼 kjj 2009-08-07  

hanyh 写道

If there are no FOREIGN KEY constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition,
  ------MySQL 5.0 Reference Manual

很遗憾，我的concat表是有外键约束。虽然另外一个外键约束的表是空的，到是可以考虑先去出外键约束后truncate....

删除前删除外间约束条件怎么样！！

9 楼 icefishc 2009-07-26  

whaosoft 写道

drop table然后重建。  都比 delete from 快吗 有什么根据吗????

这个是真的.  drop 是DDL不需要事务日志。  这两个差的还是很多的， 试一下就知道了。

8 楼 whaosoft 2009-07-26  

drop table然后重建。  都比 delete from 快吗 有什么根据吗????

7 楼 willko 2009-07-24  

那问题应该是在外键上了，，For an InnoDB table, InnoDB processes TRUNCATE TABLE by deleting rows one by one if there are any FOREIGN KEY constraints that reference the table.

刚开始，我还觉得和表空间有关系

6 楼 hanyh 2009-07-24  

If there are no FOREIGN KEY constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition,
  ------MySQL 5.0 Reference Manual

很遗憾，我的concat表是有外键约束。虽然另外一个外键约束的表是空的，到是可以考虑先去出外键约束后truncate....

5 楼 willko 2009-07-24  

mysql> TRUNCATE Member2;
Query OK, 0 rows affected (0.17 sec)

200w，innodb

配置很差的....

4 楼 icefishc 2009-07-23  

引用

If there are no FOREIGN KEY constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition,
  ------MySQL 5.0 Reference Manual

不过这个好像和版本有关系 似乎有人report过bug...
你用的啥版本

3 楼 heyjava 2009-07-23  

如果IO很大的话...根据InnoDB double Write原理,可以有以下优化:
1.innodb_log_file_size 可以设得大一点
2.innodb_log_buffer_size 也可以稍微大一点,减少BINLOG IO操作,当然这样安全性也会降低
3.innodb_flush_log_at_trx_commit =2 这个就不多说了..
4.innodb_buffer_pool_size 多余内存都设到这里去吧..

当然这跟Drop一个表再重建的速度无法比...

2 楼 hanyh 2009-07-23  

对InnoDB来说TRUNCATE TABLE CONTACTXX ;执行的动作类似，快不了多少 。

1 楼 icefishc 2009-07-23  

这个时候应该用 TRUNCATE