MySQL 优化：优化和索引

来自Wikioe
跳到导航 跳到搜索


关于

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”语句（并发插入）。但是，如果您要在持有锁的同时使用服务器外部的进程来操作数据库，则无法使用此功能。【？？？】

元数据锁定

MySQL 使用元数据锁定来管理对数据库对象的并发访问并确保数据一致性。

  • 元数据锁定不仅适用于表，而且还适用于架构，存储的程序（过程，函数，触发器，计划的事件），表空间，通过“GET_LOCK()”函数获取的用户锁。
  • Performance Schema “metadata_locks”表公开了元数据锁定信息，这对于查看哪些会话持有锁定，被阻止 await 锁定等很有用。
  • 元数据锁定确实涉及一些开销，随着查询量的增加而增加。多个查询尝试访问同一对象的情况越多，元数据争用就会增加。
  • 元数据锁定不能代替表定义高速缓存，并且其互斥锁和锁定与“LOCK_open”互斥锁不同。【？】

元数据锁定获取

如果给定锁有多个等待着，则首先满足最高优先级的锁请求，但与“max_write_lock_count”系统变量有关的异常除外。写锁定请求比读锁定请求具有更高的优先级。但是，如果“max_write_lock_count”设置为某个较低的值（例如 10），则如果已将读取锁定请求传递给了 10 个写入锁定请求，则读取锁定请求可能比挂起的写入锁定请求优先。【通常，因为“max_write_lock_count”默认具有非常大的值，所以不会发生此现象】

  • 语句逐个（而不是同时）获取元数据锁，并在此过程中执行死锁检测。
  • DML 语句通常以在语句中提到表的顺序获取锁。
  • DDL 语句“LOCK TABLES”和其他类似的语句通过按名称顺序获取对显式命名的表的锁，试图减少并发 DDL 语句之间可能出现的死锁。对于隐式使用的表（例如，具有外键关系的表也必须被锁定），可以以不同的顺序获取锁。
    例如，“RENAME TABLE”是按名称 Sequences 获取锁的 DDL 语句：
    此 RENAME TABLE 语句将 tbla 重命名为其他名称，并将 tblc 重命名为 tbla：
    RENAME TABLE tbla TO tbld, tblc TO tbla;
    该语句按顺序获取 tbla，tblc 和 tbld 上的元数据锁（因为 tbld 按照名称顺序在 tblc 之后）：
    这个稍有不同的语句还将 tbla 重命名为其他名称，并将 tblc 重命名为 tbla：
    RENAME TABLE tbla TO tblb, tblc TO tbla;
    在这种情况下，该语句按顺序获取 tbla，tblb 和 tblc 上的元数据锁（因为tblb在名称顺序上先于tblc）：
    这两个语句都按此顺序获取 tbla 和 tblc 上的锁，但是不同之处在于是否在 tblc 之前或之后获取了对剩余表名的锁定。


当多个事务同时执行时，元数据锁获取顺序可能会在操作结果上产生差异。


示例1：

从两个具有相同结构的表 x 和 x_new 开始。三个 Client 发出涉及这些表的语句：

Client 1: 

LOCK TABLE x WRITE, x_new WRITE;
该语句按名称顺序在 x 和 x_new 上请求并获取写锁定。

Client 2: 

INSERT INTO x VALUES(1);
该语句请求并阻塞等待 x 上的写锁定。

Client 3: 

RENAME TABLE x TO x_old, x_new TO x;
该语句按名称顺序请求 x，x_new 和 x_old 上的互斥锁，但阻塞等待 x 上的锁。【DDL 语句按名称顺序：x，x_new 和 x_old】

Client 1: 

UNLOCK TABLES;
该语句释放对 x 和 x_new 的写锁定。Client 3 端对 x 的排他锁定请求具有比 Client 2 端的写锁定请求更高的优先级【？？表锁优先于行锁？？】，因此 Client 3 端在 x 上获得其锁定，然后在 x_new 和 x_old 上获得其锁定，执行重命名并释放其锁定。然后，Client 2 端获得对 x 的锁定，执行插入操作，然后释放其锁定。

锁定获取顺序导致“RENAME TABLE”在“INSERT”之前执行。发生插入的 x 是当 Client 2 端发出插入时被命名为 x_new 的表，并被 Client 3 端重命名为 x。 

mysql> SELECT * FROM x;
+------+
| i    |
+------+
|    1 |
+------+

mysql> SELECT * FROM x_old;
Empty set (0.01 sec)

示例2：【示例1：x，x_new 和 x_old 三个表。示例二：x，new_x，old_x】

现在从具有相同结构的名为 x 和 new_x 的表开始。同样，三个 Client 发出涉及这些表的语句：

Client 1: 

LOCK TABLE x WRITE, new_x WRITE;
该语句按名称顺序在 new_x 和 x 上请求并获取写锁定。

Client 2: 

INSERT INTO x VALUES(1);
该语句请求并阻塞等待 x 上的写锁定。

Client 3: 

RENAME TABLE x TO old_x, new_x TO x;
该语句按名称顺序请求 new_x，old_x 和 x 上的互斥锁，但阻塞等待 new_x 上的锁。【DDL 语句按名称顺序：new_x，old_x 和 x】

Client 1: 

UNLOCK TABLES;
该语句释放对 x 和 new_x 的写锁定。对于 x，唯一未决的请求是 Client 2 端发出的，因此 Client 2 端获得其锁，执行插入操作，然后释放该锁。对于 new_x，唯一待处理的请求是 Client 3 端允许其获取该锁（以及 old_x 的锁）。重命名操作仍然会阻塞 x 上的锁定，直到 Client 2 端插入完成并释放其锁定为止。然后， Client 3 端获取 x 上的锁，执行重命名，然后释放其锁。

在这种情况下，锁定获取顺序导致“INSERT”在“RENAME TABLE”之前执行。插入的 x 是原始 x，现在已通过重命名操作重命名为 old_x： 

mysql> SELECT * FROM x;
Empty set (0.01 sec)

mysql> SELECT * FROM old_x;
+------+
| i    |
+------+
|    1 |
+------+


如前例所示，如果并发语句中锁获取的顺序对应用程序的操作结果有所不同，则可以调整表名以影响锁获取的顺序。

元数据锁定释放

为了确保事务可串行化，服务器不得允许一个会话对在另一会话中未完成的显式或隐式启动的事务中使用的表执行数据定义语言（DDL）语句。服务器通过“获取在事务中使用的表上的元数据锁，并将这些锁的释放推迟到事务结束之前”来实现。表上的元数据锁可防止更改表的结构。

  • 这种锁定方法意味着，在事务结束之前，一个会话中的事务正在使用的表不能被其他会话在 DDL 语句中使用。


该原理不仅适用于事务表，而且还适用于非事务表。假设会话开始使用事务表 t 和非事务表 nt 的事务，如下所示： 

START TRANSACTION;
SELECT * FROM t;
SELECT * FROM nt;

服务器在 t 和 nt 上都保留元数据锁，直到事务结束。如果另一个会话在任一表上尝试 DDL 或写锁定操作，它将阻塞，直到在事务结束释放元数据锁定为止。

例如，如果第二个会话尝试执行以下任何操作，则它将阻塞：
DROP TABLE t;
ALTER TABLE t ...;
DROP TABLE nt;
ALTER TABLE nt ...;
LOCK TABLE t ... WRITE;


  • “LOCK TABLES ... READ”的行为相同。即，更新任何表（事务性或非事务性）的显式或隐式启动的事务将对该表进行阻塞并被“LOCK TABLES ... READ”阻塞。
  • 如果服务器获取语法上有效但在执行过程中失败的语句的元数据锁，则它不会尽早释放该锁。锁定释放仍被推迟到事务结束，因为失败的语句被写入二进制日志，并且锁定保护了日志的一致性。
  • 在自动提交模式下，每个语句实际上是一个完整的事务，因此为该语句获取的元数据锁仅保留到该语句的末尾。
  • 一旦准备好语句，即使在多语句事务中进行准备，也会释放在“PREPARE”语句期间获取的元数据锁。【！】

外部锁定【针对 MyISAM？？？】

外部锁定是使用文件系统锁定来管理多个进程对 MyISAM 数据库表的争用。在无法将单个进程（例如 MySQL 服务器）假定为唯一需要访问表的进程的情况下，可以使用外部锁定。这里有些例子：

  1. 如果运行使用相同数据库目录的多个服务器（不建议），则每个服务器必须启用了外部锁定。
  2. 如果使用 myisamchk 对 MyISAM 表执行表维护操作，则必须确保服务器未在运行，或者服务器启用了外部锁定，以便根据需要锁定表文件以与 myisamchk 协作以访问表。使用 myisampack 打包 MyISAM 表也是如此。

如果服务器在启用了外部锁定的情况下运行，则可以随时使用 myisamchk 进行诸如检查表之类的读取操作。在这种情况下，如果服务器尝试更新 myisamchk 正在使用的表，则服务器将等待 myisamchk 完成后再继续。


如果使用 myisamchk 进行修复或优化表之类的写操作，或者如果使用 myisampack 打包表，则必须始终确保 mysqld 服务器未使用该表。如果不停止 mysqld，则至少应先运行“mysqladmin flush-tables”，然后再运行 myisamchk。如果服务器和 myisamchk 同时访问表，则表可能已损坏。


启用外部锁定后，每个需要访问表的进程都将在继续访问表之前获取表文件的文件系统锁。如果无法获取所有必需的锁，则会阻止该进程访问表，直到获得锁为止（在当前持有锁的进程释放它们之后）。


外部锁定会影响服务器性能，因为服务器有时必须等待其他进程才能访问表。


如果您运行单个服务器来访问给定的数据目录（通常是这种情况），并且在服务器运行时不需要其他程序（例如myisamchk）来修改表，则不需要外部锁定。如果仅通过其他程序“读取”表，则不需要外部锁定，尽管如果 myisamchk 正在读取表时服务器更改了表，则 myisamchk 可能会报告警告。


在禁用外部锁定的情况下，要使用 myisamchk，您必须在 myisamchk 执行时停止服务器，或者在运行 myisamchk 之前锁定并刷新表。为避免此要求，请使用“CHECK TABLE”和“REPAIR TABLE”语句检查和修复 MyISAM 表。

对于 mysqld，外部锁定由“skip_external_locking”系统变量的值控制。启用此变量后，将禁用外部锁定，反之亦然。默认情况下，外部锁定是禁用的。

可以使用“--external-locking”或“--skip-external-locking”选项在服务器启动时控制外部锁定的使用。


如果确实使用外部锁定选项来启用许多 MySQL 进程对 MyISAM 表的更新，则必须确保满足以下条件：

  1. 不要将查询高速缓存用于使用由另一个进程更新的表的查询。
  2. 不要将“delay_key_write”系统变量设置为“ALL”来启动服务器，也不要对任何共享表使用“DELAY_KEY_WRITE=1”表选项。否则，可能会发生索引损坏。

满足这些条件的最简单方法是始终将“--external-locking”与“--delay-key-write=OFF”和“--query-cache-size=0”一起使用。（默认情况下不会进行此操作，因为在许多设置中，混合使用上述选项会很有用）

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