# MariaDB desde cero (IV): índices, EXPLAIN y tuning

*Cuarta entrega de la serie **[MariaDB desde cero a pro](/search?tag=mariadb-desde-cero)**. Tiempo de lectura estimado: 13 minutos.*

Ya tienes tablas bien tipadas ([II](/post/mariadb-desde-cero-ii-storage-engines-tipos-y-restricciones)) y sabes exprimir el SQL moderno ([III](/post/mariadb-desde-cero-iii-consultas-ctes-y-window-functions)). Antes o después, algo irá lento. Este post es sobre cómo averiguar por qué y qué hacer al respecto.

La filosofía es la misma que en la [entrega IV de PostgreSQL](/post/postgresql-desde-cero-iv-indices-explain-y-rendimiento): medir antes de optimizar, entender el plan antes de tocar nada, y conocer los cuatro o cinco parámetros que de verdad mueven la aguja.

## InnoDB: tabla = índice clustered

En InnoDB (que es lo que usarás casi siempre), **los datos de la tabla se almacenan físicamente en un árbol B+ ordenado por la clave primaria**. A esto se le llama *clustering key* o *clustered index*.

Consecuencias:

- Las consultas por PK (o rango de PK) son baratísimas.
- Los índices secundarios almacenan el valor de la PK como "puntero" a la fila. Si la PK es ancha, todos los índices lo son.
- Insertar con PK aleatoria (UUID v4) fragmenta el árbol. Insertar con PK secuencial (AUTO_INCREMENT) es óptimo.

Regla práctica: **PK numérica auto-incremental corta**. Si necesitas UUID, guárdalo como columna secundaria con su propio índice único, o usa UUID v7 (ordenado por tiempo).

## Tipos de índices

### B-tree secundario

El estándar. Sirve para igualdad, rango y orden.

```sql
CREATE INDEX ix_posts_author ON posts(author_id);
CREATE INDEX ix_posts_published ON posts(published_at);
```

### Índices compuestos

El orden importa. El principio es el mismo que en cualquier SGBD:

```sql
-- Bueno para WHERE author_id = ? AND published_at > ?
CREATE INDEX ix_posts_author_pub ON posts(author_id, published_at);
```

Cubre:

- `WHERE author_id = ?`
- `WHERE author_id = ? AND published_at > ?`
- `WHERE author_id = ? ORDER BY published_at`

No cubre `WHERE published_at > ?` por sí solo.

### Índices sobre columnas virtuales

Como vimos en la [entrega II](/post/mariadb-desde-cero-ii-storage-engines-tipos-y-restricciones), el patrón idiomático para indexar expresiones es crear una columna virtual y un índice sobre ella:

```sql
ALTER TABLE users
  ADD COLUMN email_lower VARCHAR(320) AS (LOWER(email)) VIRTUAL,
  ADD INDEX ix_users_email_lower (email_lower);

-- Usa el índice
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';
```

### Covering (includes en el compuesto)

MariaDB no tiene `INCLUDE` como PostgreSQL. El equivalente es meter las columnas necesarias dentro del propio índice:

```sql
CREATE INDEX ix_posts_slug_covering
  ON posts(slug, title, published_at);
```

Si la consulta pide solo `slug`, `title` y `published_at`, el motor responde desde el índice, sin tocar la tabla (*index-only scan*).

### Fulltext

```sql
CREATE FULLTEXT INDEX ft_posts_body ON posts(title, body);

SELECT id, title,
       MATCH(title, body) AGAINST('mariadb performance' IN NATURAL LANGUAGE MODE) AS score
FROM posts
WHERE MATCH(title, body) AGAINST('mariadb performance' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 20;
```

Suficiente para buscadores internos de blogs o catálogos medianos. Para búsquedas sofisticadas, Elasticsearch, Typesense o Meilisearch.

### Spatial

Para tipos geométricos `POINT`, `POLYGON`, etc.:

```sql
CREATE TABLE places (
  id      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name    VARCHAR(200),
  location POINT NOT NULL,
  SPATIAL INDEX sp_places (location)
) ENGINE=InnoDB;
```

Consultas tipo "¿qué hay en este bounding box?" se resuelven vía índice R-tree.

## EXPLAIN

El plan del optimizador:

```sql
EXPLAIN SELECT * FROM posts WHERE author_id = 42;
```

Salida tabular con columnas clave:

