Как получить оптимизированный список с разбивкой на страницы из запроса с UNION ALL?

У меня есть запрос, сформированный UNION ALL из двух таблиц. Результаты должны быть упорядочены и разбиты на страницы (как обычный список веб-приложения).

Исходный запрос (упрощенный):

SELECT name, id
FROM _test1 -- conditions WHERE
UNION ALL
SELECT name, id
FROM _test2 -- conditions WHERE
ORDER BY name DESC LIMIT 10,20

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

Как я могу получить оптимизированный список с разбивкой на страницы из UNION ALL?

Постданные:

Я воспользовался поиском Stack Overflow и нашел несколько похожих вопросов, но ответ был неверным или вопрос не совсем тот же. Два примера:

Оптимизация запроса UNION mysql

Объединение операций UNION и LIMIT в запросе MySQL

Я удивлен, что в Stack Overflow никто не смог ответить на этот вопрос. Может быть, нельзя сделать этот запрос более эффективно? Какое может быть решение этой проблемы?


sql optimization query-optimization union-all
person Dr. No    schedule 27.09.2011    source источник
comment
Я подозреваю, что проблема не в UNION ALL, а в ORDER BY.   -  person NPE    schedule 27.09.2011
comment
Упрощенный пример полезен только в том случае, если упрощенный пример может воспроизвести проблему. Видите ли вы проблему с двумя приведенными выше таблицами, без предложения where и индекса имени в обеих таблицах?   -  person Miserable Variable    schedule 27.09.2011
comment
Если у вас есть 2 таблицы и вам нужен такой запрос, то это больше похоже на проблему дизайна. Для LIMIT 10,20 оптимизировать будет несложно, а для LIMIT 5000,20 это будет довольно сложно, если не невозможно.   -  person ypercubeᵀᴹ    schedule 27.09.2011
comment
В комментариях можно сказать, что могут быть условия, а может и нет (в списке есть несколько полей ввода для поиска), но оба они медленные (может быть, я плохо объяснил). Проблема (я думаю) заключается в огромном количестве данных, которые генерируют объединение, которые нужно упорядочить и, наконец, применить LIMIT.   -  person Dr. No    schedule 27.09.2011
comment
@ypercube Ты прав. Проблема в устаревшей базе данных, структуру которой я не могу изменить.   -  person Dr. No    schedule 27.09.2011
comment
@aix Да, используя план объяснения, инструкция, вызывающая наибольшую загрузку ЦП, — это ORDER BY, но я думаю, что это вызвано огромным объемом данных, сгенерированных UNION ALL.   -  person Dr. No    schedule 27.09.2011

Ответы (1)


arrow_upward
4
arrow_downward

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

(   SELECT name, id
    FROM _test1 -- conditions WHERE
    ORDER BY name DESC LIMIT 0, 30
)
UNION ALL
(   SELECT name, id
    FROM _test2 -- conditions WHERE
   ORDER BY name DESC LIMIT 0, 30
)
ORDER BY name DESC
LIMIT 10, 20

По сути, вы ограничиваете каждый подзапрос подмножеством возможных строк, которые могут находиться на данной странице. Таким образом, вам нужно только извлечь и объединить 20 строк из каждой таблицы, прежде чем определить, какие 10 возвращать. В противном случае сервер потенциально возьмет все строки из каждой таблицы, упорядочит и объединит их, а затем начнет пытаться найти правильные строки.

Однако я не использую MySQL много, поэтому я не могу гарантировать, что движок будет вести себя так, как я думаю :)

В любом случае, как только вы попадете на более поздние страницы, вы все равно будете объединять все более и более крупные наборы данных. ОДНАКО, я придерживаюсь твердого мнения, что пользовательский интерфейс НИКОГДА не должен позволять пользователю извлекать набор записей, которые позволяют ему перейти (например) на страницу 5000. Это просто слишком много данных для человеческого разума, чтобы найти их полезными сразу и должны требовать дополнительной фильтрации. Может быть, пусть они увидят первые 100 страниц (или какое-то другое число), но в противном случае им придется лучше ограничивать результаты. Только мое мнение.

person Tom H    schedule 27.09.2011
comment
Вам нужно LIMIT 0, 30, а не LIMIT 0, 20 в подзапросах, но я согласен во всех остальных аспектах. ( LIMIT x,y совпадает с LIMIT y OFFSET x ) - person ypercubeᵀᴹ; 27.09.2011
comment
Спасибо. Я думал, что синтаксис был началом и концом, а не началом и числом для возврата. Исправляем сейчас. - person Tom H; 27.09.2011
comment
Да, ОП тоже может так думать! - person ypercubeᵀᴹ; 27.09.2011