Почему SQL Server не гарантирует сортировку результатов без ORDER BY

Автор оригинала: Brent Ozar
  • Перевод
И снова здравствуйте. В июне OTUS вновь запускает курс «MS SQL Server разработчик», традиционно в преддверии старта курса мы начинаем делиться с вами материалом по теме.




Если в вашем запросе отсутствует ORDER BY, то вы не можете быть уверены в том, что сортировка результатов не изменится со временем.

Конечно, поначалу все будет довольно предсказуемо, но по мере того, как происходят изменения (в индексах, таблицах, конфигурации сервера, объеме ваших данных), вы можете столкнуться с некоторыми неприятными сюрпризами.

Давайте начнем с чего-нибудь простого: выполним SELECT для таблицы Users базы данных Stack Overflow. В этой таблице есть кластерный индекс по колонке Id, который начинается с единицы и увеличивается до триллиона. Для этого запроса данные возвращаются в порядке кластерного индекса:



Но если создать индекс на DisplayName и Location, то SQL Server внезапно решит использовать новый индекс, а не кластерный:



Вот план выполнения:



Почему SQL Server решил использовать этот индекс, хотя ему не нужно было сортировать по DisplayName и Location? Потому что этот индекс — наименьшая копия данных, которые необходимо получить. Давайте посмотрим на размеры индексов с помощью sp_BlitzIndex:



В кластерном индексе (CX/PK) около 8,9 млн строк и его размер 1,1 ГБ.

В некластерном индексе для DisplayName, Location также около 8,9 млн строк, но его размер всего 368 МБ. Если вам нужно сделать scan для получения результатов запроса, то почему бы не выбрать наименьший источник данных, так как это будет быстрее. Именно по этой причине SQL Server поступил таким образом.

“Да, но мой запрос содержит WHERE”.


Хорошо, теперь, когда у нас есть индекс для DisplayName и Location, давайте попробуем выполнить запрос, который ищет конкретное имя (DisplayName). Результаты получаются отсортированными по DisplayName:



В плане выполнения видно, что используется индекс по DisplayName и Location:



Но если искать по другому значению, то результаты уже не будут отсортированы по DisplayName:



SQL Server обнаружил, что Alex’ов много и более разумно выполнить Clustered Index Scan вместо Index Seek + Key Lookup:



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

В последнее время я столкнулся с гораздо более сложными случаями:

  • Удаление индекса, который использовался в запросе
  • Включение принудительной параметризации (Forced Parameterization), которая изменяет оценку предполагаемого количества строк, заставляя SQL Server выбирать другой индекс
  • Изменение уровня совместимости базы данных (Compatibility Level) с включением нового механизма оценки кардинальности (Cardinality Estimator), который выдает другой вариант плана.



Узнать подробнее о курсе.


OTUS. Онлайн-образование
Цифровые навыки от ведущих экспертов

