8.11.2 table 锁定问题

InnoDBtable 使用行级锁定,因此多个会话和应用程序可以同时从同一 table 读取和写入同一 table,而不会彼此 await 或产生不一致的结果。对于此存储引擎,请避免使用LOCK TABLES语句,因为它不提供任何额外的保护,而是减少了并发性。自动行级锁定使这些 table 适合于具有最重要数据的最繁忙数据库,同时由于不需要锁定和解锁 table,还简化了应用程序逻辑。因此,InnoDB存储引擎是 MySQL 中的默认引擎。

MySQL 对除InnoDB之外的所有存储引擎都使用 table 锁定(而不是页面锁定,行锁定或列锁定)。锁定操作本身没有太多的开销。但是,由于一次只能有一个会话可以写入 table,因此要与其他存储引擎一起获得最佳性能,请主要将它们用于经常查询且很少插入或更新的 table。

支持 InnoDB 的性能注意事项

选择是使用InnoDB还是使用其他存储引擎创建 table 时,请记住 table 锁定的以下缺点:

  • table 锁定使许多会话可以同时从一个 table 读取,但是如果一个会话要写入一个 table,则必须首先获得互斥访问,这意味着它可能必须 await 其他会话首先完成该 table。在更新期间,要访问此特定 table 的所有其他会话都必须 await,直到更新完成。

  • 在会话 await 期间,table 锁定会导致问题,因为磁盘已满,会话开始前需要释放可用空间。在这种情况下,所有要访问问题 table 的会话也将处于 await 状态,直到有更多磁盘空间可用为止。

  • 花费很长时间运行的SELECT语句会阻止其他会话同时更新该 table,从而使其他会话显得缓慢或无响应。当会话正在 await 获取 table 的独占访问权以进行更新时,其他发出SELECT语句的会话将在其后排队,从而即使对于只读会话也降低了并发性。

解决性能问题的方法

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

  • 考虑在安装过程中使用CREATE TABLE ... ENGINE=INNODB或将ALTER TABLE ... ENGINE=INNODB用于现有 table 将 table 切换到InnoDB存储引擎。有关此存储引擎的更多详细信息,请参见第 14 章,InnoDB 存储引擎

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

  • --low-priority-updates开头mysqld。对于仅使用 table 级锁定的存储引擎(例如MyISAMMEMORYMERGE),这使所有更新(修改)table 的语句的优先级低于SELECT语句。在这种情况下,前面场景中的第二个SELECT语句将在UPDATE语句之前执行,而不会 await 第一个SELECT完成。

  • 若要指定应以低优先级完成在特定 Connecting 发布的所有更新,请将low_priority_updates服务器系统变量设置为等于 1.

  • 若要给特定的INSERTUPDATEDELETE语句较低的优先级,请使用LOW_PRIORITY属性。

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

  • max_write_lock_count系统变量的低值开始mysqld,以强制 MySQL 在对 table 进行特定次数的插入之后暂时提高所有 awaittable 的SELECT语句的优先级。这允许一定数量的WRITE锁之后的READ锁。

  • 如果您在混合使用SELECTDELETE语句时遇到问题,则DELETELIMIT选项可能会有所帮助。参见第 13.2.2 节“删除声明”

  • SQL_BUFFER_RESULTSELECT语句一起使用可以帮助缩短 table 锁定的持续时间。参见第 13.2.9 节“ SELECT 语句”

  • 通过允许对一个 table 中的列运行查询,而将更新限制在另一个 table 中的列,将 table 内容拆分为单独的 table 可能会有所帮助。

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