Impacto negativo de los índices en el rendimiento de INSERT, UPDATE y DELETE en Firebird SQL

En general, los índices son necesarios para cualquier base de datos seria, ya que son críticos para acelerar las consultas con cláusula WHERE (SELECT, UPDATE, MERGE, DELETE, etc.). Sin embargo, cada índice tiene un costo, y el costo es evidente cuando comparamos la velocidad de las operaciones INSERT/UPDATE/DELETE en campos indexados y no indexados.

En este artículo, demostraremos el impacto del índice con algunas claves únicas en la velocidad de las operaciones.

Base de datos de prueba

Creemos la base de datos Firebird simple:

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 demostrar el problema con los índices malos, realicemos las siguientes operaciones:

• Primero, insertamos 1 millón de registros en la tabla
• Luego actualizamos estos registros
• Después, eliminamos todos los registros
• Y, finalmente, ejecutamos SELECT count(*) de la tabla

Los comandos a continuación realizan las operaciones descritas:

set stat on;  /*habilitar visualización de estadísticas en isql*/
select * from ins1mln;
update tableind1 SET MANORWOMAN  = 3;
delete from tableind1;
select count(*) from tableind1;

Ejecutémoslo y guardemos los resultados para un análisis posterior.

Después, creemos otra tabla con la misma estructura y agreguemos el índice para la columna MANORWOMAN:

CREATE INDEX TABLEIND1_IDX1 ON TABLEIND1 (MANORWOMAN);

Como puede ver arriba en el script, insertamos solo valores enteros 0 o 1 en esta columna. Tal índice es inútil para consultas SELECT y, en teoría, todos los desarrolladores de bases de datos deberían evitar tales índices malos (excepto en casos muy especiales con una distribución desequilibrada de valores en la tabla), pero en la práctica hay muchos índices con 2 valores únicos o incluso con 1 de ellos.

Luego repitamos el script con este índice y comparemos los resultados - vea la siguiente tabla:

Entonces, un índice malo disminuye el rendimiento aproximadamente 2 veces al insertar o actualizar. También podemos ver que un índice no óptimo aumenta considerablemente el número de escrituras y lecturas de registros.

Obtengamos estadísticas para esta base de datos de muestra (con el índice malo para MANORWOMAN) e intentemos encontrar algunos detalles. Para recopilar estadísticas, ejecutamos el siguiente comando:

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

La sección de estadísticas de la tabla TABLEIND1 y sus índices parece intrigante, pero ¿qué información útil nos proporciona?

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 el significado de los números y valores porcentuales mostrados, podemos usar HQbird Database Analyst, que ofrece una interpretación visual de las estadísticas de la base de datos:

Al hacer clic en Informes/Ver recomendaciones, podemos encontrar la explicación adecuada 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%

En bases de datos de producción, a menudo podemos ver muchos índices malos que pueden afectar enormemente el rendimiento de la base de datos. En este ejemplo, podemos ver una tabla con 13 millones de registros que tiene 7 índices malos, que (muy probablemente) son inútiles y disminuyen considerablemente el rendimiento de Firebird:

Si HQbird Database Analyst resalta índices como «Useless» (es decir, que tienen solo 1 valor), se recomienda eliminarlos de inmediato, si es posible.

Si el índice se resalta como Bad (con pocos valores), también se debe considerar su eliminación, pero con precaución: es posible que el índice malo se use en alguna consulta SQL particular que requiera la combinación específica de índices (incluido el malo) para ejecutarse rápido.

Ciertamente, estas consultas SQL deben reescribirse para usar un plan de ejecución más óptimo sin un índice malo. Para encontrar tales consultas, se debe realizar una auditoría de los planes SQL: la forma más fácil es ejecutar una sesión de seguimiento con HQbird PerfMon y habilitar el registro de planes SQL, y luego buscar planes SQL para los índices malos específicos.