新手必看：MySQL事务执行计划分析 | 13分钟学会

▌ 技术引导
MySQL事务执行计划分析，是调试性能瓶颈的核武器。我见过太多人把执行计划当成可有可无的装饰，结果真刀真枪的死锁、慢查询、资源争抢全靠它来定位。执行计划不是用来装门面的，它是将事务的执行过程打成快照，供你反向推演。你说你不会看，那我直接给你看一个真实案例，一个insert into和update的组合操作在执行计划中是如何被拆解和执行的。别问，直接看delete from table where id in (select ...)这种嵌套查询，执行计划里的derived table和materialized subquery会直接暴露你的写法是否高效。

事务执行计划的分析工具不是只有explain，还有performance_schema、trace、profiling，这些都能从不同维度帮你抓取事务执行的细节。现实中，我遇到过无数次因为没有查看执行计划，导致索引失效、全表扫描、锁等待，甚至oom。执行计划是你调优的第一步，别等系统崩溃了才想起它。

我之前在生产环境中用过trace来追踪事务的每个步骤，看着每个lock table、query execution、filesort、temporary table，那种触目惊心的感觉至今难忘。执行计划中的type字段，如果是system那就是最优，如果是range可能还有优化空间，如果是all那就必须加索引。

真正懂执行计划的人，会直接从索引使用、锁机制、语句顺序、连接方式这些角度切入。explain的输出看似简单，但它埋藏着事务执行的真相。我见过有人用explain发现某次update操作反复读取同一个行锁，导致死锁，最后通过调整where条件和加锁策略解决了问题。

别光看优化器的决策，看它的执行顺序。比如一个join操作，如果explain中的type是index，但实际执行时却变成了all，那说明优化器选了错的索引，或者你的表结构设计有问题。执行计划是你的第一道防线，它告诉你，你写的SQL到底在干啥，而不是你想象的那样。

▌ 技术参考
MySQL事务执行计划分析是理解事务执行路径、识别性能瓶颈的关键手段。通过explain命令，可以得到事务执行的步骤、使用的索引、临时表生成、排序方式等信息。执行计划中的type字段是判断查询效率的重要依据，system、const、eq_ref、ref、range、index、all分别代表不同的执行方式。type为all时通常意味着全表扫描，需要检查是否存在合适的索引。

在实际使用中，explain的输出需要结合多个字段综合判断。比如type为index时，若extra字段显示Using index，说明查询可以通过索引完成，不需要回表。但若extra显示Using where，说明虽然用了索引，还存在额外的过滤条件。这种情况下，可以考虑调整where子句，让MySQL更高效地利用索引。执行计划中的rows字段也能帮助评估查询是否合理，值越大，执行效率通常越差。

MySQL提供了一些内置工具辅助分析事务执行计划，如performance_schema和trace。通过performance_schema，可以获取更详细的事务执行信息，包括锁等待、查询时间、执行步骤等。trace工具则用于追踪SQL执行过程，帮助你了解每个操作的具体细节。在执行trace时，需要设置session变量SET profiling=1，然后执行查询后再用SHOW PROFILES查看执行时间分布，用SHOW PROFILE CPU, BLOCK, MEMORY来获取具体资源消耗情况。

事务执行计划中，锁机制是不可忽视的一环。通过执行计划可以观察到事务是否持有锁、锁的类型、是否发生死锁。例如，当执行计划中出现lock table或lock in share mode时，说明事务正在请求锁，这可能会影响并发性能。在实际场景中，我遇到过一个insert操作因为锁等待导致整个事务阻塞，通过分析执行计划，发现是由于没有使用合适的索引，导致锁范围过大。

在事务执行过程中，临时表和文件排序（filesort）是常见的性能消耗点。当explain中的type为all且extra字段显示Using temporary和Using filesort时，说明查询需要生成临时表并进行排序，这对性能影响极大。例如，一个group by操作在没有索引的情况下，MySQL会生成临时表并使用filesort，这种情况下可以通过添加合适的索引或者调整查询语句来优化。

执行计划中的type字段对事务性能影响深远。如果type为range，说明MySQL使用了范围索引扫描，这种情况下通常表现较好，但需注意范围过大可能导致性能下降。如果type为all，说明全表扫描，此时必须考虑添加索引。我在实际工作中见过多次这种情况，一个简单的where id=xxx的查询被迫进行全表扫描，结果执行时间长达数十秒，后来通过添加id字段的索引大幅优化了性能。

事务执行计划的分析可以结合实际场景中的索引使用情况进行优化。比如在写insert into和update的组合操作时，执行计划会显示是否使用了覆盖索引。如果查询能够通过索引完成而不需要回表，那么执行效率会显著提高。我之前处理过一个事务，其中update操作在没有覆盖索引的情况下，进行了多次回表操作，导致性能下降。通过添加覆盖索引，将执行时间从数百毫秒缩短到了几十毫秒。

