InterBase: тормозология и глюконавтика
Страница 10. Детали планирования в Interbase

Детали планирования в Interbase

Сам план строится, как особое выражение, исполняемое слева направо. Элементами этого выражения являются способы перебора конкретных таблиц, которых, вообще говоря, три, а объединяются они операциями соединения, которых тоже три. Способы перебора таблиц:

• имя_таблицы natural
• имя_таблицы index(имя_индекса, ...)
• имя_таблицы order имя_индекса

• join(обращение, обращение)
• merge(обращение, обращение)
• sort merge(обращение, обращение)

Natural означает, что делается полный перебор записей таблицы. Для каждой записи проверяются условия, которые можно проверить сразу. Если таблица - первая в плане и если запись не отброшена, то для неё выбираются подходящие записи из следующей таблицы плана (по индексам или нет, зависит от того, как описана та таблица), и т. д. Обычно такой режим если и применяется, то только к одной таблице в плане. При нескольких таблицах без индексов выгоднее sort merge.

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

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

Order означает, что данная таблица будет перебрана вся, но в порядке, заданном указанным индексом. Имеются не совсем проверенные подозрения, что order может искать не с начала таблицы, а с заданного значения, по указанному индексу. Образуется обычно при употреблении в select конструкции order by, но может применяться и для осуществления Merge.

Join означает переборный метод соединения. Эффективным он может быть лишь тогда, когда в результате образуется небольшое количество подходящих друг другу записей. Об этом было сказано и ещё будет подробно обсуждаться. Тем не менее, это - любимый способ соединения, применяемый оптимизатором InterBase.

Merge - слияние, способ однопроходного соединения без физической сортировки. Обращения, служащие аргументами должны быть заранее отсортированными в нужном порядке, то есть где-то в их дебрях должен быть order. В некоторых случаях, когда упорядоченность записей возникает в результате тонких и хитрых эффектов, interbase может это не понять и отказаться выполнять непосредственное слияние, хотя теоретически оно и возможно. Этот способ быть эффективным на небольших и средних по объёму соединениях при наличии хороших индексов и отсутствии сильных посторонних условий в запросе.

Sort Merge - способ однопроходного соединения с предварительной сортировкой. Обращения, служащие аргументами, могут быть получены любым путём и не обязаны быть отсортированными. Эффективно для больших по объёму соединений, но требует создания временных копий соединяемых таблиц, которые часто не умещаются в памяти и поэтому сохраняются сервером во временный файл на диске. Подробности процесса описаны в разделе про сортировку.

Однако, как я говорил в самом начале, нельзя вечно полагаться на автоматическое планирование. К счастью, interbase даёт возможность полностью управлять всеми своими способностями. Синтаксис оператора select в InterBase позволяет в некоторых указывать вручную план запроса. Правда, практика показывает, что эффективные с точки зрения здравого смысла планы эта штуковина иногда отказывается воспринимать под разными предлогами, или без оных. Сама конструкция имеет вид:

select ... from ... where ... group by ... having ... 

plan выражение_для_плана order by ...

create table t(x integer, y integer, z integer);

В этом случае запрос "select * from t order by y" будет использовать индекс по t(y), а запрос "select * from t where z=77 order by y" - уже никак, если поиск использует индекс по t(z). По логике, эта задача решаема за один проход (то есть практически мгновенно), если создать индекс по t(z,y). В этом случае можно по индексу быстро выйти на диапазон записей, у которых z=77 и этот диапазон сразу же окажется перечислен в заданном порядке. Однако такой ход уже выше понимания InterBase. Вместо этого InterBase найдёт индекс, у которого самое старшее поле - z, по нему выберет нужные записи, отсортирует их физически (вхолостую), и только потом начнёт выдавать клиенту.

Ещё один неприятный фактор, который можно продемонстрировать уже даже на таком примитивном примере - это замедление работы в результате наличия дополнительных индексов. Да, именно так! Сделав несколько раз create index с одинаковыми определениями, но разными именами, можно существенно замедлить обращения. По крайней мере в interbase 4.X. И не только по обновлению, как может показаться на первый взгляд. Дело в том, что обнаружив несколько индексов, содержащих в старшей части нужные поля, InterBase бросается параллельно читать их все. В некоторых случаях это имеет смысл, но далеко не всегда. Так же нужно заметить, что более поздние версии interbase страдают этим делом всё меньше и меньше.

