如何设计一个频繁DML操作的表,mysql

J2EE 码拜 6年前 (2015-04-08) 472次浏览 0个评论

现在有一个中间表需要设计,由系统A负责插入数据,由系统B负责取出数据处理并删除。
由于会产生以下问题,请问该表该如何设计,或者有什么其他的解决方案。

1,由于不知道A系统何时插入数据,所以删除只能用delete,这会导致表空间一直在增加。

2,这个中间表需要加索引么,如果加索引,频繁dml操作会造成该表查询效率变慢。
如果不加索引,B系统异常停止,导致该中间表数据变多,也会导致查询效率变低的问题。

B系统的设计是这样的
1)B系统会启动一个线程循环调用一个存储过程pro1
2)pro1会循环调用存储过程pro2
3)pro2会开启一个事物,每次处理中间表的前200条数据,并把成功处理的条数返回给pro1
4)pro1会进行判断,如果成功条数累加后大于2500或者小于200,则退出,并返回结果给B系统
5)B系统会判断该返回值,如果该值小于200,则线程等待5秒,再调用pro1。

之前做测试的时候,如果给中间表加索引,B系统启动2个小时左右存储过程就阻塞了,没有异常,只是不停的调用存储过程pro1但是没有任何返回结果,也就是没有对中间表做相应的处理,这里初步怀疑是由于加了索引,并频繁的dml操作,导致
中间表查询效率变慢,事物持续时间太长,导致mysql把中间表锁住了。

之后我把索引删除了,不出所料,比之前跑的时间长了,B系统跑了7,8小时都没问题,可是第二天还是出现了之前的问题,
这里怀疑是表空间变的太大导致查询效率变低,以至于造成了上面提到的锁表。

这里提一下,测试环境是为了测试峰值,插入速度是非常快的,平均每秒30-50条记录,项目实际情况不会有这么大的负担。

如何设计一个频繁DML操作的表,mysql
看的头疼 楼主可以把这个发到数据库论坛去
如何设计一个频繁DML操作的表,mysql
推荐使用消息队列中间件
如何设计一个频繁DML操作的表,mysql
30分
对于 这种 多个系统 频繁操作 某一个中间表的设计 本身 应该以业务为主,不一定 非要数据库,也可以把这个频繁操作 转移到程序中处理,或者 用缓存+消息队列来解决 数据库中间表本身频繁DML的局限性。

若非要用 中间表+存储过程来处理,那么有些比较通用的模式可以提供:

  1,不要建立索引,索引目前对于更新力度不大的 海量查询比较有用,
  2,中间表 在不断的 inser update delte 那么对于表空间已经高水位线 是一直不断膨胀的,那么可以再每晚定时做 清理表空间高水位操作,有这样的SQL命令.
  3,可以添加表来做处理,形成一张主表和详情表,对于频繁操作的主表 建议设计字段和空间 都不要太大,这样主表的压力会小很多,然后对于处理后的业务数据在保存到临时表中,然后后台启定时任务去不断的从临时表中获取主要标示字段,然后进行详情表的操作。


CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明如何设计一个频繁DML操作的表,mysql
喜欢 (0)
[1034331897@qq.com]
分享 (0)

文章评论已关闭!