Как устроено индексирование баз данных / Хабр

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

Как базы данных создают индексы

Неиндексированная и индексированная базы данных

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

1. Определение столбца или столбцов в таблице базы данных, которые нужно индексировать. Обычно они определяются по тому, какие столбцы чаще всего используются в запросах или поисках.
2. Выбор алгоритма индексирования, подходящего для типа индексируемых данных. Например, индексы в виде B-деревьев обычно используются для индексирования строковых или числовых данных, а полнотекстовые индексы — для индексирования текстовых данных.
3. Применение алгоритма индексирования к выбранным столбцам, что создаёт структуру данных, сопоставляющую значения в столбцах с местоположениями соответствующих записей таблицы.
4. Сохранение индекса в отдельной структуре данных, обычно в другой части диска или в памяти, чтобы доступ к ней был более эффективным, чем к соответствующим табличным данным.
5. Обновление индекса в случае добавления новых записей, удаления или изменения записей в таблице.

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

Алгоритмы индексирования

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

• B-дерево
• Bitmap-индекс
• Хэш-индекс
• GiST (Generalized Search Tree, обобщённое поисковое дерево)
• Полнотекстовый индекс

Каждый алгоритм индексирования имеет свои сильные и слабые стороны; давайте рассмотрим их по порядку.

B-дерево

▍ Определение

B-дерево — это структура данных самобалансирующегося дерева, которая часто используется в качестве алгоритма индексирования в базах данных. Каждый узел дерева состоит из набора ключей и указателей на дочерние узлы; хранение данных осуществляется в иерархической структуре. Деревья B-узлов упорядочены таким образом, что позволяют быстро выполнять поиск, вставку и удаление данных.

Самое большое преимущество алгоритма B-дерева заключается в минимизации количества дисковых операций ввода-вывода, необходимых для доступа к данным, потому что в B-дереве все узлы-листья находятся на одном уровне, а каждый узел может хранить множество ключей и указателей. Количество ключей и указателей, которое может храниться в узле, определяется параметром, называемым «порядок» дерева.

▍ Как это работает

Алгоритм B-дерева работает следующим образом:

1. Инициализация: при создании B-дерева создаётся пустой корневой узел. Параметр, задающий максимальное количество ключей («порядок»), которые могут храниться в каждом узле, управляет B-порядком дерева.
2. Вставка: при добавлении нового узла в B-дерево алгоритм сначала подыскивает подходящий узел-лист, в который нужно вставить ключ. B-дерево разделяет заполненный узел-лист на два новых узла и перемещает медианный ключ в родительский узел. Пока не достигнут корневой узел, процесс разделения может распространяться по дереву. Благодаря этой процедуре дерево остаётся сбалансированным, а узлы-листья находятся на одинаковой высоте.
3. Удаление: когда ключ удаляется из B-дерева, алгоритм ищет узел, который изначально хранил ключ. Если узел-лист хранил ключ, то ключ извлекается и узел может нуждаться в перебалансировке. Алгоритм удаляет предшествующий или последующий лист после листа-узла, удалив ключ с ним, если ключ обнаружен не в узле-листе.
4. Поиск: в процессе поиска ключа в B-дереве алгоритм начинает с корневого узла и рекурсивно движется к веткам, пока не найдёт нужный узел-лист. Метод поиска сравнивает искомый ключ с ключами, содержащимися в каждом узле, а затем использует соответствующий указатель для перехода к дочернему узлу, в котором может находиться ключ. Этот процесс продолжается, пока не будет найден искомый ключ или пока не будет определено, что он отсутствует в дереве.

Однако B-деревья обладают некоторыми недостатками:

• Излишняя трата ресурсов: B-деревья задействуют большой объём излишнего пространства, поскольку каждый узел в B-дереве содержит указатель на родительский и дочерний узлы.
• Сложность: алгоритмы, используемые для вставки, удаления и поиска данных в B-дереве, сложнее по сравнению с другими структурами данных. Это усложняет реализацию и поддержку B-деревьев.
• Медленные обновления: обновление данных в B-дереве может быть относительно медленным по сравнению с другими структурами данных. Каждая операция обновления требует множества операций доступа к диску, и этот процесс может быть медленным для больших B-деревьев.

▍ Bitmap-индексирование

▍ Определение

Bitmap-индексирование — это методика индексирования данных, использующая битовые карты (bitmap) для обозначения наличия или отсутствия значения в таблице. Это успешная техника индексирования для таблиц с низкой кардинальностью, где количество уникальных значений в столбце довольно мало по сравнению с общим количеством строк.

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

▍ Как это работает

