一次MySQL更新操作导致死锁问题的处理过程：
MySQL更新使用二级索引字段导致死锁问题分析，https://blog.csdn.net/a82514921/article/details/104616763
1. MySQL文档说明
1.1 InnoDB死锁
https://dev.mysql.com/doc/refman/5.6/en/innodb-deadlocks.html
死锁是指不同的事务都无法继续的情况，由于每个事务都持有另一个需要的锁导致。因为两个事务都在等待资源可用，所以它们都不会释放它所拥有的锁。
当事务锁定多个表中的行（通过如UPDATE或SELECT ... FOR UPDATE之类的语句），但是顺序相反时，会发生死锁。当语句锁定索引记录和间隙的范围时，也会发生死锁，由于时间问题，每个事务都获得了一些锁，但没有其他锁。
死锁的发生与事务隔离级别无关，因为事务隔离级别改变读操作的行为，而死锁是由于写操作导致的。
当死锁发生时，InnoDB会检测到该情况并回滚其中一个事务（受害者）。因此，即使应用程序逻辑正确，也必须处理需要重试事务的情况。
使用“SHOW ENGINE INNODB STATUS”命令，可以查看InnoDB用户最后发生的死锁。如果频繁出现死锁突出显示事务结构或应用程序错误处理问题，请在启用innodb_print_all_deadlocks设置的情况下运行，以便将有关所有死锁的信息打印到mysqld错误日志中。
1.2 InnoDB死锁检测与回滚
https://dev.mysql.com/doc/refman/5.6/en/innodb-deadlock-detection.html
InnoDB自动检测事务死锁，并回滚一个或多个事务以打破死锁。
InnoDB尝试将更小的事务回滚，事务的大小是由插入/更新/删除涉及的行数决定的。
1.3 避免并处理死锁
https://dev.mysql.com/doc/refman/5.6/en/innodb-deadlocks-handling.html
死锁是事务数据库中的一个典型问题，但并不危险，除非死锁出现得过于频繁以至于根本无法运行事务。通常应当使应用程序支持，当事务由于死锁而回滚时，始终准备重新发起事务。
InnoDB使用自动行级锁。即使只是插入或删除单行的事务，也可能会遇到死锁。那是因为这些操作并非真正的“原子”;它们会自动设置对插入或删除的行的（可能是多个）索引记录的锁定。
可以使用以下技术处理死锁并降低其发生的可能性：
l 在任何时候，使用“SHOW ENGINE INNODB STATUS”命令以确定最近死锁的原因。这可以帮助调整应用程序以避免死锁。
l 如果频繁出现死锁警告，通过启用innodb_print_all_deadlocks配置选项来收集更多的调试信息。有关每个死锁的信息，而不仅仅是最新的死锁，都记录在MySQL错误日志中。完成调试后禁用此选项。
l 如果由于死锁而失败，始终准备重新发起事务。死锁并不危险，再试一次。
l 保持事务小且持续时间短，以它们不易发生冲突。
l 在进行一组相关更改后立即提交事务，以使它们不易发生冲突。特别是，不要使用未提交的事务使交互式mysql会话长时间保持打开状态。
l 如果使用锁定读取（SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE），尝试使用较低的事务隔离级别，例如READ COMMITTED。
l 在事务中修改多个表或同一个表中的不同行集时，每次都以一致的顺序执行这些操作。然后事务可以形成定义良好的队列，不会死锁。
l 在表中添加精心选择的索引。然后，查询需要扫描更少的索引记录，从而设置更少的锁。使用EXPLAIN SELECT确定MySQL服务器认为哪些索引最适合用于查询。
l 使用较少的锁定。如果您能够允许SELECT从旧快照返回数据，请不要在其中添加FOR UPDATE或LOCK IN SHARE MODE子句。在这里使用READ COMMITTED隔离级别很好，因为同一事务中的每个一致读取都从其自己的新快照读取。
l 如果没有其他方法有帮助，请使用表级锁定序列化事务。将LOCK TABLES用于事务表（如InnoDB表）的正确方法是使用SET autocommit = 0（不是START TRANSACTION）后使用LOCK TABLES开始事务，并且在显式提交事务之前不调用UNLOCK TABLES。表级锁可防止对表的并发更新，从而避免死锁，但代价是对繁忙系统的响应性较低。
l 序列化事务的另一种方法是创建一个只包含一行的辅助“信号量”表。让每个事务在访问其他表之前更新该行。这样，所有事务都以串行方式发生。请注意，InnoDB即时死锁检测算法在这种情况下也适用，因为序列化锁是一个行级锁。使用MySQL表级锁定时，必须使用超时方法来解决死锁。
1.4 InnoDB锁
1.4.1 共享锁与排它锁
https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html#innodb-shared-exclusive-locks
InnoDB实现了标准的行级锁，其中有两种类型的锁，共享（S）锁和独占（X）锁。
共享（S）锁允许持有锁的事务读取行。
独占（X）锁允许持有锁的事务更新或删除行。
如果事务T1在行r上持有共享（S）锁，则来自某个不同事务T2的对行r的锁的请求按如下方式处理：
可以立即授予T2对S锁的请求。结果为T1和T2都在r上保持S锁定。
T2的X锁定请求不能立即授予。
如果事务T1在行r上持有独占（X）锁定，则不能立即授予来自某个不同事务T2的对r上任一类型的锁的请求。相反，事务T2必须等待事务T1释放其对行r的锁定。
https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_exclusive_lock
排它锁是一种可以防止任何其他事务锁定同一行的锁。根据事务隔离级别，这种锁可能会阻止其他事务写入同一行，或者也可能阻止其他事务读取同一行。默认的InnoDB事务隔离级别REPEATABLE READ通过允许事务读取具有独占锁的行来实现更高的并发性，这种技术称为一致读取。
1.4.2 记录锁
https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html#innodb-record-locks
记录锁是对索引记录的锁定。例如，“SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;”操作会防止任何其他事务对t.c1的值为10的行的插入、更新或删除操作。
记录锁始终锁定索引记录，即使数据库表未定义索引。在上述情况下，InnoDB会创建一个隐藏的聚簇索引并使用该索引用于记录锁定。
记录锁的事务数据在SHOW ENGINE INNODB STATUS和InnoDB监控器输出中显示类似于以上内容。
1.5 InnoDB行级锁
https://dev.mysql.com/doc/refman/5.6/en/internal-locking.html#internal-row-level-locking
MySQL使用InnoDB表的行级锁来支持多个会话的同时写访问，使其适用于多用户，高度并发和OLTP（On-Line Transaction Processing，联机事务处理过程）应用程序。
行级锁的优点如下：
l 当不同的会话访问不同的行时，产生的锁冲突更少。
l 回滚的变化较少。
l 可以长时间锁定单行。
1.6 LOCK TABLES与UNLOCK TABLES语法
https://dev.mysql.com/doc/refman/5.6/en/lock-tables.html
| LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ... lock_type: { READ [LOCAL] | [LOW_PRIORITY] WRITE } UNLOCK TABLES |
MySQL使客户端会话能够显式获取表锁，以便与其他会话协作以访问表，或者防止其他会话在会话需要对其进行独占访问期间修改表。会话只能为自己获取或释放锁。一个会话无法获取另一个会话的锁或释放另一个会话持有的锁。
1.6.1 表锁获取
要在当前会话中获取表锁，请使用LOCK TABLES语句来获取元数据锁。
读锁：
l 持有锁的会话可以读取表（但不能写入）。
l 多个会话可以同时获取表的READ锁。
l 其他会话可以在不明确获取READ锁的情况下读取该表。
写锁：
l 持有锁的会话可以读写表。
l 只有持有锁的会话才能访问该表。在释放锁之前，没有其他会话可以访问它。
l 在保持WRITE锁定时，其他会话阻止对表的请求。
1.6.2 表锁释放
当释放会话持有的表锁时，它们都会同时释放。会话可以显式释放其锁，或者可以在某些条件下隐式释放锁。
l 会话可以使用UNLOCK TABLES显式释放其锁。
l 如果会话发出LOCK TABLES语句以在已经持有锁的同时获取锁，则在授予新锁之前将隐式释放其现有锁。
l 如果会话开始事务（例如，使用START TRANSACTION），则执行隐式UNLOCK TABLES，这会导致释放现有锁。
1.7 InnoDB中不同SQL语句设置的锁
https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html
锁定读取，UPDATE或DELETE通常会在处理SQL语句时扫描的每个索引记录上设置记录锁定。在语句中是否存在排除该行的WHERE条件并不重要。InnoDB不记得确切的WHERE条件，只知道扫描了哪些索引范围。
事务隔离级别也可以影响设置的锁。
如果在搜索中使用了二级索引，并且要设置的索引记录锁是排它的，InnoDB还会检索相应的聚簇索引记录并对它们设置锁。
如果没有适合语句的索引，并且MySQL必须扫描整个表来处理该语句，则表的每一行都会被锁定，这反过来会阻止其他用户对表的所有插入。创建好的索引非常重要，这样查询就不会不必要地扫描很多行。
1.8 InnoDB事务隔离级别
https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html
事务隔离是数据库处理的基础之一。隔离对应ACID首字母缩写中的I。隔离级别是在多个事务同时进行更改及查询时，在性能与可靠性，一致性与结果的可重复性之间的平衡进行调整的设置。
InnoDB提供了SQL:1992标准描述的所有四个事务隔离级别：READ UNCOMMITTED，READ COMMITTED，REPEATABLE READ和SERIALIZABLE。InnoDB的默认隔离级别是REPEATABLE READ。
用户可以使用SET TRANSACTION语句更改单个会话或所有后续连接的隔离级别。要为所有连接设置服务器的默认隔离级别，请在命令行或选项文件中使用--transaction-isolation选项。
REPEATABLE READ是InnoDB的默认隔离级别。同一事务中的一致性读操作读取第一次读取建立的快照。这意味着如果在同一事务中发出多个普通（非锁定）SELECT语句，则这些SELECT语句也相互一致。
READ COMMITTED事务隔离级别，即使在同一事务中，每个一致性读也会设置和读取自己的新快照。
对于锁定读取（使用FOR UPDATE或LOCK IN SHARE MODE的SELECT），UPDATE语句和DELETE语句，InnoDB仅锁定索引记录，而不锁定它们之前的间隙，因此允许在锁定记录旁自由插入新记录。间隙锁仅用于外键约束检查和重复键检查。
由于禁用了间隙锁，因此可能会出现幻像问题，因为其他会话可以在间隙中插入新行。
使用READ COMMITTED还有其他影响：
对于UPDATE或DELETE语句，InnoDB仅为其更新或删除的行保留锁定。MySQL评估WHERE条件后，将释放不匹配行的记录锁。这大大降低了死锁的可能性，但它们仍然可能发生。
对于UPDATE语句，如果一行已被锁定，InnoDB将执行“半一致”读取，将最新提交的版本返回给MySQL，以便MySQL可以确定该行是否与UPDATE的WHERE条件匹配。如果行匹配（必须更新），MySQL再次读取该行，这次InnoDB将其锁定或等待锁定。
1.9 InnoDB索引类型（聚簇/二级索引）
https://dev.mysql.com/doc/refman/5.6/en/innodb-index-types.html
每个InnoDB表都有一个称为聚簇索引的特殊索引，其中存储了行的数据。通常，聚簇索引与主键的含义相同。为了在查询、插入或其他数据库操作中获得最佳性能，必须了解InnoDB如何使用聚簇索引对每个表的最常见的查找和DML操作进行优化。
当在数据库表定义了主键时，InnoDB会将其作为聚簇索引使用。请为创建的数据库表都定义主键。如果数据库表没有逻辑上唯一且非空的列或一组列，请增加一个新的自增长且值会自动填充的列。
如果没有为表定义主键，MySQL会找到第一个所有键列都非空的唯一索引，InnoDB会将它用作聚簇索引。
如果表没有主键或合适的唯一索引，InnoDB会在包含行ID值的合成列内部生成名为GEN_CLUST_INDEX的隐藏聚簇索引。行按InnoDB分配给此类表中的行的ID排序。行ID是一个6字节的字段，随着新行的插入而单向增加。因此，通过行ID排序的列在物理上按插入顺序排列。
除聚簇索引之外的所有索引都称为二级索引。在InnoDB中，二级索引中的每条记录都包含该行的主键列以及二级索引指定的列。InnoDB使用此主键值来搜索聚簇索引中的行。
1.10 InnoDB索引扩展的使用
https://dev.mysql.com/doc/refman/5.6/en/index-extensions.html
InnoDB自动扩展每个二级索引，将主键列附加过来。
有表定义列（i1，i2）上的主键，还在列（d）上定义了二级索引k_d，但InnoDB内部扩展了该索引并将其当作列（d，i1，i2）。
1.11 索引提示
https://dev.mysql.com/doc/refman/5.6/en/index-hints.html
索引提示为优化器提供了信息，关于在进行查询时如何选择索引。索引提示仅适用于SELECT语句。（它们被UPDATE语句的解析器接受但被忽略并且没有效果。）
经过测试，发现索引提示对于UPDATE语句也有效。与文档说明不完全一致。
USE INDEX（index_list）提示告诉MySQL只使用其中一个命名索引来查找表中的行。替代语法IGNORE INDEX（index_list）告诉MySQL不使用某些特定索引或索引。如果EXPLAIN显示MySQL正在使用可能索引列表中的错误索引，这些提示可以发挥作用。
FORCE INDEX提示的作用类似于USE INDEX（index_list），并且假设表扫描的开销非常昂贵。换句话说，仅当无法使用其中一个命名索引查找表中的行时，才使用表扫描。
每个提示都需要索引名称，而不是列名。要引用主键时，请使用名称PRIMARY。
1.12 优化器
https://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html
optimizer_switch系统变量可以控制优化器行为。它的值是一组标志，每个标志的值都为on或off，用于指示是启用还是禁用相应的优化器行为。此变量具有全局值和会话值，可以在运行时更改。可以在服务器启动时设置全局默认值。
index_merge相关优化器默认均为打开。
1.12.1 index merge优化选项
https://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html
索引合并访问方法检索具有多个范围扫描的行，并将其结果合并为一个。此访问方法仅合并来自单个表的索引扫描，不跨多个表扫描。合并可以生成基础扫描的联合，交叉或交叉联合。
在EXPLAIN输出中，索引合并方法在类型列中显示为index_merge。在这种情况下，键列包含使用的索引列表，key_len包含这些索引的最长键部分列表。
索引合并访问方法有几种算法，它们显示在EXPLAIN输出的Extra字段中：
使用交叉（...）
使用联合（...）
使用排序联合（...）
索引合并的使用取决于optimizer_switch系统变量的index_merge，index_merge_intersection，index_merge_union和index_merge_sort_union标志的值。默认情况下，所有这些标志都为打开。
1.13 InnoDB死锁日志查看
https://dev.mysql.com/doc/refman/5.6/en/show-engine.html
使用“SHOW ENGINE INNODB STATUS”命令，可以查看InnoDB用户最后发生的死锁。
SHOW ENGINE INNODB STATUS显示来自标准InnoDB监控器的关于InnoDB存储引擎状态的大量信息。
1.14 启用InnoDB监控
https://dev.mysql.com/doc/refman/5.6/en/innodb-enabling-monitors.html
当InnoDB监控器启用定期输出时，InnoDB每隔15秒将输出写入mysqld服务器标准错误输出（stderr）。
在Windows系统中，stderr会定向到默认日志文件，除非配置为其他。
只有在真正需要查看监控器信息时才应启用InnoDB监控器，因为输出生成会导致性能下降。此外，如果将监控器输出定向到错误日志，如果您忘记稍后通过删除监控器表来禁用监控器，则日志可能会变得非常大。
| 2014-10-16 16:28:15 7feee43c5700 INNODB MONITOR OUTPUT ===================================== |
每个监控器都以包含时间戳和监控器名称的头开头。示例如上。
标准InnoDB监控器（INNODB MONITOR OUTPUT）的头也用于锁监控器，因为后者通过添加额外的锁定信息产生相同的输出。
可以通过以下两种方式之一来启用InnoDB标准监控器和锁监控器以进行定期输出：
l 使用CREATE TABLE语句创建与监控器关联的特殊命名的InnoDB表。
使用CREATE TABLE语名是通过MySQL的SQL解析器将命令传递给InnoDB引擎的一种方法。唯一重要的是表的名字，以及需要是InnoDB表。表的结构和创建表的数据库与是否启动监控不相关。如果关闭服务器，则重新启动服务器时监控器不会自动重新启动。需要删除监控表并执行新的CREATE TABLE语句以启动监控器。
l 使用MySQL 5.6.16中引入的innodb_status_output和innodb_status_output_locks系统变量。
启用或禁用InnoDB监控器时需要PROCESS权限。
1.14.1 启用标准InnoDB监控
为了标准InnoDB监控器能够定期输出，需要创建innodb_monitor表，如“CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;”。
删除该表可以禁用标准的InnoDB监控器。
从MySQL 5.6.16开始，还可以通过将innodb_status_output系统变量设置为ON来启用标准的InnoDB监控器，如“SET GLOBAL innodb_status_output=ON;”。
将innodb_status_output设置为OFF可以禁用标准的InnoDB监控器。
关闭服务器时，innodb_status_output变量会被设置为默认的OFF值。
1.14.2 启用InnoDB锁监控
为了启用InnoDB锁监控器定期输出，请创建innodb_lock_monitor表，如“CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;”。
删除该表可以禁用InnoDB锁监控器。
从MySQL 5.6.16开始，还可以通过将innodb_status_output_locks系统变量设置为ON来启用InnoDB锁监控器。与启用InnoDB监控器的创建表方法一样，必须启用InnoDB标准监控器和InnoDB锁监控器才能定期打印InnoDB锁监控器数据，如“SET GLOBAL innodb_status_output = ON; SET GLOBAL innodb_status_output_locks = ON;”。
关闭服务器时，innodb_status_output和innodb_status_output_locks变量将设置为默认的OFF值。
为了禁用InnoDB锁监控器，请将innodb_status_output_locks设置为OFF。将innodb_status_output设置为OFF可以禁用标准的InnoDB监控器。
1.14.3 按需获取标准InnoDB监控输出
作为启用标准InnoDB监控器进行定期输出的替代方法，可以使用“SHOW ENGINE INNODB STATUS”SQL语句按需获取标准的InnoDB 监控器输出，该语句将输出提取到客户端程序。
如果启用了InnoDB锁监控器，则“SHOW ENGINE INNODB STATUS”输出还包括InnoDB锁监控器数据。
1.14.4 启动InnoDB死锁监控
https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks
启用此选项（innodb_print_all_deadlocks）后，有关InnoDB用户事务中所有死锁的信息将记录在mysqld错误日志中。否则，使用SHOW ENGINE INNODB STATUS命令查看有关上一次死锁的信息。偶尔的InnoDB死锁不一定是个问题，因为InnoDB会立即检测到这种情况并自动回滚其中一个事务。如果应用程序没有适当的错误处理逻辑来检测回滚并重试其操作，则可以使用此选项来定位发生死锁的原因。
1.15 InnoDB锁信息
https://dev.mysql.com/doc/refman/5.6/en/innodb-information-schema-understanding-innodb-locking.html
INNODB_LOCKS表为每个锁等待事务保存一行或多行，表明阻止其进度的任何锁定请求。此表还包含一行，用于描述因指定行或表而挂起的锁队列中的每个锁。
1.15.1 INFORMATION_SCHEMA.INNODB_LOCKS表
https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-table.html
INNODB_LOCKS表提供有关InnoDB事务已请求但尚未获取的每个锁的信息，以及事务持有的阻塞另一个事务的每个锁。
INNODB_LOCKS表包含以下列：
l LOCK_ID
InnoDB内部的唯一的锁ID号。将其视为不透明的字符串。虽然LOCK_ID当前包含TRX_ID（事务ID），但LOCK_ID中的数据格式可能随时更改。
l LOCK_TRX_ID
持有锁的事务的ID。为了获取有关事务的详细信息，请将此列与INNODB_TRX表的TRX_ID列连接查询。
l LOCK_MODE
锁是如何被请求的。允许的锁定模式描述符是S，X，IS，IX，GAP，AUTO_INC和UNKNOWN。锁定模式描述符可以组合使用以识别特定的锁定模式。
l LOCK_TYPE
锁的类型。允许的值是行级锁对应的RECORD，表级锁对应的TABLE。
l LOCK_TABLE
已锁定或包含锁定记录的表的名称。
l LOCK_INDEX
索引的名称，若LOCK_TYPE为RECORD则有值，否则为NULL。
l LOCK_SPACE
锁定记录的表空间ID，若LOCK_TYPE为RECORD则有值，否则为NULL。
l LOCK_PAGE
锁定记录的页码，若LOCK_TYPE为RECORD则有值，否则为NULL。
l LOCK_REC
页面内锁定记录的堆号，若LOCK_TYPE为RECORD则有值，否则为NULL。
l LOCK_DATA
与锁相关的数据（如果有）。如果LOCK_TYPE是RECORD，则显示一个值，否则该值为NULL。对于锁定在主键索引上的记录，显示锁定记录的主键值。对于锁定在唯一二级索引的记录，显示锁定记录的二级索引值。假如二级索引不是唯一的，则显示二级索引及主键值。假如没有主键，LOCK_DATA显示选择的唯一索引键值，或InnoDB内部唯一行号，取决于管理InnoDB聚集索引使用的规则。
1.16 InnoDB锁信息的持久性和一致性
https://dev.mysql.com/doc/refman/5.6/en/innodb-information-schema-internal-data.html
事务与锁的表(INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS)暴露的数据代表了快速变化的数据。这与用户表不同，用户表仅在发生应用程序启动的更新时才更改数据。以上底层数据是内部系统管理的数据，可以非常快速地变化。
出于性能原因，并且为了减少在事务和锁的表之间误用连接的可能性，只要发出任何表上的SELECT，InnoDB就会将所需的事务和锁定信息收集到中间缓冲区中。仅当自上次读取缓冲区起超过0.1秒后，才会刷新此缓冲区。填充三个表所需的数据以原子方式一致地获取，并保存在此全局内部缓冲区中，形成时间点“快照”。如果在0.1秒内发生多个表访问（因为当MySQL处理这些表之间的连接时几乎肯定会这样做），则使用相同的快照来满足查询。
由于InnoDB必须在收集事务和锁定数据时暂时停止，因此对这些表的过于频繁的查询会对其他用户的性能产生负面影响。
由于这些表包含敏感信息（至少INNODB_LOCKS.LOCK_DATA和INNODB_TRX.TRX_QUERY包含），出于安全原因，只允许具有PROCESS权限的用户从中进行查询。
1.17 EXPLAIN输出格式
https://dev.mysql.com/doc/refman/5.6/en/explain-output.html
EXPLAIN语句提供有关MySQL如何执行语句的信息。EXPLAIN适用于SELECT，DELETE，INSERT，REPLACE和UPDATE语句。
1.17.1 EXPLAIN输出列
https://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain-output-columns
| Column | Meaning | 含义 |
| id | The SELECT identifier | SELECT标识符 |
| select_type | The SELECT type | SELECT类型 |
| table | The table for the output row | 输出行的表 |
| partitions | The matching partitions | 匹配的分区 |
| type | The join type | 连接类型 |
| possible_keys | The possible indexes to choose | 可供选择的索引 |
| key | The index actually chosen | 实际选择的索引 |
| key_len | The length of the chosen key | 所选键的长度 |
| ref | The columns compared to the index | 与索引进行比较的列 |
| rows | Estimate of rows to be examined | 需要检查的行的估计值 |
| filtered | Percentage of rows filtered by table condition | 按表条件过滤的行的百分比 |
| Extra | Additional information | 附加信息 |
l possible_keys
possible_keys列表示MySQL可以从中选择查找此表中的行的索引。请注意，此列完全独立于EXPLAIN输出中显示的表的顺序。
l key
key列表示MySQL实际决定使用的键（索引）。如果MySQL决定使用其中一个possible_keys索引来查找行，那么该索引将被列在key值中。
要强制MySQL使用或忽略possible_keys列中列出的索引，请在查询中使用FORCE INDEX，USE INDEX或IGNORE INDEX。
l key_len
key_len列表示MySQL决定使用的key的长度。key_len的值使得MySQL实际使用的多部分key有多少部分可以确定。
由于key的存储格式，对于可以为NULL的列，key的长度比不允许为NULL的列大1。
l rows
rows列表示MySQL认为执行查询时必须检查的行数。
对于InnoDB表，此数字是估计值，可能并不总是准确的。
l Extra
此列包含有关MySQL如何解析查询的其他信息。
1.17.2 EXPLAIN连接类型
https://dev.mysql.com/doc/refman/5.6/en/explain-output.html#jointype_index_merge
EXPLAIN输出的type列描述了表的连接方式。
l const
该表最多只有一行匹配，其在查询的开头读取。因为只有一行，所以优化器的其余部分可以将此行中列的值视为常量。const表非常快，因为它们只读取一次。
将主键或唯一索引的所有部分与常量值进行比较时使用const类型。
l ref
对于上一个表中的每个行组合，将从此表中读取所有具有匹配索引值的行。如果连接仅使用键的最左前缀或者键不是主键或唯一索引（换句话说，如果连接不能基于键值选择单行），则使用ref。如果使用的key只匹配几行，则这是一个好的连接类型。
ref类型可用于使用=或<=>运算符进行比较的索引列。
l fulltext
使用全文索引执行连接。
l index_merge
此连接类型表示使用了索引合并优化。在这种情况下，输出行中的key列包含使用的索引列表，key_len包含所用索引的最长键部分列表。
l range
仅检索给定范围内的行，使用索引选择行。输出行中的key列表示使用哪个索引。key_len包含使用的最长的关键部分。对于此类型，ref列为NULL。
使用=，<>，>，> =，<，<=，IS NULL，<=>，BETWEEN，LIKE或IN（）运算符中的任何一个将键列与常量进行比较时，可以使用range类型。
l index
index连接类型与ALL类型相同，除了扫描索引树的方法。有两种方式：
a.如果索引是查询的覆盖索引，并且可满足表中所需的所有数据，则仅扫描索引树。在这种情况下，Extra列显示“Using index”。仅索引扫描通常比ALL类型快，因为索引的大小通常小于表数据。
b.使用索引中的读取执行全表扫描，用于按索引顺序查找数据行。使用的索引不会出现在Extra列中。
当查询仅使用属于单个索引的列时，MySQL可以使用此连接类型。
l ALL
对之前的表中的每个行组合进行全表扫描。如果表是第一个没有标记为const的表，这通常是不好的，并且在所有其他情况下通常非常糟糕。通常，可以通过添加索引来避免ALL类型，这些索引根据以前表中的常量值或列值从表中启用行检索。
1.18 查看系统变量
1.18.1 INFORMATION_SCHEMA GLOBAL_VARIABLES与SESSION_VARIABLES表
https://dev.mysql.com/doc/refman/5.6/en/variables-table.html
GLOBAL_VARIABLES和SESSION_VARIABLES表提供有关服务器状态变量的信息。
1.18.2 SHOW VARIABLES语法
https://dev.mysql.com/doc/refman/5.6/en/show-variables.html
| SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern' | WHERE expr] |
SHOW VARIABLES显示MySQL系统变量的值。此语句不需要任何权限。只需要能够连接到服务器。
对于SHOW VARIABLES，如果存在LIKE子句，则指示需要匹配的变量名称。可以使用WHERE子句来使用更笼统的条件选择行。
SHOW VARIABLES接受可选的GLOBAL或SESSION变量范围修饰符：
l 使用GLOBAL修饰符，该语句显示全局系统变量值。这些是用于初始化与MySQL的新连接的相应会话变量的值。如果变量没有全局值，则不显示任何值。
l 使用SESSION修饰符，语句将显示对当前连接有效的系统变量值。如果变量没有会话值，则显示全局值。LOCAL是SESSION的同义词。
l 如果不存在修饰符，则默认为SESSION。
| SELECT @@GLOBAL.innodb_data_file_path; |
SHOW VARIABLES受版本相关的显示宽度限制。对于具有非常长值但未完全显示的变量，请使用SELECT作为变通方法。示例如上。
| SHOW VARIABLES LIKE 'max_join_size'; SHOW SESSION VARIABLES LIKE 'max_join_size'; |
使用LIKE子句，该语句仅显示名称与模式匹配的变量的行。要获取特定变量的行，请使用LIKE子句，如上所示。
| SHOW VARIABLES LIKE '%size%'; SHOW GLOBAL VARIABLES LIKE '%size%'; |
要获取名称与模式匹配的变量列表，请在LIKE子句中使用%通配符。
1.18.3 查看系统变量示例
| select @@global.[变量名称]; select @@session.[变量名称]; select @@[变量名称]; show variables where variable_name like '[变量名称]'; show global variables where variable_name like '%[变量名称]%'; show session variables where variable_name like '%[变量名称]%'; show variables like '[变量名称]'; show global variables like '%[变量名称]%'; show session variables like '%[变量名称]%'; select * from information_schema.GLOBAL_VARIABLES where variable_name like '%[变量名称]%'; select * from information_schema.SESSION_VARIABLES where variable_name like '%[变量名称]%'; |
1.19 修改系统变量
1.19.1 变量赋值的SET语法
https://dev.mysql.com/doc/refman/5.6/en/set-variable.html
| SET variable = expr [, variable = expr] ... variable: { user_var_name | param_name | local_var_name | {GLOBAL | @@GLOBAL.} system_var_name | [SESSION | @@SESSION. | @@] system_var_name } SET ONE_SHOT system_var_name = expr |
变量赋值的SET语法，可以将值分配给影响服务器或客户端操作的不同类型的变量：
l 用户定义的变量。
l 存储过程和函数参数，以及存储的程序局部变量。
l 系统变量。系统变量也可以在服务器启动时设置。
MySQL服务器维护配置其操作的系统变量。系统变量可以具有影响整个服务器操作的全局值，或影响当前会话的会话值，或两者都影响。许多系统变量是动态的，可以在运行时使用SET语句更改，以影响当前服务器实例的操作。（要使全局系统变量设置为永久性，以便它在服务器重新启动时应用，您还应该在选项文件中设置它。）
如果更改会话系统变量，则该值在会话中保持有效，直到将变量更改为其他值或会话结束。此更改对其他会话没有影响。
如果更改全局系统变量，则会记住该值并用于初始化新会话的会话值，直到将变量更改为其他值或服务器退出为止。访问全局值的任何客户端都可以看到此更改。但是，更改仅影响更改后连接的客户端的相应会话值。全局变量更改不会影响任何当前客户端会话的会话值（甚至不会影响全局值发生更改的会话）。
1.19.2 修改系统变量示例
| SET GLOBAL [变量名] = [值]; SET @@GLOBAL.[变量名] = [值]; SET SESSION [变量名] = [值]; SET @@SESSION.[变量名] = [值]; SET @@[变量名] = [值]; SET [变量名] = [值]; |
1.20 系统变量名称
1.20.1 事务隔离级别
https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_tx_isolation
变量名称为tx_isolation，可设置的值为“READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE”。
https://dev.mysql.com/doc/refman/5.6/en/set-transaction.html
变量名称为“TRANSACTION ISOLATION LEVEL”，可设置的值为“REPEATABLE READ、READ COMMITTED、READ UNCOMMITTED、SERIALIZABLE”。
1.20.2 优化器
https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_optimizer_switch
变量名称为optimizer_switch。
1.20.3 InnoDB监控器开关
https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_status_output
变量名称为innodb_status_output。
1.20.4 InnoDB锁监控器开关
https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_status_output_locks
变量名称为innodb_status_output_locks。
1.20.5 InnoDB死锁监控开关
https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks
变量名称为innodb_print_all_deadlocks。
1.20.6 日志文件路径
https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_log_error
变量名称为log_error。
2. 其他资料
2.1 MySQL · 引擎分析 · InnoDB行锁分析
http://mysql.taobao.org/monthly/2018/05/04/
| 分析使用的表Schema和数据如下：
create table t(c1 int primary key, c2 int, c3 int, c4 int, unique index i_c2(c2), index i_c3(c3)); insert into t values (10, 11, 12, 13), (20, 21, 22, 23), (30, 31, 32, 33), (40, 41, 42, 43); |
| Read-Uncommitted/RC级别加锁分析
查询条件为唯一索引等值
UPDATE … WHERE UK = XX; 未更新其他索引列 update t set c4 = 12 where c2 = 21; 对唯一索引上数据加X锁（LOCK_X|LOCK_REC_NOT_GAP），然后对应的主键行也需要加X锁。 更新其他索引列 update t set c3 = 12 where c2 = 21; 依次对唯一索引数据、主键行、索引数据加X锁。 |
| 查询条件为非唯一索引
实际非唯一索引情况与前面唯一索引情况加锁情况一致，这里不再展开叙述。 |
2.2 MySQL 加锁处理分析
| 一条简单SQL的加锁实现分析
组合二：id唯一索引+RC 这个组合，id不是主键，而是一个Unique的二级索引键值。那么在RC隔离级别下，delete from t1 where id = 10; 需要加什么锁呢？见下图： 此组合中，id是unique索引，而主键是name列。此时，加锁的情况由于组合一有所不同。由于id是unique索引，因此delete语句会选择走id列的索引进行where条件的过滤，在找到id=10的记录后，首先会将unique索引上的id=10索引记录加上X锁，同时，会根据读取到的name列，回主键索引(聚簇索引)，然后将聚簇索引上的name = ‘d’ 对应的主键索引项加X锁。为什么聚簇索引上的记录也要加锁？试想一下，如果并发的一个SQL，是通过主键索引来更新：update t1 set id = 100 where name = ‘d’; 此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。
结论：若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name=’d’,id=10]的记录。 |
| 组合三：id非唯一索引+RC
相对于组合一、二，组合三又发生了变化，隔离级别仍旧是RC不变，但是id列上的约束又降低了，id列不再唯一，只有一个普通的索引。假设delete from t1 where id = 10; 语句，仍旧选择id列上的索引进行过滤where条件，那么此时会持有哪些锁？同样见下图：
根据此图，可以看到，首先，id列索引上，满足id = 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于，组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。
结论：若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。 |
| 死锁原理与分析
两个Session的一条SQL，产生死锁 第二个用例，虽然每个Session都只有一条语句，仍旧会产生死锁。要分析这个死锁，首先必须用到本文前面提到的MySQL加锁的规则。针对Session 1，从name索引出发，读到的[hdc, 1]，[hdc, 6]均满足条件，不仅会加name索引上的记录X锁，而且会加聚簇索引上的记录X锁，加锁顺序为先[1,hdc,100]，后[6,hdc,10]。而Session 2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。发现没有，跟Session 1的加锁顺序正好相反，如果两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。
结论：死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。而使用本文上面提到的，分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因。 |
2.3 How to deal with MySQL deadlocks
https://www.percona.com/blog/2014/10/28/how-to-deal-with-mysql-deadlocks/
当两个或多个事务相互保持并请求锁定时，会发生MySQL中的死锁，会创建一个依赖循环。在交易系统中，死锁是不争的事实，并不是完全可以避免的。
| 1 141013 6:06:22 2 *** (1) TRANSACTION: 3 TRANSACTION 876726B90, ACTIVE 7 sec setting auto-inc lock 4 mysql tables in use 1, locked 1 5 LOCK WAIT 9 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 4 6 MySQL thread id 155118366, OS thread handle 0x7f59e638a700, query id 87987781416 localhost msandbox update 7 INSERT INTO t1 (col1, col2, col3, col4) values (10, 20, 30, 'hello') 8 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: 9 TABLE LOCK table `mydb`.`t1` trx id 876726B90 lock mode AUTO-INC waiting 10 *** (2) TRANSACTION: 11 TRANSACTION 876725B2D, ACTIVE 9 sec inserting 12 mysql tables in use 1, locked 1 13 876 lock struct(s), heap size 80312, 1022 row lock(s), undo log entries 1002 14 MySQL thread id 155097580, OS thread handle 0x7f585be79700, query id 87987761732 localhost msandbox update 15 INSERT INTO t1 (col1, col2, col3, col4) values (7, 86, 62, "a lot of things"), (7, 76, 62, "many more") 16 *** (2) HOLDS THE LOCK(S): 17 TABLE LOCK table `mydb`.`t1` trx id 876725B2D lock mode AUTO-INC 18 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: 19 RECORD LOCKS space id 44917 page no 529635 n bits 112 index `PRIMARY` of table `mydb`.`t2` trx id 876725B2D lock mode S locks rec but not gap waiting 20 *** WE ROLL BACK TRANSACTION (1) |
第1行给出了发生死锁的时间。如果应用程序代码捕获并记录了死锁错误，那么可以将此时间戳与应用程序日志中的死锁错误的时间戳匹配。将获得回滚的事务。从那里，检索该事务中的所有语句。
第3行和第11行，记下事务编号和活动时间。如果定期记录SHOW ENGINE INNODB STATUS输出（这是一个很好的做法），那么可以使用事务编号搜索以前的输出，以希望查看同一事务的更多语句。ACTIVE sec提供关于事务是单个语句还是多语句的提示。
第4和第12行，使用及锁定的表仅与当前语句有关。因此，使用1个表并不一定意味着该事务仅涉及1个表。
第5行和第13行，值得关注，因为它告诉了事务已经进行了多少更改，即“undo log entries”以及它所持有了多少行的锁（“row lock(s)”）。这些信息提示了事务的复杂性。
第6和第14行，记下了线程ID，连接主机和连接用户。如果将不同的MySQL用户用于不同的应用程序功能，这是另一个好习惯，那么可以根据连接的主机和用户确定事务来自哪个应用程序区域。
第9行，对于第一个事务，它只显示它正在等待的锁，在这种情况下是表t1上的AUTO-INC锁。其他可能的值是S表示共享锁，X表示独占，有或没有间隙锁。
第16和17行，对于第二个事务，它显示了它所持有的锁，在这种情况下是AUTO-INC锁，这是事务1正在等待的。
第18和19行显示事务2正在等待的锁。在这种情况下，它是另一个表的主键上的共享非间隙记录锁。InnoDB中的共享记录锁只有几个来源：
1）使用SELECT ... LOCK IN SHARE MODE
2）关于外键引用记录
3）使用INSERT INTO ... SELECT，源表上的共享锁
trx（2）的当前语句是对表t1的简单插入，因此消除了1和3。通过检查SHOW CREATE TABLE t1，可以确认S锁是由于父表t2的外键约束引起的。
3. 总结
3.1 更新时使用二级索引会锁聚簇索引
如果在搜索中使用了二级索引，并且要设置的索引记录锁是排它的，InnoDB还会检索相应的聚簇索引记录并对它们设置锁。
3.2 更新时使用索引防止锁表
如果没有适合语句的索引，并且MySQL必须扫描整个表来处理该语句，则表的每一行都会被锁定，这反过来会阻止其他用户对表的所有插入。创建好的索引非常重要，这样查询就不会不必要地扫描很多行。
3.3 出现死锁的条件
两个(或以上)的Session加锁的顺序不一致，导致创建了依赖循环 。
3.4 锁表模拟异常
使用“LOCK TABLES [表名] WRITE;”可以对指定的表获取写锁，其他会话将无法对表访问（读取或修改），“LOCK TABLES [表名] READ;”可以对指定的表获取读锁，其他会话无法对表进行修改，但可以进行读取。
使用“UNLOCK TABLES;”可以释放全部的表锁。
当需要模拟数据库读取或修改异常时，可以使用“LOCK TABLES READ/WRITE”锁表进行模拟；当测试完毕时，可以使用“UNLOCK TABLES;”释放表锁。
625
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
