mysql8 翻译系列 五十三

10.11.2 表锁定问题

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

除InnoDB外，MySQL对所有存储引擎都使用表锁定（而不是页锁定、行锁定或列锁定）。锁定操作本身的开销并不大。但是，由于在任何时刻只有一个会话可以对表进行写入操作，为了在使用这些其他存储引擎时获得最佳性能，应主要将它们用于查询频繁且很少进行插入或更新操作的表。

• 倾向于InnoDB的性能考量
• 锁定性能问题的解决方法

倾向于InnoDB的性能考量

在选择使用InnoDB还是其他存储引擎创建表时，请记住表锁定存在以下缺点：

• 表锁定允许许多会话同时从表中读取数据，但如果一个会话想要写入表，则必须首先获得排他访问权，这意味着它可能必须等待其他会话先完成对表的操作。在更新期间，所有其他想要访问该特定表的会话都必须等待，直到更新完成。
• 当一个会话因磁盘已满而等待，需要有可用的空闲空间才能继续操作时，表锁定会引发问题。在这种情况下，所有想要访问该问题表的会话也会被置于等待状态，直到有更多磁盘空间可用。
• 运行时间较长的SELECT语句会阻止其他会话在此期间更新表，使得其他会话看起来运行缓慢或无响应。当一个会话等待获得对表的排他访问权以进行更新时，其他发出SELECT语句的会话会在其后面排队，即使是只读会话的并发性也会降低。

锁定性能问题的解决方法

以下介绍一些避免或减少表锁定导致的争用的方法：

• 考虑将表切换到InnoDB存储引擎，可以在创建表时使用CREATE TABLE ... ENGINE=INNODB，对于现有表则使用ALTER TABLE ... ENGINE=INNODB 。有关此存储引擎的更多详细信息，请参见第17章 “InnoDB存储引擎”。
• 优化SELECT语句以使其运行得更快，从而缩短锁定表的时间。可能需要创建一些汇总表来实现这一点。
• 使用--low-priority-updates选项启动mysqld。对于仅使用表级锁定的存储引擎（如MyISAM、MEMORY和MERGE），这会使所有更新（修改）表的语句的优先级低于SELECT语句。在这种情况下，前面场景中的第二条SELECT语句将在UPDATE语句之前执行，并且无需等待第一条SELECT语句完成。
• 要指定特定连接中发出的所有更新都应以低优先级执行，可将low_priority_updates服务器系统变量设置为1。
• 要使特定的INSERT、UPDATE或DELETE语句具有较低的优先级，可使用LOW_PRIORITY属性。
• 要使特定的SELECT语句具有较高的优先级，可使用HIGH_PRIORITY属性。请参见15.2.13节 “SELECT语句”。
• 使用较低的max_write_lock_count系统变量值启动mysqld，这会在对表进行特定数量的写锁操作（例如插入操作）后，强制MySQL临时提升所有等待该表的SELECT语句的优先级。这允许在一定数量的写锁之后进行读锁操作。
• 如果在混合使用SELECT和DELETE语句时遇到问题，DELETE语句的LIMIT选项可能会有所帮助。请参见15.2.2节 “DELETE语句”。
• 在SELECT语句中使用SQL_BUFFER_RESULT可以帮助缩短表锁定的持续时间。请参见15.2.13节 “SELECT语句”。
• 将表内容拆分为单独的表可能会有所帮助，这样查询可以针对一个表中的列运行，而更新操作则局限于另一个表中的列。
• 可以修改mysys/thr_lock.c中的锁定代码以使用单个队列。在这种情况下，写锁和读锁具有相同的优先级，这可能对某些应用程序有帮助。

10.11.3 并发插入

MyISAM存储引擎支持并发插入，以此减少给定表上读写操作之间的争用：如果MyISAM表的数据文件中没有空洞（即中间不存在已删除的行），那么在SELECT语句从表中读取数据的同时，可以执行INSERT语句在表的末尾添加行。如果有多个INSERT语句，它们会被排队并按顺序执行，同时与SELECT语句并发执行。并发INSERT的结果可能不会立即显示出来。

concurrent_insert系统变量可用于修改并发插入的处理方式。默认情况下，该变量设置为AUTO（或1），并发插入的处理方式如上文所述。如果将concurrent_insert设置为NEVER（或0），则会禁用并发插入。如果将该变量设置为ALWAYS（或2），即使表中有已删除的行，也允许在表的末尾进行并发插入。另请参阅concurrent_insert系统变量的说明。

如果使用二进制日志，对于CREATE...SELECT或INSERT...SELECT语句，并发插入会被转换为普通插入。这样做是为了确保在备份操作期间，通过应用日志能够精确地重新创建表的副本。请参见7.4.4节 “二进制日志”。此外，对于这些语句，会在被选择的表上放置读锁，从而阻止对该表的插入操作。其结果是，对该表的并发插入也必须等待。

使用LOAD DATA时，如果对满足并发插入条件（即中间不包含空闲块）的MyISAM表指定CONCURRENT，那么在执行LOAD DATA时，其他会话可以从该表检索数据。即使没有其他会话同时使用该表，使用CONCURRENT选项也会对LOAD DATA的性能产生一定影响。

如果指定HIGH_PRIORITY，若服务器是使用--low-priority-updates选项启动的，那么HIGH_PRIORITY会覆盖该选项的效果，并且也会导致不使用并发插入。

对于LOCK TABLE，READ LOCAL和READ之间的区别在于，持有READ LOCAL锁时，允许不冲突的INSERT语句（即并发插入）执行。但是，如果在持有锁的同时要使用服务器外部的进程来操作数据库，则不能使用READ LOCAL锁。