Установка Windows

Sql индексы. Индексирование баз данных

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

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

2) Создание индексов
CREATE INDEX
ON ()

3) Изменение и удаление индексов
Для управления активностью индекса используется оператор:
ALTER INDEX
Для удаления индекса используется оператор:
DROP INDEX

a) Правила выбора таблиц
1. Целесообразно индексировать таблицы, в которых выбирается не более 5% строк.
2. Следует индексировать таблицы, не имеющие дублей в разделе WHERE оператора SELECT.
3. Нецелесообразно индексировать часто обновляемые таблицы.
4. Нецелесообразно индексировать таблицы, занимающие не более 2-х страниц (для Oracle это менее 300 строк), поскольку её полный просмотр не дольше.

b) Правила выбора столбцов
1. Первичные и внешние ключи – часто используются для объединения таблиц, выборки данных и поиска. Это всегда уникальные индексы с максимальной полезностью
2. При использовании опций ссылочной целостности всегда нужен индекс на FK.
3. Столбцы, по которым часто производится сортировка и/или группирование данных.
4. Столбцы, по которым часто производится поиск в разделе WHERE оператора SELECT.
5. Не следует создавать индексов для длинных описательных столбцов.

c) Принципы создания составных индексов
1. Составные индексы хороши, если столбцы по отдельности имеют мало уникальных значений, а составной индекс обеспечивает большую уникальность.
2. Если все значения, выбираемые оператором SELECT, принадлежат составному индексу, то значения выбираются из индекса.
3. Следует создавать составной индекс, если в разделе WHERE используется два или более значений объединенных оператором AND.

d) Не рекомендуется создавать
Не рекомендуется создавать индексы по столбцам, включая составные, которые:
1. Редко используются для поиска, объединения и сортировки результатов запросов.
2. Содержат часто меняющиеся значения, что требует частого обновления индекса замедляющего производительность БД.
3. Содержат небольшое количество уникальных значений (менее 10% м/ж) или преобладающее число строк с одним-двумя значениями (город проживания поставщика Москва).
4. К ним в разделе WHERE применяют функции или выражение, и индекс не работает.

e) Следует не забывать
Следует стремиться к уменьшению количества индексов, поскольку при большом их числе снижается скорость обновления данных. Так MS SQL Server рекомендует создавать не более 16 индексов на таблицу.
Как правило, индексы создаются для запросов и поддержки ссылочной целостности.
Если индекс не используется для запросов, то его следует удалять, а ссылочную целостность обеспечивать с использованием триггеров.

В этой статье рассматриваются индексы и их роль в оптимизации времени выполнения запросов. В первой части статьи обсуждаются разные формы индексов и способы их хранения. Далее исследуются три основные инструкции языка Transact-SQL, применяемые для работы с индексами: CREATE INDEX, ALTER INDEX и DROP INDEX. Потом рассматривается фрагментация индексов ее влияния на производительность системы. После этого дается несколько общих рекомендаций по созданию индексов и описывается несколько специальных типов индексов.

Общие сведения

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

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

Но между индексом книги и индексом базы данных есть две существенные разницы:

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

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

Если для таблицы отсутствует подходящий индекс, для выборки строк система использует метод сканирования таблицы. Выражение сканирование таблицы означает, что система последовательно извлекает и исследует каждую строку таблицы (от первой до последней), и помещает строку в результирующий набор, если для нее удовлетворяется условие поиска в предложении WHERE. Таким образом, все строки извлекаются в соответствии с их физическим расположением в памяти. Этот метод менее эффективен, чем доступ с использованием индексов, как объясняется далее.

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

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

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

На рисунке ниже показана структура B+-дерева для таблицы Employee и прямой доступ к строке в этой таблице со значением 25348 для столбца Id. (Предполагается, что таблица Employee проиндексирована по столбцу Id.) На этом рисунке можно также видеть, что B+-дерево состоит из корневого узла, узлов дерева и промежуточных узлов, количество которых может быть от нуля и больше:

Поиск в этом дереве значения 25348 можно выполнить следующим образом. Начиная с корня дерева, выполняется поиск наименьшего значения ключа, большего или равного требуемому значению. Таким образом, в корневом узле таким значением будет 29346, поэтому делается переход на промежуточный узел, связанный с этим значением. В этом узле заданным требованиям отвечает значение 28559, вследствие чего выполняется переход на узел дерева, связанный с этим значением. Этот узел и содержит искомое значение 25348. Определив требуемый индекс, мы можем извлечь его строку из таблицы данных с помощью соответствующих указателей. (Альтернативным эквивалентным подходом будет поиск меньшего или равного значения индекса.)

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

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

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

Кластеризованный индекс определяет физический порядок данных в таблице. Компонент Database Engine позволяет создавать для таблицы лишь один кластеризованный индекс, т.к. строки таблицы нельзя упорядочить физически более чем одним способом. Поиск с использованием кластеризованного индекса выполняется от корневого узла B+-дерева по направлению к узлам дерева, которые связаны между собой в двунаправленный связанный список (doubly linked list), называющийся цепочкой страниц (page chain) .

Важным свойством кластеризованного индекса является та особенность, что его узлы дерева содержат страницы данных. (Узлы кластеризованного индекса всех других уровней содержат страницы индекса.) Таблица, для которой определен кластеризованный индекс (явно или неявно), называется кластеризованной таблицей. Структура B+-дерева кластеризованного индекса показана на рисунке ниже:

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

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

Некластеризованные индексы

Структура некластеризованного индекса точно такая же, как и кластеризованного, но с двумя важными отличиями:

    некластеризованный индекс не изменяет физическое упорядочивание строк таблицы;

    страницы узлов некластеризованного индекса состоят из ключей индекса и закладок.

Если для таблицы определить один или более некластеризованных индексов, физический порядок строк этой таблицы не будет изменен. Для каждого некластеризованного индекса компонент Database Engine создает дополнительную индексную структуру, которая сохраняется в индексных страницах. Структура B+-дерева некластеризованного индекса показана на рисунке ниже:

Закладка в некластеризованном индексе указывает, где находится строка, соответствующая ключу индекса. Составляющая закладки ключа индекса может быть двух видов, в зависимости от того, является ли таблица кластеризованной таблицей или кучей (heap). (Согласно терминологии SQL Server, кучей называется таблица без кластеризованного индекса.) Если существует кластеризованный индекс, то закладка некластеризованного индекса показывает B+-дерево кластеризованного индекса таблицы. Если таблица не имеет кластеризованного индекса, закладка идентична идентификатору строки (RID - Row Identifier) , состоящего из трех частей: адреса файла, в котором хранится таблица, адреса физического блока (страницы), в котором хранится строка, и смещения строки в странице.

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

    куча - прохождение при поиске по структуре некластеризованного индекса, после чего строка извлекается, используя идентификатор строки;

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

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

Язык Transact-SQL и индексы

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

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

Индекс для таблицы создается с помощью инструкции CREATE INDEX . Эта инструкция имеет следующий синтаксис:

CREATE INDEX index_name ON table_name (column1 ,...) [ INCLUDE (column_name [ ,... ]) ] [[, ] PAD_INDEX = {ON | OFF}] [[, ] DROP_EXISTING = {ON | OFF}] [[, ] SORT_IN_TEMPDB = {ON | OFF}] [[, ] IGNORE_DUP_KEY = {ON | OFF}] [[, ] ALLOW_ROW_LOCKS = {ON | OFF}] [[, ] ALLOW_PAGE_LOCKS = {ON | OFF}] [[, ] STATISTICS_NORECOMPUTE = {ON | OFF}] [[, ] ONLINE = {ON | OFF}]] Соглашения по синтаксису

