Índices

  • Lección 10 de 40
  • 12 min
  • Inicial
  • 11 pasos

Un índice convierte un recorrido de millones de filas en un puñado de lecturas. Verás cómo funciona un árbol B, lo que cuesta mantenerlo y cuándo no sirve de nada.

p95 de la consulta
1 ms
Filas leídas
1
Escrituras por INSERT
3
Índices
2
AppAppA–ZraízA–ZraízpaísraízpaísraízA–ZraízA–ZA–HhojaA–HhojaAR ·ciudadhojaAR–CLhojaA–H +nombrehojaA–H + nombreI–QhojaI–QhojaCL ·ciudadhojaCO–MXhojaI–Q +nombrehojaI–Q + nombreR–ZhojaR–ZhojaMX ·ciudadhojaPE–VEhojaR–Z +nombrehojaR–Z + nombretabla users · 50M filaslee cada páginaLa consultaSELECT * FROM usersWHERE email = 'ana@example.com'50.000.000 filas · sin índicelee cada páginaOrdenado → búsqueda binariadescarta la mitad en cada paso2^25 ≈ 33,5M < 50M < 2^26 ≈ 67,1M≈ 26 pasos hasta la fila26 comparaciones, no 50 millonesCREATE INDEX (email)1 fila3 nivelesAlcance por nivelcada nodo ≈ 500 claves (ejemplo)2 niveles: 500 × 500 = 250.000 filas3 niveles: 500 × 500 × 500 = 125M3 niveles + 1 página de tabla = 4 lecturasINSERT fila+1 escrituralee cada página¿Puede usar el índice?pais = 'AR' AND ciudad = 'Rosario'sípais = 'AR'síciudad = 'Rosario'no: recorridolee cada página¿Puede usar el índice?email = 'ana@…'sí: búsquedaemail LIKE 'ana%'sí: rangoemail LIKE '%@example.com'no: recorridoLOWER(email) = '…'no: recorridolee cada páginaignorado¿Cuántas filas coinciden?email = 'ana@…'1 filaíndiceestado = 'pendiente'0,1 %índice parcialpais = 'MX'40 %mejor recorrerun índice ayuda cuando descarta casi todas las filasEXPLAINSELECT … WHERE email = 'ana@…'→ Index Scan · rows=1SELECT … WHERE email LIKE '%@…'→ Seq Scan · rows=50000000INCLUDE (id, nombre)responde aquípocas lecturas
  • petición
  • respuesta
  • petición lenta

Paso 1 de 11

Problema

Buscar a un usuario tarda dos segundos

Para iniciar sesión, la app busca al usuario por su email. Con 50 millones de filas (cifras de ejemplo), la consulta pasa de los dos segundos y empeora a medida que la tabla crece.

Sin nada que la oriente, la base de datos hace un recorrido completo de la tabla (full table scan): lee todas sus páginas y compara fila por fila. Mira la petición lenta que se arrastra hasta la tabla.

Paso 2 de 11

Concepto

Lo que hace una guía telefónica

Nadie busca un nombre en la guía leyendo página por página. Como está ordenada, la abres por la mitad, ves si el nombre va antes o después y descartas la otra mitad. Y repites.

Partir 50 millones por la mitad una y otra vez deja una sola entrada en unos 26 pasos, porque 2 elevado a 26 ya supera los 67 millones.

Paso 3 de 11

Solución

Crea un índice

CREATE INDEX ON users (email) construye un árbol B: una estructura pequeña de claves ordenadas. La raíz indica qué rama contiene tu email y la hoja señala la página exacta donde vive la fila.

Un puñado de saltos en lugar de cincuenta millones de filas (el dibujo resume el árbol en dos niveles): la búsqueda baja a unos 2 ms. A cambio, cada INSERT escribe ahora en dos sitios; lo verás en dos pasos.

Qué cambió

  • Índice de árbol B sobre email
  • Filas leídas: 50.000.000 → 1
  • p95: 2,3 s → 2 ms
  • Escrituras por INSERT: 1 → 2

Paso 4 de 11

Técnica

Por qué el árbol B es tan bajo

Cada nodo del árbol ocupa una página de disco con cientos de claves, así que cada nivel multiplica por cientos las filas a las que se llega.

Con 500 claves por nodo (valor de ejemplo), 3 niveles ya cubren 125 millones de filas: nuestros 50 millones caben de sobra. Los niveles de arriba se usan tanto que suelen quedarse en memoria.

