sql - "ORDER BY"当结果集很大并且不能按索引排序时优化

Question

当我不能使用索引来满足行的排序时，如何使具有小 LIMIT(即一次 20 行)的 ORDER BY 子句快速返回？

假设我想从表“节点”(简化如下)中检索一定数量的标题。顺便说一下，我正在使用 MySQL。

node_ID INT(11) NOT NULL auto_increment,
node_title VARCHAR(127) NOT NULL,
node_lastupdated INT(11) NOT NULL,
node_created INT(11) NOT NULL

但我需要将返回的行限制为仅特定用户有权访问的行。许多用户可以访问大量节点。我在一个大的查找表中预先计算了这些信息(试图让事情变得更容易)，其中主键覆盖了两列，一行的存在意味着用户组可以访问该节点:

viewpermission_nodeID INT(11) NOT NULL,
viewpermission_usergroupID INT(11) NOT NULL

因此，我的查询包含类似

FROM
  node
  INNER JOIN viewpermission ON
    viewpermission_nodeID=node_ID
    AND viewpermission_usergroupID IN (<...usergroups of current user...>)

...而且我还使用了 GROUP BY 或 DISTINCT，这样即使用户的两个“用户组”都有权访问该节点，节点也只会返回一次。

我的问题是，按创建日期或上次更新日期对结果进行排序的 ORDER BY 子句似乎无法使用索引，因为返回的行取决于其他 viewpermission 表中的值。

因此 MySQL 需要找到所有符合条件的行，然后自己对它们进行排序。如果特定用户有 100 万行，并且我们想查看最新的 100 行或按上次更新排序的第 100-200 行，则 DB 需要确定用户可以看到哪一百万行，排序整个结果集本身，在它可以返回那 100 行之前，对吗？

有什么创造性的方法可以解决这个问题吗？我一直在思考:

以某种方式将日期添加到查看权限查找表中，以便我可以构建一个包含日期和权限的索引。我猜是有可能的。

编辑:简化问题

也许我可以通过像这样重写来简化问题:

有什么方法可以重写此查询或为以下内容创建索引，以便可以使用索引进行排序(而不仅仅是选择行)？

SELECT nodeid
FROM lookup
WHERE
  usergroup IN (2, 3)
GROUP BY
  nodeid

(usergroup) 上的索引允许索引满足 WHERE 部分，但 GROUP BY 强制在这些行上使用临时表和文件排序。 (nodeid) 上的索引对我没有任何作用，因为 WHERE 子句需要一个以 usergroup 作为第一列的索引。 (usergroup, nodeid) 上的索引强制临时表和文件排序，因为 GROUP BY 不是索引的第一列可以变化。

任何解决方案？

Answer 1

我可以回答我自己的问题吗？

我相信我已经发现做我所描述的事情的唯一方法是让我的查找表为一个人可能想要成为成员的用户组的每个可能组合都有行。

选择一个简化的例子，而不是这样做:

SELECT id FROM ids WHERE groups IN(1,2) ORDER BY id

如果您需要使用索引来选择行并对其进行排序，则必须将 IN(1,2) 抽象为常量而不是范围，即:

SELECT id FROM ids WHERE grouplist='1,2' ORDER BY id

当然，除了使用字符串“1,2”之外，您还可以在那里使用外键等。关键是您不仅必须为每个组而且为多个组的每个组合都有一行。

所以，这就是我的答案。

无论如何，对于我的应用程序，我觉得为每个节点维护所有可能的用户组组合是不值得的。出于我的目的，我预测大多数节点对大多数用户都是可见的，所以我觉得简单地让 GROUP BY 使用索引是可以接受的，因为过滤并不那么需要它。

换句话说，我对原始查询采用的方法可能类似于:

SELECT
    <fields>
FROM
  node
  INNER JOIN viewpermission ON
    viewpermission_nodeID=node_ID
    AND viewpermission_usergroupID IN (<...usergroups of current user...>)
  FORCE INDEX(node_created_and_node_ID)
GROUP BY
  node_created, node_ID

如果 GROUP BY 从索引的最左侧列开始，并且它位于要处理的第一个非常量非系统表中，则它可以使用索引。然后连接处理整个列表(已经排序)，只有那些对当前用户不可见的(将是一小部分)被 INNER JOIN 删除。