InterBase: тормозология и глюконавтика
Страница 14. Как написать запрос с планом

Как написать запрос с планом

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

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

В качестве отправной точки лучше начать с написания в ISQL запроса без плана, не обращая внимание на эффективность. То есть прежде, чем решать, как именно должен отрабатываться запрос, нужно определиться с тем, что он должен делать. И определиться достаточно чётко, чтобы довести замысел до выражения SQL, делающего то, что требуется.

В некоторых случаях, когда используются компоненты, генерирующие запросы (типа TTable в Delphi) запрос нужно отловить с помощью средств мониторинга (SQL Monitor).

Далее нужно это самое выражение прогнать в Interactive SQL со включёнными показами планов, статистики и числа записей. Иногда, если запрос должен возвращать много записей бывает можно заменить "select поля" на "select count(*)" или "select distinct поле" на "select count(distinct поле)", чтобы избавиться от вывода самих данных и увидеть только характеристики запроса. Дело в том, что count в interbase работает методом перебора (по крайней мере в известных мне версиях), так что на планирование такая замена существенно не повлияет. Даже order by сохраняется внутри count(), как бы бессмысленно это ни выглядело.

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

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

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

Здесь, правда, есть множество возможностей сделать исключение из правила. Дело в том, что если через фильтрующие выражения проходит одна запись из 1000, то вторая стратегия может привести к перебору 1000 хранимых записей на каждую выданную. Это без учёта соединений и прочих спецэффектов. В таких случаях бывает лучше плюнуть на то, что сортировка расходует время и провернуть запрос по первой схеме.

Далее: если уж дело доходит до того, что нужно сортировать большой объём данных, то бывает полезным отсортировать не целиком записи, а только их ключи. То есть если запись целиком занимает 500 байт, а ключ+поле_сортировки занимают 50, то имеет смысл отсортировать только последних, взять, сколько надо записей из полученного списка, и лишь потом по ключам выбрать из базы их полные даные. Это позволит резко сократить объём сортируемых данных и, как следствие:

1. Повысит вероятность того, что interbase сможет отсортировать всё в памяти при первом же заходе.
2. Если памяти всё же не хватит, радикально сократит объём дискового ввода-вывода и потребность во временном дисковом пространстве.

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

• Основные таблицы. Они обычно содержат главные данные, необходимые в запросе, а так же ссылки (на справочники, подчинённые или родительские таблицы). Как правило, имеет смысл начинать план запроса именно со сканирования этих таблиц. Они же обычно бывают самыми большими. Конечная сортировка order by так же обычно делается по полям основных таблиц.
• Присоединяемые таблицы. Обычно это справочники, но могуть быть подчинённые или родительские таблицы, поля которых необходимо выводить или применять в вычислениях вместе с полями основных. Присоединение данных таблиц обычно не связано с фильтрацией исходного множества. В частности по-этому справочники довольно часто подсоединяются через outer join.
• Фильтрующие таблицы. Их иногда включают в запрос, чтобы отфильтровать его по наличию фильтрующих записей.

Если они выбираются целиком или почти целиком (исключаются лишь редкие записи), то следует интерактивную выборку следует основывать на индексном переборе той из основных таблиц, по полю которой идёт сортировка. При этом для грида придётся создать два индекса - в прямом и обратном направлении. То есть план будет начинаться примерно так: plan join (ГлавнаяТаблица order i_ГлавнаяТаблицаСортировка, Присоединяемая index (rdb$primary666), ...). То есть перебираем главную таблицу в заданном порядке, после чего к найденной записи подцепляем остальные по индексам. Индекс должен быть возрастающим или убывающим в зависимости от направления выборки (это обеспечивается в Монстре).

Ещё одно замечание: в процессе эволюции различных экземпляров базы системные индексы могут получать различные номера. Так что писать эти номера в запросах приложения достаточно опасно - после первого же перебэкапа запрос может отказать. Лучше воспользоваться специальной функцией, которая извлекает индекс по ключевым полям (в проекте "Архив" такая функция имеется в dmShareDatabase).

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

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

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

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

create table t1(x integer, y integer);

create index i_t1_x_y on t1(x, y);

create table t2(z integer);

create index i_t2_z on t2(z);

select ... from t1, t2 where t1.x = 333 and t1.y = t2.z

Очевидно, что если в последнем запросе отработать сначала фильтр по t1.x, по индексу i_t1_x_y, то полученный остаток от t1 будет упорядочен по y вследствие природы упомянутого индекса. А значит, его можно безо всяких сортировок слить с таблицей t2 по индексу i_t2_z. Это можно было бы выразить планом plan merge (t1 index (i_t1_x_y), t2 index (i_t2_x)). Но, как я сказал, не судьба. Похожий план пройдёт, если вместо merge написать join. Это будет означать, что первое условие отработает по старшей части индекса, причём информация о сортировке будет потеряна. Потом к каждой оставшейся записи t1 будут подсоединены записи t2 и здесь уже проблем с выбором индекса не будет. Вроде бы похожий процесс, но по каждой найденной записи t1 делается отдельный независимый проход по t2, от корня индекса и до самих записей. Очевидно, что диск будет дёргаться больше, да и вычислений отнюдь не уменьшится.