完蛋,公司被一条 update 语句干趴了!
扫描二维码
随时随地手机看文章
- 为什么会发生这种的事故?
- 又该如何避免这种事故的发生?
但是,在 update 语句的 where 条件没有使用索引,就会全表扫描,于是就会对所有记录加上 next-key 锁(记录锁 间隙锁),相当于把整个表锁住了。假设有两个事务的执行顺序如下:可以看到,这次事务 B 的 update 语句被阻塞了。这是因为事务 A的 update 语句中 where 条件没有索引列,所有记录都会被加锁,也就是这条 update 语句产生了 4 个记录锁和 5 个间隙锁,相当于锁住了全表。因此,当在数据量非常大的数据库表执行 update 语句时,如果没有使用索引,就会给全表的加上 next-key 锁, 那么锁就会持续很长一段时间,直到事务结束。而这期间除了
select ... from
语句,其他语句都会被锁住不能执行,业务会因此停滞,接下来等着你的,就是老板的挨骂。那 update 语句的 where 带上索引就能避免全表记录加锁了吗?并不是。关键还得看这条语句在执行过程中,优化器最终选择的是索引扫描,还是全表扫描,如果走了全表扫描,就会对全表的记录加锁了。2 又该如何避免这种事故的发生?我们可以将 MySQL 里的 sql_safe_updates
参数设置为 1,开启安全更新模式。官方的解释:大致的意思是,当 sql_safe_updates 设置为 1 时。update 语句必须满足如下条件之一才能执行成功:
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.
- 使用 where,并且 where 条件中必须有索引列;
- 使用 limit;
- 同时使用 where 和 limit,此时 where 条件中可以没有索引列;
- 使用 where,并且 where 条件中必须有索引列;
- 同时使用 where 和 limit,此时 where 条件中可以没有索引列;
force index([index_name])
可以告诉优化器使用哪个索引,以此避免有几率锁全表带来的隐患。3 总结不要小看一条 update 语句,在生产机上使用不当可能会导致业务停滞,甚至崩溃。当我们要执行 update 语句的时候,确保 where 条件中带上了索引列,并且在测试机确认该语句是否走的是索引扫描,防止因为扫描全表,而对表中的所有记录加上锁。我们可以打开 MySQL 里的 sql_safe_updates 参数,这样可以预防 update 操作时 where 条件没有带上索引列。如果发现即使在 where 条件中带上了列索引列,优化器走的还是全标扫描,这时我们就要使用 force index([index_name])
可以告诉优化器使用哪个索引。这次就说到这啦,下次要小心点,别再被老板挨骂啦。