Индекс в СУБД

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

    • чтению большего количества строк данных
    • увеличению времени выполнения запроса
    • появлению избыточных блокировок
    • и другим негативным последствиям

В данной статье мы рассмотрим как можно найти недостающие индексы с помощью динамических административных функций MS SQL Server.

Информация о динамических функциях

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

Функция Описание
dm_db_missing_index_group_stats Возвращает сведения о группах отсутствующих индексов. Содержит информацию о количествах операций поиска, которые могли бы быть выполнены по отсутствующему индексу, среднем проценте выигрыша и средней стоимости запросов, которая могла бы быть уменьшена при использовании индекса
dm_db_missing_index_groups Возвращает сведения об отсутствующих индексах, содержащихся в конкретной группе отсутствующих индексов. Далее используется для связи отсутствующих индексов и групп индексов
dm_db_missing_index_details Возвращает подробные сведения об отсутствующих индексах. Содержит информацию о столбцах соответствующих предикатам равенства и неравенства, а так же о столбцах необходимых для запроса

Запрос поиска недостающих индексов и анализ его результата

Следующий запрос выведет всю необходимую в дальнейшем информацию:

Выражение avg_total_user_cost * avg_user_impact * (user_seeks + user_scans), использованное в запросе, соответствует оценке выигрыша при добавлении отсутствующего индекса. Файл запроса можно скачать во вложениях к статье.

Выполним запрос и проанализируем его результаты на простом примере.

Отсутствующие индексы в базе данных

Как видно, отсутствует индекс в таблице AccumRg23573, при этом запрос, для которого необходим индекс, выполняется с предикатом равенства по полю Fld23580RRef. В поле user_seeks указано что этот индекс мог бы быть использован 750 раз в целях поиска по индексу, avg_user_impact говорит о том что средний процент выигрыша равен 99,99%. Так же имеет смысл обратить внимание на поле last_user_seek, оно указывает на дату и время последнего пользовательского запроса, который мог бы использовать отсутствующий индекс для поиска. Если последний раз подходящий запрос был давно, возможно, индекс будет использовать редко и необходимости в нем нет — необходимо оценить перед добавлением индекса. Теперь воспользуемся обработкой выводящей структуру хранения базы данных в терминах 1С:Предприятия (из статьи «Получение информации о структуре хранения базы данных в терминах 1С:Предприятие и СУБД»).

Структура хранения базы данных с отсутствующим индексом

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

Добавим отсутствующий индекс

Статистика использования индекса

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

Статистика использования добавленного отсутствующего индекса

Оценив значения в колонке user_seeks можно сделать вывод что в данной системе этот индекс используется достаточно часто, а эффективность от его использования высока (была оценена первым запросом).

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

INDEXING — это метод структуры данных, который позволяет вам быстро извлекать записи из файла базы данных. Индекс — это небольшая таблица, имеющая всего два столбца. Первый столбец содержит копию первичного или потенциального ключа таблицы. Его второй столбец содержит набор указателей для хранения адреса дискового блока, где хранится это конкретное значение ключа.

Индекс —

  • Принимает ключ поиска в качестве ввода
  • Эффективно возвращает коллекцию совпадающих записей.

Из этого руководства по индексированию СУБД вы узнаете:

  • Типы индексации
  • Первичная индексация
  • Вторичный индекс
  • Индекс кластеризации
  • Что такое многоуровневый индекс?
  • B-Tree Index
  • Преимущества индексации
  • Недостатки индексации

Типы индексации

Тип индексов

Индексация базы данных определяется на основе ее атрибутов индексации. Два основных типа методов индексации:

  • Первичная индексация
  • Вторичная индексация

Первичная индексация

Первичный индекс — это упорядоченный файл с фиксированной длиной и двумя полями. Первое поле — это тот же первичный ключ, а второе поле указывает на этот конкретный блок данных. В первичном индексе всегда существует отношение один к одному между записями в таблице индекса.

Первичная индексация также делится на два типа.

  • Плотный индекс
  • Разреженный индекс

Плотный индекс

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

Разреженный индекс

Это индексная запись, которая отображается только для некоторых значений в файле. Разреженный индекс поможет вам решить проблемы плотного индексирования. В этом методе методики индексирования диапазон столбцов индекса хранит один и тот же адрес блока данных, и когда данные должны быть извлечены, адрес блока будет выбран.

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

Пример разреженного индекса

Вторичный индекс

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

Этот двухуровневый метод индексации базы данных используется для уменьшения размера отображения первого уровня. Для первого уровня из-за этого выбирается большой диапазон чисел; размер отображения всегда остается небольшим.

Пример вторичной индексации

В базе данных банковского счета данные хранятся последовательно с помощью acc_no; Вы можете найти все счета в конкретном отделении банка ABC.

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

Индекс кластеризации

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

Пример:

Давайте предположим, что компания набрала много сотрудников в различных отделах. В этом случае кластерная индексация должна быть создана для всех сотрудников, принадлежащих к одному отделу.

Он рассматривается в одном кластере, а индексные точки указывают на кластер в целом. Здесь Department _no — неуникальный ключ.

Что такое многоуровневый индекс?

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

B-Tree Index