Параметр index_name задает имя создаваемого индекса. Индекс можно создать для одного или больше столбцов одной таблицы, обозначаемой параметром table_name. Столбец, для которого создается индекс, указывается параметром column1. Числовой суффикс этого параметра указывает на то, что индекс можно создать для нескольких столбцов таблицы. Компонент Database Engine также поддерживает создание индексов для представлений.

Можно проиндексировать любой столбец таблицы. Это означает, что столбцы, содержащие значения типа данных VARBINARY(max), BIGINT и SQL_VARIANT, также могут быть индексированы.

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

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

Параметр CLUSTERED задает кластеризованный индекс, а параметр NONCLUSTERED (применяется по умолчанию) указывает, что индекс не изменяет порядок строк в таблице. Компонент Database Engine разрешает для таблицы максимум 249 некластеризованных индексов.

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

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

Чтобы по-настоящему понять полезность параметра INCLUDE, нужно понимать, что собой представляет покрывающий индекс (covering index) . Если все столбцы запроса включены в индекс, то можно получить значительное повышение производительности, т.к. оптимизатор запросов может определить местонахождение всех значений столбцов по страницам индекса, не обращаясь к данным в таблице. Такая возможность называется покрывающим индексом или покрывающим запросом. Поэтому включение в страницы узлов некластеризованного индекса дополнительных неключевых столбцов позволит получить больше покрывающих запросов, при этом их производительность будет значительно повышена.

Параметр FILLFACTOR задает заполнение в процентах каждой страницы индекса во время его создания. Значение параметра FILLFACTOR можно установить в диапазоне от 1 до 100. При значении n=100 каждая страница индекса заполняется на 100%, т.е. существующая страница узла так же, как страница, не относящаяся к узлу, не будет иметь свободного места для вставки новых строк. Поэтому это значение рекомендуется применять только для статических таблиц. (Значение по умолчанию, n=0, означает, что страницы узлов индекса заполняются полностью, а каждая из промежуточных страниц содержит свободное место для одной записи.)

При значении параметра FILLFACTOR между 1 и 99 страницы узлов создаваемой структуры индекса будут содержать свободное место. Чем больше значение n, тем меньше свободного места в страницах узлов индекса. Например, при значении n=60 каждая страница узлов индекса будет иметь 40% свободного места для вставки строк индекса в дальнейшем. (Строки индекса вставляются посредством инструкции INSERT или UPDATE.) Таким образом, значение n=60 будет разумным для таблиц, данные которых подвергаются довольно частым изменениям. При значениях параметра FILLFACTOR между 1 и 99 промежуточные страницы индекса содержат свободное место для одной записи каждая.

После создания индекса в процессе его использования значение FILLFACTOR не поддерживается. Иными словами, оно только указывает объем зарезервированного места с имеющимися данными при задании процентного соотношения для свободного места. Для восстановления исходного значения параметра FILLFACTOR применяется инструкция ALTER INDEX.

Параметр PAD_INDEX тесно связан с параметром FILLFACTOR. Параметр FILLFACTOR в основном задает объем свободного пространства в процентах от общего объема страниц узлов индекса. А параметр PAD_INDEX указывает, что значение параметра FILLFACTOR применяется как к страницам индекса, так и к страницам данных в индексе.

Параметр DROP_EXISTING позволяет повысить производительность при воспроизведении кластеризованного индекса для таблицы, которая также имеет некластеризованный индекс. Более подробную информацию смотрите далее в разделе "Пересоздание индекса".

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

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

Когда параметр ALLOW_ROW_LOCKS активирован (имеет значение on), система применяет блокировку строк. Подобным образом, когда активирован параметр ALLOW_PAGE_LOCKS , система применяет блокировку страниц при параллельном доступе. Параметр STATISTICS_NORECOMPUTE определяет состояние автоматического перерасчета статистики указанного индекса.

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

Параметр ON создает указанный индекс или на файловой группе по умолчанию (значение default), или на указанной файловой группе (значение file_group).

В примере ниже показано создание некластеризованного индекса для столбца Id таблицы Employee:

USE SampleDb; CREATE INDEX ix_empid ON Employee(Id);

Создание однозначного составного индекса показано в примере ниже:

USE SampleDb; CREATE UNIQUE INDEX ix_empid_prnu ON Works_on (EmpId, ProjectNumber) WITH FILLFACTOR= 80;

В этом примере значения в каждом столбце должны быть однозначными. При создании индекса заполняется 80% пространства каждой страницы узлов индекса.

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

Получение информации о фрагментации индекса

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

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

Использование представления sys.dm_db_index_physical_stats показано в примере ниже. (Прежде чем запускать пакет в примере на выполнение, необходимо удалить все существующие индексы таблицы Works_on. Для удаления индексов используется инструкция DROP INDEX, применение которой показано позже.)

USE SampleDb; DECLARE @dbId INT; DECLARE @tabId INT; DECLARE @indId INT; SET @dbId = DB_ID("SampleDb"); SET @tabId = OBJECT_ID("Employee"); SELECT avg_fragmentation_in_percent, avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats (@dbId, @tabId, NULL, NULL, NULL);

Как видно из примера, представление sys.dm_db_index_physical_stats имеет пять параметров. Первые три параметра определяют идентификаторы текущей базы данных, таблицы и индекса соответственно. Четвертый параметр задает идентификатор раздела, а последний определяет уровень сканирования, применяемый для получения статистической информации. (Значение по умолчанию для определенного параметра можно указать посредством значения NULL.)

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

Редактирование информации индекса

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

    представления каталога sys.indexes;

    представления каталога sys.index_columns;

    системной процедуры sp_helpindex;

    функции свойств objectproperty;

    среды управления Management Studio сервера SQL Server;

    динамического административного представления DMV sys.dm_db_index_usage_stats;

    динамического административного представления DMV sys.dm_db_missing_index_details.

Представление каталога sys.indexes содержит строку для каждого индекса и строку для каждой таблицы без кластеризованного индекса. Наиболее важными столбцами этого представления каталога являются столбцы object_id, name и index_id. Столбец object_id содержит имя объекта базы данных, которой принадлежит индекс, а столбцы name и index_id содержат имя и идентификатор этого индекса соответственно.

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

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

sp_helpindex [@db_object = ] "name"

Здесь переменная @db_object представляет имя таблицы.

Применительно к индексам, функция свойств objectproperty имеет два свойства: IsIndexed и IsIndexable. Первое свойство предоставляет сведения о наличии индекса у таблицы или представления, а второе указывает, поддается ли таблица или представление индексированию.

Для редактирования информации существующего индекса с помощью среды SQL Server Management Studio выберите требуемую базу данных в папке Databases, разверните узел Tables, в этом узле разверните требуемую таблицу и ее папку Indexes. В папке таблицы Indexes отобразится список всех существующих индексов для данной таблицы. Двойной щелчок мышью по индексу откроет диалоговое окно Index Properties со свойствами этого индекса. (Создать новый индекс или удалить существующий можно также с помощью среды Management Studio.)

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

Представление sys.dm_db_missing_index_details возвращает подробную информацию о столбцах таблицы, для которых отсутствуют индексы. Наиболее важными столбцами этого DMV являются столбцы index_handle и object_id. Значение в первом столбце определяет конкретный отсутствующий индекс, а во втором - таблицу, в которой отсутствует индекс.

Изменение индексов

Компонент Database Engine является одной из немногих систем баз данных, которые поддерживают инструкцию ALTER INDEX . Эту инструкцию можно использовать для выполнения операций по обслуживанию индекса. Синтаксис инструкции ALTER INDEX очень сходен с синтаксисом инструкции CREATE INDEX. Иными словами, эта инструкция позволяет изменять значения параметров ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY и STATISTICS_NORECOMPUTE, которые были описаны ранее при рассмотрении инструкции CREATE INDEX.