- `type`: tipo de acceso. De mejor a peor: `const`, `eq_ref`, `ref`, `range`, `index`, `ALL` (full table scan).
- `key`: índice usado. `NULL` significa que no se usa.
- `rows`: filas estimadas que se leerán.
- `Extra`: información adicional. `Using index` (solo índice), `Using where` (filtro post-scan), `Using temporary` (temp table), `Using filesort` (ordenación en disco/memoria).

### EXPLAIN ANALYZE

Desde MariaDB 10.1. Ejecuta la consulta y muestra tiempos reales:

```sql
EXPLAIN ANALYZE
SELECT p.title, a.name
FROM posts p
JOIN authors a ON p.author_id = a.id
WHERE p.status = 'published';
```

Incluye:

- `r_rows`: filas reales leídas por loop.
- `r_total_time_ms`: tiempo real acumulado.

Cuando `rows` (estimado) y `r_rows` (real) están muy lejos, el optimizador está decidiendo mal. Suele ser que hay que lanzar `ANALYZE TABLE` o ajustar estadísticas.

### FORMAT=JSON

Vista mucho más detallada:

```sql
EXPLAIN FORMAT=JSON SELECT ...;
```

Incluye `cost_info` de cada paso, condiciones empujadas, uso real de índices. Para planes complicados es donde encuentras la respuesta.

## Estadísticas del optimizer

MariaDB usa dos fuentes de estadísticas:

- **InnoDB persistent stats** (por defecto): estadísticas guardadas en tablas de `mysql`. Se calculan con `ANALYZE TABLE` y de forma automática.
- **Engine-independent statistics**: controladas por `use_stat_tables`. Más detalladas, pero menos usadas.

Para recalcular estadísticas:

```sql
ANALYZE TABLE posts;
```

Para tablas muy activas, puedes configurar que se recalculen al cambiar cierto porcentaje:

```sql
ALTER TABLE posts
  STATS_AUTO_RECALC = 1,
  STATS_SAMPLE_PAGES = 100;
```

Más páginas = más precisión = más coste. 20 (default) es poco para tablas grandes; 100-200 suele dar mucha mejor calidad.

## Slow query log

La herramienta más útil para encontrar las consultas problemáticas:

```ini
[mariadb]
slow_query_log = 1
long_query_time = 0.5
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
```

Para agregarlo:

```bash
mariadb-dumpslow -s t /var/log/mysql/slow.log | head -30
```

Ordena las consultas por tiempo total (`-s t`), tiempo medio (`-s at`), número de llamadas (`-s c`). Es la primera parada cuando algo va mal en producción.

## PERFORMANCE_SCHEMA y sys

`performance_schema` es el esquema de estadísticas detalladas. Desde MariaDB 10.5 hay un helper `sys` con vistas cómodas:

```sql
-- Top consultas por tiempo total
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1e12 AS total_s,
       AVG_TIMER_WAIT/1e9 AS avg_ms, SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- Tablas con más filas leídas
SELECT OBJECT_SCHEMA, OBJECT_NAME, COUNT_READ, SUM_TIMER_READ/1e12 AS total_s
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql','performance_schema','information_schema')
ORDER BY SUM_TIMER_READ DESC
LIMIT 20;
```

Si activas `performance_schema` (normalmente viene on), tienes un radar permanente sin necesidad de extensiones externas.

## Parámetros de servidor que más se notan

La mayoría viven en `/etc/mysql/mariadb.conf.d/50-server.cnf` (Debian/Ubuntu).

### InnoDB

```ini
innodb_buffer_pool_size = 12G      # ~70% de la RAM en servidor dedicado
innodb_buffer_pool_instances = 8   # paralelismo de pool
innodb_log_file_size = 1G          # redo log. Grande = menos checkpoints
innodb_flush_log_at_trx_commit = 1 # 1 = durable, 2 = cada segundo, 0 = peligroso
innodb_flush_method = O_DIRECT     # evita double-buffering en Linux
innodb_io_capacity = 2000          # IOPS en SSD. 200 en HDD
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 0      # 0 = sin límite
```

El más importante con diferencia: `innodb_buffer_pool_size`. Todo lo demás afecta mucho menos.

### Conexiones

```ini
max_connections = 500
thread_cache_size = 100
table_open_cache = 4000
table_definition_cache = 2000
```