• Для создания bitmap-индекса столбца для каждого уникального значения столбца создаётся отдельный bitmap. Каждый bitmap имеет длину, равную количеству строк в таблице.
• Если значение присутствует в строке, соответствующему биту в bitmap присваивается значение 1, а если оно отсутствует, то присваивается значение 0. (Представьте таблицу, где столбец «Gender» имеет два уникальных значения, например, «Male» и «Female». Если этот столбец имеет bitmap-индекс, можно создать два bitmap, длина каждого из которых равна количеству строк в таблице. Когда в строке встречается «Male» или «Female», соответствующий бит в bitmap «Male» или «Female» получает значение 1, и наоборот. В случае отсутствия значения «Male» или «Female» соответствующему биту присваивается значение 0.)
• Чтобы выполнить запрос при помощи bitmap-индекса, соответствующие в запросе значения bitmap комбинируются при помощи побитовых операторов AND, OR и NOT. (например, если мы хотим найти все строки, где «Gender» равно «Male» И «Age» больше 30, нам сначала нужно получить bitmap «Male» и bitmap «Age > 30» из соответствующих индексов. Затем мы комбинируем эти два bitmap при помощи побитового оператора AND и получаем окончательный bitmap только с единицами в тех позициях, где оба условия истинны. Затем окончательный bitmap используется для получения из таблицы строк, удовлетворяющих запросу.)

Bitmap-индексы имеют множество недостатков, и в том числе:

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

Хэш-индекс

▍ Определение

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

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

▍ Как это работает

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

1. Мы создадим хэш-функцию, получающую на входе пользовательский ID и генерирующую на выходе уникальный хэш-код. Хэш-функция должна быть спроектирована таким образом, чтобы генерировать равномерно распределённое множество хэш-кодов для равномерного распределения записей по корзинам в файле индекса. На практике хэш-функция может использовать для генерации хэш-кода различные методики, например, модульную арифметику или побитовые операции.
2. Мы создаём файл хэш-индекса, содержащий набор корзин (bucket), каждая из которых соответствует уникальному хэш-коду сгенерированному хэш-функцией. Каждая корзина содержит указатель на файл базы данных, содержащий записи для этого хэш-кода.
3. При выполнении запроса к значению запроса применяется хэш-функция для генерации хэш-кода. Затем хэш-код используется для нахождения соответствующей корзины в файле хэш-индекса. Записи с одинаковым хэш-кодом хранятся в одной корзине, поэтому мы можем просто просканировать записи в этой корзине и найти совпадающую запись/записи. Если присутствуют коллизии (то есть несколько записей с одинаковым хэш-кодом), то для их разрешения можно использовать техники наподобие создания цепочек или открытой адресации.
4. Чтобы вставить новую запись в хэш-индекс, мы применяем к значению ключа записи хэш-функцию, чтобы сгенерировать его хэш-код, а затем вставляем запись в соответствующую корзину в файле хэш-индекса. Если коллизии отсутствуют, вставку можно выполнить за постоянное время O(1), так как нам нужно всего лишь вычислить хэш-код и вставить запись в корзину. Если коллизии есть, нам может потребоваться проделать дополнительные операции, например, вставку записи в связанный список в корзине или проверку других корзин, пока не будет найден свободный слот.

Хэш-индексы также имеют множество недостатков, в том числе:

• Ограниченные возможности поиска: хэш-индексы предназначены для обработки только поисков равенства (например, «найти все записи, где столбец A равен значению»). Они плохо подходят для запросов диапазонов или сортировки.
• Коллизии: хэш-индексы могут иметь коллизии, при которых несколько ключей соответствуют одному хэш-значению. Это может привести к снижению производительности, поскольку базе данных нужно будет выполнять дополнительные операции для разрешения коллизий.
• Непредсказуемые требования к размеру хранилища: размер хэш-индекса невозможно предугадать, так как он зависит от количества уникальных значений в индексируемом столбце. Это усложняет планирование требований к размеру хранилища.

▍ GiST

▍ Определение

GiST (Generalized Search Tree, обобщённое поисковое дерево) — это техника индексирования баз данных, которая может использоваться для индексирования сложных типов данных, например, геометрических объектов, текста или массивов. Это сбалансированная древовидная структура, состоящая из узлов с множественными дочерними узлами. Каждый узел описывает диапазон или множество значений и связан с предикативной функцией, проверяющей, принадлежит ли значение диапазону или множеству. Предикативная функция зависит от типа индексируемых данных и может быть подстроена под разные типы данных.

▍ Как это работает

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

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

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

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

3. При выполнении запроса значение в запросе преобразуется при помощи функции преобразования в набор ключей.

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

   Поиск продолжается вниз по дереву и выбирает дочерний узел, содержащий значение из запроса.

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

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

   Затем ключи вставляются в соответствующие узлы индекса, начиная с корневого узла.

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

