谈谈错误的MySQL优化器计划，结合 where、order by 和 limit 查询耗时过长

原创小小亮 2022-11-18

654

这是2019 年在Percona 社区博客上发布的帖子的改进版本（ MySQL 优化器：结合 WHERE、ORDER BY 和 LIMIT 的查询的超时异常：https://percona.community/blog/2019/07/29/mysql-optimizer-naughty-aberrations-on-queries-combining-where-order-by-and-limit/）。在我的日常工作中，我经常发现自己链接到这篇文章，希望一些事情能以不同的方式解释更多的细节。所以这是改进后的帖子，下面有更改日志和愿望清单。

2022-11-15：重新发布，添加关于不稳定的部分，提供有关延迟行查找技巧的更多详细信息，添加带有 ICP 解决方案的新索引和 IGNORE INDEX 技巧，提及 Jeremy Cole 的工作和新的优化器标志，并做其他不值得详细列出的外观变化；
TODO：简化示例查询和表，并提供问题的 dbdeployer 示例。

有时，MySQL 优化器选择了一个错误的计划，一个应该在不到 0.1 秒内执行的查询最终运行了 12 分钟！这不是一个新问题：关于这个的错误可以追溯到 2014 年，一篇关于主题日期为 2015 年的博客文章。但即使这是旧消息，因为这个问题最近引起了我的注意，这是一个值得关注的问题写在。

这篇文章中描述的问题的一个令人惊讶的行为是它不稳定。即使一切都顺利运行了一段时间，大多数查询速度很快，资源消耗正常，事情也会在瞬间变得更糟！没有明显的原因，查询突然需要更长的时间来执行，并且一个表现良好的 MySQL 实例由于 CPU 或存储饱和而变得非常慢。这是因为查询计划对表统计信息很敏感，并且统计信息会随时间变化，这可能会向错误的方向倾斜。此外，主要故障转移或计划内切换（Vitesscalls a reparent），也可能触发错误计划的性能下降，因为新主节点上的统计信息与旧主节点上的统计信息不同。执行另一次切换/重新父级可能会暂时恢复良好的性能，但糟糕的计划仍会挥之不去，随时准备重新出现。被魔法消失的问题有被魔法重新出现的趋势，所以让我们充分理解这个主题并探索解决方案。

MySQL优化器

在查看有问题的查询之前，我们必须先谈谈优化器。查询优化器是选择查询计划的查询执行的一部分。查询执行计划是数据库选择运行特定查询的方式。它包括索引选择、连接类型、表查询顺序、临时表使用、排序类型，……可以使用EXPLAIN命令获得特定查询的执行计划。

一个有问题的案例

被查询的表如下：

mysql> SHOW CREATE TABLE _test_jfg_201907\G
*************************** 1. row ***************************
       Table: _test_jfg_201907
Create Table: CREATE TABLE `_test_jfg_201907` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `str1` varchar(150) DEFAULT NULL,
  `id1` int(10) unsigned NOT NULL,
  `id2` bigint(20) unsigned DEFAULT NULL,
  `str2` varchar(255) DEFAULT NULL,
