MySQL 优化：优化和索引

来自Wikioe
Eijux（讨论 | 贡献）2021年4月26日 (一) 14:21的版本 （→‎并发插入）
跳到导航 跳到搜索


关于

MySQL 使用locking管理表内容的争用：

  1. 内部锁定：在 MySQL 服务器本身内部执行，以管理多个线程对表内容的争用。这种类型的锁定是内部锁定，因为它完全由服务器执行，并且不涉及其他程序。
  2. 外部锁定：当服务器和其他程序锁定 MyISAM 表文件以在它们之间协调哪个程序可以在何时访问表时，就会发生外部锁定。

内部锁定方法

Row-Level Locking（行级锁）

MySQL 使用行级锁到 InnoDB 表来支持多个会话的同时写访问，从而使其适用于多用户，高并发和 OLTP 应用程序。


为避免在单个 InnoDB 表上执行多个并发写操作时出现deadlocks（死锁）：

  1. 请在事务开始时通过为预期要修改的每行行发出“SELECT ... FOR UPDATE”语句来获取必要的锁，即使数据更改语句稍后在事务中。
  2. 如果事务修改或锁定了多个表，请在每个事务中以相同顺序发出适用的语句。

死锁会影响性能，而不是代表严重的错误，因为 InnoDB 自动检测死锁条件并回滚受影响的事务之一。


在高并发系统上，当多个线程 await 相同的锁时，死锁检测会导致速度变慢。有时，禁用死锁检测并在发生死锁时依靠“innodb_lock_wait_timeout”设置进行事务回滚可能会更有效。可以使用“innodb_deadlock_detect”配置选项禁用死锁检测。


行级锁定的优点：

  1. 当不同的会话访问不同的行时，锁冲突减少。
  2. 回滚更改较少。
  3. 可以长时间锁定单个行。

Table-Level Locking（表级锁）

MySQL 将表级锁用于 MyISAM，MEMORY 和 MERGE 表，一次只允许一个会话更新这些表。

  • 此锁定级别使这些存储引擎更适合于只读，只读为主或单用户应用程序。


这些存储引擎通过始终在查询开始时一次请求所有必需的锁并始终以相同 Sequences 锁定表来避免deadlocks。权衡是该策略减少了并发性。其他要修改表的会话必须 await，直到当前数据更改语句完成为止。


表级锁定的优点：

  1. 所需的内存相对较少（行锁定需要锁定每行或每组行的内存）
  2. 在表的大部分上使用时非常快，因为只涉及一个锁。
  3. 如果您经常对大部分数据执行“GROUP BY”操作，或者必须经常扫描整个表，则速度很快。


MySQL 授予表 写锁，如下所示：

  1. 如果表上没有锁，请在其上放置写锁。【？】
  2. 否则，将锁定请求放入写锁定队列中。

MySQL 授予表读锁，如下所示：

  1. 如果表上没有写锁，请在其上放置一个读锁。
  2. 否则，将锁定请求放入读取锁定队列中。

表更新的优先级高于表检索。因此，释放锁时，该锁可用于写锁队列中的请求，然后可用于读锁队列中的请求。这样可以确保即使表有大量的 SELECT 活动，对表的更新也不会“饿死”。但是，如果一个表有很多更新，则 SELECT 语句将等待知道没有更新。【写锁优先于读锁】


您可以通过检查状态变量“Table_locks_immediate”和“Table_locks_waited”来分析系统上的表锁争用，这两个变量分别指示可以立即授予表锁请求的次数和必须 await 的次数：【？？？】 

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited    | 15324   |
+-----------------------+---------+


MyISAM 存储引擎支持并发插入，以减少给定表的读取器和写入器之间的争用：如果 MyISAM 表在数据文件的中间没有空闲块，则始终在数据文件的末尾插入行。【？？？还能这么搞？】

  • 在这种情况下，您可以为 MyISAM 表自由混合并发的“INSERT”和“SELECT”语句而无需锁定。也就是说，您可以在其他 Client 端读取 MyISAM 表的同时插入行。
    • 从表的中间删除或更新的行可能会导致漏洞。如果有漏洞，将禁用并发插入，但是当所有孔都已填充新数据时，并发插入会自动重新启用。若要控制此行为，请使用“concurrent_insert”系统变量。


如果您使用“LOCK TABLES”显式获取表锁，则可以请求“READ LOCAL”锁而不是“READ”锁，以使其他会话在锁定表的同时执行并发插入。【？？？“READ LOCAL”和“READ”？？？没找到资料啊？？？】


要在无法同时插入表 t1 时对表 t1 执行许多“INSERT”和“SELECT”操作，可以将行插入到临时表 temp_t1 中，并使用临时表中的行更新实际表： 

mysql> LOCK TABLES t1 WRITE, temp_t1 WRITE;
mysql> INSERT INTO t1 SELECT * FROM temp_t1;
mysql> DELETE FROM temp_t1;
mysql> UNLOCK TABLES;

选择锁定类型

通常，在以下情况下，表锁优于行级锁：【！！！！！】

  • 该表的大多数语句均为读取。
  • 该表的语句是读和写的混合，其中写是对单行的更新或删除，可通过一次按键读取来获取： 
    UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
DELETE FROM tbl_name WHERE unique_key_col=key_value;
    “SELECT”与并发的“INSERT”语句结合使用，很少有“UPDATE”或“DELETE”语句。
  • 整个表上有许多扫描或“GROUP BY”操作，没有任何写入。