GiST имеет несколько недостатков, которые нужно учитывать:

1. Сниженная скорость вставок и обновлений: структуры индексирования GiST могут быть сложнее, чем традиционные структуры индексирования, что может привести к снижению скорости операций вставки и обновления.
2. Больше дискового пространства: структуры индексирования GiST могут требовать больше дискового пространства, чем другие методики индексирования, поскольку хранят дополнительную информацию для поддержки различных типов поиска.
3. Подходит не для всех типов данных: GiST оптимизирован под индексирование сложных типов данных, например, пространственных данных, однако может быть не лучшим выбором для индексирования более простых типов данных, например, целочисленных значений или строк.
4. Повышенные затраты на поддержку: из-за сложности реализации индексы GiST требуют больше обслуживания по сравнению с традиционными индексами.

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

▍ Определение

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

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

▍ Как это работает

Процесс полнотекстового индексирования состоит из нескольких этапов:

1. Токенизация: текстовое содержимое индексируемого столбца разбивается на отдельные слова или токены, которые затем сохраняются в индекс. При создании полнотекстового индекса система базы данных сначала анализирует текстовое содержимое индексируемых столбцов, а затем разбивает его на отдельные слова или токены. Этот процесс называется токенизацией, он может включать в себя фильтрацию игнорируемых слов (например, «the», «and», «or») и выделение корней (редуцирование слов до их базовой формы).
2. Индексирование: затем токены индексируются при помощи специальной структуры данных, например, B-дерева или инвертированного индекса. Структура индекса обеспечивает возможность эффективного поиска и извлечения строк, содержащих указанные токены.
3. Построение и выполнение запросов: система базы данных использует полнотекстовый индекс для поиска строк, содержащих релевантные токены. В процессе поиска токены запроса сопоставляются с индексированными токенами и извлекаются строки, соответствующие запросу. Результаты поиска можно ранжировать на основании их релевантности запросу, который вычисляется при помощи алгоритмов наподобие TF-IDF (term frequency-inverse document frequency).

Полнотекстовое индексирование имеет некоторые недостатки:

1. Сниженная скорость индексирования и поиска: полнотекстовое индексирование может быть более сложным, чем другие техники индексирования, что может приводить к снижению скорости индексирования и поиска, особенно в больших базах данных со множеством текстовых полей.
2. Подходит не для всех типов данных: полнотекстовое индексирование лучше всего подходит для баз данных, содержащих большие объёмы текстовых данных. Оно может и не быть наиболее эффективной техникой для баз данных, по большей мере, для содержащих числовую или другую нетекстовую информацию.
3. Зависимость от языка: полнотекстовое индексирование может быть не очень эффективно для многоязычных баз данных, поскольку требует отдельных индексов для каждого языка и может оказаться неспособным справиться с нюансами различных языков и систем письменности.

Заключение

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

Подробнее об индексировании баз данных можно узнать из следующих ресурсов:

1. “Use The Index, Luke!”, Markus Winand — это подробное руководство по индексированию баз данных SQL, в котором освещаются как основы, так и расширенные возможности.
2. “Database Indexing Explained”, DigitalOcean — в этом туториале представлено понятное для новичков введение в концепции и методики индексирования с примерами на PostgreSQL.
3. “Indexing Strategies for MySQL and MariaDB”, Severalnines — в этом посте представлены практические советы по проектированию и оптимизации индексов в MySQL и MariaDB.

Telegram-канал с розыгрышами призов, новостями IT и постами о ретроиграх 🕹️

15) Индексирование в базах данных

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

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) разреженный индекс.
• В плотном индексе запись создается для каждого поискового ключа, оцененного в базе данных.
• Метод разреженной индексации помогает решить проблемы плотной индексации.
• Вторичный индекс – это метод индексации, ключ поиска которого определяет порядок, отличный от последовательного порядка файла.
• Индекс кластеризации определяется как файл данных заказа.
• Многоуровневое индексирование создается, когда первичный индекс не помещается в памяти.
• Самым большим преимуществом индексирования является то, что оно помогает вам сократить общее количество операций ввода-вывода, необходимых для извлечения этих данных.
• Самый большой недостаток для выполнения системы управления базами данных индексации, вам нужен первичный ключ в таблице с уникальным значением.

Что такое индекс базы данных? — Глоссарий ИТ

• Определение индекса базы данных

  Индекс базы данных Определение

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

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

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

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

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

  Поскольку индексирование оптимизирует производительность запросов, большинство систем управления базами данных поддерживают встроенные и определяемые пользователем индексы. Столбцы таблицы, помеченные ограничениями уникальности или первичного ключа, могут иметь неявные индексы в большинстве баз данных. Напротив, определяемые пользователем индексы полезны, когда большинство запросов к базе данных содержат столбцы, не являющиеся первичными ключами.

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

