Почему делает верх (1) в индексированном столбце SQL Server медленным?

Я озадачен следующим. У меня есть БД с 10 миллионами строк, и (среди других индексов) в 1 столбце (campaignid_int) есть индекс.

Теперь у меня есть 700k строк, где агитайд действительно 3835

Для всех этих строк connectionid одинакова.

Я просто хочу узнать эту connectionid.

 use messaging_db;
 SELECT     TOP (1) connectionid
 FROM         outgoing_messages WITH (NOLOCK)
 WHERE     (campaignid_int = 3835)

Теперь для выполнения этого запроса требуется около 30 секунд!

I (с моим небольшим знанием db) ожидал бы, что он возьмет любую из строк и вернет мне, что connectionid

Если я тестирую этот же запрос для кампании, которая имеет только 1 запись, она идет очень быстро. Таким образом, индекс работает.

Как бы я справился с этим и почему это не сработало?

изменить:

estimated execution plan:

select (0%) - top (0%) - clustered index scan (100%)

Ответ 1

Из-за статистики вы должны явно попросить оптимизатор использовать созданный вами индекс вместо кластерного.

SELECT  TOP (1) connectionid
FROM    outgoing_messages WITH (NOLOCK, index(idx_connectionid))
WHERE  (campaignid_int = 3835)

Я надеюсь, что это решит проблему.

С уважением, Enrique

Ответ 2

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

Если вы добавили ORDER BY -clause в любой или некоторые из столбцов, которые проиндексировали его, он должен быть разрешен. Это, по крайней мере, решило это для меня.

Ответ 3

Вы не указываете предложение ORDER BY в своем запросе, поэтому оптимизатору не дается указание о порядке сортировки, на котором он должен выбрать верхнюю часть 1. SQL Server не просто возьмет случайную строку, он будет упорядочивать строки на что-то и взять верхнюю 1, и может быть выбор по заказу чего-то, что является неоптимальным. Я бы предположил, что вы добавляете предложение ORDER BY x, где x является кластеризованным ключом в этой таблице, вероятно, будет самым быстрым.

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

Ответ 4

Если столбец campaignid_int не проиндексирован, добавьте к нему индекс. Это должно ускорить запрос. В настоящее время я предполагаю, что вам нужно выполнить полное сканирование таблицы, чтобы найти совпадения для campaignid_int = 3835, прежде чем строка top(1) будет возвращена (фильтрация происходит до того, как результаты будут возвращены).

EDIT: Индекс уже установлен, но поскольку SQL Server выполняет кластерное сканирование индексов, оптимизатор игнорирует индекс. Вероятно, это связано с (многими) повторяющимися строками с тем же значением campaignid_int. Вы должны рассматривать индексирование по-разному или запрос в другом столбце, чтобы получить connectionid, который вы хотите.

Ответ 5

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

700k в 10 миллионов может быть недостаточно избирательным
и/или
connectionid необходимо включить так, чтобы весь запрос мог использовать только индекс

В противном случае оптимизатор решает, что он может также использовать индекс PK/clustered для фильтрации на campaignid_int и получить connectionid, чтобы избежать поиска по закладкам в 700k строк из текущего индекса.

Итак, я предлагаю это...

CREATE NONCLUSTERED INDEX IX_Foo ON MyTable (campaignid_int) INCLUDE (connectionid)

Ответ 6

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

Возможные решения? Создайте индексный композит, если у вас есть что-либо, чтобы скомпоновать его, то есть, например, составите его с датой отправки сообщения (если я правильно понял ваше дело), а затем выберите верхнюю 1 запись из списка с указанным идентификатором, упорядоченным по дате. Хотя я не уверен, будет ли это лучше (например, сложный индекс занимает больше места) - просто предположение.

РЕДАКТИРОВАТЬ: Я только что опробовал предложение о создании составного индекса, добавив столбец даты. Если вы это сделаете и укажите order by date в своем запросе, поиск индекса выполняется так, как ожидалось.

Ответ 7

Это не отвечает на ваш вопрос, но попробуйте использовать:

SET ROWCOUNT 1
SELECT     connectionid
 FROM         outgoing_messages WITH (NOLOCK)
 WHERE     (campaignid_int = 3835)

Я видел, что top (x) работает очень плохо в определенных ситуациях. Я уверен, что он выполняет полное сканирование таблицы. Возможно, ваш индекс на этом конкретном столбце нужно перестроить? Однако вышеизложенное стоит попробовать.

Ответ 8

но поскольку я указываю 'top (1)' it означает: дайте мне любой ряд. Почему это сначала пройдите через строки 700 тыс. вернуть? - reinier 30 мин назад

Извините, не могу комментировать, но ответ здесь заключается в том, что SQL-сервер не собирается понимать человеческий эквивалент "Принесите мне первый, который вы найдете", когда он услышит "Top 1". Вместо ожидаемого "Дайте мне любую строку" SQL Server отправляет и извлекает первые найденные строки. Только раз, когда он знает, что после извлечения всех строк сначала, а затем отбрасывания остальных. Очень тщательно, но в вашем случае не очень быстро.

Основная проблема, как говорят другие, - это ваша статистика и избирательность вашего индекса. Если у вас есть другое уникальное поле в вашей таблице (например, столбец идентификатора), попробуйте объединенный индекс в campaignid_int сначала, уникальный столбец второй. Поскольку вы только запрашиваете в campaignid_int, это должна быть первая часть ключа. Звуки стоит попробовать, так как этот индекс должен иметь более высокую избирательность, поэтому оптимизатор может использовать это лучше, чем сканирование индекса.