使用高级锁，您可以通过支持不同类型的锁来更轻松地调整应用程序，因为锁开销比行级锁要少。


行级锁定以外的选项：

  • 版本控制（例如在 MySQL 中用于并发插入的版本控制），可以同时拥有一个编写者和多个阅 Reader。这意味着数据库或表根据访问开始的时间支持数据的不同视图。其他常用术语是“时间旅行”，“写时复制”或“按需复制”。【即：MVCC】
  • 在许多情况下，按需复制优于行级锁定。但是，在最坏的情况下，与使用普通锁相比，它可以使用更多的内存。【？】
  • 除了使用行级锁，您还可以使用应用程序级锁，例如 MySQL 中的“GET_LOCK()”和“RELEASE_LOCK()”提供的锁。这些是咨询锁，因此它们仅适用于相互协作的应用程序。

表锁定问题

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


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

性能方面的考虑有利于 InnoDB

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

  1. 表锁定使许多会话可以同时从一个表读取，但是如果一个会话要写入一个表，则必须首先获得互斥访问，这意味着它可能必须 await 其他会话首先完成该表。在更新期间，要访问此特定表的所有其他会话都必须 await，直到更新完成。
  2. 在会话 await 期间，表锁定会导致问题，因为磁盘已满，会话开始前需要释放可用空间。在这种情况下，所有要访问问题表的会话也将处于 await 状态，直到有更多磁盘空间可用为止。
  3. 花费很长时间运行的 SELECT 语句会阻止其他会话同时更新该表，从而使其他会话显得缓慢或无响应。当会话正在 await 获取表的独占访问权以进行更新时，其他发出 SELECT 语句的会话将在其后排队，从而即使对于只读会话也降低了并发性。

锁定性能问题的变通办法

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

  • 考虑在安装过程中使用“CREATE TABLE ... ENGINE=INNODB”或将“ALTER TABLE ... ENGINE=INNODB”用于现有表将表切换到 InnoDB 存储引擎。
  • 优化“SELECT”语句以使其运行更快，以便它们在较短的时间内锁定表。您可能必须创建一些汇总表才能执行此操作。
  • 以“--low-priority-updates”开头 mysqld。对于仅使用表级锁定的存储引擎(例如 MyISAM，MEMORY 和 MERGE)，这使所有更新（修改）表的语句的优先级低于“SELECT”语句。在这种情况下，前面场景中的第二个“SELECT”语句将在“UPDATE”语句之前执行，而不会 await 第一个“SELECT”完成。
  • 若要指定应以低优先级完成在特定 Connecting 发布的所有更新，请将“low_priority_updates”服务器系统变量设置为等于 1。
  • 若要给特定的“INSERT”，“UPDATE”或“DELETE”语句较低的优先级，请使用“LOW_PRIORITY”属性。
  • 要给特定的“SELECT”语句更高的优先级，请使用“HIGH_PRIORITY”属性。
  • 以“max_write_lock_count”系统变量的低值开始 mysqld，以强制 MySQL 在对表进行特定次数的插入之后暂时提高所有 await 表的“SELECT”语句的优先级。这允许一定数量的 WRITE锁 之后的 READ锁。
  • 如果您在混合使用“SELECT”和“DELETE”语句时遇到问题，则“DELETE”的“LIMIT”选项可能会有所帮助。
  • 将“SQL_BUFFER_RESULT与“SELECT”语句一起使用可以帮助缩短表锁定的持续时间。
  • 通过允许对一个表中的列运行查询，而将更新限制在另一个表中的列，将表内容拆分为单独的表可能会有所帮助。
  • 您可以在“mysys/thr_lock.c”中更改锁定代码以使用单个队列。在这种情况下，写锁和读锁将具有相同的优先级，这可能对某些应用程序有所帮助。【？】

并发插入

MyISAM 存储引擎支持并发插入，以减少给定表的读写器之间的争用：如果 MyISAM 表在数据文件中没有孔（中间已删除的行），则可以执行“INSERT”语句以将行添加到末尾“SELECT”语句从表中读取行的同时显示表的数据。如果有多个“INSERT”语句，它们将与“SELECT”语句同时排队并按 Sequences 执行。并发“INSERT”的结果可能不会立即可见。


可以设置“concurrent_insert”系统变量以修改并发插入处理。默认情况下，该变量设置为“AUTO”（或 1），并按上述方式处理并发插入。如果“concurrent_insert”设置为“NEVER”（或 0），则会禁用并发插入。如果变量设置为“ALWAYS”（或 2），则即使对于已删除行的表，也允许在表末尾进行并发插入。


如果使用二进制日志，则并发插入将转换为“CREATE ... SELECT”或“INSERT ... SELECT”语句的普通插入。这样做是为了确保您可以通过在备份操作期间应用日志来重新创建表的精确副本。此外，对于那些语句，在从中选择的表上放置了一个读取锁，从而阻止了对该表的插入。结果是该表的并发插入也必须 await。


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


如果指定“HIGH_PRIORITY”，并且服务器使用该选项启动时，它将覆盖“--low-priority-updates”选项的效果。这还会导致不使用并发插入。


对于“LOCK TABLE”，“READ LOCAL”和“READ”的区别在于：“READ LOCAL”允许在保持锁的同时执行无冲突的“INSERT”语句（并发插入）。但是，如果您要在持有锁的同时使用服务器外部的进程来操作数据库，则无法使用此功能。【？？？】

元数据锁定

外部锁定

取自“http://wiki.eijux.com/index.php?title=MySQL_优化：优化和索引&oldid=2712”