MySQL EXPLAIN ANALYZE：深入理解 actual 与 estimated 行数差异
各位同学，大家好！今天我们来深入探讨 MySQL 的 EXPLAIN ANALYZE 命令，重点聚焦于 actual 与 estimated 行数之间的差异。这个差异是性能优化的关键线索，理解它能帮助我们更有效地诊断和解决查询性能问题。
EXPLAIN ANALYZE 是 MySQL 8.0.18 引入的一个强大工具，它不仅能像 EXPLAIN 那样展示查询的执行计划，还能实际执行查询并提供更详细的运行时信息，包括实际的执行时间、返回的行数等。
EXPLAIN 与 EXPLAIN ANALYZE 的对比
在深入研究 actual 和 estimated 行数差异之前，我们先简单回顾一下 EXPLAIN 和 EXPLAIN ANALYZE 的区别。
EXPLAIN 仅提供查询优化器对查询执行计划的估计，它不会实际执行查询。其输出结果中包含诸如 possible_keys、key、key_len、rows (估计的行数) 等信息。rows 列就是优化器估计需要扫描的行数。
EXPLAIN ANALYZE 则会实际执行查询，并提供查询执行过程中每个步骤的实际信息。它会在 EXPLAIN 的基础上，增加实际的执行时间、实际返回的行数等信息。
-- EXPLAIN 示例
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
-- EXPLAIN ANALYZE 示例
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;EXPLAIN ANALYZE 的输出会更长，因为它包含了实际的执行时间和行数。例如：
-> Filter: (orders.customer_id = 123) (cost=0.35 rows=1) (actual time=0.010..0.010 rows=1 loops=1)
-> Table scan on orders (cost=0.35 rows=10) (actual time=0.008..0.009 rows=10 loops=1)在这个例子中，rows=10 是优化器估计需要扫描的行数，而 rows=1 是实际返回的行数。 actual time 显示了执行花费的实际时间。
理解 actual 与 estimated 行数差异
actual 行数是查询执行期间实际处理的行数，而 estimated 行数是优化器根据统计信息和一些启发式规则所做的估计。 两者之间的差异，揭示了优化器对数据分布的理解与实际情况的偏差。 这种偏差是性能问题的根源之一。
主要有以下几个原因可能导致 actual 和 estimated 行数存在显著差异：
-
过期的统计信息 (Outdated Statistics):
MySQL 优化器依赖于表的统计信息来估计查询成本和选择最佳执行计划。 这些统计信息包括表中总行数、索引的基数（cardinality，不同值的数量）等。 如果表的数据发生了显著变化（例如，大量数据插入、删除或更新），而没有及时更新统计信息，优化器就会基于过时的信息进行估计，导致
estimated行数不准确。可以使用
ANALYZE TABLE命令更新表的统计信息。ANALYZE TABLE orders;定期更新统计信息是一个良好的实践，尤其是在数据发生重大变化后。 可以通过事件调度器来自动执行
ANALYZE TABLE。 -
数据倾斜 (Data Skew):
当数据在列中的分布不均匀时，就会发生数据倾斜。 例如，在一个
orders表中，大部分订单可能属于少数几个客户。 优化器通常假设数据是均匀分布的，因此在存在数据倾斜的情况下，estimated行数可能会与actual行数相差很大。例如，如果
customer_id = 123的客户拥有大量的订单，而优化器假设customer_id列的值是均匀分布的，那么它可能会低估实际返回的行数。处理数据倾斜的方法包括：
-
直方图 (Histograms): MySQL 8.0 引入了直方图功能，可以更准确地描述列的数据分布情况。 直方图可以帮助优化器更好地处理数据倾斜问题。
ANALYZE TABLE orders UPDATE HISTOGRAM ON customer_id; -
强制使用索引 (Force Index): 如果优化器选择了错误的索引，导致性能下降，可以尝试强制使用正确的索引。
SELECT * FROM orders FORCE INDEX (customer_id_index) WHERE customer_id = 123; -
重写查询 (Rewrite Query): 在某些情况下，可以通过重写查询来避免数据倾斜带来的问题。例如，可以将一个包含数据倾斜列的查询分解成多个子查询，分别处理不同范围的数据。
-
-
多列索引 (Multi-Column Index) 的使用不当:
对于多列索引，优化器可能会基于索引的前导列进行估计，而忽略了后续列的选择性。 如果只有索引的前导列被用于过滤，而后续列的过滤条件具有很强的选择性，那么
estimated行数可能会远高于actual行数。例如，假设有一个包含
customer_id和order_date的多列索引。 如果查询只使用了customer_id进行过滤，而order_date列的过滤条件能够显著减少返回的行数，那么优化器可能会高估实际返回的行数。解决方法：
-
调整索引列的顺序 (Adjust Index Column Order): 将选择性更高的列放在索引的前面。
-
创建新的索引 (Create New Index): 创建一个包含所有过滤列的索引。
-
-
子查询优化 (Subquery Optimization) 的问题:
优化器在处理子查询时，可能会做出不准确的估计。 例如，对于
IN子查询，优化器可能会低估子查询返回的行数，从而导致最终查询的执行计划不佳。解决方法：
-
将子查询转换为
JOIN(Convert Subquery to JOIN): 在许多情况下，将子查询转换为JOIN可以提高查询性能。 -
使用
EXISTS替代IN(Use EXISTS instead of IN): 在某些情况下，使用EXISTS替代IN可以获得更好的性能。
-
-
范围查询 (Range Query) 的估计误差:
对于范围查询（例如，
WHERE date BETWEEN '2023-01-01' AND '2023-01-31'），优化器可能无法准确估计范围内的数据量。解决方法：
- 使用直方图 (Use Histograms): 直方图可以帮助优化器更准确地估计范围查询的行数。
-
复杂
WHERE子句 (Complex WHERE Clause):当
WHERE子句包含多个条件，并且这些条件之间存在复杂的逻辑关系（例如，AND、OR、NOT）时，优化器可能难以准确估计满足所有条件的行数。解决方法：
-
简化
WHERE子句 (Simplify WHERE Clause): 尝试简化WHERE子句，或者将其分解成多个更简单的查询。 -
使用
STRAIGHT_JOIN(Use STRAIGHT_JOIN): 在某些情况下，可以使用STRAIGHT_JOIN强制指定表的连接顺序。
-
-
触发器 (Triggers) 和存储过程 (Stored Procedures):
如果查询涉及触发器或存储过程，优化器可能无法准确估计这些代码的实际影响，因为它们的操作对优化器来说是黑盒。
解决方法：
-
仔细审查触发器和存储过程的性能 (Review Triggers and Stored Procedures): 确保触发器和存储过程本身的性能良好，避免它们成为瓶颈。
-
尽可能避免在查询中使用复杂的触发器和存储过程 (Avoid Complex Triggers and Stored Procedures): 尽量将复杂逻辑移到应用程序层处理，而不是依赖数据库的触发器和存储过程。
-
案例分析
为了更好地理解 actual 和 estimated 行数差异的影响，我们来看几个具体的案例。
案例 1：过期的统计信息
假设 orders 表包含数百万行数据，并且 customer_id 列有一个索引。 最近，我们向表中插入了大量新的订单数据，但没有更新统计信息。
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;输出结果可能如下：
-> Filter: (orders.customer_id = 123) (cost=0.35 rows=1) (actual time=0.010..0.010 rows=1000 loops=1)
-> Table scan on orders (cost=0.35 rows=10) (actual time=0.008..0.009 rows=1000000 loops=1)在这个例子中，estimated 行数为 1，而 actual 行数为 1000。 这表明优化器低估了实际返回的行数，因为它使用了过时的统计信息。
解决方法：
ANALYZE TABLE orders;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;更新统计信息后，优化器会更准确地估计行数，从而选择更合适的执行计划。
案例 2：数据倾斜
假设 orders 表中，customer_id = 1 的客户拥有大量的订单，而其他客户的订单数量相对较少。
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1;输出结果可能如下：
-> Filter: (orders.customer_id = 1) (cost=0.35 rows=10) (actual time=0.010..0.100 rows=10000 loops=1)
-> Table scan on orders (cost=0.35 rows=1000) (actual time=0.008..0.050 rows=1000000 loops=1)在这个例子中，estimated 行数为 10，而 actual 行数为 10000。 这表明优化器低估了实际返回的行数，因为它没有考虑到数据倾斜的情况。
解决方法：
ANALYZE TABLE orders UPDATE HISTOGRAM ON customer_id;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1;创建直方图后，优化器会更准确地估计行数，从而选择更合适的执行计划。
案例 3：多列索引的使用不当
假设 orders 表有一个包含 customer_id 和 order_date 的多列索引。 查询只使用了 customer_id 进行过滤，而 order_date 列的过滤条件能够显著减少返回的行数。
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123 AND order_date BETWEEN '2023-01-01' AND '2023-01-31';输出结果可能如下：
-> Filter: ((orders.customer_id = 123) and (orders.order_date between '2023-01-01' and '2023-01-31')) (cost=0.35 rows=10) (actual time=0.010..0.020 rows=100 loops=1)
-> Index lookup on orders using customer_id_order_date (customer_id=123) (cost=0.35 rows=100) (actual time=0.008..0.015 rows=1000 loops=1)在这个例子中，estimated 行数为 100，而 actual 行数为 100。虽然看起来 estimated 和 actual 行数接近，但通过 actual time 可以发现 Index lookup 扫描了1000行。
解决方法：
CREATE INDEX idx_order_date ON orders (order_date);
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123 AND order_date BETWEEN '2023-01-01' AND '2023-01-31';创建 order_date 的单列索引可以帮助优化器更好地利用 order_date 列的过滤条件。
优化策略选择
面对 actual 和 estimated 行数差异，选择哪种优化策略取决于具体情况。 一般来说，可以按照以下步骤进行：
- 收集信息: 使用
EXPLAIN ANALYZE找出存在显著差异的步骤。 - 分析原因: 确定导致差异的原因，例如过期的统计信息、数据倾斜、索引问题等。
- 选择策略: 根据原因选择合适的优化策略，例如更新统计信息、创建直方图、调整索引、重写查询等。
- 验证效果: 使用
EXPLAIN ANALYZE验证优化策略的效果，确保性能得到改善。 - 迭代优化: 如果效果不佳，重复上述步骤，尝试不同的优化策略。
总结一下
EXPLAIN ANALYZE 是 MySQL 性能优化的利器，它能够帮助我们深入了解查询执行过程中的实际情况。 通过分析 actual 和 estimated 行数之间的差异，我们可以找到性能瓶颈，并采取相应的优化措施。 掌握这一工具，你将能够更有效地诊断和解决 MySQL 查询性能问题，构建更高效的数据库应用。