MySQL 优化：优化和索引

关于

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()”提供的锁。这些是咨询锁，因此它们仅适用于相互协作的应用程序。