Muchas conexiones simultáneas consumen RAM (cada una reserva buffers). Como en PostgreSQL, pon un pool delante (**ProxySQL**, **HAProxy** con MaxScale, **PgBouncer**-like para MySQL).

### Memoria por operación

```ini
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
tmp_table_size = 64M
max_heap_table_size = 64M
```

**Atención**: estos buffers se reservan por cliente y por operación. Un valor alto puede reventar la RAM si tienes muchas conexiones simultáneas.

### Logs

```ini
log_error = /var/log/mysql/error.log
log_warnings = 2
general_log = 0            # NO activar en producción
log_output = FILE
binlog_format = ROW        # para replicación moderna
```

## Receta de depuración

Cuando una consulta va lenta:

1. `EXPLAIN ANALYZE` y compara `rows` con `r_rows`.
2. Si están descuadrados: `ANALYZE TABLE` para refrescar estadísticas. Si persiste, sube `STATS_SAMPLE_PAGES`.
3. Si ves `type: ALL` sobre tabla grande: falta un índice adecuado.
4. Si ves `Using filesort`: o creas un índice con el orden, o subes `sort_buffer_size`, o reduces el número de filas antes de ordenar.
5. Si ves `Using temporary`: revisa `GROUP BY`/`DISTINCT` sobre columnas sin índice.
6. Si el JOIN es lento: valida que las columnas del `ON` están indexadas **en ambas tablas** y con el mismo tipo/charset.
7. Si `InnoDB_buffer_pool_reads` / `InnoDB_buffer_pool_read_requests` es alto (>1%): el buffer pool es pequeño, hay que crecer.

## Bloqueos y deadlocks

InnoDB detecta deadlocks automáticamente y aborta una transacción con error 1213. Para ver el último:

```sql
SHOW ENGINE INNODB STATUS\G
```

La sección `LATEST DETECTED DEADLOCK` muestra las dos transacciones implicadas, las queries y los locks.

Para reducir deadlocks:

- Accede a las filas siempre en el mismo orden.
- Transacciones cortas. Muy cortas.
- Nada de esperas largas (llamadas HTTP, jobs pesados) dentro de una transacción abierta.
- Si haces "bloquea y procesa", usa `SELECT ... FOR UPDATE SKIP LOCKED` (soportado desde 10.6).

## Online DDL

Casi todos los `ALTER TABLE` se hacen en línea sin bloquear escrituras, pero hay excepciones. Comprueba el algoritmo:

```sql
ALTER TABLE posts
  ADD COLUMN reading_time INT,
  ALGORITHM=INSTANT;

ALTER TABLE posts
  ADD INDEX ix_posts_slug (slug),
  ALGORITHM=INPLACE, LOCK=NONE;
```

Opciones:

- `ALGORITHM=INSTANT`: cambios metadata-only (añadir columna sin default específico). Inmediato.
- `ALGORITHM=INPLACE`: modifica la tabla en su sitio. Bloquea escrituras solo al principio y al final.
- `ALGORITHM=COPY`: copia toda la tabla. Lento y bloquea.
- `LOCK=NONE`: intenta no bloquear escrituras.

Para tablas enormes, herramientas como **pt-online-schema-change** (Percona Toolkit) o **gh-ost** (GitHub) son la solución histórica. En MariaDB moderno, `ALGORITHM=INPLACE, LOCK=NONE` resuelve la mayoría de casos.

## Connection pooling

Como en todas partes: pon un pool delante. En el ecosistema MariaDB/MySQL:

- **ProxySQL**: proxy completo, con routing, caché, reescritura de queries. Mi elección por defecto.
- **MaxScale** (MariaDB Corporation): proxy con HA, routing, filtros. Bajo licencia BSL.
- **HAProxy**: L4 puro. Suficiente para balanceo simple.
- **Librería con pool interno**: la mayoría de ORMs ya traen pooling básico, útil en apps de un solo proceso.

## Por dónde seguir

- **[V: replicación, Galera y producción](/post/mariadb-desde-cero-v-replicacion-galera-y-produccion)** — último tramo: operar MariaDB en serio.

Repasando:

- [I: instalación y primeros pasos](/post/mariadb-desde-cero-i-instalacion-y-primeros-pasos)
- [II: storage engines, tipos y restricciones](/post/mariadb-desde-cero-ii-storage-engines-tipos-y-restricciones)
- [III: consultas, CTEs y window functions](/post/mariadb-desde-cero-iii-consultas-ctes-y-window-functions)