38.进阶-SQL优化-update优化(避免行锁升级为表锁)
(1)update优化
我们主要需要注意一下update语句执行时的注意事项。
update course set name = 'javaEE' where id = 1 ;
当我们在执行删除的SQL语句时,会锁定id为1这一行的数据,然后事务提交之后,行锁释放。
但是当我们在执行如下SQL时。
update course set name = 'SpringBoot' where name = 'PHP' ;
当我们开启多个事务,在执行上述的SQL时,我们发现行锁升级为了表锁。 导致该update语句的性能大大降低。
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁 ,并且该索引不能失效,否则会从行锁升级为表锁 。
过程介绍:
InnoDB引擎的三大特性:事务、外键、行级锁。
现在开启了两个窗口,同时begin一个事务,一个窗口执行根据id修改数据。我们提到对于InnoDB引擎当前默认事务隔离级别来说,我们在进行操作的时候,执行了一条update语句,当前他会把id为1的数据锁住,加的是行锁,只要你事务没提交,这个行锁就不会释放。另一个窗口也根据id修改数据,能执行成功,因为窗口1锁的是id为1的数据,窗口二操作的是id为4的数据,索引能操作成功。然后两个窗口都提交事务。
现在开启了两个窗口,同时begin一个事务,一个窗口执行根据name修改数据。当我们执行这一条根据name修改数据的语句的时候,它将会把name="PHP"的这行数据锁住。然后在开启第二个窗口的事务,然后根据id为4更新数据。根据我们之前的分析,窗口1锁住的是na=“PHP”的这行数据,窗口二更新的是id为4的数据,应该是可以成功的,但是没有成功。原因是因为此时在执行update语句的时候,name这个字段没有索引,此时加的就不是行锁了,而是表锁。所以窗口1加表锁,窗口2就执行失败了(一直在那儿等着),只有等窗口1提交了,锁被释放了,窗口2才能执行成功。
所以我们在更新数据的时候一定要根据索引字段来更新。
测试:给name建立索引,然后执行相同的sql语句,发现和第一种情况一样,执行成功了,加的是行锁。
(2)update语句在执行的时候需要规避的问题:
执行update语句的时候,我们一定要根据索引字段进行更新,否则就会出现行锁升级为表锁,锁住整张表。一旦锁表了,我们的并发性能就会降低。
在InnoDB的引擎当中,行锁是针对索引加的锁,不是针对记录加的锁。所以我们在执行更新的时候,更新的条件一定要有索引,如果没有索引,就会出现行锁升级为表锁,并且索引不能失效,否则行锁也会升级为表锁。一旦升级为表锁,我们的并发性能就会降低。