Impacto negativo dos índices no desempenho de INSERT, UPDATE e DELETE no Firebird SQL

Em geral, os índices são necessários para qualquer banco de dados sério, pois são essenciais para acelerar consultas com cláusula WHERE (SELECT, UPDATE, MERGE, DELETE, etc). No entanto, todo índice tem um custo, e esse custo fica evidente quando comparamos a velocidade das operações INSERT/UPDATE/DELETE em campos indexados e não indexados.

Neste artigo, demonstraremos o impacto do índice com poucas chaves únicas na velocidade das operações.

Banco de dados de teste

Vamos criar um banco de dados Firebird simples:

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;

Para demonstrar o problema com índices ruins, vamos realizar as seguintes operações:

• Primeiro, inserimos 1 milhão de registros na tabela
• Em seguida, atualizamos esses registros
• Depois, excluímos todos os registros
• E, finalmente, executamos SELECT count(*) na tabela

Os comandos abaixo realizam as operações descritas:

set stat on;  /*habilita a exibição de estatísticas no isql*/
select * from ins1mln;
update tableind1 SET MANORWOMAN  = 3;
delete from tableind1;
select count(*) from tableind1;

Vamos executar e guardar os resultados para análise posterior.

Depois disso, vamos criar outra tabela com a mesma estrutura - e adicionar o índice para a coluna MANORWOMAN:

CREATE INDEX TABLEIND1_IDX1 ON TABLEIND1 (MANORWOMAN);

Como você pode ver acima no script, inserimos apenas valores inteiros 0 ou 1 nesta coluna. Esse índice é inútil para consultas SELECT e, em teoria, todos os desenvolvedores de banco de dados deveriam evitar esses índices ruins (exceto em casos muito especiais com distribuição desequilibrada de valores na tabela), mas na prática existem muitos índices com 2 valores únicos ou até mesmo com apenas 1 deles.

Então, vamos repetir o script com esse índice e comparar os resultados - veja a tabela a seguir:

Portanto, um índice ruim diminui o desempenho em aproximadamente 2 vezes durante inserções ou atualizações. Além disso, podemos ver que um índice não otimizado aumenta significativamente o número de gravações e buscas de registros.

Vamos obter estatísticas para este banco de dados de exemplo (com o índice ruim para MANORWOMAN) e tentar encontrar alguns detalhes. Para coletar estatísticas, executamos o seguinte comando:

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

A seção de estatísticas da tabela TABLEIND1 e dos índices parece intrigante, mas que informações úteis ela nos fornece?

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

Para entender o significado dos números e valores percentuais mostrados, podemos usar o HQbird Database Analyst, que oferece interpretação visual das estatísticas do banco de dados:

Ao clicar em Relatórios/Ver recomendações, podemos encontrar a explicação apropriada para este índice:

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%

Em bancos de dados de produção, muitas vezes podemos ver muitos índices ruins, que podem afetar muito o desempenho do banco de dados. Neste exemplo, podemos ver uma tabela com 13 milhões de registros que possui 7 índices ruins, que (provavelmente) são inúteis e diminuem muito o desempenho do Firebird:

Se o HQbird Database Analyst destacar índices como «Inúteis» (ou seja, eles têm apenas 1 valor), é recomendado removê-los imediatamente, se possível.

Se o índice for destacado como Ruim (poucos valores), também deve ser considerado para exclusão, mas com cautela: é possível que o índice ruim seja usado em alguma consulta SQL específica, que exigia a combinação específica de índices (incluindo o ruim) para executar rapidamente.

Certamente, essas consultas SQL devem ser reescritas para usar um plano de execução mais otimizado sem um índice ruim. Para encontrar tais consultas, deve ser feita uma auditoria dos planos SQL: a maneira mais fácil é executar uma sessão de rastreamento com o HQbird PerfMon e habilitar o registro de planos SQL, e então pesquisar nos planos SQL os índices ruins específicos.