Impatto negativo degli indici sulle prestazioni di INSERT, UPDATE e DELETE in Firebird SQL

In generale, gli indici sono necessari per qualsiasi database serio, poiché sono fondamentali per velocizzare le query con clausola WHERE (SELECT, UPDATE, MERGE, DELETE, ecc.). Tuttavia, ogni indice ha un costo, e il costo è evidente quando confrontiamo la velocità delle operazioni INSERT/UPDATE/DELETE su campi indicizzati e non indicizzati.

In questo articolo, dimostreremo l’impatto dell’indice con poche chiavi univoche sulla velocità delle operazioni.

Database di test

Creiamo il semplice database Firebird:

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;

Per dimostrare il problema con gli indici inefficienti, eseguiamo le seguenti operazioni:

Prima, inseriamo 1 milione di record nella tabella
Poi aggiorniamo questi record
Dopo, eliminiamo tutti i record
E, infine, eseguiamo SELECT count(*) dalla tabella

I comandi seguenti eseguono le operazioni descritte:

Code

set stat on;  /*abilita la visualizzazione delle statistiche in isql*/
select * from ins1mln;
update tableind1 SET MANORWOMAN  = 3;
delete from tableind1;
select count(*) from tableind1;

Eseguiamolo e conserviamo i risultati per ulteriori analisi.

Dopo, creiamo un’altra tabella con la stessa struttura - e aggiungiamo l’indice per la colonna MANORWOMAN

Code

CREATE INDEX TABLEIND1_IDX1 ON TABLEIND1 (MANORWOMAN);

Come potete vedere sopra nello script, inseriamo in questa colonna solo valori interi 0 o 1. Tale indice è inutile per le query di selezione e, in teoria, tutti gli sviluppatori di database dovrebbero evitare tali indici inefficienti (tranne casi molto particolari con distribuzione sbilanciata dei valori nella tabella), ma in pratica ci sono molti indici con 2 valori univoci o addirittura con 1 solo.

Poi ripetiamo lo script con questo indice e confrontiamo i risultati - vedi la tabella seguente:


Senza indice per MANORWOMAN	Con indice per MANORWOMAN
SQL> set stat on; /mostra statistiche/ 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; /mostra statistiche/ 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

Senza indice per MANORWOMAN

Con indice per MANORWOMAN

SQL> set stat on; /*mostra statistiche*/
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; /*mostra statistiche*/
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

Quindi, un indice inefficiente riduce le prestazioni di circa 2 volte durante l’inserimento o l’aggiornamento. Inoltre, possiamo vedere che un indice non ottimale aumenta notevolmente il numero di scritture e fetch di record.

Otteniamo le statistiche per questo database di esempio (con l’indice inefficiente per MANORWOMAN) e proviamo a trovare alcuni dettagli. Per raccogliere le statistiche, eseguiamo il seguente comando:

Code

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

La sezione delle statistiche della tabella TABLEIND1 e degli indici sembra intrigante, ma quali informazioni utili ci fornisce?

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

Per comprendere il significato dei numeri e dei valori percentuali mostrati, possiamo usare HQbird Database Analyst, che offre un’interpretazione visiva delle statistiche del database:

Facendo clic su Reports/View recommendations possiamo trovare la spiegazione appropriata per questo indice:

Bad indices count: 1. Con `bad` intendiamo indici con molte chiavi duplicate (90% di tutte le chiavi) e grandi gruppi di chiavi uguali (30% di tutte le chiavi). Grandi gruppi di chiavi uguali rallentano la garbage collection - MaxEquals qui è la percentuale dei gruppi massimi di chiavi con valori uguali. La ricerca tramite indice per tale indice non è efficiente. Puoi eliminare tali indici (se non sono su vincoli FK).

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

Nei database di produzione spesso possiamo vedere molti indici inefficienti, che possono influire notevolmente sulle prestazioni del database. In questo esempio possiamo vedere una tabella con 13 milioni di record che ha 7 indici inefficienti, che (molto probabilmente) sono inutili e riducono notevolmente le prestazioni di Firebird:

Se HQbird Database Analyst evidenzia gli indici come «Useless» (cioè hanno solo 1 valore), si consiglia di eliminarli immediatamente, se possibile.

Se l’indice è evidenziato come Bad (pochi valori), dovrebbe essere considerato per l’eliminazione anche in questo caso, ma con cautela: è possibile che l’indice inefficiente sia utilizzato in qualche query SQL particolare, che richiede la combinazione specifica di indici (incluso quello inefficiente) per essere eseguita rapidamente.

Certamente, queste query SQL dovrebbero essere riscritte per utilizzare un piano di esecuzione più ottimale senza un indice inefficiente. Per trovare tali query, dovrebbe essere eseguito un audit dei piani SQL: il modo più semplice è eseguire una sessione di trace con HQbird PerfMon e abilitare la registrazione dei piani SQL, quindi cercare nei piani SQL gli indici inefficienti specifici.