关于删除数据库中百万级数据的解决方案

问题描述：在我们数据中由于是根据当时的情况对客户表和客户分类表新增一个中间，又因为是中期新增，所以中间表的建表结构是将客户表的ID和客户分组表的ID分别写入到中间表里面，这样中间表就有三个字段主键客户表ID客户分组表ID，当时由于数据量大处理不过来所以没有建立级联，在客户表里面有许多导入数据的功能，也有删除数据的功能由于删除和导入操作频繁没有管理中间表的空间，致使中间表荣升到数据库第一大表快超过1达到1千万数据，苦于找方法瘦身，就找到了中间表的内容，删除当时删除客户资料没有删除中间表数据的内容：就有了以下的解决方案：

       利用sql语句删除：delete from m_customer_category where customer_sid_fk not in(select customer_sid from customer);

       由于customer表中的数据是680W，m_customer_category表数据是890w，上面的一条语句在服务器上执行了 3个小时后没有任何反应state状态是end一直是这样的情况，所以放弃没有进行删除。

       第二次删除数据又想想利用存储过程删除也想就会快点所以就找相关的资料写存储过程，如下：

create procedure   del()

              begin

              declare  coun1 int;

               select count(*) into coun1 from m_customer_category where customer_sid_fk not in(select customer_sid from customer);

              while coun1 > 0 do

              delete from m_customer_category where customer_sid_fk not in(select customer_sid from customer) limit 10000;

              commit;

              set coun1=coun1-10000;

              end while;

 

end 

因为在本机上数据比服务器上的数据少了很多，在本机上可以正常执行就是没什么输出，数据也删除，本以为可以执行，可是在第二次晚上更新的时候还是如上的情况还是没有反应，在执行过2个小时后我又放弃了。

       查找原因可能是数据量大并且建立有所以，所以造成批量删除并没什么效果，再次思考能不能做单个删除，也就是说找到一条记录的ID后在根据Id删除数据，在利用存储过程在循环中删除数据应该就没有问题了，所以做了如下修改：

   我将符合条件的数据的id都查找出来放到一张自己新建立的临时表里面如下语句：

insert   into  m_customer_category_back (select id from m_customer_category where customer_sid_fk not in(select customer_sid from customer));

表中是建立索引的所以执行查询的时候是不会很慢的，我是看中他的查询条件才这样操作的，数据很快20多分钟就执行完了130+W数据，这下好了，修改下存储过程就可以实现单条删除数据了，根据这些条件即时不关闭数据的情况也可以正常的删除数据存储过程如下：

begin

              declare  coun1 int;

              declare   count2 int;

               select count(*) into coun1 from m_customer_category_back   limit  300000;

              while coun1 > 0 do

                     select   id into  count2 from m_customer_category_back LIMIT 1;

                     delete from m_customer_category where id = count2;

                     delete  from m_customer_category_back where id=count2;

                     SELECT  '删除一条'+count2;

                     commit;

                     set coun1=coun1-1;

              end while;

 

end

由于害怕服务器长时间造成占用时间过程所以在查询的时候做了小小的修改也就是说我调用一次这个存储过程的时候只删除30w数据。基本告一段落如果有数据偶尔执行一下就好了有输出，删除数据不是很快但也很有效果，也不影响业务的正常使用。

评论

20 楼 zzhonghe 2011-02-20  

我觉得你这几个表应该是可以优化好的，不要因为这点小问题去加入这么一大块东西。

估计你按客户类型查客户资料的语句会类似如下：

select * from customer c
inner join m_customer_category m on c.customerId=m.customerId
inner join category cg on cg.categoryId=m.categoryId
where cg.category categoryName ='xxx';

为了配合这个查询，有几个地方是要家索引的：

customer上： key(customerId)
m_customer_category上：   索引key(categoryId)
category上：  key(categoryName)

如果你的category表数据只有那么几行，或者很少，估计你查出来的结果集会相当大，注意加limit。

--------------------

如果以上不行的话，贴出你的sql执行计划：  explain select ***.. ,以及你们目前的索引和表结构：  show index from tablexxx;    show create table xxx; 一起研究下如何。

19 楼 zzhonghe 2011-02-20  