Paso 5 de 11

Compromiso

Cada índice encarece las escrituras

Cuando alguien se registra, la fila nueva va a la tabla y también a cada índice, que tiene que seguir ordenado. Mira las dos escrituras: una llega a la tabla y la otra baja por el árbol hasta su hoja.

Además, los índices ocupan disco y memoria. Indexa lo que consultas, no todo lo que existe.

Qué cambió

  • El INSERT escribe en la tabla y en el índice
  • Escrituras por INSERT: 2 (1 tabla + 1 índice)

Paso 6 de 11

Técnica

Índices compuestos: el orden importa

Un índice puede abarcar varias columnas, como (pais, ciudad). Se ordena primero por país y, dentro de cada país, por ciudad: igual que una lista ordenada por apellido y luego por nombre.

Sirve para buscar por país, o por país y ciudad. Una consulta solo por ciudad no puede aprovecharlo y vuelve al recorrido completo: otra vez más de dos segundos.

Qué cambió

  • Índice compuesto (pais, ciudad)
  • Consulta solo por ciudad → recorrido completo
  • Escrituras por INSERT: 2 → 3

Paso 7 de 11

Problema

Caso: consultas que el índice no resuelve

Te piden todos los usuarios de un dominio: WHERE email LIKE '%@example.com'. El índice está ordenado por el inicio del email y este patrón no dice nada del inicio, así que toca recorrer la tabla entera.

Pasa lo mismo con las funciones: WHERE LOWER(email) = … no aprovecha un índice normal sobre email, porque el índice guarda los valores tal como se escribieron.

Qué cambió

  • Consulta con comodín al inicio
  • Índice ignorado → recorrido completo

Paso 8 de 11

Problema

Caso: el índice que nadie usa

Soporte quiere todos los usuarios de un país: WHERE pais = 'MX'. Creas un índice sobre país… y la base de datos lo ignora, porque en este ejemplo coincide el 40 % de las filas.

Seguir el índice obligaría a saltar a una página distinta de la tabla por cada una de 20 millones de filas: más lento que leer la tabla de principio a fin. Un índice compensa cuando es selectivo, es decir, cuando reduce la búsqueda a una fracción pequeña de las filas.

Qué cambió

  • Índice sobre país
  • Coincide el 40 % → recorrido completo igualmente
  • Escrituras por INSERT: 3 → 4, sin ningún beneficio

Paso 9 de 11

Técnica

Pregúntale a la base de datos: EXPLAIN

No hace falta adivinar. Antepón EXPLAIN a una consulta y la base de datos te muestra su plan: qué índice usará y cuántas filas espera leer.

«Index Scan» sobre pocas filas es buena noticia. «Seq Scan» sobre una tabla grande es la señal de alarma. EXPLAIN ANALYZE ejecuta la consulta de verdad y añade los tiempos reales.

Qué cambió

  • Plan de la consulta a la vista
  • Índice sobre país eliminado
  • Escrituras por INSERT: 4 → 3

Paso 10 de 11

Técnica

Índices que cubren: sin pasar por la tabla

La pantalla de inicio de sesión solo necesita el id y el nombre del usuario. Si el índice guarda también esas columnas, la base de datos responde solo con el índice y no visita las páginas de la tabla.

Es un índice que cubre la consulta (covering index). Fíjate: las búsquedas terminan en la hoja y ya no llegan a la tabla; en este ejemplo el p95 baja a 1 ms.

Qué cambió

  • El índice incluye id y nombre
  • Lecturas de la tabla en esta consulta
  • p95: 2 ms → 1 ms

Paso 11 de 11

Resumen

Resumen

Sin índice, encontrar una fila obliga a recorrer la tabla entera.

Un índice de árbol B llega a cualquier fila con un puñado de lecturas de página.

Cada índice suma trabajo a cada escritura: indexa lo que consultas.

Un índice compuesto se aprovecha desde su columna de la izquierda.

Los comodines al inicio, las funciones y la baja selectividad anulan un índice; compruébalo con EXPLAIN.

Aprenderás

  • Cuánto cuesta recorrer la tabla entera
  • Cómo encuentra una fila un índice de árbol B
  • Qué paga cada escritura por tener índices
  • Índices compuestos y el orden de sus columnas
  • Cuándo un índice no ayuda y cómo comprobarlo