Optimització de consultes SQL
Principis generals per escriure consultes eficients
L'optimització de consultes SQL no consisteix a aprendre un conjunt de regles màgiques, sinó a entendre com pensa el motor i escriure SQL que permeti al planificador prendre les millors decisions.
Els principis fonamentals són:
- Selecciona només el que necessites: evita
SELECT *; especifica les columnes. - Filtra tan aviat com sigui possible: posa els filtres més selectius primer al
WHERE. - Usa índexs correctament: no derrotis els índexs aplicant funcions sobre columnes indexades.
- Evita la conversió implícita de tipus: provoca que els índexs no s'usin.
- Redueix el conjunt de dades en els joins: filtra abans de fer el join, no després.
- Mesura sempre: usa
EXPLAIN ANALYZEper confirmar que les teves suposicions son correctes.
Antipatrons comuns i com corregir-los
1. SELECT * vs SELECT columnes específiques
SELECT * és l'antipatró més bàsic. A més de transferir dades innecessàries, impedeix l'ús d'index only scans i pot causar problemes en producció si s'afegeixen columnes noves a la taula.
-- MAL: llegeix i transfereix TOTES les columnes (pot ser 200+ bytes per fila)
SELECT * FROM empleats WHERE departament_id = 10;
-- BÉ: només les columnes necessàries (pot usar un covering index)
SELECT id, nom, cognom, sou FROM empleats WHERE departament_id = 10;
-- Molt millor si existeix l'índex: CREATE INDEX ON empleats(departament_id) INCLUDE (nom, cognom, sou)
-- → EXPLAIN mostrarà "Index Only Scan" (zero accés al heap)
2. Funcions sobre columnes indexades (derrota l'índex)
Aplicar una funció a una columna indexada en la clàusula WHERE impedeix que el motor usi l'índex, ja que l'índex emmagatzema el valor original, no el valor transformat per la funció.
-- MAL: la funció UPPER derrota l'índex sobre email
SELECT * FROM usuaris WHERE UPPER(email) = 'JOAN@EXEMPLE.CAT';
-- BÉ: opció 1 — normalitza les dades en inserció i busca en minúscules
SELECT * FROM usuaris WHERE email = 'joan@exemple.cat';
-- BÉ: opció 2 — índex funcional
CREATE INDEX idx_usuaris_email_upper ON usuaris (UPPER(email));
SELECT * FROM usuaris WHERE UPPER(email) = 'JOAN@EXEMPLE.CAT'; -- ara sí usa l'índex!
-- MAL: funció sobre data
SELECT * FROM comandes WHERE YEAR(data_creacio) = 2024;
-- Error: MySQL syntax. En PostgreSQL seria:
SELECT * FROM comandes WHERE EXTRACT(YEAR FROM data_creacio) = 2024;
-- BÉ: rang equivalent que usa l'índex
SELECT * FROM comandes
WHERE data_creacio >= '2024-01-01' AND data_creacio < '2025-01-01';
-- MAL: funció sobre columna indexada
SELECT * FROM comandes WHERE YEAR(data_creacio) = 2024;
SELECT * FROM usuaris WHERE LOWER(email) = 'joan@exemple.cat';
-- BÉ: reescriure amb rangs
SELECT * FROM comandes
WHERE data_creacio >= '2024-01-01' AND data_creacio < '2025-01-01';
-- BÉ: índex funcional (MySQL 8.0+)
CREATE INDEX idx_email_lower ON usuaris ((LOWER(email)));
SELECT * FROM usuaris WHERE LOWER(email) = 'joan@exemple.cat'; -- usa l'índex
-- MAL: funció sobre columna indexada
SELECT * FROM comandes WHERE YEAR(data_creacio) = 2024;
SELECT * FROM usuaris WHERE UPPER(email) = 'JOAN@EXEMPLE.CAT';
-- BÉ: rang equivalent
SELECT * FROM comandes
WHERE data_creacio >= '2024-01-01' AND data_creacio < '2025-01-01';
-- BÉ: columna computada persistent + índex
ALTER TABLE usuaris ADD email_upper AS UPPER(email) PERSISTED;
CREATE INDEX idx_usuaris_email_upper ON usuaris (email_upper);
SELECT * FROM usuaris WHERE email_upper = 'JOAN@EXEMPLE.CAT';
-- MAL: funció sobre columna indexada
SELECT * FROM comandes WHERE TRUNC(data_creacio) = DATE '2024-01-15';
SELECT * FROM usuaris WHERE UPPER(email) = 'JOAN@EXEMPLE.CAT';
-- BÉ: rang equivalent
SELECT * FROM comandes
WHERE data_creacio >= DATE '2024-01-15' AND data_creacio < DATE '2024-01-16';
-- BÉ: índex basat en funció (Function-Based Index)
CREATE INDEX idx_usuaris_email_upper ON usuaris (UPPER(email));
SELECT * FROM usuaris WHERE UPPER(email) = 'JOAN@EXEMPLE.CAT'; -- usa l'índex
3. Conversió implícita de tipus
Si una columna indexada és de tipus INT i el valor de comparació és un string '42', el motor ha de convertir tots els valors de la columna per comparar, derrota l'índex.
-- MAL: client_id és INT, però comparem amb un VARCHAR (conversió implícita)
SELECT * FROM comandes WHERE client_id = '42'; -- pot funcionar, però és imprecís
-- BÉ: tipus correcte
SELECT * FROM comandes WHERE client_id = 42;
-- Exemple problemàtic: telefon guardat com VARCHAR, però comparat com INT
SELECT * FROM contacts WHERE telefon = 934567890; -- DERROTA l'índex!
-- BÉ: mateixa comparació de tipus
SELECT * FROM contacts WHERE telefon = '934567890';
-- MAL: comparació de VARCHAR amb INT (conversió implícita)
SELECT * FROM usuaris WHERE codi_postal = 08001; -- codi_postal és VARCHAR
-- BÉ
SELECT * FROM usuaris WHERE codi_postal = '08001';
-- MySQL fa conversions implícites "silencioses" que poden causar resultats incorrectes
-- '08001' = 8001 és TRUE a MySQL per a integers... però derrota l'índex VARCHAR
-- MAL: columna VARCHAR, valor sense N'' (Unicode vs ANSI)
SELECT * FROM usuaris WHERE email = 'joan@exemple.cat'; -- email és NVARCHAR!
-- BÉ: usar el prefix N per a literals Unicode
SELECT * FROM usuaris WHERE email = N'joan@exemple.cat';
-- MAL: columna DATE comparada amb DATETIME
SELECT * FROM events WHERE data_event = '2024-01-15 00:00:00';
-- BÉ: tipus consistents
SELECT * FROM events WHERE data_event = CAST('2024-01-15' AS DATE);
-- MAL: comparar DATE amb string (Oracle intenta convertir automàticament)
SELECT * FROM comandes WHERE data_creacio = '15/01/2024'; -- format depèn de NLS_DATE_FORMAT
-- BÉ: usar DATE literals o TO_DATE explícit
SELECT * FROM comandes WHERE data_creacio = DATE '2024-01-15';
SELECT * FROM comandes WHERE data_creacio = TO_DATE('15/01/2024', 'DD/MM/YYYY');
4. El problema N+1
L'antipatró N+1 succeeix quan una aplicació fa 1 consulta per obtenir N registres, i llavors fa N consultes addicionals (una per cada registre). És un error comú en aplicacions que usen ORMs.
-- MAL: l'aplicació fa 1 query per obtenir clients, llavors 1 query per client
-- Query 1: SELECT id FROM clients LIMIT 100;
-- Query 2..101: SELECT * FROM comandes WHERE client_id = ? (100 vegades!)
-- BÉ: una sola query amb JOIN
SELECT
c.id,
c.nom,
COUNT(co.id) AS num_comandes,
COALESCE(SUM(co.total), 0) AS total_gastat
FROM clients c
LEFT JOIN comandes co ON c.id = co.client_id
GROUP BY c.id, c.nom
LIMIT 100;
-- Alternativa: usar una subquery correlacionada (millor que N+1, pitjor que JOIN)
SELECT
c.id,
c.nom,
(SELECT COUNT(*) FROM comandes co WHERE co.client_id = c.id) AS num_comandes
FROM clients c
LIMIT 100;
-- MAL (pseudocodi de l'aplicació):
-- results = SELECT id, nom FROM clients LIMIT 100
-- for each client in results:
-- commandes = SELECT * FROM comandes WHERE client_id = client.id
-- BÉ: tot en una sola consulta
SELECT
c.id,
c.nom,
COUNT(co.id) AS num_comandes,
COALESCE(SUM(co.total), 0) AS total_gastat
FROM clients c
LEFT JOIN comandes co ON c.id = co.client_id
GROUP BY c.id, c.nom
LIMIT 100;
5. LIKE amb comodí inicial ('%paraula%')
Un patró LIKE '%text%' no pot usar un índex B-tree (el comodí inicial impedeix navegar per l'arbre). El motor ha de llegir totes les files. Per a cerques de text, cal usar full-text search.
-- MAL: full table scan garantit
SELECT * FROM productes WHERE nom LIKE '%càmera%';
-- BÉ: full-text search
ALTER TABLE productes ADD COLUMN ts_nom tsvector
GENERATED ALWAYS AS (to_tsvector('catalan', nom)) STORED;
CREATE INDEX idx_productes_fts ON productes USING GIN (ts_nom);
SELECT * FROM productes WHERE ts_nom @@ to_tsquery('catalan', 'càmera');
-- ACCEPTABLE: comodí únicament al final (pot usar índex B-tree)
SELECT * FROM productes WHERE nom LIKE 'càm%'; -- usa l'índex!
-- MAL: full table scan
SELECT * FROM productes WHERE nom LIKE '%càmera%';
-- BÉ: full-text search
ALTER TABLE productes ADD FULLTEXT INDEX idx_nom_fts (nom);
SELECT * FROM productes WHERE MATCH(nom) AGAINST('càmera' IN NATURAL LANGUAGE MODE);
-- ACCEPTABLE: comodí al final (usa l'índex)
SELECT * FROM productes WHERE nom LIKE 'càm%';
-- MAL: full table scan
SELECT * FROM productes WHERE nom LIKE '%càmera%';
-- BÉ: Oracle Text
-- CREATE INDEX idx_nom_ctx ON productes(nom) INDEXTYPE IS CTXSYS.CONTEXT;
SELECT * FROM productes WHERE CONTAINS(nom, 'càmera') > 0;
-- ACCEPTABLE: comodí al final
SELECT * FROM productes WHERE nom LIKE 'càm%';
6. Subconsultes correlacionades vs JOINs
Una subconsulta correlacionada s'executa una vegada per a cada fila de la consulta exterior. Un JOIN sol ser equivalent però molt més eficient.
-- MAL: subconsulta correlacionada (s'executa N vegades)
SELECT
e.nom,
e.cognom,
(SELECT MAX(sou) FROM empleats e2 WHERE e2.departament_id = e.departament_id) AS max_sou_dept
FROM empleats e;
-- BÉ: CTE + JOIN (s'executa 1 vegada)
WITH max_sous AS (
SELECT departament_id, MAX(sou) AS max_sou
FROM empleats
GROUP BY departament_id
)
SELECT e.nom, e.cognom, ms.max_sou AS max_sou_dept
FROM empleats e
JOIN max_sous ms ON e.departament_id = ms.departament_id;
-- O amb window function (molt eficient)
SELECT
nom,
cognom,
MAX(sou) OVER (PARTITION BY departament_id) AS max_sou_dept
FROM empleats;
-- MAL: subconsulta correlacionada
SELECT nom, sou,
(SELECT AVG(sou) FROM empleats e2 WHERE e2.departament_id = e.departament_id) AS mitja_dept
FROM empleats e;
-- BÉ: JOIN amb subquery agregada
SELECT e.nom, e.sou, stats.mitja_dept
FROM empleats e
JOIN (
SELECT departament_id, AVG(sou) AS mitja_dept
FROM empleats
GROUP BY departament_id
) stats ON e.departament_id = stats.departament_id;
CTEs (WITH clause) i implicacions de rendiment
Les CTEs (Common Table Expressions) milloren la llegibilitat, però tenen implicacions de rendiment importants.
-- PostgreSQL < 12: les CTEs eren "barreres d'optimització" (materialitzades sempre)
-- PostgreSQL >= 12: el planificador pot "inlinar" les CTEs si no son recursives
-- CTE materialitzada (força la materialització, útil si la CTE s'usa múltiples vegades)
WITH MATERIALIZED comandes_2024 AS (
SELECT * FROM comandes WHERE EXTRACT(YEAR FROM data_creacio) = 2024
)
SELECT c.nom, COUNT(co.id) AS num_comandes
FROM clients c
JOIN comandes_2024 co ON c.id = co.client_id
GROUP BY c.nom;
-- CTE no materialitzada (l'optimitzador la pot inlinar i optimitzar millor)
WITH NOT MATERIALIZED comandes_2024 AS (
SELECT * FROM comandes WHERE data_creacio >= '2024-01-01' AND data_creacio < '2025-01-01'
)
SELECT c.nom, COUNT(co.id)
FROM clients c
JOIN comandes_2024 co ON c.id = co.client_id
GROUP BY c.nom;
-- CTE recursiva: per a jerarquies (departaments, categories, etc.)
WITH RECURSIVE jerarquia AS (
-- Cas base: el node arrel
SELECT id, nom, pare_id, 0 AS nivell
FROM departaments
WHERE pare_id IS NULL
UNION ALL
-- Pas recursiu
SELECT d.id, d.nom, d.pare_id, j.nivell + 1
FROM departaments d
JOIN jerarquia j ON d.pare_id = j.id
)
SELECT id, REPEAT(' ', nivell) || nom AS nom_indentat, nivell
FROM jerarquia
ORDER BY nivell, nom;
-- MySQL 8.0+ / MariaDB 10.2+ suporten CTEs
-- MySQL materialitza les CTEs per defecte (similar a PostgreSQL < 12)
WITH comandes_2024 AS (
SELECT client_id, SUM(total) AS total_any
FROM comandes
WHERE data_creacio >= '2024-01-01' AND data_creacio < '2025-01-01'
GROUP BY client_id
)
SELECT c.nom, co.total_any
FROM clients c
JOIN comandes_2024 co ON c.id = co.client_id
WHERE co.total_any > 1000
ORDER BY co.total_any DESC;
-- CTE recursiva per a jerarquies
WITH RECURSIVE jerarquia AS (
SELECT id, nom, pare_id, 0 AS nivell
FROM departaments WHERE pare_id IS NULL
UNION ALL
SELECT d.id, d.nom, d.pare_id, j.nivell + 1
FROM departaments d JOIN jerarquia j ON d.pare_id = j.id
)
SELECT id, REPEAT(' ', nivell), nom FROM jerarquia ORDER BY nivell, nom;
-- SQL Server: les CTEs NO es materialitzen per defecte (son inline)
-- Per a grans conjunts de dades, pot ser millor usar taules temporals
WITH comandes_2024 AS (
SELECT client_id, SUM(total) AS total_any
FROM comandes
WHERE data_creacio >= '2024-01-01' AND data_creacio < '2025-01-01'
GROUP BY client_id
)
SELECT c.nom, co.total_any
FROM clients c
JOIN comandes_2024 co ON c.id = co.client_id
WHERE co.total_any > 1000
ORDER BY co.total_any DESC;
-- Oracle: les CTEs son "inline views" per defecte, però es poden materialitzar
WITH comandes_2024 AS (
SELECT /*+ MATERIALIZE */ client_id, SUM(total) AS total_any
FROM comandes
WHERE data_creacio >= DATE '2024-01-01' AND data_creacio < DATE '2025-01-01'
GROUP BY client_id
)
SELECT c.nom, co.total_any
FROM clients c
JOIN comandes_2024 co ON c.id = co.client_id
WHERE co.total_any > 1000
ORDER BY co.total_any DESC;
Window Functions vs GROUP BY
Les funcions de finestra (window functions) permeten fer càlculs agregats sense col·lapsar les files, cosa impossible amb GROUP BY sol.
-- GROUP BY: col·lapsa les files, perd la granularitat de fila
SELECT departament_id, AVG(sou) AS mitja_sou
FROM empleats
GROUP BY departament_id;
-- Window function: manté totes les files, afegeix el valor agregat
SELECT
id,
nom,
sou,
departament_id,
AVG(sou) OVER (PARTITION BY departament_id) AS mitja_dept,
sou - AVG(sou) OVER (PARTITION BY departament_id) AS diferencia_mitja,
RANK() OVER (PARTITION BY departament_id ORDER BY sou DESC) AS ranking_dept,
ROW_NUMBER() OVER (ORDER BY sou DESC) AS ranking_global,
LAG(sou) OVER (PARTITION BY departament_id ORDER BY data_contracte) AS sou_anterior,
SUM(sou) OVER (PARTITION BY departament_id ORDER BY data_contracte ROWS UNBOUNDED PRECEDING) AS acumulat
FROM empleats;
-- Window functions disponibles des de MySQL 8.0 / MariaDB 10.2
SELECT
id,
nom,
sou,
departament_id,
AVG(sou) OVER (PARTITION BY departament_id) AS mitja_dept,
RANK() OVER (PARTITION BY departament_id ORDER BY sou DESC) AS ranking,
ROW_NUMBER() OVER (ORDER BY sou DESC) AS ranking_global
FROM empleats;
SELECT
id,
nom,
sou,
departament_id,
AVG(sou) OVER (PARTITION BY departament_id) AS mitja_dept,
RANK() OVER (PARTITION BY departament_id ORDER BY sou DESC) AS ranking,
SUM(sou) OVER (PARTITION BY departament_id ORDER BY data_contracte
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS acumulat_dept
FROM empleats;
Hints de l'optimitzador (Query Hints)
Els hints permeten forçar decisions del planificador. Usa'ls amb molta precaució i només quan tens evidències clares que el planificador pren una decisió incorrecta.
-- PostgreSQL no té hints SQL estàndard, però permet canviar paràmetres per sessió
-- Desactivar sequential scans (força l'ús d'índexs)
SET enable_seqscan = off;
SELECT * FROM empleats WHERE departament_id = 10;
RESET enable_seqscan;
-- Desactivar Hash Join
SET enable_hashjoin = off;
-- Desactivar Nested Loop
SET enable_nestloop = off;
-- Forçar paral·lelisme (o desactivar-lo)
SET max_parallel_workers_per_gather = 4; -- activar paral·lelisme
SET max_parallel_workers_per_gather = 0; -- desactivar paral·lelisme
-- Extensió pg_hint_plan: hints SQL directes (cal instal·lar-la)
-- SELECT /*+ IndexScan(e idx_empleats_departament) */ *
-- FROM empleats e WHERE departament_id = 10;
-- Forçar l'ús d'un índex específic
SELECT * FROM empleats USE INDEX (idx_empleats_departament)
WHERE departament_id = 10;
-- Ignorar un índex (el planificador el descarta)
SELECT * FROM empleats IGNORE INDEX (idx_empleats_cognom)
WHERE departament_id = 10;
-- Forçar l'ús d'un índex (no permet al planificador escollir)
SELECT * FROM empleats FORCE INDEX (idx_empleats_departament)
WHERE departament_id = 10;
-- STRAIGHT_JOIN: forçar l'ordre de join (primera taula = outer)
SELECT STRAIGHT_JOIN e.nom, d.nom
FROM empleats e, departaments d
WHERE e.departament_id = d.id;
-- NOLOCK: lectura bruta sense bloquejos (pot llegir dades no confirmades!)
SELECT * FROM comandes WITH (NOLOCK) WHERE estat = 'pendent';
-- Forçar un join específic
SELECT e.nom, d.nom
FROM empleats e
INNER JOIN departaments d WITH (INDEX(idx_dept_id))
ON e.departament_id = d.id
OPTION (HASH JOIN);
-- OPTION (RECOMPILE): recompila el pla cada vegada (evita el "parameter sniffing")
SELECT * FROM comandes WHERE client_id = @client_id
OPTION (RECOMPILE);
-- Forçar el nombre de files estimat
SELECT * FROM comandes WHERE estat = 'pendent'
OPTION (FAST 100); -- retorna les 100 primeres files tan aviat com sigui possible
-- Forçar ús d'un índex
SELECT /*+ INDEX(e idx_empleats_departament) */ e.nom
FROM empleats e
WHERE e.departament_id = 10;
-- Forçar full table scan (desactivar índex)
SELECT /*+ FULL(e) */ e.nom
FROM empleats e
WHERE e.departament_id = 10;
-- Forçar tipus de join
SELECT /*+ USE_HASH(e d) */ e.nom, d.nom
FROM empleats e, departaments d
WHERE e.departament_id = d.id;
-- Hint de paral·lelisme
SELECT /*+ PARALLEL(e, 4) */ COUNT(*) FROM empleats e;
Precaució amb els hints de l'optimitzador
Els hints han de ser l'últim recurs, no el primer. Els hints:
- Fixen el pla per sempre, fins i tot quan les dades canvien i el pla òptim seria diferent.
- Poden empitjorar el rendiment en escenaris futurs que ara no existeixen.
- Oculten problemes reals (estadístiques obsoletes, manca d'índex) que caldria solucionar.
Sempre que sents la temptació d'usar un hint, pregunta't primer: "He actualitzat les estadístiques? Existeix l'índex adequat? El tipus de join és l'adequat per al volum de dades actual?"
Miniactivitat — AC0504
Objectiu: Identificar i corregir antipatrons SQL en un conjunt de consultes reals.
Preparació:
-- Crea el schema de proves (PostgreSQL)
CREATE TABLE clients (
id SERIAL PRIMARY KEY,
nom VARCHAR(100),
email VARCHAR(120),
pais VARCHAR(50),
creat_a TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE comandes (
id SERIAL PRIMARY KEY,
client_id INT REFERENCES clients(id),
total DECIMAL(12,2),
estat VARCHAR(20),
creat_a TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE linies_comanda (
id SERIAL PRIMARY KEY,
comanda_id INT REFERENCES comandes(id),
producte VARCHAR(100),
quantitat INT,
preu_unit DECIMAL(10,2)
);
-- Inserir dades de prova
INSERT INTO clients (nom, email, pais)
SELECT 'Client-' || i, 'client' || i || '@exemple.cat',
(ARRAY['Catalunya','Espanya','França','Alemanya'])[1 + (i % 4)]
FROM generate_series(1, 50000) i;
INSERT INTO comandes (client_id, total, estat)
SELECT (random()*50000)::INT + 1,
(random()*5000)::DECIMAL(12,2),
(ARRAY['pendent','enviat','entregat','cancel·lat'])[1 + floor(random()*4)::INT]
FROM generate_series(1, 200000);
INSERT INTO linies_comanda (comanda_id, producte, quantitat, preu_unit)
SELECT (random()*200000)::INT + 1,
'Producte-' || (random()*500)::INT,
(random()*10)::INT + 1,
(random()*200)::DECIMAL(10,2)
FROM generate_series(1, 800000);
ANALYZE clients, comandes, linies_comanda;
Consultes a analitzar i optimitzar (usa EXPLAIN ANALYZE per comparar):
-- Consulta 1 (antipatró: SELECT *, funció sobre data)
SELECT * FROM comandes
WHERE EXTRACT(YEAR FROM creat_a) = 2024;
-- Consulta 2 (antipatró: LIKE amb % inicial)
SELECT * FROM clients WHERE email LIKE '%exemple.cat';
-- Consulta 3 (antipatró: subconsulta correlacionada)
SELECT c.nom,
(SELECT COUNT(*) FROM comandes co WHERE co.client_id = c.id) AS num_comandes
FROM clients c
WHERE pais = 'Catalunya';
-- Consulta 4 (antipatró: SELECT * en JOIN)
SELECT * FROM comandes co
JOIN clients c ON co.client_id = c.id
WHERE co.estat = 'pendent';
Tasques:
- Per a cadascuna de les 4 consultes, executa
EXPLAIN ANALYZEi identifica el problema principal. - Reescriu cada consulta eliminant l'antipatró.
- Crea els índexs necessaris per optimitzar-les.
- Compara els temps d'execució (abans / després) i explica la millora.
Lliura: Document amb el EXPLAIN ANALYZE original, la versió corregida, l'EXPLAIN ANALYZE de la versió corregida, i una taula comparativa de temps d'execució.