Коли треба пересоздавать індекс?, Інші СУБД, Бази даних, статті

Попередню статтю про індекси на основі B-дерева я закінчував коментарем: “Не погоджуйтеся з аргументом, що необхідно регулярно пересоздавать індекси, оскільки” вони стають розбалансованими “. Це необгрунтований аргумент “. Трохи пізніше я отримав повідомлення по електронній пошті, в якому вказувалося, що є інші, обгрунтовані причини пересозданія індексів. Це вірно, і перестворення індексів на основі B-дерева іноді дійсно дає переваги, так що, я подумав, що має сенс написати невелику статтю, досліджує причини пересозданія індексів на основі B-дерева.


Навіщо пересоздавать індекс?


Є тільки дві реальних причини пересоздавать індекси. Одна пов’язана з відповіддю на питання: “Чи буде приріст продуктивності виправдовувати витрати на перестворення цього індексу? “. Інша виникає при відповіді на схоже запитання:” Чи буде перевага для адміністратора операвдивать витрати? “.


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


Стандартний аргумент на користь пересозданія індексів засновано на тому факті, що індекси на основі B-дерева мають тенденцію до “виродження” (тобто, стають менш ефективні з точки зору використовуваного простору) по мірі заповнення даними. І цей аргумент вірний, але тільки тому, що стандартна теорія передбачає тенденцію заповнення індексів на основі B-дерева на 75 відсотків, а сервер Oracle створює їх, за замовчуванням, із заповненням блоків на 90 відсотків. (Фактично, Oracle не об’єднує сусідні мало використовувані листові блоки автоматично, тому упаковка часто буде на кілька відсотків менше, ніж теоретичне значення). Тому питання пересозданія зазвичай зазвичай зводиться до наступного неявному питання:


Якщо передбачається, що індекс працює з упаковкою на X відсотків, чи треба пересоздавать його з упаковкою на 90 відсотків?


Давайте почнемо з розгляду значимості зміни заповнення з X відсотків, перш ніж турбуватися про значення магічного числа X.


Потенційні переваги


Коли ви перестворює індекс, фізичний ефект полягає в зменшенні розміру індексу (особливо у зменшенні кількості ключових блоків). Це може привести до трьох можливим переваг:



Тому давайте по черзі перевіримо кожний ефект і подивимося, як виглядають різні переваги в різних ситуаціях. Ми почнемо з розрахунків вартості оптимізатором і простого працюючого прикладу.


Оцінка вартості оптимізатором


Якщо ви читали статтю “Чому 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) Краще в короткостроковій перспективі, зі зміною стратегії доступу на базі індексів по функції в якості довгострокового рішення.


Висновок


Є тільки один гідний аргумент на користь пересозданія індексу:


Чи будуть загальні витрати на перестворення індексу виправдані отриманими перевагами для системи?


Відповідь на це питання, найчастіше, – гучне НІ. Фактично, іноді загальний вплив пересозданія активного індексу буде згубним для системи. Однак, все ще є багато помилок щодо індексів, призводять до того, що АБД витрачають цінний час і зусилля на непотрібні пересозданія індексів.


АЛЕ, якщо є регулярний період, коли система не використовується, є просте пакетне завдання, яке працює в цей період часу, не заважаючи роботі інших пакетних завдань, і завдання це завжди спрацьовує успішно– Можете вільно пересоздавать все “безпечні” індекси, які хочете; часто так можна добитися невеликого підвищення продуктивності, а якщо воно вам нічого не варто, цілком можна на це піти.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*