• Архитектура индекса и методы индексирования

  Архитектура индекса и методы индексирования

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

  Ниже приведены некоторые из ключевых аспектов индексов баз данных:

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

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

  Селективность: мощность индекса, деленная на общее количество кортежей в индексе, представляет селективность. Например, представьте, что таблица сотрудников имеет 100 строк, а один из ее индексированных столбцов имеет 50 уникальных значений, что также является кардинальностью столбца. Тогда селективность индексированного столбца будет 50/100 = 0,5. Селективность «1» считается лучшей, поскольку она указывает, что все значения в индексе уникальны. Избирательность столбца с ограничением первичного ключа всегда высока, поскольку он не содержит похожих значений.

  Напротив, столбец, имеющий несколько неуникальных значений, имеет низкую селективность. Например, столбец «пол» в таблице сотрудников с 10 000 записей будет иметь низкую избирательность, поскольку он содержит повторяющиеся значения, такие как «мужской» и «женский». Селективность столбца пола будет 2/10000 = 0,0002.

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

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

  Например, предположим таблицу сотрудников с тремя столбцами: идентификатор сотрудника, отдел и имя. Многие сотрудники могут принадлежать к одному и тому же отделу в этой таблице, но каждый сотрудник может иметь только один идентификатор. Следовательно, индекс столбца идентификатора сотрудника будет более «избирательным», чем столбец отдела, поскольку он имеет низкую плотность или нулевые повторяющиеся значения по сравнению со столбцом отдела. Оптимизаторы запросов в базах данных, таких как SQL Server, используют данные плотности для определения ожидаемого количества записей, которые столбец может вернуть при запросе.

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

Как работает индексация | Учебное пособие от Chartio

Что делает индексация?

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

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

Например, в таблице ниже представлена ​​таблица в вымышленном источнике данных, которая полностью неупорядочена.

компания_id	шт.	unit_cost
10	12	1,15
12	12	1,05
14	18	1,31
18	18	1,34
11	24	1,15
16	12	1,31
10	12	1,15
12	24	1,3
18	6	1,34
18	12	1,35
14	12	1,95
21	18	1,36
12	12	1,05
20	6	1,31
18	18	1,34
11	24	1,15
14	24	1,05

Если бы мы выполнили следующий запрос:

 SELECT
Идентификатор компании,
единицы измерения,
себестоимость единицы продукции
ОТ
index_test
ГДЕ
идентификатор_компании = 18
 

База данных должна будет выполнить поиск по всем 17 строкам в порядке их появления в таблице, сверху вниз, по одной за раз. Таким образом, для поиска всех потенциальных экземпляров company_id число 18, база данных должна просмотреть всю таблицу на наличие всех вхождений 18 в столбце company_id .

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

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

Запрос к неиндексированной таблице, если он представлен визуально, будет выглядеть так:

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

С индексом в столбце company_id таблица, по сути, будет выглядеть так:

company_id	шт.	unit_cost
10	12	1,15
10	12	1,15
11	24	1,15
11	24	1,15
12	12	1,05
12	24	1,3
12	12	1,05
14	18	1,31
14	12	1,95
14	24	1,05
16	12	1,31
18	18	1,34
18	6	1,34
18	12	1,35
18	18	1,34
20	6	1,31
21	18	1,36

Теперь база данных может искать company_id номер 18 и возвращать все запрошенные столбцы для этой строки, а затем переходить к следующей строке. Если в следующей строке comapny_id номер также равен 18, тогда он вернет все столбцы, запрошенные в запросе. Если в следующей строке company_id равен 20, запрос прекращает поиск и завершается.

Как работает индексация?

На самом деле таблица базы данных не переупорядочивается каждый раз при изменении условий запроса для оптимизации производительности запроса: это было бы нереалистично. На самом деле происходит то, что индекс заставляет базу данных создавать структуру данных. Тип структуры данных, скорее всего, B-Tree. Несмотря на множество преимуществ B-дерева, основное преимущество для наших целей заключается в том, что его можно сортировать. Когда структура данных отсортирована по порядку, это делает наш поиск более эффективным по очевидным причинам, которые мы указали выше.

Когда индекс создает структуру данных для определенного столбца, важно отметить, что никакой другой столбец не сохраняется в структуре данных. Наша структура данных для приведенной выше таблицы будет содержать только номера company_id . Units и unit_cost не будут храниться в структуре данных.

Откуда база данных узнает, какие еще поля в таблице нужно вернуть?

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

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

This entry was posted in Популярное