Негативний вплив індексів на продуктивність INSERT, UPDATE та DELETE у Firebird SQL

Загалом, індекси необхідні для будь-якої серйозної бази даних, оскільки вони критично важливі для прискорення запитів із реченням WHERE (SELECT, UPDATE, MERGE, DELETE тощо). Однак кожен індекс має свою ціну, і ця ціна стає очевидною, коли ми порівнюємо швидкість операцій INSERT/UPDATE/DELETE для індексованих та неіндексованих полів.

У цій статті ми продемонструємо вплив індексу з кількома унікальними ключами на швидкість операцій.

Тестова база даних

Створімо просту базу даних Firebird:

CREATE DATABASE “E:\TESTFIREBIRDINDEX.FDB” USER “SYSDBA” PASSWORD “masterkey”;
CREATE TABLE TABLEIND1 (
    I1            INTEGER NOT NULL PRIMARY KEY,
    NAME          VARCHAR(250),
    MANORWOMAN  SMALLINT
);
CREATE GENERATOR G1;
SET TERM ^ ;

create or alter procedure INS1MLN
returns (
    INSERTED_CNT integer)
as
BEGIN
inserted_cnt = 0;
WHILE (inserted_cnt <1000000) DO
BEGIN
 Insert into tableind1(i1, name, manorwoman) values(gen_id(g1,1), 'TEST name', (:inserted_cnt - (:inserted_cnt/2)*2));
 inserted_cnt=inserted_cnt+1;
END
suspend;
END^
SET TERM ; ^
GRANT INSERT ON TABLEIND1 TO PROCEDURE INS1MLN;
GRANT EXECUTE ON PROCEDURE INS1MLN TO SYSDBA;
COMMIT;

Щоб продемонструвати проблему з поганими індексами, виконаймо такі операції:

• Спочатку вставимо 1 мільйон записів у таблицю
• Потім оновимо ці записи
• Після цього видалимо всі записи
• І, нарешті, виконаємо SELECT count(*) з таблиці

Наведені нижче команди виконують описані операції:

set stat on;  /*enable display of statistics in isql*/
select * from ins1mln;
update tableind1 SET MANORWOMAN  = 3;
delete from tableind1;
select count(*) from tableind1;

Запустімо їх і збережемо результати для подальшого аналізу.

Після цього створімо іншу таблицю з такою самою структурою - і додамо індекс для стовпця MANORWOMAN

CREATE INDEX TABLEIND1_IDX1 ON TABLEIND1 (MANORWOMAN);

Як видно вище у скрипті, ми вставляємо в цей стовпець лише цілі значення 0 або 1. Такий індекс є марним для запитів SELECT, і теоретично всі розробники баз даних мали б уникати таких поганих індексів (за винятком дуже особливого випадку з незбалансованим розподілом значень у таблиці), але на практиці існує багато індексів із 2 унікальними значеннями або навіть з одним із них.

Потім повторімо скрипт із цим індексом і порівняймо результати - див. наступну таблицю:

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

Отримаймо статистику для цієї прикладної бази даних (з поганим індексом для MANORWOMAN) і спробуймо знайти деякі деталі. Щоб зібрати статистику, виконаймо таку команду:

gstat -r e:\testfirebirdindex.fdb > e:\teststat.txt

Розділ статистики таблиці TABLEIND1 та індексів виглядає інтригуюче, але яку корисну інформацію він нам дає?

TABLEIND1 (128)
    Primary pointer page: 166, Index root page: 167
    Average record length: 0.00, total records: 1000000
    Average version length: 27.00, total versions: 1000000, max versions: 1
    Data pages: 16130, data page slots: 16130, average fill: 93%
    Fill distribution:
       0 - 19% = 1
      20 - 39% = 0
      40 - 59% = 0
      60 - 79% = 0
      80 - 99% = 16129

    Index RDB$PRIMARY1 (0)
      Depth: 3, leaf buckets: 1463, nodes: 1000000
      Average data length: 1.00, total dup: 0, max dup: 0
      Fill distribution:
           0 - 19% = 0
          20 - 39% = 0
          40 - 59% = 0
          60 - 79% = 1
          80 - 99% = 1462

    Index TABLEIND1_IDX1 (1)
      Depth: 3, leaf buckets: 2873, nodes: 2000000
      Average data length: 0.00, total dup: 1999997, max dup: 999999
      Fill distribution:
           0 - 19% = 0
          20 - 39% = 1
          40 - 59% = 1056
          60 - 79% = 0
          80 - 99% = 1816

Щоб зрозуміти значення показаних чисел і відсоткових значень, ми можемо скористатися HQbird Database Analyst, який пропонує візуальну інтерпретацію статистики бази даних:

Натиснувши Reports/View recommendations, ми можемо знайти відповідне пояснення для цього індексу:

Bad indices count: 1. By `bad` we name indices with many duplicate keys (90% of all keys) and big groups of equal keys (30% of all keys). Big groups of equal keys slowdown garbage collection - MaxEquals here is % of max groups of keys having equal values. Index search for such an index is not efficient. You can drop such indices (if they are not on FK constraints).

Index ( Relation) Duplicates MaxEquals TABLEIND1.IDX1 (TABLEIND1) : 100%, 50%

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

Якщо HQbird Database Analyst виділяє індекси як «Useless» (тобто вони мають лише 1 значення), такі індекси рекомендується негайно видалити, якщо це можливо.

Якщо індекс виділено як Bad (там є кілька значень), його також слід розглянути для видалення, але з обережністю: можливо, поганий індекс використовується в якомусь конкретному SQL-запиті, який потребує певної комбінації індексів (включно з поганим), щоб працювати швидко.

Звісно, ці SQL-запити слід переписати, щоб використовувати більш оптимальний план виконання без поганого індексу. Щоб знайти такі запити, слід провести аудит SQL-планів: найпростіший спосіб - запустити сеанс трасування з HQbird PerfMon і ввімкнути журналювання SQL-планів, а потім шукати SQL-плани для конкретних поганих індексів.