Кроме вышеперечисленных параметров, инструкция ALTER INDEX поддерживает три другие параметра:

    параметр REBUILD , используемый для пересоздания индекса;

    параметр REORGANIZE , используемый для реорганизации страниц узлов индекса;

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

Пересоздание индекса

При любом изменении данных, используя инструкции INSERT, UPDATE или DELETE, возможна фрагментация данных. Если эти данные проиндексированы, то также возможна фрагментация индекса, когда информация индекса оказывается разбросанной по разным физическим страницам. В результате фрагментации данных индекса компонент Database Engine может быть вынужден выполнять дополнительные операции чтения данных, что понижает общую производительность системы. В таком случае требуется пересоздать (REBUILD) все фрагментированные индексы.

Это можно сделать двумя способами:

    посредством параметра REBUILD инструкции ALTER INDEX;

    посредством параметра DROP_EXISTING инструкции CREATE INDEX.

Параметр REBUILD применяется для пересоздания индексов. Если для этого параметра вместо имени индекса указать ALL, будут вновь созданы все индексы таблицы. (Разрешив динамическое пересоздание индексов, вам не нужно будет удалять и создавать их заново.)

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

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

Реорганизация страниц узлов индекса

Параметр REORGANIZE инструкции ALTER INDEX задает реорганизацию страниц узлов указанного индекса, чтобы физический порядок страниц совпадал с их логическим порядком - слева направо. Это удаляет определенный объем фрагментации индекса, повышая его производительность.

Отключение индекса

Параметр DISABLE отключает указанный индекс. Отключенный индекс недоступен для применения, пока он не будет снова включен. Обратите внимание, что отключенный индекс не изменяется при внесении изменений в соответствующие данные. По этой причине, чтобы снова использовать отключенный индекс, его нужно полностью создать вновь. Для включения отключенного индекса применяется параметр REBUILD инструкции ALTER TABLE.

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

Удаление и переименование индексов

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

USE SampleDb; DROP INDEX ix_empid ON Employee;

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

Инструкцию DROP INDEX нельзя использовать для удаления индексов, которые создаются неявно системой для ограничений целостности, таких индексов, как PRIMARY KEY и UNIQUE. Чтобы удалить такие индексы, нужно удалить соответствующее ограничение.

Индексы можно переименовывать с помощью системной процедуры sp_rename.

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

Хотя компонент Database Engine не накладывает никаких практических ограничений на количество индексов, по паре причин это количество следует ограничивать. Во-первых, каждый индекс занимает определенный объем дискового пространства, следовательно, существует вероятность того, что общее количество страниц индекса базы данных может превысить количество страниц данных в базе. Во-вторых, в отличие от получения выгоды при использовании индекса для выборки данных, вставка и удаление данных такой выгоды не предоставляют по причине необходимости обслуживания индекса. Чем больше индексов имеет таблица, тем больший требуется объем работы по их реорганизации. Общим правилом будет разумно выбирать индексы для частых запросов, а затем оценивать их использование.

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

Индексы и условия предложения WHERE

Если предложение WHERE инструкции SELECT содержит условие поиска с одним столбцом, то для этого столбца следует создать индекс. Это особенно рекомендуется при высокой селективности условия. Под селективностью (selectivity) условия имеется в виду соотношение количества строк, удовлетворяющих условию, к общему количеству строк в таблице. Высокой селективности соответствует меньшему значению этого соотношения. Обработка поиска с использованием индексированного столбца будет наиболее успешной при селективности условия, не превышающей 5%.

Столбец не следует индексировать при постоянном уровне селективности условия 80% или более. В таком случае для страниц индекса потребуются дополнительные операции ввода/вывода, которые уменьшат любую экономию времени, достигаемую за счет использования индексов. В этом случае быстрее выполнять поиск сканированием таблицы, что и будет обычно выбрано оптимизатором запросов, делая индекс бесполезным.

