mysql:非常简单的 SELECT id ORDER BY LIMIT 不会按预期使用 INDEX(？!)

Question

我有一个包含大约 300 万条记录的简单表。我制作了必要的索引，我也强制索引 PRIMARY 但仍然不起作用。 它搜索几乎所有 300 万行而不是使用索引来执行这一行(record_id 是 INT 自动递增):

EXPLAIN SELECT record_id
FROM myrecords
FORCE INDEX (
PRIMARY )
ORDER BY record_id ASC
LIMIT 2955900 , 300

id  select_type     table     type  possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE          myrecords index NULL            PRIMARY 4           NULL    2956200 Using index

索引是

Keyname Type    Unique  Packed  Column      Cardinality Collation   Null
PRIMARY BTREE   Yes     No      record_id   2956742     A           No  

我想知道为什么这个 FORCED 索引没有被正确使用。

在不强制索引 'primary' 的情况下，ASC 和 DESC 都尝试过，结果是一样的。表已修复-优化-分析。运气不好。

查询需要一分钟以上才能执行!

我所期望的:查询应该只处理 300 行，因为该列已被索引。正如您在第一个代码格式 block 中看到的那样(向右滚动一点)

Answer 1

索引查找是按值，而不是按位置。索引可以搜索值 2955900，但您并不是在要求它。您要求查询从表中第 2955900 行的偏移量开始。

优化器不能假定所有主键值都是连续的。因此，第 2955900 行的值很可能远高于该值。

即使主键值是连续的，您的 WHERE 条件也可能只匹配 45% 的行。在这种情况下，第 2955900 行的 id 值将方式超过 id 值 2955900。

换句话说，id 值 2955900 的索引查找不会提供第 2955900 行。

因此 MySQL 不能使用索引作为限制的偏移量。它必须扫描行以对它们进行计数，直到它达到 offset+limit 行数。

MySQL 确实有 optimizations related to LIMIT ，但更多的是在达到要返回的行数后停止表扫描。优化器可能仍会在 EXPLAIN 计划中报告它预计它可能必须扫描整个表。

关于FORCE INDEX的常见误解是它强制使用索引。 :-)事实上，如果查询不能使用索引(或者如果可用索引对该查询没有任何好处)，FORCE INDEX 就没有效果。

---

回复你的评论:

分页是数据驱动的 Web 应用程序的常见问题。尽管此功能很常见，但要对其进行优化并不容易。这里有一些提示:

• 为什么要使用偏移量 2955900 进行查询？您真的希望用户筛选那么多页面吗？大多数用户在浏览几页后就放弃了(具体多少取决于应用程序的类型和数据)。

• 减少查询次数。您的分页功能可以获取前 5-10 页，即使它只向用户显示第一页。缓存其他页面，假设用户将浏览几页。只有当他们前进到缓存的页面集之后，您的应用程序才必须执行另一个查询。您甚至可以在客户端浏览器上用 Javascript 缓存所有 10 个页面，因此点击“下一步”对他们来说是即时的(至少对于前几页)。

• 不要在任何用户界面上放置“上一个”按钮，因为人们会出于好奇点击它。请注意，Google 有一个“下一步”按钮，但没有“最后一个”按钮。因此，UI 本身不鼓励人们运行具有高偏移量的低效查询。

• 如果用户一次翻一页，则在下一页查询的 WHERE 子句中使用上一页中返回的最高 ID 值。 IE。以下确实使用了索引，即使没有 FORCE INDEX 提示:

  SELECT * FROM thistable WHERE id > 544 LIMIT 20