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

Cuarta entrega de la serie MariaDB desde cero a pro. Tiempo de lectura estimado: 13 minutos.

Ya tienes tablas bien tipadas (II) y sabes exprimir el SQL moderno (III). 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: 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.

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:

-- 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, el patrón idiomático para indexar expresiones es crear una columna virtual y un índice sobre ella:

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:

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

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.:

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:

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:

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:

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:

ANALYZE TABLE posts;

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

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:

[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:

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:

-- 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

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

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

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

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:

EXPLAIN ANALYZE y compara rows con r_rows.
Si están descuadrados: ANALYZE TABLE para refrescar estadísticas. Si persiste, sube STATS_SAMPLE_PAGES.
Si ves type: ALL sobre tabla grande: falta un índice adecuado.
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.
Si ves Using temporary: revisa GROUP BY/DISTINCT sobre columnas sin índice.
Si el JOIN es lento: valida que las columnas del ON están indexadas en ambas tablas y con el mismo tipo/charset.
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:

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:

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 — último tramo: operar MariaDB en serio.

Repasando: