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

qq78292959 阅读:24 2024-05-10 16:29:43 评论:0

我有一个非常大的 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子句还过滤出要显示的日志文件类型。日志文件以两级层次结构组织。让我们调用指定日志文件的列 xy .两列的类型都是 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. 相比)。

我还尝试包括 xyORDER BY .我猜,对于 MySQL 为此使用索引,xy也需要按降序包含,尽管在这里没有多大意义(因为结果只包含 xy 的相同值)。 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 可能会选择不扫描索引以满足WHEREid 上的标准.原因是它不知道(甚至无法找到)索引中 id < 438353696 左边的位置。会是真的。所以,它选择只扫描表。完成后,它会继续手动对结果集进行排序,从而导致您所看到的性能下降。


标签:mysql
声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

关注我们

一个IT知识分享的公众号