High Performance MySQL [Cap. 5: Indexación para alto rendimiento]

Qué es realmente un índice

Piensa en el índice al final de un libro: buscas el término, obtienes un número de página y vas a esa página. Un índice hace lo mismo con las filas, solo que las "páginas" son páginas de InnoDB de 16 KB organizadas como un B+Tree. Las páginas raíz e internas solo guardan claves separadoras y punteros a páginas hijas; todos los valores viven en las páginas hoja, ordenados, y cada hoja está enlazada con sus vecinas en ambas direcciones. Todas las hojas están a la misma profundidad, así que cada búsqueda cuesta las mismas pocas lecturas de página: unas tres o cuatro incluso con millones de filas.

Como las claves están ordenadas, el motor puede descender hasta un valor y luego avanzar lateralmente para recorrer un rango; ese recorrido es la razón por la que los rangos son baratos y los predicados arbitrarios no. Los enlaces van en ambos sentidos, así que el mismo recorrido funciona hacia atrás para ORDER BY … DESC.

Lo que contiene una entrada de hoja depende del índice. En la clave primaria es la fila completa. En un índice secundario son las columnas indexadas más la clave primaria de la fila, nunca una dirección en disco. Tenlo presente: explica casi todo lo que sigue.

Tipos de índice, y por qué gana el B-Tree

Cuando se dice "índice" en MySQL casi siempre se habla de un B-Tree. Los demás existen para tareas más concretas.

Un índice hash guarda un hash de la clave completa y un puntero a la fila. Eso lo hace compacto y rápido para la igualdad, pero los hashes están en orden aleatorio, así que no sirve para un rango ni para un ORDER BY, y un índice sobre (a, b) calcula el hash de ambas columnas juntas, de modo que una consulta solo sobre a no puede usarlo. Las columnas con pocos valores distintos son su peor caso: muchas filas comparten un hash y el motor recorre una cadena de ellas en cada búsqueda.

Solo puedes declarar índices hash en tablas MEMORY, pero InnoDB puede crear uno por su cuenta: el índice hash adaptativo detecta páginas B-Tree que se consultan muy a menudo y añade encima un hash en memoria. Está activado por defecto en MySQL 8.0 y desactivado por defecto desde la 8.4, y en ningún caso requiere cambiar el esquema.

Las tres cosas que puede hacer un índice

La clasificación de tres estrellas del libro: un índice gana una estrella por cada trabajo que hace para una consulta. La mayor parte de la ganancia está en la primera.

La primera estrella también cambia cómo se lee el disco: las filas que están juntas en el índice se leen página tras página en lugar de saltar por toda la tabla, y un índice que entrega el orden ahorra la tabla temporal y la ordenación que GROUP BY y ORDER BY necesitarían de otro modo.

Los índices no son gratis. Cada INSERT, UPDATE y DELETE actualiza todos los índices de la tabla, cada índice ocupa memoria del buffer pool y el optimizador evalúa cada uno al planificar una consulta. En una tabla diminuta que cabe en unas pocas páginas, un recorrido ya es barato y un índice aporta poco. Cuanto más grande la tabla, más compensa su mantenimiento un índice bien elegido.

La regla del prefijo por la izquierda

Un índice de varias columnas ordena por la primera columna, luego por la segunda dentro de ella, y así sucesivamente. Las búsquedas deben empezar por la izquierda y no pueden saltarse una columna. Dado idx_orders_customer_status_placed (customer_id, status, placed_at):

-- usa el índice
WHERE customer_id = 42
WHERE customer_id = 42 AND status = 'paid'
WHERE customer_id = 42 AND status = 'paid' AND placed_at >= '2026-01-01'

-- no puede usarlo
WHERE status = 'paid'                      -- se salta la primera columna
WHERE customer_id = 42 AND placed_at > ?   -- falta status, placed_at inutilizable
WHERE DATE(placed_at) = '2026-01-01'       -- columna envuelta en una función

Dicho de otro modo, el mismo índice puede responder cinco formas de pregunta, y todas empiezan por la izquierda:

El límite está en la última fila: nada a la derecha del primer rango puede acotar la búsqueda. En customer_id = 42 AND status LIKE 'p%' AND placed_at = '2026-01-04', el LIKE es un rango, así que MySQL recorre todos los status 'p…' del cliente 42 y solo filtra placed_at sobre las entradas que ya leyó.

