MySQL 8.0 参考手册  / 第8章优化  / 8.11 优化锁定操作  /  8.11.2 表锁定问题

8.11.2 表锁定问题

InnoDB表使用行级锁定,以便多个会话和应用程序可以同时读取和写入同一个表,而不会相互等待或产生不一致的结果。对于这种存储引擎,避免使用该LOCK TABLES语句,因为它没有提供任何额外的保护,反而降低了并发性。自动行级锁定使这些表适用于拥有最重要数据的最繁忙的数据库,同时还简化了应用程序逻辑,因为您不需要锁定和解锁表。因此, InnoDB存储引擎是 MySQL 中的默认引擎。

MySQL 对除 InnoDB. 锁定操作本身没有太多开销。但是因为在任何时候只有一个会话可以写入一个表,为了使用这些其他存储引擎获得最佳性能,主要将它们用于经常查询但很少插入或更新的表。

有利于 InnoDB 的性能考虑

在选择是否使用 InnoDB或不同的存储引擎创建表时,请记住表锁定的以下缺点:

  • 表锁定使多个会话可以同时从一个表中读取,但如果一个会话想要写入一个表,它必须首先获得独占访问权限,这意味着它可能必须等待其他会话先完成对表的操作。在更新期间,所有其他想要访问该特定表的会话必须等到更新完成。

  • 当会话正在等待时,表锁定会导致问题,因为磁盘已满并且在会话可以继续之前需要可用空间。在这种情况下,所有想要访问问题表的会话也将处于等待状态,直到有更多磁盘空间可用。

  • SELECT运行时间较长 的语句会阻止其他会话同时更新表,从而使其他会话显得缓慢或无响应。当一个会话等待获得对表的独占访问权以进行更新时,其他发出 SELECT语句的会话在它后面排队,即使是只读会话也会降低并发性。

锁定性能问题的解决方法

以下各项描述了一些避免或减少由表锁定引起的争用的方法:

  • 考虑将表切换到 InnoDB存储引擎,要么 CREATE TABLE ... ENGINE=INNODB在设置期间使用,要么ALTER TABLE ... ENGINE=INNODB用于现有表。有关此存储引擎的更多详细信息,请参阅 第 14 章,InnoDB 存储引擎。

  • 优化SELECT语句以更快地运行,以便它们锁定表的时间更短。您可能必须创建一些汇总表才能执行此操作。

  • 启动mysqld--low-priority-updates。_ 对于仅使用表级锁定(例如 、 和 )的存储引擎 MyISAMMEMORYMERGE使所有更新(修改)表的语句的优先级低于 SELECT语句。SELECT 在这种情况下,前面场景中的第二条语句将在该UPDATE语句之前执行,并且不会等待第一 条语句SELECT完成。

  • 要指定在特定连接中发出的所有更新都应以低优先级完成,请将 low_priority_updates 服务器系统变量设置为 1。

  • 要赋予特定INSERTUPDATE、 或 DELETE语句较低的优先级,请使用该LOW_PRIORITY 属性。

  • 要赋予特定SELECT 语句更高的优先级,请使用该 HIGH_PRIORITY属性。请参阅 第 13.2.9 节,“SELECT 语句”

  • 以较低的系统变量值 启动mysqldmax_write_lock_count , 以强制 MySQL 在对表发生特定数量的写锁定后临时提升所有SELECT 等待表的语句的优先级(例如,对于插入操作)。这允许在一定数量的写锁之后读锁。

  • 如果您在同一个非事务表上混合插入和删除,INSERT DELAYED可能会有所帮助。请参阅 第 13.2.5.3 节,“插入延迟语句”

    笔记

    INSERT DELAYED已弃用;希望在未来的版本中将其删除。使用INSERT(without DELAYED) 代替。

  • 如果您对混合 SELECTDELETE语句有疑问, LIMIT选项 DELETE可能会有所帮助。请参阅 第 13.2.2 节,“DELETE 语句”

  • 使用SQL_BUFFER_RESULTwith SELECT语句有助于缩短表锁定的持续时间。请参阅 第 13.2.9 节,“SELECT 语句”

  • 将表内容拆分为单独的表可能会有所帮助,因为它允许查询针对一个表中的列运行,而更新仅限于不同表中的列。

  • 您可以更改锁定代码 mysys/thr_lock.c以使用单个队列。在这种情况下,写锁和读锁将具有相同的优先级,这可能有助于某些应用程序。