Почему выполнение является вершиной (1) на индексированном столбце в медленном SQL Server?
Я озадачен следующим. У меня есть DB приблизительно с 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 секунд для выполнения!
Я (с моим маленьким знанием дб) ожидал бы, что оно возьмет любую из строк и возвратит меня это connectionid
Если я тестирую этот тот же запрос на кампанию, которая только имеет 1 запись, он идет действительно быстро. Так индексные работы.
Как я занялся бы этим и почему это не работает?
править:
estimated execution plan:
select (0%) - top (0%) - clustered index scan (100%)
7 ответов
Из-за статистики вы должны явно попросить оптимизатор использовать созданный вами индекс вместо кластеризованного.
SELECT TOP (1) connectionid
FROM outgoing_messages WITH (NOLOCK, index(idx_connectionid))
WHERE (campaignid_int = 3835)
Надеюсь, это решит проблему.
С уважением, Энрике
Если столбец campaignid_int не проиндексирован, добавьте к нему индекс. Это должно ускорить запрос. Прямо сейчас я предполагаю, что вам нужно выполнить полное сканирование таблицы, чтобы найти совпадения для campaignid_int = 3835 , прежде чем будет возвращена строка top (1) (фильтрация выполняется перед возвратом результатов) .
РЕДАКТИРОВАТЬ: Индекс уже существует, но поскольку SQL Server выполняет сканирование кластерного индекса, оптимизатор проигнорировал индекс. Вероятно, это связано с (многими) повторяющимися строками с одинаковым значением campaignid_int . Вам следует подумать об индексировании по-другому или запросить другой столбец, чтобы получить идентификатор соединения , который вам нужен.
Это не ответ на ваш вопрос, но попробуйте использовать:
SET ROWCOUNT 1
SELECT connectionid
FROM outgoing_messages WITH (NOLOCK)
WHERE (campaignid_int = 3835)
Я видел, что top (x) также очень плохо работает в определенных ситуациях. Я уверен, что он выполняет полное сканирование таблицы. Возможно, ваш индекс для этого столбца нужно перестроить? Однако все вышеперечисленное стоит попробовать.
Вы не указываете предложение ORDER BY в своем запросе, поэтому оптимизатор не получает инструкции относительно порядка сортировки, из которого он должен выбирать первую 1. SQL Server не просто возьмет случайную строку, он упорядочит строки по чему-то и возьмет верхнюю 1, и может выбрать что-то неоптимальное. Я бы посоветовал вам добавить предложение ORDER BY x , где x - кластерный ключ в этой таблице, вероятно, будет самым быстрым.
Возможно, это не решит вашу проблему - на самом деле, я не уверен, что ожидаю этого из предоставленной вами статистики - но (а) это не повредит, и (б) вы сможете чтобы исключить это как способствующий фактор.
Индекс может быть бесполезным по 2 причинам:
- 700k из 10 миллионов может быть неселективным необходимо включить достаточное количество
- и / или
- connectionid, чтобы весь запрос мог использовать только индекс
В противном случае оптимизатор решает, что он может также использовать PK / кластеризованный индекс как для фильтрации по campaignid_int, так и для получения connectionid, чтобы Избегайте поиска по закладкам в 700 тыс. строк из текущего индекса.
Итак, я предлагаю это ...
CREATE NONCLUSTERED INDEX IX_Foo ON MyTable (campaignid_int) INCLUDE (connectionid)
Ваш запрос не работает так, как вы ожидали, потому что Sql Server хранит статистику по вашему индексу и в данном конкретном случае знает, что есть много повторяющихся строк с идентификатор 3835, следовательно, он предполагает, что было бы разумнее просто выполнить полное сканирование индекса (или таблицы). Когда вы проверяете идентификатор, который разрешается только в одну строку, он использует индекс, как ожидалось, т.е. выполняет поиск по индексу (план выполнения должен проверять это предположение).
Возможные решения? Сделайте составной индекс, если вам есть что его составить, то есть, например, составьте его с датой отправки сообщения (если я правильно понимаю ваш случай), а затем выберите первую запись из списка с указанным идентификатором, упорядоченным по дате. Хотя я не уверен, будет ли это лучше (например, составной индекс занимает больше места) - просто предположение.
РЕДАКТИРОВАТЬ: Я только что попробовал сделать индекс составным, добавив столбец даты. Если вы сделаете это и укажете в своем запросе упорядочить по дате , поиск индекса будет выполняться должным образом.
но поскольку я указываю 'top (1)', это означает: дайте мне любую строку. Зачем ему сначала просканировать 700 тыс. Строк только , чтобы вернуть одну? - Reinier 30 минут назад
Извините, пока не могу комментировать, но ответ заключается в том, что SQL-сервер не будет понимать человеческий эквивалент «Принеси мне первый, который найдешь», когда он слышит «Первое место».Вместо ожидаемого «Дайте мне любую строку» SQL Server выбирает первую из всех найденных строк. Единственный раз, когда он знает, это сначала после выборки всех строк, а затем отбрасывания остальных. Очень тщательно, но в вашем случае не очень быстро.
Как уже говорилось, главная проблема - это ваша статистика и избирательность индекса. Если у вас есть другое уникальное поле в вашей таблице (например, столбец идентификаторов), попробуйте сначала объединить индекс для campaignid_int, а затем - на уникальный столбец. Поскольку вы запрашиваете только campaignid_int, это должна быть первая часть ключа. Похоже, стоит попробовать, так как этот индекс должен иметь более высокую избирательность, поэтому оптимизатор может использовать это лучше, чем сканирование индекса.