MySQL 8.0 suaviza la regla de "no se puede saltar la primera columna" en un caso. Cuando la primera columna tiene muy pocos valores distintos y todas las columnas que necesita la consulta están en el índice, el optimizador puede hacer un skip scan: un rango por cada valor de la primera columna. EXPLAIN muestra Using index for skip scan. Lee más que un prefijo fijado y desaparece en cuanto seleccionas una columna fuera del índice.

Deja la columna sola

MySQL solo puede buscar en un índice por la columna misma. Envuelve la columna en aritmética, una función o una conversión y el orden deja de servir: el motor tiene que calcular la expresión para cada fila y comparar. Pasa el trabajo al lado de la constante.

-- la columna está dentro de una expresión: recorrido completo
WHERE id + 1 = 5
WHERE YEAR(reported_at) = 2020
WHERE DATEDIFF(CURRENT_DATE, reported_at) <= 10

-- mismas filas, la columna sola: rango sobre el índice
WHERE id = 4
WHERE reported_at >= '2020-01-01' AND reported_at < '2021-01-01'
WHERE reported_at >= CURRENT_DATE - INTERVAL 10 DAY

Algunas variantes del error son más difíciles de ver:

• Conversiones implícitas. Comparar una columna de texto con un número (WHERE phone = 5551234 sobre un VARCHAR) obliga a MySQL a convertir a número el valor de cada fila, así que el índice no sirve. Pon el literal entre comillas. Al revés, una columna numérica comparada con una cadena, no hay problema: solo se convierte la constante.
• Comodines al principio. LIKE 'abc%' es un rango sobre el índice; LIKE '%abc' puede empezar en cualquier parte, así que es un recorrido completo.
• Collations o juegos de caracteres distintos en un join. Unir dos columnas de texto con collations diferentes obliga a convertir uno de los lados, y ese lado pierde su índice.

Si de verdad necesitas buscar por una expresión, MySQL 8.0.13 y posteriores pueden indexarla directamente con una parte de clave funcional como INDEX ((YEAR(reported_at))). La consulta tiene que usar entonces exactamente esa expresión.

Índices de varias columnas y orden de columnas

Tres errores explican la mayoría de los malos índices de varias columnas:

• Un índice por columna. Índices separados sobre store_id y status no son un índice compuesto. Lo mejor que puede hacer MySQL es un index merge: leer ambos e intersecar o unir las claves primarias, algo que EXPLAIN muestra como index_merge. Funciona, pero cuesta más que un índice sobre (store_id, status), y suele indicar que falta ese índice.
• Indexar todas las columnas del WHERE. Un único índice ancho en un orden cualquiera no es mejor: solo ayuda su prefijo por la izquierda, y cada columna extra se paga en las escrituras.
• El orden equivocado. Las columnas correctas en el orden incorrecto dan un índice largo y casi inútil.

La regla conocida, la columna más selectiva primero, solo vale cuando todos los predicados son igualdades. La regla más sólida es columnas de igualdad primero, columna de rango al final. El primer predicado de rango agota el orden utilizable del índice; las columnas a su derecha pueden filtrar filas que el motor ya leyó, pero no pueden acotar el recorrido.

-- consulta: WHERE store_id = 7 AND status = 'late' AND rental_date >= '2025-06-01'
KEY (store_id, status, rental_date)   -- un único rango estrecho
KEY (rental_date, store_id, status)   -- recorre todos los alquileres desde junio

La selectividad se mide, no se adivina: COUNT(DISTINCT col) / COUNT(*). Revisa el peor caso además del promedio: una columna que parece selectiva en general puede tener un valor que cubre un tercio de la tabla. Y deja que decidan las consultas que realmente ejecutas: un índice que además sirve al ORDER BY o al GROUP BY puede valer más que otro un poco más selectivo.

Índices agrupados: la clave primaria es la tabla

InnoDB guarda las filas dentro del B-Tree de la clave primaria. Dos consecuencias dominan el diseño del esquema.

• Los índices secundarios guardan la clave primaria, no un puntero a la fila. Encontrar una fila a través de uno cuesta dos descensos: el árbol secundario y luego el agrupado. Ese segundo salto es la razón por la que una clave primaria ancha infla todos los índices de la tabla.
• El orden de inserción es el orden físico. Las claves monótonas se añaden a la última página. Las claves aleatorias se insertan en medio, dividen páginas, fragmentan el tablespace y ensucian mucho más buffer pool por escritura.

