El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
HOME Els meus cursos Finalitzats
◄ Anterior
◄

Estratègies d'indexació

Següent ►
►

Introducció

L'indexació és una tècnica fonamental per millorar el rendiment de les consultes en una base de dades. En aquest tema, explorarem les diferents estratègies d'indexació disponibles a PostgreSQL, com crear i utilitzar índexs de manera eficient, i com seleccionar l'índex adequat per a cada situació.

Conceptes Clau

  1. Índexs Bàsics: Els índexs són estructures de dades que milloren la velocitat de recuperació de dades en una taula.
  2. Tipus d'Índexs: PostgreSQL ofereix diversos tipus d'índexs, cadascun amb els seus propis avantatges i desavantatges.
  3. Estratègies d'Indexació: Seleccionar l'índex adequat depèn de la naturalesa de les consultes i les operacions que es realitzen sobre les dades.

Tipus d'Índexs en PostgreSQL

Índexs B-Tree

Els índexs B-Tree són els més comuns i s'utilitzen per a la majoria de les operacions de consulta. Són eficients per a les operacions de cerca, inserció, actualització i eliminació.

-- Crear un índex B-Tree
CREATE INDEX idx_nom_columna ON nom_taula(nom_columna);

Índexs Hash

Els índexs Hash són útils per a operacions d'igualtat, però no són adequats per a operacions de rang.

-- Crear un índex Hash
CREATE INDEX idx_nom_columna_hash ON nom_taula USING HASH (nom_columna);

Índexs GIN (Generalized Inverted Index)

Els índexs GIN són útils per a tipus de dades complexos com arrays, JSONB i text complet.

-- Crear un índex GIN
CREATE INDEX idx_nom_columna_gin ON nom_taula USING GIN (nom_columna);

Índexs GiST (Generalized Search Tree)

Els índexs GiST són útils per a dades geoespacials i altres tipus de dades que requereixen operacions de similitud.

-- Crear un índex GiST
CREATE INDEX idx_nom_columna_gist ON nom_taula USING GiST (nom_columna);

Índexs BRIN (Block Range INdex)

Els índexs BRIN són útils per a taules molt grans on les dades estan ordenades de manera natural.

-- Crear un índex BRIN
CREATE INDEX idx_nom_columna_brin ON nom_taula USING BRIN (nom_columna);

Estratègies d'Indexació

Índexs en Columnes Úniques

Per a columnes que s'utilitzen freqüentment en condicions de cerca, és recomanable crear un índex únic.

CREATE UNIQUE INDEX idx_nom_columna_unic ON nom_taula(nom_columna);

Índexs Compostos

Per a consultes que involucren múltiples columnes, un índex compost pot millorar significativament el rendiment.

CREATE INDEX idx_nom_columna_compost ON nom_taula(columna1, columna2);

Índexs Parcials

Els índexs parcials són útils quan només una part de les dades és rellevant per a les consultes.

CREATE INDEX idx_nom_columna_parcial ON nom_taula(nom_columna) WHERE condicio;

Índexs Funcionals

Els índexs funcionals permeten indexar el resultat d'una funció aplicada a una columna.

CREATE INDEX idx_nom_columna_funcional ON nom_taula (funcio(nom_columna));

Exercicis Pràctics

Exercici 1: Crear un Índex B-Tree

  1. Crea una taula empleats amb les columnes id, nom i departament.
  2. Insereix algunes dades a la taula.
  3. Crea un índex B-Tree a la columna nom.
-- Solució
CREATE TABLE empleats (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100),
    departament VARCHAR(50)
);

INSERT INTO empleats (nom, departament) VALUES
('Anna', 'Vendes'),
('Joan', 'Màrqueting'),
('Maria', 'IT');

CREATE INDEX idx_nom_empl ON empleats(nom);

Exercici 2: Crear un Índex Compost

  1. Crea una taula productes amb les columnes id, nom i categoria.
  2. Insereix algunes dades a la taula.
  3. Crea un índex compost a les columnes nom i categoria.
-- Solució
CREATE TABLE productes (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100),
    categoria VARCHAR(50)
);

INSERT INTO productes (nom, categoria) VALUES
('Ordinador', 'Electrònica'),
('Cadira', 'Mobles'),
('Telèfon', 'Electrònica');

CREATE INDEX idx_nom_categoria ON productes(nom, categoria);

Errors Comuns i Consells

  1. No crear índexs innecessaris: Cada índex afegeix sobrecàrrega a les operacions d'inserció, actualització i eliminació.
  2. Utilitzar índexs parcials amb cura: Assegura't que la condició de l'índex parcial sigui rellevant per a les consultes.
  3. Monitoritzar el rendiment: Utilitza EXPLAIN per analitzar el pla d'execució de les consultes i ajustar els índexs segons sigui necessari.

Conclusió

Les estratègies d'indexació són crucials per optimitzar el rendiment de les consultes en PostgreSQL. Comprendre els diferents tipus d'índexs i saber quan utilitzar-los pot marcar una gran diferència en l'eficiència de la teva base de dades. Practica amb els exercicis proporcionats per consolidar els teus coneixements i millorar les teves habilitats en indexació.

◄ Anterior
◄
Següent ►
►

Curs de PostgreSQL

Mòdul 1: Introducció a PostgreSQL

Mòdul 2: Operacions bàsiques de SQL

Mòdul 3: Consultes SQL avançades

Mòdul 4: Disseny de bases de dades i normalització

Mòdul 5: Funcionalitats avançades de PostgreSQL

Mòdul 6: Optimització i millora del rendiment

Mòdul 7: Seguretat i gestió d'usuaris

Mòdul 8: Treballant amb JSON i funcionalitats NoSQL

Mòdul 9: Extensions i eines avançades

Mòdul 10: Estudis de cas i aplicacions del món real

El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
© Copyright 2024. Tots els drets reservats