关于mysql:为什么update语句where-条件没有带上索引会锁全表

43次阅读

共计 937 个字符，预计需要花费 3 分钟才能阅读完成。

InnoDB 存储引擎的默认事务隔离级别是「可反复读」，然而在这个隔离级别下，在多个事务并发的时候，会呈现幻读的问题，所谓的幻读是指在同一事务下，间断执行两次同样的查问语句，第二次的查问语句可能会返回之前不存在的行。

因而 InnoDB 存储引擎本人实现了行锁，通过 next-key 锁（记录锁和间隙锁的组合）来锁住记录自身和记录之间的“间隙”，避免其余事务在这个记录之间插入新的记录，从而防止了幻读景象。

当咱们执行 update 语句时，实际上是会对记录加独占锁（X 锁）的，如果其余事务对持有独占锁的记录进行批改时是会被阻塞的。另外，这个锁并不是执行完 update 语句就会开释的，而是会等事务完结时才会开释。

在 InnoDB 事务中，对记录加锁带根本单位是 next-key 锁，然而会因为一些条件会进化成间隙锁，或者记录锁。加锁的地位精确的说，锁是加在索引上的而非行上。

在 update 语句的 where 条件没有应用索引，就会全表扫描，于是就会对所有记录加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住了。

能够看到，这次事务 B 的 update 语句被阻塞了。这是因为事务 A 的 update 语句中 where 条件没有索引列，所有记录都会被加锁，也就是这条 update 语句产生了 4 个记录锁和 5 个间隙锁，相当于锁住了全表。

咱们能够将 MySQL 里的 sql_safe_updates 参数设置为 1，开启安全更新模式。大抵的意思是，当 sql_safe_updates 设置为 1 时。
update 语句必须满足如下条件之一能力执行胜利：
应用 where，并且 where 条件中必须有索引列；
应用 limit；同时应用 where 和 limit，此时 where 条件中能够没有索引列；

delete 语句必须满足以下条件能执行胜利：
同时应用 where 和 limit，此时 where 条件中能够没有索引列；
如果 where 条件带上了索引列，然而优化器最终扫描抉择的是全表，而不是索引的话，咱们能够应用 force index([index_name]) 能够通知优化器应用哪个索引，以此防止有几率锁全表带来的隐患。

参考资料：