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

问题描述：在我们数据中由于是根据当时的情况对客户表和客户分类表新增一个中间，又因为是中期新增，所以中间表的建表结构是将客户表的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数据。基本告一段落如果有数据偶尔执行一下就好了有输出，删除数据不是很快但也很有效果，也不影响业务的正常使用。