MySQL 优化：优化和索引 - 版本历史

2023年3月31日 (五) 15:17 Eijux

2023-03-31T15:17:12Z

←上一版本		2023年3月31日 (五) 23:17的版本
第1行：		第1行：
	[[category:~~MySQL~~]]		[[category:MySQL文档]]

	== 关于 ==		== 关于 ==

Eijux：/* 外部锁定 */

2021-04-26T07:05:42Z

外部锁定

←上一版本		2021年4月26日 (一) 15:05的版本
第267行：		第267行：
	* 一旦准备好语句，即使在多语句事务中进行准备，也会释放在“PREPARE”语句期间获取的元数据锁。【！】		* 一旦准备好语句，即使在多语句事务中进行准备，也会释放在“PREPARE”语句期间获取的元数据锁。【！】

	== ~~外部锁定~~ ==		== 外部锁定【针对 MyISAM？？？】 ==
			外部锁定是使用文件系统锁定来管理多个进程对 MyISAM 数据库表的争用。在无法将单个进程（例如 MySQL 服务器）假定为唯一需要访问表的进程的情况下，可以使用外部锁定。这里有些例子：
			# 如果运行使用相同数据库目录的多个服务器（不建议），则每个服务器必须启用了外部锁定。
			# 如果使用 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 表的更新，则必须确保满足以下条件：
			# 不要将查询高速缓存用于使用由另一个进程更新的表的查询。
			# 不要将“delay_key_write”系统变量设置为“ALL”来启动服务器，也不要对任何共享表使用“DELAY_KEY_WRITE=1”表选项。否则，可能会发生索引损坏。
			满足这些条件的最简单方法是始终将“--external-locking”与“--delay-key-write=OFF”和“--query-cache-size=0”一起使用。（默认情况下不会进行此操作，因为在许多设置中，混合使用上述选项会很有用）

Eijux：/* 元数据锁定 */

2021-04-26T06:57:02Z

元数据锁定

显示更改

Eijux：/* 并发插入 */

2021-04-26T06:21:27Z

并发插入

@@ 第121行： / 第121行： @@
 == 并发插入 ==
 == 元数据锁定 ==
 == 外部锁定 ==

Eijux：/* 表锁定问题 */

2021-04-26T04:53:25Z

表锁定问题

←上一版本		2021年4月26日 (一) 12:53的版本
第95行：		第95行：

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


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

			=== 性能方面的考虑有利于 InnoDB ===
			选择是使用 InnoDB 还是使用其他存储引擎创建表时，请记住表锁定的以下缺点：
			# 表锁定使许多会话可以同时从一个表读取，但是如果一个会话要写入一个表，则必须首先获得互斥访问，这意味着它可能必须 await 其他会话首先完成该表。在更新期间，要访问此特定表的所有其他会话都必须 await，直到更新完成。
			# 在会话 await 期间，表锁定会导致问题，因为磁盘已满，会话开始前需要释放可用空间。在这种情况下，所有要访问问题表的会话也将处于 await 状态，直到有更多磁盘空间可用为止。
			# 花费很长时间运行的 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”中更改锁定代码以使用单个队列。在这种情况下，写锁和读锁将具有相同的优先级，这可能对某些应用程序有所帮助。【？】

	== 并发插入 ==		== 并发插入 ==

Eijux：建立内容为“category:MySQL == 关于 == MySQL 使用'''locking'''管理表内容的争用： # 内部锁定：'''在 MySQL 服务器本身内部执行'''，以管理多…”的新页面

2021-04-26T04:42:48Z

建立内容为“category:MySQL == 关于 == MySQL 使用'''locking'''管理表内容的争用： # 内部锁定：'''在 MySQL 服务器本身内部执行'''，以管理多…”的新页面

新页面

[[category:MySQL]]

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

== 内部锁定方法 ==
=== Row-Level Locking（行级锁） ===
MySQL 使用行级锁到 InnoDB 表来支持多个会话的同时写访问，从而使其适用于多用户，高并发和 OLTP 应用程序。

为避免在单个 InnoDB 表上执行多个并发写操作时出现deadlocks（死锁）：
# 请在事务开始时通过为预期要修改的每行行发出“SELECT ... FOR UPDATE”语句来获取必要的锁，即使数据更改语句稍后在事务中。
# 如果事务修改或锁定了多个表，请在每个事务中以相同顺序发出适用的语句。
死锁会影响性能，而不是代表严重的错误，因为 InnoDB 自动检测死锁条件并回滚受影响的事务之一。

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

