Автор оригинала: Vlad Mihalcea.
Вступление
В этой статье мы рассмотрим, как мы можем ограничить набор результатов SQL-запроса только верхними N строками.
Ограничение набора результатов SQL очень важно, когда базовый запрос может привести к получению очень большого количества записей, что может оказать существенное влияние на производительность приложения .
Зачем ограничивать количество строк SQL-запроса?
Извлечение большего количества данных, чем необходимо, является основной причиной проблем с производительностью доступа к данным. При разработке конкретного бизнес-варианта использования объем данных, доступных как в среде разработки, так и в среде контроля качества, довольно мал, поэтому не все SQL-запросы записываются таким образом, чтобы результирующий набор ограничивался фиксированным количеством записей.
После развертывания приложения в рабочей среде данные начинают накапливаться, и запросы, которые когда-то были очень быстрыми, начинают выполняться все медленнее и медленнее. Даже если индексы применяются к критериям фильтрации и сортировки SQL-запросов, если индекс не охватывает весь запрос (например, сканирование только по индексу), записи таблицы должны быть проверены с использованием шаблона чтения с произвольным доступом.
Если размер результирующего набора невелик и база данных может использовать индекс для критериев фильтрации и сортировки, то затраты, связанные с чтением записей таблицы, все равно меньше, чем сканирование всей таблицы. С другой стороны, если размер результирующего набора очень велик и базе данных требуется доступ к очень большому проценту данной таблицы, то использование индекса будет менее эффективным, чем сканирование всей таблицы.
Чтобы доказать это, рассмотрим, что у нас есть следующая таблица post в нашей базе данных, которая содержит 5000 записей:
Итак, если мы не ограничим набор результатов верхними N записями:
EXPLAIN ANALYZE
SELECT
title
FROM
post
ORDER BY
id DESC
План выполнения приведенного выше SQL – запроса выглядит следующим образом:
| QUERY PLAN | |------------------------------------------------------| | Sort | | (cost=63.66..64.92 rows=504 width=524) | | (actual time=4.999..5.808 rows=5000 loops=1) | | Sort Key: id DESC | | Sort Method: quicksort Memory: 583kB | | -> Seq Scan on post | | (cost=0.00..41.04 rows=504 width=524) | | (actual time=0.059..1.753 rows=5000 loops=1) | | | | Planning time: 0.833 ms | | Execution time: 6.660 ms |
Обратите внимание на последовательное сканирование всех 5000 строк таблицы post .
Теперь при добавлении предложения LIMIT, которое ограничивает набор результатов только 5 записями:
EXPLAIN ANALYZE
SELECT
title
FROM
post
ORDER BY
id DESC
LIMIT 5
План выполнения SQL-запроса Top-N выглядит следующим образом:
| QUERY PLAN | |-------------------------------------------------| | Limit | | (cost=0.28..0.46 rows=5 width=24) | | (actual time=0.019..0.021 rows=5 loops=1) | | -> Index Scan Backward using post_pkey on post | | (cost=0.28..178.28 rows=5000 width=24) | | (actual time=0.017..0.019 rows=5 loops=1) | | | | Planning time: 0.854 ms | | Execution time: 0.046 ms |
Обратите внимание, что на этот раз было использовано сканирование индекса, и только 5 записей были отсканированы и извлечены. Более того, время выполнения в сотни раз меньше, чем при предыдущем выполнении.
Размер набора результатов SQL-запроса может повлиять на план выполнения, поскольку база данных может выбрать сканирование всей таблицы, даже если для критериев фильтрации и сортировки запросов доступен индекс.
Не только план выполнения может быть менее эффективным, но и извлечение большего количества данных, чем необходимо, потребует значительного объема ресурсов как на стороне базы данных, сервера, так и на стороне клиента.
Во-первых, записи должны быть извлечены в пул буферов базы данных.
После этого записи отправляются по сети на сервер. На сервере драйвер JDBC выделит все необходимые объекты Java для представления набора результатов запроса.
Однако, поскольку JDBC Набор результатов не передается клиенту, записи должны быть преобразованы в сущности или DTOs .
Полученные объекты или DTO могут быть преобразованы в JSON и снова переданы по сети клиенту, где объекты JSON должны быть загружены в память браузера перед использованием для визуализации пользовательского интерфейса.
Для извлечения больших объемов данных требуется значительное количество ресурсов на нескольких уровнях (например, база данных, сервер, клиент).
Выборка только верхних N строк
Таким образом, поскольку дисплей пользовательского интерфейса имеет ограниченный размер, нет смысла извлекать больше данных, чем может быть отображено одновременно. Теперь, в зависимости от используемой вами базовой системы реляционных баз данных, предложение SQL, позволяющее ограничить размер набора результатов запроса, может отличаться.
SQL:Стандарт 2008
До SQL:2008 не существовало стандартного способа извлечения верхних N записей из заданного набора результатов. Стандартный синтаксис выглядит следующим образом:
SELECT
title
FROM
post
ORDER BY
id DESC
FETCH FIRST 5 ROWS ONLY
Обратите внимание на предложение ИЗВЛЕКАТЬ ТОЛЬКО ПЕРВЫЕ 5 СТРОК , которое сообщает базе данных, что мы заинтересованы в извлечении только первых 5 записей. Еще одна вещь, на которую следует обратить внимание, заключается в том, что мы используем предложение ORDER BY , поскольку в противном случае нет гарантии, какие записи будут первыми включены в возвращаемый набор результатов.
Предложение SQL:2008 Top-N записей поддерживается в Oracle с 12c, SQL Server с 2012 года и PostgreSQL с 8.4.
SQL Server
Хотя SQL Server поддерживает стандартный синтаксис SQL:2008 Top-N, вам также необходимо указать предложение OFFSET:
SELECT
title
FROM
post
ORDER BY
id DESC
OFFSET 0 ROWS
FETCH FIRST 5 ROWS ONLY
Поскольку нас интересуют только записи Top-N, в нашем случае СМЕЩЕНИЕ равно 0.
До SQL Server 2012 вам приходилось использовать TOP для ограничения размера результирующего набора:
SELECT TOP 5
title
FROM post
ORDER BY id DESC
Oracle 11g и более старые версии
До версии 12c для извлечения записей верхнего уровня N вам приходилось использовать производную таблицу и псевдоколонку ROWNUM :
SELECT *
FROM (
SELECT
title
FROM
post
ORDER BY
id DESC
)
WHERE ROWNUM <= 5
Причина, по которой используется внешняя производная таблица, заключается в том, что значение псевдоколонки ROWNUM присваивается до выполнения предложения ORDER BY. Используя производную таблицу, мы можем убедиться, что псевдоколоночка ROWNUM, которую мы используем для фильтрации записей Top-N, назначена после сортировки базового набора результатов.
MySQL и PostgreSQL 8.3 или старше
Традиционно MySQL и PostgreSQL используют предложение LIMIT для ограничения набора результатов записями Top-N:
SELECT
title
FROM
post
ORDER BY
id DESC
LIMIT 5
Вывод
Получение нужного объема данных очень важно для производительности приложения. К счастью, SQL позволяет нам ограничить данный запрос записями Top-N, используя либо стандартный синтаксис SQL:2008, либо альтернативы для конкретной базы данных.