SQL ORDER BY: 5 правил сортировки на профессиональном уровне
Пересказ статьи Edwin Sanchez. SQL ORDER BY The 5 Do’s and Don’ts to Sort Data Like a Pro
Уродливо. Так выглядят неупорядоченные данные. Данные легче воспринимаются зрительно, если они отсортированы. Для этого и служит SQL ORDER BY. Используйте один или несколько столбцов или выражений, на основании которых должны сортироваться данные. Затем добавьте ASC или DESC для сортировки по возрастанию или убыванию.
Синтаксис SQL ORDER BY:
Выражение ORDER BY может простым, каким является список столбцов или выражений. Оно может быть также условным при использовании блока CASE WHEN.
Это добавляет гибкости.
Вы можете также использовать пейджинг (разбивку на страницы) посредством OFFSET и FETCH. Задавайте номера отбрасываемых и выводимых строк.
Но есть и плохие новости.
Добавление ORDER BY в запросы может замедлить их выполнение. Есть и другие предосторожности, которые могут сделать ORDER BY "нерабочим". Вы не можете использовать их всякий раз, когда захотите, т.к. возможны штрафы. Так что же нам делать?
В этой статье мы исследуем, что можно, а что нельзя делать с помощью ORDER BY. Каждый пункт связан с некоторой проблемой и её решением.
Готовы?
Индексы предназначены для быстрого поиска. И наличие его на столбцах, которые вы используете в предложении ORDER BY может ускорить ваш запрос.
Давайте начнем применение ORDER BY со столбцом без индекса. Мы будем использовать тестовую базу данных AdventureWorks. Перед выполнением запроса ниже отключите индекс IX_SalesOrderDetail_ProductID в таблице SalesOrderDetail. Затем нажмите Ctrl-M, и выполните его.
Код выше будет выводить статистику ввода/вывода на вкладке Messages в SQL Server Management Studio. Вы увидите план выполнения на другой вкладке.
Сначала давайте получим логические чтения из STATISTICS IO. Проверьте на Рис.1.
Рис.1. Логические чтения при использовании ORDER BY на неиндексируемом столбце. (отформатировано с помощью statisticsparser.com)
Без индекса запрос использовал 1313 логических чтений. А что за WorkTable? Это означает, что SQL Server использовал TempDB для выполнения сортировки.
А что происходило под капотом? Давайте проверим план выполнения на Рис.2.
Рис.2. План выполнения запроса, использующего ORDER BY по неиндексированному столбцу.
Вы видите оператор Parallelism (Gather Streams)? Это означает, что SQL Server использовал более одного процессора для обработки запроса. Запрос был достаточно тяжелым, что потребовало больше ЦП.
Итак, что если SQL Server использовал TempDB и несколько процессоров? Это плохо для простого запроса.
Что получится, если снова включить индекс? Давайте выясним. Перестроим индекс IX_SalesOrderDetail_ProductID. Затем снова выполним вышеприведенный запрос.
Проверьте теперь логические чтения на рис.3.
Рис.3. Логические чтения после перестройки индекса.
Много лучше. Мы урезали число логических чтений почти вдвое. Это означает, что индекс уменьшил потребление ресурсов. Как насчет WorkTable? Она ушла! Нет необходимости в использовании TempDB.
А план выполнения? Смотрите Рис.4.
Рис.4. Новый план выполнения стал проще после перестройки индекса.
Видите? План стал проще. Нет необходимости в дополнительных ЦП для сортировки тех же 121317 строк.
Итак, вывод: Убедитесь, что столбцы, которые вы используете в ORDER BY проиндексированы.
НО ЧТО, ЕСЛИ ДОБАВЛЕНИЕ ИНДЕКСА ПОВЛИЯЕТ НА ПРОИЗВОДИТЕЛЬНОСТЬ ЗАПИСИ?
Хороший вопрос.
Если это проблема, вы можете сбросить часть исходной таблицы во временную таблицу или оптимизированную в памяти таблицу. Затем проиндексируйте таблицу. Сделайте то же самое, если задействованы несколько таблиц. Затем оцените производительность запроса каждого варианта. Более быстрый вариант станет победителем.
Давайте возьмем другой запрос. Скажем, вам требуется вывести в приложении информацию о товаре с картинками. Изображения могут сделать запросы еще тяжелее. Итак, мы будем проверять не просто логические чтения, но также логические чтения LOB.
Вот код:
Он вернет 97 велосипедов с картинками. Их очень сложно просматривать на мобильном устройстве.
Вот сколько требуется логических чтений, чтобы вывести 97 товаров с изображениями. Посмотрите Рис.5.
Рис.5. Логические чтения и логические чтения LOB при использовании ORDER BY без OFFSET/FETCH и минимальным условием в WHERE. (Замечание. statisticsparser.com не показывает логические чтения LOB. Этот скриншот отредактирован на основе полученных результатов в SSMS)
667 логических чтений LOB появились в связи с выборкой изображений из 2 столбцов. Между тем, на остальное потребовалось 590 логических чтений.
На рис.6 показан план выполнения, поэтому мы можем сравнить его позже с лучшим планом.
Рис.6. План выполнения, использующий ORDER BY без OFFSET/FETCH и с минимальным условием WHERE.
Здесь мало что можно сказать, пока мы не увидим другой план выполнения.
Давайте теперь настроим запрос, чтобы обеспечить возврат минимального количества данных. Вот что мы собираемся сделать:
Вот отредактированный код:
Этот код можно еще улучшить, если поместить его в хранимую процедуру с параметрами типа номера страницы и числа строк. Номер страницы означает ту страницу, которую просматривает пользователь. Передача числа строк обеспечивает гибкость при подстраивании под разрешение экрана. Но это совсем другая история.
Теперь давайте посмотрим на логические чтения на Рис.7.
Рис.7. Меньше число логических чтений после упрощения запроса. В ORDER BY также используется OFFSET/FETCH.
Затем сравним рис.7 с рис.5. Ушли логические чтения LOB. Кроме того, значительно уменьшилось число логических чтений, поскольку результирующий набор так же уменьшился с 97 до 10.
А что делал SQL Server под капотом? Проверим план выполнения на Рис.8.
Рис.8. Более простой план выполнения после упрощения запроса и добавления OFFSET/FETCH в ORDER BY.
Теперь сравните рис.8 с рис.6. Без исследования каждого оператора мы можем увидеть, что этот новый план проще предыдущего.
Какой извлекаем урок? Упрощайте ваши запросы. Используйте OFFSET/FETCH, где это возможно.
Мы закончили с тем, что следует делать при использовании ORDER BY. Теперь перейдем к тому, чего следует избегать.
Поскольку это бесполезно.
Давайте покажем это на примере.
Затем проверим логические чтения обоих операторов на Рис.9.
Рис.9. 2 запроса к таблице Person показывают одинаковое число логических чтений. Один с ORDER BY, а другой - без него.
Оба имеют 17 логических чтений. Это логично, поскольку возвращаются те же 100 строк. Но имеют ли они одинаковые планы? Проверьте на Рис.10.
Рис.10. Один и тот же план, используется ли ORDER BY либо нет при сортировке по ключу кластеризованного индекса.
Наблюдаем одни и те же операторы и одинаковую стоимость запроса.
Но почему? При индексировании по одному или нескольким столбцам в кластеризованном индексе таблица физически сортируется по ключу кластеризованного индекса. Поэтому, даже если вы не сортируете по этому ключу, результат все равно будет отсортирован.
Резюме? Не вините себя, если вы не используете в подобных случаях ключ кластеризованного индекса в ORDER BY. Сохраните свою энергию на нескольких нажатиях клавиш.
Если вы сортируете по строковому столбцу, содержащему числа, не ждите сортировки в числовом порядке. В противном случае вас ждет большой сюрприз.
Вот пример.
Посмотрите вывод на Рис.11.
Рис.11. Порядок сортировки строкового столбца, содержащего числа. Числовой порядок не соблюдается.
На Рис.11 порядок сортировки соответствует лексикографическому. Чтобы это исправить, используйте преобразование к целому типу.
Проверьте исправленный вывод на Рис.12.
Рис.12. Преобразование к INT исправляет сортировку строкового столбца, содержащего числа.
Итак, вместо ORDER BY <строковый столбец> используйте ORDER BY CAST(<строковый столбец> AS INT).
Желаемый вами порядок сортировки не гарантирован во временной таблице. Почитайте официальную документацию.
Давайте используем модифицированный код из предыдущего примера.
Единственным отличием от предыдущего примера является предложение INTO. Вывод будет тем же самым, что и на рис.11. Мы возвращаемся к квадрату 1, даже если преобразуем столбец к типу INT.
Вам нужно создать временную таблицу с помощью CREATE TABLE. Но включить дополнительный столбец identity, и сделать его первичным ключом. Затем использовать INSERT INTO для вставки во временную таблицу.
Вот исправленный код.
Теперь вывод будет тем же, что и на Рис.12. Это работает!
Мы рассмотрели основные капканы при использовании ORDER BY. Краткое резюме:
Делать
Не делать
ORDER BY <выражение_для_сортировки> [ASC | DESC]Выражение ORDER BY может простым, каким является список столбцов или выражений. Оно может быть также условным при использовании блока CASE WHEN.
Это добавляет гибкости.
Вы можете также использовать пейджинг (разбивку на страницы) посредством OFFSET и FETCH. Задавайте номера отбрасываемых и выводимых строк.
Но есть и плохие новости.
Добавление ORDER BY в запросы может замедлить их выполнение. Есть и другие предосторожности, которые могут сделать ORDER BY "нерабочим". Вы не можете использовать их всякий раз, когда захотите, т.к. возможны штрафы. Так что же нам делать?
В этой статье мы исследуем, что можно, а что нельзя делать с помощью ORDER BY. Каждый пункт связан с некоторой проблемой и её решением.
Готовы?
Что нужно делать с SQL ORDER BY?
1. Индексировать столбцы ORDER BY
Индексы предназначены для быстрого поиска. И наличие его на столбцах, которые вы используете в предложении ORDER BY может ускорить ваш запрос.
Давайте начнем применение ORDER BY со столбцом без индекса. Мы будем использовать тестовую базу данных AdventureWorks. Перед выполнением запроса ниже отключите индекс IX_SalesOrderDetail_ProductID в таблице SalesOrderDetail. Затем нажмите Ctrl-M, и выполните его.
-- Получить информацию о продукте, и отсортировать её по ProductID
USE AdventureWorks
GO
SET STATISTICS IO ON
GO
SELECT
ProductID
,OrderQty
,UnitPrice
,LineTotal
FROM Sales.SalesOrderDetail
ORDER BY ProductID
SET STATISTICS IO OFF
GOАнализ
Код выше будет выводить статистику ввода/вывода на вкладке Messages в SQL Server Management Studio. Вы увидите план выполнения на другой вкладке.
Без индекса
Сначала давайте получим логические чтения из STATISTICS IO. Проверьте на Рис.1.
Рис.1. Логические чтения при использовании ORDER BY на неиндексируемом столбце. (отформатировано с помощью statisticsparser.com)
Без индекса запрос использовал 1313 логических чтений. А что за WorkTable? Это означает, что SQL Server использовал TempDB для выполнения сортировки.
А что происходило под капотом? Давайте проверим план выполнения на Рис.2.
Рис.2. План выполнения запроса, использующего ORDER BY по неиндексированному столбцу.
Вы видите оператор Parallelism (Gather Streams)? Это означает, что SQL Server использовал более одного процессора для обработки запроса. Запрос был достаточно тяжелым, что потребовало больше ЦП.
Итак, что если SQL Server использовал TempDB и несколько процессоров? Это плохо для простого запроса.
С индексом
Что получится, если снова включить индекс? Давайте выясним. Перестроим индекс IX_SalesOrderDetail_ProductID. Затем снова выполним вышеприведенный запрос.
Проверьте теперь логические чтения на рис.3.
Рис.3. Логические чтения после перестройки индекса.
Много лучше. Мы урезали число логических чтений почти вдвое. Это означает, что индекс уменьшил потребление ресурсов. Как насчет WorkTable? Она ушла! Нет необходимости в использовании TempDB.
А план выполнения? Смотрите Рис.4.
Рис.4. Новый план выполнения стал проще после перестройки индекса.
Видите? План стал проще. Нет необходимости в дополнительных ЦП для сортировки тех же 121317 строк.
Итак, вывод: Убедитесь, что столбцы, которые вы используете в ORDER BY проиндексированы.
НО ЧТО, ЕСЛИ ДОБАВЛЕНИЕ ИНДЕКСА ПОВЛИЯЕТ НА ПРОИЗВОДИТЕЛЬНОСТЬ ЗАПИСИ?
Хороший вопрос.
Если это проблема, вы можете сбросить часть исходной таблицы во временную таблицу или оптимизированную в памяти таблицу. Затем проиндексируйте таблицу. Сделайте то же самое, если задействованы несколько таблиц. Затем оцените производительность запроса каждого варианта. Более быстрый вариант станет победителем.
Ограничивайте результаты с помощью WHERE и OFFSET/FETCH
Давайте возьмем другой запрос. Скажем, вам требуется вывести в приложении информацию о товаре с картинками. Изображения могут сделать запросы еще тяжелее. Итак, мы будем проверять не просто логические чтения, но также логические чтения LOB.
Вот код:
SET STATISTICS IO ON
GO
SELECT
a.ProductID
,a.Name AS ProductName
,a.ListPrice
,a.Color
,b.Name AS ProductSubcategory
,d.ThumbNailPhoto
,d.LargePhoto
FROM Production.Product a
INNER JOIN Production.ProductSubcategory b ON a.ProductSubcategoryID = b.ProductSubcategoryID
INNER JOIN Production.ProductProductPhoto c ON a.ProductID = c.ProductID
INNER JOIN Production.ProductPhoto d ON c.ProductPhotoID = d.ProductPhotoID
WHERE b.ProductCategoryID = 1 -- Велосипеды
ORDER BY ProductSubcategory, ProductName, a.Color
SET STATISTICS IO OFF
GOОн вернет 97 велосипедов с картинками. Их очень сложно просматривать на мобильном устройстве.
Анализ
Использование минимальных условий в WHERE без OFFSET/FETCH
Вот сколько требуется логических чтений, чтобы вывести 97 товаров с изображениями. Посмотрите Рис.5.
Рис.5. Логические чтения и логические чтения LOB при использовании ORDER BY без OFFSET/FETCH и минимальным условием в WHERE. (Замечание. statisticsparser.com не показывает логические чтения LOB. Этот скриншот отредактирован на основе полученных результатов в SSMS)
667 логических чтений LOB появились в связи с выборкой изображений из 2 столбцов. Между тем, на остальное потребовалось 590 логических чтений.
На рис.6 показан план выполнения, поэтому мы можем сравнить его позже с лучшим планом.
Рис.6. План выполнения, использующий ORDER BY без OFFSET/FETCH и с минимальным условием WHERE.
Здесь мало что можно сказать, пока мы не увидим другой план выполнения.
Использование дополнительного условия WHERE и OFFSET/FETCH в ORDER BY
Давайте теперь настроим запрос, чтобы обеспечить возврат минимального количества данных. Вот что мы собираемся сделать:
- Добавим условие на подкатегорию товара. Это соответствует тому, что в вызывающем приложении пользователь также может выбрать подкатегорию.
- Затем удалим подкатегорию товара из списка столбцов в SELECT и в ORDER BY.
- Наконец, добавим OFFSET/FETCH в ORDER BY. Только 10 товаров будет возвращаться и показываться в вызывающем приложении.
Вот отредактированный код:
DECLARE @pageNumber TINYINT = 1
DECLARE @noOfRows TINYINT = 10 -- на каждой странице будут показаны 10 товаров
SELECT
a.ProductID
,a.Name AS ProductName
,a.ListPrice
,a.Color
,d.ThumbNailPhoto
FROM Production.Product a
INNER JOIN Production.ProductSubcategory b ON a.ProductSubcategoryID = b.ProductSubcategoryID
INNER JOIN Production.ProductProductPhoto c ON a.ProductID = c.ProductID
INNER JOIN Production.ProductPhoto d ON c.ProductPhotoID = d.ProductPhotoID
WHERE b.ProductCategoryID = 1 -- велосипеды
AND a.ProductSubcategoryID = 2 -- дорожные велосипеды
ORDER BY ProductName, a.Color
OFFSET (@pageNumber-1)*@noOfRows ROWS FETCH NEXT @noOfRows ROWS ONLYЭтот код можно еще улучшить, если поместить его в хранимую процедуру с параметрами типа номера страницы и числа строк. Номер страницы означает ту страницу, которую просматривает пользователь. Передача числа строк обеспечивает гибкость при подстраивании под разрешение экрана. Но это совсем другая история.
Теперь давайте посмотрим на логические чтения на Рис.7.
Рис.7. Меньше число логических чтений после упрощения запроса. В ORDER BY также используется OFFSET/FETCH.
Затем сравним рис.7 с рис.5. Ушли логические чтения LOB. Кроме того, значительно уменьшилось число логических чтений, поскольку результирующий набор так же уменьшился с 97 до 10.
А что делал SQL Server под капотом? Проверим план выполнения на Рис.8.
Рис.8. Более простой план выполнения после упрощения запроса и добавления OFFSET/FETCH в ORDER BY.
Теперь сравните рис.8 с рис.6. Без исследования каждого оператора мы можем увидеть, что этот новый план проще предыдущего.
Какой извлекаем урок? Упрощайте ваши запросы. Используйте OFFSET/FETCH, где это возможно.
Не делайте этого с SQL ORDER BY
Мы закончили с тем, что следует делать при использовании ORDER BY. Теперь перейдем к тому, чего следует избегать.
Не используйте ORDER BY при сортировке по ключу кластеризованного индекса
Поскольку это бесполезно.
Давайте покажем это на примере.
SET STATISTICS IO ON
GO
-- Использовнаие ORDER BY с BusinessEntityID - первичный ключ
SELECT TOP 100 * FROM Person.Person
ORDER BY BusinessEntityID;
-- Вообще без использования ORDER BY
SELECT TOP 100 * FROM Person.Person;
SET STATISTICS IO OFF
GOЗатем проверим логические чтения обоих операторов на Рис.9.
Рис.9. 2 запроса к таблице Person показывают одинаковое число логических чтений. Один с ORDER BY, а другой - без него.
Оба имеют 17 логических чтений. Это логично, поскольку возвращаются те же 100 строк. Но имеют ли они одинаковые планы? Проверьте на Рис.10.
Рис.10. Один и тот же план, используется ли ORDER BY либо нет при сортировке по ключу кластеризованного индекса.
Наблюдаем одни и те же операторы и одинаковую стоимость запроса.
Но почему? При индексировании по одному или нескольким столбцам в кластеризованном индексе таблица физически сортируется по ключу кластеризованного индекса. Поэтому, даже если вы не сортируете по этому ключу, результат все равно будет отсортирован.
Резюме? Не вините себя, если вы не используете в подобных случаях ключ кластеризованного индекса в ORDER BY. Сохраните свою энергию на нескольких нажатиях клавиш.
Не используйте ORDER BY <строковый столбец>, когда строковый столбец содержит числа
Если вы сортируете по строковому столбцу, содержащему числа, не ждите сортировки в числовом порядке. В противном случае вас ждет большой сюрприз.
Вот пример.
SELECT
NationalIDNumber
,JobTitle
,HireDate
FROM HumanResources.Employee
ORDER BY NationalIDNumber;Посмотрите вывод на Рис.11.
Рис.11. Порядок сортировки строкового столбца, содержащего числа. Числовой порядок не соблюдается.
На Рис.11 порядок сортировки соответствует лексикографическому. Чтобы это исправить, используйте преобразование к целому типу.
SELECT
NationalIDNumber
,JobTitle
,HireDate
FROM HumanResources.Employee
ORDER BY CAST(NationalIDNumber AS INT)Проверьте исправленный вывод на Рис.12.
Рис.12. Преобразование к INT исправляет сортировку строкового столбца, содержащего числа.
Итак, вместо ORDER BY <строковый столбец> используйте ORDER BY CAST(<строковый столбец> AS INT).
Не используйте SELECT INTO #TempTable с ORDER BY
Желаемый вами порядок сортировки не гарантирован во временной таблице. Почитайте официальную документацию.
Давайте используем модифицированный код из предыдущего примера.
SELECT
NationalIDNumber
,JobTitle
,HireDate
INTO #temp
FROM HumanResources.Employee
ORDER BY CAST(NationalIDNumber AS INT);
SELECT * FROM #temp;Единственным отличием от предыдущего примера является предложение INTO. Вывод будет тем же самым, что и на рис.11. Мы возвращаемся к квадрату 1, даже если преобразуем столбец к типу INT.
Вам нужно создать временную таблицу с помощью CREATE TABLE. Но включить дополнительный столбец identity, и сделать его первичным ключом. Затем использовать INSERT INTO для вставки во временную таблицу.
Вот исправленный код.
CREATE TABLE #temp2
(
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
NationalIDNumber NVARCHAR(15) NOT NULL,
JobTitle NVARCHAR(50) NOT NULL,
HireDate DATE NOT NULL
)
GO
INSERT INTO #temp2
(NationalIDNumber, JobTitle, HireDate)
SELECT
NationalIDNumber
,JobTitle
,HireDate
FROM HumanResources.Employee
ORDER BY CAST(NationalIDNumber AS INT);
SELECT
NationalIDNumber
,JobTitle
,HireDate
FROM #Temp2;Теперь вывод будет тем же, что и на Рис.12. Это работает!
Выводы при использовании SQL ORDER BY
Мы рассмотрели основные капканы при использовании ORDER BY. Краткое резюме:
Делать
- Индексировать столбцы в ORDER BY.
- Ограничиватьрезультаты в предложениях WHERE и OFFSET/FETCH.
Не делать
- Не использовать ORDER BY при сортировке по ключу кластеризованного индекса.
- Не использовать ORDER BY, когда строковый столбец содержит числа. Сначала преобразуйте строковый столбец к типу INT.
- Не используете SELECT INTO #TempTable вместе с ORDER BY. Вместо этого сначала создайте временную таблицу с дополнительным столбцом identity.
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой