对于一个频繁增删的表，如何建立索引，提高查询效率？

[u010074019]

我有一个表A，可以看做是临时表，我启动了一个线程，不停的查询这个表，只要有数据，便取出处理并删除。在索引的建立规则中，对于一个频繁删除和增加的表适不适合建立索引的。所以表A我并没有建立索引。
这里要提一个问题：就是之前我的表A是有索引的，可是线程在执行20分钟左右后，就会自己停止，线程未退出，
就像阻塞了一样。
一直不清楚是什么问题，后来考虑到可能是索引的问题，就把索引删了。测试时项目启动了6 7 个小时，一直没问题。

但是，如果A表中有了太多的数据，项目就会报错，query execution was interrupted
我从网上查到是慢查询导致的异常。
我的查询其实很简单，就是一个select from limit 200；可是我A表有4000w条数据，用这个查询就会非常慢。
我只是每次想取到A表的前200条数据，处理并删除。可是数据量一大，就出现了问题。

请问现在的问题怎么解决？

[attilax]

4000w条数据 ,,,当然是要有索引拉..... 频繁删除更新 也许造成了锁表...造成阻塞..... 一个好的方法是添加个锁表检测线程,自动排除死锁... 另一个方法是,普通的删除只做逻辑删除,设置个flag字段......添加个线程定时做物理删除....

[u010074019]

请问， 1，如何自动排除死锁。锁表检测什么的确实没有接触过。 2，我现在的解决方案就是，逻辑删除。然后每周删一次删除状态的数据。这时需要重建索引么？ 还有我是这样设定的，status = 1的数据标注为删除状态，这个status字段需要加索引么？ 因为删除的时候需要 where status = 1。 3，我每次取出队列表的数据，会做相应的处理，同时会更新另外几个表的信息。你说，频繁删除更新 也许造成了锁表...造成阻塞.....。如果更新也会锁表，那么这几个表是不是都要处理？

[Norris_Zhang]

哦原来是逻辑删除，我说为什么有那么多数据？你不停地检索出数据来处理然后删除掉，还有那么多数据？吞吐量有多大？一周4000w条吗？ 如果是一周4000w条，一分钟会有4000条数据写进来，如果处理后用物理删除呢？那这个表就始终在1w条左右的数据量了，就不存在检索慢的问题了啊。

[日知己所无]

感觉得先分析出来是哪里造成性能瓶颈的？ 每次想取到A表的前200条数据，处理并删除。可是数据量一大（4000万条数据），就出现了问题。 1）如果是单纯的取前200条数据造成的性能问题，暂时没想到什么好办法 2）如果是每取200条就处理一下，然后再取200条再处理的话 可以考虑一次取多一些，比如1万条，然后分批处理（这时候可以每200条一处理），全都处理完，把这1万条一起commit回去，以减少IO操作的次数 3）这些数据都是本地机器上的数据库还是远程服务器端的数据库上的数据？ 这个可能会有一些影响 4）可以想办法把那个大的表拆分成小表吗？ 5）必须实时进行删除操作吗？可以用替代方案，比如在数据库中外加1列删除标志列，程序暂时不马上删除数据库里的数据，而是仅仅处理这个标志位。等晚上系统空闲的时候再额外执行一个删除指令，把符合删除条件的数据删除掉……总之，方法总比问题多…… 如果是频繁操作的话，暂时认为和索引怎么设定没什么太大关系 相反，如果特别频繁的操作的话，索引少一些也许还能快一点儿

[u010074019]

你好，我之前就是用的处理完后就直接删除。可是上面我也提到了，就是这样处理会出现一些问题。系统开始一段时间后，再去执行处理队列表的存储过程就会堵塞，一直没有处理。控制台信息一直在打印调用存储过程的信息，没有处理成功的信息。 上面那位也说到了，他怀疑是锁表的问题。我一直也没找到到底是什么问题。

[attilax]

首先,我觉得必做的是添加个锁表检测线程,自动排除死锁... 因为其他方法常常要调整工作量大的,而且智者千虑 必有一失,谁也不能保证哪块代码绝对的不锁表。。 锁表检测：：一般数据库都提供一个特别sql查询锁住的表，查出来后调用数据库sql提供的 kill （ 这个kill是数据库提供的kill语句，非操作系统的kill程序） ， 杀掉锁住的sql连接id 2.检测长查询，长时间更新等操作语句，，一般锁表都是这类语句造成的，锁表查询一般可查看到那条sql的执行时间。。 数据库服务器配置日志也可以记录长时间的sql语句 代码层也可使用日志记录执行时间。。 一般是相关索引没有加或者长事务造成，缺失索引要加上。。 3.使用短事务。。单语句事务（ 无事务） 长事务会大大增加锁表机会。。 尽可能不使用事务操作，事务虽然减少io以及增加数据完整性，但目前当务之急是死锁，适当放弃事务是个完美平衡。。 4.逻辑删除并读写分离分表。。 这个删除标志 可以放在a_del表里，通过外键与A表关联。。。这样A表就没有更新了，只有查询了。。A_del表则基本只增加操作了。。读写分离大大减少锁表可能性。。 这个删除标志要加索引。。 .添加个线程定时做物理删除.... 3，我每次取出队列表的数据，会做相应的处理，同时会更新另外几个表的信息。你说，频繁删除更新 也许造成了锁表...造成阻塞.....。如果更新也会锁表，那么这几个表是不是都要处理？ 这个看情况，只要锁表检测做好了，一般就大大轻松了。。不用那么严格的避免锁表了，

[attilax]

就把索引删了。测试时项目启动了6 7 个小时，一直没问题。.根据这个判断，因为有索引，会拉长事务操作，容易造成锁表。。但是这个表太大，查询时索引必须要的。。只能锁表排除+读写分离。。。至于要不要拆分事务，一般看那几部做完，很可能就满足需要了。。实在不行，还得拆事务吧。。

[attilax]

还有一种可能是连接数满了，，有索引的情况下，时间会长点，导致连接占满，，释放少，占用多，也可能造成阻塞，，可以使用线程dump ，看阻塞在哪块代码了。。阻塞的是后，连接数情况可以查看下

[多木多多木]

哥们，我最近一段时间正在学hadoop平台，基于分布式的数据分析和处理，建议用这个。面对4000w的数据，确实得分块处理。

[kahnnash]

学习学习！！

[程序员阿超的博客]

可以根据id来分块处理，比如第一次处理id<1000000,第二次处理1000000<=id<20000,以此类推 delete from table where id>=10000 and id<2000000