当前位置:首页 > 单片机 > 小林coding
[导读]大家好,我是小林。昨晚在群划水的时候,看到有位读者说了这么一件事。大概就是,在线上执行一条update语句修改数据库数据的时候,where条件没有带上索引,导致业务直接崩了,被老板教训了一波这次我们就来看看:为什么会发生这种的事故?又该如何避免这种事故的发生?说个前提,接下来说的...

大家好,我是小林。

昨晚在群划水的时候,看到有位读者说了这么一件事。

大概就是,在线上执行一条 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]) 可以告诉优化器使用哪个索引。这次就说到这啦,下次要小心点,别再被老板挨骂啦。

本站声明: 本文章由作者或相关机构授权发布,目的在于传递更多信息,并不代表本站赞同其观点,本站亦不保证或承诺内容真实性等。需要转载请联系该专栏作者,如若文章内容侵犯您的权益,请及时联系本站删除。
换一批
延伸阅读

LED驱动电源的输入包括高压工频交流(即市电)、低压直流、高压直流、低压高频交流(如电子变压器的输出)等。

关键字: 驱动电源

在工业自动化蓬勃发展的当下,工业电机作为核心动力设备,其驱动电源的性能直接关系到整个系统的稳定性和可靠性。其中,反电动势抑制与过流保护是驱动电源设计中至关重要的两个环节,集成化方案的设计成为提升电机驱动性能的关键。

关键字: 工业电机 驱动电源

LED 驱动电源作为 LED 照明系统的 “心脏”,其稳定性直接决定了整个照明设备的使用寿命。然而,在实际应用中,LED 驱动电源易损坏的问题却十分常见,不仅增加了维护成本,还影响了用户体验。要解决这一问题,需从设计、生...

关键字: 驱动电源 照明系统 散热

根据LED驱动电源的公式,电感内电流波动大小和电感值成反比,输出纹波和输出电容值成反比。所以加大电感值和输出电容值可以减小纹波。

关键字: LED 设计 驱动电源

电动汽车(EV)作为新能源汽车的重要代表,正逐渐成为全球汽车产业的重要发展方向。电动汽车的核心技术之一是电机驱动控制系统,而绝缘栅双极型晶体管(IGBT)作为电机驱动系统中的关键元件,其性能直接影响到电动汽车的动力性能和...

关键字: 电动汽车 新能源 驱动电源

在现代城市建设中,街道及停车场照明作为基础设施的重要组成部分,其质量和效率直接关系到城市的公共安全、居民生活质量和能源利用效率。随着科技的进步,高亮度白光发光二极管(LED)因其独特的优势逐渐取代传统光源,成为大功率区域...

关键字: 发光二极管 驱动电源 LED

LED通用照明设计工程师会遇到许多挑战,如功率密度、功率因数校正(PFC)、空间受限和可靠性等。

关键字: LED 驱动电源 功率因数校正

在LED照明技术日益普及的今天,LED驱动电源的电磁干扰(EMI)问题成为了一个不可忽视的挑战。电磁干扰不仅会影响LED灯具的正常工作,还可能对周围电子设备造成不利影响,甚至引发系统故障。因此,采取有效的硬件措施来解决L...

关键字: LED照明技术 电磁干扰 驱动电源

开关电源具有效率高的特性,而且开关电源的变压器体积比串联稳压型电源的要小得多,电源电路比较整洁,整机重量也有所下降,所以,现在的LED驱动电源

关键字: LED 驱动电源 开关电源

LED驱动电源是把电源供应转换为特定的电压电流以驱动LED发光的电压转换器,通常情况下:LED驱动电源的输入包括高压工频交流(即市电)、低压直流、高压直流、低压高频交流(如电子变压器的输出)等。

关键字: LED 隧道灯 驱动电源
关闭