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

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

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

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

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

Оптимизатор - это совокупность механизмов, которые разрабатывают таи выполнения запроса. Когда пользователь передает InterBase какой-либо SQL-запрос, он указывает, ЧТО должен вернуть сервер в результате выполнения запроса, но не определяет, КАК сервер должен выполнять запрос. Оптимизатор на основе переданного запроса строит план его выполнения, т. е. откуда и в каком порядке будут браться данные для выполнения запроса, какие индексы будут при этом использоваться. Когда сервер анализирует условия на выборку (это в основном части выражения WHERE, ORDER BY и т. д.), то для каждого поля, входящего в условие, сервер пытается использовать индекс. К сожалению, алгоритм создания плана несовершенен и оптимизатор часто использует индексы, которые не слишком эффективны для конкретного запроса, из-за чего может существенно замедлиться время выполнения. Поэтому создание лишних индексов может привести к созданию неоптимальных планов.

Надо отметить, что в клоне Yaffil эта проблема разрешена за счет использования современных алгоритмов построения пчанов.

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

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

* Когда это поле используется в условиях поиска в запросах.

* Когда соединения таблиц (JOIN) используют это поле.

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

Как видно из описания синтаксиса, индекс может содержать не одно, а несколько полей. Такой индекс используется при часто выполняющихся запросах, которые содержат в условиях поиска или сортировки сочетание индексированных полей. Например, если у нас есть таблица, содержащая поля Фамилия, Имя, Отчество, то при запросе, использующем сортировку по ФИО, будет применен 1акой индекс. Вообще говоря, необязательно вводить условия на все 3 поля, применяемые в индексе, чтобы использовать его преимущества. Если мы желаем сортировать результат запроса, то индекс будет использован в случае, если первое поле в условии сортировки совпадает с первым полем в индексе, например наш индекс будет задействован в случае сортировки по Фамилии и Имени.

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

К вопросу о порядке сортировки индекса: как видно, он может быть либо возрастающим (ASQENDING]), либо убывающим (DESCENDING]). Зачем нужны разные порядки сортировки? Очевидно, для разных сортировок! Если мы желаем сортировать людей по фамилии в возрастающем порядке, то создаем возрастающий индекс (ASC), а если в убывающем (от Я до А) - то убывающий! А если хотим и то и другое, то необходимо создавать оба индекса.

Поделитесь на страничке

Следующая глава >

Похожие главы из других книг

Поиск с помощью языка операторов запросов поисковых систем как инструмент раскрутки блога

Из книги Блог. Создать и раскрутить автора Ющук Евгений Владимирович

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


4.1.3 Освобождение индексов

Из книги Архитектура операционной системы UNIX автора Бах Морис Дж

4.1.3 Освобождение индексов В том случае, когда ядро освобождает индекс (алгоритм iput, Рисунок 4.4), оно уменьшает значение счетчика ссылок для него. Если это значение становится равным 0, ядро переписывает индекс на диск в том случае, когда копия индекса в памяти отличается от


Тестирование запросов с помощью компонента Server Explorer

Из книги Обработка баз данных на Visual Basic®.NET автора Мак-Манус Джеффри П

Тестирование запросов с помощью компонента Server Explorer Компонент Server Explorer среды Visual Studio .NET — это полезный инструмент для опробования концепций, описанных в этой главе. С помощью перечисленных ниже действий создайте тестовое представление данных в окне компонента Server


Создание индексов с помощью команды CREATE INDEX

Из книги Руководство администратора баз данных Informix. автора Кустов Виктор

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


Удаление таблиц и индексов с помощью предложения DROP

Из книги Курс "Язык программирования PHP" автора Савельева Нина Владимировна

Удаление таблиц и индексов с помощью предложения DROP Удалять элементы базы данных можно с помощью предложения DROP. Например, чтобы удалить таблицу, используйте приведенную ниже команду SQL.DROP TABLE tblRegionС помощью предложения DROP можно также удалить индекс в таблице, как


2.2.6 Оптимизатор выполнения запросов по стоимости

Из книги Windows Vista. Трюки и эффекты автора Зозуля Юрий

2.2.6 Оптимизатор выполнения запросов по стоимости Оптимизатор запросов определяет наиболее оптимальный с точки зрения затрат системных ресурсов план реализации каждого запроса к базе данных. Учитывается число обменов с диском, затраты разделяемой памяти, затраты на


Обработка запросов с помощью PHP

Из книги Microsoft Access 2007 автора Днепров Александр Г.

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


10.5. Автоматизация выполнения заданий с помощью планировщика

Из книги Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ автора Борри Хелен

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


Глава 6 Изменение и анализ данных с помощью запросов

Из книги Мир InterBase. Архитектура, администрирование и разработка приложений баз данных в InterBase/FireBird/Yaffil автора Ковязин Алексей Николаевич

Глава 6 Изменение и анализ данных с помощью запросов Запросы на изменениеСоздание таблиц с помощью запросаСоздание запросов на добавление данныхСоздание запросов на обновление таблицСоздание запросов на удаление записейАнализ данных с помощью запросовУдаление


Анализ данных с помощью запросов

Из книги Linux и UNIX: программирование в shell. Руководство разработчика. автора Тейнсли Дэвид

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


Удаление повторяющихся записей с помощью запросов

Из книги автора

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


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

Из книги автора

Просмотр индексов Для просмотра всех индексов, определенных в текущей базе данных, используйте в isql команду SHOW INDEX:* чтобы просмотреть все индексы, определенные для конкретной таблицы, используйте команду:SHOW INDEX имя-таблицы;* для просмотра информации конкретного индекса


Обеспечение ссылочной целостности с помощью индексов

Из книги автора

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


18.8. Управление ходом выполнения циклов с помощью команд break и continue

Из книги автора

18.8. Управление ходом выполнения циклов с помощью команд break и continue Иногда в процессе работы возникает необходимость в прерывании или пропуске отдельных итераций цикла. При этом применяются определенные критерии. Для обеспечения подобных возможностей интерпретатор shell


28.3. Ускорение работы с помощью файла inittab

Из книги автора

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