行级锁定的优点：
# 当不同的会话访问不同的行时，锁冲突减少。
# 回滚更改较少。
# 可以长时间锁定单个行。

=== Table-Level Locking（表级锁） ===
MySQL 将表级锁用于 MyISAM，MEMORY 和 MERGE 表，一次只允许一个会话更新这些表。
* 此锁定级别使这些存储引擎更适合于只读，只读为主或单用户应用程序。

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

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

MySQL 授予表 '''写锁'''，如下所示：
# 如果表上没有锁，请在其上放置写锁。【？】
# 否则，将锁定请求放入写锁定队列中。
MySQL 授予表'''读锁'''，如下所示：
# 如果表上没有写锁，请在其上放置一个读锁。
# 否则，将锁定请求放入读取锁定队列中。
表更新的优先级高于表检索。因此，释放锁时，该锁可用于写锁队列中的请求，然后可用于读锁队列中的请求。这样可以确保即使表有大量的 SELECT 活动，对表的更新也不会“饿死”。但是，如果一个表有很多更新，则 SELECT 语句将等待知道没有更新。【'''写锁优先于读锁'''】

您可以通过检查状态变量“'''Table_locks_immediate'''”和“'''Table_locks_waited'''”来分析系统上的表锁争用，这两个变量分别指示可以立即授予表锁请求的次数和必须 await 的次数：【？？？】
'''<syntaxhighlight lang="mysql">
mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+-----------------------+---------+
</syntaxhighlight>'''

MyISAM 存储引擎支持'''并发插入'''，以减少给定表的读取器和写入器之间的争用：'''如果 MyISAM 表在数据文件的中间没有空闲块，则始终在数据文件的末尾插入行'''。【？？？还能这么搞？】
* 在这种情况下，您可以为 MyISAM 表自由混合并发的“INSERT”和“SELECT”语句而'''无需锁定'''。也就是说，您可以在其他 Client 端读取 MyISAM 表的同时插入行。
** 从表的中间删除或更新的行可能会导致漏洞。如果有漏洞，将禁用并发插入，但是当所有孔都已填充新数据时，并发插入会自动重新启用。若要控制此行为，请使用“concurrent_insert”系统变量。

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

要在无法同时插入表 t1 时对表 t1 执行许多“INSERT”和“SELECT”操作，可以将行插入到临时表 temp_t1 中，并使用临时表中的行更新实际表：
<syntaxhighlight lang="mysql">
mysql> LOCK TABLES t1 WRITE, temp_t1 WRITE;
mysql> INSERT INTO t1 SELECT * FROM temp_t1;
mysql> DELETE FROM temp_t1;
mysql> UNLOCK TABLES;
</syntaxhighlight>

=== 选择锁定类型 ===
通常，在以下情况下，表锁优于行级锁：【！！！！！】
* 该表的大多数语句均为读取。
* 该表的语句是读和写的混合，其中写是对单行的更新或删除，可通过一次按键读取来获取：
*: <syntaxhighlight lang="mysql">
UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
DELETE FROM tbl_name WHERE unique_key_col=key_value;
</syntaxhighlight>
*: “SELECT”与并发的“INSERT”语句结合使用，很少有“UPDATE”或“DELETE”语句。
* 整个表上有许多扫描或“GROUP BY”操作，没有任何写入。
使用高级锁，您可以通过支持不同类型的锁来更轻松地调整应用程序，因为锁开销比行级锁要少。

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

== 表锁定问题 ==

== 并发插入 ==

== 元数据锁定 ==

== 外部锁定 ==

MySQL 优化：优化和索引 - 版本历史

2023年3月31日 (五) 15:17 Eijux

Eijux：​/* 外部锁定 */

Eijux：​/* 元数据锁定 */

Eijux：​/* 并发插入 */

Eijux：​/* 表锁定问题 */

Eijux：​建立内容为“category:MySQL == 关于 == MySQL 使用'''locking'''管理表内容的争用： # 内部锁定：'''在 MySQL 服务器本身内部执行'''，以管理多…”的新页面

Eijux：/* 外部锁定 */

Eijux：/* 元数据锁定 */

Eijux：/* 并发插入 */

Eijux：/* 表锁定问题 */

Eijux：建立内容为“category:MySQL == 关于 == MySQL 使用'''locking'''管理表内容的争用： # 内部锁定：'''在 MySQL 服务器本身内部执行'''，以管理多…”的新页面