共计 2258 个字符，预计需要花费 6 分钟才能阅读完成。
摘要： 有天，一敌人在线上执行一条 update 语句批改数据库数据的时候，where 条件没有带上索引，导致业务间接崩了，被老板教训了一波。
本文分享自华为云社区《update 没有索引，会锁全表！》，作者：小林 coding。
有天，一敌人在线上执行一条 update 语句批改数据库数据的时候，where 条件没有带上索引，导致业务间接崩了，被老板教训了一波。
这次咱们就来看看：
- 为什么会产生这种的事变？
- 又该如何防止这种事变的产生？
说个前提，接下来说的案例都是基于 InnoDB 存储引擎，且事务的隔离级别是可反复读。
为什么会产生这种的事变？
InnoDB 存储引擎的默认事务隔离级别是「可反复读」，然而在这个隔离级别下，在多个事务并发的时候，会呈现幻读的问题，所谓的幻读是指在同一事务下，间断执行两次同样的查问语句，第二次的查问语句可能会返回之前不存在的行。
因而 InnoDB 存储引擎本人实现了行锁，通过 next-key 锁（记录锁和间隙锁的组合）来锁住记录自身和记录之间的“间隙”，避免其余事务在这个记录之间插入新的记录，从而防止了幻读景象。
当咱们执行 update 语句时，实际上是会对记录加独占锁（X 锁）的，如果其余事务对持有独占锁的记录进行批改时是会被阻塞的。另外，这个锁并不是执行完 update 语句就会开释的，而是会等事务完结时才会开释。
在 InnoDB 事务中，对记录加锁带根本单位是 next-key 锁，然而会因为一些条件会进化成间隙锁，或者记录锁。加锁的地位精确的说，锁是加在索引上的而非行上。
比方，在 update 语句的 where 条件应用了惟一索引，那么 next-key 锁会进化成记录锁，也就是只会给一行记录加锁。
这里举个例子，这里有一张数据库表，其中 id 为主键索引。
假如有两个事务的执行程序如下：
能够看到，事务 A 的 update 语句中 where 是等值查问，并且 id 是惟一索引，所以只会对 id = 1 这条记录加锁，因而，事务 B 的更新操作并不会阻塞。
然而， 在 update 语句的 where 条件没有应用索引，就会全表扫描，于是就会对所有记录加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住了。
假如有两个事务的执行程序如下：
能够看到，这次事务 B 的 update 语句被阻塞了。
这是因为事务 A 的 update 语句中 where 条件没有索引列，所有记录都会被加锁，也就是这条 update 语句产生了 4 个记录锁和 5 个间隙锁，相当于锁住了全表。
因而，当在数据量十分大的数据库表执行 update 语句时，如果没有应用索引，就会给全表的加上 next-key 锁，那么锁就会继续很长一段时间，直到事务完结，而这期间除了 select … from 语句，其余语句都会被锁住不能执行，业务会因而停滞，接下来等着你的，就是老板的挨骂。
那 update 语句的 where 带上索引就能防止全表记录加锁了吗？
并不是。
要害还得看这条语句在执行过程种，优化器最终抉择的是索引扫描，还是全表扫描，如果走了全表扫描，就会对全表的记录加锁了。
又该如何防止这种事变的产生？
咱们能够将 MySQL 里的 sql_safe_updates 参数设置为 1，开启安全更新模式。
官网的解释：
If set to 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause. (Specifically, UPDATE statements must have a WHERE clause that uses a key or a LIMIT clause, or both. DELETE statements must have both.) This makes it possible to catch UPDATE or DELETE statements where keys are not used properly and that would probably change or delete a large number of rows. The default value is 0.
大抵的意思是，当 sql_safe_updates 设置为 1 时。
update 语句必须满足如下条件之一能力执行胜利：
- 应用 where，并且 where 条件中必须有索引列；
- 应用 limit；
- 同时应用 where 和 limit，此时 where 条件中能够没有索引列；
delete 语句必须满足如下条件之一能力执行胜利：
- 应用 where，并且 where 条件中必须有索引列；
- 同时应用 where 和 limit，此时 where 条件中能够没有索引列；
如果 where 条件带上了索引列，然而优化器最终扫描抉择的是全表，而不是索引的话，咱们能够应用 force index([index_name]) 能够通知优化器应用哪个索引，以此防止有几率锁全表带来的隐患。
总结
不要小看一条 update 语句，在生产机上使用不当可能会导致业务停滞，甚至解体。
当咱们要执行 update 语句的时候，确保 where 条件中带上了索引列，并且在测试机确认该语句是否走的是索引扫描，避免因为扫描全表，而对表中的所有记录加上锁。
咱们能够关上 MySQL sql_safe_updates 参数，这样能够预防 update 操作时 where 条件没有带上索引列。
如果发现即便在 where 条件中带上了列索引列，优化器走的还是全标扫描，这时咱们就要应用 force index([index_name]) 能够通知优化器应用哪个索引。
这次就说到这啦，下次要小心点，别再被老板挨骂啦。
点击关注，第一工夫理解华为云陈腐技术~
