为什么说InnoDB在删除和更新语句时要带上索引?
因为InnoDB只有在访问行的时候才会对其加锁,而索引能够减少InnoDB的访问行数,从而减少锁的数量。
虽然InnoDB行锁的效率很高,内存使用很少,但是锁定行的时候仍会带来额外的开销,而且锁定超过需要的行会增加锁争用和降低并发性。
所以说删除和更新的时候要带上索引,而且尽量使用唯一索引或者是尽量使用可以查询范围比较小的索引。
唯一索引的一个特性
如果索引无法过滤掉无效的行,那么在InnoDB检索到数据并返回给服务层后,MySQL服务器才能应用Where子句。在MySQL5.1和之后的版本,唯一索引可以在服务器端过滤行后就释放无效行的锁,而不用等到事务的提交。而普通索引,只有等到事务提交后才会释放锁。
不带索引的后果
如果不带索引,删除和更新的时候会进行全表扫描 --浪费时间 ,而且表中每一行都会加上行–加大锁争用和降低了并发性,如果表过大很可能出现锁超时
验证唯一索引的特性
使用sakila库中的actor表。sakila库下载地址,下载sakila database 的TGZ/ZIP压缩包。
表结构
#事务一
start transaction;
select actor_id from actor where actor_id<5 and actor_id<>1 for UPDATE;
COMMIT;
#事务二
start transaction;
select actor_id from actor where actor_id=1 for UPDATE;
COMMIT;
执行事务一不提交,按照上面所说返回结果后actor_id=1的列就释放了。
执行事务二不提交,正常返回数据,没有阻塞。
提交两个事务
说明:唯一索引可以在服务器端过滤行后就释放无效行的锁,而不用等到事务的提交
验证普通索引
复制一个actor表加一个age列,并给age加普通索引
事务一:
start transaction;
select * from actor_me where age<5 and age<>1 for UPDATE;
COMMIT;
事务二:
start transaction;
select * from actor_me where age=1 for UPDATE;
COMMIT;
执行事务一不提交
执行事务二不提交,被阻塞没有返回结果。
提交事务一,事务二返回结果。提交事务二
说明:普通索引等到事务的提交后才释放锁
参考
《高性能的Mysql》