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用于现有表。有关此存储引擎的更多详细信息，请参阅 第 15 章，InnoDB 存储引擎。

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

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

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

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

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

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

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

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

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

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