연락처
IBSurgeon
이 페이지는 기계 번역되었습니다. 영어 원본을 읽어보세요. English

IBSurgeon 라이브러리

인덱스 (InterBase 및 Firebird)

Alexey Kovyazin, последнее обновление 07-сен-2005

Концепция, положенная в основу индексов, проста и наглядна и является одной из важнейших основ проектирования баз данных. На основе индексов строятся многие базовые объекты базы данных, а кроме того, правильное использование индексов - ключ к повышению производительности приложений баз данных. Однако что такое индекс? Индекс - это упорядоченный указатель на записи в таблице. Указатель означает, что индекс содержит значения одного или нескольких полей таблицы и адреса страниц данных, где эти значения расположены (подробнее о страницах данных см. главу «Структура базы данных InterBase») (часть 4). Другими словами, индекс состоит из пар значений «значение поля» - «физическое расположение этого поля».

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

Зачем нужны индексы?

Единственное, чему способствуют индексы, - это ускорение поиска записей по индексируемому полю (индексируемое - означает включенное в индекс). Основная функция индексов - обеспечить быстрый поиск записей в таблице. Любое использование индекса сводится к этому.

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

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

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

Как они организованы?

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

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

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

Разделение хранения данных и их представления дает дополнительные преимущества по сравнению с прямой сортировкой - возможно, вам понадобится сортировать исходную таблицу разными способами. Тогда вам помогут индексы - для каждой таблицы может быть до 64 индексов!

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

Применение индексов

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

  1. Ускорение выполнения запросов. Индексы создаются для полей, используемых в условиях поиска SQL-запросов.

  2. Поддержка уникальности значений в полях; ограничение первичного ключа (о котором говорилось в главе «Таблицы. Первичные ключи») требует, чтобы в таблице не было двух одинаковых значений полей, входящих в первичный ключ. Чтобы выполнить это условие, при вставке новой записи следует искать то же значение, которое будет вставлено. Для поиска записей используется особая разновидность индекса - уникальный индекс (см. ниже).

  3. Поддержка ссылочной целостности. Ограничения внешних ключей (которые рассматриваются в главе «Ограничения базы данных») используются для проверки того, что значения, вставленные в таблицу, обязательно существуют в другой таблице. При создании внешнего ключа автоматически создается индекс. Этот индекс применяется для ускорения запросов, использующих соединение таблиц, а также для проверки условий внешнего ключа. Мы кратко рассмотрели все возможные применения индексов. Теперь рассмотрим особенности каждого случая более подробно и ответим на наиболее часто возникающие вопросы, касающиеся применения индексов.

Ускорение выполнения запросов с помощью индексов

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

Второй момент более важен. Это затраты при изменении данных в таблице. В реляционной СУБД, как известно, записи в таблицах неупорядочены, и поэтому добавление/удаление записей происходит без значительных затрат ресурсов сервера. Даже если запись удаляется из середины базы данных, нет перемещения объемов данных для заполнения этой пустоты - это не требуется: сервер просто пометит пустое место и запишет туда что-нибудь при необходимости. Что касается добавления, в большинстве случаев оно выполняется в конец таблицы. Однако, хотя сервер не перемещает основные данные в таблице при изменении, данные, хранящиеся в индексах, переупорядочиваются каждый раз при добавлении/удалении записей! Другими словами, серверу приходится перестраивать индекс при добавлении записи в середину таблицы. Конечно, реализация индекса так или иначе предназначена для частых реорганизаций, но эти операции тем не менее занимают время и ресурсы процессора, и при большом количестве индексов в таблице изменение данных в ней может быть намного медленнее, чем в той же таблице без индексов!

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

옵티마이저는 쿼리 실행 일정을 개발하는 메커니즘의 집합입니다. 사용자가 InterBase에 SQL 쿼리를 제공할 때, 사용자는 서버가 쿼리 실행 후 무엇을 반환해야 하는지 지정하지만, 서버가 쿼리를 어떻게 수행해야 하는지는 정의하지 않습니다. 옵티마이저는 주어진 쿼리를 기반으로 실행 일정을 생성합니다. 즉, 쿼리 실행을 위한 데이터를 어디서, 어떤 순서로 가져올지, 그때 어떤 인덱스를 사용할지 결정합니다. 서버가 검색 조건(주로 WHERE, ORDER BY 등의 표현식 부분)을 분석할 때, 조건에 포함된 모든 필드에 대해 서버는 인덱스를 사용하려고 시도합니다. 불행히도, 일정 생성 알고리즘은 불완전하며 옵티마이저는 특정 쿼리에 대해 그다지 효과적이지 않은 인덱스를 자주 사용하므로 실행 시간이 상당히 느려질 수 있습니다. 따라서 불필요한 인덱스 생성은 비최적 일정을 만들 수 있습니다.

Yaffil 클론에서는 현대적인 일정 생성 알고리즘을 사용하여 이 문제가 해결되었음을 주목해야 합니다. 인덱스가 필요하지 않은 세 번째 경우는 값 집합이 제한된 필드입니다. 예를 들어, 사람의 성별 정보를 저장하고 “F"와 “M” 두 가지만 포함하는 필드가 있다면 이 필드를 인덱싱할 필요가 없습니다. 이제 인덱스 생성의 주요 제약 조건을 살펴보았습니다. 이제 생산성 향상을 위해 인덱스를 사용해야 하는 문제를 다루어야 합니다. 필드를 인덱싱해야 하는 주요 경우는 3가지입니다:

  • 이 필드가 쿼리의 검색 조건에서 사용될 때
  • 테이블 조인에서 이 필드를 사용할 때
  • 이 필드가 ORDER BY 정렬 문에서 사용될 때

위에서 언급한 방식으로 필드가 적용되면, 해당 필드에 대한 인덱스 생성은 쿼리 생산성 향상으로 이어질 수 있습니다.

인덱스 생성 구문을 살펴보겠습니다. 인덱스를 생성할 수 있는 DDL 명령의 전체 형식은 다음과 같습니다:

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX index ON table (col [, col …]);

인덱스를 생성하는 최소 표현식은 다음과 같습니다:

CREATE INDEX my_index ON Table_example(ID)

이 예제에서 my_index라는 이름의 인덱스가 Table_example 테이블에 생성되며, ID 필드가 인덱싱된 필드입니다. 인덱스는 오름차순입니다. 즉, 값이 오름차순으로 정렬되며, 비고유(non-unique)이기도 합니다. 이는 ID 필드에 여러 개의 동일한 값이 있을 수 있음을 의미합니다. 물론 이것은 가장 일반적인 인덱스의 가장 간단한 예입니다. 구문 설명에서 볼 수 있듯이 인덱스는 하나가 아닌 여러 필드를 포함할 수 있습니다. 이러한 인덱스는 검색 또는 정렬 조건에 인덱싱된 필드의 조합을 포함하는 쿼리가 자주 실행될 때 사용됩니다. 예를 들어, 성(Surname), 이름(Name), 부칭(Patronymic) 필드를 포함하는 테이블이 있다면, 이러한 인덱스는 성, 이름, 부칭으로 정렬을 사용하는 쿼리를 만들 때 적용됩니다. 일반적으로 인덱스의 이점을 활용하기 위해 인덱스에 적용된 3개 필드 모두에 대한 조건을 지정할 필요는 없습니다. 쿼리 결과를 정렬하려면, 정렬 조건의 첫 번째 필드가 인덱스의 첫 번째 필드와 일치하는 경우 인덱스가 사용됩니다. 예를 들어, 성과 이름으로 정렬하는 경우 우리의 인덱스가 적용됩니다.

문서에 따르면 WHERE 문에 OR 조건으로 필드 조인이 포함된 쿼리 실행 최적화를 위해, 집계 인덱스가 아닌 OR 조건에 포함된 모든 필드에 대한 여러 개의 단일 인덱스를 사용해야 합니다.

인덱스 정렬 순서에 관한 질문으로, 인덱스는 오름차순 또는 내림차순일 수 있습니다. 왜 다른 정렬 순서가 필요할까요? 당연히 다른 정렬을 위해서입니다! 성으로 사람들을 오름차순으로 정렬하려면 오름차순 인덱스(ASC)를 만들고, 내림차순(Z에서 A로)으로 정렬하려면 내림차순 인덱스를 만듭니다! 둘 다 원한다면 두 인덱스를 모두 만들어야 합니다.

인덱스를 사용한 참조 무결성 지원

인덱스 정의에는 UNIQUE라는 또 다른 옵션이 있습니다. 이를 지정하면 인덱스는 테이블에 고유한 값만 삽입할 수 있게 합니다. 실제로 이것은 고유 키 구현의 기초입니다. 고유 키는 데이터베이스에서 널리 사용됩니다. 즉, РК는 고유 키-인덱스이지만 모든 UK가 РК는 아닙니다. 우리는 위에서 РК에 대해서만 이야기했습니다. 기본 키는 고유 키의 가장 일반적으로 사용되는 유형입니다. 테이블에 기본 키를 생성하면 고유 인덱스가 자동으로 생성됩니다. 이 인덱스에는 RDB$PRIMARYNNN으로 구성된 이름이 부여되며, 여기서 NNN은 데이터베이스 내의 순차적 고유 번호입니다. 따라서 참조 무결성의 두 가지 주요 제약 조건인 고유 키와 기본 키는 고유 인덱스를 사용하여 실현됩니다. 고유성의 개념이 정의되지 않은 값의 개념과 양립할 수 없다는 것은 명백합니다. 즉, 고유 인덱스에 포함된 필드에는 NULL 유형의 값이 없어야 합니다. 필드에 고유 인덱스를 생성하기 전에 NOT NULL 제약 조건을 설정해야 합니다. 이미 존재하는 데이터에 대해 인덱스가 생성되는 경우, 생성 시 인덱싱된 필드에 반복 값이 포함되어 있는지 확인됩니다. 포함되어 있으면 인덱스 생성이 금지됩니다.

고유 키 및 기본 키 제약 조건 외에도 인덱스 메커니즘은 참조 무결성의 또 다른 제약 조건인 외래 키 구현의 기초가 됩니다. 외래 키 제약 조건은 모든 테이블의 하나 또는 여러 필드에 설정되며, 다른 부모 테이블의 기본 키에 포함되지 않은 값을 이러한 필드에 삽입하는 것을 방지합니다. 외래 키를 구현하기 위해, 즉 부모 테이블에 값이 있는지 확인하기 위해 특수 인덱스가 자동으로 생성됩니다. 그 이름은 RDB$FOREIGNNN이며, 여기서 NNN은 데이터베이스 내의 순차적 고유 번호입니다.

왜 참조 무결성 제약 조건을 구현하는 데 인덱스 메커니즘이 사용될까요? 문제는 InterBase의 인덱스가 특별하고 선호되는 위치에 있다는 것입니다. 즉, 트랜잭션 컨텍스트 밖에서 실행된다고 합니다. 이것은 매우 중요한 속성입니다. 트랜잭션에 대해서는 나중에 그에 관한 장에서 이야기하겠습니다. 지금은 인덱스가 트랜잭션 밖에 있을 때, 같은 테이블의 데이터로 동시에 작업하는 모든 사용자가 참조 무결성 제약 조건을 준수해야 함을 의미한다는 것만 언급하겠습니다.

인덱스 생산성 최적화

이 부분의 제목에서 우리는 어떤 역설을 발견할 수 있습니다. 인덱스는 위에서 말했듯이 쿼리 실행을 빠르게 하기 위한 것인데, 그것들도 최적화되어야 한다는 것이 밝혀졌습니다! 하지만 어떻게 해야 할까요(그것이 인생입니다) - 누군가는 인덱스를 관리해야 합니다. 인덱스에는 무엇이 발생할까요? 왜 “모양을 잃을"까요? 인덱스가 이진 트리로 구현된다는 것을 다시 한 번 말해야 합니다. 그리고 새 레코드가 테이블에 추가(업데이트, 삭제 - 원하는 대로)될 때, 트리에 새 분기가 추가됩니다. 이러한 분기는 트리 중간이 아니라 다른 분기의 꼭대기에 추가됩니다. 점차 트리는 점점 더 가지가 많아지고(또는 불균형해지며), 검색은 덜 효과적이 됩니다. 트리 재구성 또는 (일부 경우) 통계 재계산은 상황을 개선할 수 있습니다.

주기적으로 인덱스를 재생성하여 생산성을 복원해야 합니다. 인덱스 재생성은 다음 경우에 발생합니다:

  • ALTER INDEX 명령을 사용하여 인덱스를 재구축할 때.
  • DROP INDEX 및 CREATE INDEX 명령을 사용하여 인덱스를 삭제하고 재생성할 때.
  • gbak 도구를 사용하여 백업 및 백업 복사본에서 복원할 때.

또한 통계 재계산을 사용할 수 있습니다. 그러나 이 작업은 인덱스 상태를 변경하지 않으며, 옵티마이저에게 인덱스 상태에 대한 정확한 정보를 제공하여 이 인덱스를 올바르게 사용할 수 있게 한다는 것을 이해해야 합니다. 즉, 통계 재계산은 인덱스의 “치료"가 아니라 상태의 정확한 진단일 뿐입니다. 인덱스 최적화의 이러한 모든 방법을 더 자세히 살펴보겠습니다. ALTER INDEX 명령 사용은 다음 형식을 갖습니다:

ALTER INDEX name {ACTIVE | INACTIVE};

여기서 name은 인덱스 이름이고, ACTIVE와 INACTIVE는 ALTER INDEX 명령을 사용하여 변환할 수 있는 인덱스의 두 상태입니다. ACTIVE 매개변수는 인덱스가 활성 상태이며 모든 쿼리와 프로시저에 적용될 수 있음을 의미합니다. 인덱스를 INACTIVE로 설정하면 사용이 중단됩니다. 트리를 재배열하기 위해 두 명령을 순차적으로 실행해야 합니다:

markdown 
ALTER INDEX name INACTIVE; ALTER INDEX name ACTIVE;

따라서 인덱스가 재구축됩니다. ALTER INDEX 사용에는 몇 가지 제약이 있습니다: 기본 키, 고유 키 및 외래 키에 사용되는 인덱스는 재구축할 수 없습니다. 현재 어떤 쿼리에서도 사용 중인 인덱스는 재구축할 수 없습니다. 또한 인덱스를 변경하려면 관리자(SYSDBA) 권한이 있거나 해당 인덱스의 생성자여야 합니다.

DROP INDEX 및 CREATE INDEX 명령을 사용한 인덱스 재생성은 데이터베이스에서 인덱스를 완전히 삭제한 후, 빈 상태에서 다시 생성하는 방식입니다. DROP INDEX 명령의 구문은 명확합니다:

DROP INDEX 인덱스\_이름;

삭제 후에는 이미 살펴본 구문의 CREATE INDEX 명령을 사용하여 동일한 이름과 매개변수로 인덱스를 생성해야 합니다. 인덱스를 완전히 재생성하는 방식의 재구축 방법은 ALTER INDEX 사용 시의 제약과 유사한 제약이 있습니다.

인덱스 재구축의 세 번째 방법은 gbak 유틸리티로 생성된 InterBase 데이터베이스 백업 복사본의 속성에 기반합니다. 백업 시 인덱스에 포함된 데이터는 백업 복사본에 저장되지 않고 인덱스 정의만 저장됩니다. 백업 복사본에서 복원할 때 인덱스가 다시 생성됩니다. 백업에 대해 더 자세히 알고 싶다면 "백업 및 백업 복사본에서 복원" 장(4부)을 참조하십시오.

인덱스 생산성을 향상시키는 네 번째 방법은 SET STATISTICS 명령을 사용하여 인덱스에 대한 통계를 수집하는 것입니다. 테이블 통계는 테이블의 서로 다른 레코드 수에 따라 달라지는 0에서 1 사이의 값입니다. InterBase 옵티마이저는 통계를 사용하여 쿼리에서 특정 인덱스 적용의 효율성을 판단합니다. 테이블의 레코드 수가 크게 변할 수 있는 경우(예: 많은 수의 삽입 또는 삭제로 인해) 통계 재계산은 생산성을 상당히 향상시킬 수 있습니다. 통계 재계산 명령은 다음과 같습니다:

SET STATISTICS INDEX name;

여기서 name은 통계가 재계산되는 인덱스의 이름입니다. 통계 재계산은 인덱스를 재구축하지 않으므로 위에서 설명한 생산성 향상 방법에 설정된 대부분의 제약에서 자유롭지만, 인덱스 생성자 또는 시스템 관리자(SYSDBA라는 이름의 사용자)만 통계를 재계산할 수 있습니다. 올바른 통계는 옵티마이저가 인덱스 사용 여부에 대해 올바른 결정을 내릴 수 있게 합니다.

우리는 인덱스 생산성을 향상시키는 몇 가지 방법을 살펴보았습니다. ALTER INDEX 및 DROP/CREATE INDEX 명령을 사용하여 참조 무결성을 제공하기 위해 자동으로 생성된 시스템 인덱스를 제외한 모든 인덱스를 재구축할 수 있습니다. 이러한 인덱스를 재구축하려면 ALTER TABLE 및 CREATE TABLE 명령을 사용해야 합니다. 이러한 인덱스는 테이블 키의 필수적인 부분이기 때문입니다.