Un índice funciona como el índice de un libro: en lugar de leer todas las páginas para encontrar un tema, miras el índice y vas directamente a la página. Sin índices, para responder a WHERE idClientes = 4 la base de datos tiene que leer la tabla Pedidos entera, fila a fila. Con un índice sobre idClientes, va directa a las filas de ese cliente.
Con ocho pedidos la diferencia no se nota. Con ocho millones, es pasar de varios segundos a unos milisegundos.
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX nombreIndice
ON tabla (columna1 [ASC | DESC], columna2, ...);
Crear un índice
Las consultas de la tienda filtran y cruzan muy a menudo por idClientes en la tabla Pedidos. Es el candidato perfecto:
El nombre es libre, pero seguir una convención ayuda a reconocerlo: ix_ (de index) seguido de la tabla y las columnas.
Una vez creado, no hay que hacer nada más: el índice no se nombra en las consultas. El optimizador decide solo si le conviene usarlo, y a partir de ese momento lo mantiene actualizado en cada INSERT, UPDATE y DELETE.
Clustered y nonclustered
SQL Server tiene dos tipos de índice, y la diferencia es importante:
| Clustered (agrupado) | Nonclustered (no agrupado) | |
|---|---|---|
| Qué es | La propia tabla, ordenada físicamente por la clave del índice | Una estructura aparte que apunta a las filas de la tabla |
| Cuántos por tabla | Solo uno | Muchos (hasta 999) |
| Se crea automáticamente | Al definir la PRIMARY KEY |
Al definir una restricción UNIQUE |
| Bueno para | Búsquedas por rango y consultas ordenadas por la clave | Búsquedas por columnas concretas |
Cuando en la lección de CREATE TABLE definimos idClientes como PRIMARY KEY, SQL Server creó un índice clustered sobre esa columna: por eso buscar un cliente por su id ya era rápido.
-- Solo en SQL Server: tipo explícito
CREATE NONCLUSTERED INDEX ix_pedidos_fechaPedido
ON Pedidos (fechaPedido DESC);
CLUSTERED y NONCLUSTERED son palabras de SQL Server. En PostgreSQL todos los índices son, en la práctica, no agrupados, y el editor de esta página no las acepta. CREATE INDEX a secas funciona en las dos.Índices únicos
Un índice UNIQUE, además de acelerar las búsquedas, impide que se repitan valores. Intentar crear uno sobre una columna que ya tiene duplicados da error. En la tabla Pedidos hay dos productos con la descripción 'Altavoz Alexa':
En cambio, sobre Clientes (apellidos) sí se puede crear, porque no hay dos clientes con los mismos apellidos. A partir de ese momento, cualquier INSERT que repita unos apellidos fallará. Ejecuta el bloque y verás el error del segundo paso:
Una restricción UNIQUE en el CREATE TABLE y un índice UNIQUE hacen lo mismo; de hecho, SQL Server implementa la restricción creando un índice. La restricción deja más clara la intención en el diseño de la tabla.
Índices compuestos
Un índice puede incluir varias columnas. El orden importa: un índice sobre (idClientes, fechaPedido) sirve para buscar por cliente, o por cliente y fecha, pero no para buscar solo por fecha, igual que una guía telefónica ordenada por apellido y nombre no sirve para buscar a todos los que se llaman Ana.
| idClientes | fechaPedido | producto |
|---|---|---|
| 4 | 2022-09-01 | Echo DOT 3 |
| 4 | 2022-09-01 | Echo DOT 4 |
Regla práctica: pon primero la columna por la que más se filtra con igualdad (=) y después la que se usa en rangos u ordenaciones.
Columnas incluidas
En SQL Server (y en PostgreSQL desde la versión 11) se pueden añadir columnas que no forman parte de la clave pero se guardan en el índice con INCLUDE. Si una consulta solo pide columnas que están en el índice, el motor no necesita ni tocar la tabla:
CREATE INDEX ix_pedidos_cliente_incluye
ON Pedidos (idClientes)
INCLUDE (producto, totalPrecio);
Cuándo no crear un índice
Los índices no son gratis:
- Ocupan espacio, a veces tanto como la propia tabla.
- Ralentizan las escrituras: cada
INSERT,UPDATEoDELETEtiene que actualizar también todos los índices afectados. - En tablas pequeñas no aportan nada: leer cien filas es más rápido que consultar un índice.
- En columnas con pocos valores distintos (un campo sí/no, por ejemplo) el optimizador suele ignorarlos.
Y hay consultas que no pueden aprovecharlos aunque existan. Aplicar una función a la columna indexada, como WHERE YEAR(fechaPedido) = 2022, obliga a calcularla en todas las filas. La versión que sí usa el índice es WHERE fechaPedido >= '2022-01-01' AND fechaPedido < '2023-01-01'.
Borrar un índice
DROP INDEX ix_pedidos_idClientes ON Pedidos; -- SQL Server
DROP INDEX ix_pedidos_idClientes; -- PostgreSQL
Practica
- Crea un índice sobre
Clientes (apellidos, nombre). - ¿Serviría ese índice para la consulta
WHERE nombre = 'Ana'? ¿Por qué? - Reescribe
WHERE YEAR(fechaPedido) = 2022 AND MONTH(fechaPedido) = 9para que pueda usar un índice sobrefechaPedido.
