Negative Auswirkungen von Indizes auf die INSERT-, UPDATE- und DELETE-Leistung in Firebird SQL

Grundsätzlich sind Indizes für jede ernsthafte Datenbank notwendig, da sie entscheidend sind, um Abfragen mit WHERE-Klausel (SELECT, UPDATE, MERGE, DELETE usw.) zu beschleunigen. Allerdings hat jeder Index seinen Preis, und dieser Preis wird deutlich, wenn wir die Geschwindigkeit von INSERT/UPDATE/DELETE-Operationen auf indizierten und nicht indizierten Feldern vergleichen.

In diesem Artikel werden wir die Auswirkung des Index mit einigen eindeutigen Schlüsseln auf die Geschwindigkeit der Operationen demonstrieren.

Testdatenbank

Erstellen wir die einfache Firebird-Datenbank:

Code

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;

Um das Problem mit schlechten Indizes zu demonstrieren, führen wir die folgenden Operationen durch:

Zuerst fügen wir 1 Million Datensätze in die Tabelle ein
Dann aktualisieren wir diese Datensätze
Danach löschen wir alle Datensätze
Und schließlich führen wir SELECT count(*) aus der Tabelle aus

Die folgenden Befehle führen die beschriebenen Operationen aus:

Code

set stat on;  /*Statistikanzeige in isql aktivieren*/
select * from ins1mln;
update tableind1 SET MANORWOMAN  = 3;
delete from tableind1;
select count(*) from tableind1;

Lassen Sie uns dies ausführen und die Ergebnisse für die weitere Analyse aufbewahren.

Danach erstellen wir eine weitere Tabelle mit derselben Struktur - und fügen dort den Index für die Spalte MANORWOMAN hinzu:

Code

CREATE INDEX TABLEIND1_IDX1 ON TABLEIND1 (MANORWOMAN);

Wie Sie oben im Skript sehen können, fügen wir in diese Spalte nur die Integer-Werte 0 oder 1 ein. Ein solcher Index ist für SELECT-Abfragen nutzlos, und theoretisch sollten alle Datenbankentwickler solche schlechten Indizes vermeiden (außer in sehr speziellen Fällen mit einer unausgewogenen Verteilung der Werte in der Tabelle), aber in der Praxis gibt es viele Indizes mit 2 eindeutigen Werten oder sogar nur mit einem.

Dann wiederholen wir das Skript mit diesem Index und vergleichen die Ergebnisse - siehe folgende Tabelle:


Ohne Index für MANORWOMAN	Mit Index für MANORWOMAN
SQL> set stat on; /Statistiken anzeigen/ SQL *> select from ins1mln; INSERTED_CNT ============ 1000000 Current memory = 10487216 Delta memory = 80560 Max memory = 12569996 Elapsed time= 13.33 sec Buffers = 2048 Reads = 0 Writes 18756 Fetches = 7833503 SQL > update tableind1 SET MANORWOMAN = 3; Current memory = 76551788 Delta memory = 66064572 Max memory = 111442520 Elapsed time= 15.04 sec Buffers = 2048 Reads = 16166 Writes 15852 Fetches = 6032307 SQL> delete from tableind1; Current memory = 76550240 Delta memory = -1548 Max memory = 111442520 Elapsed time= 3.27 sec Buffers = 2048 Reads = 16147 Writes 16006 Fetches = 5032277 SQL> select count() from tableind1; COUNT ============ 0 Current memory = 76552064 Delta memory = 1824 Max memory = 111442520 Elapsed time= 1.35* sec Buffers = 2048 Reads = 16021 Writes 1791 Fetches = 2032278	SQL> set stat on; /Statistiken anzeigen/ SQL> *select from ins1mln; INSERTED_CNT ============ 1000000 Current memory = 10484140 Delta memory = 75524 Max memory = 12569996 Elapsed time= 23.94 sec Buffers = 2048 Reads = 1 Writes 23942 Fetches = 11459599 SQL> update tableind1 SET MANORWOMAN = 3; Current memory = 76548712 Delta memory = 66064572 Max memory = 111439444 Elapsed time= 29.30 sec Buffers = 2048 Reads = 16167 Writes 19492 Fetches = 10035948 SQL> delete from tableind1; Current memory = 76547164 Delta memory = -1548 Max memory = 111439444 Elapsed time= 3.41 sec Buffers = 2048 Reads = 16147 Writes 15967 Fetches = 5032277 SQL> select count() from tableind1; COUNT ============ 0 Current memory = 76548988 Delta memory = 1824 Max memory = 111439444 Elapsed time= 0.69* sec Buffers = 2048 Reads = 16021 Writes 1901 Fetches = 2032278

Ohne Index für MANORWOMAN

Mit Index für MANORWOMAN

