Предыдущую статью об индексах на основе B-дерева я заканчивал комментарием: "Не соглашайтесь с аргументом, что необходимо регулярно пересоздавать индексы, так как "они становятся разбалансированными". Это необоснованный аргумент". Немного позже я получил сообщение по электронной почте, в котором указывалось, что есть другие, обоснованные причины пересоздания индексов. Это верно, и пересоздание индексов на основе B-дерева иногда действительно дает преимущества, так что, я подумал, что имеет смысл написать небольшую статью, исследующую причины пересоздания индексов на основе B-дерева.
Зачем пересоздавать индекс?
Есть только две реальных причины пересоздавать индексы. Одна связана с ответом на вопрос: "Будет ли прирост производительности оправдывать затраты на пересоздание этого индекса?". Другая возникает при ответе на похожий вопрос: "Будет ли преимущество для администратора операвдывать затраты?".
Одна удобная особенность такой формулировки вопросов состоит в том, что, при нулевых фактических затратах, даже если пересоздание индекса является бесполезным упражнением, я не собираюсь настаивать на его пррекращении. Конечно, время и усилия, фактически, не могут быть нулевыми, и случается, что пересоздание индекса может вызвать последующие кратковременные проблемы производительности, так что продолжайте читать статью до конца.
Стандартный аргумент в пользу пересоздания индексов основан на том факте, что индексы на основе B-дерева имеют тенденцию к "вырождению" (т.е., становятся менее эффективны с точки зрения используемого пространства) по мере заполнения данными. И этот аргумент верен, но только потому, что стандартная теория предсказывает тенденцию заполнения индексов на основе B-дерева на 75 процентов, а сервер Oracle создает их, по умолчанию, с заполнением блоков на 90 процентов. (Фактически, Oracle не объединяет соседние мало используемые листовые блоки автоматически, поэтому упаковка часто будет на несколько процентов меньше, чем теоретическое значение). Поэтому вопрос пересоздания обычно обычно сводится к следующему неявному вопросу:
Если предполагается, что индекс работает с упаковкой на X процентов, надо ли пересоздавать его с упаковкой на 90 процентов?
Давайте начнем с рассмотрения значимости изменения заполнения с X процентов, прежде чем беспокоиться о значениях магического числа X.
Потенциальные преимущества
Когда вы пересоздаете индекс, физический эффект состоит в уменьшении размера индекса (особенно в уменьшении количества ключевых блоков). Это может привести к трем возможным преимуществам:
- Расчеты оптимизатора могут выдать меньшее значение стоимости использования индекса, поэтому оптимизатор может использовать индекс в больем количестве планов выполнения.
- Типичным запросам, использующим индекс, может потребоваться посетить меньше блоков индекса, и поэтому они будут выполняться более эффективно.
- Поскольку индекс меньше, он может дольше оставаться в цепочке LRU буферного кеша, так что блоки индекса будут находиться в буфере чаще и меньше блоков других объектов будут сбрасываться на диск, чтобы можно было повторно загрузить блоки индекса. В этом случае уменьшается объем ввода-вывода на уровне системы, что приводит к возможному повышению производительности для всех.
Поэтому давайте поочередно проверим каждый эффект и посмотрим, как выглядят различные преимущества в различных ситуациях. Мы начнем с расчетов стоимости оптимизатором и простого работающего примера.
Оценка стоимости оптимизатором
Если вы читали статью "Почему Oracle не использует мой индекс?!", то знакомы с формулой, исходно опубликованной Вольфгангом Брейтлингом (Wolfgang Breitling) на конференции IOUG-2002, которая дает общую оценку стоимости доступа к таблице по индексу как:
количество_уровней_b-дерева + избирательность * количество_листовых_блоков + избирательность * степень кластеризации.
Рассмотрим индекс по 10000000 строк, со средним размером записи 40 байтов. Это даст нам около 200 записей в листовом блоке при использовании блоков размером 8 Кбайт. Предположим, что индекс (в соответствии с некоей статистической информацией в представлении index_stats) работает с 50-процентной эффективностью и, поэтому, в соответствии с типичными предписаниями, является хорошим кандидатом на пересоздание (с принятым по умолчанию заполнением блоков на 90 процентов - но мы будем использовать в нашем примере 100 процентов). Другими словами, сейчас в листовом блоке индекса находится в среднем около 100 записей.
Давайте займемся арифметикой, сначала до пересоздания:
10000000 строк при 100 строках в листовом блоке => 100000 листовых блоков 100000 листовых блоков (при 50-процентной упаковке) => 1000 блоков ветвления уровня 1 1000 блоков ветвления уровня 1 (при 50-процентной упаковке) => 10 блоков ветвления уровня 2 10 блоков ветвления уровня 2 (при 50-процентной упаковке) => 1 блок ветвления уровня 3
Теперь, после пересоздания (со значением pctfree = 0):
10000000 строк при 200 строках на листовой блок => 50000 листовых блоков 50000 листовых блоков (при 100-процентной упаковке) => 250 блоков ветвления уровня 1 250 блоков ветвления уровня 1 (при 100-процентной упаковке) => 2 блока ветвления уровня 2 2 блока ветвления уровня 2 (при 100-процентной упаковке) => 1 блок ветвления уровня 3
Итак, индекс состоит из трех уровней как до, так и после пересоздания, но количество листовых блоков сократилось после пересоздания со 100000 до 50000. Говорит ли это нам что-нибудь о новой оценке стоимости? На самом деле, нет, хотя стоит учесть небольшую, но важную деталь: количество уровней b-дерева, являющееся одним из компонентов при расчете стоимости, при пересоздании индекса меняется редко. Экспоненциальная зависимость максимального количества строк от количества уровней b-дерева делает это практически неизбежным.
Нам надо учесть не только количество листовых блоков, но и все слагаемое стоимости: избирательность * количество_листовых_блоков. Насколько изменилась эта величина? Ну, это зависит от того, сколько строк соответствует каждому значению индекса. Давайте проверим пару значений, одно для "высокоточного" индекса (пять строк на значение), а другое - для индекса "качеством" пониже (50 строк на значение).
Строк на значение = 5 => избирательность = 1/2000000 Избирательность * количество_листовых_блоков (старое) = 100000/2000000 = 0,05 Избирательность * количество_листовых_блоков (новое) = 50000/2000000 = 0,025
Из-за округления (я уверен, что при вычислении по формуле в этот момент сервер Oracle выполняет округление), это слагаемое оценки стоимости не меняется.
Строк на значение = 50 => избирательность = 1/200000 Избирательность * количество_листовых_блоков (старое) = 100000/200000 = 0.5 Избирательность * количество_листовых_блоков (новое) = 50000/200000 = 0.25
Снова это слагаемое формулы не изменилось. Фактически, оно и не изменится, пока каждому значению ключа не будет соответствовать 100 строк таблицы - тогда предполагаемая стоимость в результате пересоздания индекса с упаковкой на 100 процентов вместо 50 уменьшится.
Итак, бывают случаи, когда пересоздание индекса изменит мнение оптимизатора о его полезности, но в вашей системе эти случаи могут быть весьма нечастыми.
Типичные запросы
Итак, что, если оптимизатор не осознает, что ваш индекс стал лучше; может, лучше начнут работать запросы конечных пользователей, которые уже используют этот индекс? Как обычно, важно знать особенности данных и приложения. Когда вы начинаете задумываться о пересоздании индекса "поскольку, судя по index_stats, он заполнен на 50 процентов", прежде всего, подумайте о том (например), что если эти 50 процентов означают, что половина индекса почти заполнена, а другая половина - почти пуста, то одно магическое число для всего индекса может и не давать никакой полезной информации. (Если точно ничего не известно, можно попытаться выполнить весьма ресурсоемкий treedump, чтобы уточнить детали).
Затем, когда вы разобрались, что означает это число, надо решить, даст ли пересоздание какой-то реально ощутимый эффект для ваших пользователей. В зависимости от способа использования таблицы и индекса, ответ может быть "нет".
Рассмотрим следующий пример. К таблице обращается "объектно-ориентированная" система, которая всегда использует бессмысленные числовые ключи. Каждый запрос использует значение ключа для сбора данных. Один проход вниз по индексу выбирает один идентификатор строки с листового блока, а затем - одну строку из таблицы. Пересоздание индекса, позволяющее упаковать 200 идентификаторов строк в листовой блок, ничего не дает, если вам нужна всего одна строка.
Как насчет примера ближе к другому концу спектра: "подчиненная" таблица, в которой одно значение внешнего ключа соответствует 100 строкам? Упаковка индекса означает, что вы посещаете в поисках этих идентификаторов строк один листовой блок вместо двух. Замечательно - разве что, эти 100 строк таблицы могут оказаться в 100 разных блоках данных, так что усилия по пересозданию индекса дадут для конечного пользователя эффект немногим менее одного процента. При принятии решения о том, стоит ли пересоздавать индекс, надо считать, особенно если по какой-то причине пересоздавать индекс неудобно.
Преимущества для буферизации
Понятно, что при наличии хорошо упакованного индекса, он, вероятно, будет меньше "загрязнять" буферный кеш - в индексе меньше блоков, так что весь индекс может поместиться в буферном кеше, а поскольку меньше блоков будут "выбивать" блоки с другими данными из буферного кеша, общий объем ввода-вывода в системе может уменьшиться. С другой стороны, хорошо упакованный индекс может вызвать лишние конфликты при вставках, изменениях и удалениях. Оценить подобные ситуации сложнее, но пересоздание индекса (секции), который предполагается использовать только для чтения, вероятно, имеет смысл.
Учтите, однако, три следующих соображения. Если этот индекс действительно популярен, то принцип LRU и счетчик количества обращений могут сохранять его в буферном кеше постоянно в любом случае, так что, хотя он и занимает "лишнее пространство" в буферном кеше, к дополнительному вводу-выводу это может и не приводить. Во-вторых, для многих запросов основная составляющая стоимсоти выполнения запроса - это стоимость чтения блоков таблицы, которые редко посещаются повторно и вызывают намного более агрессивный сброс буферов на диск, так что беспокойство об индексах может отвлекать вас от более насущной проблемы. Наконец, возможно, что, слишком часто пересоздавая индексы, вы усложняете проблему, а не решаете ее - можете выполнить следующий (сравнительно долго работающий; он может выполняться несколько минут) тест на системе с размером блока 8 Кбайтов, в табличном пространстве без использования ASSM , если вы работаете в Oracle 9:
drop table t1;
create table t1(n1 number(38));
create index i1 on t1(n1);
execute dbms_random.seed(0)
begin
for i in 1..400000 loop
insert into t1 values(
trunc(power(10,14) * dbms_random.value)
);
commit;
end loop;
end;
/
-- стандартное значение pctfree для пересоздания
alter index i1 rebuild pctfree 10;
begin
for i in 1..100000 loop
insert into t1 values(
trunc(power(10,14) * dbms_random.value)
);
commit;
end loop;
end;
/
analyze index i1 validate structure;
select lf_blks from index_stats;
Проверьте количество листовых блоков в индексе после пересоздания, а затем повторите эксперимент без пересоздания. Различие результатов может вас удивить. (В моем случае, после "повышающего производительность" пересоздания листовых блоков было 2227, но если не пересоздавать, то в индексе оказалось всего 1778 блоков).
Учтите, пожалуйста, что этот тест не доказывает (и не должен был доказывать), что пересоздавать индексы не стоит. Он только демонстрирует, что пересоздание может иметь неожиданный эффект. Бывают ситуации, в зависимости от особенностей данных, когда подобный эффект может произойти в критических подсекциях индекса вскоре после пересоздания. Я подчеркиваю, что вы всегда должны хорошо подумать, прежде чем решить пересоздавать индекс.
Напоминание
В этой статье я обсуждал индексы на основе B-дерева. С битовыми индексами ситуация другая, и я уже описал ее в различных статьях о битовых индексах, опубликованных ранее на сайте www.dbazine.com.
Я также игнорировал возможность пересоздания индексов (или, по крайней мере, секций индексов) со 100-процентным заполнением блоков (pctfree = 0) непосредственно перед переводом табличного пространства в режим только чтения. Даже в этом случае усилия могут оказаться неоправданными, если при пересоздании возникают проблемы (помните, что в ходе оперативного пересоздания индексов возникала достаточно серьезная ошибка, которая может быть и в последних версиях сервера Oracle).
Наконец, всегда есть индексы, которые из-за особенностей приложений, ведут себя просто катастрофически - всегда будут частные случаи , в которых регулярное пересоздание может оказаться хорошей идеей. Даже тогда, проверьте, не будет ли регулярное выполнение объединения (coalesce) лучше в краткосрочной перспективе, с изменением стратегии доступа на базе индексов по функции в качестве долговременного решения.
Вывод
Есть только один достойный аргумент в пользу пересоздания индекса:
Будут ли общие затраты на пересоздание индекса оправданы полученными преимуществами для системы?
Ответ на этот вопрос, зачастую, - громкое НЕТ. Фактически, иногда общее влияние пересоздания активного индекса будет губительным для системы. Однако, все еще есть много заблуждений относительно индексов, приводящих к тому, что АБД тратят ценное время и усилия на ненужные пересоздания индексов.
НО, если есть регулярный период, когда система не используется, есть простое пакетное задание, которое работает в этот период времени, не мешая работе других пакетных заданий, и задание это всегда срабатывает успешно - можете свободно пересоздавать все "безопасные" индексы, которые хотите; часто так можно добиться небольшого повышения производительности, а если оно вам ничего не стоит, вполне можно на это пойти.