最近，我遇到了另一个引起我注意的慢查询问题。经过快速调查，结果又是 MySQL 优化器在查询规划中做出不准确估计的情况。这是有问题的 SQL：
select * from t_pay_record
WHERE
((
user_id = 'user_id1'
AND is_del = 0
))
ORDER BY
id DESC
LIMIT 20
这个查询花了整整 20 分钟才返回结果！然而，当我们切换到不同的 user_id 时，执行速度非常快。从我们的生产环境观察，大多数用户都经历了正常性能。我们甚至测试了具有相似数据分布模式的用户，这些查询也运行顺利。
让我们先检查原始有问题的 SQL 的 EXPLAIN 输出：
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | t_pay_record | NULL | index | idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del | PRIMARY | 8 | NULL | 22593 | 0.01 | Using where |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------+---------+------+-------+----------+-------------+
现在，当我们测试具有相似数据分布但响应时间正常的用户时，我们得到了不同的 EXPLAIN 结果。一些显示：
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | t_pay_record | NULL | index | idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del | idx_user_id_trade_code_status_amount_create_time_is_del | 195 | NULL | 107561| 10.00| Using where |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------+-------+----------+-------------+
而其他显示：
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+-------------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+-------------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | t_pay_record | NULL | index | idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del | idx_user_id | 195 | NULL | 87514| 10.00| Using where |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+-------------+---------+------+-------+----------+-------------+
基于这些观察，很明显查询使用了错误的索引。但为什么 MySQL 会选择不合适的索引？ 这是由于多个相互关联的因素造成的，本文将深入分析这些原因，同时提供实用解决方案。
分析 MySQL 慢查询#
在我之前的文章中，我提到 SQL 优化通常依赖于三个基本工具：
EXPLAIN：这提供了表面级别的分析，而不实际执行 SQL。虽然它可能并不总是完全准确或详细，但它可以揭示关键问题。
PROFILING：使用
set profiling = 1启用，此工具对 SQL 执行进行采样，并将查询分解为不同阶段及其各自的时间。它需要实际的 SQL 执行和成功，尽管阶段分解并不总是足够细粒度。它主要用于识别和避免某些阶段（如防止内存排序）。OPTIMIZER TRACE：此工具提供优化器采取的每个步骤的详细视图，需要实际的 SQL 执行和成功。MySQL 的优化器通过多次迭代考虑众多因素，使其配置相当复杂。虽然默认设置在大多数场景中工作正常，但特殊情况需要手动干预。
值得注意的是，在不同的 MySQL 版本中，由于 MySQL 的固有设计限制，EXPLAIN 和 OPTIMIZER TRACE 结果可能不同。EXPLAIN 往往更接近实际执行结果，而 OPTIMIZER TRACE 就像整个过程中的检查点采样。在 MySQL 的持续开发迭代中，一些不一致是不可避免的。
对于我们特定的 SQL 案例，EXPLAIN 已经显示它使用了错误的索引。然而，要理解为什么会发生这种情况，我们需要使用 OPTIMIZER TRACE 进行更深入的分析。在我们继续该分析之前，让我解释 MySQL 的 InnoDB 查询优化器统计配置。
MySQL InnoDB 优化器统计配置#
官方文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html
为了优化用户 SQL 查询，MySQL 执行 SQL 解析、重写和查询计划优化。对于 InnoDB 引擎，创建查询计划涉及分析：
- 全表扫描的成本
- 哪些索引可用于 WHERE 和 ORDER BY 条件
- 每个潜在索引的查询成本
- 选择并执行成本最低的计划
每个索引的查询成本通过 InnoDB 优化器统计确定。这些数据通过对表和索引数据进行采样收集 - 不是通过完整收集，而是通过统计采样。几个配置参数控制此过程：
innodb_stats_persistent：此全局变量控制统计是否默认持久化（默认 ON）。我们通常不能接受内存存储，因为数据库重启需要重新分析表，减慢启动时间。单个表控制使用STATS_PERSISTENT（在CREATE TABLE和ALTER TABLE语句中）。innodb_stats_auto_recalc：此全局变量控制默认自动更新（默认 ON），当超过 10% 的表行被修改时触发后台异步更新。单个表控制使用STATS_AUTO_RECALC（在CREATE TABLE和ALTER TABLE语句中）。innodb_stats_persistent_sample_pages：此全局变量控制默认采样的页数（默认 20）。每次更新从表和每个索引中随机采样 20 页，以估计每个索引和全表扫描的查询成本。单个表控制使用STATS_SAMPLE_PAGES（在CREATE TABLE和ALTER TABLE语句中）。
最慢 SQL 执行的根本原因分析#
从我们之前的 EXPLAIN 结果，我们知道最终查询使用了 PRIMARY 键索引。这意味着整个 SQL 执行过程涉及：以反向主键顺序遍历表中的每一行，直到找到 20 条匹配记录。考虑到执行时间，我们知道这个过程在收集 20 条匹配之前检查了许多记录，使其效率极低。但为什么会发生这种情况？
查看我们的 SQL 语句，在前面提到的第二步中，考虑的索引包括与 WHERE 条件中的 user_id 和 is_del 相关的索引（如 EXPLAIN 所示：idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del），以及来自 ORDER BY 条件的 id 索引（主键索引）。假设随机采样的页看起来像这样：
蓝色部分表示采样的页，表中的每个索引默认采样 20 页。假设我们的采样结果与图表匹配，其他索引被相对均匀地采样，导致优化器估计使用其他索引需要扫描数万行。然而，从主键采样的最后一页恰好包含此特定用户在末尾的所有记录。由于语句包含 LIMIT 20，如果末尾恰好有 20 条记录（都满足 WHERE 条件），优化器会得出结论，通过主键向后扫描 20 条记录将是最有效的。这导致优化器相信主键扫描具有最低成本。实际上，这不是真的，因为采样数据不代表全貌 - 之后可能有很多很多不属于此用户的记录，特别是在大表中。
如果我们移除 LIMIT 子句，EXPLAIN 显示选择了正确的索引，因为在不限制结果的情况下，主键索引需要扫描整个表，使其不可能比与 user_id 相关的索引成本更低。
为什么具有正常执行时间的不同 user_id 显示不同的索引选择#
类似地，由于所有索引优化器统计都是随机采样的，随着表变得更大和索引扩展，加上可能添加更复杂的索引，这放大了使用不同参数（在我们的案例中是不同的 user_id）分析索引成本的方差。
这提出了你可能遇到的另一个问题：在现有索引之上添加复合索引时（例如，最初只有 idx_user_id，然后添加 idx_user_status_pay），以前仅按 user_id 搜索的 SQL 查询可能有时使用 idx_user_id，有时使用 idx_user_status_pay。使用 idx_user_status_pay 可能比使用 idx_user_id 慢。因此，添加新的复合索引可能会减慢其他业务 SQL 查询，这些查询本不应该由复合索引优化，所以应该谨慎进行。
随着数据量增长和表变得更复杂，此设计产生的问题#
由于统计不是实时更新的，而是仅在修改的行超过一定百分比时更新，并且统计是采样的而不是全面的，当表数据量很大时，这些统计可能非常不准确。
由于统计本身不准确，具有不同数据类型、许多字段，特别是各种复合索引的复杂表设计使统计更加不准确。
作为旁注：MySQL 表不应该太大，需要适当的水平分区，而字段不应该太多，需要良好的垂直分区。索引不应该随意添加 - 添加太多会加剧统计不准确性，导致错误的索引选择。
手动 ANALYZE TABLE 向表添加读锁，阻止更新和事务。这不能用于关键在线业务表。考虑在低流量期间为关键业务表安排 ANALYZE。
依赖自动表刷新机制使参数难以调整（主要是
STATS_SAMPLE_PAGES参数 - 我们通常不会更改STATS_PERSISTENT，因为我们不能接受由于重启延迟而导致的内存存储，我们也不会禁用STATS_AUTO_RECALC，因为它会使优化器分析越来越不准确）。很难预测最佳值。业务增长和用户行为导致的数据倾斜也是不可预测的。使用 ALTER TABLE 修改特定表的STATS_SAMPLE_PAGES与 ANALYZE TABLE 具有相同的效果 - 添加读锁并阻止更新和事务。这不能用于关键在线业务表，所以最好从一开始就估计大表规模，尽管这很困难。
结论和建议#
总之，对于具有大数据量的生产表，我建议通过数据库和表分区主动控制每个表的数据量。然而，业务增长和产品需求不断迭代并变得更加复杂，很难保证我们不会最终得到具有复杂索引的大表。在这种情况下，我们需要适当增加 STATS_SAMPLE_PAGES，同时使用 FORCE INDEX 引导关键用户触发的查询使用正确的索引。这防止了本文描述的问题，即不准确的 MySQL 优化器统计导致某些用户 ID 使用错误的索引。
