ВЛИЯНИЕ ИНДЕКСОВ НА ПРОИЗВОДИТЕЛЬНОСТЬ 1С:ПРЕДПРИЯТИЕ 8.1

или

Зачем разработчику 1С «индексировать» измерения регистров и реквизиты ?

- Ну у вас и запросы! - сказала база данных и повисла…

Краткий ответ на  вопрос заголовка заключается в том, что это позволит выполнять запросы быстро и уменьшать негативное влияние блокировок на производительность в многопользовательском режиме.

Что такое индекс?

Подобно содержанию в книге, индекс в базе данных позволяет быстро искать конкретные сведения в таблице.

Сначала поговорим про индексы в MS SQL Server 2005.
Индексы представляют собой структуру, позволяющую выполнять ускоренный доступ к строкам таблицы на основе значений одного или более ее столбцов.
Индекс содержит ключи, построенные из одного или нескольких столбцов таблицы или представления, и указатели, которые сопоставляются с местом хранения заданных данных.
Индексы сокращают объем данных, которые необходимо считать, чтобы возвратить результирующий набор.

Хотя индекс и связан с конкретным столбцом (или столбцами) таблицы, все же он является самостоятельным объектом базы данных.

Просто объекта «Индекс» в платформе 1С:Предприятие 8.1 нет.

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

• Неявным образом индексы создаются с учетом типов полей ключа данных - набора полей, однозначно определяющих данные. Для объектных типов данных (Справочник, Документ, ПланСчетов и др.) - это "Ссылка"; для регистров, подчиненных регистратору (РегистрНакопления, РегистрБухгалтерии, РегистрСведений, подчиненный регистратору и др.) - "Регистратор"; для регистров сведений, неподчиненных регистратору - поля, соответствующие изменениям, входящим в основной отбор регистра; для констант - идентификатор объекта метаданных Константы.
• индексируются данные в «соответствии»

Явным способом включением свойства "Индексировать" реквизитов и измерений с значение «Индексировать» и «Индексировать с доп. Упорядочиванием». Вариант "«Индексировать с доп. Упорядочиванием»" включает обычно включает колонку «код» или «наименование» в индекс.

Еще одним явным способом можно считать добавление объекта метаданных в объект метаданных «критерий отбора».

Можно указать индекс для таблицы значений и в запросах для временных таблиц.

ВЫБРАТЬ
Код,
Наименование
ПОМЕСТИТЬ ВременнаяТаблица
ИЗ Справочник.Номенклатура
ИНДЕКСИРОВАТЬ ПО Код

В любом случаи, надо понимать, что говоря об индексах, мы фактически подразумеваем индексы СУБД, которая используется для 1С:Предприятие. Исключению составляют объекты типа Таблица значений, когда индексы находятся в RAM (оперативной памяти).

Физическая сущность индексов в MS SQL Server 2005.

Физически данные хранятся на 8Кб страницах. Сразу после создания, пока таблица не имеет индексов, таблица выглядит как куча (heap) данных. Записи не имеют определенного порядка хранения.
Когда вы хотите получить доступ к данным, SQL Server будет производить сканирование таблицы (table scan). SQL Server сканирует всю таблицу, что бы найти искомые записи.
Отсюда становятся понятными базовые функции индексов:
- увеличение скорости доступа к данным,
- поддержка уникальности данных.

Несмотря на достоинства, индексы так же имеют и ряд недостатков. Первый из них – индексы занимают дополнительное место на диске и в оперативной памяти. Каждый раз когда вы создаете индекс, вы сохраняете ключи в порядке убывания или возрастания, которые могут иметь многоуровневую структуру. И чем больше/длиннее ключ, тем больше размер индекса. Второй недостаток – замедляются операции вставки, обновления и удаления записей.
В среде MS SQL Server 2005 реализовано несколько типов индексов:

• некластерные индексы;
• кластерные (или кластеризованные) индексы;
• уникальные индексы;
• индексы с включенными столбцами
• индексированные представления
• полнотекстовый
• XML

Некластерный индекс

Некластерные индексы – не перестраивают физическую структуру таблицы, а лишь организуют ссылки на соответствующие строки.
Для идентификации нужной строки в таблице некластерный индекс организует специальные указатели, включающие в себя:

• информацию об идентификационном номере файла, в котором хранится строка;
• идентификационный номер страницы соответствующих данных;
• номер искомой строки на соответствующей странице;
• содержимое столбца.

Некластерных индексов может быть несколько для одной таблицы.

Некластеризованный индекс по таблице, не имеющей кластеризованного индекса

Некластеризованный индекс по таблице, имеющей кластеризованный индекс

Кластерный (кластеризованный) индекс

Принципиальным отличием кластерного индекса от индексов других типов является то, что при его определении в таблице физическое расположение данных перестраивается в соответствии со структурой индекса. Логическая структура таблицы в этом случае представляет собой скорее словарь, чем индекс. Данные в словаре физически упорядочены, например по алфавиту.
Кластерные индексы могут дать существенное увеличение производительности поиска данных даже по сравнению с обычными индексами. Увеличение производительности особенно заметно при работе с последовательными данными. Если в таблице определен некластерный индекс, то сервер должен сначала обратиться к индексу, а затем найти нужную строку в таблице. При использовании кластерных индексов следующая порция данных располагается сразу после найденных ранее данных. Благодаря этому отпадают лишние операции, связанные с обращением к индексу и новым поиском нужной строки в таблице.
Естественно, в таблице может быть определен только один кластерный индекс. Кластерный индекс может включать несколько столбцов.
Необходимо избегать создания кластерного индекса для часто изменяемых столбцов, поскольку сервер должен будет выполнять физическое перемещение всех данных в таблице, чтобы они находились в упорядоченном состоянии, как того требует кластерный индекс. Для интенсивно изменяемых столбцов лучше подходит некластерный индекс.
При создании в таблице первичного ключа (PRIMARY KEY) сервер автоматически создает для него кластерный индекс, если его не существовало ранее или если при определении ключа не был явно указан другой тип индекса.
Когда же в таблице определен еще и некластерный индекс, то его указатель ссылается не на физическое положение строки в базе данных, а на соответствующий элемент кластерного индекса, описывающего эту строку, что позволяет не перестраивать структуру некластерных индексов всякий раз, когда кластерный индекс меняет физический порядок строк в таблице.

Уникальный индекс

Уникальность значений в индексируемом столбце гарантируют уникальные индексы. При их наличии сервер не разрешит вставить новое или изменить существующее значение таким образом, чтобы в результате этой операции в столбце появились два одинаковых значения.
Уникальный индекс является своеобразной надстройкой и может быть реализован как для кластерного, так и для некластерного индекса. В одной таблице может существовать один уникальный кластерный и множество уникальных некластерных индексов.
Уникальные индексы следует определять только тогда, когда это действительно необходимо. Для обеспечения целостности данных в столбце можно определить ограничение целостности UNIQUE или PRIMARY KEY, а не прибегать к уникальным индексам. Их использование только для обеспечения целостности данных является неоправданной тратой пространства в базе данных. Кроме того, на их поддержание тратится и процессорное время.

Если неуникальность заключается в датах с нулевыми значениями, то проблема решается созданием базы с параметром смещения равным 2000.
«Рыба» скрипта для определения неуникальных записей:
SELECT COUNT(*) Counter, <перечисление всех полей соответствующего индекса> from <имя таблицы>
GROUP BY <перечисление всех полей соответствующего индекса>
HAVING Counter > 1

Смотрите подробней описание проблемы в моей статье здесь.

Понятие первичного и внешнего ключа

Первичный ключ (primary key) – это набор столбцов таблицы, значения которых уникально определяют строку.

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

Ограничения индексов

Индекс может быть создан на основании нескольких полей. В этом случае существует ограничение – длина ключа индекса не должна превышать 900 байтов и не более 16 ключевых столбцов. На практике это означает что при создании индекса, включающего более 16 полей, индекс усекается. Это может оказать влияние на производительность при количестве субконто составного типа более 4х.

Такая ситуация для версии 8.0. В версии 8.1 выполнена оптимизация данного случая.

Статистика индексов

Microsoft SQL Server 2005 собирает статистику по индексам и полям данных, хранимых в базе. Эта статистика используется оптимизатором запроса SQL Server при выборе оптимального плана исполнения запросов на выборку или обновление данных.

При создании индекса оптимизатор запросов автоматически сохраняет данные статистики о проиндексированых столбцах.

Просмотр статистики - sp_helpstats.

Фрагментация индексов

Чрезмерная фрагментация создает проблемы для больших операций ввода-вывода. Фрагментация не должна превышать 25%. От снижения фрагментации индексов могут выиграть операции сканирования больших диапазонов данных. Для этого рекомендуется выполнять периодическую дефрагментацию индексов. Обратите внимание, что при дефрагментации индексов (по умолчанию) автоматически обновляется статистика.
Смотреть степень фрагментированности индексов можно штатными средствами СУБД или скриптом:

SELECT     

OBJECT_NAME(f.object_id) AS [таблица индекса],

i.name AS [имя индекса], 

f.avg_fragmentation_in_percent AS [Фраментированность], 

f.alloc_unit_type_desc AS [тип размещения]                                                                                

FROM          

sys.dm_db_index_physical_stats(DB_ID('upp'), NULL, NULL, NULL, 'LIMITED') AS f

LEFT JOIN sys.indexes AS i ON (f.object_id=i.object_id) AND (i.index_id=f.index_id)

ORDER BY [Фраментированность] Desc

, где upp – это имя базы данных  

а  в разрезе объектов метаданных можно например с помощью обработки  http://www.gilev.ru/1c/81/lock/index.htm.

Оптимизация размещения индексов

При объеме таблиц не позволяющем им «разместиться» в оперативной памяти сервера, на первое место выходит скорость дисковой подсистемы (I/O). И здесь можно обратить внимание возможность размещать индексы в отдельных файлах расположенных на разных жестких дисках.

Подробное описание действий http://technet.microsoft.com/ru-ru/library/ms175905.aspx
Использование индекса из другой файловой группы повышает производительность некластерных индексов в связи с параллельностью выполнения процессов ввода/вывода и работы с самим индексом.
Для определения размеров можно использовать выше упомянутую обработку.

Влияние индексов на блокировки

Отсутствие необходимого индекса для запроса означает перебор всех записей таблицы, что в свою очередь приводит к избыточным блокировкам, т.е. блокируются лишние записи. Кроме того, чем дольше выполняется запрос из-за отсутствующих индексов, тем больше время удержания блокировок.
Другая причина блокировок  - малое количество записей в таблицах. В связи с этим SQL Server, при выборе плана выполнения запроса, не использует индексы, а обходит всю таблицу(Table Scan), блокируя целиком. Для того, чтобы избежать подобных блокировок, необходимо увеличить количество записей в таблицах до 1500-2000. В этом случае сканирование таблицы становится долее дорогостоящей операцией и SQL Server начинает использовать индексы. Конечно это можно сделать не всегда, ряд справочников как «Организации», «Склады», «Подразделения» и т.п. обычно имеют мало записей. В этих случаях индексирование не будет улучшать работу.

Эффективность индексов

Мы уже отметили в заголовке статьи, что нас интересуют влияние индексов на быстродействие запросов. Итак, индексы наиболее подходят для задач следующего типа:

• Запросы, которые указывают "узкие" критерии поиска. Такие запросы должны считывать лишь небольшое число строк, отвечающих определенным критериям.
• Запросы, которые указывают диапазон значений. Эти запросы также должны считывать небольшое количество строк.
• Поиск, который используется в операциях связывания. Колонки, которые часто используются как ключи связывания, прекрасно подходят для индексов.
• Поиск, при котором данные считываются в определенном порядке. Если результирующий набор данных должен быть отсортирован в порядке кластеризованного индекса, то сортировка не нужна, поскольку результирующий набор данных уже заранее отсортирован. Например, если кластеризованный индекс создан по колонкам lastname (фамилия), firstname (имя), а для приложения требуется сортировка по фамилии и затем по имени, то здесь нет необходимости добавлять инструкцию ORDER BY.

Правда при всей полезности индексов, есть одно очень важное НО – индекс должен быть «эффективно используемым» и должен позволять  находить данные с использованием меньшего числа операций ввода-вывода и объема системных ресурсов. И наоборот, неиспользуемые (редко используемые) индексы скорее ухудшают скорость записи данных (поскольку каждая операция, изменяющая данные, должна также обновлять страницы индексов) и создают избыточный объем базы.

Покрывающим (для данного запроса), называется индекс в котором есть все необходимые поля для этого запроса. Например, если индекс создан по колонкам a, b и c, а оператор SELECT запрашивает данные только из этих колонок, то требуется доступ только к индексу.

Для того, что бы определить эффективность индекса, мы можем приблизительно оценить с помощью средств 1С:Предприятие 8.1, 
обратив внимание на текст запроса и  используемые таблицы и выделив те поля, которые используются в условиях запроса, сравнив их с индексами, которые автоматически и явно создаются 1С:Предприятием для таблиц соответствующего типа, либо точно с помощью MS SQL Server 2005, обратив внимание на содержимое плана запроса, а точнее виды операций, не использующие индексы.

Литература

Платные услуги, контакты в 1С-Рарус

Офис на улице Бутырский Вал (ст. м. «Савёловская»)

г. Москва, ул. Бутырский Вал, д. 68
Телефоны: (495) 250-6383, 250-6393, 223-0404

Схема проезда к московскому офису на улице Бутырский Вал.

Вопросы администратору этой странички: gilev_slava@mail.ru

Перейти к другим материалам сайта

Вы также можете пообщаться со мной на курсах Администрирование 1С