8.11.2 table 锁定问题
InnoDB
table 使用行级锁定,因此多个会话和应用程序可以同时从同一 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 级锁定的存储引擎(例如
MyISAM
,MEMORY
和MERGE
),这使所有更新(修改)table 的语句的优先级低于SELECT语句。在这种情况下,前面场景中的第二个SELECT语句将在UPDATE语句之前执行,而不会 await 第一个SELECT完成。 -
若要指定应以低优先级完成在特定 Connecting 发布的所有更新,请将low_priority_updates服务器系统变量设置为等于 1.
-
要给特定的SELECT语句更高的优先级,请使用
HIGH_PRIORITY
属性。参见第 13.2.9 节“ SELECT 语句”。 -
以max_write_lock_count系统变量的低值开始mysqld,以强制 MySQL 在对 table 进行特定次数的插入之后暂时提高所有 awaittable 的SELECT语句的优先级。这允许一定数量的
WRITE
锁之后的READ
锁。 -
如果您在混合使用SELECT和DELETE语句时遇到问题,则DELETE的
LIMIT
选项可能会有所帮助。参见第 13.2.2 节“删除声明”。 -
将
SQL_BUFFER_RESULT
与SELECT语句一起使用可以帮助缩短 table 锁定的持续时间。参见第 13.2.9 节“ SELECT 语句”。 -
通过允许对一个 table 中的列运行查询,而将更新限制在另一个 table 中的列,将 table 内容拆分为单独的 table 可能会有所帮助。
-
您可以在
mysys/thr_lock.c
中更改锁定代码以使用单个队列。在这种情况下,写锁和读锁将具有相同的优先级,这可能对某些应用程序有所帮助。