mysql
这个sql如何优化(在线 高并发的情况容易引起死锁)
update Ehistory set boolsLatest='N', boolHistory='Y' where  boolLatest='Y' and ( (id=186559604 and parentId=23741) or (id=186559604 and parentId=85947) or (id=186559604 and parentId=85946) or (id=186559604 and parentId=85949) or (id=186559604 and parentId=85948) or (id=186559604 and parentId=85951) or (id=186559604 and parentId=23737) or (id=186559604 and parentId=85950) or (id=186559604 and parentId=23718) or (id=186559604 and parentId=85920) or (id=186559604 and parentId=23719) or (id=186559604 and parentId=23716) or (id=186559604 and parentId=23717) or (id=186559604 and parentId=23714) or (id=186559604 and parentId=85924) or (id=186559604 and parentId=23715) or (id=186559604 and parentId=23712) or (id=186559604 and parentId=85926) or (id=186559604 and parentId=23726) or (id=186559604 and parentId=85928) or (id=186559604 and parentId=85929) or (id=186559604 and parentId=23724) or (id=186559604 and parentId=85932) or (id=186559604 and parentId=23723) or (id=186559604 and parentId=85933) or (id=186559604 and parentId=85934) or (id=186559604 and parentId=23720) or (id=186559604 and parentId=85907) or (id=186559604 and parentId=85911) or (id=186559604 and parentId=85910) )我新创建了 索引 (id,pid)并且强制force_index(),但是根据执行计划还是不理想
+----+-------------+--------------------------+-------+-------------------------+-------------------------+---------+------+------+-------------+
| id | select_type | table                    | type  | possible_keys           | key                     | key_len | ref  | rows | Extra       |
+----+-------------+--------------------------+-------+-------------------------+-------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | Ehistory | range | idx_id_pID | idx_id_pID | 16      | NULL |   30 | Using where |
+----+-------------+--------------------------+-------+-------------------------+-------------------------+---------+------+------+-------------+
1 row in set根据 ref为null  而且最后using where说明索引没有用到这里需要知道这个sql 还有优化的方式没有  (这个表有120万 一周可以达到200万。并发1大 就容易deadlock)

解决方案 »

  1.   

    没有 。
    这个表 
    id  和 pid 没有规律 。 没有确定的 规则。  这个环境是 oltp 
    反复思考 我并不是很想用 分区表 。该表主要是上面的语句以引起的deadlock
    平时也就主要用 insert update delete这些操作
      

  2.   

    每周清理一次的  该表是 history 所以最好不哟啊要伸出历史数据。
      

  3.   

    检查所有java代码
    有类似语句  " ahistory e left join Ehistory part on (e.id = part.Id and e.type=1 and e.boolLatest='Y' and part.boolLatest='Y')可以知道  。 如果有这样复杂的join, 这个表确实不适合分区的  
      

  4.   

    建立复合索引 (id,parentId,boolLatest)
    把boolLatest条件直接放到每一个条件中,然后按照顺序更新,这样或许可以减少一些deadlock
    update Ehistory set boolsLatest='N', boolHistory='Y' where (id=186559604 and parentId=23741 and boolLatest='Y') or (id=186559604 and parentId=85947 and boolLatest='Y') order by id,parentId;
      

  5.   

    看原表 已经有索引 (id,parentId)
    测试 复合索引 (id,parentId,boolLatest)
    2个sql的执行时间是一样的update Ehistory set boolsLatest='N', boolHistory='Y' where boolLatest='Y' and ( (id=186559604 and parentId=23741) or (id=186559604 and parentId=85947) or (id=186559604 and parentId=85946) or (id=186559604 and parentId=85949) or (id=186559604 and parentId=85948) or (id=186559604 and parentId=85951) or (id=186559604 and parentId=23737) or (id=186559604 and parentId=85950) or (id=186559604 and parentId=23718) or (id=186559604 and parentId=85920) or (id=186559604 and parentId=23719) or (id=186559604 and parentId=23716) or (id=186559604 and parentId=23717) or (id=186559604 and parentId=23714) or (id=186559604 and parentId=85924) or (id=186559604 and parentId=23715) or (id=186559604 and parentId=23712) or (id=186559604 and parentId=85926) or (id=186559604 and parentId=23726) or (id=186559604 and parentId=85928) or (id=186559604 and parentId=85929) or (id=186559604 and parentId=23724) or (id=186559604 and parentId=85932) or (id=186559604 and parentId=23723) or (id=186559604 and parentId=85933) or (id=186559604 and parentId=85934) or (id=186559604 and parentId=23720) or (id=186559604 and parentId=85907) or (id=186559604 and parentId=85911) or (id=186559604 and parentId=85910) )死锁的准确原因是 2台web机器 同时在数据库服务器c机器上执行这个相同的sql 导致
    update 产生锁 把所有where语句 后面的行锁死. (这个表没有pk)
    现在准确原因找到, 问题该如何解决 ??
      

  6.   

    2台web机器(java) ,一台数据库服务器(mysql),
    先把问题简化如下,现在2台机器同时往数据库服务器同1表插入或者修改数据, 导致死锁2台web机器,分别是web1 web2
    web1是update a  where a='N' and id in (1,2,3,4,54,45,4,5,45,4,5,4,5,4,5,4,5,.......)
    web2是update a  where a='N' and id in (1,2,3,4,54,45,4,5,45,4,5,4,5,4,5,4,5,.......)
    是同一时刻,结果导致发生死锁。
    这a表没有pk , 问web程序或者db 该如何操作才能避免deadlock?问该如何解决或者避免这个现象??