Типы и виды индексов
• первичный индекс — индекс, построенный на основе поля первичного ключа данных. Такой индекс всегда один, значение ключа индекса уникально; введение первичного индекса позволяет хранить данные в неупорядоченном последовательном файле. В реляционной БД каждая таблица может иметь только один первичный ключ. Внешних ключей у таблицы может быть много и они могут иметь один из типов:
Ш Candidat — это кандидат в первичный ключ или альтернативный ключ
Ш Unique (уникальный тип индекса) — это тип индекса, который допускает повторяющиеся значения в поле, по которому он построен, но на экран будет выводиться только одна первая запись из группы записей с одинаковым значением индексного поля
Ш Regular (регулярный тип индекса) — это тип индекса, который не накладывает никаких ограничений на значения индексного поля и на вывод записей, на экран
Ш Primary — это один из индексов, удовлетворяющий требованиям индекса типа Candidat может быть выбран в качестве первичного ключа; [2, с.47]
• вторичный индекс — индекс, построенный не по первичному ключу (см. рис.4) [6]. Позволяет ускорить операции выборки для запросов, выполняющих фильтрацию не по полям первичного ключа. Вторичных индексов может быть несколько (однако следует помнить, что чем больше индексов у файла данных, тем медленнее вставка в него данных — вторичные индексы должны строиться только для самых критичный и часто выполняемых запросов); значения ключей в индексе могут повторяться;
Рис. 4. Пример структуры с вторичным индексом
• индекс кластеризации. Файл данных последовательно упорядочивается по неключевому полю, и на основе этого неключевого поля формируется поле индексации, поэтому в файле может быть несколько записей, соответствующих значению этого поля индексации. Неключевое поле называется атрибутом кластеризации; [3, с.111]
• обращенный (inverted) индекс — это индекс, который сочетает в себе все индексы и используется совместно с промежуточным уровнем групп — сегментов указателей; [6, с.635] На рисунке 5 [9] показан поиск документов с помощью обращенного индекса.
Рис. 5. Поиск документа с помощью обращенного индекса
2) по числу используемых полей записи данных, а также в зависимости от структуры и организации записи:
• несоставной — ключ индекса состоит только из одного поля записи;
• составной — ключ индекса состоит из нескольких полей записи, при этом сортировка ключа индекса выполняется в следующем виде: основной порядок сортировки задает первое поле ключа, дополнительный порядок сортировки (сортировка в группе) задает второе поле ключа, и т.п.; составной индекс по полям записи (f1,f2,f3,…,fn) может использоваться для поиска по полю f1 либо по комбинациям полей (f1, f2), (f1, f2, f3), (f1, f2, f3, f4), …, (f1,f2,f3,…,fn-1), (f1,f2,f3,…,fn) — т.е. один составной индекс может обслуживать ряд запросов, но поля, используемые при этом, должны располагаться с начала ключа индекса (т.к. они зададут порядок сортировки записей индекса) и без разрывов; [3, с.112]
• простой индекс — это индекс, построенный по значениям одного поля;
• сложный индекс — это индекс, построенный по значениям двух и более полей;
• интервальный индекс [interval index] — это индекс, значения которого определяются некоторой областью, например, диапазоном от 3 до 12;
3) по числу ссылок на данные:
• плотный — число индексных записей равно числу записей данных, одна индексная запись ссылается только на одну запись данных. На рисунке 6 [8] изображен пример плотного индекса;
Рис. 6. Пример плотного индекса
• неплотный — число индексных записей меньше числа записей данных, индекс указывает либо на первую запись в определенной группе, либо на страницу с определенной группой записей данных, записи данных при этом также должны быть упорядочены по некоторому полю. Например, если список сотрудников упорядочен по фамилии, то можно построить неплотный индекс, которых будет в качестве ключа содержать первую букву фамилии, и этот индекс будет ссылаться на записи данных следующим образом: «А» — > на первую фамилию в списке, начинающуюся на «А», «Б» — > на первую фамилию в списке, начинающуюся на «Б», и т.п. Поиск конкретного сотрудника по фамилии тогда можно осуществить следующим образом:
a) найти букву, на которую начинается фамилия сотрудника;
b) выполнить поиск фрагмента файла данных, отвечающего за размещение фамилий начинающихся на данную букву, с использованием неплотного индекса;
c) выполнить поиск в этом фрагменте файла данных (либо последовательным перебором, либо используя упорядоченность по фамилии для бинарного поиска).
Рис. 7. Многоуровневый индекс [3, с.113]
4) по числу уровней индекса:
· одноуровневый — индекс, который непосредственно ссылается на данные, а не на другие индексные структуры;
· многоуровневый — индекс, состоящий из нескольких индексных файлов, при этом только индекс первого уровня ссылается на реальные данные (обычно это плотный индекс), а индексы более высоких уровней ссылаются на предыдущие уровни (эти индексы обязательно неплотные). Структура многоуровневого индекса за счет увеличения объема данных позволяет сократить время поиска записей, т.к. данные уже разбиты на фрагменты, которые бы получались, например, в результате бинарного поиска. Многоуровневый индекс вводится при наличии в файле данных большого числа записей, обычно при условии, что бинарный поиск в одноуровневом плотном индексе проводится за значительное число шагов. Поиск данных в многоуровневом индексе начинается с самого верхнего уровня и продолжается пока не будет достигнута запись данных, поиск ссылки на следующий уровень на текущем уровне проводится методом двоичного поиска в определенном диапазоне. На практике, число уровней многоуровневого индекса обычно не превышает трех; [3, с.114]
· в зависимости от характера используемой системы знаков:
· буквенный индекс [alphabetic code, alphabetic notation] — индекс, использующий отдельные буквы или сочетание букв алфавита;
· цифровой индекс [numerical code, numerical notation] — индекс, использующий отдельные цифры, числа, сочетания цифр или их комбинации;
· десятичный индекс [decimal code, decimal classification code] — цифровой индекс, составленный на основе десятичной системы счисления;
· алфавитно-цифровой индекс [alphanumeric code] — смешанный индекс, состоящий из букв и цифр;
· смешанный индекс [mixed code, mixed notation] — индекс, состоящий из разнородных знаков, например, из букв различных алфавитов, букв и цифр и т.п.
5) в зависимости от уровня приоритетности:
· гипериндекс [hyperindex] — высший уровень индекса индексной организации баз данных, принятый в некоторых СУБД (наряду с главным и нормальным индексами);
· главный (основной, первичный, старший) индекс [master index, primary index, main subject code, main classification number] — 1. Индекс высшего уровня в иерархической системе организации данных;
· 2. Индекс, отражающий главную тему содержания индексируемого текста, документа и т.п. и относящийся к основной принятой системе классификации;
· нормальный индекс [normal index] — подмножество ключей базы данных, соответствующих конкретному значению поля, объявленного дескриптором (признаком поиска). Используется в четырехуровневой системе индексов СУБД, например, — ADABAS;
· вспомогательный (дополнительный) индекс [additional index, auxiliary code, auxiliary classification number] — индекс, являющийся дополнением к главному (основному) и отражающий дополнительные признаки индексируемого текста, документа и т.п. или относящийся к вспомогательной системе классификации;
6) в зависимости от характера индексируемых объектов и/или назначения индекса различают:
· авторский знак [author mark, author notation, author number] — индекс, обозначающий автора произведения, используемый при расстановке и поиске книг в библиотеках;
· кеттерский знак [cutter number] — авторский знак, определяемый по «Авторской таблице» Ч. Кеттера;
· расстановочный индекс [location mark, location number] — индекс, используемый для расстановки и поиска книг, документов и т.п. в библиотеке или фонде;
· каталожный индекс [catalog classification mark, catalog classification number] — индекс, используемый для расстановки и поиска карточек в каталоге;
· индекс каталога [catalog index] — старший индекс в библиотечной организации данных;
· индекс массива [array index] — индекс, присваиваемый массиву документов или данных для его идентификации;
· индекс файла [index number] — в некоторых операционных системах (например, UNIX) номер индексного дескриптора файла и др. [7]
Преимущество всех перечисленных выше типов и видов индексов в том, что они значительно повышают производительность, и уменьшают скорость поиска.
Основной недостаток использования индексов — замедление обновления файла данных, т.к. при добавлении новой записи в файл данных требуется обновление индекса, а индекс представляет собой упорядоченный последовательный файл и обновляется медленно. [3]
Индексы в базе данных Oracle
Индексы Oracle обеспечивают быстрый доступ к строкам таблиц, сохраняя отсортированные значения указанных столбцов и используя эти отсортированные значения для быстрого нахождения ассоциированных строк таблицы . Индексы позволяют находить строку с определенным значением столбца, просматривая при этом лишь небольшую часть общего объема строк таблицы. Таким образом правильное использование индексов сокращает до минимума количество дорогостоящих операций ввода-вывода.
Применение индексов представляет собой компромисс между ускорением получения результатов запросов и замедлением обновлений и вставок данных. Первая часть этого компромисса – ускорение запросов – довольно очевидна: если поиск выполняется по отсортированному индексу вместо полного сканирования всей таблиц, то запрос проходит намного быстрее. Но всякий раз, когда вы обновляете, вставляете или удаляете строку таблицы с индексами, индексы также должны быть обновлены соответствующим образом. То есть такие операции на таблицах с индексами обходятся дороже.
Вообще говоря, если таблицы в основном используются для чтения (выборки) информации, как в хранилищах данных, то лучше иметь много индексов. Если база данных относится к типу OLTP, с большим количеством вставок, обновлений и удалений, то лучше обойтись меньшим числом индексов.
Если только вам не нужно обращаться к большинству сток таблицы, индексированные запросы обеспечивают более быстрое получение результатов, чем запросы, не использующие индексы. Не существует ограничений на количество индексов, которые могут относиться к одной таблице Oracle, но, как упоминалось ранее, от их количества зависит производительность. Индекс полностью прозрачен для пользователя – т.е. оператор SQL пользователя не должен изменяться в результате создания индексов. Однако разработчикам приложений для построения эффективных запросов следует хорошо представлять себе , что такое индексы и как они работают.
Индексы могут относиться к нескольким типам, наиболее важные из которых перечислены ниже:
- Уникальные и неуникальные индексы. Уникальные индексы основаны на уникальном столбце – обычно вроде номера карточки социального страхования сотрудника. Хотя уникальные индексы можно создавать явно, Oracle не рекомендует это делать. Вместо этого следует использовать уникальные ограничения. Когда накладывается ограничение уникальности на столбец таблицы, Oracle автоматически создает уникальные индексы по этим столбцам.
- Первичные и вторичные индексы. Первичные индексы – это уникальные индексы в таблице, которые всегда должны иметь какое-то значение и не могут быть равны null. Вторичные индексы – это прочие индексы таблицы, которые могут и не быть уникальными.
- Составные индексы – индексы, содержащие два или более столбца из одной и той же таблицы. Они также известны как сцепленные индексы (concatenated index). Составные индексы особенно полезны для обеспечения уникальности сочетания столбцов таблицы в тех случаях, когда нет уникального столбца, однозначно идентифицирующего строку.
Руководство по созданию индексов
Хотя хорошо известно, что индексы повышают производительность базы данных, следует знать, как их заставить работать должным образом. Добавление ненужных или неподходящих индексов к таблице может даже привести к снижению производительности. Ниже предоставлены некоторые рекомендации по созданию эффективных индексов в базе данных Oracle.
- Индекс имеет смысл, если нужно обеспечить доступ одновременно не более чем к 4-5% данных таблицы. Альтернативной использования индекса для доступа к данным строки является полное последовательное чтение таблицы от начала до конца, что называется полным сканированием таблицы. Полное сканирование таблицы больше подходит для запросов, которые требуют извлечения большего процента данных таблицы. Помните, что применение индексов для извлечения строк требует двух операций чтения: индекса и затем таблицы.
- Избегайте создания индексов для сравнительно небольших таблиц. Для таких таблиц больше подходит полное сканирование. В случае маленьких таблиц нет необходимости в хранении данных и таблиц, и индексов.
- Создавайте первичные ключи для всех таблиц. При назначении столбца в качестве первичного колюча Oracle автоматически создаст индекс по этому столбцу.
- Индексируйте столбцы, участвующие в многотабличных операциях соединения.
- Индексируйте столбцы, которые часто используются в конструкциях WHERE.
- Индексируйте столбцы, участвующие в операциях ORDER BY и GROUP BY или других операциях, таких как UNION и DISTINCT, включающих сортировку. Поскольку индексы уже отсортированы, объем работы по выполнению необходимой сортировки данных для упомянутых операций будет существенно сокращен.
- Столбцы, стоящие из длинно-символьных строк, обычно плохие кандидаты на индексацию.
- Столбцы, которые часто обновляются, в идеале не должны быть индексированы из-за связанных с этим накладных расходов.
- Индексируйте таблицы в которых мало строк имеют одинаковые значения.
- Сохраняйте количество индексов небольшим.
- Составные индексы могут понадобиться там, где одностолбцовые значения сами по себе не уникальны. В составных индексах первым столбцом ключа должен быть столбец в котором количество строк с одинаковым значением минимально.
Всегда помните золотое правило индексации таблиц: индекс таблицы должен быть основан на типах запросов, которые будут выполняться над столбцами этой таблицы. На таблице можно создавать более одного индекса: например, можно создать индекс на столбце X, или столбце Y, или обоих сразу, а также один составной индекс на обоих столбцах. Принимая правильное решение относительно того, какие индексы следует создавать, подумайте о наиболее часто используемых типах запросов данных таблицы.
Схемы индексации Oracle
Oracle предлагает несколько схем индексации, соответствующих требованиям различных типов приложений. На фазе проектирования после тщательного анализа конкретных требований приложения, необходимо выбрать правильный тип индекса.
(B*tree)
В реализации индексов на основе B-деревьев используется концепция сбалансированного (на что указывает буква ‘B’ (balanced)) дерева поиска в качестве основы структуры индекса. В Oracle имеется собственный вариант B-дерева. Это обычные индексы, создаваемые по умолчанию, когда вы применяете оператора CREATE INDEX.
Индексы на основе B-деревьев структурированы в форме обратного дерева, где блоки верхнего уровня называются блоками ветвей (branch blocks), а блоки нижнего уровня – листовыми блоками (leaf blocks). В иерархии узлов все узлы кроме вершины, или корневого узла, имеют родительский узел и могут иметь ноль или более дочерних узлов. Если глубина древовидной структуры , т.е. количество уровней, одинакова от каждого листового блока до корневого узла, то такое дерево называется сбалансированным, или B-деревом.
B-деревья автоматически поддерживают необходимый уровень индекса по размеру таблицы. B-деревья также гарантируют, что индексные блоки всегда будут заполнены не меньше, чем наполовину, и менее, чем на 100%. B-деревья допускают операции выборки, вставки и удаления с очень небольшим количеством операций ввода-вывода на один оператор. Большинство B-деревьев имеет всего три и менее уровней. При использовании B-дерева нужно читать только блоки B-дерева, так что количество операций ввода-вывода будет ограничено числом уровней B-дерева (скажем, тремя) плюс две операции ввода-вывода на выполнение обновления или удаления (одна для чтения и одна для записи). Для выполнения поиска по B-дереву понадоисят всего три или менее обращений к диску.
Реализация B-дерева от Oracle – всегда сохраняет дерево сбалансированным. Листовые блоки содержат по два элемента: индексированные значения столбца и соответствующий идентификатор ROWID для строки, которая содержит это значение столбца. ROWID – уникальный указатель Oracle, идентифицирующий физическое местоположение строки и обеспечивающий самый быстрый способ доступа к строке в базе данных Oracle. Сканирование индекса быстро дает ROWID строки, и отсюда можно быстро получить к ней доступ непосредственно. Если запрос нуждается лишь в значении индексированного столбца, то конечно, последний шаг исключается, поскольку извлекать дополнительные данные, кроме прочитанных из индекса, не потребуется.
Оценка размера индекса
Для оценки размера нового индекса можно использовать пакет DBMS_SPACE. Процедуре CREATE_INDEX_COST этого пакета потребуется передать оператор DDL, создающий индекс, в качестве атрибута.
SET SERVEROUTPUT ON DECLARE l_index_ddl varchar2(1000); l_used_bytes NUMBER; l_allocated_bytes NUMBER; BEGIN DBMS_SPACE.create_index_cost ( ddl => 'create index repsons_idx on EMP(ENAME)', used_bytes => l_used_bytes, alloc_bytes => l_allocated_bytes); DBMS_OUTPUT.PUT_LINE ('RESULT:'); DBMS_OUTPUT.PUT_LINE ('used_bytes = ' || l_used_bytes || ' byte'); DBMS_OUTPUT.PUT_LINE ('alloc_bytes = ' || l_allocated_bytes || ' byte'); END; / Обратите внимание на отличие между атрибутами, касающимися размера, в процедуре CREATE_INDEX_COST:
- Used_bytes показывает количество байт, которыми представлены данные индекса;
- Alloc_bytes показывает количество байт, которое займет индекс в табличном пространстве после его создания.
Создание индекса
Индекс создается с помощью оператора CREATE INDEX
CREATE INDEX employee_id ON employee(employee_id) TABLESPACE MY_INDEXES; По умолчанию Oracle допускает дублирование значения в столбцах индекса, которые также называются ключевыми столбцами. Однако можно специфицировать уникальный индекс, что исключит дублирование значений столбца в нескольких строках.
Для создания уникального индекса служит оператор CREATE UNIQUE INDEX.
Специальные типы индексов
Нормальный или типовой индекс, который создается в базе данных, называется индексом кучи (heap index), или неупорядоченным индексом. Oracle также предоставляет несколько специальных типов индексов для специфических нужд.
Битовые индексы (bitmap indexes)
Битовые индексы используют битовые карты для указания значения индексированного столбца. Это идеальный индекс для столбца с низкой кардинальностью (число уникальных записей в таблице мало) при при большом размере таблицы. Эти индексы обычно не годятся для таблиц с интенсивным обновлением, но хорошо подходят для приложений хранилищ данных.
Битовые индексы состоят из битового потока (единиц и нулей) для каждого столбца индекса. Битовые индексы очень компактны по сравнению с нормальными индексами на основе B-деревьев.
| Индексы B-деревьев | Битовые индексы |
| Хороши для данных с высокой кардинальностью | Хороши для данных с низкой кардинальностью |
| Хороши для баз данных OLTP | Хороши для приложений хранилищ данных OLAP |
| Занимают много места | Используют, относительно мало места |
| Легко обновляются | Трудно обновляются |
Для создания битового индекса используется оператор
CREATE BITMAP INDEX gender_dx ON employee(gender) TABLESPACE MY_INDEXES; Иногда можно наблюдать значительное повышение производительности при замене обычных индексов B-дерева на битовые в некоторых очень крупных таблицах. Однако каждый элемент битового индекса открывает огромное количество строк в таблице, так что когда данные обновляются,вставляются или удаляются из таблицы, то необходимые обновления битового индекса очень велики., и сам индекс может существенно увеличиться в размере. Единственный способ обойти это увеличение размера индекса с последующим падением производительности заключается в регулярной его перестройке. Битовый индекс – не слишком разумная альтернатива для таблиц, подвергающихся большому количеству вставок, удалений и обновлений.
Индексы с реверсированным ключом
Индексы с реверсированным ключом – это, по сути, то же самое, что и индексы B-деревьев, за исключением того, что байты данных ключевого столбца при индексации меняют порядок на противоположный. Порядок столбцов остается нетронутым, меняется только порядок байтов. Самое большое преимущество применения индексов с реверсивным ключом состоит в том, что они исключают неприятные последствия упорядоченной вставки значений в индекс. Вот как создается индекс с реверсированным ключом:
SQL> CREATE INDEX reverse_idx ON employee(emp_id) REVERSE; При использовании индекса с реверсированным ключом базы данных не сохраняет ключи индекса друг за другом в лексикографическом порядке. Таким образом, когда в запросе присутствует предикат неравенства, ответ получается медленнее, поскольку база данных вынуждена выполнять полное сканирование таблицы. При индексе с реверсированным ключом база данных не может запустить запрос по диапазону ключа индекса.
Индексы со сжатым ключом
Сэкономить пространство хранения индекса вместе с повышением производительности можно за счет создания индекса со сжатым ключом. Всякий раз, когда индексируемый ключ имеет повторяющийся компонент, или же создается уникальный многостолбцовый индекс, получается выигрыш от использования сжатия ключа. Вот пример:
SQL> CREATE INDEX emp_indx1 ON employees(ename) TABLESPACE MY_INDEXES COMPRESS 1; Приведенный выше оператор сжимает все дублированные вхождения индексированного ключа в листовом блоке индекса (на уровне 1).
Индексы на основе функций
Индексы на основе функций предварительно вычисляют значения функций по заданному столбцы и сохраняют результат в индексе. Когда конструкция WHERE содержит вызовы функций, то основанные на функциях индексы являются идеальным способом индексирования столбца.
Ниже показано, как создать индекс на основе функции LOWER
SQL> CREATE INDEX lastname _idx ON employees(LOWER(l_name)); Этот оператор CREATE INDEX создаст индекс по столбцу l_name, хранящему фамилии сотрудников в верхнем регистре. Однако этот индекс будет основан на функции, поскольку база данных создаст его по столбцу l_name, применив к нему предварительно функцию LOWER для преобразования его значения в нижний регистр.
Секционированные индексы
Секционированные индексы используются для индексации секционированных таблиц. Oracle предлагает два типа индексов для таких таблиц: локальные и глобальные.
Существенное различие между ними заключается в том, что локальные индексы основаны на разделах таблицы, по которой они созданы. Если таблица секционирована на 12 разделов по диапазонам дат, то индексы также будут распределены по тем же 12 разделам. Другими словами, между разделами индексов и разделами таблиц существует соответствие «один к одному». Такого соответствия нет между глобальными индексами и разделами таблицы, потому что глобальные индексы секционируются независимо от базовых таблиц.
В следующих разделах будут раскрыт важные различия между управлением глобального секционированными индексами и локально секционированными индексами.
Глобальные индексы
Глобальные индексы на секционированных таблицах могут быть как секционированными, так и несекционированными. Глобальные несекционированные индексы подобны обычным индексам Oracle для несекционированных таблиц. Для создания таких индексов применяется обычный синтаксис CREATE INDEX.
Ниже приведен пример глобального индекса на таблице ticket_sales:
SQL> CREATE INDEX tickersales_idx ON ticket_sales(month) GLOBAL PARTITION BY range(month) (PARTITION ticketsales1_idx VALUES LESS THAN (3) PARTITION ticketsales1_idx VALUES LESS THAN (6) PARTITION ticketsales2_idx VALUES LESS THAN (9) PARTITION ticketsales3_idx VALUES LESS THAN (MAXVALUE); Обратите внимание, что управление глобально секционированными индексами требует серьезных усилий. Всякий раз, когда происходит какое-т о действие DDL над секционированной таблицей, ее глобальные индексы требуют перестройки. Действия DDL над лежащей в основе таблице помечают глобальные индексы как недействительные. По умолчанию любая операция обслуживания секционированной таблицы делает недействительными глобальные индексы.
Давайте в качестве примера воспользуемся таблицей ticket_sales, чтобы разобраться, почему это так. Предположим, что вы ежеквартально уничтожаете самый старый раздел, чтобы освободить место для нового раздела, в который поступят данные за новый квартал. Когда уничтожается раздел, относящийся к таблице ticket_sales, глобальные индексы могут стать недействительными, потому что часть данных, на которые они указывают, перестают существовать. Чтобы предотвратить такое объявление недействительным индекса из-за уничтожения раздела, необходимо использовать опцию UPDATE GLOBAL INDEXES вместе с оператором DROP PARTITION:
SQL> ALTER TABLE ticket_sales DROP PARTITION sales_quarter01 UPDATE GLOBAL INDEXES; Если не включить оператор UPDATE GLOBAL INDEXES, то все глобальные индексы станут недействительными. Опцию UPDATE GLOBAL INDEXES можно также использовать при добавлении, объединении, обмене, слиянии, перемещении, разделении или усечении секционированных таблиц. Разумеется, с помощью ALTER INDEX..REBUILD можно перестраивать любой индекс, который становится недействительным, но эта опция также требует дополнительных затрат времени и обслуживания.
При небольшом количестве листовых блоков индекса, что приводит к высокой конкуренции Oracle рекомендует использовать глобальные индексы с хэш-секционированием. Синтаксис для создания хэш-секционированного глобального индекса подобен тому, что применяется для хэш-секционированной таблицы. Например, следующий оператор создает хэш-секционированный глобальный индекс:
SQL> CREATE INDEX hgidx ON tab (c1,c2,c3) GLOBAL PARITION BY HASH (c1,c2) ( PARTITION p1 TABLESPACE tsb_1, PARTITION p2 TABLESPACE tsb_2, PARTITION p3 TABLESPACE tsb_3, PARTITION p4 TABLESPACE tsb_4, ); Локальные индексы
Локально секционированные индексы, в отличие от глобально секционированных индексов, имею отношение «один к одному» с разделами таблицы. Локально секционированные индексы можно создавать в соответствии с разделами и даже подразделами. База данных конструирует индекс таким образом, чтобы он был секционирован так же, как и его таблица. При каждой модификации раздела таблицы база автоматически сопровождает это соответствующей модификацией раздела индекса. Это, наверное, самое большое преимущество использования локально секционированных индексов – Oracle автоматически перестраивает их всегда, когда уничтожается раздел или над ним выполняется какая-то другая операция DDL.
Ниже приведен простой пример создания локально секционированного индекса на секционированной таблице:
SQL> CREATE INDEX ticket_no_idx ON Ticket_sales(ticket_no) LOCAL TABLESPACE localidx_01; Невидимые индексы
По умолчанию оптимизатор «видит» все индексы. Тем не менее, можно создать невидимый индекс, который оптимизатор не обнаруживает и не принимает во внимание при создании плана выполнения оператора. Невидимый индекс можно применять в качестве временного индекса для определенных операций или его тестирования перед тем, как сделать его «официальным». Вдобавок, иногда объявления индекса невидимым можно использовать в качестве альтернативы уничтожению индекса или объявлению его недоступным. Сделать индекс невидимым можно временно, чтобы протестировать эффект от его уничтожения.
База данных поддерживает невидимый индекс точно так же, как и нормальный (видимый) индекс. После объявления индекса невидимым, его и все прочие невидимые индексы можно сделать вновь видимым для оптимизатора, установив значение параметра optimizer_use_invisible_index равным TRUE на уровне сеанса или всей системы. Значением этого параметра по умолчанию является FALSE, а это означает, что оптимизатор по умолчанию не может использовать невидимые индексы.
Создание невидимого индекса.
Чтобы сделать индекс невидимым, к оператору CRETE INDEX нужно добавить конструкцию INVISIBLE.
С помощью команды ALTER INDEX можно превратить существующий индекс в невидимый.
ALTER INDEX test_idx INVISIBLE; И обратная команда
ALTER INDEX test_idx VISIBLE; Приведенный ниже запрос к представлению DBA_INDEXES показывает состояние видимости индекса:
SQL> SELECT index_name, visibility FROM user_indexes WHERE index_name =’indx1’; Мониторинг использования индекса
Если вы сомневаетесь в использовании определенного индекса, можете попросить Oracle выполнить мониторинг его применения. Таким образом, если индекс окажется избыточным, его можно уничтожить и сэкономить место в хранилище, а также снизить накладные расходы на операции DML.
Опишем, что потребуется сделать для отслеживания индекса в базе данных. Предположим, что вы пытаетесь узнать, используется ли индекс p_key_sales в определенных запросах к таблице sales. Обеспечьте репрезентативный промежуток времени для оценки использования индекса. Для базы данных OLTP это промежуток может быть относительно коротким. Для хранилища данных может понадобится запустить тестовый мониторинг на несколько дней, чтобы точно проверить, как используется индекс.
Чтобы запустить мониторинг использования индекса, войдите в базу данный как владелец индекса p_keyPsales и запустите следующую команду:
SQL> ALTER INDEX p_key_sales MONITORING USAGE; Теперь запустите какие-нибудь запросы к таблице sales. Завершите мониторинг, применив следующую команду:
SQL> ALTER INDEX p_key_sales NOMONITORING USAGE; После этого можно запросить представление словаря данных V$OBJECT_USAGE для определения того, используется ли индекс p_key_sales.
SQL> SELECT index_nm, used FROM v$object_usage WHERE index_name=’P_KEY_SALES’; Причина по которой нельзя узнать количество случаев использования индекса, связана с тем, что база данных выполняет мониторинг его использования только на фазе разбора (parsing); если бы разбор производился при каждом выполнении, пострадала бы производительность.
Обслуживание индексов
Данные индекса постоянно изменяются из-за DML-действий, связанных с его таблицей. Индексы часто становятся слишком большими, если происходит много удалений сток, потому что пространство, занятое удаленными значениями, автоматически повторно индексом не используется. За счет периодического применения команды REBUILD можно реорганизовать индексы и сделать их более компактными, а потому и более эффективными. Команда REBUILD также служит для изменения параметров хранения, которые устанавливаются во время начального создания индекса.
ALTER INDEX sales_idx REBUILD; Перестройка индексов лучше уничтожения и воссоздания неудачного индекса, потому что при этой операции пользователи продолжают иметь доступ к индексу в процессе его перестройки. Однако индексы в процессе перестройки накладывают много ограничений на действия пользователя. Еще более эффективный способ перестройки индексов состоит в том, чтобы сделать это в оперативном (online) режиме, как показано в следующем примере. Во время оперативной перестройки индекса разрешено применение всех операций DML, но не операций DDL.
ALTER INDEX sales_idx REBUILD ONLINE; Оперативную перестройку индекса можно ускорить за счет добавления к показанному выше оператору ALTER INDEX конструкции ONLINE NOLOGGING. После добавления этой конструкции база данных не будет генерировать данные повторного выполнения для операции перестройки индекса.
Пример запроса который показывает на какие внешние ключи отсутствуют индексы
elect table_name, constraint_name, cname1 || nvl2(cname2,','||cname2,null) || nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) || nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) || nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null) columns from ( select b.table_name, b.constraint_name, max(decode( position, 1, column_name, null )) cname1, max(decode( position, 2, column_name, null )) cname2, max(decode( position, 3, column_name, null )) cname3, max(decode( position, 4, column_name, null )) cname4, max(decode( position, 5, column_name, null )) cname5, max(decode( position, 6, column_name, null )) cname6, max(decode( position, 7, column_name, null )) cname7, max(decode( position, 8, column_name, null )) cname8, count(*) col_cnt from (select substr(table_name,1,30) table_name, substr(constraint_name,1,30) constraint_name, substr(column_name,1,30) column_name, position from user_cons_columns ) a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'R' group by b.table_name, b.constraint_name ) cons where col_cnt > ALL ( select count(*) from user_ind_columns i where i.table_name = cons.table_name and i.column_name in (cname1, cname2, cname3, cname4, cname5, cname6, cname7, cname8 ) and i.column_position cons.col_cnt group by i.index_name ) Tags: Oracle Database, Indexes
1.2.7. Индексы в SQL Server
Для ограничений UNIQUE и PRIMARY KEY автоматически создается индекс, который упрощает поиск необходимых данных. Что такое индекс? Если говорить простыми словами, то это способ отсортировать данные по определенной колонке. Когда список отсортирован, намного проще производить поиск необходимых данных.
Понимание того, как хранятся данные – является основой понимание того, как получить к ним доступ. Для начала нам нужно разобраться с таким понятием как куча – это коллекция страниц данных, содержащих строки для таблицы:
- Каждая страница данных содержит 8 килобайт информации. Группа и 8-и рядом стоящих страниц называется пространством.
- Строки данных не хранятся в каком-либо определенном порядке, и нет определенного порядка для последовательности страниц.
- Страницы данных не связаны в связанные списки.
- Когда строка вставляется в страницу и страница переполнена, страница разделяется.
Сервер SQL получает доступ к данным одним из следующих способов:
- Сканирует все страницы таблицы – сканирование таблицы. Когда SQL Server выполняет сканирование таблицы он:
- Начинает с начала таблицы;
- Сканирует от страницы к странице через все строки таблицы;
- Выделяет строку, которая соответствует запросу.
- Пересекает структуру дерева индексов для поиска строк, соответствующих запросу;
- Выделяет только необходимые строки, соответствующие критериям запроса.
Первым делом, SQL Server определяет, какие индексы существуют. Оптимизатор запроса (компонент, предназначенный для генерирования оптимального плана для запроса) определяет что использовать – сканировать таблицу или индексы. Индексы более предпочтительны.
Когда вы рассматриваете, нужно ли создавать индексы, рассчитайте два фактора, для гарантирования, что индексы будут более эффективны, чем сканирование таблицы: природа данных и природа запросов к таблице.
Индексы ускоряют доступ к данным. Для примера, без индекса, без индексы вам понадобится перелистать постранично всю книгу для определения содержания. По содержанию легче найти интересующую информацию.
Сервер SQL использует индексы для указания на расположение строки в странице данных вместо просматривания всех страниц таблицы. Рассматривайте следующие факты и рекомендации об индексах:
- Индексы обычно увеличивают скорость выполнения запросов связанных таблиц и выполнение сортировки и группировки;
- Индексы принуждают делать строки уникальными, если включена уникальность.
- Индексы создаются в порядке возрастания или уменьшения.
Индексы достаточно полезны, но они занимают место на диске и берут на себя дополнительные накладные расходы и расходы на эксплуатацию. Индексы могут создать и проблемы:
- когда вы изменяете данные в индексной колонке, сервер SQL обновляет связанные индексы.
- накладные расходы на поддержку индексов требуют времени и ресурсов. Поэтому не создавайте индексы, которые не будете часто использовать.
- индексы на колонки, содержащие большое количество доблирующих данных могут иметь несколько преимуществ.
Индексы бывают кластерными (CLUSTERED) и не кластерными (NONCLUSTERED). В кластерном индексе строки физически сортируются на диске в соответствии с индексируемым полем. Я думаю, что не надо объяснять, почему кластерный индекс может быть только один на таблицу? Нельзя же одновременно физически отсортировать данные по двум ключам.
При не кластерном индексе строки могут на диске храниться в любом порядке, а сортировка осуществляется с помощью определенной таблицы или дерева индекса. В SQL сервере используется принцип дерева.
Конечно, это все из области администрирования SQL сервера, но вы должны понимать, что такое индекс и для чего он нужен.
Все первичные ключи, начиная с SQL Server 2000, по умолчанию создаются кластерными. Ограничения UNIQUE по умолчанию создаются не кластерными.
Чтобы лучше понимать индексы и разницу между кластерным и не кластерным индексом, посмотрим на рисунок 1.6, где показана примерная схема индекса. Каждый прямоугольник – это страница данных из 8 кб. Вверху находится корень дерева, а внизу листья дерева.
Допустим, что нам нужно найти имя Анатолий. Из корня мы узнаем, что для поиска информации об имени нужно спуститься влево вниз. Здесь также будет ссылка о том, что нужно спуститься еще влево вниз и тогда мы окажемся в листовом блоке.
В не кластерном индексе в листовом блоке находится ссылка на строку с данными, где можно найти имя Анатолий, а в кластерном индексе данные находятся непосредственно в листовом блоке.
Кластерный индекс иногда работает быстрее, а иногда удобнее. У меня был случай, когда я разрабатывал программу на Delphi + MS SQL Server, которая состояла из двух частей. Серверная часть программы сохраняла в базе данных строки, полученные с производственного оборудования, а клиентская их читала. Когда сервер сохранял данные в базе, программа направляла программе клиенту по сети сообщение. Получив это сообщение, клиент должен был прочитать сохраненную строку и вывести пользователю на экран.
Чтобы упростить чтение на клиентской стороне, без выполнения лишних запросов на выборку данных, я использовал готовый метод компонентов доступа к базам данных Delphi – переход на последнюю строку. Этот метод очень удобен тем, что быстро позволяет получить последнюю строку. Все прекрасно работало, но почему-то в определенные моменты переход блокировался на определенной строке, и какое-то время новее строки не отображались.
Проблема крылась как раз в не кластерном индексе. Когда базе данных не хватало места, она выделяла блок памяти на диске в произвольном месте. Если это место оказывалось в конце базы, то переходы происходили удачно. Если находилось свободное место в уже существующих блоках памяти, но не заполненных до конца (в любом месте базы данных, но не в конце), то переход не осуществлялся, потому что физически строка оказывалась не в конце. Проблему решило использование кластерного индекса.
Следующий пример, показывает, как можно создать не кластерный индекс:
CREATE TABLE Names ( idName int IDENTITY(1,1), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY NONCLUSTERED (idName), )
Если нужно, чтобы первичный ключ был кластерным, то для MS SQL Server 2000 это по умолчанию и явное указание оператора CLUSTERED нужно только для MS SQL Server 7. Учитывайте эту разницу при обработке первичного ключа разными версиями MS SQL Server во время разработке собственных приложений. А лучше ни на кого не надеяться, а всегда явно указывать тип необходимого ключа.
В таблице может быть создано 249 не кластерных индексов и только один индекс может быть кластерным. Такое ограничение количества кластерных индексов связано с тем, что физически можно упорядочить только по одному полю, а не из-за прихоти разработчиков MS SQL Server.
Как мы уже знаем, кластерным может быть и ограничение уникальности. Для ограничения уникальности создается индекс, а свойство кластерный/не кластерный относится как раз к индексу. Например:
CREATE TABLE Names ( idName int , vcName varchar(50), vcLastName varchar(50), vcSurName varchar(50), dBirthDay datetime, CONSTRAINT cn_unique UNIQUE CLUSTERED(vcName, vcLastName, vcSurName, dBirthDay) )
В данном примере создается только индекс для полей, с ограничением уникальности. При этом главного ключа нет. А что если его создать:
CREATE TABLE Names ( idName int , vcName varchar(50), vcLastName varchar(50), vcSurName varchar(50), dBirthDay datetime, CONSTRAINT pk_idName PRIMARY KEY (idName), CONSTRAINT cn_unique UNIQUE CLUSTERED(vcName, vcLastName, vcSurName, dBirthDay) )
В данном случае создается первичный ключ и по умолчанию он должен быть кластерным. Но так как кластерным создается ограничение уникальности cn_unique, то первичный ключ автоматически получит не кластерный индекс.
А если явно указать, что мы хотим первичный ключ и ограничение уникальности сделать кластерными? В этом случае произойдет ошибка: «Cannot add more than one clustered index for constraints on table ‘Names’ » (не могу добавлять более чем один кластерный индекс для ограничения на таблицу Names). Я же говорил, что кластерным может быть только один индекс.
Чтобы вам было удобно видеть, что делает сервер после создания таблицы, лучше всего выполнить команду:
sp_help Names
Команда sp_help (точнее это процедура SQL сервера) отображает подробную информацию о указанной таблице, в данном случае это таблица Names. Ее команду лучше всего выполнять в программе Query Analyzer, которая поставляется вместе с MS SQL Server, потому что ее результат состоит из нескольких таблиц, а Query Analyzer умеет их отображать в удобочитаемом виде.
Выполните команду и посмотрите на результат выполнения sp_help для таблицы Names. Обратите внимание на последние две строки. Здесь отображается список ограничений и их имена. Чуть выше показаны две строки индексов для этих ограничений. Первая строка соответствует ограничению cn_unique и во второй колонке видно, что этот индекс кластерный (clustered). Вторая строка – это индекс pk_idNames и во второй колонке указано, что индекс не кластерный (nonclustered).
Ваше бизнес окружение, характеристики данных и использование данных определяют колонки, которые должны быть проиндексированы. Полезность индекса напрямую зависит от процента возвращаемых запросом строк. Маленький или большой процент более эффективен.
Создавайте индексы на следующие поля:
- первичный ключ, такой индекс создается автоматически;
- внешний ключ или поле, которое часто используется для связи таблиц. На внешние ключи индексы автоматически не создаются, но если в связанных таблицах находится много строк, то индекс реально может повысить производительность. Если в основной таблице много строк, а в связанной не более 100, можно обойтись и без индекса;
- поле, используемое для поиска ряда значений;
- поле, по которому сортируются данные;
- поля, которые группируются во время агрегации (оператор GROUP BY);
- поле, которое часто используется в запросах SELECT.
- редко используемые в запросе;
- содержащие несколько уникальных значений, например колонки, содержащие только значения мужской или женский пол. Такой индекс будет только тормозить систему;
- объявленные как text, ntext или image типы данных. Колонки с этими типами данных не могут быть проиндексированы.
Вы должны создавать только самые необходимые индексы, потому что каждый лишний индекс может серьезно ударить по производительности во время добавления новых записей. Это особенно становится заметным, при массовой загрузке данных.
Попробуем разобраться, какой индекс нужно создавать – кластерный или нет. Для того чтобы сделать правильный выбор нужно понимать, как будет использоваться ваша таблица.
Когда вы оптимизируете производительность для вставки данных для часто используемой таблицы, рассмотрите создание кластерного индекса на первичный ключ уникальной колонки. Для увеличения скорости вставки в маленькие группы страниц в конец таблицы. Частый доступ помещает эти страницы в память.Таблицы, которые часто используются для отчетов, группировки для агрегации или поиска ряда значений могут принести пользу при кластерном индексе на сортируемую колонку.
Сервер SQL использует значение кластерного индекса в качестве идентификатора строки внутри каждого не кластерного индекса. Кластерный индекс может повторяться много раз в структуре вашей таблицы.
Рекомендуется делать кластерный индекс минимальным по размеру. Для предотвращения больших кластерных индексов:
- ограничьте количество колонок в кластерном индексе;
- уменьшите среднее значение символов с помощью использования типа данных varchar вместо char, а лучше использовать числовые типы данных или уникальный идентификатор guid;
- старайтесь использовать максимально маленький тип данных.
Когда вы определяете плотность ваших данных, помните, что плотность связана с определенными элементами данных. Плотность может изменяться. Что значит плотность? Рассмотрим ее на примере таблицы работников, которая содержит даты рождения. Допустим, что у вас на фирме работает 100 человек в возрасте от 23 до 30 и 10 человек в возрасте старше 30. В диапазоне от 23 до 30 получается высокая плотность, потому что здесь находиться очень много записей.
Так как данные распределяются не равномерно, оптимизатор запросов может использовать или не использовать индексы. Оптимизатор может:
- Выполнить сканирование таблицы для поля с большой плотностью или для поля, значение которого может вызвать возврат большого количества строк.
- Если в проиндексированном поле с именами много имен «Вася», то по этому имени может быть использовано сканирование. При этом, для редкого значения, например, имени «Аврора», будет использоваться индекс.
Разброс данных связан с плотностью. Когда вы определяете плотность данных, вы должны также рассматривать и разброс.
Разброс данных определяет количество данных в определенных рамках значений и как много строк попадает в эти пределы. Если индексированная колонка имеет мало уникальных значений, получение данных может быть медленным. Например, если у вас есть таблица с полем отсортированным по фамилии, то данные могут быть неравномерно разбросаны по алфавиту. На некоторые буквы фамилий больше.
Сервер сам по себе не может знать о разбросе данных. Для этого ему необходимо собрать определенную статистику о полях и содержащихся в таблице значениях, чтобы можно было принять эффективное решение. Имея статистику, сервер сможет принять более эффективное решение о том, надо ли использовать индекс, или сканирование таблицы будет выполняться быстрее. О статистике мы еще не раз будем говорить в главе 4.
Создание индексов в SQL Server
Теперь посмотрим, как создавать индексы вручную. До этого момента мы использовали индексы, которые сервер создавал автоматически для первичного ключа и уникального поля. Сервер SQL автоматически создает индекс, когда создается ограничение PRIMARY KEY или UNIQUE, но бывает необходимость создать индекс на поле без этих ограничений.
Для создания индекса на произвольное поле используется оператор CREATE INDEX, а для удаления используется DROP INDEX. Вы должны быть владельцем базы данных или администратором, чтобы выполнять эти операторы.
Информация об индексах храниться в системной таблице sysindexes. В главе 2 мы научимся работать с таблицами и просматривать их содержимое. Просто ради интереса попробуйте просмотреть системную таблицу sysindexes. Только не вздумайте ее изменять вручную, системные таблицы можно только просматривать.
Лучше всего, если индекс создается на поле с маленьким типом данных, такой индекс будет более эффективным. Когда вы создаете кластерный индекс, все существующие не кластерные индексы перестраиваются, поэтому желательно в первую очередь создавать кластерный индекс.
В общем виде команда создания индекса выглядит следующим образом:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON < table | view >( column [ ASC | DESC ] [ . n ] ) [ WITH < index_option >[ . n] ] [ ON filegroup ]
Чтобы удобнее было понять команду, я разбиваю ее на строчки. В первой строке указывается ключевые слова CREATE и INDEX, между которыми можно указать UNIQUE, чтобы индекс был уникальным и CLUSTERED или NONCLUSTERED, чтобы сделать индекс кластерным или не кластерным соответственно. После INDEX указывается имя индекса.
Имя должно быть понятным, должно отображать, что это индекс и желательно, чтобы отражалось имя поля. Я рекомендую использовать для этого формат: «I_CL_Имя». Первая буква I, указывает на то, что это индекс. Затем я ставлю CL или UCL, что будет показывать кластерный или не кластерный индекс. И в самом конце перечисляются имена полей, которые индексируются. В данном случае только одно поле «vcName».
Во вторую строку я пишу ключевое слово ON, за которым идет имя таблицы и в скобках имена индексируемых полей.
Следующий пример создает кластерный индекс на колонку vcName:
CREATE CLUSTERED INDEX I_CL_vcName ON TestTable(vcName)
После имени колонки нужно указать направление сортировки индекса. Направление задается ключевыми словами ASC (возрастание) или DESC (убывание). Следующий пример создает не кластерный индекс по убыванию:
CREATE NONCLUSTERED INDEX I_CL_vcName ON TestTable(vcName DESC)
Теперь поговорим о удалении индексов. Можно удалять только созданные вами индексы. Для этого используется оператор DROP INDEX. Вы не можете использовать этот оператор для удаления индекса, который был автоматически создан на ограничения PRIMARY KEY или UNIQUE. Вы должны удалить ограничение, прежде чем удалять индекс. Нельзя удалять индексы системных таблиц.
Если удалить кластерный индекс, то все не кластерные индексы будут автоматически перестроены.
В общем виде команда удаления индекса выглядит следующим образом:
DROP INDEX 'table.index | view.index' [ . n ]
В следующем примере удаляется созданный нами ранее индекс:
DROP INDEX TestTable.I_CL_vcName
Ранее мы уже создавали индекс уникальности, но делали мы это только на этапе создания таблицы. Если она уже существует, то индекс уникальности можно добавить с помощью оператора CREATE UNIQUE INDEX.
Уникальный индекс гарантирует, что все данные в колонке с таким индексом – уникальны, и не содержат повторяющихся значений. Сервер SQL автоматически создает индекс, когда создается ограничение PRIMARY KEY или UNIQUE.
Сервер SQL проверяет дубликаты каждый раз, когда вы выполняете операторы INSERT или UPDATE. Если дубликат существует, то сервер отклоняет ваши операторы и возвращает сообщение об ошибке.
Если повторяющиеся значения существуют, когда вы создаете уникальный индекс, операция CREATE INDEX отклоняется. Сервер возвращает сообщение об ошибке с первым дубликатом, но могут существовать и еще дубликаты. Используйте следующий простой сценарий для любых таблиц, чтобы найти дублирующие значения в колонке.
SELECT индексная колонка, COUNT(индексная колонка) FROM имя таблицы GROUP BY индексная колонка HAVING COUNT (индексная колонка)>1 ORDER BY индексная колонка
Снова мы забегаем вперед, потому что запросы SELECT это тема следующей главы. Если вы не работали с SQL, то этот запрос еще не понятен для вас, но вернитесь к нему после прочтения второй главы, и все встанет на свои места.
Составные индексы
Составные индексы используют более одной колонки в качестве ключевого значения. Создавайте составные индексы, когда две или более полей чаще всего используются для поиска в качестве ключа и если запрос ссылается только на все поля в составном индексе. Если запрос будет использовать не все поля, то индекс, скорей всего использоваться не будет.
Для примера, телефонный справочник является хорошим примером. Справочник организован по фамилии. Вместе с фамилией для поиска регулярно используется имя, потому что часто существует много записей для одной фамилии с разными именами и вполне логично создать индекс из фамилии и имени одновремнно.
Вы можете объединять до 16 колонок в составной индекс. Сумма длины всех колонок составного индекса должна быть менее 900 байт. При этом, все поля должны быть из одной таблицы.
Объявляйте сначала уникальные колонки. Первые колонки, описанные в операторе CREATE INDEX, имеют высший приоритет при сортировке. При поиске данных в таблице, ваш запрос должен будет обязательно ссылаться на первую колонку индекса, иначе индекс точно использоваться не будет.
Индекс на поля «Фамилия» и «Имя» это не то же самое, что индекс на поля «Фамилия» и «Имя». Эти индексы имеют разный порядок полей. Например, для первого случая сортировка будет следующей:
Фамилия Имя ----------------------------------------- Иванов Андрей Иванов Сергей Петров Андрей Петров Василий
Те же самые поля, но с индексом «Имя» и «Фамилия» будут отсортированы следующим образом:
Фамилия Имя ----------------------------------------- Иванов Андрей Петров Андрей Петров Василий Иванов Сергей
В данном случае главным является имя, и именно оно сортируется первым.
Составной индекс позволяет повысить производительность запросов и уменьшить количество индексов на таблицу. Производительность повышается за счет того, что сервер для поиска необходимых данных сканирует только один индекс.
Следующий пример создает не кластерный составной индекс для таблицы телефонного справочника. Обратите внимание, что поле «Фамилия» описывается первой, потому что она чаще всего является основой при выборке данных из таблицы:
CREATE UNIQUE NONCLUSTERED INDEX I_NCL_Фамилия_Имя ON [Телефонный справочник] (Фамилия, Имя)
Так как индекс уникальный, в таблицу нельзя будет записать двух людей с фамилией и именем Иванов Андрей.
Сервер SQL предлагает опции, которые могут ускорить создание индекса, а также увеличить производительность индексов.
Сколько типов индексов существует sql
На индексы XE «Индекс» в таблице возлагаются две задачи:
q ускорение поиска по индексированным столбцам;
q гарантия уникальности значений, хранящихся в индексируемых столбцах.
2.2.1. Общие соображения
Принцип индексирования довольно прост. Пояснить его легче на примере массивов. Пусть имеется массив A[i] , где I может меняться от 1 до N . Тогда массив B[i] будет индексировать массив A , если фрагмент
FOR I:=1 TO N DO WRITELN(A[B[I]]);
выведет нам все элементы массива A . Если при этом элементы выводимого массива будут упорядочены, то массив B может быть использован для ускоренного поиска элементов массива A . Этот ускоренный поиск называется еще двоичным или бинарным поиском и может быть представлен листингом 2.1.
IF C>A[B[I]] THEN K1:=I ELSE K2 :=I;
Обратим внимание, что поиск всегда начинается с одного и того же элемента, определяемого начальным значением индекса I . Дальнейшее шаги зависят от элемента C , который мы ищем в массиве. Множество путей поиска (значений индекса I ) образует разветвленную структуру, называемую B -деревом XE » B -дерево» , или сбалансированным деревом XE «Сбалансированное дерево» ( balanced tree XE » Balanced tree » ).
На рис. 2.3 схематически изображено четырехуровневое B -дерево. Уровень 1 дерева соответствует корню дерева. Промежуточные уровни 2 и 3 называют узловыми. Здесь располагаются ссылки на другие уровни дерева. Уровень 4 называется уровнем листьев. На уровне листьев содержатся указатели на данные. Мы видим, что для получения любого данного следует пройти одинаковый путь от корня до соответствующего листа. Это и есть основная отличительная черта сбалансированного дерева.
Рис. 2.3. B -дерево
Наш алгоритм (см. листинг 2.1) не предполагал построение сбалансированного дерева. Для поиска в упорядоченном массиве это совсем не нужно. Дело в том, что за один шаг мы всегда можем получить любой элемент массива по его индексу. Однако структура данных в базе SQL Server отлична от линейной структуры массива (см. разд. 2.5). Данные хранятся на страницах размером 8 Кбайт. Причем между страницами одной таблицы могут быть свободные страницы или страницы другой таблицы. Поэтому, чтобы осуществлять эффективный поиск данных на этих страницах, индексы изначально строятся в виде B -дерева.
Обратимся опять к алгоритму из листинга 2.1. На каждом шаге поиска происходит обращение к индексируемому массиву. Это существенный момент. Если речь пойдет об индексации данных, располагаемых в файле, то обращение на каждом шаге может сильно замедлить поиск. По этой причине в структурах современных индексов хранится и само значение атрибута, по которому осуществляется индексирование. Разумеется, от этого размер индекса увеличивается, но поиск осуществляется быстрее.
Индексы уже давно взяты на вооружение современными СУБД. Посредством индексов доступ к данным, хранящимся в таблицах, ускоряется многократно. Но есть и обратная сторона медали. При изменении содержимого таблицы (вставки строк, удаление строк, обновление столбцов, входящих в атрибут, по которому произведено индексирование) требуется дополнительное время для перестройки содержимого и структуры индексов. Можно вывести простую закономерность: чем больше индексов определено в таблице, тем быстрее выполняются операции поиска в этой таблице и тем медленнее операции изменения данных в таблице. Следовательно, количество используемых в базе данных индексов будет определяться характером информационной системы. Можно выделить две крайних ситуации: большое количество операций выборки данных и статические таблицы или часто меняющееся содержимое таблиц и не большое количество операций выборки. Реальные системы, как правило, находятся где-то посредине, и искусство разработчика заключается в поиске компромиссного решения.
2.2.2. Типы индексов
Некластерные индексы
Некластерный индекс XE «Индекс:некластерный» является самостоятельной структурой (объектом), принимающей форму B -дерева (см. предыдущий раздел). Листья такого индексного дерева (см. рис. 2.3) содержат ссылки на страницы данных таблицы. Все узлы некластерного индекса содержат значения ключа, по которому произведено индексирование, т. е. поиск по индексу осуществляется без обращения к данным таблицы. Некластерные индексы можно создавать не только для таблиц, но и представлений XE «Представление» ( Views XE » Views » ) — виртуальных таблиц (см. разд. 4.4).
В некластерный индекс можно включать неключевые столбцы таблицы. Неключевые столбцы добавляются к листьям индекса. Это ускоряет выполнение запросов, если все столбцы, указанные в запросе, входят в индекс илибо как ключевые, либо как неключевые элементы. Такой результат объясняется тем, что поиск по таблице, по сути, осуществляется без обращения к самой таблице.
Рассмотрим кратко некоторые характеристики индекса, которые доступны при работе с этим окном.
q При помощи свойства Columns можно определить, какие столбцы будут входить в атрибут, по которому будет производиться индексирование. Имейте в виду, что порядок следования столбцов в составном атрибуте также важен. Кроме этого, можно установить также порядок сортировки индекса ( Ascending или Descending ).
q Свойство Is Unique определяет, будет ли данный индекс гарантировать уникальность атрибуту, по которому он устанавливается.
q Свойства ( Name ) и Description позволяют задать уникальное имя индекса и записать комментарий для данного индекса.
q Свойство Create As Clustered позволяет создавать кластерные индексы.
q Свойство Filegroup or Partition Scheme Name определяет имя группы файлов или имя схемы секции (см. разд. 2.6).
q Свойство Partition Column List содержит список столбцов, которые используются в секционной функции (см. разд. 2.6).
q Fill Factor — фактор заполнения страницы индекса. Фактор заполнения индекса измеряется в процентах и определяет, какая часть листовых страниц индекса будет заполнена. Если страницы заполнены на 100%, то для добавления новых строк индекса, что потребуется, если содержимое таблицы будет изменено, придется перестраивать весь индекс, добавляя новые листовые страницы. С другой стороны, если листовые страницы заполнены почти полностью, то индекс более компактен, что оптимизирует выборку (поиск) из соответствующей таблицы. Таким образом, варьируя фактором заполнения, можно увеличивать скорость тех или иных операций над таблицей.
q Pad Index — с помощью этого свойства можно предписать серверу резервировать свободные строки на узловых страницах индекса (см. рис. 2.3 и комментарий к нему).
q Ignore Duplicate Keys — игнорировать дублирующие ключи. Если этому свойству присвоить значение Yes (при условии, что индекс уникален), то вместе с отменой операции, приводящей к дублированию строк, произойдет откат всей транзакции. В противном случае операция дублирования будет отменена, но откат транзакции не произойдет.
q Re — compute Statistics — установка этого свойства (значение Yes ) приводит к автоматическому перестроению статистики. Статистика необходима оптимизатору запросов сервера. Отмена автоматической перестройки статистики может повысить скорость выполнения операций вставки и изменения данных, но отрицательно скажется на скорости выполнения запросов чтения к таблице.
Мы еще вернемся к перечисленным и другим свойствам индексов, когда в главе 3 будем рассматривать команды CREATE INDEX и ALTER INDEX .
При работе с окном управления индексами (см. рис. 2.4) вы обнаружите, что некоторые свойства недоступны для изменения. Это не должно вас расстраивать: все их можно изменять при помощи команд CREATE INDEX и ALTER INDEX . Но и это еще не все.
Кластерные индексы
В таблице может существовать лишь один кластерный индекс XE «Индекс:кластерный» . По умолчанию кластерный индекс создается, когда определяется первичный ключ. Кластерный индекс встроен в саму таблицу, листовая часть индекса является страницами данных самой таблицы.
Рис. 2.6. Структура кластерного индекса
На рис. 2.6 представлена схема кластерного индекса. Прямоугольниками изображены страницы памяти, где хранятся и данные, и индексы. Обратим внимание на следующее.
q Таблица и индекс неразрывно связаны. По сути, таблица стала частью (листовой) индекса.
q Страницы в кластерном индексе связаны не только по вертикали, как в обычном индексе, но и по горизонтали, т. е. образуют двунаправленный список.
q Поскольку таблица обычно снабжается первичным ключом, что вызывает автоматическое создание кластерного индекса, то может создаться впечатление, что кластерный индекс предполагает уникальность атрибута, по которому он создан. В действительности, это совсем не так, и вы можете при желании создавать неуникальные кластерные индексы.
Свойства, которые мы разбирали для обычных индексов, будут справедливы и для кластерных индексов, и мы вернемся к ним к следующей главе, когда будем рассматривать программные способы управления индексами.
Индексы xml
В SQL Server 2005 появился новый тип данных — xml . Если в таблице есть столбцы, имеющие тип xml , то для таблицы могут быть созданы xml -индексы. Эти индексы увеличат скорость запросов к xml -данным, но могут замедлить операции обновления этих данных.
Создание xml -индексов XE «Индекс: xml » состоит из двух этапов.
q Создание основного ( Primary ) xml -индекса. Индекс может быть создан только при условии, что в таблице уже существует кластерный индекс, созданный по первичному ключу.
q Создание дополнительных ( Secondary ) xml -индексов. Могут быть три типа дополнительных индексов:
Полнотекстовые индексы
В SQL Server 2005 заложены возможности ускоренного поиска по текстовым полям таблиц. Поиск базируется на концепции полнотекстовых индексов XE «Индекс:полнотекстовый» ( full — text indexes XE » Full — text indexes » ). Особенно эффективно данный механизм будет работать, если в ваших таблицах хранятся большие объемы текстовой информации (типы столбцов char , varchar , nvarchar ). В полнотекстовых индексах индексируются отдельные слова и фразы, расположенные в текстах, так что вы сможете быстро найти все строки таблицы, где в проиндексированных текстовых полях содержится заданное вами сочетание слов. Полнотекстовый поиск может быть также настроен на поиск в структурированных текстах, например документах MS Word , которые хранятся в столбцах типа varbinary и image .
Для того чтобы создавать полнотекстовые индексы для таблиц выбранной базы данных, следует вначале в окне свойств базы данных на вкладке Files установить флаг Use full — text indexing . Далее имеются два пути.
q Обратиться в раздел Full Text Catalogs в Object Explorer . Потом щелкнув в разделе правой кнопкой мыши, выбрать в контекстном меню New Full — Text Catalog . Затем следует определиться с именем индекса и каталогом, где будут храниться файлы индекса. После этого в разделе Full Text Catalogs появится новая строка с именем индекса. Щелкнув по ней правой кнопкой мыши и выбрав пункт меню Properties , можно приступить к настройке индекса. В частности, в окне можно указать таблицы и столбцы таблицы, которые будут участвовать в индексировании. Принимаются следующие типы столбцов: char , varchar , nchar , nvarchar , varbinary и image . На последних двух типах данных следует остановиться отдельно. Для того чтобы включить их в полнотекстовый индекс, в таблице должен быть еще один текстовый столбец, содержащий тип данных, которые будут храниться в индексируемом столбце.
q Полнотекстовый индекс можно создать и другим способом. Для этого в разделе Tables следует щелкнуть правой кнопкой мыши по пункту меню Full — Text index | Define Full — Text index . . Данный пункт меню будет доступен при условии, что для таблицы полнотекстовый индекс не был ранее создан. В противном случае вы можете выбрать пункт меню Full — Text index | Properties , чтобы изменить параметры уже существующего индекса. При создании нового индекса в вашем распоряжении будет мастер создания полнотекстовых индексов ( Full — Text indexing wizard ).
Процесс создания и поддержка полнотекстового индекса называется заполнением XE «Заполнение» ( population XE » Population » ). SQL Server поддерживает следующие типы заполнения индекса.
q Полное заполнение XE «Полное заполнение» ( Full Population XE » Full Population » ). Данный тип заполнения индекса осуществляется при его создании. Предполагается, что в дальнейшем его поддержание осуществляется другими типами заполнения.
q Заполнение на основе отслеживания изменений XE «Заполнение на основе отслеживания изменений» ( Change Tracking Based Population XE » Change Tracking Based Population » ). Для тех индексированных таблиц, где установлен данный тип заполнения, SQL Server поддерживает запись измененных строк. Записанные изменения затем переносятся в полнотекстовый индекс. Замечу, что данный тип заполнения будет работать, если предварительно уже было произведено заполнение индекса. Если вы используете данный тип заполнения, следует указать, каким образом эти изменения будут переноситься в индекс:
· автоматический перенос — сервер сам переносит изменения, после их появления;
· ручной перенос — администратор должен время от времени обновлять индекс;
· перенос по расписанию — можно создать расписание, и SQL Server Agent будет периодически, по расписанию, производить обновление.
q Инкрементное заполнение XE «Инкрементное заполнение» на основе версии строки ( Incremental Timestamp Based Population XE » Incremental Timestamp Based Population » ). Для того чтобы использовать данный тип заполнения, в таблице должен быть столбец с типом timestamp XE » Timestamp » . Для запуска этого типа заполнения используем пункт контекстного меню Full — Text Index | Start Incremental Population , щелкнув предварительно по строке с именем таблицы в разделе Tables в окне Object Explorer .