Индекс B-дерева — это широко используемые структуры данных для индексации. Это метод многоуровневого индексного формата, который сбалансирован бинарными деревьями поиска. Все конечные узлы дерева B обозначают фактические указатели данных.

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

  • Ведущие узлы должны иметь от 2 до 4 значений.
  • Каждый путь от корня до листа в основном одинаковой длины.
  • Нелистовые узлы, кроме корневого, имеют от 3 до 5 дочерних узлов.
  • Каждый узел, который не является корнем или листом, имеет от n / 2] до n дочерних узлов.

Преимущества индексации

Важные плюсы / преимущества индексирования:

  • Это помогает сократить общее количество операций ввода-вывода, необходимых для извлечения этих данных, поэтому вам не нужно обращаться к строке в базе данных из структуры индекса.
  • Предлагает более быстрый поиск и поиск данных для пользователей.
  • Индексирование также помогает сократить табличное пространство, поскольку вам не нужно ссылаться на строку в таблице, поскольку нет необходимости хранить ROWID в индексе. Таким образом вы сможете уменьшить табличное пространство.
  • Вы не можете сортировать данные в ведущих узлах, так как значение первичного ключа классифицирует их.

Недостатки индексации

Важными недостатками / минусами индексации являются:

  • Для выполнения системы управления базами данных индексирования вам необходим первичный ключ в таблице с уникальным значением.
  • Вы не можете выполнять какие-либо другие индексы для проиндексированных данных.
  • Вам не разрешено разбивать организованную по индексу таблицу.
  • Индексирование SQL Снижение производительности в запросах INSERT, DELETE и UPDATE.

Резюме:

  • Индексирование — это небольшая таблица, состоящая из двух столбцов.
  • Два основных типа методов индексации: 1) первичная индексация 2) вторичная индексация.
  • Первичный индекс — это упорядоченный файл с фиксированной длиной и двумя полями.
  • Первичная индексация также делится на два типа: 1) плотный индекс 2) разреженный индекс.
  • В плотном индексе запись создается для каждого поискового ключа, оцененного в базе данных.
  • Метод разреженной индексации помогает решить проблемы плотной индексации.
  • Вторичный индекс — это метод индексации, ключ поиска которого определяет порядок, отличный от последовательного порядка файла.
  • Индекс кластеризации определяется как файл данных заказа.
  • Многоуровневое индексирование создается, когда первичный индекс не помещается в памяти.
  • Самым большим преимуществом индексирования является то, что оно помогает вам сократить общее количество операций ввода-вывода, необходимых для извлечения этих данных.
  • Самый большой недостаток для выполнения системы управления базами данных индексации, вам нужен первичный ключ в таблице с уникальным значением.

Получить учебные материалы по этому курсу

Индексы в базах данных: назначение, влияние на производительность, принципы создания индексов

6.1 Для чего нужны индексы

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

Обычно чем больше индексов, тем больше производительность запросов к базе данных. Однако при излишнем увеличении количества индексов падает производительность операций изменения данных (вставка/изменение/удаление), увеличивается размер БД, поэтому к добавлению индексов следует относиться осторожно.

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

· индексы необходимо создавать для столбцов, которые используются в джойнах, по которым часто производится поиск и операции сортировки. При этом необходимо учесть, что индексы всегда автоматически создаются для столбцов, на которые накладывается ограничение primary key. Чаще всего они создаются и для столбцов с foreign key (в Access — автоматически);

· индекс обязательно в автоматическом режиме создается для столбцов, на которые наложено ограничение уникальности;

· лучше всего индексы создавать для тех полей, в которых — минимальное число повторяющихся значений и данные распределены равномерно. В Oracle есть специальные битовые индексы для столбцов с большим количеством повторяющихся значений, в SQL Server и Access такой разновидности индексов не предусмотрено;

· если поиск постоянно производится по определенному набору столбцов (одновременно), то в этом случае, возможно, есть смысл создать композитный индекс (только в SQL Server) — один индекс для группы столбцов;

· при внесении изменений в таблицы автоматически изменяются и индексы, наложенные на эту таблицу. В результате индекс может быть сильно фрагментирован, что сказывается на производительности. Периодически следует проверять степень фрагментации индексов и дефрагментировать их. При загрузке большого количества данных иногда есть смысл вначале удалить все индексы, а после завершения операции создать их заново;

· индексы можно создавать не только для таблиц, но и для представлений (только в SQL Server). Преимущества — возможность вычислять поля не в момент запроса, а в момент появления новых значений в таблицах.

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

Индекс — это копия выбранных столбцов данных из таблицы, называемая ключом базы данных или просто ключом , в которой можно очень эффективно искать, которая также включает адрес низкоуровневого блока данных или прямую ссылку на всю строку данных, которые были скопированы. из. Некоторые базы данных расширяют возможности индексирования, позволяя разработчикам создавать индексы для функций или выражений . Например, можно создать индекс upper(last_name), который будет хранить только версии last_nameполя в верхнем регистре в индексе. Другой вариант, который иногда поддерживается, — это использование частичных индексов , когда элементы индекса создаются только для тех записей, которые удовлетворяют некоторому условному выражению. Еще одним аспектом гибкости является разрешение индексирования определяемых пользователем функций , а также выражений, сформированных из набора встроенных функций.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *