Планы запросов
Перед выполнением запроса комплект программ подготовки - известный как оптимизатор- начинает анализировать столбцы и операции запроса для вычислен? самого быстрого способа выполнения. Подготовка начинается с просмотра индексов таблицы и используемых столбцов. Работая таким образом с последовательностью путей решения (каждый из которых имеет свою "стоимость"), оптимизатор создает план - некий вид "дорожной карты" того пути, по которому сервер будет следовать при выполнении запроса. Конечный план выбирается по критерию "самой дешевой" дороги, оцениваемой в соответствии с индексами, которые могут быть использованы.
План оптимизатора может быть просмотрен в isql двумя способами.
* По умолчанию isql не отображает план. Используйте SET PLAN ON для отображения плана в самом начале вывода запроса SELECT.
* Используйте SET PLANONLY для рассмотрения запроса и просмотра плана без фактического выполнения запроса. Это позволяет вам анализировать план любого запроса, а не только запросов SELECT.
Можно перекрыть план запроса оптимизатора вашим собственным планом, включив предложение PLAN в оператор запроса. Большинство инструментов графического интерфейса сторонних разработчиков обеспечивают возможность просматривать план, выполняя или не выполняя запрос, и перекрывать его.
! ! !
СОВЕТ. Не перекрывайте план оптимизатора, пока вы не протестировали ваш собственный и не убедились, что он выполняется быстрее на реальных данных.
. ! .
Более подробную информацию о планах запроса см. в разд. "Тема оптимизации" главы 20. Подробности использования isql см. в главе 37.
Как могут помочь индексы
Если оптимизатор принимает решение использовать индекс, он отыскивает страницы индекса для поиска требуемых значений ключа и использует указатель для локализации выбранных строк на страницах данных этой таблицы. Поиск данных выполняется быстро, потому что значения индекса упорядочены. Это позволяет системе локализовать нужные значения напрямую по указателю и полностью исключает просмотр ненужных строк. Обычно использование индекса требует чтения меньшего количества страниц, чем "прогулка по" всем строкам в таблице. Индекс по размеру мал по сравнению с размером строки в таблице и, если было выполнено хорошее проектирование индекса, занимает меньшее количество страниц базы данных, чем строки таблицы.
Сортировка и группирование
Когда столбцы, указанные в предложениях ORDER BY или GROUP BY, являются индексированными, оптимизатор может упорядочить выходные данные, просматривая индексы, и собирать упорядочиваемые наборы быстрее, чем без использования индексов.
Убывающий индекс для группы столбцов может увеличить скорость выполнения запросов для агрегатной (обобщающей) функции мах(), потому что получение строки с максимальным значением требует только одного обращения. Информацию об использовании функциональных выражений в запросах см. в главах 21 и 23.
Соединения
Для соединений оптимизатор выполняет процесс слияния потоков данных на основании соответствия значений, явно или неявно указанных в критерии ON. Если какой- нибудь индекс доступен для столбца или столбцов на одной стороне соединения, оптимизатор создает свой начальный поток, используя этот индекс для соответствия ключа соединения с его корреспондентом из таблицы другой стороны соединения. При отсутствии индекса на любой из сторон он должен сначала создать образ одной стороны, а затем просматривать его с целью выборки из таблицы другой стороны соединения.
Сравнения
Когда сравнивается индексированный столбец для определения, является ли его значение больше, равно или меньше значения константы, то значение индекса используется в таком сравнении, и несоответствующие строки не выбираются. При отсутствии индекса все строки-кандидаты будут читаться и сравниваться последовательно.
Что индексировать
Величина времени, затрачиваемая на поиск во всей таблице, прямо пропорциональна количеству строк в таблице. Индекс для столбца может означать разницу между немедленным ответом на запрос и долгим ожиданием. Так почему бы не индексировать каждый столбец?
Основные препятствия этому состоят в том, что индексы занимают дополнительное дисковое пространство, а добавление, удаление и изменение строк занимает больше времени для индексированных столбцов, чем для не индексированных. Индекс должен быть изменен каждый раз, когда изменяется элемент данных в индексированном столбце, и каждый раз, когда строка добавляется в таблицу или удаляется из таблицы.
Тем не менее повышение производительности поиска данных обычно является более важным, чем накладные расходы по поддержке требующих дополнительных ресурсов, но полезных коллекций индексов. Вы должны создавать индекс для столбца, когда:
* условие поиска часто ссылается на столбец (Индекс поможет в поиске дат и чисел, когда ожидается прямое сравнение или вычисление BETWEEN. Поисковые индексы для строковых столбцов полезны, когда строки проверяются на точное соответствие или в предикатах STARTING WITH и CONTAINING. Они не годятся для предиката LIKE[52].);
* столбец не включен в ограничение целостности, но на него часто ссылается условие в JOIN;
* предложение ORDER BY часто использует столбец для сортировки данных (Когда набор данных должен быть упорядочен по нескольким столбцам, составной индекс, соответствующий порядку, указанному в предложении ORDER BY, может увеличить скорость поиска[53].);
* вам нужен индекс со специфическими характеристиками, не предоставляемыми данными или существующими индексами, такими как недвоичная сортировка, убывающая или возрастающая упорядоченность;
* производится группировка больших наборов записей (Индексы из одного столбца или подходящим образом упорядоченные составные индексы могут увеличить скорость группировки, условия которой заданы в сложном предложении GROUP BY.).
Вы не должны использовать индексы для столбцов, которые:
* редко используются в условиях поиска;
* являются часто изменяемыми неключевыми значениями, такими как значение времени или идентификация пользователя;
* имеют небольшое количество возможных или фактических значений в большом количестве строк;
* представляют собой двухзначное или трехзначное логическое значение.
Когда индексировать
Некоторые индексы сами заявят о себе в начальном периоде проектирования - обычно через известные вам требования сортировки, группировки, вычислений. Очень хорошей практикой является консервативный подход к созданию индексов: не создавать их, пока не станет ясным их польза. Является хорошей практикой отложить создание сомнительных индексов до того момента в разработке, когда у вас появится хороший набор тестовых данных и сведения о том, какие операции слишком медленные.
Преимущества отложенного проектирования индексов:
* уменьшение "завуалированное(tm) производительности", на которую может накладываться тестирование функциональной полноты;
* более быстрая идентификация реальных источников узких мест;
* исключение ненужного или неэффективного индексирования.
Использование CREATE INDEX
Оператор CREATE INDEX создает индекс из одного или более столбцов таблицы. Индекс из одного столбца отыскивает только один столбец в ответ на запрос, в то время как индекс из нескольких столбцов отыскивает один или более столбцов.
Синтаксис:
CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]]
INDEX имя-индекса ON имя-таблицы (столбец [, столбец ...]);
Обязательные элементы
52
В случаях, когда условие поиска задает LIKE ' string% ', оптимизатор обычно преобразовывает его к предикату STARTING WITH 1 string1 и использует индекс, если он доступен.
53
Составные индексы не являются столь важными в Firebird, как в большинстве других СУБД. Часто их использование является неоправданным, потому что Firebird интеллектуально использует индексы из одного столбца.