Создание и управление полнотекстовыми индексамиcreate and manage full-text indexes

Первичные ключи

Первичный ключ (Primary Key) — это особый тип индекса, который является идентификатором записей в таблице. Он обязательно уникальный и указывается при создании таблиц:

При использовании таблиц InnoDB всегда определяйте первичные ключи. Если первичного ключа нет, MySQL все равно создаст виртуальный скрытый ключ.

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

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

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

Первичные ключи таблиц InnoDB являются кластерными. Поэтому выборки по ним происходят очень эффективно.

Виды индексов Oracle Database

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

• Уникальные и неуникальные индексы. Уникальные индексы основаны на уникальном столбце — обычно вроде номера карточки социального страхования сотрудника. Хотя уникальные индексы можно создавать явно, Oracle не рекомендует это делать. Вместо этого следует использовать уникальные ограничения. Когда накладывается ограничение уникальности на столбец таблицы, Oracle автоматически создает уникальные индексы по этим столбцам.
• Первичные и вторичные индексы. Первичные индексы — это уникальные индексы в таблице, которые всегда должны иметь какое-то значение и не могут быть равны null. Вторичные индексы — это прочие индексы таблицы, которые могут и не быть уникальными.
• Составные индексы. Составные индексы — это индексы, содержащие два или более столбца из одной и той же таблицы. Они также известны как сцепленные индексы (concatenated index). Составные индексы особенно полезны для обеспечения уникальности сочетания столбцов таблицы в тех случаях, когда нет уникального столбца, однозначно идентифицирующего строку.

Индексы и ключи

Часто можно встретить взаимозаменяемое употребление терминов “индекс” и “ключ”. Тем не менее, эти две сущности на самом деле отличаются друг от друга. Индекс — это физическая структура, хранящаяся в базе данных. Индекс можно создавать, изменять и уничтожать; в основном он служит для ускорения доступа к данным таблицы. С другой стороны, ключи — полностью логическая концепция. Ключи, с другой стороны, являются чисто логическим концепциями. Они представляют ограничения целостности, создаваемые для реализации бизнес-правил. Путаница между индексами и ключами обычно возникает потому, что база данных часто использует индекс для обеспечения ограничения целостности. Просто помните, что эти две вещи — не одно и то же.

Руководство по созданию индексов

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

• Индексация имеет смысл, если нужно обеспечить доступ одновременно не более чем к 4–5% данных таблицы. Альтернативой использованию индекса для доступа к данным строки является полное последовательное чтение таблицы от начала до конца, что называется полным сканированием таблицы. Полное сканирование таблицы больше подходит для запросов, которые требуют извлечения большего процента данных таблицы. Помните, что применение индексов для извлечения строк требует двух операций чтения: индекса и затем таблицы.
• Избегайте создания индексов для сравнительно небольших таблиц. Для таких таблиц больше подходит полное сканирование. В случае маленьких таблиц нет необходимости в хранении данных и таблиц, и индексов.
• Создавайте первичные ключи для всех таблиц. При назначении столбца в качестве первичного ключа Oracle автоматически создает индекс по этому столбцу.
• Индексируйте столбцы, участвующие в многотабличных операциях соединения.
• Индексируйте столбцы, которые часто используются в конструкциях WHERE.
• Индексируйте столбцы, участвующие в операциях ORDER BY и GROUP BY или других операциях, таких как UNION и DISTINCT, включающих сортировку. Поскольку индексы уже отсортированы, объем работы по выполнению необходимой сортировки данных для упомянутых операций будет существенно сокращен.
• Столбцы, состоящие из длинно-символьных строк, обычно плохие кандидаты на индексацию.
• Столбцы, которые часто обновляются, в идеале не должны быть индексированы из-за связанных с этим накладных расходов.
• Индексируйте таблицы только с высокой селективностью. То есть индексируйте таблицы, в которых мало строк имеют одинаковые значения.
• Сохраняйте количество индексов небольшим.
• Составные индексы могут понадобиться там, где одностолбцовые значения сами по себе не уникальны. В составных индексах первым столбцом ключа должен быть столбец с максимальной селективностью.

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

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

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

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

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

Теперь посмотрим, как создавать индексы вручную. До этого момента мы использовали индексы, которые сервер создавал автоматически для первичного ключа и уникального поля. Сервер SQL автоматически создает индекс, когда создается ограничение PRIMARY KEY или UNIQUE, но бывает необходимость создать индекс на поле без этих ограничений.

Для создания индекса на произвольное поле используется оператор CREATE INDEX, а для удаления используется DROP INDEX. Вы должны быть владельцем базы данных или администратором, чтобы выполнять эти операторы.

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

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

В общем виде команда создания индекса выглядит следующим образом:

CREATE   INDEX index_name 
    ON { table | view } ( column   ) 
 ] 

Чтобы удобнее было понять команду, я разбиваю ее на строчки. В первой строке указывается ключевые слова 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' 

В следующем примере удаляется созданный нами ранее индекс:

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, то этот запрос еще не понятен для вас, но вернитесь к нему после прочтения второй главы, и все встанет на свои места.

Мониторинг использования индекса

Oracle предлагает инструменты EXPLAIN PLAN и SQL Trace, которые помогают увидеть путь, проходимый запросом перед его выполнением. Вывод команды EXPLAIN PLAN и результаты SQL Trace позволяют увидеть путь выполнения запроса и определить, использует ли он индексы.

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

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

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

SQL> ALTER INDEX p_key_sales MONITORING USAGE;
Index altered.
SQL>

Теперь запустите какие-нибудь запросы к таблице sales. Завершите мониторинг,применив следующую команду:

SQL> ALTER INDEX p_key_sales NOMONITORING USAGE;
Index altered.
SQL> 

После этого можно запросить представление словаря данных V$OBJECT_USAGE для определения того, использовался ли индекс p_key_sales. Следующий результат подтверждает использование индекса:

SQL> SELECT * FROM v$object_usage
WHERE index_name='P_KEY_SALES';
INDEX_NM    TAB_NM MON  USED  START_MON            END_MONITORING
----------  ------ ---- ----- ------------------   --------------------
P_KEY_SALES SALE   NO   YES   01/23/2008 06:20:45  01/23/2008 06:40:22

В приведенном выводе Oracle выводит значение YES в столбце USED, указывая на то,что интересующий индекс использовался базой данных. Если индекс был проигнорирован во время мониторинга, столбец содержал бы значение NO. Причина, по которой нельзя узнать количество случаев использования индекса, связана с тем, что база данных выполняет мониторинг его использования только на фазе разбора (parsing); если бы разбор производился при каждом выполнении, пострадала бы производительность.

ALTER Command to add and drop the PRIMARY KEY

You can add a primary key as well in the same way. But make sure the Primary Key works on columns, which are NOT NULL.

The following code block is an example to add the primary key in an existing table. This will make a column NOT NULL first and then add it as a primary key.

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

You can use the ALTER command to drop a primary key as follows −

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

To drop an index that is not a PRIMARY KEY, you must specify the index name.

Displaying INDEX Information

You can use the SHOW INDEX command to list out all the indexes associated with a table. The vertical-format output (specified by \G) often is useful with this statement, to avoid a long line wraparound −

Try out the following example −

mysql> SHOW INDEX FROM table_name\G
........

Previous Page
Print Page

Next Page  

Схемы индексации Oracle

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

В реализации индексов на основе B-деревьев используется концепция сбалансированного (на что указывает буква “B” (balanced)) дерева поиска в качестве основы структуры индекса. В Oracle имеется собственный вариант B-дерева, именуемый “B*tree”. Это обычные индексы, создаваемые по умолчанию, когда вы применяете оператор CREATE INDEX. Термин “индекс B*tree” обычно не используется, когда речь идет об обычных индексах Oracle — они называются просто “индексами”.

Индексы на основе B-деревьев структурированы в форме обратного дерева, где блоки верхнего уровня называются блоками ветвей (branch blocks), а блоки нижнего уровня — листовыми блоками (leaf blocks). В иерархии узлов все узлы кроме вершины, или корневого узла, имеют родительский узел и могут иметь ноль или более дочерних узлов. Если глубина древовидной структуры, т.е. количество уровней, одинакова от каждого листового блока до корневого узла, то такое дерево называется сбалансированным, или B-деревом.

B-деревья автоматически поддерживают необходимый уровень индекса по размеру таблицы. B-деревья также гарантируют, что индексные блоки всегда будут заполнены не меньше, чем наполовину, и менее, чем на 100%. B-деревья допускают операции выборки, вставки и удаления с очень небольшим количеством операций ввода-вывода на один оператор. Большинство B-деревьев имеет всего три и менее уровней. При использовании B-дерева нужно читать только блоки B-дерева, так что количество операций ввода-вывода будет ограничено числом уровней B-дерева (скажем, тремя) плюс две операции ввода-вывода на выполнение обновления или удаления (одна для чтения и одна для записи). Для выполнения поиска по B-дереву понадобится всего три или менее обращений к диску.

Реализация B-дерева от Oracle — B*tree — всегда сохраняет дерево сбалансированным. Листовые блоки содержат по два элемента: индексированные значения столбца и соответствующий идентификатор ROWID для строки, которая содержит это значение столбца. ROWID — уникальный указатель Oracle, идентифицирующий физическое местоположение строки и обеспечивающий самый быстрый способ доступа к строке в базе данных Oracle. Сканирование индекса быстро дает ROWID строки, и отсюда можно быстро получить к ней доступ непосредственно. Если запрос нуждается лишь в значении индексированного столбца, то конечно, последний шаг исключается, поскольку извлекать дополнительные данные, кроме прочитанных из индекса, не потребуется.

Creating a Function-Based Index

Function-based indexes facilitate queries that qualify a value returned by a function or expression. The value of the function or expression is precomputed and stored in the index.

In addition to the prerequisites for creating a conventional index, if the index is based on user-defined functions, then those functions must be marked . Also, you just have the object privilege on any user-defined function(s) used in the function-based index if those functions are owned by another user.

Additionally, to use a function-based index:

• The table must be analyzed after the index is created.

• The query must be guaranteed not to need any values from the indexed expression, since values are not stored in indexes.

  Note:

  stores the timestamp of the most recent function used in the function-based index. This timestamp is updated when the index is validated. When performing tablespace point-in-time recovery of a function-based index, if the timestamp on the most recent function used in the index is newer than the timestamp stored in the index, then the index is marked invalid. You must use the statement to validate this index.

To illustrate a function-based index, consider the following statement that defines a function-based index () defined on the function :

CREATE INDEX area_index ON rivers (area(geo));

In the following SQL statement, when is referenced in the clause, the optimizer considers using the index .

SELECT id, geo, area(geo), desc
     FROM rivers     
     WHERE Area(geo) >5000;

Table owners should have privileges on the functions used in function-based indexes.

Because a function-based index depends upon any function it is using, it can be invalidated when a function changes. If the function is valid, you can use an statement to enable a function-based index that has been disabled. The statement lets you disable the use of a function-based index. Consider doing this if you are working on the body of the function.

Note:

An alternative to creating a function-based index is to add a virtual column to the target table and index the virtual column. See for more information.

Creating a Unique Index Explicitly

Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique indexes do not impose this restriction on the column values.

Use the statement to create a unique index. The following example creates a unique index:

CREATE UNIQUE INDEX dept_unique_index ON dept (dname)
      TABLESPACE indx;

Alternatively, you can define integrity constraints on the desired columns. The database enforces integrity constraints by automatically defining a unique index on the unique key. This is discussed in the following section. However, it is advisable that any index that exists for query performance, including unique indexes, be created explicitly.

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

Индекс создается с помощью оператора CREATE INDEX, как показано ниже:

SQL> CREATE INDEX employee_id ON employee(employee_id)
TABLESPACE emp_index_01; 

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

SQL> CREATE INDEX employee_id ON employee(employee_id)
TABLESPACE emp_index_01
COMPUTE STATISTICS;

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

По умолчанию Oracle допускает дублированные значения в столбцах индекса, которые также называются ключевыми столбцами. Однако можно специфицировать уникальный индекс, что исключит дублирование значений столбца в нескольких строках. Для создания уникального индекса служит оператор CREATE UNIQUE INDEX: 

SQL> CREATE UNIQUE INDEX employee_id ON employee(employee_id)
TABLESPACE emp_index_01;

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

SQL> CREATE INDEX employee_id ON employee(employee_id,location_id)
TABLESPACE emp_index_01;

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

В первом случае задается уникальное ограничение на двух столбцах: dept_name и location.

SQL> CREATE TABLE dept(
dept_no NUMBER(3),
dept_name VARCHAR2(15),
location VARCHAR2(25),
CONSTRAINT dept_name_ukey UNIQUE(dept_Name,location);

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

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

SQL> CREATE TABLE employee (
empno NUMBER (5) PRIMARY KEY, age INTEGER)
ENABLE PRIMARY KEY USING INDEX
TABLESPACE users;

Приведенный выше оператор CREATE TABLE включает ограничение первичного ключа, которое автоматически создает уникальный индекс на столбце empno.

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

SQL> ALTER TABLE employee ADD CONSTRAINT test_const1
PRIMARY KEY (pkey1) USING INDEX ind1;

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

SQL> CREATE TABLE employee (
emp_id INT PRIMARY KEY USING INDEX (create index ind1
ON employee (emp_id)))

Применение оператора CREATE INDEX в этом примере обеспечивает более тонкий контроль над созданием индекса для указанного ограничения первичного ключа.