SQL> set stat on; /*Statistiken anzeigen*/
SQL > select * from ins1mln;
INSERTED_CNT
============
1000000
Current memory = 10487216
Delta memory = 80560
Max memory = 12569996
Elapsed time= 13.33 sec
Buffers = 2048
Reads = 0
Writes 18756
Fetches = 7833503
SQL > update tableind1 SET MANORWOMAN = 3;
Current memory = 76551788
Delta memory = 66064572
Max memory = 111442520
Elapsed time= 15.04 sec
Buffers = 2048
Reads = 16166
Writes 15852
Fetches = 6032307
SQL> delete from tableind1;
Current memory = 76550240
Delta memory = -1548
Max memory = 111442520
Elapsed time= 3.27 sec
Buffers = 2048
Reads = 16147
Writes 16006
Fetches = 5032277
SQL> select count(*) from tableind1;
COUNT
============
0
Current memory = 76552064
Delta memory = 1824
Max memory = 111442520
Elapsed time= 1.35 sec
Buffers = 2048
Reads = 16021
Writes 1791
Fetches = 2032278

SQL> set stat on; /*Statistiken anzeigen*/
SQL> select * from ins1mln;
INSERTED_CNT
============
1000000
Current memory = 10484140
Delta memory = 75524
Max memory = 12569996
Elapsed time= 23.94 sec
Buffers = 2048
Reads = 1
Writes 23942
Fetches = 11459599
SQL> update tableind1 SET MANORWOMAN = 3;
Current memory = 76548712
Delta memory = 66064572
Max memory = 111439444
Elapsed time= 29.30 sec
Buffers = 2048
Reads = 16167
Writes 19492
Fetches = 10035948
SQL> delete from tableind1;
Current memory = 76547164
Delta memory = -1548
Max memory = 111439444
Elapsed time= 3.41 sec
Buffers = 2048
Reads = 16147
Writes 15967
Fetches = 5032277
SQL> select count(*) from tableind1;
COUNT
============
0
Current memory = 76548988
Delta memory = 1824
Max memory = 111439444
Elapsed time= 0.69 sec
Buffers = 2048
Reads = 16021
Writes 1901
Fetches = 2032278

Ein schlechter Index verringert also die Leistung beim Einfügen oder Aktualisieren um etwa das Zweifache. Außerdem sehen wir, dass ein nicht optimaler Index die Anzahl der Schreibvorgänge und Datensatzabrufe erheblich erhöht.

Lassen Sie uns Statistiken für diese Beispieldatenbank (mit dem schlechten Index für MANORWOMAN) abrufen und versuchen, einige Details zu finden. Um Statistiken zu sammeln, führen wir den folgenden Befehl aus:

Code

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

Der Abschnitt mit den Statistiken für die Tabelle TABLEIND1 und ihre Indizes sieht faszinierend aus, aber welche nützlichen Informationen gibt er uns?

Code

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

Um die Bedeutung der angezeigten Zahlen und Prozentwerte zu verstehen, können wir HQbird Database Analyst verwenden, der eine visuelle Interpretation der Datenbankstatistiken bietet:

Durch Klicken auf Berichte/Empfehlungen anzeigen finden wir die passende Erklärung für diesen Index:

Anzahl schlechter Indizes: 1. Mit `schlecht` bezeichnen wir Indizes mit vielen doppelten Schlüsseln (90% aller Schlüssel) und großen Gruppen gleicher Schlüssel (30% aller Schlüssel). Große Gruppen gleicher Schlüssel verlangsamen die Garbage Collection - MaxEquals ist hier der Prozentsatz der maximalen Gruppen von Schlüsseln mit gleichen Werten. Die Indexsuche für einen solchen Index ist nicht effizient. Sie können solche Indizes entfernen (wenn sie nicht auf FK-Einschränkungen liegen).

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

In Produktionsdatenbanken sehen wir oft viele schlechte Indizes, die die Datenbankleistung erheblich beeinträchtigen können. In diesem Beispiel sehen wir eine Tabelle mit 13 Millionen Datensätzen, die 7 schlechte Indizes hat, die (höchstwahrscheinlich) nutzlos sind und die Firebird-Leistung erheblich verringern:

Wenn HQbird Database Analyst Indizes als «Useless» (d. h. sie haben nur 1 Wert) hervorhebt, wird empfohlen, solche Indizes sofort zu entfernen, wenn möglich.

Wenn der Index als Bad (einige Werte vorhanden) hervorgehoben wird, sollte auch das Löschen in Betracht gezogen werden, jedoch mit Vorsicht: Es ist möglich, dass der schlechte Index in einer bestimmten SQL-Abfrage verwendet wird, die die spezifische Kombination von Indizes (einschließlich des schlechten) benötigt, um schnell ausgeführt zu werden.

Diese SQL-Abfragen sollten sicherlich umgeschrieben werden, um einen optimaleren Ausführungsplan ohne einen schlechten Index zu verwenden. Um solche Abfragen zu finden, sollte ein Audit der SQL-Pläne durchgeführt werden: Der einfachste Weg ist, eine Trace-Sitzung mit HQbird PerfMon auszuführen und die SQL-Planprotokollierung zu aktivieren, und dann die SQL-Pläne nach den spezifischen schlechten Indizes zu durchsuchen.