Комментарии 19

    +2
    Переиспользование страниц в файлах базы приведет к тому, что данные таблицы без кластерного индекса будут выводиться в порядке их хранения = не по порядку добавления.
    Нужен порядок на выборке — пишется order by
      –1

      Вроде это не специфично для ms sql. Навскидку разве что mysql в myisam и innodb по PK отсортирует, и то без гарантий кажется

        +1

        PostgreSQL вообще не сортирует результат выборки, а выдаёт в том порядке, в каком прочитал с диска. Нужен порядок — указывай ORDER BY явно.

          +1

          Собственно после знакомства с ним я и стал добавлять ORDER BY почти во все запросы, результат которых отдаётся пользователю. Чаще всего ORDER BY id DESC или BY name/title/… ASC получается нужно по дефолту пользователям или их "заместителю" вроде дизайнера или продакт-оунера.

            +1
            Более того, порядок записей может меняться при разных значениях LIMIT OFFSET на одном и том же запросе на одних и тех же данных. Поэтому при создании какого-нибудь GridView — обязательно указывать сортировку по-умолчанию. Многие начинающие использовать постгрес с этим сталкиваются.
          +4

          Совершенно не важно, почему при отсутствии ORDER BY сортировка будет разная.
          Совершенно не важно, что это можно иногда предсказать.
          Главное: пишите ORDER BY если это нужно.

            +3
            Или то же другими словами: TL;DR: потому, что не должен
            +8
            Я вот чего не понимаю. Если нам важен порядок следования записей — мы добавляем ORDER BY. Если нам он не важен — не добавляем. Как тогда может прийти в голову мысль о сохранении порядка, который нам не важен?! Какого порядка, если мы его никак не определили?

            Интересно, это у меня так мозги трансформировались от длительной работы с SQL, или всё-же некая логика в моих рассуждениях есть?
              +2

              Это такая реклама (антиреклама?) своеобразная у компании. У этого автора много статей с подобной ерундой)

                +2

                Нет, не только у Вас.

                +6

                "Почему SQL Server не гарантирует сортировку результатов, если мы запросили результаты без сортировки?"

                  0

                  Я решил посмотреть что за курс у ребят, попытался зарегаться, чтобы пройти "вводный тест", но смс, которая необходима для верификации телефонного номера не пришла (видимо, что проблема в том, что номер указал не +7), но пришло письмо на почту и теперь я думаю, как долго меня будут мучать рекламными письмами...

                    0
                    К сожалению, дефолтное поведение бывает обманчиво. Предлагаю по дефолту перемешивать результат перед выдачей.
                      0
                      Зачем делать лишнюю работу? Серверу, как правило, и так есть чем заняться.
                      Кстати, это относится и к исходному вопросу.
                      +2
                      Вчера были по 5, но очень большие. А сегодня — по 3, но очень маленькие. Вот весь смысл поста. SQL — не бабка, судачить и придумывать не будет. Есть требование сортировки — гарантированно выполняет. Нет — выдает так, как считает оптимальным по трудоемкости выполнения.

                      Одно справедливо — наше наблюдение, что раки вчера были по 5 выдаваемый порядок соответствует кластерному индексу, можно смело выбрасывать в помойку — так как то, что было вчера, никак не поможет нам сегодня. Но опять же, черт побери, незнание этих нюансов не делает хорошего разработчика моделей плохим! Сортировка в отсутствие ORDER BY — это, по сути, undefined behavior. Хороший БД-модельер должен прежде всего знать не анти-, а позитивное множество средств, которыми можно эффективно автоматизировть бизнес-процессы.

                      Аналогия из мира схемотехники — что будет, если свободные входы логических элементов оставлять в воздухе? Чтобы ответить на этот вопрос, нужно знать особенности построения элементов различных типов — КМОП, ТТЛ, ТТЛШ. А можно просто всегда придерживаться правил, и фиксировать потенциал на свободном входе так, как предписано ТУ для микросхем, и даже не вдаваться в детали «а что будет, если..» — и это не будет признаком плохого, а будет, наоборот, признаком хорошего индустриального разработчика. При отладке, правда, нужно будет в уме построить вероятный отказ, связанный, например, с висящим в воздухе входом — но это тоже уже не тема туториала в любом случае…
                        0

                        Выборка и сортировка суть разные действия.


                        Например. Из колоды надо выбрать карты. Или из колоды надо выбрать карты, например все красные. Или из колоды надо выбрать все старшие красные карты. Во всех случаях выборка (SELECT) работает и работает быстро. Если надо сортировать (ORDER BY) это уже дополнительные расходы. И не важно какой алгоритм сортировки используется. В любом случае это дополнительные расходы.

                          0

                          Не в любом. Если алгоритм выборки гарантированно сортирует по нужному критерию, то дополнительных расходов нет.

                            0

                            Еще раз. Выборка и сортировка разные действия.


                            Алгоритм выборки… сортирует

                            Хотите сказать сортировка вставкой? Приемлем на малых выборках.

                              0

                              Хочу сказать выборка из заранее упорядоченного множества типа упомянутой выборки по кластерному индексу. То есть не сам алгоритм выборки сортирует, а выбирает из уже когда отсортированного, пускай даже отсортированного "нечаянно", просто по порядку добавления в генеральное множество.

                        Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                        Самое читаемое