Негативни утицај индекса на перформансе 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;

Да бисмо демонстрирали проблем са лошим индексима, извршимо следеће операције:

• Прво, убацујемо милион записа у табелу
• Затим ажурирамо те записе
• Након тога, бришемо све записе
• И, на крају, покрећемо 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 јединствене вредности или чак са 1 од њих.

Затим поновимо скрипту са овим индексом и упоредимо резултате - погледајте следећу табелу:

Дакле, лош индекс смањује перформансе приближно 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 планова: најлакши начин је покренути trace сесију са HQbird PerfMon и омогућити евидентирање SQL планова, а затим претражити SQL планове за специфичне лоше индексе.