Мне было интересно, как сервер sql сортирует данные. Я заметил, что если у меня есть таблица, которая не содержит столбец «Id», и вы выбираете данные без «ORDER BY», сервер sql не выполняет автоматическую сортировку по основному столбцу.
Кто-нибудь знает, какое правило следует серверу sql для сортировки данных?
Хотя полезно задаться вопросом, как можно объяснить, что вы часто видите один и тот же порядок, я хотел бы отметить, что никогда не стоит полагаться на неявный порядок, вызванный конкретной реализацией базового механизма базы данных. Другими словами, приятно знать, почему, но вы никогда не должны полагаться на это. Для MS SQL единственное, что надежно доставляет строки в определенном порядке, — это явное предложение ORDER BY.
Мало того, что разные RDMBS ведут себя по-разному, один конкретный экземпляр может вести себя по-разному из-за обновления (исправления). Мало того, даже состояние программного обеспечения СУБД может оказывать влияние: «теплая» база данных ведет себя иначе, чем «холодная», маленькая таблица ведет себя иначе, чем большая.
Даже если у вас есть справочная информация о реализации (например: «имеется кластеризованный индекс, поэтому, вероятно, данные будут возвращены в порядке кластеризованного индекса»), всегда есть вероятность, что есть другой механизм, который вам неизвестен. не знаю об этом, что приводит к тому, что строки возвращаются в другом порядке (пример 1: «если другой сеанс только что выполнил полное сканирование таблицы с явным ORDER BY, набор результатов мог быть кэширован; последующее полное сканирование попытается вернуть строки из cache"; пример 2: "GROUP BY может быть реализован путем сортировки данных, что влияет на порядок возврата строк"; пример 3: "Если все выбранные столбцы находятся во вторичном индексе, который уже кэширован в памяти, механизм может сканировать вторичный индекс вместо таблицы, что, скорее всего, возвращает строки в порядке вторичного индекса").
Вот очень простой тест, иллюстрирующий некоторые из моих тезисов.
Во-первых, запустите SQL-сервер (я использую 2008). Создайте эту таблицу:
create table test_order (
id int not null identity(1,1) primary key
, name varchar(10) not null
)
Изучите таблицу и убедитесь, что кластеризованный индекс был создан для поддержки primary key в столбце id. Например, в студии управления сервером sql вы можете использовать древовидное представление и перейти к папке индексов под вашей таблицей. Там вы должны увидеть один индекс с именем вроде: PK__test_ord__3213E83F03317E3D (Clustered)
Вставьте первую строку с этим оператором:
insert into test_order(name)
select RAND()
Вставьте больше строк, повторив это утверждение 16 раз:
insert into test_order(name)
select RAND()
from test_order
Теперь у вас должно быть 65536 строк:
select COUNT(*)
from test_order
Теперь выберите все строки, не используя порядок:
select *
from test_order
Скорее всего, результаты будут возвращены по порядку первичного ключа (хотя гарантии нет). Вот результат, который я получил (который действительно по порядку первичного ключа):
# id name
1 1 0.605831
2 2 0.517251
3 3 0.52326
. . .......
65536 65536 0.902214
(# - это не столбец, а порядковая позиция строки в результате)
Теперь создайте вторичный индекс для столбца name:
create index idx_name on test_order(name)
Выберите все строки, но получите только столбец name:
select name
from test_order
Скорее всего, результаты будут возвращены по порядку вторичного индекса idx_name, поскольку запрос может быть разрешен только путем сканирования индекса (i.o.w. idx_name является покрывающим индексом). Вот результат, который я получил, действительно по заказу name.
# name
1 0.0185732
2 0.0185732
. .........
65536 0.981894
Теперь снова выберите все столбцы и все строки:
select *
from test_order
Вот результат, который я получил:
# id name
1 17 0.0185732
2 18 0.0185732
3 19 0.0185732
... .. .........
как видите, совсем не так, как в первый раз, когда мы запускали этот запрос. (Похоже, что строки упорядочены по вторичному индексу, но у меня нет объяснения, почему это должно быть так).
В любом случае, суть в том, что не полагайтесь на неявный порядок. Вы можете придумать объяснение, почему можно наблюдать тот или иной порядок, но даже в этом случае вы не всегда можете его предсказать (как в последнем случае), не имея глубоких знаний о реализации и состоянии времени выполнения.
Если вы не укажете предложение ORDER BY явно, не будет гарантированного порядка, в котором будут отсортированы результаты. Даже не гарантируется, что он будет основан на кластеризованном индексе.
Вы можете увидеть пример этого в этой статьи.
AS SQL основан на теории Set, и Set не гарантирует никакого порядка, поэтому, если вы не укажете конкретный порядок явно, порядок не будет гарантирован.
У меня был аналогичный опыт с SQL Server, возвращающим результаты, отсортированные не так, как я ожидал. Я обнаружил, что если вы укажете табличную подсказку в операторе select, указав имя кластеризованного индекса, вы получите результаты, упорядоченные так, как вы хотите:
select * from test_order WITH (INDEX([ClusteredIndexName]))
