频率很高的update系统如何优化
50W数据的一个表,系统运行时,大量update case set visit = ?, due_date = ?, update_by = ?, update_date = ? WHERE id = ? 语句,监控到同一个update 语句时间很不稳定,经常很慢,目前代码上面已做到优化,但是因为系统是crm系统,流程job+数据流入+大量客服使用系统,导致大量update业务操作无法避免,想从数据库层面做一些优化,求一些建议。mysql是innodb。
2016-03-28 14:12:54,027 [HandleTimeService.java:56] - case update spend 3593ms,caseId:496344
2016-03-28 14:12:57,682 [HandleTimeService.java:56] - case update spend 3649ms,caseId:496309
2016-03-28 14:12:57,676 [HandleTimeService.java:56] - case update spend 3642ms,caseId:496032
2016-03-28 14:12:57,684 [HandleTimeService.java:56] - case update spend 3654ms,caseId:496309
2016-03-28 14:13:00,510 [HandleTimeService.java:56] - case update spend 683ms,caseId:496333
2016-03-28 14:13:00,701 [HandleTimeService.java:56] - case update spend 182ms,caseId:496031
2016-03-28 14:13:11,005 [HandleTimeService.java:56] - case update spend 5444ms,caseId:495848
2016-03-28 14:13:15,130 [HandleTimeService.java:56] - case update spend 4109ms,caseId:467920
2016-03-28 14:13:17,525 [HandleTimeService.java:56] - case update spend 2383ms,caseId:495767
2016-03-28 14:13:21,197 [HandleTimeService.java:56] - case update spend 3680ms,caseId:496307
2016-03-28 14:13:21,891 [HandleTimeService.java:56] - case update spend 699ms,caseId:496346
2016-03-28 14:13:21,887 [HandleTimeService.java:56] - case update spend 697ms,caseId:496347
2016-03-28 14:13:21,892 [HandleTimeService.java:56] - case update spend 699ms,caseId:255415
2016-03-28 14:13:22,013 [HandleTimeService.java:56] - case update spend 119ms,caseId:496254
2016-03-28 14:13:26,365 [HandleTimeService.java:56] - case update spend 3798ms,caseId:496305