执行计划中的possible_keys字段是优化的重要参考。它列出了可能用到的索引，但并不代表一定会使用。如果possible_keys中没有索引，说明查询条件无法匹配任何索引，此时必须考虑添加合适的索引。我见过很多表因为缺少索引导致全表扫描，即使查询条件看似简单，比如where name like 'xxx'，但如果没有索引，执行效率会变得极差。

在分析执行计划时，需要特别关注join顺序和连接方式。explain中的select_type字段能够帮助判断是否使用了子查询优化。例如，当select_type为dependent subquery时，说明子查询依赖外部查询，这可能导致性能问题。我在生产环境中处理过一个复杂的join查询，因为join顺序不符合最左前缀原则，导致执行计划选择了错误的索引，查询耗时显著增加。

事务执行计划中的extra字段也能提供很多有用信息。比如Using where表示使用了where条件，Using temporary表示生成了临时表，Using filesort表示需要排序。这些信息可以帮助你判断是否需要优化查询。我之前遇到一个select语句，extra显示Using temporary和Using filesort，后来通过调整group by的字段顺序，让MySQL能够使用索引进行排序，避免了生成临时表。

MySQL 8.0版本中，explain的输出更加详细，提供了更多的字段，如filtered、key_len、rows等。filtered字段表示查询条件过滤掉的行数比例，可以用来判断索引是否有效。例如，如果filtered值接近于0，说明索引几乎没有帮助，需要重新考虑索引策略。key_len字段显示了使用的索引长度，可以帮助判断是否使用了最合适的索引。

在分析执行计划时，还要注意事务是否受到影响。比如，在innodb引擎下，如果事务中包含多个update操作，执行计划可能会显示多个lock table步骤，这会影响并发性能。我之前处理过一个事务，因为它包含了多个写操作，导致锁等待时间过长，最终通过调整事务的写顺序、减少锁范围，解决了性能问题。

执行计划的使用还需要结合具体的事务场景。比如，在批量导入数据时，如果执行计划显示全表扫描，应该先考虑是否使用了合适的索引，或者是否可以调整导入策略，比如使用insert ignore或批量处理。我之前处理过一个大数据量的insert操作，因为没有使用索引，导致整个事务执行极慢，后来通过调整导入顺序和添加索引，将导入时间降低了近90%。

事务执行计划的分析还可以用于优化索引使用。比如，在explain中，如果key字段为空，说明没有使用索引，需要检查查询条件是否能够匹配索引。另外，如果多个索引都可用，但优化器选择了最慢的索引，可以通过调整索引顺序或显式指定使用哪个索引来优化查询。我见过很多这样的场景，索引虽然存在，但因为顺序不当，导致执行效率低下。

在某些特殊场景下，执行计划可以揭示更深层次的问题。例如，当一个事务包含多个子查询，执行计划可能会显示derived table，这说明子查询被当作临时表处理，可能会影响性能。我之前处理过一个包含多个子查询的select语句，通过将部分子查询改写为join，减少了derived table的使用，执行时间大大缩短。

有些情况下，执行计划的输出可能不够准确，需要结合其他工具进行验证。比如，使用trace工具可以获取更详细的执行步骤，帮助你判断执行计划是否真实反映了事务的执行路径。我曾经在生产环境中遇到一个执行计划显示使用了索引，但实际执行时却发生了全表扫描，后来通过trace发现是由于某些条件导致优化器选择了错误的索引，最终通过调整查询条件解决了问题。

对于复杂的事务，执行计划还能帮助你判断是否需要拆分操作。比如，一个涉及多个表的事务，如果执行计划中出现了多个filesort或temporary table，说明查询效率低下。我之前处理一个事务，它包含多个嵌套查询和复杂的join，后来通过将部分操作拆分为独立事务，解决了性能瓶颈。

有些时候，执行计划的效率评估并不准确，这时候需要结合实际数据和索引的物理结构进行分析。比如，如果表的索引碎片率过高，即使执行计划显示使用了索引，实际查询效率也可能很差。我曾遇到一个索引字段存在大量重复值，导致优化器选择了全表扫描，后来通过重建索引，解决了这个问题。

在某些特定情况下，执行计划还可以帮助你判断是否需要使用覆盖索引。比如，当select语句中的字段都包含在索引中时，MySQL可以避免回表操作，提高查询效率。我处理过一个select操作，通过添加覆盖索引，将查询时间从几秒减少到了几十毫秒。

如果执行计划中的type为all，说明查询需要进行全表扫描，这时必须考虑是否需要添加索引。有时，即使索引存在，由于查询条件不匹配，也会导致全表扫描。我曾经处理过一个select查询，因为没有使用where条件中的字段，导致索引失效，执行时间变得极长，后来通过调整查询条件，解决了这个问题。