Несоответствие индексов и условий запроса

Область применения: управляемое приложение, мобильное приложение, обычное приложение.

Методическая рекомендация (полезный совет)

1.1. Необходимо убедиться в том, что для всех условий, использованных в запросе, имеются подходящие индексы.  

Условия используются в следующих секциях запроса:

• ВЫБРАТЬ … ИЗ … ГДЕ <условие>
• СОЕДИНЕНИЕ … ПО <условие>
• ВЫБРАТЬ … ИЗ <ВиртуальнаяТаблица>(, <условие>)
• ИМЕЮЩИЕ <условие>

Для каждого условия должен существовать подходящий индекс. Подходящим является индекс, удовлетворяющий следующим требованиям:

1. Индекс содержит все поля перечисленные в условии;
2. Эти поля находятся в самом начале индекса;
3. Эти поля идут подряд, то есть между ними не «вклиниваются» поля, не участвующие в условии запроса

1.2. Если в структуре базы данных отсутствует индекс, удовлетворяющий всем перечисленным условиям, то для получения результата СУБД будет вынуждена сканировать таблицу или один из ее индексов. Это приведет к увеличению времени выполнения запроса, а также к возможному снижению параллельности системы, поскольку возрастет количество установленных блокировок.  

Требования к индексу связаны с физической структурой индекса в СУБД. Эта структура представляет собой дерево значений проиндексированных полей. На первом уровне дерева находятся значения первого поля индекса, на втором - второго и так далее. Такая структура позволяет достичь высокой эффективности при поиске по индексу. Кроме того, она гарантирует отсутствие деградации производительности индекса с ростом количества данных.  

Однако, индекс такой структуры, очевидно, может быть использован только строго определенным образом. Сначала необходимо провести поиск по значению первого поля индекса, затем - второго и так далее. Если, например, условие по первому полю индекса не указано, то индекс уже не сможет обеспечить быстрый поиск. Если указано условие по нескольким первым полям индекса, а затем одно или несколько полей индекса не задано, то индекс может быть использован только частично.

2. При создании объекта метаданных 1С:Предприятие автоматически создает индексы, которые должны подходить для работы большинства запросов.

Основные индексы, создаваемые 1С:Предприятием:

• индекс по уникальному идентификатору (ссылке) для всех объектных сущностей (справочники, документы и т.д.);
• индекс по регистратору (ссылке на документ) для таблиц движений регистров, подчиненных регистратору;
• индекс по периоду и значениям всех измерений для итоговых таблиц регистров накопления;
• индекс по периоду, счету и значениям всех измерений для итоговых таблиц регистров бухгалтерии.

3. В тех случаях, когда автоматически созданных индексов недостаточно, можно дополнительно проиндексировать реквизиты объекта метаданных.

При этом реквизиты справочников и документов рекомендуется индексировать с дополнительным упорядочиванием. Такой индекс будет учитывать упорядочивание по основному представлению объекта, тем самым он будет эффективно использоваться, например, когда в списке установлен отбор по данному реквизиту, а сам список упорядочен по полям основного представления объекта.

Следует иметь в виду, что создание индекса ускоряет процесс поиска информации, но может несколько замедлить процесс ее изменения (добавления, редактирования и удаления). Поэтому индексы следует создавать осознанно и только в том случае, если точно известен запрос, для которого такой индекс необходим. Не следует создавать индексы "на всякий случай" или заведомо избыточные индексы. В частности:

• не следует дополнительно индексировать первое измерение регистра, поскольку для поиска по значению первого измерения подходит основной индекс таблицы итогов, который автоматически создаст платформа;
• не следует создавать индексы по низкоселективным полям (полям, которые содержат малое количество уникальных значений по сравнению с количеством записей в таблице). Например, индексировать реквизит типа Булево имеет смысл, только если незначительная часть записей всегда будет иметь одно значение, и в запросах всегда выбираются записи по этому значению

Примеры

В конфигурации описан регистр накопления ТоварыНаСкладах: 

Платформа 1С:Предприятие автоматически создаст для таблицы остатков данного регистра индекс по периоду и всем измерениям в том порядке, в котором они перечислены в конфигураторе. 

Рассмотрим несколько примеров запросов и проанализируем, смогут ли они оптимально выполняться при такой структуре данных.

Запрос 1

Запрос.Текст = "ВЫБРАТЬ
| ТоварыНаСкладахОстатки.Склад,
| ТоварыНаСкладахОстатки.Номенклатура,
| ТоварыНаСкладахОстатки.Качество
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки(, Номенклатура = &Номенклатура) КАК ТоварыНаСкладахОстатки"; 

В данном случае нарушено требование 2 раздела 1.1. В условии отсутствует отбор по первому полю индекса (Склад). Такой запрос не сможет выполниться оптимально. Для его выполнения серверу СУБД придется перебирать все записи таблицы. Время выполнения этой операции напрямую зависит от количества записей в таблице остатков регистра и может быть очень большим (и будет увеличиваться с ростом количества данных). 

Варианты оптимизации:

• Проиндексировать измерение Номенклатура
• Поставить измерение Номенклатура первым в списке измерений. Следует осторожно использовать этот метод. В конфигурации могут присутствовать другие запросы, которые могут замедлиться в результате этой перестановки.

Запрос 2

Запрос.Текст = "ВЫБРАТЬ
| ТоварыНаСкладахОстатки.Склад,
| ТоварыНаСкладахОстатки.Номенклатура,
| ТоварыНаСкладахОстатки.ХарактеристикаНоменклатуры,
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки(
| ,
| ХарактеристикаНоменклатуры = &ХарактеристикаНоменклатуры
| Номенклатура = &Номенклатура
| И Склад = &Склад) КАК ТоварыНаСкладахОстатки"; 

В данном случае нарушено требование 3 раздела 1.1. Между измерениями  Номенклатура и ХарактеристикаНоменклатуры в структуре регистра находится измерение Качество, которое не задано в условии запроса. Этот запрос также не сможет выполняться оптимально. При его выполнении СУБД выполнит поиск по первым полям индекса, но затем вынужденно просканирует некоторую его часть. Сканирование может привести к существенному увеличению времени выполнения запроса.

Варианты оптимизации:

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

Запрос 3

Запрос.Текст = "ВЫБРАТЬ
| ТоварыНаСкладахОстатки.Склад,
| ТоварыНаСкладахОстатки.Номенклатура,
| ТоварыНаСкладахОстатки.Качество,
| ТоварыНаСкладахОстатки.КоличествоОстаток
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки(
| ,
| Номенклатура = &Номенклатура
| И Склад = &Склад) КАК ТоварыНаСкладахОстатки";

В этом случае требования соответствия индекса и запроса не нарушены. Данный запрос будет выполнен СУБД оптимальным способом. Следует обратить внимание на то, что порядок следования условий в запросе не обязан совпадать с порядком следования полей в индексе. Это не является проблемой и будет нормально обработано СУБД.

См. также

• Индексы таблиц базы данных
• Эффективные условия запросов