不知道你们做这个维护工作的时候，数据库是否可以停机，或者说只能读不能写，时间有多长。（估计是可以的，你之前让它这么满负荷跑了N小时，这段时间估计其他什么程序都干不了活）。

我也谈谈我们的经验，不是删除无效数据，而是创建新表，获取有效数据：

1. 或者凌晨找个时间禁止写入， flush tables with read lock;
2. 然后创建一个m_customer_category_new新表, 分好区partition by xxx
3. 用java多线程程序，把m_customer_category中有效数据用多线程程序插入进去，每个区1个线程，速度会很快。（在我们的项目innodb上，每秒插入1万条左右）
4. 数据准备好以后，再创建索引（无索引的表，插入操作比有索引的快很多）
5. 将原来的表干掉，新表rename
6. 最后再把lock取消， unlock tables;


这样分区优化也做了，数据也删除了。

18 楼 还有也许 2011-01-20  

xiaoshan5634 写道

还有也许 写道

兄弟们呀，我现在还有个头大的问题，就是我们的customer已经860万了，而中间表m_customer_category 也有900多万了，如果按类型查找客户资料的话巨慢，如何解决呀，就是customer，m_customer_category,custoemr_category三张表多对多关系，怎么按类型快速查找呀

我真的不明白你们为什么一定要使用多对多的关系，现在你的数据量已经达到这种级别了，你可以考虑第一、使用分区分表；第二、引入Lucene,将客户资料的搜索交给全文索引来处理，他可以解决你搜索的性能。如果对实时性要求很高的话，你可以使用Lucene3.0,Lucene2.0不支持实时索引。

分区分表已经晚了吧，这是在规划项目时考虑好的，第二种很实用，我去学习一下Lucene的使用。

17 楼 wolfbrood 2011-01-19  

一般删除慢都是应该io忙或内存不够。io不行有可能是sql不好，也有可能本来io能力就不行，或者你的数据文件分配有问题。io还可能是因为log日志引起的。

所以在删除的时候是否可以考虑让被删除的表不支持事务，不记log。


否则只能想办法小批量删除提交，不要在一个大事务里面做。

其实你也可以考虑用jdbc把要删除的id查出来，然后批量删除。也许这样会更快，我这边就有类似的功能，也是几百万数据量，直接用sql还没有用jdbc查出来删除快。

16 楼 xiaoshan5634 2011-01-19  

还有也许 写道

兄弟们呀，我现在还有个头大的问题，就是我们的customer已经860万了，而中间表m_customer_category 也有900多万了，如果按类型查找客户资料的话巨慢，如何解决呀，就是customer，m_customer_category,custoemr_category三张表多对多关系，怎么按类型快速查找呀

我真的不明白你们为什么一定要使用多对多的关系，现在你的数据量已经达到这种级别了，你可以考虑第一、使用分区分表；第二、引入Lucene,将客户资料的搜索交给全文索引来处理，他可以解决你搜索的性能。如果对实时性要求很高的话，你可以使用Lucene3.0,Lucene2.0不支持实时索引。

15 楼 feiyan35488 2010-12-28  

可以考虑切表呗

14 楼 云中苍月 2010-12-27  

不知道楼主用的是什么数据库,
至少在db2和mysql下delete基本属于噩梦.
truncate ,load replace之类的命令还是比较适合你的 .
当然,如果有分区表的话可以考虑drop Partation一类的指令.

13 楼 zy8643954 2010-12-27  

还有也许 写道

zy8643954 写道

1.不要迷信所有的查询必须使用索引。

2.查询需要删除的数据的sql，不会变成这个case的性能瓶颈。

3.如果选择用存储过程的话，就要减少 pl/sql引擎 和 sql引擎的切换。

一看就是高手，能不能给小弟详细说个例子呢？

我才入门呢，我在前面 写了一个存储过程的。楼主没看见？

12 楼 还有也许 2010-12-27  

zy8643954 写道

1.不要迷信所有的查询必须使用索引。

2.查询需要删除的数据的sql，不会变成这个case的性能瓶颈。

3.如果选择用存储过程的话，就要减少 pl/sql引擎 和 sql引擎的切换。