Если условие поиска часто используемого запроса содержит операторы AND, лучше всего будет создать составной индекс по всем столбцам таблицы, указанным в предложении WHERE инструкции SELECT. Создание такого составного индекса показано в примере ниже:

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

Индексы и оператор соединения

В случае операции соединения рекомендуется создавать индекс для каждого соединяемого столбца. Соединяемые столбцы часто представляют первичный ключ одной из таблицы и соответствующий внешний ключ другой таблицы. Если указываются ограничения для обеспечения целостности PRIMARY KEY и FOREIGN KEY для соответствующих соединяемых столбцов, следует создать только некластеризованный индекс для столбца внешнего ключа, т.к. система неявно создаст кластеризованный индекс для столбца первичного ключа.

В примере ниже показано создание индексов, которые будут использованы, если у вас есть запрос с операцией соединения и дополнительным фильтром:

Покрывающий индекс

Как уже упоминалось ранее, включение всех столбцов запроса в индекс может значительно повысить производительность запроса. Создание такого индекса, называемого покрывающим (covering), показано в примере ниже:

USE AdventureWorks2012; GO DROP INDEX Person.Address.IX_Address_StateProvinceID; GO CREATE INDEX ix_address_zip ON Person.Address (PostalCode) INCLUDE (City, StateProvinceID); GO SELECT City, StateProvinceID FROM Person.Address WHERE PostalCode = 84407;

В этом примере в первую очередь из таблицы Address удаляется индекс IX_Address_StateProvinceID. Затем создается новый индекс, который помимо столбца PostalCode включает два дополнительных столбца. Наконец, инструкция SELECT в конце примера показывает запрос, покрываемый индексом. Для этого запроса системе нет необходимости выполнять поиск данных в страницах данных, поскольку оптимизатор запросов может найти все значения столбцов в страницах узлов некластеризованного индекса.

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

Индексы для вычисляемых столбцов

Компонент Database Engine позволяет создавать следующие специальные типы индексов:

    индексированные представления;

    фильтруемые индексы;

    индексы для вычисляемых столбцов;

    секционированные индексы;

    индексы сохранения столбца;

    XML-индексы;

    полнотекстовые индексы.

В этом разделе рассматриваются вычисляемые столбцы и связанные с ними индексы.

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

Виртуальные вычисляемые столбцы

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

USE SampleDb; CREATE TABLE Orders (OrderId INT NOT NULL, Price MONEY NOT NULL, Quantity INT NOT NULL, OrderDate DATETIME NOT NULL, Total AS Price * Quantity, ShippedDate AS DATEADD (DAY, 7, orderdate));

Таблица Orders в этом примере имеет два виртуальных вычисляемых столбца: total и shippeddate. Столбец total вычисляется с использованием двух других столбцов, price и quantity, а столбец shippeddate вычисляется при использовании функции DATEADD и столбца orderdate.

Постоянные вычисляемые столбцы

Компонент Database Engine позволяет создавать индексы для детерминированных вычисляемых столбцов, где базовые столбцы имеют точные типы данных. (Вычисляемый столбец называется детерминированным, если всегда возвращаются одни и те же значения для одних и тех же данных таблицы.)

Индексированный вычисляемый столбец может быть создан только в том случае, если следующим параметрам инструкции SET присвоено значение ON (эти параметры обеспечивают детерминированность столбца):

    QUOTED_IDENTIFIER

    CONCAT_NULL_YIELDS_NULL

Кроме этого, параметру NUMERIC_ROUNDABORT нужно присвоить значение off.

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

USE SampleDb; CREATE CLUSTERED INDEX ix1 ON Orders (Total);

После выполнения инструкции CREATE INDEX вычисляемый столбец total будет присутствовать в таблице физически. Это означает, что все обновления базовых столбцов вычисляемого столбца будут вызывать его обновление.