Lo que aporta el agrupamiento: las filas con claves cercanas comparten páginas, así que un rango de clave primaria lee pocas páginas, y una búsqueda por clave primaria encuentra la fila al final de un único descenso. Lo que cuesta: cambiar el valor de una clave primaria mueve físicamente la fila, y la clave primaria se copia en cada índice secundario.

Por eso una clave sustituta AUTO_INCREMENT es la opción habitual. Una clave natural como un email, o un UUID aleatorio, es más ancha y llega en orden aleatorio. Si necesitas UUID, guárdalos como BINARY(16) con UUID_TO_BIN(uuid, 1), que pone primero la parte temporal para que las claves nuevas caigan cerca del final. Las claves secuenciales tienen su propio coste con muchas inserciones concurrentes: todos los escritores apuntan a la misma última página. El valor por defecto de MySQL 8.0, innodb_autoinc_lock_mode = 2, evita que el propio contador sea un cuello de botella. Tras muchas inserciones o borrados aleatorios, OPTIMIZE TABLE reconstruye la tabla y sus índices de forma compacta.

Índices de cobertura

Si un índice contiene todas las columnas que toca una consulta (lista de selección, filtros, joins, orden), el motor responde desde el índice y nunca lee una fila. EXPLAIN muestra Using index. Los índices son mucho más pequeños que sus tablas, así que esta es la mayor ganancia posible sin cambiar la pregunta.

-- cubierto por (customer_id, placed_at): sin leer filas
SELECT customer_id, placed_at FROM orders WHERE customer_id = 42;

-- un descenso al árbol agrupado por cada fila que coincide
SELECT customer_id, placed_at, notes FROM orders WHERE customer_id = 42;

Cuando leer la fila es inevitable pero el filtro es débil, un join diferido (una subconsulta solo sobre el índice para encontrar las claves primarias y luego un join para las columnas anchas) compensa en offsets grandes.

Ordenar con un índice

Un índice satisface un ORDER BY solo cuando las columnas de orden forman un prefijo por la izquierda y las direcciones coinciden, con una excepción: las columnas fijadas a constantes por predicados de igualdad se pueden saltar. Mezcla ascendente y descendente, u ordena por columnas de dos tablas distintas, y MySQL recurre a un filesort. Sobre un resultado intermedio grande, ese filesort, no el recorrido, es el coste.

La tabla rentals que usan varios retos tiene UNIQUE KEY rental_date (rental_date, inventory_id, customer_id) y KEY (store_id, status, rental_date). Estas consultas leen las filas del índice directamente en orden:

-- primera columna fijada, ordenar por las dos siguientes
WHERE rental_date = '2025-05-25' ORDER BY inventory_id, customer_id

-- rango sobre la primera columna, el orden empieza por esa columna
WHERE rental_date > '2025-05-25' ORDER BY rental_date, inventory_id

-- todas las columnas descendentes: el índice se lee hacia atrás
ORDER BY rental_date DESC, inventory_id DESC, customer_id DESC LIMIT 10

-- dos columnas fijadas, ordenar por la tercera
WHERE store_id = 7 AND status = 'late' ORDER BY rental_date DESC LIMIT 10

Estas necesitan un filesort:

-- direcciones mezcladas
WHERE rental_date = '2025-05-25' ORDER BY inventory_id DESC, customer_id ASC

-- store_id no está en este índice
WHERE rental_date = '2025-05-25' ORDER BY inventory_id, store_id

-- inventory_id se salta y no está fijada
WHERE rental_date = '2025-05-25' ORDER BY customer_id

-- rango sobre la primera columna, el orden la salta
WHERE rental_date > '2025-05-25' ORDER BY inventory_id, customer_id

-- aquí IN es un rango: varios bloques, cada uno ordenado por separado
WHERE store_id = 7 AND status IN ('late', 'lost') ORDER BY rental_date DESC

De esos ejemplos salen algunas reglas:

• EXPLAIN te lo dice. Using filesort en Extra significa que MySQL ordena después de leer; Backward index scan significa que recorre el índice desde el final.
• Un filesort ignora LIMIT al leer. Para devolver los 10 primeros tiene que leer antes todas las filas que coinciden. Con el orden del índice, LIMIT 10 se detiene tras unas 10 entradas.
• Las direcciones mezcladas necesitan un índice a juego. Desde MySQL 8.0 un índice puede declararse como (inventory_id DESC, customer_id ASC), y entonces ese orden mixto es gratis.
• En un join, solo el índice de la primera tabla puede ordenar. Ordenar por una columna de una tabla posterior siempre implica un filesort, así que la tabla por la que ordenas tiene que dirigir el join. STRAIGHT_JOIN fuerza ese orden cuando el optimizador elige otro.
• Con IN, divide la consulta. Una rama ordenada por el índice para cada valor, combinadas con UNION ALL y ordenadas al final, lee muchas menos filas que un filesort de todo lo que coincide.
• La clave primaria es la última columna oculta. KEY (customer_id) se guarda como (customer_id, id), así que WHERE customer_id = 42 ORDER BY id DESC ya sigue el orden del índice.

