Индексы в базах данных

Индексы позволяют повысить производительность базы данных. 

Индекс — специальная структура данных, которая связана с таблицей и создается на основе данных, содержащихся в ней. Основная цель создания индексов — повышение производительности функционирования базы данных.

 

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

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

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

Как индекс ускоряет поиск в БД ?

При каждом выполнении SELECT с условием WHERE, происходит проверка, имеется ли для этой таблицы индекс, для столбцов, которые могут быть применены для поиска нужных строк.

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

Т.е. если есть индекс на нужное поле, то всместо построчного перебора строк в таблице —

  1. Будет произведерн поиск по индексу и получены указатели на нужные строки в целевой таблице
  2. Выборка строка из целевой таблицы, имея прямые ссылки на эти строки, полученные из индекса.

 

Что такое селективность значения столбца.

Вообще говоря, селективность (избирательность) индекса — это оценочное количество строк, которые могут быть выбраны при поиске по каждому значению индекса. Уникальный индекс имеет максимально возможную селективность, потому что он не может выбрать более одной строки для каждого значения, в то время как индекс для столбца BOOLEAN имеет практически самую низкую селективность.

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

Селективность уникального индекса равна 1, например ID первичный ключ. Все неуникальные индексы имеют значение меньше 1. Селективность (s) вычисляется как

s = n / количество строк в таблице

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

Составной индекс на несколько столбцов.

Индекс может быть создан на основе нескольких столбцов.

Нужно оценить какие поля участвуют в операции SELECT после ключевых слов ORDR BY и WHERE и может быть разумным создать составной индекс.

При этом самое первое поле идущее в составном индексе должно иметь наибольшую селективность (наибольшую уникальность).

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

  • Накладные расходы на поддержание актуальности индекса. Теперь каждая операция Insert в таблицу с индексом — также имеет дополнительную операцию — по вставке и перестройке индекса на эту таблицу. Таким образом наличие индексов на таблицу — всегда увеличивает время операций на Insert и Update

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

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