Крейг Штунц
Перевод: Ронсаль Е.Е.
Оригинал статьи: http://blogs.teamb.com/craigstuntz/articles/IBOptimization2.aspx
В то время как практически каждый разработчик может создавать запросы SQL, способность оптимизировать базу данных InterBase (и использовать это) позволит ощутить разницу между приемлемой производительностью и по-настоящему эффективным приложением.
Поскольку выражения PLAN могут быть достаточно краткими в простейших случаях и непонятными, когда запросы усложняются, я написал свободно распространяемую (freeware) утилиту InterBase PLANalyzer, которая графически отображает PLANы выполнения запроса InterBase, а также выдает информацию об индексах, используемых запросом и внутренних чтениях, производимых InterBase во время выполнения запроса. Эта статья описывает текстовый синтаксис PLAN, отображаемый такими средствами InterBase, как IBConsole и ISQL и графический план выдаваемый InterBase PLANalyzer. Пользуйтесь тем инструментом, который вам наиболее удобен.
Данная статья предполагает, что читатель обладает некоторым пониманием того, как написать хороший SQL запрос и не ставит задачи объяснить, как настроить серверной InterBase, но прежде чем приступать к оптимизации определенных запросов или таблиц, следует учесть эти два аспекта.
Тот факт, что InterBase может принимать PLANы как часть выражений DSQL, вводит в заблуждение многих разработчиков, пытающихся их использовать. Кроме того что PLANы запроса могут быть трудными для понимания – не говоря уже о написании – PLANы, в общем случае, плохой способ оптимизации запроса. Для более подробной информации см. раздел How InterBase Optimizes a Statement (Как оптимизируются запросы в InterBase).
Существует несколько способов помочь оптимизатору:
Давайте рассмотрим каждый из способов отдельно. Но в первую очередь необходимо понять, почему запрос выполняется медленно. Это позволит нам определить, какой из этих трёх способов поможет решить проблему.
Чтобы начать процесс оптимизации вам необходим следующее:
Примечание: Для всех примеров в этом тексте я буду использовать Employee.gdb. В таблице EMPLOYEE базы данных Employee.GDB – 42 записи, поэтому тяжело проследить изменения скорости выполнения, особенно если вы используете локальное соединение. Как я упоминал ранее, для оптимизации запросов важно работать с заполненной базой данных. Тем не менее, я настаиваю на использовании в этом документе Employee.GDB в качестве примеров, потому что хочу, чтобы читатели могли поэкспериментировать с запросами, которые я использую. К тому же, использование базы данных в 2 Гб в качестве примера, сделало бы распространение данного документа невозможным. Для более наглядных примеров заполняйте таблицы, используя генератор тестовых данных.
Когда вы запускаете запрос, происходит следующее:
Анализ и оптимизация обычно не занимают много времени (и требуются только тогда, когда запрос подготавливается, а не каждый раз, когда запускается), поэтому первым делом при медленном выполнении запроса следует запустить его вне контекста клиентского приложения. Если при этом проблем не возникает, значит у вас проблема с кодом, а не с InterBase. Использование SQL Monitor – самый быстрый способ выявить причину проблемы.
[Примечание: Иногда случается, что подготовка запроса в приложении занимает достаточно много времени. Часть этого времени занимается сервером IB, а часть – компонентами доступа к базе данных, которые часто делают вещи типа поиска значений метаданных во время подготовки запроса.]
Однако когда вы запускаете запрос вне своего приложения, помните, что большинство инструментов IB администрирования не делают FetchAll (полную выборку), имеется в виду, что они делают выборку только для необходимого количества записей из результирующего набора для заполнения одного экрана и делают дополнительные выборки, когда пользователь делает прокрутку, вместо того чтобы сделать выборку всего набора данных сразу. (InterBase PLANalyer является исключением из этого правила, так как FetchAll необходим для корректного считывания статистики). Это влияет на то, насколько быстро выполняется запрос, таким образом удостоверьтесь, что сравнение будет корректным, когда вы выполняете запрос вне контекста программы, если ваша программа делает FetchAll. Использование FetchAll иногда бывает необходимым (например, в сервере приложений, не сохраняющем состояния), иногда нет, поэтому, если FetchAll вызывает проблемы со скоростью выполнения запросов, вам следует подумать о том, чтобы исключить FetchAll для уменьшения числа записей в результирующем наборе.
Если вне контекста приложения скорость все равно не увеличилась, определите, проблема – в скорости выполнения запроса или в выборке. InterBase PLANalyzer предоставляет оба эти значения отдельно на вкладке Статистика. Важно не попасть в ловушку, обращая внимание только на время выполнения, так как и время выполнения, и время выборки – оба оказывают влияние на конечную скорость, которую имеет пользователь программы. Например, сортировка результирующего набора с использованием индекса ускоряет выполнение запроса, но увеличивает время передачи результатов клиенту, и сортировка того же набора, но без индекса оказывает противоположный эффект. Если сравнить оба эти результата, то в целом сортировка без индекса окажется быстрее. Поэтому для выявления причины проблем со скоростью необходимо знать и время выполнения запроса, и время выборки.
Если время передачи данных клиенту слишком велико, проверьте количество записей, возвращаемое запросом. Спросите себя, действительно ли клиентскому приложению нужна каждая запись результирующего набора. Если ответ «нет», то необходимо изменить запрос так, чтобы результатом запроса были только те записи, в которых клиент действительно нуждается (обычно с использованием оператора WHERE или JOIN). Это особенно важно для медленных сетей.
Если количество записей, возвращаемое клиенту небольшое, то следующее, что нужно проверить – это количество записей, которое InterBase внутренним образом читает, чтобы выполнить запрос. Если, например, нет индекса, который оптимизирует предложение WHERE, InterBase вынужден будет читать каждую запись в таблице, чтобы проверить, удовлетворяет ли она условию WHERE.
Используйте вкладку Статистика в InterBase PLANalyzer для определения количества записей, читаемого InterBase при выполнении запроса. Оно должно приближаться к числу возвращаемых записей. Ясно, что в случае запроса на объединение двух таблиц, используя INNER JOIN, InterBase должен будет прочитать по крайней мере по одной записи из каждой таблицы для одной записи результирующего набора, таким образом, число записей, читаемых InterBase будет как минимум в два раза больше числа возвращаемых.
Если вы установили, что InterBase читает значительно больше записей, чем того требует результирующий набор, то производительность для такого запроса может быть повышена при помощи использования индекса. Если индекс для данного поля уже определен, но не используется, вы должны выяснить, по каким причинам InterBase не использует индекс при выполнении запроса. Если индекс (или составной индекс) использовался, но при этом читалось много записей, вы должны попытаться повысить селективность индекса (или комбинации индексов).
Если передача данных происходит достаточно медленно, но количество записей невелико, причиной этого может быть индекс. Для более детальной информации читайте раздел по теме чтение таблицы и сортировка, а также примите во внимание то, что эффективная селективность индекса может быть ниже, чем селективность, показываемая средствами оценки эффективности. Индекс вызовет повышение скорости, только если уменьшит количество возвращаемых записей.
Медленная сеть, особенно соединение по телефонной линии, может заметно снизить производительность системы клиент-сервер. Но даже пропускная способность 100 Мб\с сети Ethernet является одним из главнейших узких мест. Вот почему основой хорошего приложения клиент/сервер является уменьшение количества данных, которые пересылаются с сервера БД клиентскому приложению. Используйте операторы WHERE, избегайте SELECT *, если вам не нужны все столбцы и т. д. Но иногда вы выполняете всё это, а пропускная способность сети по-прежнему остаётся проблемой. Каким будет следующий шаг? Вот несколько вещей, которые вы можете сделать, чтобы передать данные по медленному каналу:
Когда вы уверены, что ваш запрос возвращает только нужные строки и столбцы, для уменьшения количества данных, которые должны быть переданы по сети, следующим шагом является проверка самого SQL. Может оказаться необходимым создание, удаление или изменение индексов используемых таблиц.
Индексы InterBase реализованы в виде b-дерева, и иногда b-деревья могут становиться несбалансированными, особенно после удаления или обновления большого количества строк. Что это значит, объяснить достаточно сложно и это не входит в область рассмотрения данного документа (для основательной подготовки по данной теме см. отличную книгу Julian Bucknall The Tomes of Delphi: Algorithms and Data Structures), но кратким ответом по этой проблеме является то, что хорошо время от времени создавать резервные копии и восстанавливать базу данных InterBase. В дополнение к созданию резервной копии (что никогда не помешает) процесс восстановления гарантирует сбалансированность индексов.
Очевидно, что хотя утилита InterBase backup может функционировать даже когда пользователи работают с базой данных, перед резервным копированием необходимо перевести базу данных в offline если вы собираетесь восстанавливать базу данных из резервной копии.
Вы можете диагностировать разбалансировку индексов утилитой командной строки gstat, входящей в поставку InterBase или с помощью функции Статистика в IBConsole (Database->Maintenance->Database Statistics).
Отдельные индексы можно сбалансировать их дезактивацией и активацией заново. Следующие две команды в ISQL выполнят это:
ISQL> ALTER INDEX INDEX_NAME INACTIVE; ISQL> ALTER INDEX INDEX_NAME ACTIVE;
Это безопасно для выполнения, если пользователи работают с БД, но имейте в виду, что это может временно вызвать медленное выполнение запросов, которые используют таблицу в вопросе! Заметьте, что индексы, которые используются в ограничениях (такие как первичные или внешние ключи), не могут быть дезактивированы. Эти индексы могут быть перестроены только с помощью процедуры резервного копирования и восстановления из копии.
Наилучший способ определить, разбалансирован индекс или нет - это использовать статистику, которая генерируется утилитой командной строки gstat или командой Database Statistics в IBConsole. Имейте в виду, что можно генерировать статистику только для индексов вместо попыток её поиска посреди всей информации, которую могут генерировать данные инструменты.
При обсуждении индексов в InterBase термин «статистика» используется в двух разных направлениях. Он может означать статистику, отображенную утилитой gstat, или значение поля RDB$STATISTICS в системной таблице RDB$INDICES.
Команда SET STATISTICS относится к последнему упомянутому значению. Это просто другой способ выражения селективности индекса. Так как вычисление статистики может потребовать много времени (особенно для таблицы с большим количеством данных), это происходит нечасто. В исключительных случаях (особенно после изменения большого количества данных в таблице) статистика индекса может быть неточной, что приводит оптимизатор InterBase к выбору неверного PLAN для вашего запроса. Исправить это очень просто: задайте IB пересчитать статистику для того индекса. Вы делаете это с помощью команды SET STATISTICS в ISQL или кнопки "Set Statistics" на вкладке PLAN Analysis в InterBase PLANalyzer. Создание резервной копии и восстановление БД также должно это исправить.
Что происходит, когда вы отправляете SELECT или другой SQL запрос серверу InterBase? Важно понимать различие между оптимизацией и другими частями обработки SQL запроса.
Перед тем как InterBase сможет выполнить SQL запрос, сначала он переведёт его в разновидность двоичного кода, называемого BLR. BLR - это просто другой способ представления запроса: представьте себе это как трудночитаемую версию SQL. Таким образом, первое что происходит – InterBase анализирует ваш SQL, и, с учётом того, что нет ошибок, конвертирует его в BLR. Оптимизация в этот момент не производится.
Теперь InterBase имеет запрос на том языке, который он понимает, поэтому можно переходить к оптимизации. InterBase рассмотрит различные стратегии, которые можно использовать для выполнения вашего запроса и оценит стоимость (по сути, индекс производительности) каждой из них. Например, можно использовать индекс для поиска конкретной записи, а можно использовать последовательный (NATURAL) поиск. InterBase подсчитывает «стоимость» каждой из стратегий и выбирает ту, которая прогнозируется как наименее «дорогая». (Это, конечно, сильное упрощение.)
Результатом этого шага оптимизации является план того, как InterBase выполнит запрос. InterBase может (частично) выражать это через предложения PLAN, и вы можете задавать InterBase то, как оптимизировать запрос, путём подачи PLAN как части вашего запроса.
Но в то время как предложения PLAN, созданные InterBase, очень полезны для диагностики и исправления ошибок оптимизации, я настоятельно рекомендую не использовать подробный (т.е. включенный в запрос) PLAN для того чтобы задать IB то, какую оптимизационную стратегию использовать.
Важно понимать здесь, что стратегия оптимизации зависит от данных – наилучшая стратегия для выполнения определенного запроса может измениться в зависимости от того, сколько записей содержит таблица и какие значения хранятся в этих записях, так как это меняет селективность используемых индексов.
Как вы, возможно, знаете, при создании хранимой процедуры или представления (VIEW), InterBase скомпилирует это в BLR и сохранит в BLR. Таким образом, игнорируется этап анализа SQL, когда вы хотите работать с объектом, который создали. План оптимизации не сохраняется, но, воссоздается заново каждый раз при выполнении запроса. Это важно, потому что правильная стратегия может меняться в зависимости от типа данных БД.
Более подробное описание внутренних оптимизационных процессов не входит в задачи данной статьи, но интересующиеся могут посмотреть статью Paul McGee, Managing Your InterBase Server.
Используя явный PLAN вы сообщаете InterBase, что существует только один способ выполнения запроса – период, конец предложения. Оптимизатор InterBase не может изменить эту стратегию в ответ на изменение условий данных. Иными словами PLAN полностью вырубает оптимизатор.
Также вы не сможете управлять именами индексов, которые создаёт InterBase для внешних и первичных ключей. Они будут иметь имена типа RDB$PRIMARY148. Число в конце имени индекса может каждый раз быть другой, когда вы воссоздаете БД из DDL. Так что вы не можете использовать имена индексов первичных или внешних ключей в запросе PLAN, потому что вы не можете предугадать имя индекса.
Вот простейший SQL запрос
SELECT * FROM EMPLOYEE E WHERE E.PHONE_EXT = :PhoneExt;PLAN выдает следующее: PLAN (E NATURAL)
Этот PLAN означает, что InterBase прочитает каждую строку в таблице EMPLOYEE и возвратит строки, которые будут удовлетворять оператору WHERE.
Этот PLAN очень прост для прочтения, но более сложные запросы могут породить более многословные PLAN. В таком случае графический дисплей InterBase PLANalyzer сделает PLAN гораздо легче для понимания.
В таблице подобной EMPLOYEE, которая содержит меньше 100 строк, с этим не будет никаких проблем, но в большой таблице, скажем, с несколькими сотнями тысяч записей, это может быть проблемой. (Здесь, хорошо бы вернуться к золотому правилу оптимизации). Так как Employee.gdb не имеет таблиц по 100 000 строк для работы с ними, давайте представим, что таблица EMPLOYEE гораздо больше, чем есть на самом деле.
Если этот запрос выполнялся слишком медленно, первое, что следует проверить это доступные в таблице индексы и убедиться, что не имеется подходящего индекса, который бы не использовался по какой-то причине.
Если подходящий индекс доступен, а оптимизатор как будто его не находит, проверьте следующее:
Если нет подходящего индекса, вы можете его создать (Сreate).
Так как InterBase использует префиксную компрессию при сортировке индексов, возможно сравнение индексных записей с использованием только начала значения (если не всего значения). Например, этот запрос использует индекс:
SELECT EMP_NO, FIRST_NAME, LAST_NAME FROM EMPLOYEE WHERE LAST_ NAME LIKE 'St%';
так как St будут первыми двумя буквами этого входа индекса. Но этот запрос не может:
SELECT EMP_NO, FIRST_NAME, LAST_NAME FROM EMPLOYEE WHERE LAST_ NAME LIKE '%t%';
…так как IB не может использовать индекс для поиска значений содержащих t на любой позиции внутри строки.
Это становится особенно важным при использовании параметризованных запросов. Хотя первый запрос, приведенный выше может использовать индекс при его наличии, данный запрос не может:
SELECT EMP_NO, FIRST_NAME, LAST_NAME FROM EMPLOYEE WHERE LAST_ NAME LIKE :LastName;
…даже если параметр 'LastName' установлен на 'St%'! Причина в том, что приложение могло просто установить параметр на '%t%" и InterBase должен создавать PLAN, который работает для всех значений параметров во время подготовки запроса. Чтобы это исправить, следует использовать STARTING_WITH вместо LIKE:
SELECT EMP_NO, FIRST_NAME, LAST_NAME FROM EMPLOYEE WHERE LAST_ NAME STARTING WITH :LastName;
Теперь установите значение параметра равным 'St' (без %) и IB выдаст те же результаты, но при этом будет использовать для поиска записей индекс, если таковой имеется.
CONTAINING никогда не может использовать индекс по причинам, описанным выше. Наилучший путь для поиска по полному тексту - это использование алгоритма списка слов. Коммерческие инструменты списка слов можно купить на http://www.fulltextsearch.com/, http://www.ibobjects.com/ibofts.html and http://www.softcomplete.com/products/ftsib/index.html, в других местах или написать самим.
Давайте проверим проблемный SQL запрос:
SELECT
*
FROM
EMPLOYEE E
WHERE
E.PHONE_EXT = (SELECT
E2.DEPT_NO
FROM
EMPLOYEE E2
WHERE
E2.EMP_NO = E.DEPT_NO);Понятное дело, что данный запрос был создан для того, чтобы продемонстрировать потенциальные способы оптимизации, а не для того, чтобы быть примером настоящего запроса! Игнорируйте на мгновение бессмысленность этого запроса, давайте взглянем на выражение PLAN, которое он производит и то, как мы можем использовать его для диагностики проблем с запросом.
Вот выражение PLAN
PLAN (E2 INDEX (RDB$PRIMARY7)) PLAN (E NATURAL)
Первое, что мы видим это то, что PLAN выражение включает в себя два PLAN, один для подзапроса и другой для внешнего запроса. Следующие функции SQL приведут к более чем одному PLAN:
Более одного PLAN для SQL запроса, который включает в себя подзапросы или UNION – это нормально. Если можно записать выражение без использования подзапроса или UNION [ALL], которые дают те же результаты, это, вероятно, хорошая идея, но иногда эти функции необходимы, и InterBase обычно хорошо выполняет запросы с их использованием.
Однако есть пара моментов, которые могут замедлять данные запросы.
SELECT
*
FROM
EMPLOYEE E
INNER JOIN EMPLOYEE E2
ON (E2.DEPT_NO = E.PHONE_EXT) AND (E2.EMP_NO = E.DEPT_NO)
Этот запрос выдает только один PLAN:
PLAN JOIN (E2 NATURAL,E INDEX (RDB$FOREIGN8))
….но более важным является то, что запрос выполняется один раз. Внутри мы увидим, что InterBase читает таблицу в натуральном порядке (порядке хранения) чтобы прочитать значение EMP_NO и определить для каждой строки, используя индекс RDB$FOREIGN8, есть ли в таблице строка со значением, совпадающим с DEPT_NO. (Нажав на индекс в древовидном отображении в InterBase PLANalyzer мы увидим, что индекс RDB$FOREIGN8 находится в столбце DEPT_NO).
С SQL запросом, который требует ключевого слова UNION конечно же потребуется более одного PLANа. Единственная вещь, требующая здесь упоминания - это использование UNION ALL вместо UNION, где только можно. UNION убирает повторяющиеся строки из результирующего набора, а это требует сортировки результирующего набора каждого запроса в выражении. UNION ALL не выполняет проверку повторяющихся строк и (иногда значительно) ускоряет выполнение запроса. Заметьте, что многие запросы, содержащие UNION вообще не содержат в результатах дублирующиеся строки.
Например, как в этом примере, InterBase не имеет NULLIF функцию. Единственный выход - это использование UNION ALL. Что будет, если мы вместо этого выберем UNION?
SELECT
FULL_NAME, PHONE_EXT
FROM
EMPLOYEE
WHERE
PHONE_EXT IS NOT NULL
UNION
SELECT
FULL_NAME, CAST('None' AS VARCHAR(4))
FROM
EMPLOYEE
WHERE
PHONE_EXT IS NULL
Этот запрос заставляет InterBase сортировать результирующий набор каждого выражения SELECT в запросе. Но так как PHONE_EXT не может иметь значение NULL и non-NULL для одной и той же строки, нет возможности появиться этой строке в 2-х запросах. Заменяя UNION на UNION ALL, мы можем ускорить запрос, уменьшая объем работы, которую InterBase должен сделать для выполнения запроса.
Оптимизатор по видимому мог бы быть изменен для того, чтобы это сделать, но это привело бы к замедлению с целью выполнения правил, которым администраторы БД должны следовать в любом случае.
InterBase работает неэффективно в одной специфической ситуации. Если запрос содержит подзапрос и если этот подзапрос сравнивает один столбец с более чем одной константой и если этот столбец индексирован, то IB поступит с подзапросом как со связанным подзапросом, даже если это не так. Вот пара примеров потенциально проблемных SQL:
SELECT
*
FROM
EMPLOYEE_PROJECT
WHERE
EMP_NO IN (SELECT EMP_NO FROM EMPLOYEE
WHERE LAST_NAME IN ('Nelson', 'Young'));SELECT
*
FROM
EMPLOYEE_PROJECT
WHERE
EMP_NO IN (SELECT EMP_NO FROM EMPLOYEE
WHERE (LAST_NAME = 'Nelson') OR (LAST_NAME = 'Young'));
Полученный PLAN одинаков для обоих подзапросов:
PLAN (EMPLOYEE INDEX (RDB$PRIMARY7,NAMEX,NAMEX)) PLAN (EMPLOYEE_PROJECT NATURAL)
Заметьте, что индекс NAMEX используется дважды. Если бы в списке были только 3 константы (т.е. другая фамилия), тогда NAMEX появлялось бы 3 раза и т.д. Это не проблема, и само по себе повторное использование индекса, возможно, самый быстрый способ извлечь строки из таблицы. Проблем в том, что InterBase делает внутренне эти поиски (lookups) один раз для каждой строки итогового массива. Если число строк возвращенных из запроса велико, это может занять много времени.
Перед тем, как пробовать любой из обходных приемов приведенных ниже, убедитесь, что проблема скорости исходит от этой причины, а не откуда-нибудь ещё. Самый лёгкий способ проверить, что проблема в этом - использовать только одну константу в операторе IN. Если скорость будет возрастать экспоненциально, значит проблема в этом. Если нет – проблема в чём-то другом.
Обходной путь №1: Использование табличных данных вместо констант
Решить эту проблему можно несколькими способами. Первый - это выбор совпадающих значений из таблицы вместо использования констант в запросе. Например, мы могли бы создать таблицу с названием SELECTED_EMPLOYEES и INSERT две записи для работников Нельсона и Янга. Потом мы могли бы переписать первое выражение следующим образом:
SELECT * FROM EMPLOYEE_PROJECT WHERE EMP_NO IN (SELECT EMP_NO FROM SELECTED_EMPLOYEES);
Это решает проблему.
Обходной путь №2: Использование UNION или UNION ALL
При использовании только одной константы проблем не возникает. Использование UNION или UNION ALL для объединения результатов двух запросов с одной только константой часто работает (значительно) быстрее, чем один запрос с двумя константами. Следующее выражение дает тот же результат, как и первое выражение данного раздела, но может работать значительно быстрее с большими таблицами:
SELECT
*
FROM
EMPLOYEE_PROJECT
WHERE
EMP_NO = (SELECT EMP_NO FROM EMPLOYEE
WHERE LAST_NAME = 'Nelson')
UNION ALL
SELECT
*
FROM
EMPLOYEE_PROJECT
WHERE
EMP_NO = (SELECT EMP_NO FROM EMPLOYEE
WHERE LAST_NAME = 'Young');
Обходной путь №3: Заставить IB использовать последовательное сканирование
Заставив IB не использовать индекс, вы обойдёте проблему. Этот приём очевидно практичен, только если таблица в подзапросе достаточно мала.
SELECT
*
FROM
EMPLOYEE_PROJECT
WHERE
EMP_NO IN (SELECT EMP_NO FROM EMPLOYEE WHERE LAST_NAME || '' IN ('Nelson', 'Young'));
Присоединяя LAST_NAME к пустой строке, мы ликвидируем способность IB использовать индекс для поиска совпадающих записей. Произведенный PLAN следующий:
PLAN (EMPLOYEE INDEX (RDB$PRIMARY7)) PLAN (EMPLOYEE_PROJECT NATURAL)
Таким образом, поиск каждого EMP_NO из EMPLOYEE медленнее – значительно медленнее, если таблица EMPLOYEE большая, но запрос больше не ведет себя так, как будто содержит связанный подзапрос, это значит, что он будет работать быстрее, если в итоговом запросе большое число строк.
Записи из таблицы считываются двумя способами:
Чтобы понять выражение PLAN вам нужно уметь различать эти два метода считывания данных и понимать их последствия.
Самый быстрый способ считывания записей с диска - это их считывание в порядке, в котором они хранятся. Перемещение головки жёсткого диска в случайное место на диске - одна из самых медленных вещей, которые должен делать сервер БД, и выборка записей в порядке хранения минимизирует число перемещений считывающей головки и позволяет использовать кэш опережающего считывания в полном объеме.
Когда таблица считана в порядке хранения, в PLAN выражении это будет выглядеть следующим образом:
PLAN (EMPLOYEE NATURAL)
Часть выражения PLAN выделенная выше полужирным шрифтом означает, что таблица EMPLOYEE будет считана в порядке хранения.
Считывание записей не в порядке их хранения обычно гораздо медленнее, потому что головка жёсткого диска обычно должна перемещаться часто (если только записи не расположены в том же порядке на диске, в каком они расположены в индексе, что иногда может случиться с первичными индексами). Однако потеря в скорости стоит того, когда индекс может быть использован для значительного уменьшения числа записей, которые должны быть считаны.
Есть два способа, которыми InterBase может отображать то, что таблица была считана в порядке хранения. Первый синтаксис обозначает, что индекс (или составные индексы) используются для оптимизации оператора WHERE или JOIN:
PLAN (EMPLOYEE INDEX (NAMEX,RDB$PRIMARY7))
Часть, выделенная полужирным шрифтом, означает, что InterBase объединяет два индекса и использует комбинацию для расположения совпадающих записей в таблице.
«Галочки» и цифры рядом с индексами отображают их селективность. Ярлык ноутбука рядом с таблицей является другим напоминанием того, что таблица считывается скорее по индексу, чем в порядке хранения.
InterBase реализует этот PLAN считыванием каждого индекса, сохранением его внутри в виде битовой карты, а затем объединением двух битовых карт при помощи булевой операции AND. Таким образом создаётся комбинированный индекс, с помощью которого InterBase может искать совпадающие записи. Это немного накладно, так как выполнение этой комбинации слишком зависимо от того, что ещё вам понадобится делать с таблицей, чтобы составной индекс был более подходящим.
Второй синтаксис означает, что InterBase использует индекс для выполнения ORDER BY:
PLAN (EMPLOYEE ORDER NAMEX)
Если InterBase использует индекс для выполнения ORDER BY и для оптимизации оператора WHERE или JOIN, то будет использоваться второй синтаксис.
Очень важно, чтобы вы не путали этот второй синтаксис для чтения записей не в порядке сохранения с синтаксисом PLAN для чтения записей в порядке сохранения и последующего выполнения сортировки в оперативной памяти. Это две различные операции, но они обе дают желаемый результат.
InterBase может упорядочить результирующий набор считыванием записей с жёсткого диска по порядку (используя индекс) или считыванием записей в порядке хранения, а затем их сортировкой. В общем случае, последний способ быстрее, когда учитываются и время выполнения, и время выборки.
Посмотрите на данный запрос:
SELECT EMP_NO, FIRST_NAME, LAST_NAME FROM EMPLOYEE ORDER BY FIRST_NAME
Это запрос заставляет InterBase вернуть такой PLAN:
PLAN SORT ((EMPLOYEE NATURAL))
Это значит, что InterBase сначала читает полностью таблицу EMPLOYEE в порядке хранения и использует сортировку в памяти для упорядочения результатов. Если бы в таблице было слишком много строк для сортировки их в памяти сервера, InterBase разбил бы записи на страницы во временных файлах как это нужно.
Если бы таблица EMPLOYEE содержала больше строк, мы бы увидели, что сортировка записей в памяти приводит к большему замедлению выполнения запроса, чем запрос к той же таблице с теми же строкам, но без SORT в PLAN выражении, но та выборка записей сравнительно быстрая. Это происходит, потому что записи теперь находятся упорядоченные во временном файле на сервере, и упорядоченные строки могут быть считаны в порядке хранения.
Давайте изменим запрос:
SELECT EMP_NO, FIRST_NAME, LAST_NAME FROM EMPLOYEE ORDER BY LAST_NAME, FIRST_NAME
Этот запрос заставляет возвращать следующий PLAN:
PLAN (EMPLOYEE ORDER NAMEX)
Это выражение PLAN означает, что InterBase не будет считывать таблицу в порядке хранения. Вместо этого он сначала считает индекс и затем использует информацию, полученную из индекса для посещения каждой записи на диске в алфавитном порядке.
Так как InterBase не должен считывать таблицу полностью и сортировать её в памяти, чтобы выдать первую запись клиенту, время выполнения данного запроса будет таким же как запрос без выражения ORDER BY. Однако выборка записей может быть медленнее. В самом худшем из возможных случаев InterBase может понадобиться двигать считывающую головку диска с целью считывания каждой последующей записи. В лучшем случае записи будут уже упорядочены на диске, поэтому не потребуется большого числа перемещений считывающей головки жесткого диска.
Иногда отображение записей в неупорядоченном виде может значительно увеличить скорость выполнения запроса. В частности, это помогает, если мы можем уменьшить количество считываемых записей:
SELECT EMP_NO, FIRST_NAME, LAST_NAME FROM EMPLOYEE WHERE LAST_NAME STARTING WITH 'S'
Этот запрос заставляет InterBase вернуть следующий PLAN:
PLAN (EMPLOYEE INDEX (NAMEX))
Обратите внимание на разницу между этим PLAN и приведенным выше (для запроса, который заканчивается "ORDER BY LAST_NAME, FIRST_NAME"). Вместо считывания всей таблицы с использованием индекса, InterBase использует индекс NAMEX для перемещения прямо к записям со значением LAST_NAME, которые начинаются с ‘S’. Так как запрос не имеет выражения WHERE, мы не можем предполагать, что возвращённые записи будут в каком-то определенном порядке. Если мы добавляем "ORDER BY LAST_NAME, FIRST_NAME" к этому запросу (оставляя выражение WHERE) PLAN станет таким же PLAN, который мы видели для запроса без этого выражения WHERE. Однако, глядя на вкладку Статистика InterBase PLANalyzера, мы можем сказать, что только три записи читаются внутри InterBase. Уменьшая число записей, которые InterBase должен считать с жёсткого диска с 42 до 3-х, мы добиваемся более чем приемлемой скорости для того, чтобы наверстать цену считывания 3-х записей не в порядке хранения.
Как показано выше, два метода считывания записей (в порядке хранения и не в порядке хранения) и два метода упорядочения записей (с использованием сортировки в оперативной памяти или чтения записей в порядке сортировки с использованием индекса) влияют на различные части цикла выполнения запроса и взаимодействуют разными сложными путями. Более того, воздействия на скорость различаются в зависимости от того сохранены ли записи на диск в порядке, близком к определенному в выражении ORDER BY. Таким образом, следующие выводы являются обобщением и могут изменяться в зависимости от ваших запросов и вашей базы данных.
В общем случае, самый скорейший путь к получению упорядоченного набора данных - это сортировка в ОП, когда вы принимаете во внимание и время исполнения запроса, и время выборки всех записей в результирующем наборе. Множество людей страдают, веря в это, но то, что перемещение считывающей головки жёсткого диска гораздо медленнее по сравнению с быстрой сортировкой является простейшим фактом. Даже если сервер вынужден подкачивать записи на диск во время сортировки можно делать это упорядоченным образом, делая считывание и запись более эффективными. Вы можете дополнительно выиграть в скорости, располагая временную директорию InterBase (куда идут отсортированные файлы) и саму базу данных на двух различных физических (не логических) жёстких дисках. Таким образом головка на единственном диске не должна будет метаться при копировании записей из базы данных в сортировочный файл.
Конечно же, многие приложения не делают FetchAll (полную выборку). Хотя вы не должны выбирать записи, которые не собираетесь использовать для выборки, существуют ситуации, когда быстрый начальный ответ более важен чем общее быстрое время выполнения. Запрос ORDER BY, выполненный с использованием индекса (и с демонстрацией в PLAN выражении как считывание таблицы с ORDER помимо этого) будет всегда быстрее, чем SORT в памяти, кроме тех случаев, когда значения времени так близки друг к другу, что неразличимы.
InterBase будет использовать индекс для выполнения запроса ORDER BY, если он доступен. Как уже отмечено в данном разделе, результатом этого может быть замедление выполнения запроса и выборки всех записей, чем если бы тот же ORDER BY выполнялся без индекса. Вы можете запретить InterBase использовать индекс для оптимизации выражения ORDER BY (без пересортировки в выражении PLAN, которое бы полностью отменило оптимизатор) путём добавления бессмысленной операции к одному из полей, как это показано в следующих примерах. Это позволяет вам «настраивать» оптимизацию ORDER BY, но, тем не менее, позволяет оптимизатору делать свою работу для остального запроса. SELECT EMP_NO, FIRST_NAME, LAST_NAME || '' FROM EMPLOYEE ORDER BY 3, 2
Присоединение пустой строки к концу строки в столбце позволяет IB не распознавать столбец как часть индекса, но не изменяет результирующий набор. Теперь PLAN такой:
PLAN SORT ((EMPLOYEE NATURAL))
Для числовых полей, к столбцу добавляйте 0.
Индекс – это структура данных, используемая базой данных для быстрого поиска записей. Подробное описание того, как работают индексы InterBase, лежит вне рассмотрения данной статьи, но по существу, запись (элемент) индекса позволяет InterBase прямо переходить к одной или более совпадающих записей, удовлетворяя определенным условиям. Это немного похоже на алфавитный указатель в книге: если вы ищете EXECUTE PROCEDURE в оглавлении руководства по эксплуатации InterBase, вы увидите, что найдете нужную информацию на страницах 147 и 159, это позволяет вам найти эту информацию быстрее, чем если бы вы искали слова "EXECUTE PROCEDURE" на каждой странице по порядку начиная с самой первой.
Так как индекс может вам быстро сказать о том, есть ли конкретное значение в таблице (даже нет необходимости считывать запись в запросе; надо просто посмотреть, существует ли значение в индексе), они очень полезны для обеспечения ограничений уникальности. Намного быстрее привести в исполнение ограничение уникальности с индексом, чем без него, поэтому InterBase автоматически создаёт соответствующий индекс при создании вами ограничения уникальности. Индексы могут быть неуникальными (по умолчанию) или уникальными.
Если индекс – не уникальный, он будет содержать ссылки на множество записей базы данных для некоторых значений хранящихся в индексе.
InterBase использует префиксное сжатие при хранении значений в своих индексах. То есть он сохраняет первое значение в индексе, а затем при сохранении следующего значения, он только сохраняет символы от той точки, с которой они начинают различаться с первого значения. Таким образом, если InterBase необходимо сохранить ссылки к следующим 2-м значениям в индексе:
AAAAAA
AAAABB
На самом деле внутри самого индекса сохранилось бы что-то подобное:
AAAAAA
4 BB
Это называется префиксной компрессией. Она сохраняет много пространства во многих операциях и при запуске работает очень быстро. Однако, это делает невозможным для InterBase читать индексы в обратном направлении. Если вы искали последнее значение в индексе, вы возможно должны были считать весь путь до начала индекса, чтобы понять что это было.
По этой причине InterBase предлагает два различных вида индексов: возрастающий и убывающий. Большинство индексов являются возрастающими. Возрастающие индексы используются при поиске записей, удовлетворяющих специальным критериям, таким как:
SELECT FOO FROM BAR WHERE FOO_ID = 1000
Нисходящие индексы в большинстве своём полезны для функции MAX. MAX в InterBase очень эффективен с нисходящим индексом и очень неэффективен без него, потому что при отсутствии убывающего индекса используется неиндексированный табличный поиск.
Стоит упомянуть также то, что некоторые запросы работают быстрее без использования индекса. Индексы, в сущности, полезны, если вы хотите считать небольшое подмножество записей в таблице. Так как ввод/вывод на диск занимает какое-то время, гораздо быстрее прямо перейти к нескольким записям, которые необходимо считать, чем сканировать каждую запись таблицы, чтобы ее найти.
Когда InterBase использует индекс для поиска записи, он ищет страницу базы данных (по существу, обширную область на диске, где может находиться запись) и смещение (точную область внутри той общей области для поиска записи) в индексе, а затем переходит прямо туда. Если индекс не используется, InterBase будет читать каждую запись таблицы до тех пор пока не найдёт нужную запись (записи).
Если вы хотите прочитать все записи в таблице (или большинство из них), часто лучше вообще не использовать индекс. Например, если вы хотите отсортировать каждую запись таблицы в алфавитном порядке, возможно, будет быстрее считать записи в порядке хранения, а затем отсортировать их в памяти, при необходимости использую страничную подкачку памяти. Это происходит потому, что гораздо быстрее считывать записи в порядке хранения, перемещая считывающую головку как можно меньше, чем считывать записи не в порядке хранения, заставляя головку много перемещаться.
Индексы бесполезны, если они существенно не уменьшают число записей, которые InterBase должен просмотреть, чтобы найти совпадение. Самый очевидный пример - это булев столбец, содержащий приблизительно равное количество значений TRUE и FALSE. Посмотрите на эту таблицу:
CREATE TABLE PERSON ( PERSON_ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(60), IS_MALE BOOLEAN);
Создание индекса в булевом столбце вряд ли помогло бы InterBase выполнить любой запрос, даже такой как этот:
SELECT
PERSON_ID, NAME
FROM
PERSON
WHERE
IS_MALE = FALSE
ORDER BY
NAME, PERSON_ID;
Почему? Потому что если индекс используется, то поиск совпадающих записей является двухэтапным процессом. Сначала IB должен найти совпадение в индексе, потом он должен найти саму запись на диске. Так как порядок индексов может не совпадать с порядком расположения на диске, извлечение записей происходит достаточно медленно. Без индекса InterBase может переходить по записям в порядке хранения и отклонять те, которые не соответствуют WHERE условию. Это гораздо быстрее, чем индексированный поиск. Индекс, показанный здесь, помог бы запросу, показанному выше, если бы данные таблицы представляли 100 000 мужчин и 10 женщин. Важным моментом является то, что индексы полезны лишь тогда, когда они значительно уменьшают число записей, которые InterBase должен проверить и «значительно», здесь, означает экспоненциальное уменьшение.
Мера полезности индекса для average запроса (т. е. не принятие в расчёт необычного распределения данных подобных как 100 000 мужчин⁄10 женщин) называется селективностью. Более подробно на селективности мы остановимся позже.
Помимо показателей селективности, отображаемых в InterBase PLANalyzer, другим инструментом полезным для проверки индекса на очень низком уровне является результат программы командной строки gstat (также доступная как Database Statistics в IBConsole)
В разделе Понимание индексов мы рассматривали, как индекс может ускорить простой запрос типа:
SELECT FOO FROM BAR WHERE FOO_ID = 1000
Очевидно, что индексы полезны для простых запросов, таких как приведенный выше. Но как обстоит дело с более сложными запросами, такими как:
SELECT
FOO
FROM
BAR
WHERE
FOO_COLOR = 'Blue'
AND
FOO_DATE = CURRENT_DATE
AND
FOO_DSC STARTING WITH 'Foo';
Вот несколько подходов, которые можно использовать при разработке наших метаданных:
Если решение (1) работает для вашего конкретного приложения, то это здорово. Если нет, то нужно попробовать (2) и (3).
Давайте представим, что вы избрали решение (2) и создали индексы для FOO_DATE, FOO_COLOR, и FOO_DSC. Если вы напишите выражение WHERE, как показано выше, то оптимизатор InterBase сообщит о том, что все три индекса могли бы быть полезны для выполнения запроса. Для каждого индекса он создает своего рода битовую карту, где индекс включается, если есть узел для конкретного значения и выключается если – нет. Затем он объединяет битовые изображения с помощью двоичной операции AND и использует результирующее изображение в качестве одиночного индекса в таблице. Так делать несколько накладно, но не так уж плохо в большинстве случаев.
Если вы выбираете решение (3), то InterBase может использовать ваш многостолбцовый индекс как есть, без какой-либо комбинации отдельных индексов. Таким образом (3) может обеспечить самые быстрые результаты запроса для этого конкретного SELECT. А что с другими запросами, будет ли индекс по-прежнему полезным?
Ответ – «может быть». InterBase может использовать многостолбцовый индекс в некоторых случаях, даже если вы не используете все столбцы в индексе в вашем запросе. В частности, InterBase может использовать многостолбцовый индекс, когда вы используете первые n столбцов в индексе в запросе. Иными словами, тот порядок, в котором столбцы приведены в индексе, важен! Давайте предположим, что мы создаем следующий индекс для помощи с запросом выше:
CREATE INDEX XBAR ON BAR (FOO_DATE, FOO_COLOR, FOO_DSC);
Индекс будет, очевидно, очень полезен в запросе выше. Он будет также полезен в следующем запросе:
SELECT
FOO
FROM
BAR
WHERE
FOO_DSC = 'Foo'
AND
FOO_DATE = CURRENT_DATE;
…потому что FOO_DATE и FOO_COLOR – первые два столбца в индексе. InterBase не может использовать этот индекс для такого запроса:
SELECT FOO FROM BAR WHERE FOO_DSC = 'Foo';
….однако, потому что FOO_DSC не является первым столбцов индекса.
Это чрезвычайно важно, если у вас есть составные первичные ключи в некоторых ваших таблицах. Если у вас в таблице есть составной первичный ключ, вы должны всегда определять полное значение первичного ключа – включая все столбцы – если вы хотите использовать индекс для поиска записи.
Представьте себе, что вы создаете таблицы для того, чтобы отслеживать работников и категории зарплат. Таблицы работников и категорий зарплат достаточно просты:
CREATE TABLE EMPLOYEE ( ID INTEGER NOT NULL, NAME VARCHAR(60), PRIMARY KEY (ID));
CREATE TABLE SALARY_GRADE ( ID INTEGER NOT NULL, NAME VARCHAR(60), SALARY NUMERIC(16,2), PRIMARY KEY (ID));
Теперь нам необходим способ хранения: какие работники - в каких категориях зарплат. Одним из возможных путей создания таблицы было бы использование составного первичного ключа:
CREATE TABLE EMPLOYEE_SALARY_GRADE ( EMPLOYEE_ID INTEGER NOT NULL, SALARY_GRADE_ID INTEGER NOT NULL, PRIMARY KEY(EMPLOYEE_ID, SALARY_GRADE_ID));
Но эта конструкция имеет серьёзный недостаток: с таким дизайном метаданных индекс первичного ключа будет использоваться только если мы ищем специфическую запись с использованием EMPLOYEE_ID и SALARY_GRADE_ID или если мы ищем специфический EMPLOYEE_ID. Индекс не может использоваться, если нам нужен список всех работников в определенной зарплатной категории. Мы можем перевернуть поля в определении первичного ключа и, следовательно, использовать индекс в поиске всех работников в определенной категории зарплат, но мы не смогли бы использовать индекс при поиске определенной записи работника. Более гибкое решение дало бы таблице EMPLOYEE_SALARY_GRADE свой собственный первичный ключ и создало бы индивидуальные индексы для столбцов EMPLOYEE_ID и SALARY_GRADE_ID. Теперь таблица будет определена следующим образом:
CREATE TABLE EMPLOYEE_SALARY_GRADE ( ID INTEGER NOT NULL, EMPLOYEE_ID INTEGER NOT NULL, SALARY_GRADE_ID INTEGER NOT NULL, PRIMARY KEY(ID));
Но перед созданием индексов для столбцов EMPLOYEE_ID и SALARY_GRADE_ID необходимо решить, устанавливать или нет ограничение FOREIGN KEY для столбцов EMPLOYEE_ID и SALARY_GRADE_ID, чтобы сервер InterBase обеспечивал ссылочную целостность. Если мы создаем эти ограничения, то не нужно создавать индексы для этих столбцов вручную, так как InterBase создаст эти индексы автоматически, чтобы помочь выполнить ограничения.
Селективность - это, в общих терминах, мера полезности индекса в поиске (локализации) средней записи в таблице. Это эквивалентно среднему количеству записей, совпадающих с любым элементом индекса.
Некоторые индексы являются уникальными (UNIQUE), то есть, для каждого значения индекса, вы найдёте одну и только одну запись в соответствующей таблице. Хорошим примером является первичный ключ. Первичные ключи всегда ассоциируются с уникальным индексом. Некоторые индексы неуникальные. Представьте таблицу работников с индексом по полю «Фамилия». Так как много работников могут иметь одну и ту же фамилию, каждый лист в индексе может указывать на одну или множество записей.
Селективность - это число, которое представляет среднее число записей, соответствующих каждому значению в индексе.
Индексы с низкими значениями селективности позволяют InterBase очень быстро находить записи в большой таблице. Индексы с высокими значениями селективности означают, что индекс может быть менее полезен для поиска записи. К примеру, представьте таблицу людей с 10 000 записей. Представьте, что есть столбец с названием «Цвет глаз», и что по этому столбцу есть индекс с селективностью 2000. Другими словами, в среднем 2000 человек будет иметь любой заданный цвет глаз. Этот индекс не будет очень полезен в локализации записи для определенных людей, так как даже если мы знаем, что у них голубые глаза, мы должны будем просмотреть 2000 или более записей, чтобы найти их индивидуальную запись. В действительности, этот индекс даже может не быть полезным для выбора списка людей с голубыми глазами, потому что для сервера InterBase возможно быстрее будет прочитать всю таблицу и отбросить записи с цветом глаз отличным от голубого, чем неоднократно перемещать считывающую головку диска, чтобы прямо перейти к записям с «голубыми глазами».
Очевидно, что селективность может зависеть от вида хранимых данных. Если у вас 10 000 различных значений цветов в таблице людей, то индекс «Цвет глаз» будет уместен. Вы можете задать InterBase пересчитать селективность индексов, основанную на текущем состоянии базы данных, кликнув на индексе в дереве плана (на вкладке "PLAN analysis") и нажав клавишу "Set Statistics". "Set Statistics" может занять некоторое время, если таблица большая. См. Руководство по эксплуатации InterBase для получения информации по установке статистики (главу «Производительность базы данных и сервера», раздел «Задачи настройки базы данных», «Настройка индексов»).
Однако селективность индекса или индексов, используемых в запросе, сама по себе не может сказать вам, насколько полезен индекс для отдельного запроса. Есть несколько способов, которые могут сделать поведение индекса таким, как если бы он был более или менее селективным, чем это есть на самом деле, для конкретного запроса можно запустить: - InterBase может объединять несколько индексов при выполнении запроса. Это может привести к созданию временного индекса (по сути, это будет составной индекс), который более селективен, чем отдельные индексы, которые его составляют, или это приведет к тому, что индекс будет селективен не более, чем самый селективный индекс, входящий в него. Это зависит от данных в записях. Например, если у вас один индекс к столбцу2 ,а другой – к столбцу1, столбцу2, тогда IB, объединяя эти два, не сделает данное объединение более селективным, чем уже имеющийся составной индекс к столбцу1, столбцу2. Но если у вас есть один индекс к столбцу1, столбцу2 и другой – к столбцу3, и IB объединяет их, то такое объединение возможно будет иметь большую селективность, чем каждый индекс в отдельности. - InterBase может использовать составной индекс для оптимизации выражения WHERE, которое ссылается на все столбцы в индексе или для оптимизации выражения WHERE, которое ссылается только на первые несколько столбцов в индексе. Но в последнем случае эффективный номер селективности индекса гораздо выше, чем отображаемая селективность. Это может сбить с толку оптимизатор, так как он знает селективность индекса в целом, но не знает селективность только первого или первых двух столбцов в индексе.
InterBase примет лучшее решение насколько сможет, но невозможно всегда определять точную эффективную селективность индекса для любого отдельного запроса, и это может заставить оптимизатор InterBase избирать PLANы, которые являются не самым лучшим способом выполнения конкретного запроса. Следовательно, необходимо проверить запросы, которые ваше программное обеспечение будет использовать, с заполненными базами данных и тщательно присмотреться к тем, которые могут оказаться медленными.
Создать индекс очень просто, но следует внимательно отнестись к выбору оптимальной стратегии индексирования таблиц в базе данных. Не существует единственного «правильного» способа создания индексов. Вместо этого, администратор БД должен тщательно продумать, как будет использоваться таблица. Иными словами, верная стратегия для индексирования таблицы зависит от SQL, который будет к ней применен.
Оптимизация баз данных почти всегда начинается с запросов SELECT. Потому что SELECT – часто встречающаяся операция. В действительности, UPDATE внутри выполняет SELECT перед изменением записи. Но важно понимать, что существуют альтернативы производительности, которые сильно влияют на запросы, изменяющие запись (INSERT, UPDATE, и DELETE), но не запросы SELECT. В зависимости от использования таблицы эти альтернативы могут быть очень важны.
Например, таблица без индексов может быть заполнена при помощи 100 000 операторов INSERT гораздо быстрее, чем таблица с 10-ю индексами, потому что каждый оператор INSERT обновляет каждый индекс. Но индексы могут замедлить последующие SELECTы. Можно отключить индексы, кроме индексов, используемых для приведения в действие ограничений первичных и внешних ключей (с использованием команды ALTER INDEX в ISQL) и это может значительно ускорить начальное заполнение базы данных, но подобная техника не должна применяться во время использования базы данных, так как это является глобальным изменением, которое повлияет на всех пользователей базы данных.
Вместо этого, администратор БД должен создать лучший компромисс между скоростью SELECT и скоростью INSERT, основываясь на предполагаемом использовании таблицы.
Ясно, что бессмысленно иметь два индекса для одного и того же столбца (за исключением специального случая наличия возрастающего и убывающего индекса для одного или более столбцов), но такая ситуация может дать плоды в некоторых неожиданных местах. Например, если тот же столбец находится в простом индексе (одном для этого столбца) и в составном индексе (для этого столбца плюс один или более других столбцов) это может сбить с толку оптимизатор InterBase. Оптимизатор часто выбирает составной индекс, потому что по его статистике он представляется наиболее селективным.
Также важно помнить, что InterBase создает индексы для приведения в действие ограничений PRIMARY KEY, FOREIGN KEY, и UNIQUE. Если вы уже создали ограничение для столбца, нет необходимости создавать индекс для этого столбца, так как InterBase уже это сделал для вас.
На самом деле, индексы созданные InterBase для ограничения FOREIGN KEY некоторых случаях могут вызывать проблемы. Если в связанной таблице только 10 значений (или если существует 1000 000 значений, но только 10 используются в связанной таблице), то индекс, созданный в помощь для приведения в действие ограничения не будет высокоселективным.
Индекс, который не является высокоселективным, не будет полезен, пока его не объединить с другими индексами для образования более селективного эффективного индекса для конкретного запроса.
Эта альтернатива более подробно рассмотрена в разделе Понимание составных индексов. Но взгляните также на раздел Понимание селективности и, особенно, на подраздел Эффективная селективность для полного понимания этой сложной темы.
Агрегатная функция MAX() может использовать только убывающий индекс, а MIN() – только возрастающий индекс.
Выражения ORDER BY могут, как ни странно, замедлятся при использовании индекса.
Разрабатывая индексы, администратор БД должен тщательно продумать все SQL запросы, которые вероятно ссылаются на таблицу в вопросе. К тому же неплохо бы возвращаться к таблице время от времени с использованием реальных данных заказчика, так как данные в вопросе будут влиять на селективность индексов. Оптимизатор InterBase будет пытаться избежать использования индексов, которые не обладают высокой селективностью, но он не сможет создать индекс там, где не существует ни одного.
Статистика для индекса выглядит следующим образом:
Index NAMEX (1) Depth: 1, leaf buckets: 1, nodes: 43 Average data length: 15.00, total dup: 1, max dup: 1 Fill distribution: 0 - 19% = 0 20 - 39% = 1 40 - 59% = 0 60 - 79% = 0 80 - 99% = 0
Вот что означают эти цифры
Данная статья является всеобъемлющим руководством по оптимизации запросов InterBase, и поэтому достаточно длинная. Прошу извинить за это!