Столбец можно сделать постоянным и другим способом, используя параметр PERSISTED . Этот параметр позволяет задать физическое наличие вычисляемого столбца, даже не создавая соответствующего кластеризованного индекса. Эта возможность требуется для создания физических вычисляемых столбцов, которые создаются на столбцах с приблизительным типом данных (float или real). (Как упоминалось ранее, индекс для вычисляемого столбца можно создать только в том случае, если его базовые столбцы имеют точный тип данных.)

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

Как простой, так и сложный индекс имеют свой тип (Туре). Первичный (Primary) индекс (ключ) - это поле или группа по­лей, однозначно определяющих запись, то есть значения первич­ного индекса уникальны (не повторяются). В реляционной базе данных каждая таблица может иметь только один первичный ключ. Внешних ключей у таблицы может быть много и они будут иметь один из типов:

Candidate - кандидат в первичный ключ или альтернатив­ный ключ. Он обладает всеми свойствами первичного ключа.

Unique (уникальный) - допускает повторяющиеся значе­ния в поле, по которому он построен, но на экран будет вы­водиться только одна первая запись из группы записей с одинаковым значением индексного поля.

Regular (регулярный) - не накладывает никаких ограниче­ний на значения индексного поля и на вывод записей на эк­ран. Индекс только управляет порядком отображения запи­сей. Это наиболее популярный тип индекса.

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

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

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

Автоматически открывается со своей таблицей;

Его нельзя закрыть, но можно сделать не главным.

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

Создание индекса

Создать индекс можно двумя способами.

а. С помощью команды:

INDEX ON <индексное выражение> ТО < id х-файл> | TAG <имя тега>

Назначение опций:

<индексное выражение> - имя поля (или полей), по значениям которого надо построить индекс. При построении сложного ин­декса имена полей перечисляются через знак + (плюс). Если сложный индекс построен по:

Числовым полям, то индекс строится по сумме значений полей;

Символьным полям, то индекс строится сначала по значе­нию первого поля, а при повторяющихся значениях перво­го поля - по значениям второго поля; при повторяющихся значениях первого и второго полей - по значениям треть­его поля и т. д.;

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

Длина индексного выражения не должна превышать 254 сим­вола.

ТО < id х-файл> - указывается имя одноиндексного файла.

TAG <имя тега> - указывается имя тега в мультииндексном файле. Если используется опция , то создаваемый тег помещается в указанный мульти-индексный файл, а если требуемый мультииндексный файл от­сутствует, то будет построен структурный мультииндексный файл. Если опция опущена, то созданный тег будет помещен в текущий мультииндексный файл.

FOR <условие> - устанавливает режим отбора в индекс тех за­писей таблицы, которые удовлетворяют <условию>.

COMPACT - управляет созданием компактного одноиндекс­ного файла. В старших версиях FoxPro не используется.

DESCENDING - строит индекс по убыванию. По умолчанию ис­пользуется построение индекса по возрастанию (ASCENDING). Для одноиндексных файлов можно построить индекс только по возрастанию. Если перед использованием команды INDEX ON... подать команду SET COLLATE, то можно построить одноин-дексный файл по убыванию.

UNIQUE - строит уникальный индекс. Если индексное поле (поля) содержит повторяющиеся значения, то в индекс попадает только одна первая запись и остальные записи будут не до­ступны.

ADDITIVE - вновь создаваемый индексный файл не закрыва­ет уже открытые к этому моменту времени индексные файлы. Если опция опущена, то вновь создаваемый индексный файл за­крывает все ранее открытые индексные файлы.

б. С помощью Главного меню:

В этом случае индекс создается либо при создании таблицы, либо при модификации структуры таблицы. Для этого в ди­алоговой панели Table Designer надо выбрать вкладку Index (рис. 3.1).

Каждый индекс описывается одной строкой в окне диалого­вой панели Table Designer.