一看就是高手，能不能给小弟详细说个例子呢？

11 楼 zy8643954 2010-12-27  

1.不要迷信所有的查询必须使用索引。

2.查询需要删除的数据的sql，不会变成这个case的性能瓶颈。

3.如果选择用存储过程的话，就要减少 pl/sql引擎 和 sql引擎的切换。

10 楼 wenyangbbs 2010-12-27  

在这之前也对not in  和not  exists进行过测试，但是最后的结果是查询使用的时间是基本是相同的（InnoDB引擎） 所以在上述说明中并没有说not exsts ，在对myisam引擎中相对的速度会快（因为不在同一个服务器上的两个相同的数据，数据库引擎不一样）。这种只是对mysql的两种引擎的比较，下面说1楼中所说的问题，在每个表中都建立有索引，虽然查看数据相对的比较快，但是在删除中查询虽然相对较快但是牵扯到数据更新的话那速度应该是相对慢的，根据删除的结果，大约需要10个小时左右（具体时间没有试），像这种的话我一晚上的更新时间也是不够的（所以弃而不用）。3楼所说的删除数据一条一条的删除这个问题当时也想过，只是不想在用in的子查询，所以才用一条一条的删除，但是为什么不一次删除也是有原因的，针对当时的问题也曾经执行过，执行所用的时间也是巨大的，也是没有办法的事（具体的时间上线时间也是定在3个小时）。回答5楼的问题，不是我想用，是实际情况决定的，所以在这里 一下。怎么说呢理论永远都是理论等用到实际就会明白什么是对的，什么的错的

9 楼 yjesja 2010-12-27  

分批次交提交更快，一批1000条

8 楼 还有也许 2010-12-27  

兄弟们呀，我现在还有个头大的问题，就是我们的customer已经860万了，而中间表m_customer_category 也有900多万了，如果按类型查找客户资料的话巨慢，如何解决呀，就是customer，m_customer_category,custoemr_category三张表多对多关系，怎么按类型快速查找呀

7 楼 zzhonghe 2010-12-26  

难道我眼花了...  in 不走索引？   看执行计划是走的。

6 楼 zy8643954 2010-12-25  

oracle 可以这么写：

declare

type id_tab is table of varchar2(20);

customers      id_tab;

cursor c_delete is
select id 
from m_customer_category
where customer_sid_fk
  not in(select customer_sid from customer);

begin

  open c_delete;
  loop
    fetch c_delete bulk collect into customers limit 10000;
    forall i in customers.first .. customers.last
      delete m_customer_category a where a.id = customers(i);
    commit;
    exit when customers.count() = 0;
  end loop;
  close c_delete;

end;

5 楼 richard_2010 2010-12-25  

in本来就不提倡的，lz居然还用not in
补补吧

4 楼 tomorrow009 2010-12-24  

xiaoshan5634 写道

楼主
你这是基本的SQL问题
换成如下语句试试：
delete from m_customer_category where not exists(select 1 from customer where m_customer_category.customer_sid_fk=customer_sid)

如果需要花费的时间还是很长，你可以将m_customer_category中的数据分成10次来执行。
如：delete from m_customer_category where ID>=1000000 and ID <200000 not exists(select 1 from customer where m_customer_category.customer_sid_fk=customer_sid)
这样基本可以解决你的问题。

这个是正解

in 是不走索引的，所以非常慢

3 楼 leon1509 2010-12-24  

如果删一条提交一条，会加重数据库很大的负担的！这是基本的知识。至少你应该在100－200之间去提交一次。

2 楼 coloforlia 2010-12-24  

一般備份后 truncate 的路過。。

1 楼 xiaoshan5634 2010-12-23  

楼主
你这是基本的SQL问题
换成如下语句试试：
delete from m_customer_category where not exists(select 1 from customer where m_customer_category.customer_sid_fk=customer_sid)

如果需要花费的时间还是很长，你可以将m_customer_category中的数据分成10次来执行。
如：delete from m_customer_category where ID>=1000000 and ID <200000 not exists(select 1 from customer where m_customer_category.customer_sid_fk=customer_sid)
这样基本可以解决你的问题。