SQL-Ex blog
SQL ORDER BY: 5 правил сортировки на профессиональном уровне
Добавил Sergey Moiseenko on Среда, 23 февраля. 2022
Уродливо. Так выглядят неупорядоченные данные. Данные легче воспринимаются зрительно, если они отсортированы. Для этого и служит SQL ORDER BY. Используйте один или несколько столбцов или выражений, на основании которых должны сортироваться данные. Затем добавьте ASC или DESC для сортировки по возрастанию или убыванию.
Синтаксис SQL 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.
- Ограничиватьрезультаты в предложениях WHERE и OFFSET/FETCH.
- Не использовать ORDER BY при сортировке по ключу кластеризованного индекса.
- Не использовать ORDER BY, когда строковый столбец содержит числа. Сначала преобразуйте строковый столбец к типу INT.
- Не используете SELECT INTO #TempTable вместе с ORDER BY. Вместо этого сначала создайте временную таблицу с дополнительным столбцом identity.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
SQL оператор ORDER BY
В этом учебном материале вы узнаете, как использовать SQL оператор ORDER BY с синтаксисом и примерами.
Описание
SQL оператор ORDER BY используется для сортировки записей в наборе результатов запроса SELECT.
Синтаксис
Синтаксис для оператора ORDER BY в SQL.
SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];
Параметры или аргумент
expressions Столбцы или расчеты, которые вы хотите получить tables Таблицы, из которых вы хотите получить записи. В предложении FROM должна быть указана хотя бы одна таблица WHERE conditions Необязательный. Условия, которые должны быть выполнены для записей, которые будут выбраны ASC Необязательный. ASC сортирует результирующий набор в порядке возрастания по expressions . Это поведение по умолчанию, если модификатор не указан. DESC Необязательный. DESC сортирует результирующий набор в порядке убывания по expressions
Примечание
- Если модификатор ASC или DESC не указан в предложении ORDER BY, результаты будут отсортированы по expressions в порядке возрастания. Это эквивалентно ORDER BY expressions ASC
Пример — сортировка результатов по возрастанию
Чтобы отсортировать результаты в порядке возрастания, вы можете указать атрибут ASC. Если после поля в предложении ORDER BY не указано значение (ASC или DESC), порядок сортировки по умолчанию будет соответствовать возрастающему. Давайте рассмотрим это дальше.
В этом примере у нас есть таблица customers со следующими данными:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
Введите следующий SQL оператор.
Сортировка (ORDER) — Основы реляционных баз данных
У выборки данных из базы нет определенного порядка. Точнее порядок есть, но он логически не обоснован. По умолчанию база возвращает данные в том порядке, в котором ей удобнее. По счастливому стечению обстоятельств этот порядок может совпасть с желаемым, но рассчитывать на это нельзя.
Теоретически порядок может поменяться при следующем запросе, или когда количество данных в таблице изменится. Обновление версии базы данных тоже может повлиять на это.
В этом уроке мы разберем, как сортировать данные в базе в том порядке, который нам нужен. Для этого используется запрос ORDER BY .
Общая схема запроса на сортировку
Допустим, нам нужно сортировать данные в базе:
Сортировка задается с помощью части ORDER BY , за которой следует имя поля — по нему происходит сортировка:
SELECT * FROM users ORDER BY username; Такой запрос вернет всю информацию о пользователях, отсортированную по username . Если порядок не указан, то сортировка происходит по возрастанию ASC .
Сортировка подразумевает сравнение значений между собой. По какому принципу база данных сортирует строки username как в примере выше? Самый распространенный способ — лексикографический порядок, то есть по алфавиту.
База данных знает, как сравнивать не только числа, но и строки, и даты. Сортировка по датам — крайне частая операция, выглядит она идентично любой другой сортировке:
SELECT * FROM users ORDER BY created_at; Здесь мы отсортировали пользователей по дате создания в прямом порядке, то есть по возрастанию.
Управление порядком сортировки
Сортировать данные можно разными способами:
- В прямом порядке
- В обратном порядке
- По нескольким полям
Прямой порядок сортировки
Если ничего дополнительно не указывать, то ORDER BY сортирует в прямом порядке — от меньшего к большему. Запрос выше — это сокращенная версия полного запроса с сортировкой. Полный запрос включает в себя ASC , который подставляется автоматически, если ничего не указано:
SELECT * FROM users ORDER BY created_at ASC; -- То же самое, что и SELECT * FROM users ORDER BY created_at; Обратный порядок сортировки
Чтобы выполнить сортировку в обратном порядке, указывается DESC :
SELECT * FROM users ORDER BY created_at DESC; Сортировка по нескольким полям
Иногда нужно сортировать данные сразу по нескольким полям. Этого можно достичь, если перечислить поля через запятую:
SELECT * FROM users ORDER BY first_name, created_at; Порядок сортировки в таком случае задается для каждого поля индивидуально:
SELECT * FROM users ORDER BY first_name DESC, created_at DESC; SELECT first_name, created_at FROM users ORDER BY first_name ASC, created_at DESC; | first_name | created_at |
|---|---|
| Maryse | 2019-03-04 13:03:01 |
| Maryse | 2018-12-06 10:24:04 |
| Maryse | 2018-12-06 10:09:48 |
| Sunny | 2019-03-04 13:04:28 |
| Sunny | 2018-12-06 06:19:30 |
| Tanya | 2018-12-06 07:12:01 |
| Tanya | 2018-12-05 23:55:15 |
В этой выборке сначала выполняется сортировка по имени в прямом порядке. Затем внутри групп с одинаковым именем данные сортируются по created_at в обратном порядке.
Еще у сортировки есть один тонкий момент, который связан с полями NULL .
Сортировка NULL-значений
Если поле содержит NULL , и ничего не указано дополнительно, то считается, что NULL больше любого значения. Получается, что при прямой сортировке они окажутся в конце выборки, а при обратной — в начале. Этим поведением можно управлять с помощью фразы NULLS FIRST :
-- сортируем по возрастанию даты (поле "created_at") -- поведение по умолчанию: -- поля, которые содержат NULL, идут последними SELECT * FROM users ORDER BY created_at ASC; -- сортируем по возрастанию даты (поле "created_at") -- поля, которые содержат NULL, идут первыми SELECT * FROM users ORDER BY created_at ASC NULLS FIRST; -- сортируем по убыванию даты (поле "created_at") -- поведение по умолчанию: -- поля, которые содержат NULL, идут первыми SELECT * FROM users ORDER BY created_at DESC; -- сортируем по убыванию даты (поле "created_at") -- поля, которые содержат NULL, идут последними SELECT * FROM users ORDER BY created_at DESC NULLS LAST; Открыть доступ
Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно
- 130 курсов, 2000+ часов теории
- 1000 практических заданий в браузере
- 360 000 студентов
Наши выпускники работают в компаниях:
SQL ORDER BY Ключевое слово
Команда ORDER BY команда используется для сортировки результирующего набора в порядке возрастания или убывания. Команда ORDER BY по умолчанию команда сортирует результирующий набор в порядке возрастания. Чтобы отсортировать записи в порядке убывания, используйте ключевое слово DESC .
Следующая инструкция SQL выбирает все столбцы из таблицы «Клиенты», отсортированные в столбце «CustomerName»:
Пример
SELECT * FROM Customers
ORDER BY CustomerName;
ASC — Команда
Команда ASC используется для сортировки возвращаемых данных в порядке возрастания.
Следующая инструкция SQL выбирает все столбцы из таблицы «Клиенты», отсортированные в столбце «CustomerName»:
Пример
SELECT * FROM Customers
ORDER BY CustomerName ASC;
DESC — Описание
Команда DESC используется для сортировки возвращаемых данных в порядке убывания.
Следующая инструкция SQL выбирает все столбцы из таблицы «Клиенты», отсортированные по убыванию в столбце «CustomerName»:
Пример
SELECT * FROM Customers
ORDER BY CustomerName DESC;
Мы только что запустили
SchoolsW3 видео
курс сегодня!
Сообщить об ошибке
Если вы хотите сообщить об ошибке или внести предложение, не стесняйтесь отправлять на электронное письмо:
Ваше предложение:
Спасибо Вам за то, что помогаете!
Ваше сообщение было отправлено в SchoolsW3.
Schoolsw3 оптимизирован для бесплатного обучения, проверки и подготовки знаний. Примеры в редакторе упрощают и улучшают чтение и базовое понимание. Учебники, ссылки, примеры постоянно пересматриваются, чтобы избежать ошибок, но не возможно гарантировать полную правильность всего содержания. Некоторые страницы сайта могут быть не переведены на РУССКИЙ язык, можно отправить страницу как ошибку, так же можете самостоятельно заняться переводом. Используя данный сайт, вы соглашаетесь прочитать и принять Условия к использованию, Cookies и политика конфиденциальности.