Оптимизация ORDER BY, когда результирующий набор очень велик и его нельзя упорядочить по индексу

Могу я ответить на свой вопрос?

Я считаю, что обнаружил, что единственный способ сделать то, что я описываю, состоит в том, чтобы моя таблица поиска содержала строки для каждой возможной комбинации групп пользователей, членом которых человек может захотеть быть.

Чтобы выбрать упрощенный пример, вместо этого:

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 может использовать индекс, если он начинается с крайнего левого столбца индекса и находится в первой неконстантной несистемной таблице для обработки. Затем объединение работает со всем списком (который уже упорядочен), и только те, которые не видны текущему пользователю (которых будет небольшая часть), удаляются с помощью ВНУТРЕННЕГО СОЕДИНЕНИЯ.

Скопируйте значение, которое вы собираетесь заказывать, в таблицу разрешений на просмотр и добавьте его в свой индекс.

Вы можете использовать триггер для сохранения этого значения из другой таблицы.

select * from
(
select *
FROM  node  
INNER JOIN viewpermission 
ON    viewpermission_nodeID=node_ID    
AND viewpermission_usergroupID IN (<...usergroups of current user...>)
) a
order by a.node_lastupdated desc

Внутренний запрос дает вам отфильтрованное подмножество, которое, как я понимаю, существенно меньше, чем весь набор. Только меньшие должны быть отсортированы.

У MySQL возникают проблемы, когда вы используете GROUP BY и ORDER BY в одном запросе. Это вызывает сортировку файлов, и это, вероятно, самый большой штраф за производительность.

Вы можете устранить необходимость в DISTINCT (или GROUP BY), используя некоррелированный подзапрос вместо JOIN.

SELECT * FROM node
WHERE node_id IN (
  SELECT viewpermission_nodeID
  FROM viewpermission
  WHERE viewpermissiong_usergroupID IN ( <...usergroups...> )
)
ORDER BY node_lastupdated DESC
LIMIT 100;

Нет необходимости сортировать или выполнять DISTINCT в подзапросе, поскольку IN (1, 1, 2, 3) совпадает с IN (1, 3, 2).

Обратите внимание, что MySQL может использовать только один индекс для каждой таблицы в заданном запросе, поэтому он попытается сделать лучший выбор между индексом по node_id и индексом по node_lastupdated. Он не может использовать оба, и даже если вы сделаете составной индекс, это не поможет в этом случае.

Не забудьте проанализировать различные решения с помощью EXPLAIN.