| tags |
|---|
SQL |
Индекс (index) — объект базы данных, создаваемый с целью повышения производительности выборки данных.
Наборы данных могут иметь большое количество записей, которые хранятся в произвольном порядке, и их поиск по заданному критерию путем последовательного просмотра набора данных запись за записью может занимать много времени. Индекс формируется из значений одного или нескольких полей и указателей на соответствующие записи набора данных, таким образом, достигается значительный прирост скорости выборки из этих данных.
Преимущества - ускорение поиска и сортировки по определенному полю или набору полей. - обеспечение уникальности данных.
Недостатки - требование дополнительного места на диске и в оперативной памяти. Чем больше/длиннее ключ, тем больше размер индекса. - замедление операций вставки, обновления и удаления записей, поскольку при этом приходится обновлять сами индексы.
Индексы предпочтительней для полей: - Поля-счетчика, чтобы в том числе избежать и повторения значений в этом поле;
- по которому проводится сортировка данных; - по которым часто проводится соединение наборов данных. Данные располагаются в порядке возрастания индекса и соединение происходит значительно быстрее; - которое объявлено первичным ключом (primary key); - Поля, в котором данные выбираются из некоторого диапазона. Как только будет найдена первая запись с нужным значением, все последующие значения будут расположены рядом.
Использование индексов нецелесообразно для полей которые: - редко используются в запросах; - содержат всего два или три значения, например: мужской, женский пол или значения «да», «нет».
- упорядоченные / неупорядоченные — индексы, в которых элементы упорядочены; - возрастающие / убывающие;
- индексы по представлению (view);
- индексы по выражениям.
Некластеризованный индекс содержит указатели на строки данных, которые могут быть расположены в любом порядке1. Как кластеризованные, так и некластеризованные индексы могут быть уникальными
Выбор типа индекса зависит от целей и характеристик данных. В общем случае, кластеризованные индексы рекомендуются для часто используемых столбцов с низкой кардинальностью (например, первичные ключи), а некластеризованные индексы — для столбцов с высокой кардинальностью (например, даты или цены) Пример создания кластеризованного индекса:
CREATE CLUSTERED INDEX IX_Products_Name ON Products (Name);Пример создания некластеризованного индекса:
CREATE NONCLUSTERED INDEX IX_Orders_Date ON Orders (OrderDate);- B*-деревья; - B+-деревья; - B-деревья; - Хэши.
- простой индекс (индекс с одним ключом) — строится по одному полю; - составной (многоключевой, композитный) индекс — строится по нескольким полям при этом важен порядок их следования; - индекс с включенными столбцами — некластеризованный индекс, дополнительно содержащий кроме ключевых столбцов еще и неключевые; - главный индекс (индекс по первичному ключу) — это тот индексный ключ, под управлением которого в данный момент находится набор данных. Набор данных не может быть отсортирован по нескольким индексным ключам одновременно. Хотя, если один и тот же набор данных открыт одновременно в нескольких рабочих областях, то у каждой копии набора данных может быть назначен свой главный индекс.
- уникальный индекс состоит из множества уникальных значений поля; - плотный индекс (NoSQL) — индекс, при котором, каждом документе в индексируемой коллекции соответствует запись в индексе, даже если в документе нет индексируемого поля.
- разреженный индекс (NoSQL) — тот, в котором представлены только те документы, для которых индексируемый ключ имеет какое-то определённое значение (существует). - пространственный индекс — оптимизирован для описания географического местоположения. Представляет из себя многоключевой индекс состоящий из широты и долготы. - составной пространственный индекс — индекс, включающий в себя кроме широты и долготы ещё какие-либо мета-данные (например теги). Но географические координаты должны стоять на первом месте. - полнотекстовый (инвертированный) индекс — словарь, в котором перечислены все слова и указано, в каких местах они встречаются. При наличии такого индекса достаточно осуществить поиск нужных слов в нём и тогда сразу же будет получен список документов, в которых они встречаются. - хэш-индекс предполагает хранение не самих значений, а их хэшей, благодаря чему уменьшается размер (а, соответственно, и увеличивается скорость их обработки) индексов из больших полей. Таким образом, при запросах с использованием хэш-индексов, сравниваться будут не искомое со значения поля, а хэш от искомого значения с хэшами полей. Из-за нелинейнойсти хэш-функций данный индекс нельзя сортировать по значению, что приводит к невозможности использования в сравнениях больше/меньше и «is null». Кроме того, так как хэши не уникальны, то для совпадающих хэшей применяются методы разрешения коллизий. - битовый индекс (bitmap index) — метод битовых индексов заключается в создании отдельных битовых карт (последовательностей 0 и 1) для каждого возможного значения столбца, где каждому биту соответствует запись с индексируемым значением, а его значение равное 1 означает, что запись, соответствующая позиции бита содержит индексируемое значение для данного столбца или свойства.
- обратный индекс (reverse index) — B-tree индекс, но с реверсированным ключом, используемый в основном для монотонно возрастающих значений (например, автоинкрементный идентификатор) в OLTP системах с целью снятия конкуренции за последний листовой блок индекса, т.к. благодаря переворачиванию значения две соседние записи индекса попадают в разные блоки индекса. Он не может использоваться для диапазонного поиска. - функциональный индекс, индекс по вычисляемому полю (function-based index) — индекс, ключи которого хранят результат пользовательских функций. Функциональные индексы часто строятся для полей, значения которых проходят предварительную обработку перед сравнением в команде SQL. Например, при сравнении строковых данных без учета регистра символов часто используется функция UPPER. Кроме того, функциональный индекс может помочь реализовать любой другой отсутствующий тип индексов данной СУБД. - первичный индекс — уникальный индекс по полю первичного ключа. - вторичный индекс — индекс по другим полям (кроме поля первичного ключа). - XML-индекс — вырезанное материализованное представление больших двоичных XML-объектов (BLOB) в столбце с типом данных xml.
- полностью перестраиваемый — при добавлении элемента заново перестраивается весь индекс.
- пополняемый (балансируемый) — при добавлении элементов индекс перестраивается частично (например одна из ветви) и периодически балансируется.
По покрытию индексируемого содержимого - полностью покрывающий (полный) индекс — покрывает всё содержимое индексируемого объекта. - частичный индекс (partial index) — это индекс, построенный на части набора данных, удовлетворяющей определенному условию самого индекса. Данный индекс создан для уменьшения размера индекса. - инкрементный (delta) индекс — индексируется малая часть данных(дельта), как правило, по истечении определенного времени. Используется при интенсивной записи. Например, полный индекс перестраивается раз в сутки, а дельта-индекс строится каждый час. По сути это частичный индекс по временной метке. - индекс реального времени (real-time index) — особый вид инкрементного индекса, характеризующийся высокой скоростью построения. Предназначен для часто меняющихся данных.
Индексы в кластерных системах - глобальный индекс — индекс по всему содержимому всех сегментов БД (shard). - сегментный индекс — глобальный индекс по полю-сегментируемому ключу (shard key). Используется для быстрого определения сегмента, на котором хранятся данные в процессе маршрутизации запроса в кластере БД. - локальный индекс — индекс по содержимому только одного сегмента БД.
Примерное правило, которым можно руководствоваться при создании индекса - если объем информации (в байтах) НЕ удовлетворяющей условию выборки меньше, чем размер индекса (в байтах) по данному условию выборки, то в общем случае оптимизация приведет к замедлению выборки.
Полное сканирование производится многоблочным чтением. Сканирование по индексу - одноблочным. Также, при доступе по индексу сначала идет сканирование самого индекса, а затем чтение блоков из набора данных. Число блоков, которые надо при этом прочитать из набора зависит от фактора кластеризации.
[!note] Если суммарная стоимость всех необходимых одноблочных чтений больше стоимости полного сканирования многоблочным чтением, то полное сканирование выгоднее и оно выбирается оптимизатором.
Таким образом, полное сканирование выбирается при слабой [[Селективность SQL|селективности]] предикатов зароса и/или слабой кластеризации данных, либо в случае очень маленьких наборов данных.
Индекс можно создать либо с помощью выражения CREATE INDEX:
CREATE INDEX index_name ON table_name (column_name)
либо указав ограничение целостности в виде уникального ==UNIQUE== или первичного ==PRIMARY== ключа в операторе создания таблицы CREATE TABLE