Negatieve impact van indexen op de INSERT-, UPDATE- en DELETE-prestaties in Firebird SQL

In het algemeen zijn indexen noodzakelijk voor elke serieuze database, omdat ze cruciaal zijn om query’s met een WHERE-clausule (SELECT, UPDATE, MERGE, DELETE, etc.) te versnellen. Elke index heeft echter zijn prijs, en die prijs wordt duidelijk wanneer we de snelheid van INSERT/UPDATE/DELETE-bewerkingen op geïndexeerde en niet-geïndexeerde velden vergelijken.

In dit artikel demonstreren we de impact van de index met een paar unieke sleutels op de snelheid van bewerkingen.

Testdatabase

Laten we een eenvoudige Firebird-database maken:

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;

Om het probleem met slechte indexen te demonstreren, voeren we de volgende bewerkingen uit:

• Eerst voegen we 1 miljoen records in de tabel in
• Vervolgens werken we deze records bij
• Daarna verwijderen we alle records
• En tot slot voeren we SELECT count(*) uit op de tabel

De onderstaande opdrachten voeren de beschreven bewerkingen uit:

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

Laten we dit uitvoeren en de resultaten bewaren voor verdere analyse.

Daarna maken we een andere tabel met dezelfde structuur - en voegen daar de index voor de MANORWOMAN-kolom aan toe:

CREATE INDEX TABLEIND1_IDX1 ON TABLEIND1 (MANORWOMAN);

Zoals u hierboven in het script kunt zien, voegen we in deze kolom alleen de integerwaarden 0 of 1 in. Zo’n index is nutteloos voor select-query’s, en in theorie zouden alle databaseontwikkelaars zulke slechte indexen moeten vermijden (behalve in zeer speciale gevallen met een onevenwichtige verdeling van waarden in de tabel), maar in de praktijk zijn er veel indexen met 2 unieke waarden of zelfs met 1 ervan.

Laten we vervolgens het script herhalen met deze index en de resultaten vergelijken - zie de volgende tabel:

Dus een slechte index vermindert de prestaties met ongeveer 2 keer bij het invoegen of bijwerken. We zien ook dat een niet-optimale index het aantal schrijfbewerkingen en record-fetches aanzienlijk verhoogt.

Laten we statistieken ophalen voor deze voorbeelddatabase (met de slechte index voor MANORWOMAN) en proberen details te vinden. Om statistieken te verzamelen, voeren we de volgende opdracht uit:

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

De sectie met statistieken van de TABLEIND1-tabel en indexen ziet er intrigerend uit, maar welke nuttige informatie geeft het ons?

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

Om de betekenis van de getoonde getallen en percentagewaarden te begrijpen, kunnen we HQbird Database Analyst gebruiken, die een visuele interpretatie van databasestatistieken biedt:

Door op Reports/View recommendations te klikken, vinden we de juiste uitleg voor deze index:

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%

In productiedatabases zien we vaak veel slechte indexen, die de databaseprestaties sterk kunnen beïnvloeden. In dit voorbeeld zien we een tabel met 13 miljoen records met 7 slechte indexen, die (hoogstwaarschijnlijk) nutteloos zijn en de Firebird-prestaties sterk verminderen:

Als HQbird Database Analyst indexen markeert als «Useless» (d.w.z. ze hebben slechts 1 waarde), wordt aanbevolen om dergelijke indexen onmiddellijk te verwijderen, indien mogelijk.

Als de index is gemarkeerd als Bad (een paar waarden), moet ook worden overwogen om deze te verwijderen, maar met voorzichtigheid: het is mogelijk dat een slechte index wordt gebruikt in een specifieke SQL-query die de specifieke combinatie van indexen (inclusief de slechte) vereist om snel te kunnen draaien.

Deze SQL-query’s moeten zeker worden herschreven om een optimaler uitvoeringsplan zonder een slechte index te gebruiken. Om dergelijke query’s te vinden, moet een audit van SQL-plannen worden uitgevoerd: de eenvoudigste manier is om een trace-sessie uit te voeren met HQbird PerfMon en SQL-planlogboekregistratie in te schakelen, en vervolgens SQL-plannen te doorzoeken op de specifieke slechte indexen.