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 python.han@gmail.com

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
”

这个时候应该用 TRUNCATE

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

如果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一个表再重建的速度无法比...

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

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

200w，innodb

配置很差的....

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....

那问题应该是在外键上了，，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.

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

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

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