То есть если в вышеприведённом примере есть индексы t(y), t(y,z), t(y,x), то поиск может пойти в три раза медленнее, чем при наличии только t(y)! Что, правда, легко поправимо руками.
Далее, ещё один немаловажный вопрос: уж коль скоро серверу приходится так часто физически сортировать наборы записей, то что именно он сортирует - сами записи или ссылки на них (выдавая записи клиенту по ссылкам). Оказывается, что именно физические записи. Допустим, что:

create table t1(x integer not null primary key, y integer);
create table t2(z integer, x integer not null references t1(x));
create view v1(z, x, y) as select t2.z, t2.x, t1.y from t1, t2 where t1.x = t2.x;

И допустим, что мы пишем следующую пару запросов:

1. select z from t2 order by z
2. select z from v1 order by z

Другой недостаток представлений InterBase состоит в том, что в них нельзя пользоваться такими конструкциями, как union и plan. С планом, в принципе, всё понятно, он реально формируется только при обращении к представлению и сильно зависит от контекста, в котором идёт обращение к этому представлению. Но вот запрет на объединение - это вообще-то гадость существенная. Первые шаги по её преодолению я видел лишь в interbase 6.0 Beta.

Пример плана для представления:

select *
from v1

plan join (v1 t1 index (rdb$primary1), v1 t2 index (rdb$foreign2))

order by x;

Далее в связи с представлениями я не могу не упомянуть такую штуку, как distinct. Ни в коем случае нельзя применять его внутри представлений, если хотите сохранить производительность. Вообще-то во многих практических случаях уникальность либо вытекает из ограничений уникальности, наложенных на хранимые таблицы, либо легко может быть получена проходом по индексам. InterBase, как обычно, не способен ни на то, ни на другое. Когда он встречает select distinct (или, что почти то же самое - select count(distinct ...)), он просто копит результирующие записи то ли в отсортированном, то ли в проиндексированном виде, избегая таким образом добавления дубликатов и по окончании запроса выдаёт пользователю то, что получилось в отсортированном же виде (если не задан другой явный порядок через order by, который в данном случае приведёт к пересортировке).

А теперь представьте, что запрос select distinct написан в представлении, а оно включено в другой запрос. Как поступит InterBase? Очень просто - вычислит подзапрос с distinct, а потом, трактуя его как таблицу, вычислит внешний запрос. Замечу, неиндексированную таблицу. И никакие планы тут не помогут. Дело в том, что distinct надёжно заизолирует подзапрос и сослаться извне на него станет уже невозможно, а изнутри его никак не задашь - представление всё-таки. В общем, у меня ни один из вариантов не прошёл. Реальный способ вычисления такого представления останется на выбор InterBase, а он известно, как это делает. Причём, даже если включить опцию Display Query Plan, то ISQL всё равно не сознается, какой применён план. К счастью, в большинстве случаев можно вынести distinct на уровень внешнего запроса и распланировать всё, как положено.

В общем, отсюда мораль: обращайтесь с представлениями осторожнее.

Что касается union, то в подзапросах, входящих в него, планы использоваться можно, но именно как в подзапросах. Указать, как осуществлять сам union, невозможно. Похоже, что у interbase есть только два способа для его осуществления. Просто union делается подобно distinct - сортировкой множества записей с удалением дубликатов, а union all - простым копированием двух результирующих множеств в одно место (хорошо, если напрямую клиенту).

Далее - будьте осторожны с агрегатными функциями (SUM(), AVG(), MIN(), COUNT(), ...). Дело в том, что ни для одной из этих функций InterBase 4.х не применяет индексы. Они всегда подсчитываются полным перебором всех выбранных записей. Разумеется, это не так худо, как перебор всех хранимых записей, но при больших размерах выборки время получается отвратительным. Особенно не рекомендуются такие функции во вложенных коррелированных подзапросах и в группировках с большим количеством групп. Хотя там они обычно и нужны. InterBase 5.х уже умеет искать по индексам MIN() и MAX(). Борланд заявляет, что, мол, "это была ошибка в 4.х и мы её исправили". Хотя теоретически по индексу можно вычислить все агрегатные функции SQL, причём быстрее, чем по хранимым таблицам. Правда, степень улучшения зависит от функции и состояния данных.

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

Далее - будьте осторожны с таким "удобствами" SQL, как between, containing, like и т. п. Почти все подобные словарные" конструкции (за исключением разве что starting with в отдельных случаях при точном сравнении) не используют индексы и решают свою задачу методом полного перебора. Даже between! То есть "select * from t1 where x >= 5 and x <= 500" пойдёт гораздо быстрее, чем "select * from t1 where x between 5 and 500". И если ISQL скажет, что в обоих случаях используется индекс - не верьте, посмотрите на статиситку обращений к диску.