Índices de prefijo en cadenas largas

Indexar los primeros n caracteres mantiene el árbol pequeño. Elige n aumentándolo hasta que la selectividad del prefijo se acerque a la de la columna completa, y revisa el peor caso en lugar del promedio. El coste es real: un índice de prefijo no puede cubrir una consulta ni usarse para ordenar o agrupar.

SELECT COUNT(DISTINCT LEFT(title, 8))  / COUNT(*) AS sel8,
       COUNT(DISTINCT LEFT(title, 16)) / COUNT(*) AS sel16,
       COUNT(DISTINCT title)           / COUNT(*) AS full_sel
FROM bugs;

ALTER TABLE bugs ADD INDEX idx_title (title(16));

Para columnas TEXT y BLOB no es opcional: MySQL solo puede indexarlas con una longitud de prefijo.

Índices redundantes, duplicados y sin uso

Cada índice se paga en cada escritura y en cada decisión del optimizador. Un índice sobre (A) es redundante cuando existe (A, B); ampliar el índice más ancho suele ser mejor que añadir otro. Los duplicados llegan en silencio, con declaraciones UNIQUE, PRIMARY KEY e INDEX separadas sobre la misma columna. Y un index merge en EXPLAIN no es algo que celebrar: es el optimizador avisando de que ningún índice encaja con la consulta.

Cuidado con un caso: (A, id) en una tabla InnoDB parece redundante, porque (A) ya termina con la clave primaria. Pero sustituir (A) por (A, B) rompe las consultas que dependían de WHERE A = ? ORDER BY id.

MySQL 8.0 ayuda en los dos lados del problema:

-- índices que otro índice hace redundantes
SELECT * FROM sys.schema_redundant_indexes;

-- índices sin uso desde que arrancó el servidor
SELECT * FROM sys.schema_unused_indexes;

-- ocultar un índice al optimizador antes de borrarlo
ALTER TABLE rentals ALTER INDEX idx_customer INVISIBLE;

Un índice invisible se sigue manteniendo en cada escritura, pero el optimizador lo ignora. Si nada se vuelve más lento, bórralo; si algo sí, vuelve a hacerlo VISIBLE al instante, sin reconstruirlo. Lee la lista de índices sin uso con cuidado: solo cubre el tiempo desde el último reinicio, y puede que un informe mensual aún no se haya ejecutado.

Índices y bloqueos

InnoDB bloquea los registros de índice que lee, no las filas que pretendías cambiar. Un UPDATE o un SELECT … FOR UPDATE que recorre 10.000 entradas de índice para encontrar 10 filas puede bloquear las 10.000, y las demás transacciones que las tocan esperan.

• REPEATABLE READ, el nivel por defecto, toma bloqueos next-key: el registro más el hueco anterior, de modo que no se puede insertar ninguna fila nueva en el rango recorrido hasta que termine la transacción.
• READ COMMITTED libera los bloqueos de las filas que resultaron no coincidir, y solo toma bloqueos de hueco para comprobar claves foráneas y claves duplicadas.
• Index condition pushdown (desde 5.6) evalúa las condiciones sobre columnas del índice sin salir del índice, antes de leer la fila. EXPLAIN muestra Using index condition. Solo funciona en índices secundarios, y significa que se leen y bloquean menos filas.

Así que un índice estrecho también es una herramienta de concurrencia: cuantas menos entradas recorre una escritura, menos filas bloquea. El sandbox de QueryCrack solo ejecuta sentencias SELECT simples, así que aquí no tomarás bloqueos, pero el recuento de lecturas que minimizas es el mismo número que decide cuánto bloquearía una escritura.

Cómo se refleja en tu veredicto

Aquí no puedes añadir índices: cada reto trae un esquema fijo. Lo que cambias es si la consulta permite al motor usarlos, y el evaluador lo lee directamente de los contadores handler. Para ver con qué trabajas, ejecuta SHOW INDEX FROM bugs y luego haz EXPLAIN de tu consulta para ver qué índice aparece en la columna key.

Preguntas frecuentes