[...many more id and str fields...]
  `create_datetime` datetime NOT NULL,
  `update_datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `key1` (`id1`,`id2`)
) ENGINE=InnoDB AUTO_INCREMENT=_a_big_number_ DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

这不是一张小表（虽然也不是很大）：

# ls -lh _test_jfg_201907.ibd 
-rw-r----- 1 mysql mysql 11G Jul 23 13:21 _test_jfg_201907.ibd

在隐藏结果之前运行PAGER cat > /dev/null的有问题的查询如下：

mysql> SELECT * FROM _test_jfg_201907
  WHERE id1 = @v AND id2 IS NOT NULL
  ORDER BY id DESC LIMIT 20;
20 rows in set (27.22 sec)

嗯，查询需要很长时间才能执行（27.22 秒）。由于id1, id2上的索引，预计执行速度会更快。让我们检查一下查询计划：

mysql> EXPLAIN SELECT * FROM _test_jfg_201907
  WHERE id1 = @v AND id2 IS NOT NULL
  ORDER BY id DESC LIMIT 20\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: _test_jfg_201907
   partitions: NULL
         type: index
possible_keys: key1
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 13000
     filtered: 0.15
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

什么：查询没有使用索引key1 ！它正在扫描表格（上面的key: PRIMARY），这怎么可能？简短的解释是优化器认为——或者我应该说希望——扫描已经按id排序的表，将足够快地找到不需要排序的有限行。因此，通过尝试避免排序，优化器最终会浪费时间扫描表。

一些解决方案

避免扫描表的第一个解决方案是提示 MySQL 使用key1索引，如下所示。有了这个，查询几乎是即时的，但它不是我最喜欢的解决方案，因为如果索引被删除或它的名称被更改，它就会失败。

mysql> SELECT * FROM _test_jfg_201907 USE INDEX (key1)
  WHERE id1 = @v AND id2 IS NOT NULL
  ORDER BY id DESC LIMIT 20;
20 rows in set (0.00 sec)

一个更优雅但仍然非常 hack-ish 的解决方案是防止优化器使用索引进行排序。这可以通过下面修改后的ORDER BY子句来实现（感谢Shlomi Noach建议这个解决方案）。这是我在发布原始帖子时的首选解决方案，但我发现了一个更好的解决方案，我在下面添加了它 ( IGNORE INDEX )。

mysql> SELECT * FROM _test_jfg_201907
  WHERE id1 = @v AND id2 IS NOT NULL
  ORDER BY (id+0) DESC LIMIT 20;
20 rows in set (0.00 sec)

另一种解决方案是使用Late Row Lookup技巧的变体（感谢我的同事Michal Skrzypecki让我注意到了这一点）。这个技巧迫使优化器选择好的计划，因为查询被修改以使计划明确。这是一个优雅的 hack，但因为它使查询更加复杂，并且在最初发布时，我更喜欢其他解决方案。但从那以后我改变了主意，主要是因为我更了解ICP ...

mysql> SELECT y.* FROM (
  SELECT id FROM _test_jfg_201907
    WHERE id1 = @v AND id2 IS NOT NULL
    ORDER BY id DESC LIMIT 20) x
  JOIN _test_jfg_201907 y ON x.id = y.id
  ORDER BY y.id DESC;
20 rows in set (0.00 sec)

在发布原始帖子时我没有意识到的是，像上面这样重写查询可以避免进行过多的主键查找。当使用索引key1时，MySQL 获取与 where 子句匹配的所有行，执行与匹配行一样多的主键查找，按id排序，然后返回LIMIT的 20 行，丢弃未使用的行。对于更复杂的查询，后期行查找技巧只会根据需要执行尽可能多的主键查找，在本例中为 20。所以对于这个查询和表结构，使用这个技巧是我所知道的最好的优化，这使它成为我最喜欢的新解决方案，但只有在无法添加索引时才使用。

这给我们带来了我在原始帖子中没有的解决方案：在id1, id, id2上添加索引。这个索引允许比使用延迟行查找技巧更有效地提供查询，因为它不需要排序（在id1与第一列相等之后，这个新索引的第二列提供ORDER BY id ）。所以有了这个索引，MySQL 可能会扫描更少的行（一旦 MySQL 有 20 行与 where 子句匹配，就不再扫描更多的行），所有这些都不需要进行额外的主键查找，因为索引条件下推（我在我的文章中提供了有关 ICP 的更多详细信息发布由于 ICP 而检查的行不可信）。

坚持使用表上的索引，原始帖子中没有的另一种解决方案是通过向查询添加IGNORE INDEX (PRIMARY)提示来防止使用错误的计划，如下所示。当延迟行查找技巧引入的复杂性不受欢迎并且无法添加索引时，这是我推荐的解决方案。

mysql> SELECT * FROM _test_jfg_201907 IGNORE INDEX (PRIMARY)
  WHERE id1 = @v AND id2 IS NOT NULL
  ORDER BY id DESC LIMIT 20;
20 rows in set (0.00 sec)

理想的解决方案

理想的解决方案是修复下面的错误（我声称 Bug#74602 没有修复，即使它在错误系统中被标记为这样，但我不会对此发出太多噪音，因为 Bug#78612 也引起了对此的关注问题）：

Bug#74602: Optimizer prefers wrong index because of low limit（https://bugs.mysql.com/bug.php?id=74602）
Bug#78612: Optimizer chooses wrong index for ORDER BY（https://bugs.mysql.com/bug.php?id=78612）
PS-1653: Optimizer chooses wrong index for ORDER BY DESC（https://jira.percona.com/browse/PS-1653）

一个好的解决方法

即使上面的错误没有修复，Jeremy Cole在我最初的帖子后已经提供了一个很好的解决方法。Jeremy 在Bug#97001（https://bugs.mysql.com/bug.php?id=97001）中提交了一个补丁，允许告诉优化器不喜欢排序索引，Oracle 接受了这个补丁。可以通过在优化器开关中将标志prefer_ordering_index设置为OFF（默认 ON）来实现良好的行为，避免糟糕的计划。Jeremy 还在他的帖子Reconsidering access paths for index ordering… a dangerous optimization… and a fix （https://blog.jcole.us/2019/09/30/reconsidering-access-paths-for-index-ordering-a-dangerous-optimization-and-a-fix/）中描述了这项工作！.

结束这篇文章之前的最后一件事：我只对优化器做出错误选择的原因做了简短的解释。Domas Mituzas 在 2015 年已经给出了更长的解释，因此我建议您参阅他的帖子以获取更多详细信息：on ORDER BY optimization（https://dom.as/2015/07/30/on-order-by-optimization/）。

Mydbaops 在 2020 年就同一主题发表了另一篇文章，因此如果您想了解此问题的其他示例，可以阅读Row scanned equals to 1, Is the query is [sic] optimized tuned ? （https://mydbops.wordpress.com/2020/03/24/row-scanned-equals-to-1-is-the-query-is-optimally-tuned/）.

原文标题：Bad Optimizer Plan on Queries Combining WHERE, ORDER BY and LIMIT
原文作者：Jean-François Gagné
原文链接：https://jfg-mysql.blogspot.com/2022/11/bad-optimizer-plan-on-queries-combining-where-order-by-and-limit.html

mysql 墨力翻译小小亮的翻译文章

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者