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

Question

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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