#sql #sql-server #indexing
Вопрос:
У меня есть таблица, в которой есть автоматическое увеличение числа PK, a InsertDate и LastUpdated столбец, например:
| ПерсонИд | … … … | Вставить дату | В прошлом году |
|---|---|---|---|
| 123 | 2020-12-15 | 2021-10-29 | |
| 124 | 2021-10-29 | нулевой |
Существует индекс для этих 2 «отфильтрованных» столбцов и других включенных столбцов.
Команда DW каждые 5 минут выполняет следующий запрос, извлекая пользователей, созданных/обновленных в этом окне:
SELECT {included columns} FROM Purchases WHERE InsertDate gt; {five minutes ago} OR LastUpdated gt; {five minutes ago} Этот запрос обычно возвращает менее 100 элементов, а длина таблицы превышает 150 миллионов записей.
Поскольку запрашиваемые элементы являются «очень свежими», я боюсь, что они присутствуют в фрагментированной части индекса. Сам индекс очень велик с фрагментацией менее 0,05%.
У меня есть 2 вопроса:
- Влияет ли на запрос «фрагментация» индекса?
- Влияет ли порядок индекса (ASC против DESC) на производительность?
PS: из-за чувствительности данных я не могу воспроизвести это в среде, не связанной с продуктами, и выполнить тесты
Комментарии:
1. Скорее всего, вам повредит тот факт, что вы используете один индекс для выполнения двух разных заданий. Фраза
WHERE InsertDate gt; {five minutes ago} OR LastUpdated gt; {five minutes ago}действительно нуждается в двух разных индексах.2. итак, я должен создать 2 индекса, по одному для каждого отфильтрованного столбца, с включенными столбцами в каждом?
3. ДА. Вопрос о том, следует ли использовать столбцы включения, сложнее. Для запросов к очень большим таблицам, которые возвращают очень мало строк, возможно, лучше не использовать функцию ВКЛЮЧЕНИЯ в индексах.
4. Альтернатива — вы можете установить lastUpdated на то же значение, что и insertDate, а затем просто упростить логику, чтобы использовать только lastUpdated.
5. Я не могу повторить , у вас должен быть способ сделать это, потому что вы НИКОГДА не должны экспериментировать с производственной базой данных.
Ответ №1:
Для эффективной работы этого запроса вам понадобятся два индекса, и вы надеетесь, что компилятор выберет объединение индексов.
Purchases (InsertDate) INCLUDE (OtherColumns) Purchases (LastUpdated) INCLUDE (OtherColumns) Обычно в идеале вы должны DESC это сделать , потому что, хотя SQL Server может выполнять обратное сканирование, он не может распараллелить его. Но в данном случае я не уверен, что это хорошая идея: вам нужно всего несколько строк, поэтому не нужно распараллеливать, и это может привести к сильной фрагментации на первой странице индекса. Возможно, просто сделайте это дальше InsertDate DESC .
В SQL Server 2019 было бы неплохо иметь опцию OPTIMIZE_FOR_SEQUENTIAL_KEY = ON для этих индексов.
Даже с обоими индексами вы все равно можете получить сканирование индекса по всей таблице.
Возможно, вам потребуется переписать, чтобы принудительно объединить индексы
SELECT PersonId, {included columns} FROM Purchases WHERE InsertDate gt; {five minutes ago} UNION SELECT PersonId, {included columns} FROM Purchases WHERE LastUpdated gt; {five minutes ago}; Убедитесь, что вы включили первичный ключ в список столбцов при объединении
Комментарии:
1. изменится ли ваш ответ кардинально, если я скажу, что есть еще один фильтр типа «ИЛИ ProductID в (ВЫБЕРИТЕ ProductID ИЗ dbo. Продукты, ГДЕ ProductType = @тип)» ???
2. Делает более вероятным, что вам нужно принудительно объединить индексы, как показано на рисунке. Вам понадобится третий индекс
ProductIdс включениями. В какой-то момент всеINCLUDEстолбцы слишком сильно замедляют вставку и обновление, это решение, которое вам нужно принять