Salta el contingut

DDL - Índex

Un índex és un objecte que pot accelerar la recuperació de files gràcies a apuntadors interns. Els índexs poden crear-se explícitament o automàticament.

Si en una cerca no hi ha un índex per una columna del WHERE, es produeix una exploració completa de la taula, el que anomenem FULL ACCESS.

Un índex proporciona accés directe i ràpid a les files d'una taula.

La seva finalitat és reduir la necessitat d'E/S (entrada/sortida) de disc gràcies a una ruta d'accés indexada per buscar dades de forma ràpida.

L'índex l'utilitza i el manté directament el SGBDR. Una vegada l'hem creat no cal que fem cap altra actuació.

Els índexs són lògica i físicament independents de la taula que indexen.

Això significa que els podem crear o eliminar en qualsevol moment sense que afectin les taules base o altres índexs.

A l'eliminar una taula també s'eliminen els índexs corresponents.

Tipus d'índex

Podem crear dos tipus d'índexs:

  • Índex únic: Es crea automàticament quan creem una restricció de PRIMARY KEY o UNIQUE. També podem crear-los manualment sense associar-los a cap restricció.

  • Índex no únic: és el que solem crear per accelerar algunes consultes. El sistema en crea automàticament quan definim FOREIGN KEY.

Per crear índexs utilitzem la comanda CREATE INDEX.

CREATE [OR REPLACE] [UNIQUE|FULLTEXT|SPATIAL] INDEX
    [IF NOT EXISTS] index_name
    [index_type]
ON tbl_name (index_col_name,...)
    [WAIT n | NOWAIT]
    [index_option]
    [algorithm_option | lock_option] ...

Millorem la velocitat d'accés de consulta a la columna REGION_ID de la taula WF_COUNTRIES:

CREATE INDEX wf_cont_reg_id_idx
       ON wf_countries(region_id);

Quan creem un índex?

Crearem un índex només si:

  • La columna conté una àmplia varietat de valors

  • Una columna conté un gran nombre de valors nuls

  • Una o més columnes s'utilitzen amb freqüència en conjunt en una clàusula WHERE o un JOIN

  • La taula és gran i s'espera que la majoria de les consultes recuperen menys del 2% al 4% de les files.

Quan no creem un índex?

Hem de vigilar els índexos que creem ja que aquests ocupen espai a disc i a més suposen més feina en sentències de DML.

No crearem índex si cada operació DML (INSERT, UPDATE, DELETE) que realitzem en una taula amb índexs implica l'actualització d'aquests.

Com més gran sigui el nombre d'índexs associats en una taula, major serà l'esforç per actualitzar tots els índexs després de la operació DML.

Normalment no cal crear un índex quan:

  • La taula és petita

  • No utilitzem les columnes com a condició en les consultes

  • S'espera que la majoria de las consultes recuperin més del 2 al 4% de files de la taula

  • La taula s'actualitza amb freqüència

  • Es fa referència a les columnes indexades dins d'expressions o funcions.

Índexs compostos

Un índex compost és un índex sobre vàries columnes a la vegada.

Les columnes de l'índex compost poden aparèixer en qualsevol ordre i no cal que sigui el mateix que el que tenen dins la taula.

Un índex compost accelera la recuperació de les dades en sentències SELECT on la clàusula WHERE fa referència a totes o a la part inicial de les columnes de l'index compost.

CREATE INDEX emps_name_idx
       ON employees(first_name, last_name);

Els valors nuls no s'inclouen a l'índex compost.

Per optimitzar els JOIN, podem crear un índex a la columna FOREIGN KEY, que accelera la cerca de las files coincidents a la columna PRIMARY KEY. Alguns SGBDR ja ho fan automàticament.

L'optimitzador no utilitza un índex si la clàusula WHERE conté l'expressió IS NULL.

Diccionari de dades

La informació dels Índexs els trobem a la taula information_schema.STATISTICS.

Podem consultar, per exemple, els índexs existents a la taula EMPLOYEES de la base EMPRESA.

SELECT table_name, index_name,
       column_name, seq_in_index, non_unique
FROM information_schema.statistics
WHERE index_schema = 'empresa' AND table_name = 'employees'
ORDER BY 1, 2, 4;

Eliminació d'índex

Per eliminar un índex utilitzem la comanda DROP INDEX

Per eliminar un índex cal saber-ne el seu nom, per això és imporant posar noms que siguem capaços de reconèixer i/o saber consultar el diccionari de dades per a saber-ne el nom.

Índex sobre funcions

Alguns SGBDR, com Oracle, són capaços de crear índexs sobre funcions que apliquem en camps. En MariaDB això no és possible.

També és cert que, jugant amb el paràmetre Collation, podem fer que no es diferenciï entre majúscules i minúscules i per tant sigui més senzill fer les cerques sobre un índex amb aquesta propietat. De vegades a Oracle es creen índexs sobre UPPER(camp) per fer cerques.