Вграфе Name указывается имя тега мультииндексного фай­ла. Если ранее открыт один из мультииндексеых файлов, то вновь построенный индекс помещается в открытый мультиин-дексный файл. Если индекс строится одновременно с создани­ем табличного файла или табличный файл не имеет мультиин-дексных файлов, то вновь построенный индекс помещается в автоматически создаваемый структурный мультииндексный файл.

В графе Туре, снабженной раскрывающимся списком, ука­зывается один из допустимых типов индекса. Если индекс стро­ится для таблицы, входящей в состав базы данных, то возможны четыре значения: Primary, Candidate, Unique и Regular. Если ин деке строится для свободной таблицы, то в раскрывающемся списке отсутствует значение Primary.

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

В графе Filter можно задать логическое условие и построить индекс не для всех записей таблицы, а только для записей, удов­летворяющих условию фильтра. Эта графа также снабжена построителем выражений. Содержание и внешний вид обоих по­строителей одинаковые (рис. 3.2).

На рис. 3.2 показано построение сложного индекса по двум символьным полям ush_step и uch_zvan (имя тегу uch было присвоено до вызова на экран построителя выражений). Знак «+», указывающий на построение сложного индекса, взят из рас­крывающегося списка String,

В раскрывающемся списке String приведены допустимые строковые функции. Аналогично в раскрывающихся списках Math, Logical и Date приведены допустимые математические, ло­гические функции и функции даты. Нужная функция из этих раскрывающихся списков выбирается щелчком левой кнопки мыши. Имена полей (список Fields) и имена переменных (спи­сок Variables) выбираются с помощью двойного щелчка левой кнопки мыши. Получившееся в результате выражение помеща­ется в окно Expression.

В раскрывающемся списке From Table указано имя таблицы, из которой берутся поля для построения индекса. При желании можно заказать любую таблицу из текущей базы данных и для по­строения индекса взять любое поле.

6. Индексы и оптимизация производительности

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

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

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

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

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

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

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

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

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

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

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

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

Последовательность столбцов в составном индексе

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

Например, представим себе телефонный справочник, отсортированный вначале по городу, затем по фамилии, и затем по имени. Если вы знаете город, вы можете легко найти все телефоны этого города. Однако в таком справочнике будет весьма трудоёмко найти все телефоны, записанные на определённую фамилию - для этого необходимо посмотреть в секцию каждого города и поискать там нужную фамилию. Некоторые СУБД выполняют эту работу, остальные же просто не используют такой индекс.

Производительность

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

Ограничения

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

SELECT first_name FROM people WHERE last_name = "Франкенштейн"; .

Для выполнения такого запроса без индекса СУБД должна проверить поле last_name в каждой строке таблицы (этот механизм известен как «полный перебор» или «полное сканирование таблицы», в плане может отображаться словом NATURAL). При использовании индекса СУБД просто проходит по B-дереву, пока не найдёт запись «Франкенштейн». Такой проход требует гораздо меньше ресурсов, чем полный перебор таблицы.

Теперь возьмём такой запрос:

SELECT email_address FROM customers WHERE email_address LIKE "%@yahoo.com"; .

Этот запрос должен нам найти всех клиентов, у которых е-мейл заканчивается на @yahoo.com , однако даже если по столбцу email_address есть индекс, СУБД всё равно будет использовать полный перебор таблицы. Это связано с тем, что индексы строятся в предположении, что слова/символы идут слева направо. Использование символа подстановки в начале условия поиска исключает для СУБД возможность использования поиска по B-дереву. Эта проблема может быть решена созданием дополнительного индекса по выражению reverse(email_address) и формированием запроса вида:

SELECT email_address FROM customers WHERE reverse(email_address) LIKE reverse("%@yahoo.com"); .

В данном случае символ подстановки окажется в самой правой позиции (moc.oohay@%), что не исключает использование индекса по reverse(email_address) .