Блог

Главная - Вопросы по программированию - Как запрос с ORDER BY может выполняться быстрее, чем тот же запрос без упорядочения?

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

#sql-server #query-optimization

#sql-server #оптимизация запросов

Вопрос:

У меня есть база данных SQL Server с таблицей EventJournal со следующими столбцами:

 Ordering (bigint, primary key)
PersistenceID (nvarchar(255))
SequenceNr (bigint)
Payload (varbinary(max))

Другие столбцы опущены для ясности. В дополнение к первичному ключу при упорядочении существует уникальное ограничение на PersistenceId SequenceNr .

Если я выполняю запрос

 select top 100 * from EventJournal where PersistenceID like 'msc:%'

… выполнение занимает очень много времени (таблица содержит более 100 миллионов строк)

Но если я добавлю упорядочение к результатам:

 select top 100 * from EventJournal where PersistenceID like 'msc:%' order by Ordering

… затем он немедленно возвращает результат.

План выполнения для обоих запросов одинаков и, по сути, представляет собой сканирование кластеризованного индекса на PK. Тогда почему выполнение первого запроса занимает много времени?

Вот определение таблицы:

 CREATE TABLE [dbo].[EventJournal](
    [PersistenceID] [nvarchar](255) NOT NULL,
    [SequenceNr] [bigint] NOT NULL,
    [IsDeleted] [bit] NOT NULL,
    [Manifest] [nvarchar](500) NOT NULL,
    [Payload] [varbinary](max) NOT NULL,
    [Timestamp] [bigint] NOT NULL,
    [Tags] [nvarchar](100) NULL,
    [Ordering] [bigint] IDENTITY(1,1) NOT NULL,
    [SerializerId] [int] NULL,
 CONSTRAINT [PK_EventJournal] PRIMARY KEY CLUSTERED 
(
    [Ordering] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [QU_EventJournal] UNIQUE NONCLUSTERED 
(
    [PersistenceID] ASC,
    [SequenceNr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Это 1-й план:

https://www.brentozar.com/pastetheplan/?id=SJ3kCo-Fv

И вот 2-й:

https://www.brentozar.com/pastetheplan/?id=Hy1B0ibtP

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

1. Не могли бы вы вставить оба ФАКТИЧЕСКИХ плана, используя brentozar.com/pastetheplan и скопировать обе ссылки сюда?

2. Я думаю, вам следует изучить свои планы в разделе «Сканирование ПК». Ваш второй план, вероятно, имеет Ordered = True, в то время как первый может иметь значение False, поэтому они разные

3. Я сомневаюсь, что 2 плана выполнения идентичны.

4. Вы абсолютно правы, они разные. Но он не хочет показывать их нам

5. Я бы хотел показать вам планы! Работаем над этим…

Ответ №1:

Как я упоминал в своих комментариях, планы разные, разница в методе доступа:

Первый план использует неупорядоченное сканирование:

введите описание изображения здесь

И второй план использует упорядоченное сканирование:

введите описание изображения здесь

Кстати, другой ответ предполагает бесполезность index .

SQL Server НЕ будет использовать это index , поскольку это эквивалентно некластерному index уже установленному. Поскольку индекс QU_EventJournal on ([PersistenceID], [SequenceNr]) не использовался, точно так (PersistenceID, Ordering) же не будет использоваться индекс on . Оба этих индекса есть PersistenceID Ordering в индексе как Ordering есть clustered index key , поэтому он представлен в index on ([PersistenceID], [SequenceNr]) , даже если вы не видите его в определении. Предлагаемый индекс также будет больше, поскольку он не определен как unique , а размеры других полей одинаковы: Ordering is bigint , SequenceNr is bigint .

Ошибочно думать, что в индексе по 2 полям второе поле (упорядочение) можно использовать, чтобы избежать сортировки во втором запросе, это неправда.

Например, в индексе on PersistenceID Ordering могут быть строки, подобные этим:

  • msc:123, 100
  • msc:124, 5
  • msc:124, 6
  • msc:125, 1

Я надеюсь, вы ясно видите, что индекс упорядочен по PersistenceID , Ordering , но ожидается, что результат второго запроса будет

  • msc:125, 1
  • msc:124, 5
  • msc:124, 6
  • msc:123, 100

Итак SORT , оператор необходим, поэтому этот индекс использоваться не будет.

Теперь к вашему вопросу:

разве анализатор запросов не должен использовать отсутствие ORDER BY как возможность построения более эффективного плана выполнения без ограничений упорядочения

Да, вы правы, без order by сервер может свободно выбирать как ordered и unordered сканировать, и да, вы правы в этом:

Я также не понимаю, почему использование TOP без ORDER BY является плохой практикой, если мне нужны КАКИЕ-ЛИБО N строк из результата

Если вам не нужно упорядочивать top N по, потому что вы просто хотите посмотреть, какие записи содержат в них ‘msc:’, вам не следует добавлять order by, потому что это может вызвать a SORT в вашем плане.

И на ваш главный вопрос:

Тогда почему выполнение первого запроса занимает много времени?

Ответ: это было чистое совпадение. Ваши данные закладываются таким образом, что строки с ‘msc:’ в них идут первыми, в порядке, определенном упорядочением. И если вы просматриваете свой индекс не по порядку, они находятся только в середине или в конце таблицы.

Если вы ищете другой шаблон в PersistenceID unordered scan , будет быстрее

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

1. Большое спасибо, очень хорошо объяснил.