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)
这个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)
解决方案 »
- Administrator 和 Query Browser
- java.sql.SQLException: Access denied for user 'root'@'localhost' (using password
- 诚心请教Explain问题...不理解ING...
- 求一mysql语句,急,在线等
- 同时查询两个表,估计要用 left join
- 怎样在DOS下远程登陆一台MYSQL的服务器?
- 双master ,双mysqlproxy 问题
- mysql 怎么计算一行的长度
- mysql主从复制报错
- 求教case when 与left join连用的语法问题
- mysql一个多步操作涉及多个表,要求如果有一步不成功就撤销全部操作,该如何实现?
- 如何在存储过程中创建事件
这个表
id 和 pid 没有规律 。 没有确定的 规则。 这个环境是 oltp
反复思考 我并不是很想用 分区表 。该表主要是上面的语句以引起的deadlock
平时也就主要用 insert update delete这些操作
有类似语句 " ahistory e left join Ehistory part on (e.id = part.Id and e.type=1 and e.boolLatest='Y' and part.boolLatest='Y')可以知道 。 如果有这样复杂的join, 这个表确实不适合分区的
把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;
测试 复合索引 (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)
现在准确原因找到, 问题该如何解决 ??
先把问题简化如下,现在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?问该如何解决或者避免这个现象??