mysql之ORDER BY id DESC 结合 WHERE id ... 不会使用索引

我有一个非常大的 MySQL 表(十亿行)，有点像“日志文件”，我在其中读取数据作为从“当前”到“过去”的 block 流。机制如下:第一个查询获取例如最近的 10 个条目，用户可以选择加载“more from past”，这意味着加载比已加载的最旧条目更旧的 10 个最近的条目。行由 id 标识，而不是时间。所以查询有一个 WHERE id < [the id of the oldest entry already loaded]以及ORDER BY id DESC LIMIT 10以便加载“接下来的 10”行。

WHERE连同 ORDER BY id DESC LIMIT 10如果我使用超过 (id) 的索引，工作正常仅专栏。

但是，我的情况比这复杂一点。巨大的表实际上是几个日志文件的组合。所以 WHERE子句还过滤出要显示的日志文件类型。日志文件以两级层次结构组织。让我们调用指定日志文件的列 x和 y .两列的类型都是 varchar(32)整理ascii_general_ci ，我有一个索引 (x, y, id)全文使用它们。 (我正在考虑通过为日志文件使用数字标识符(而不是为它们使用两级层次结构)来简化这一点，但是，以下内容非常奇怪。)

所以我的查询包含以下子句:

WHERE x = ... AND y = ... AND id < [the id of the oldest entry already loaded] 

连同前面提到的

ORDER BY id DESC LIMIT 10 

索引(x, y, id)如果我不按降序排列而是按升序排列，则 MySQL 会很好地使用它。但是，降序排序时，将不会使用它。

1. 原始查询(慢)

   SELECT * FROM syncLog WHERE x = '' AND y = '' AND id < 438353696 ORDER BY id DESC LIMIT 10

   select_type?  table?   partitions?  type?  possible_keys?  key?    key_len?  ref?         rows?     Extra? 
   SIMPLE        syncLog  NULL         ref    PRIMARY,x_y_id  x_y_id  68        const,const  37040991  Using where 
   

2. 升序(快速)

   SELECT * FROM syncLog WHERE x = '' AND y = '' AND id < 438353696 ORDER BY id LIMIT 10

   select_type?  table?   partitions?  type?  possible_keys?  key?    key_len?  ref?         rows?     Extra? 
   SIMPLE        syncLog  NULL         ref    PRIMARY,x_y_id  x_y_id  68        const,const  37041163  Using index condition; Using where 
   

3. 没有 x,y (快)

   SELECT * FROM syncLog WHERE id < 438353696 ORDER BY id DESC LIMIT 10

   select_type?  table?   partitions?  type?  possible_keys?  key?    key_len?  ref?         rows?     Extra? 
   SIMPLE        syncLog  NULL         range  PRIMARY         PRIMARY 8         NULL         37041281  Using where 
   

我也试过FORCE INDEX FOR ORDER BY (x_y_id) , 但它不会改变任何东西(与 1. 相比)。

我还尝试包括 x和 y在ORDER BY .我猜，对于 MySQL 为此使用索引，x和 y也需要按降序包含，尽管在这里没有多大意义(因为结果只包含 x 和 y 的相同值)。 ORDER BY x DESC, y DESC, id DESC但它也没有改变任何东西。

为什么搜索id最大的10行时没有使用索引？如何解决？

有趣的是，当强制 MySQL 对此查询使用主索引时(如 1. 但使用 FORCE INDEX (PRIMARY) )，查询速度很快。然而，一旦单个日志文件变得非常稀疏，这就会成为一个问题，因为它们共享一个 id 空间，导致需要扫描表的大部分以查找具有正确 x 的 10 行。和 y .

此设置使用相当过时的 MySQL 版本:5.6.25

请您参考如下方法:

这是我对正在发生的事情的最佳猜测。这是您的查询，仅供引用:

SELECT * 
FROM syncLog 
WHERE x = '' AND y = '' AND id < 438353696 
ORDER BY id 
LIMIT 10 

在升序排序的情况下，MySQL 只需对 id 进行一次索引扫描。 ，从左侧(最低)开始。请注意，这会用一 block 石头杀死两只鸟，因为扫描同时满足 WHERE限制 id < 438353696 和 ORDER BY 中的排序要求条款。

然而，在降序排序版本中，这不是一回事:

SELECT * 
FROM syncLog 
WHERE x = '' AND y = '' AND id < 438353696 
ORDER BY id DESC 
LIMIT 10 

在这种情况下，MySQL 可能会选择不扫描索引以满足WHERE。 id 上的标准.原因是它不知道(甚至无法找到)索引中 id < 438353696 左边的位置。会是真的。所以，它选择只扫描表。完成后，它会继续手动对结果集进行排序，从而导致您所看到的性能下降。