一条update语句引发的现场事故！

　　昨晚在群划水的时候，看到有位读者说了这么一件事。
　　大概就是，在线上执行一条update语句修改数据库数据的时候，where条件没有带上索引，导致业务直接崩了，被老板教训了一波
　　这次我们就来看看：为什么会发生这种的事故？又该如何避免这种事故的发生？
　　说个前提，接下来说的案例都是基于InnoDB存储引擎，且事务的隔离级别是可重复读。为什么会发生这种的事故？
　　InnoDB存储引擎的默认事务隔离级别是可重复读，但是在这个隔离级别下，在多个事务并发的时候，会出现幻读的问题，所谓的幻读是指在同一事务下，连续执行两次同样的查询语句，第二次的查询语句可能会返回之前不存在的行。
　　因此InnoDB存储引擎自己实现了行锁，通过nextkey锁（记录锁和间隙锁的组合）来锁住记录本身和记录之间的间隙，防止其他事务在这个记录之间插入新的记录，从而避免了幻读现象。
　　当我们执行update语句时，实际上是会对记录加独占锁（X锁）的，如果其他事务对持有独占锁的记录进行修改时是会被阻塞的。另外，这个锁并不是执行完update语句就会释放的，而是会等事务结束时才会释放。
　　在InnoDB实务中，对记录加锁带基本单位是nextkey锁，但是会因为一些条件会退化成间隙锁，或者记录锁。加锁的位置准确的说，锁是加在索引上的而非行上。
　　比如，在update语句的where条件使用了唯一索引，那么nextkey锁会退化成记录锁，也就是只会给一行记录加锁。
　　这里举个例子，这里有一张数据库表，其中id为主键索引。
　　假设有两个事务的执行顺序如下：
　　可以看到，事务A的update语句中where是等值查询，并且id是唯一索引，所以只会对id1这条记录加锁，因此，事务B的更新操作并不会阻塞。
　　但是，在update语句的where条件没有使用索引，就会全表扫描，于是就会对所有记录加上nextkey锁（记录锁间隙锁），相当于把整个表锁住了。
　　假设有两个事务的执行顺序如下：
　　可以看到，这次事务B的update语句被阻塞了。
　　这是因为事务A的update语句中where条件没有索引列，所有记录都会被加锁，也就是这条update语句产生了4个记录锁和5个间隙锁，相当于锁住了全表。
　　因此，当在数据量非常大的数据库表执行update语句时，如果没有使用索引，就会给全表的加上nextkey锁，那么锁就会持续很长一段时间，直到事务结束，而这期间除了select。。。from语句，其他语句都会被锁住不能执行，业务会因此停滞，接下来等着你的，就是老板的挨骂。
　　那update语句的where带上索引就能避免全表记录加锁了吗？
　　并不是。
　　关键还得看这条语句在执行过程种，优化器最终选择的是索引扫描，还是全表扫描，如果走了全表扫描，就会对全表的记录加锁了。又该如何避免这种事故的发生？
　　我们可以将MySQL里的sqlsafeupdates参数设置为1，开启安全更新模式。
　　官方的解释：Ifsetto1，MySQLabortsUPDATEorDELETEstatementsthatdonotuseakeyintheWHEREclauseoraLIMITclause。（Specifically，UPDATEstatementsmusthaveaWHEREclausethatusesakeyoraLIMITclause，orboth。DELETEstatementsmusthaveboth。）ThismakesitpossibletocatchUPDATEorDELETEstatementswherekeysarenotusedproperlyandthatwouldprobablychangeordeletealargenumberofrows。Thedefaultvalueis0。
　　大致的意思是，当sqlsafeupdates设置为1时。
　　update语句必须满足如下条件之一才能执行成功：使用where，并且where条件中必须有索引列；使用limit；同时使用where和limit，此时where条件中可以没有索引列；
　　delete语句必须满足如下条件之一才能执行成功：使用where，并且where条件中必须有索引列；同时使用where和limit，此时where条件中可以没有索引列；
　　如果where条件带上了索引列，但是优化器最终扫描选择的是全表，而不是索引的话，我们可以使用forceindex（〔indexname〕）可以告诉优化器使用哪个索引，以此避免有几率锁全表带来的隐患。总结
　　不要小看一条update语句，在生产机上使用不当可能会导致业务停滞，甚至崩溃。
　　当我们要执行update语句的时候，确保where条件中带上了索引列，并且在测试机确认该语句是否走的是索引扫描，防止因为扫描全表，而对表中的所有记录加上锁。
　　我们可以打开MySQLsqlsafeupdates参数，这样可以预防update操作时where条件没有带上索引列。
　　如果发现即使在where条件中带上了列索引列，优化器走的还是全标扫描，这时我们就要使用forceindex（〔indexname〕）可以告诉优化器使用哪个索引。
　　这次就说到这啦，下次要小心点，别再被老板挨骂啦。