|
Sybase
|
|
|
|
|
|
|
Продукты
|
|
|
|
|
|
|
Сообщества
|
|
|
|
|
|
|
Меню
|
|
|
|
|
|
|
Вход
|
|
|
|
|
|
|
Рассылка
|
|
|
|
|
|
|
Статистика
|
|
|
|
|
|
|
Все что мы знаем об индексах
|
Все что мы знаем об индексахНазначение
Итак, что же такое индексы ? Индексы - это сбалансированные деревья значений указанных в индексе полей и ссылки на физические записи в таблице. Индексы являются полноценными данными и занимают место в базе данных на ее страницах. Индексы позволяют ускорить работу выполнения запросов в сотни раз и сразу находить нужные данные, вместо того, чтобы последовательно читать всю таблицу и сравнивая каждую запись по указанному условию (такой метод поиска информации называется TABLE SCAN). При выполнении запроса оптимизатор запросов руководствуется двумя характеристиками при вычислении лучшего способа получения информации - это статистика (STATISTICS) и существующие индексы (INDEX). Если по статистике оптимизатор видит, что в запросе скорее всего будет обрабатываться только часть данных из таблицы и что существует подходящий индекс, по которому можно сразу определить нужные записи, то он воспользуется самым удобным и наиболее точно указывающим положение записей индексом.
Хранение индексов
На данный момент может быть 3 способа хранения индексов, которые автоматически определяются ASA во время создания индекса:
"B-Tree" - хранение значений. Используется, если длина полей индекса не превышает 10 байт.
"Hash B-Tree" - хранение значений как хэш кодов. Используется, если индекс составной, его длина больше одной восьмой от размера страницы БД или же больше, чем 256 байт.
"Compressed B-Tree" - хранение значений в сжатом (упакованном) виде. Используется, если в индексе присутствуют поля, у которых переменная длина.
Типы индексов
Индексы могут быть уникальными (UNIQUE INDEX) и не уникальными (INDEX). Уникальные индексы гарантируют, что в таблице не будет повторяющихся значения по указанным в ним полям. Это позволяет контролировать целостность данных и посредством уникальных индексов накладывать ограничения собственной бизнес-логики. Так же уникальные индексы позволяют ASA эффективно реализовывать механизм блокировок, снимая кол-во блокировок и нагрузку по их обработке во время операция вставок и изменения записей в таблице. Исходя из этого рекомендуется на каждую таблицу иметь хотя бы один уникальный индекс. Как уникальные, так и не уникальные индексы могут включать в себя поля, которым разрешено содержать пусто значение (NULL). Уникальные индексы не будут считать одинаковыми две записи с NULL значениями, так как NULL никогда не равен NULL.
Автоматически создаваемые индексы
1. ASA автоматически создает уникальные индексы для первичных ключей (PRIMARY KEY) и ограничений уникальности (CONSTRAINT UNIQUE). Оба вида этих индексов не разрешают использование NULL полей. В случе, если Вы попытаетесь создать их на NULL поле, ASA автоматически его сделает, как NOT NULL. Если же Вы хотите организовать контроль за уникальностью данных с использованием NULL полей, то вместо CONSTRAINT UNIQUE нужно создать UNIQUE INDEX.
2. Так же ASA автоматически создает индексы на ограничения связи между таблицами (FOREIGN KEY). В таких ограничениях разрешается иметь NULL поля.
Кластерные индексы
Из существующих индексов на таблицу один можно сделать кластерным. Это означает, что ASA будет стараться физически заполнять саму таблицу согласно порядку следования указанного в качестве кластерного индекса. Это позволит при выполнении запроса выборки к таблице производить меньшее кол-во чтений таблицы и сразу считывать подходящие под условия записи последовательным чтением, чем выдергивать их из разных страниц базы данных. В отличие от других СУБД, в ASA наличие кластерного индекса не гарантирует, что все записи таблицы будут отсортированны в нужном порядке. Существование кластерного индекса указывает только на то, что по мере возможности, при добавлении новых записей, СУБД будет стараться размещать их поближе к записям с похожими значениями. Можно сказать таблица будет фрагментирована и состоять из кусков последовательных записей. Для того, чтобы наиболее избежать фрагментации таблицы нужно учесть, как часто, много и в какой последовательности будут добавляться в нее данные, а так же как часто изменяться и в каких обьемах. Для управления дефрагментацией стоит использовать аттрибут резервирования пустого места на страницах (PCTFREE). Для физической дефрагментации необходимо перегрузить данные таблицы (LOAD/UNLOAD) или же перестроить ее (REORGANIZE TABLE).
Рассмотрим случаи различного использования таблицы с большим кол-вом данных:
1. Данные только добавляются - в данном случае резервировать свободное пространство на страницах смысла нет, можно указать PCTFREE 0. В случае, если порядок добавляемых данных не совпадает с порядком кластерного ключа таблицы, необходимо через определенные промежутки времени дефрагментировать таблицу.
2. Данные обновляются/удаляются в небольших кол-вах по сравнению с общим кол-вом записей в таблице - необходимо посмотреть на максимально допустимый размер записи (Maximum table width) и размер страниц БД, затем установить PCTFREE в такое значение, чтобы на странице в среднем хватало свободного места для размещения или переразмещения среднего кол-ва записей, которое могло бы затребовано сервером у заполненной другими записями страницы. В данном случае точную цифру PCTFREE назвать сложно и все зависит от логики работы с таблицей.
3. Данные обновляются/удаляются больших обьемах - использование кластерного индекса будет неэффективным, а фрагментация таблицы очень сильной. ASA будет терять время на попытки оптимального размещения данных, которые могут быть снова изменены в любой момент.
Резюмируя информацию о кластерных индексах можно сказать, что эффективней всего они подходят для таблиц, содержащих архивные данные, которые почти не изменяются и к которым в основном дописывается новая информация. Периодически такие таблицы необходимо дефрагментировать.
Из чего могут состоять индексы
Индексы могут быть:
А. Простыми, то есть индексировать только по указанному полю.
Б. Составными, то есть индексировать по нескольким указанным полям. В таком случае очень важен порядок следования полей в индексе и при неправильном порядке вполне возможно, что этот индекс просто никогда не будет использоваться оптимизатором запросов. Составление таких индексов мы рассмотрим в данной статье чуть позже.
Так же индексы могут делаться на вычисляемые поля (в ASA вычисляемые поля на самом деле физически храняться в базе данных и занимают место, как и обычные поля). Такие индексы будут использоваться оптимизатором запросов не только при обращении к вычисляемому полю в запросе, но и при выявлении выражения, аналогичному в вычисляемом поле. Например на поле f_date существует вычисляемое поле f_year, значение которого вычисляется по выражению Year(f_date) и на это поле сделан индекс IDX1. В обоих ниже приведенных запросах оптимизатором запросов будет задействован этот индекс:
SELECT *
FROM Table
WHERE f_Year = 2004;
SELECT *
FROM Table
WHERE Year(f_Date) = 2004;
Плюсы индексов
1. Ускорение работы запросов на выборку
2. Произведение операций блокировок с меньшими затратами
3. Организация физического упорядочивания данных в таблице
Минусы индексов
1. Замедление работы запросов на добавление, обновление и удаление информации из за того, что при этих операциях ASA будет вынуждена достраивать индексы
2. Увеличение размера базы данных и затрат на сопровождение БД сервером (это выделение страниц в базе данных, распределение индексов в кэшах и т.д.)
3. Увеличение времени на перегрузку данных из за полного перепостроения индексов
Необходимость создания индексов
С учетом автоматически создаваемых индексов можно сказать, что таблица уже по минимуму снабжена необходимыми индексами:
PRIMARY KEY и CONSTRAINT UNIQUE/INDEX - позволяют быстро находить нужные записи по их уникальному ключу. FOREIGN KEY позволяет эффективно производить соединения между таблицами (KEY JOIN). Пока запросы на выборку не станут тормозить, ни в коем случае не следует создавать собственные индексы. В данном случае лучше всего руководствоваться принципом "Ничего не трогать, пока все работает". Даже если Вы решите, что создав индекс Вы еще ускорите выполнение запроса, это ничего не даст, так как оптимизатор скорее всего предпочтет воспользоваться автоматически создаваемыми индексами. Как итог - вы только увеличите место в базе данных и замедлите операции изменения данных. Так что создавать индексы нужно только в случае, когда действительно начались проблемы с производительностью в запросах на выборку.
Смотрим на план запроса
После выявления "тормозящего" запроса, которое можно сделать через профайлер хранимых процедур, дебаггер или же просто путем его вычисления, необходимо посмотреть на план запроса через ISQL. К сожалению я не могу здесь привести полную расшифровку различных вариантов планов запросов (для этого нужна отдельная статья, если не книга), но на что стоит обратить внимание:
1. Для таблиц используется алгоритм TABLE SCAN
2. Для таблиц используются индексы, при сканирование которых накладываются дополнительные правила фильтрации записей уже по записям таблицы (PREDICATE)
3. Для таблиц используются индексы, при сканирование которых используются не все поля индекса
Так же настораживающим показателем в плане запроса служат использование различных хэширующих алгоритмов (HASH), фильтров (FILTER), подзапросов (Subquery) и внешних соединений (LOOP JOIN).
Решаем, кто виноват
Если мы определили, что у нас плохой план запроса, то не стоит сразу же начинать на все поля подряд создавать индексы. Ничему особо это не поможет. Начинать нужно впервую очередь не с индексов, а с рассмотрения эффективности соединения таблиц и фильтров в запросе. Очень часто именно по этим причинам оптимизатор запросов окончательно запутывается во всех соединениях и начинает строить плохие планы запросов. Так что впервую очередь нужно посмотреть, а правильно ли соединяются таблицы, а нельзя ли избавиться от подзапросов и внешних соединений. Хочу отметить, что конечно можно все это пропустить и, начиная с 9-ой версии, в ASA предусмотрено навязывание индексов (FORCE INDEX), но лично я считаю это дурным тоном, так как в ASA достаточно сообразительный оптимизатор запросов и достаточно гибкие способы соединения таблиц в WatcomSQL, позволяющие правильно указать оптимизатору их способы соединения, но предоставить ему самому право выбора использования необходимых алгоритмов соединения, фильтрации и индексов. В отличие от навязывание индексов правильное построение соединения таблиц гарантирует, что вне зависимости от данных в базе данных, оптимизатор будет выбирать наиболее эффективный алгоритм их обработки.
Приведу пример, в котором вместо того, чтобы построить на таблицу индекс, который все равно был бы неэффективен и почти не ускорял работу выборки, я перепланировал соединения таблиц и получил существенный выигрыш в скорости и красивый план запроса. Итак, в базе данных, в результате постоянного добавления данных в таблицы, стал заметно подтормаживать следующий запрос:
SELECT cc.Contract_id, Sum(ov.Value) * co.PercentValue / 100
FROM X_Contract_List cc
INNER JOIN sp_get_ContractCalcObject_Inc (@CalcDate) co
ON cc.Contract_id = co.Contract_id AND
co.CalcObject_id = @CalcObject_id AND
cc.CalcDate BETWEEN co.CreateDate AND co.c_CloseDate
INNER JOIN CalcMaskObject mo
ON co.CalcMask_id = mo.CalcMask_id AND
cc.CalcDate BETWEEN mo.CreateDate AND mo.CloseDate
INNER JOIN CalcValue_Inc ov
ON ov.CalcObject_id = mo.CalcObject_id AND
cc.Contract_id = ov.Contract_id AND
ov.CalcDate = @CalcDate
WHERE cc.CalcDate = @CalcDate
GROUP BY cc.Contract_id, co.PercentValue;
В данном запросе использовались:
X_Contract_List
- список договоров, подлежащих расчету - 1000 записей
sp_get_ContractCalcObject_Inc (@CalcDate)
- хранимая процедура, возвращающая параметры премий договоров на указанный расчетный месяц (в ее теле используется запрос, состоящий еще из 2 таблиц) - 1000 записей
CalcMaskObject
- справочник групп начислений, на которые ссылаются премии договоров. т.е. в каждой премии указанно, с каких начислений она берется и с каким процентом - 164 записи
CalcValue_Inc
- архив начислений. в нем храняться на каждый расчетный месяц и каждый договор выставленные для него начисления - 20 000 записей
По плану запроса я увидел, что оптимизатор запросов применил индексы к таблицам с малым кол-вом записей. Для больших таблиц sp_get_ContractCalcObject_Inc и CalcValue_Inc он наложил TABLE SCAN с соединением их с маленькими таблицами посредством построения хэш таблицы (HASH TABLE и HASH FILTER). По логике вещей нужно было взять список договоров к расчету, получить для них премии, выявить начисления которые в них входят и суммировать их значения. По плану же запросов вышло, что брались все начисления из архива начислений, далее на них строилась хэш таблица и используя индекс у CalcMaskObject выявлялись все начисления, которые в ней существуют, далее полученный результат проверялся по индексу в списке договоров, через TABLE SCAN и HASH FILTER соединялся с sp_get_ContractCalcObject_Inc и только потом проводилась группировка и аггрегирующая функция Sum.
При таком положении вещей ни один индекс не мог бы изменить план запроса, оптимизатор явно не понял, что от него хотят и продолжал бы соединять таблицы таким образом. Я не стал эксперементировать с индексами раньше времени и изменил сам текст запроса, переписав соединения его таблиц по другому:
SELECT cc.Contract_id,
ov.SumValue * co.c_PercentValue / 100
FROM X_Contract_List cc
INNER JOIN sp_get_ContractCalcObject_Inc (@CalcDate) co
ON cc.Contract_id = co.Contract_id AND
co.CalcObject_id = @CalcObject_id AND
cc.CalcDate BETWEEN co.CreateDate AND co.c_CloseDate,
LATERAL (
SELECT IsNull(Sum(i.Value), 0) AS SumValue
FROM CalcValue_Inc i
WHERE i.Contract_id = cc.Contract_id AND
i.CalcDate = cc.CalcDate AND
i.CalcObject_id IN (
SELECT CalcObject_id
FROM CalcMaskObject mo
WHERE co.CalcMask_id = mo.CalcMask_id AND
cc.CalcDate BETWEEN mo.CreateDate AND mo.CloseDate AND
mo.IsActive = 1 )
) AS ov
WHERE cc.CalcDate = @CalcDate
Применив алгоритм внутренного соединения подзапроса (LATERAL), который появился в ASA 9.01, фактически я сказал оптимизатору: возьми список договоров к расчету и все премии, которые на них существуют, а потом для каждой полученной записи рассчитай сумму начислений, которые входят в группу начислений, выставленной в премии договора. План запроса стал совершенно другим: изчезли TABLE SCAN и HASH таблицы, все таблицы были соединенны в нужном порядке и в качестве индексов были задействованны автоматически создаваемые индексы PRIMARY KEY и FOREIGN KEY используемых в запросе таблиц. Запрос стал работать менее секунды и как оказалось необходимости создавать собственные индексы совсем не было.
Выборочность индексов
Для оптимизатора запросов существует такое понятие, как выборочность индексов. Это означает, что при построении плана запроса и выбора наилучшего индекса оптимизатор оценивает по статистике диапазон его значений и выбирает тот индекс, который наиболее сужает круг поиска записей.
Например, есть таблица, в которой миллионом записей. В таблице описано 2 поля, на каждое из которых создан индекс. Поле IsComplete имеет тип BIT и может принимать значения 0 или 1. Другое поле Value имеет значение INT и хранит в себе некие целые числа. В запросе на таблицу указан фильтр: "IsComplete = 0 AND Value BETWEEN 1000 AND 2000".
Рассмотрим ситуацию, когда в таблице 1000 записей имеют значение у поля IsComplete = 0 и 20000 записей имеют значение у поля Value в пределах от 1000 до 2000. Оптимизатор предпочтет использовать индекс по полю IsComplete, так как по нему он сразу ограничит выборку до 1000 записей. Далее он на него наложит предикат "Value BETWEEN 1000 AND 2000" и просмотрев в таблице эти записи, отфильтрует подходящие под условие. Если же переставить условие в запросе на "IsComplete = 1 AND Value BETWEEN 1000 AND 2000", то сложилась бы ситуация с точностью до наоборот: оптимизатор просмотрел бы индекс по полю Value, таким образом просканировав всего 20000 записей, вместо 999000.
Примечание: Даже, если бы в таблице не существовало бы индекса на поле Value, то оптимизатор все равно при таком условие не стал бы пользоваться индексом по полю IsComplete и вместо этого полностью просканировал всю таблицу (TABLE SCAN), так как не имеет смысла читать 999000 записей из индекса и потом еще считывать их с таблицы, для наложения фильтра по полю Value. Если же например, условие "IsComplete=1" обхватовало примерно 50% от общего кол-ва записей, то оптимизатор при построение плана запроса примерно просчитал время затраченное на полное чтение таблицы и время, затраченное на чтение индекса и половины от кол-ва записей таблицы и
в зависимости от результата выбирал между полным сканированием таблицы и поиском с использованием индекса. В случаях, если индекс является кластерным и процент фрагментации таблицы небольшой, оптимизатор скорее всего отдаст предпочтение такому индексу.
Разветвленность составных индексов
Когда используются составные индексы, состоящие из множества полей, они имеют такую характеристику, как разветвленность. Чем больше значений имеет поле индекса, тем больше веток имеет индекса, а значит он занимает больше страниц в базе данных, требует большего времени на чтение и меньше кэшируется.
Вложенность уровней индексов
С учетом того, что индексы храняться как деревья, то структура их хранения такая:
Первая страница индекса, называемая как корневая страница, указывает на на одну или более страниц, содержащих ветки более нижнего уровня. Каждая из таких веток далее указывает на страницы более нижнего уровня. И так далее, пока не будет достигнут конец самого нижнего уровня, страницы которого называются страницами листа. Примерно это будет выглядеть примерно так:
[fixed]
-- Корневые страницы уровня 1
-- Страницы уровня уровня 2
-- Страницы уровня уровня 3
-- Страницы листа (ссылки на физические записи)
[/fixed]
В итоге, чтобы найти нужную запись, СУБД будет вынуждена просмотреть и найти значение в дереве уровня 1, далее спуститься и обработать уровень 2, затем сделать то же самое с уровнем 3 и только потом получить ссылку на физическую запись в таблице. В итоге, можно сказать, чем меньше уровней страниц в индексе, тем быстрее он считывается и большее кол-во страниц оседает в кэше для повторного использования. На кол-во уровней в индексе влияет размер страницы, длина используемых полей и разветвленность индекса. Посмотреть на кол-во уровней в индексах можно через системную хранимую процедуру "sa_index_levels".
Влияние булевых операций сравнения на выборки по индексам
Можно назвать 3 типа булевых операций при наложении фильтров на выборку из таблицы: строгие, накладывающие и отрицающие.
К строгим операциям относятся операторы "AND", "=" и "IN". С точки зрения производительности это самые выгодные операторы. Если в фильтре на таблицу используются только эти операторы, то оптимизатор запросов сразу же начинает искать на таблицу индекс, который наиболее оптимально подходит под условия выборки.
"AND" - при использовании условий на множество полей оптимизатор будет отдавать предпочтение составным индексам, если они обхватывают множество полей, по которым проводиться оптимизация.
"=" - для операции строго сравнения оптимизатору достаточно в подходящем индексе найти одно нужное значение. Это одна из самых быстрых операций. Если оператор "AND" не используется, то оптимизатор отдаст предпочтение простым индексам, так как для определения найденных записей в составных индексов ему придеться спускаться по всем уровням вложенности вниз, до страниц листов, в отличие от простого индекса, в котором уровень страниц листов идет непосредственно следующим за найденной страницей.
"IN" - для операции сравнения из списка оптимизатор организует виртуальную таблицу "IN" и использовав с ней прямое соединение выберет все нужные записи. Это достаточно быстрая операция, хотя по стоимости она чуть больше, чем операция равенства. Для оператора "IN" справедливы такие же правила выбора индекса, что и оператора "=".
К накладывающим операциям относятся операторы "OR", "<", ">", "<=", ">=", "BETWEEN". При использовании этих операторов вместе со строгими операторами эффективность последних теряется.
"OR" - если этот оператор используется только применимо к одному полю таблицы с условиями равенства, например, "Field1 = 1 OR Field1 = 2", то оптимизатор запросов автоматически преобразует условие сравнения в оператор "IN", приравнивая в данном случае этот оператор до уровня строгого оператора. В случае, если этот оператор используется применимо к полю и переменной (или же к DETERMINISTIC функции), например, "Field1 = 1 OR @Var = 2", то при выполнении запроса оптимизатор запроса проверит на истинность условие сравнения с переменной. Если условие истинно, то оптимизатор исключит из запроса все условие с оператором "OR". В противном случае оптимизатор уберет оператор "OR" и условие сравнения с переменной, оставив только оператор сравнения с полем. В любых других случаях использования оператора "OR", оптимизатор запросов будет всегда использовать алгоритм сканирования таблицы, что приведет к значительным затратам на выполнение запроса. В данном случае будет целесообразней вместо "OR" разбить запрос на серию аналогичных запросов, соединенных через "UNION ALL", где на каждый запрос накладывается условие из части условия "OR":
-- Неэффективный запрос
SELECT *
FROM Table1
WHERE Field1 = 1 OR Field2 = 1;
-- Эффективный запрос
SELECT *
FROM Table1
WHERE Field1 = 1
UNION ALL
SELECT *
FROM Table1
WHERE Field2 = 1;
"<", ">", "<=", ">=" - при использовании этих операторов оптимизатор предпочтет искать по индексу, если найдется подходящий. Однако в случае использования составного индекса, оптимизатор запросов не сможет использовать поля индекса, которые идут за полем, на которое указанна такая операция:
CREATE INDEX idx_1 ON Table1 (Field1, Field2);
SELECT *
FROM Table1
WHERE Field1 <= 100 AND Field2 = 1
В этом примере оптимизатор запросов использует индекс idx_1, сканируя по нему только все подходящие под условия Field1, далее спуститься по уровням дерева индекса вниз до страниц листов, получит ссылки на физические записи и уже по ним отфильтрует условие "Field2 = 1". В данном случае, если поле Field2 имеет большую выборочность запроса (не путать с выборочностью индекса), т.е. охватывает большую часть записей таблицы, то эффективнее использовать индекс только по полю Field1:
CREATE INDEX idx_2 ON Table1 (Field1);
Это позволит оптимизатору просмотреть по индексу все подходящие записи для Field1 и выбрать из с таблицы, попутно фильтруя по условию для Field2. Если же Field2 имеет малую выборочность запроса, то наиболее подходящим будет составной индекс:
CREATE INDEX idx_2 ON Table1 (Field2, Field1);
В данном случае оптимизатор в этом индексе сначала проведет поиск по условию для Field2 и уже спускаясь ниже по уровням индекса проведет поиск по Field1.
"BETWEEN" - данный оператор автоматически преобразуется оптимизатором из формы "Field BETWEEN MinValue AND MaxValue" в форму "Field >= MinValue AND Field <= MaxValue" и обрабатывается уже по правилам для операторов последней формы.
К отрицающим операциям относятся операторы "NOT", "!=" (или "<>").
"NOT" - при обработке запроса оптимизатор будет пытаться развернуть этот оператор в обратное условие, если это будет возможно. Например запрос:
SELECT *
FROM Table1
WHERE NOT (Field1 > 100);
будет преобразован оптимизатором в запрос:
SELECT *
FROM Table1
WHERE Field1 <= 100;
Если преобразование будет невозможным, то оптимизатор будет всегда использовать полное сканирование таблицы.
"!=" - при обработке запроса оптимизатор всегда для использовать полное сканирование таблицы. Таким образом оператор неравенства является самым дорогостоящим с точки зрения оптимизатора запроса.
Порядок определения следования полей в составных индексах
Умея читать план запроса уже всегда можно примерно сказать, какие поля стоит проиндексировать. Однако оптимизатор ASA умеет во время выполнения запроса использовать только один индекс на таблицу. Соответствующе если по плану запроса видно, что желательно проиндексировать множество полей, то не имеет смысла на каждое поле создать отдельный индекс, так как оптимизатор воспользуется только одним из них, наилучшем с его точки зрения. В таких ситуациях нужно создавать составной индекс, в который и включать все необходимые поля. Однако при проектирование такого индекса очень важным может оказаться порядок перечисления в нем полей, иначе вполне возможна ситуация, когда оптимизатор, при построении плана запроса, не задействует этот индекс или же при его обработке задействует только часть полей, несмотря на то, что казалось бы этот индекс указывает все поля, которые используются в запросе для соединения и фильтрации записей. Частично задействованные индексы хорошо видны в плане запросов, где на таблицу стоит использование составного индекса, по первым полям которого идет поиск, а на остальные стоит пропуск (*), с дальнейшим использованием предикатов по этим полям. Чем нужно руководствоваться при создании составных индексов:
1. Первыми ставить поля, обладающие лучшей выборочностью индекса.
2. Первыми ставить поля, для которых будет меньшая разветвленность.
3. Стремиться к минимальному числу уровней индекса, стараться избегать использования в составных индексах большого кол-ва полей и длинных полей. Главная задача индекса - это ограничить круг выбираемых записей до приемлевого по скорости уровня выборки, а не обхвата значений всех полей, использующихся в выборке.
4. Помнить о правилах булевых операций для оптимизатора и первыми в индексе ставить поля, на которых в операциях выборок идет сравнение на равенство (=) и вхождение в список значений (IN). Поля, фильтрующиеся по интервалам периодов значений (>, <, >=, <=, BETWEEN) нужно ставить в конец списка полей индекса. Если на несколько полей используются такие операции, то в индексе необходимо указать только одно из них, так как оптимизатор ASA не будет использовать далее идущие в индексе поля. Для выбора наиболее оптимального такого поля следует так же руководствоваться пунктами 1, 2 и 3.
Экспериментируем без последствий
Даже обладая достаточным запасом знаний по принципам работы оптимизатора запросов с индексами, все равно нельзя точно предсказать, насколько будет эффективным индекс по тем или иным полям. В данном случае действует правило "Пока не попробуешь, не узнаешь". Однако накладно пробовать создавать индексы по различным полям очень большой таблицы, что занимает время и место в базе данных. Чтобы избежать такой ситуации в 9-ой версии ASA появилась возможность примерно оценивать эффективность планируемого индекса без его фактического создания. Для этой цели были введены виртуальные индексы, при создании которых они не создаются физически в базе данных, существуют только на время действии создавшей их сессии, не используются оптимизатором при выполнении запросов, но учитываются и показываются в плане запроса. Например:
CREATE VIRTUAL INDEX v_idx_1 ON Table1 (Field1, Field2, Field3);
CREATE VIRTUAL INDEX v_idx_2 ON Table1 (Field2, Field3);
CREATE VIRTUAL INDEX v_idx_3 ON Table1 (Field3, Field1, Field2);
SELECT *
FROM Table1
WHERE Field3 = 100 AND Field1 IN (1, 2) AND Field2 > 100;
Посмотрев план запроса мы увидели, что оптимизатор предпочел в нем использовать индекс "v_idx_3", как наиболее подходящий. Теперь можно легко закончить с экспериментами и физически создать индекс на таблицу в базе данных:
CREATE INDEX idx_1 ON Table1 (Field3, Field1, Field2);
Все созданные виртуальные индексы автоматически удалятся после завершения сессии. Так же их можно удалить оператором "DROP INDEX".
|
Дата публикации: Monday 27 March 2006 18:51:17
Материал прочитан: 11765 раз(а)
[ Назад ] |
|
|
|
|
|
|
