Índexs
Què és un índex i com funciona
Un índex és una estructura de dades auxiliar que el SGBD manté paral·lelament a les dades d'una taula, i que permet localitzar files de manera molt més ràpida sense haver d'escanejar tota la taula (full table scan o sequential scan).
La analogia clàssica és l'índex d'un llibre: en lloc de llegir tot el llibre per trobar una paraula, busques al final i t'indica exactament la pàgina. El cost és que mantenir l'índex del llibre actualitzat requereix esforç addicional — exactament igual que als SGBD.
Estructura interna: l'arbre B (B-tree)
El tipus d'índex per defecte en tots els motors principals és el B-tree (Balanced tree — arbre equilibrat). La seva propietat clau és que la profunditat de l'arbre és pràcticament igual per a qualsevol valor, garantint un cost de cerca O(log n).
graph TD
R["[50 | 75]"]:::root
N1["[20 | 35]"]:::internal
N2["[60 | 68]"]:::internal
N3["[80 | 90]"]:::internal
L1["[10, 15]"]:::leaf
L2["[20, 25, 30]"]:::leaf
L3["[35, 40, 45]"]:::leaf
L4["[50, 55, 58]"]:::leaf
L5["[60, 62, 65]"]:::leaf
L6["[68, 70, 72]"]:::leaf
L7["[75, 78]"]:::leaf
L8["[80, 85, 88]"]:::leaf
L9["[90, 95, 99]"]:::leaf
R --> N1
R --> N2
R --> N3
N1 --> L1
N1 --> L2
N1 --> L3
N2 --> L4
N2 --> L5
N2 --> L6
N3 --> L7
N3 --> L8
N3 --> L9
classDef root fill:#4a90d9,color:#fff,stroke:#2c5f8a
classDef internal fill:#7ab3e0,color:#fff,stroke:#4a7aa8
classDef leaf fill:#d4e8f7,color:#333,stroke:#7ab3e0Com funciona una cerca:
- El motor comença pel node arrel.
- Compara el valor buscat amb les claus del node i baixa per la branca corresponent.
- Arriba a un node fulla (leaf node) que conté el valor i un punter a la fila real de la taula (o directament les dades, en índexs clustered).
- El nombre de passos és
log₂(n): per a 1 milió de files, son aproximadament 20 comparacions en lloc d'1.000.000.
Inserció animada: com s'afegeix un valor al B-tree
[30] · fulla esquerra [10, 18, 22] (plena) · fulla dreta [40, 45]
Comparació: escaneig complet vs. índex B-tree
Tipus d'índexs
B-tree (per defecte)
El tipus d'índex universal. Suporta els operadors =, <, <=, >, >=, BETWEEN, IN, LIKE 'prefix%' (sense comodí inicial). És el tipus que es crea per defecte amb CREATE INDEX.
Adequat per a: columnes de clau primària, claus foranies, columnes usades en WHERE, ORDER BY i JOIN.
Hash
L'índex Hash emmagatzema un valor de dispersió (hash) de la clau. Les cerques d'igualtat (=) són O(1), però no suporta rangs ni ordenació.
-- SQL Server no suporta índexs HASH estàndard.
-- Els índexs HASH estan disponibles en taules optimitzades per a memòria (In-Memory OLTP).
CREATE TABLE sessions_mem (
token NVARCHAR(64) NOT NULL,
INDEX idx_token HASH (token) WITH (BUCKET_COUNT = 1024)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
Ús limitat del Hash
Els índexs Hash rarament son la millor opció fora de casos molt específics. El B-tree sol ser igual de ràpid per a igualtat i té la versatilitat afegida de suportar rangs.
Full-text (Text complet)
Permeten cerques de paraules dins de camps de text llarg, tenint en compte lematització, stopwords i rellèvancia. Indispensables per a aplicacions de cerca.
-- Crear un índex GIN sobre un vector de cerca
ALTER TABLE articles ADD COLUMN ts_contingut tsvector
GENERATED ALWAYS AS (to_tsvector('catalan', contingut)) STORED;
CREATE INDEX idx_articles_fts
ON articles USING GIN (ts_contingut);
-- Consulta full-text
SELECT titol, ts_rank(ts_contingut, query) AS rellèvancia
FROM articles, to_tsquery('catalan', 'base & dades') query
WHERE ts_contingut @@ query
ORDER BY rellèvancia DESC;
-- Crear índex FULLTEXT (requereix motor InnoDB o MyISAM)
ALTER TABLE articles ADD FULLTEXT INDEX idx_articles_fts (titol, contingut);
-- Consulta amb MATCH ... AGAINST
SELECT titol, MATCH(titol, contingut) AGAINST('base de dades' IN NATURAL LANGUAGE MODE) AS rellev
FROM articles
WHERE MATCH(titol, contingut) AGAINST('base de dades' IN NATURAL LANGUAGE MODE)
ORDER BY rellev DESC;
Espacials / GiST (PostgreSQL)
PostgreSQL disposa de tipus d'índex especialitzats per a dades geoespacials i altres estructures complexes:
- GiST (Generalized Search Tree): adequat per a geometries, rangs, arrays, cercles.
- SP-GiST (Space-Partitioned GiST): per a estructures no equilibrades com quadtrees.
- BRIN (Block Range Index): molt compacte, per a taules molt grans amb dades naturalment ordenades (timestamps, sèries).
-- PostgreSQL: índex GiST per a geometria (requereix PostGIS)
CREATE INDEX idx_zones_geom ON zones USING GIST (geometria);
-- Consulta espacial que utilitza l'índex
SELECT nom FROM zones
WHERE ST_Contains(geometria, ST_Point(2.8372, 41.6760));
Índexs compostos (multi-columna)
Un índex compost cobreix múltiples columnes. L'ordre de les columnes és crític: el motor pot usar l'índex per a cerques que comencin per les primeres columnes (regla del prefix d'esquerra).
-- Índex compost: útil per a WHERE cognom = ? AND nom = ?
CREATE INDEX idx_empleats_cognom_nom
ON empleats (cognom, nom);
-- Consulta que UTILITZA l'índex (prefix d'esquerra)
SELECT * FROM empleats WHERE cognom = 'García' AND nom = 'Maria';
-- Consulta que UTILITZA parcialment l'índex (primer camp)
SELECT * FROM empleats WHERE cognom = 'García';
-- Consulta que NO utilitza l'índex (camp del mig sense el primer)
SELECT * FROM empleats WHERE nom = 'Maria';
Índexs parcials / filtrats
Un índex parcial cobreix només un subconjunt de les files que compleixen una condició. Ocupen menys espai i son molt eficients per a consultes sobre subconjunts freqüents.
-- Índex només sobre comandes pendents (estat = 'pendent')
-- Si el 95% de comandes estan 'completades', l'índex és molt petit
CREATE INDEX idx_comandes_pendents
ON comandes (data_creacio)
WHERE estat = 'pendent';
-- Índex parcial per a valors no nuls
CREATE INDEX idx_empleats_email_notnull
ON empleats (email)
WHERE email IS NOT NULL;
-- La consulta ha d'incloure la mateixa condició WHERE per usar l'índex
SELECT * FROM comandes
WHERE estat = 'pendent' AND data_creacio > NOW() - INTERVAL '7 days';
-- SQL Server suporta índexs filtrats amb clàusula WHERE
CREATE INDEX idx_comandes_pendents
ON comandes (data_creacio)
WHERE estat = 'pendent';
-- Consulta que aprofita l'índex filtrat
SELECT id, client_id, data_creacio
FROM comandes
WHERE estat = 'pendent' AND data_creacio > DATEADD(DAY, -7, GETDATE());
-- Oracle suporta índexs parcials en taules particionades
-- i índexs sobre funcions per simular el comportament
CREATE INDEX idx_comandes_pendents
ON comandes (CASE WHEN estat = 'PENDENT' THEN data_creacio END);
-- Consulta
SELECT id, client_id, data_creacio FROM comandes
WHERE CASE WHEN estat = 'PENDENT' THEN data_creacio END > SYSDATE - 7;
Índexs de cobertura (Covering indexes)
Un índex de cobertura conté totes les columnes que necessita una consulta, de manera que el motor pot respondre-la completament des de l'índex sense accedir a la taula. Elimina la fase d'accés al heap (heap fetch) i pot ser dràsticament més ràpid.
-- INCLUDE afegeix columnes addicionals a les fulles de l'índex (PostgreSQL 11+)
CREATE INDEX idx_comandes_covering
ON comandes (client_id)
INCLUDE (estat, total, data_creacio);
-- Aquesta consulta es resol completament des de l'índex (Index Only Scan)
SELECT estat, total, data_creacio
FROM comandes
WHERE client_id = 42;
Creació d'índexs
Índex bàsic
-- Sintaxi bàsica
CREATE INDEX idx_empleats_departament
ON empleats (departament_id);
-- Amb ordre descendent (útil per a ORDER BY DESC)
CREATE INDEX idx_comandes_data_desc
ON comandes (data_creacio DESC);
-- Concurrent: no bloqueja la taula durant la creació
CREATE INDEX CONCURRENTLY idx_big_table_col
ON big_table (columna_important);
-- Sintaxi bàsica
CREATE INDEX idx_empleats_departament
ON empleats (departament_id);
-- Alternativa: ALTER TABLE
ALTER TABLE empleats
ADD INDEX idx_departament (departament_id);
-- Sense bloqueig (MySQL 5.6+ / MariaDB)
ALTER TABLE empleats
ADD INDEX idx_departament (departament_id),
ALGORITHM=INPLACE, LOCK=NONE;
Índex únic
Quan NO usar índexs
El perill de l'excés d'índexs
Cada índex que crees té un cost de manteniment: cada INSERT, UPDATE o DELETE ha d'actualitzar tots els índexs de la taula. En taules amb moltes escriptures, un excés d'índexs pot degradar dràsticament el rendiment.
Evita crear índexs en les situacions següents:
| Situació | Motiu |
|---|---|
| Taules molt petites (< 1.000 files) | El sequential scan és igual o més ràpid que la navegació per índex |
| Columnes de baixa cardinalitat | Una columna sexe amb valors 'M'/'F' retorna el 50% de les files; l'índex no ajuda |
| Taules amb alt ràtio d'escriptures | Les insercions massives es beneficien de no tenir índexs (o desactivar-los temporalment) |
| Columnes rarament usades en WHERE | Un índex que no s'usa mai és espai i CPU malgastats |
| Columnes amb molts NULL | La majoria de motors no emmagatzemen NULLs als índexs B-tree, reduint la seva utilitat |
Manteniment d'índexs
Amb el temps, els índexs es fragmenten: les pàgines es buiden parcialment per les eliminacions i les insercions creen desequilibris. Cal reconstruir-los periòdicament.
Reconstrucció d'índexs
-- Reconstruir un índex específic
REINDEX INDEX idx_empleats_departament;
-- Reconstruir tots els índexs d'una taula
REINDEX TABLE empleats;
-- Reconstruir tots els índexs d'una base de dades
REINDEX DATABASE nom_base_dades;
-- Concurrent (PostgreSQL 12+): sense bloqueig de lectures/escriptures
REINDEX INDEX CONCURRENTLY idx_empleats_departament;
-- OPTIMIZE TABLE reconstrueix la taula i tots els índexs
OPTIMIZE TABLE empleats;
-- Per a InnoDB, pot usar ALTER TABLE ... ENGINE=InnoDB
ALTER TABLE empleats ENGINE=InnoDB;
-- Analitzar l'estat de fragmentació
SELECT TABLE_NAME, DATA_FREE, DATA_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'nom_base' AND TABLE_NAME = 'empleats';
-- Reorganitzar (desfragmentació lleugera, en línia)
ALTER INDEX idx_empleats_departament
ON empleats REORGANIZE;
-- Reconstruir (desfragmentació completa)
ALTER INDEX idx_empleats_departament
ON empleats REBUILD;
-- Reconstruir tots els índexs d'una taula
ALTER INDEX ALL ON empleats REBUILD;
-- En línia (Enterprise Edition)
ALTER INDEX ALL ON empleats REBUILD WITH (ONLINE = ON);
-- Veure fragmentació
SELECT index_id, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('empleats'), NULL, NULL, 'LIMITED');
-- Reconstruir un índex
ALTER INDEX idx_empleats_departament REBUILD;
-- En línia
ALTER INDEX idx_empleats_departament REBUILD ONLINE;
-- Compactar (desfragmentació lleugera)
ALTER INDEX idx_empleats_departament COALESCE;
-- Veure estat dels índexs
SELECT index_name, status, blevel, leaf_blocks
FROM user_indexes
WHERE table_name = 'EMPLEATS';
Detecció d'índexs no usats
Un índex no usat és un passiu: ocupa espai i alenteix les escriptures sense benefici. Cal detectar-los i eliminar-los.
-- Índexs que no han estat usats mai des de l'últim reinici
SELECT
schemaname,
relname AS taula,
indexrelname AS index_nom,
idx_scan AS vegades_usat,
pg_size_pretty(pg_relation_size(indexrelid)) AS mida
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
-- Índexs amb poques cerques i moltes actualitzacions (candidats a eliminar)
SELECT
OBJECT_NAME(i.object_id) AS taula,
i.name AS index_nom,
ius.user_seeks,
ius.user_scans,
ius.user_updates,
ius.last_user_seek
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
ON i.object_id = ius.object_id
AND i.index_id = ius.index_id
AND ius.database_id = DB_ID()
WHERE i.type_desc NOT IN ('HEAP', 'CLUSTERED')
AND OBJECT_NAME(i.object_id) NOT LIKE 'sys%'
ORDER BY ius.user_seeks + ius.user_scans ASC;
-- Activar monitoratge d'un índex
ALTER INDEX idx_empleats_departament MONITORING USAGE;
-- Consultar si s'ha usat
SELECT index_name, used, start_monitoring, end_monitoring
FROM v$object_usage
WHERE index_name = 'IDX_EMPLEATS_DEPARTAMENT';
-- Desactivar monitoratge
ALTER INDEX idx_empleats_departament NOMONITORING USAGE;
Índexs clustered vs non-clustered
Índex clustered (agrupat)
Un índex clustered determina l'ordre físic de les dades a disc. Cada taula pot tenir-ne un de sol, i les files es guarden ordenades per la clau de l'índex.
- SQL Server: Per defecte, la clau primària crea un índex clustered. Les files es guarden fisicament ordenades per la PK.
- MySQL/InnoDB: La taula sencera és l'índex clustered (estructura IOT — Index Organized Table). La PK és sempre la clau de l'índex clustered. Si no hi ha PK, InnoDB crea una columna
rowidoculta. - PostgreSQL: No té índexs clustered tradicionals. La comanda
CLUSTERreordena físicament la taula una vegada, però la taula no es manté ordenada automàticament. - Oracle: Suporta IOT (Index Organized Tables) on la taula sencera és l'índex clustered.
graph LR
subgraph "Índex Clustered (InnoDB / SQL Server)"
PK1["PK=1\nMaria García"] --> PK2["PK=2\nJoan Puig"]
PK2 --> PK3["PK=3\nAnna Valls"]
PK3 --> PK4["PK=4\nPere Ros"]
end
subgraph "Índex Non-Clustered"
IDX["Índex per email"] --> |"punter a fila"| H1[("Heap\n(dades desordenades)")]
endÍndex non-clustered (no agrupat)
Un índex non-clustered és una estructura separada de les dades. Cada entrada de l'índex conté la clau indexada i un punter a la fila real (a SQL Server, aquest punter és la clau de l'índex clustered o el rowid).
-- Clustered index (normalment creat automàticament amb la PK)
CREATE CLUSTERED INDEX idx_empleats_id
ON empleats (id);
-- Non-clustered index
CREATE NONCLUSTERED INDEX idx_empleats_email
ON empleats (email);
-- Non-clustered amb columnes incloses (covering)
CREATE NONCLUSTERED INDEX idx_empleats_dept_cov
ON empleats (departament_id)
INCLUDE (nom, cognom, sou);
-- La PK és sempre l'índex clustered en InnoDB
CREATE TABLE empleats (
id INT NOT NULL AUTO_INCREMENT,
email VARCHAR(120) NOT NULL,
departament_id INT,
PRIMARY KEY (id), -- índex CLUSTERED
UNIQUE KEY uk_email (email) -- índex non-clustered
) ENGINE=InnoDB;
-- Els índexs secundaris emmagatzemen la clau primària com a punter
-- (no el rowid físic), per la qual cosa la PK hauria de ser petita
-- Index Organized Table (IOT): equivalent a clustered index
CREATE TABLE empleats (
id NUMBER PRIMARY KEY,
email VARCHAR2(120),
departament_id NUMBER
) ORGANIZATION INDEX;
-- Taula heap normal amb índex non-clustered (per defecte)
CREATE TABLE empleats (
id NUMBER PRIMARY KEY,
email VARCHAR2(120)
) ORGANIZATION HEAP;
Miniactivitat — AC0502
Objectiu: Analitzar l'impacte dels índexs en el rendiment d'una consulta real.
Passos:
-
Crea una taula
vendesamb 500.000 files de dades aleatòries:-- PostgreSQL CREATE TABLE vendes ( id SERIAL PRIMARY KEY, client_id INT, producte VARCHAR(50), import DECIMAL(10,2), data_venda DATE ); INSERT INTO vendes (client_id, producte, import, data_venda) SELECT (random() * 10000)::INT, ('Producte-' || (random() * 100)::INT), (random() * 1000)::DECIMAL(10,2), CURRENT_DATE - (random() * 1000)::INT FROM generate_series(1, 500000); -
Executa
EXPLAIN ANALYZE SELECT * FROM vendes WHERE client_id = 42;sense índex. Anota el temps d'execució. -
Crea l'índex:
CREATE INDEX idx_vendes_client ON vendes(client_id); -
Executa la mateixa consulta amb
EXPLAIN ANALYZE. Compara el temps i el tipus d'operació (Seq Scan vs Index Scan). -
Crea un índex compost
(client_id, data_venda)i prova una consulta que filtri per les dues columnes. Valida que s'usa l'índex compost. -
Identifica quin índex dels creats és un "covering index" per a la consulta
SELECT client_id, data_venda FROM vendes WHERE client_id = 42.
Lliura: captures de pantalla dels EXPLAIN ANALYZE amb i sense índex, i conclusions sobre la millora de rendiment observada.