Índice clustered

Un índice clustered (o agrupado) determina el orden físico en que los datos se almacenan en disco. En InnoDB, la clave primaria es siempre el índice clustered. Los datos de la tabla se almacenan directamente en las hojas del árbol B+ del índice clustered, lo que significa que buscar por clave primaria es la operación más rápida posible.

Cómo funciona

En InnoDB, cada tabla tiene exactamente un índice clustered:

1. Si defines una PRIMARY KEY, esa es el índice clustered
2. Si no hay PRIMARY KEY, InnoDB usa el primer índice UNIQUE NOT NULL
3. Si no hay ninguno, InnoDB crea un índice clustered interno invisible

-- La PRIMARY KEY es el índice clustered
CREATE TABLE ejemplo (
    id INT PRIMARY KEY,     -- Este es el índice clustered
    nombre VARCHAR(100),
    email VARCHAR(150)
);

Índice clustered vs secundario

Todos los índices que no son el clustered se llaman índices secundarios. Hay una diferencia fundamental en cómo almacenan los datos:

El lookup adicional

Cuando MySQL usa un índice secundario, debe hacer dos pasos:

1. Buscar en el índice secundario para encontrar la clave primaria
2. Buscar en el índice clustered con esa clave primaria para obtener la fila completa

-- Ejemplo: buscar por precio (índice secundario)
CREATE INDEX idx_precio ON productos (precio);
 
-- Paso 1: MySQL busca precio=699 en idx_precio → encuentra id=3
-- Paso 2: MySQL busca id=3 en el clustered index → obtiene la fila completa
EXPLAIN SELECT * FROM productos WHERE precio = 699.00;

-- Buscar por PRIMARY KEY: acceso directo, sin lookup
EXPLAIN SELECT * FROM productos WHERE id = 3;

type: const indica acceso directo al índice clustered.

Covering index: evitar el lookup

Un covering index es un índice secundario que contiene todas las columnas necesarias para la consulta, evitando el lookup al clustered index:

-- Este índice cubre las columnas nombre y precio
CREATE INDEX idx_nombre_precio ON productos (nombre, precio);
 
-- La consulta solo necesita nombre y precio: covering index
EXPLAIN SELECT nombre, precio FROM productos WHERE nombre LIKE 'iPhone%';

Using index en Extra indica que MySQL resolvió la consulta usando solo el índice, sin acceder a la tabla (sin lookup al clustered index).

-- Si pedimos más columnas, necesita el lookup
EXPLAIN SELECT nombre, precio, stock FROM productos WHERE nombre LIKE 'iPhone%';

Using index condition (sin el "Using index" puro) indica que MySQL necesita volver al clustered index para obtener la columna stock.

Impacto del tamaño de la clave primaria

Como todos los índices secundarios almacenan la clave primaria, su tamaño afecta a todos los índices:

-- Buena práctica: PRIMARY KEY pequeña y secuencial
CREATE TABLE buena_pk (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 4 bytes, secuencial
    datos VARCHAR(500)
);
 
-- Mala práctica: PRIMARY KEY grande
CREATE TABLE mala_pk (
    uuid CHAR(36) PRIMARY KEY,  -- 36 bytes, aleatoria
    datos VARCHAR(500)
);

Con una PK de 36 bytes en lugar de 4, cada índice secundario usa 32 bytes adicionales por fila. En una tabla con 1 millón de filas y 5 índices secundarios, eso son ~150 MB de espacio extra.

Inserción secuencial vs aleatoria

El índice clustered funciona mejor con inserciones secuenciales (como AUTO_INCREMENT):

Con AUTO_INCREMENT, cada nueva fila se añade al final del árbol B+. Con UUIDs aleatorios, las inserciones ocurren en posiciones aleatorias, causando page splits frecuentes y fragmentación.

Ver el índice clustered

-- El índice PRIMARY es siempre el clustered en InnoDB
SELECT TABLE_NAME, INDEX_NAME, INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'tienda_mysql'
  AND INDEX_NAME = 'PRIMARY'
GROUP BY TABLE_NAME, INDEX_NAME, INDEX_TYPE;

Todas las tablas usan INT AUTO_INCREMENT como PRIMARY KEY, que es la elección óptima para InnoDB.

Limpieza

DROP INDEX idx_precio ON productos;
DROP INDEX idx_nombre_precio ON productos;
DROP TABLE IF EXISTS ejemplo;
DROP TABLE IF EXISTS buena_pk;
DROP TABLE IF EXISTS mala_pk;

En el siguiente artículo veremos los índices funcionales, que permiten indexar el resultado de una expresión o función.