Галерея
7757 8119 8300 8698 8817 9504 9722 9937
Интересные записи
Топовые новости
Новое

Инструкция CREATE/ALTER INDEX

Индексы — это специальные объекты, которые надстраиваются над таблицами и ускоряют многие операции по манипулированию данными, например выполнение инструкций SELECT, UPDATE и DELETE. Избирательность действия данного предложения WHERE и имеющиеся планы обработки запросов, из которых оптимизатор запросов к базе данных может делать свой выбор, обычно основываются на качестве индексов, которые были построены для данной таблицы и помещены в базу данных.

Команда CREATE INDEX не входит в стандарт ANSI SQL, и, следовательно, ее реализации у разных производителей значительно различаются.

Ключевые слова

CREATE [UNIQUE] INDEX имя_индекса

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

ON имя_та6лицы

Объявляет, с какой таблицей связывается индекс. Индекс зависит от таблицы. Если таблица удаляется, то же происходит и с индексом.

(имя_столбца [, …])

Определяется один или несколько столбцов таблицы, которые будут индексироваться. Указатели, полученные из проиндексированного столбца или столбцов, позволяют оптимизатору запросов базы значительно увеличить скорость манипуляций с данными, например инструкций SELECT или DELETE. Все крупные производители поддерживают составные индексы (composite index), иначе называемые сцепленными индексами (concatenated index). Эти индексы используются в тех случаях, когда лучше всего проводить поиск по двум столбцам как по единому целому, например по фамилии и имени.


Общие правила

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

  • определение значений MINQ или MAXQ по индексированному столбцу;
  • сортировку и группировку столбцов таблицы;
  • поиск типа IS NULL или IS NOT NULL;
  • более быстрое извлечение данных, когда нужны только проиндексированные данные.

Инструкция SELECT, которая извлекает данные из индекса, а не напрямую из самой таблицы, называется покрывающим запросом (covering query). Индекс, который отвечает на подобный запрос, называется покрывающим индексам (covering index).

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

CREATE INDEX ndx_ord_date ON sales (ord_date);

В другом случае нам нужно создать уникальный индекс по столбцам pubname и country таблицы publishers.

CREATE UNIQUE INDEX unq_pub_id ON publishers (pubjiame, country)

Поскольку индекс является уникальным, любая новая запись, вводимая в таблицу publishers, должна иметь уникальное сочетание имени издателя (pubjiame) и страны (country).

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

При создании индекса место, занимаемое таблицей, может увеличиться в 1.2- 1.5 раза. Убедитесь, что у вас достаточно дискового пространства. Большая часть этого пространства после создания индекса высвобождается.

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


Советы и хитрости программирования

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

Провести обновление всех зависимых индексов, внеся туда изменившееся значение из таблицы. На практике для одной таблицы можно создать самое большее 6-12 индексов.

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

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

Источник