Explicación interna del optimizador de consultas de MariaDB: conceptos básicos, planes y aplicación práctica

Voy a explicar el Optimizador de MariaDB Ejemplos prácticos: cómo elabora planes, cómo estima los costes y por qué a veces se equivoca. Así podrás interpretar el plan de ejecución de SQL de forma específica, utilizar los índices de manera adecuada y guiar al optimizador basándote en datos, en lugar de en corazonadas.

Puntos centrales

Para empezar, voy a resumir brevemente los conceptos fundamentales, para que puedas contextualizar mejor los apartados siguientes y el Visión general conservas.

• Fases: El análisis, la preparación, la optimización y la ejecución conforman el ciclo de vida de cada consulta.
• Modelo de costes: Los valores basados en el tiempo, expresados en microsegundos, controlan la selección de índices, los escaneos y el orden de las uniones.
• Estadísticas: La cardinalidad y los histogramas determinan la estimación de la selectividad.
• Transparencia: EXPLAIN, EXPLAIN ANALYZE y Optimizer Trace permiten acceder al funcionamiento interno del sistema.
• Sintonización: Los índices, las reescrituras de consultas, ANALYZE TABLE y los parámetros de coste aumentan la velocidad.

Ciclo de vida de una consulta en MariaDB

Antes de que se elabore un plan, una consulta pasa por cuatro etapas que compruebo de forma específica en mi día a día para Causas que provoquen lentitud. Durante el análisis sintáctico, MariaDB convierte el SQL en una estructura interna; aquí es donde se detectan los errores sintácticos. En la fase de preparación, el motor comprueba las tablas, las columnas y los posibles índices, y lleva a cabo transformaciones sencillas. A continuación tiene lugar la optimización, en la que se calculan los planes candidatos y se evalúan mediante un modelo de costes. Durante la ejecución, el servidor aplica el plan seleccionado paso a paso: leer, unir, filtrar, devolver.

Distingo claramente los errores de análisis por fase, porque así los diagnósticos surten efecto más rápidamente y Medidas actuar de forma específica. En la mayoría de los casos, los problemas de rendimiento tienen su origen en la optimización: estimaciones erróneas, falta de índices o secuencias de uniones desfavorables. Los errores de análisis sintáctico son triviales, pero la fase de preparación ya puede incluir técnicas como la resolución de vistas o la transformación de subconsultas. En la ejecución, las ineficiencias se hacen evidentes sin piedad si previamente se ha seleccionado un escaneo completo. Por eso, comienzo cada análisis con una revisión estructurada de las cuatro etapas.

Cómo toma decisiones internamente el optimizador

MariaDB funciona basándose en costes y evalúa las ejecuciones alternativas mediante una Función de coste. Para cada variante, el servidor estima el número de filas leídas, la selectividad de WHERE/ON, los tipos de acceso (como Table Scan, Index Scan y Range Scan), así como el tiempo que requieren las operaciones individuales. Internamente, el servidor distingue entre «join_preparation» y «join_optimization». En «join_preparation» se llevan a cabo reescrituras de consultas, simplificaciones de condiciones, transformaciones de subconsultas y resoluciones de vistas. En «join_optimization» se calculan los órdenes de unión, se comprueban los índices candidatos mediante «ref_optimizer_key_uses», se estiman las filas mediante «Range Scan» y se asignan las condiciones a tablas concretas lo antes posible.

Este mecanismo explica por qué un filtro pequeño colocado en el lugar equivocado puede provocar costosas Consecuencias tiene. Si la operación «attaching_conditions_to_tables» se realiza tarde, el plan arrastra un número innecesario de filas a través de las uniones. Si las estadísticas están desactualizadas, «rows_estimation» y «Selectivity» dan resultados erróneos; en ese caso, el optimizador recurre a rutas de acceso que parecen favorables, pero que en realidad son lentas. Es precisamente en estos aspectos donde intervengo: mejores estadísticas, predicados más claros e índices compuestos ordenados correctamente. Tras ello, la elección del plan suele cambiar de forma notable.

Modelo de costes a partir de MariaDB 11.0

Las versiones actuales ya no evalúan el trabajo de forma aproximada mediante pesos, sino con microsegundos para operaciones concretas de almacenamiento. Parámetros como `optimizer_disk_read_cost`, `optimizer_disk_read_ratio` y `optimizer_where_cost` acercan el modelo a los tiempos de ejecución reales. De este modo, el optimizador compara el escaneo de rango de índice con el escaneo completo basándose en suposiciones de tiempo reales. LAST_QUERY_COST muestra el coste total estimado y, a menudo, se ajusta mucho mejor a la realidad que antes. En los sistemas con gran volumen de datos, esta mayor precisión da sus frutos de inmediato.

Calibro el modelo con cuidado cuando las características del hardware contradicen los supuestos predeterminados y, por lo tanto, la Selección de planes distorsionar. Las unidades SSD NVMe, los sistemas de almacenamiento distribuido o las cachés especiales pueden alterar notablemente la relación de disco y los tiempos de lectura. Pequeños ajustes en los valores de «optimizer_costs» hacen que MariaDB dé prioridad a las rutas más adecuadas. Documento cada cambio y, a continuación, compruebo EXPLAIN ANALYZE para medir el impacto. Sin mediciones, el ajuste es una lotería.

Selectividad, estadísticas e histogramas

Las buenas estimaciones comienzan con datos limpios cardinalidad y una selectividad fiable. MariaDB mantiene estadísticas sobre los distintos valores de cada columna y, opcionalmente, puede utilizar histogramas para las distribuciones. Precisamente los datos irregulares —puntos calientes, distribuciones de Zipf, patrones estacionales— se benefician de los histogramas. Tras cambios importantes en los datos, ejecuto ANALYZE TABLE para que la optimización vuelva a basarse en datos reales. Quien se olvide de hacerlo, se arriesga a que se realicen escaneos completos que, objetivamente, son erróneos.

Voy a programar ANALYZE como una tarea periódica, adaptada a Cambios en el volumen de datos y en tablas críticas. Cuando la distribución de las columnas presenta una fuerte asimetría, los histogramas ayudan a evaluar de forma realista la selectividad de los valores singulares. Esto reduce los errores de estimación en los escaneos de rango y las estrategias de fusión. En combinación con índices compuestos adecuados, la precisión de los resultados mejora drásticamente. Resultado: tiempos de ejecución más cortos y menos E/S.

EXPLAIN y cómo leer los planes de ejecución

Para hacer visibles las decisiones, utilizo EXPLAIN, EXPLAIN EXTENDED y FORMATO=JSON. Las columnas clásicas ofrecen una visión general rápida: id, select_type, table, type, possible_keys, key, key_len, ref, rows y, en su caso, filtered. Un «type=ALL» indica un escaneo completo, algo que rara vez es deseable. «FORMAT=JSON» muestra en detalle cómo se han reubicado las condiciones y qué rutas ha evaluado el optimizador. En el contexto del alojamiento web, recomiendo la guía sobre Planes de ejecución en el alojamiento web, para vincular la información del plan con los efectos sobre las infraestructuras.

Para interpretarlo rápidamente, me resulta útil una pequeña tabla que resume brevemente los valores típicos y, de este modo, Interpretaciones erróneas se evita.

Campo EXPLAIN	Valor típico	Importancia en la práctica
tipo	ALL, range, ref, eq_ref, const	Cuanto más a la derecha, más selectivo; «ALL» indica un escaneo completo.
possible_keys	Lista de índices	Índices que, en teoría, encajan; si aquí faltan candidatos, falta estructura.
clave	Nombre del índice	Índice realmente utilizado; si está en blanco, significa que no se utiliza ningún índice.
filas	Número	Número estimado de líneas leídas; si difiere mucho de la realidad, significa que las estadísticas son poco fiables.
filtrado	Porcentaje	La cantidad que se pasa al filtro; suele ser mejor que sea poca.

Por qué el optimizador a veces se equivoca

Ningún modelo de costes se adapta a todas las situaciones, por lo que lo corrijo Errores De forma específica. Las estadísticas obsoletas dan lugar a estimaciones erróneas de las filas y a secuencias de uniones desfavorables. Los índices compuestos mal estructurados impiden el uso de índices en filtros de varias columnas. Las subconsultas muy anidadas dificultan las reescrituras efectivas y bloquean la materialización. Los filtros ausentes o engañosos obligan al motor a mover muchas filas antes de que se apliquen los predicados útiles.

En primer lugar, compruebo si la formulación de la consulta cumple con el Índice Lo que realmente funciona: regla de prefijos a la izquierda, orden de clasificación adecuado, evitar funciones sobre columnas en la cláusula WHERE. A continuación, compruebo en EXPLAIN ANALYZE si la realidad respalda la estimación. Si no es así, ejecuto ANALYZE TABLE y, si es necesario, una reescritura. Solo como último recurso recurro a FORCE INDEX o a las indicaciones (hinting), ya que esto puede limitar las optimizaciones futuras.

Utilizar el Optimizer Trace de forma selectiva

Si EXPLAIN no es suficiente, activo el rastreo del optimizador y hago un seguimiento de Decisiones en el registro JSON. En él puedo ver qué planes se han barajado, descartado o aceptado. Entiendo por qué una condición se aplica tarde o por qué un índice no ha sido seleccionado. El registro también muestra cómo se han reorganizado las condiciones. Esta perspectiva aclara la comprensión y ofrece puntos de actuación concretos para el próximo ajuste.

Guardo los fragmentos relevantes del rastreo junto con el hash de la consulta y Parámetrosvalorar. Así podré comparar más adelante qué cambio ha tenido qué efecto. La documentación del servidor MariaDB y diversas ponencias del ecosistema describen estos campos en detalle (fuente: documentación del servidor MariaDB sobre el optimizador de consultas y el rastreo del optimizador). Con esta herramienta, detecto los supuestos erróneos más rápido que con el método de prueba y error. Ahorro tiempo sobre todo en uniones complejas.

Práctica: Optimización de bases de datos paso a paso

Empiezo cada optimización con una clara Medición. Identifico los problemas mediante la supervisión y el Registro de consultas lentas. A continuación, comparo EXPLAIN con EXPLAIN ANALYZE para comparar el plan y el resultado real. Adapto la estrategia de indexación a WHERE, JOIN y ORDER BY; los índices compuestos los oriento hacia los puntos de acceso más frecuentes. Solo utilizo FORCE INDEX cuando el optimizador, a pesar de contar con estadísticas correctas, elige el candidato equivocado.

Cada paso conlleva el cuidado de la Estadísticas: ANALYZE TABLE en tablas con mucho tráfico; histogramas para distribuciones asimétricas. Simplifico las subconsultas innecesarias, materializo resultados intermedios cuando es necesario y elimino soluciones provisionales antiguas. En el caso de hardware especial, compruebo los optimizer_costs para garantizar que el modelo de microsegundos sea correcto. Documento cada cambio con valores «antes» y «después», para que el efecto sea comprensible a largo plazo.

Problemas típicos de los optimizadores y sus soluciones

Si EXPLAIN muestra type=ALL, aunque possible_keys esté lleno, lo primero que miro es Selectividad. A menudo, el orden de las columnas en el índice compuesto no es el adecuado o alguna función impide el uso del índice. En esos casos, invierto el orden, elimino las funciones que causan problemas o divido los predicados. Si el orden de las uniones es incorrecto, compruebo si es posible aplicar un filtrado previo, por ejemplo, dando prioridad a la tabla más selectiva. Cuando resulta conveniente, convierto las subconsultas en uniones o en tablas TEMPORARY.

También reconozco las decisiones erróneas por las que se desvían mucho filas entre lo previsto y la realidad. En ese caso, resulta útil utilizar la instrucción ANALYZE TABLE o generar un histograma de la columna en cuestión. Si ni siquiera unas estadísticas correctas permiten alcanzar el objetivo, me planteo utilizar hints explícitos. Antes de nada, guardo una copia de seguridad de la comprobación cruzada y los valores medidos, para que las versiones posteriores del optimizador no se vean frenadas por los datos almacenados. La disciplina a la hora de documentar da sus frutos en este caso.

Contexto del alojamiento web y aspectos operativos

La calidad de las consultas y la infraestructura deben ir de la mano; de lo contrario, la aplicación no rinde al máximo. Posible. Los SSD rápidos, las cachés consistentes y una configuración adecuada son la base sobre la que el optimizador toma buenas decisiones. Un tráfico elevado no admite escaneos completos; unas pocas consultas deficientes pueden ralentizar sistemas enteros. Para entornos MySQL/MariaDB en producción, ofrecemos consejos prácticos como Optimizador de MySQL Reflexiones útiles sobre la combinación entre plan y plataforma. Quien tenga en cuenta este aspecto evita los cuellos de botella antes de que se agraven.

Siempre relaciono el análisis de planes con métricas sobre E/S, latencia y concurrencia. Si los valores no se ajustan al modelo de costes previsto, compruebo los parámetros. A continuación, analizo los tamaños de los búferes, las cargas de trabajo paralelas y la distribución de los conjuntos más solicitados. Con este enfoque, se consigue gestionar de forma armoniosa las consultas y los recursos, y mantener bajo control los picos de actividad.

Las rutas de unión y acceso en la práctica

Aclaro muchos malentendidos explicando que la Tipos de acceso los sopese de forma específica unos contra otros. Un rango- o ref-El acceso funciona casi siempre TODO. En el caso de las relaciones de igualdad sobre claves únicas (eq_ref) los planos son especialmente resistentes. Además, compruebo si un Índice de cobertura que la consulta se procese íntegramente: si todas las columnas necesarias están incluidas en el índice, MariaDB se ahorra costosos accesos a la tabla. Index Condition Pushdown (ICP) Ayuda a comprobar condiciones WHERE adicionales ya en el índice, lo que reduce el número de filas devueltas y las operaciones de E/S.

Acerca de Fusión de índices MariaDB puede combinar varios índices (intersección/unión). Esto resulta útil con predicados «OR» o con varias condiciones de selección, pero suele ser más lento que un índice compuesto bien elegido. Además, estoy evaluando MRR (Lectura multirango) y BKA (Batched Key Access). MRR ordena las claves primarias que se van a leer para suavizar las E/S aleatorias; BKA agrupa las búsquedas de uniones y ofrece ventajas sobre todo en uniones sin superposición. En la práctica, pruebo BKA/MRR mediante optimizer_switch y compruebo con EXPLAIN ANALYZE si los patrones de E/S disminuyen. Sin embargo, si MariaDB recurre a Bloque de bucles anidados (BNL), suele ser más recomendable aumentar el tamaño del búfer de uniones (join_buffer_size) o realizar una reescritura que permita uniones de índices reales.

-- Ejemplo: índice compuesto para unión + filtro + ordenación
CREATE INDEX ix_orders_cust_status_created
  ON orders (customer_id, status, created_at);

-- Acceso típico
SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'open' AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;

Con el índice anterior, el optimizador puede elegir el orden más selectivo, evaluar los filtros desde el principio y, a menudo, realizar la ordenación sin necesidad de una ordenación de archivos adicional.

ORDER BY, GROUP BY, ordenación por archivos y tablas temporales

Ordenar y agrupar lleva tiempo. Me encargo de que ORDER BY y GRUPO POR pueden ejecutarse según el orden del índice. Esto funciona si el prefijo y la dirección coinciden exactamente. De lo contrario, se aplica una Clasificación de archivos con búfer de ordenación (sort_buffer_size) y, si es necesario, una tabla temporal. Si el conjunto de resultados contiene columnas TEXT/BLOB anchas, MariaDB es más rápida. en disco Tablas TEMP (Aria). Lo evito seleccionando solo las columnas necesarias, cargando los campos grandes al final o utilizando prefijos de longitud limitada.

En las agregaciones, siempre que sea posible, utilizo, Escaneo de índice libre (por ejemplo, GROUP BY en la parte principal del índice) y elijo índices compuestos a lo largo de la agrupación. Cuando los resultados intermedios son muy grandes, una materialización con claves adecuadas se adapta mejor que una única mega-unión. Mido regularmente las métricas de los controladores y los contadores Created_tmp_* para detectar puntos críticos de ordenación y de tablas temporales.

Subconsultas, semi-join y materialización

Muchas subconsultas pueden reformularse de manera eficiente durante la preparación. Las construcciones IN/EXISTS pueden expresarse como Semi-join funcionan con estrategias como la materialización o LooseScan. Compruebo si el optimizador es un derived_merge pudo llevar a cabo: si se incluye una tabla derivada (o una CTE con «WITH») en el plan externo, sus índices están disponibles directamente. Si esto no es posible, la subconsulta acaba en una tabla temporal; en ese caso, si es factible, le asigno una clave (por ejemplo, mediante SELECT DISTINCT/ORDER BY en columnas clave), para que las uniones no acaben en el limbo.

-- Ejemplo: EXISTS en lugar de IN y tabla derivada compatible con Merge
SELECT o.id
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM payments p
  WHERE p.order_id = o.id AND p.state = 'captured'
);

-- Tabla derivada con claves únicas
WITH paid_orders AS (
  SELECT DISTINCT order_id
  FROM payments
  WHERE state = 'captured'
)
SELECT o.*
FROM orders o
JOIN paid_orders po ON po.order_id = o.id;

Compruebo con EXPLAIN FORMAT=JSON si materializado o subconsulta dependiente se haya seleccionado y si hay condiciones (condición de pushdown) actuar con la suficiente antelación.

Partición y poda

La partición no sustituye a los índices, pero puede Volumen de datos por acceso reducir drásticamente. El optimizador solo realiza una poda correcta si el predicado cumple la Clave de partición se identifique de forma inequívoca y no quede enmascarado por funciones. Por eso evito expresiones como DATE(created_at) en la cláusula WHERE de tablas particionadas y, en su lugar, trabajo con límites de rango. EXPLAIN muestra qué particiones se leen; los rangos muy amplios indican una mala poda.

Un número excesivo de particiones pequeñas aumenta la sobrecarga de planificación. Por eso, elijo una granularidad adecuada (por ejemplo, mensual en lugar de diaria), mantengo actualizadas las estadísticas de cada partición (ANALYZE PARTITION) y compruebo si los índices importantes están disponibles localmente en las particiones. En los proyectos de migración, tengo en cuenta el impacto en la replicación y las copias de seguridad, ya que ambos factores influyen en el grado de segmentación que aplico.

Sargabilidad y patrones de reescritura

La forma más sencilla sigue siendo Capacidad de embalaje en ataúd – Condiciones que permiten utilizar índices. Evito las funciones en las columnas del WHERE, devuelvo constantes en el lado de las columnas y, si es necesario, desgloso las condiciones OR en UNIÓN TODOS. Para búsquedas con el operador LIKE sin ancla inicial ("%foo"), un índice BTREE no sirve de nada; en este caso, tengo pensado utilizar la búsqueda de texto completo o un servicio de búsqueda adecuado. Para los cálculos, utilizo columnas generadas indexadas, para que el optimizador pueda identificar la lógica del índice.

-- Antipatrón: función sobre una columna
WHERE DATE(created_at) = '2026-08-01'
-- Mejor: rango sobre el valor sin procesar
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02'

-- Antipatrón: el operador OR impide el uso del índice
WHERE status = 'open' OR customer_id = 42
-- Mejor: dos búsquedas con UNION ALL y cada una con su propio índice
(SELECT ... WHERE status = 'open')
UNION ALL
(SELECT ... WHERE customer_id = 42');

En cuanto a los índices compuestos, considero que la regla del prefijo de la izquierda Aplícalo estrictamente: ordena las columnas según su selectividad y según el orden de clasificación que se vaya a necesitar posteriormente. Si necesito un ORDER BY descendente, lo tengo en cuenta en la estructura del índice; así me ahorro la clasificación de archivos.

Opciones del optimizador y ajuste preciso de los costes

Antes de empezar a trabajar en las consultas, compruebo optimizer_switch y memoria intermedia. Funciones como mrr, batched_key_access, index_merge, semijoin, derived_merge o condition_pushdown_for_derived se pueden ajustar por sesión. Activo candidatos de forma selectiva para una sesión de prueba, mido con EXPLAIN ANALYZE y revierto los cambios si no se observa ningún efecto. La ruta de unión se beneficia de una cantidad suficiente de join_buffer_size; grandes variedades de sort_buffer_size. Al mismo tiempo, vigilo los búferes en relación con la concurrencia, para que el servidor no tenga que recurrir al intercambio de memoria bajo una carga paralela.

En lo que respecta a los costes, ajusto, si es necesario, los ya mencionados costes_del_optimizador en microsegundos. Mi guía: pasos pequeños y reversibles con puntos de medición documentados. Yo utilizo LAST_QUERY_COST para comprobar la plausibilidad y repetir las mediciones con valores de parámetros realistas, ya que los planes pueden depender en gran medida de literales concretos.

Estabilidad de los planes, regresiones y flujo de trabajo en equipo

Incluso un buen plan puede verse afectado por el aumento del volumen de datos o los cambios de versión volcar. Por eso me aseguro de disponer de información sobre los planes de ejecución: hash de consultas, EXPLAIN-JSON, fragmentos de trazas del optimizador y tiempos de ejecución de EXPLAIN ANALYZE. Los cambios en los índices y las reescrituras los realizo mediante pull requests con pruebas de antes y después. En entornos de CI/CD, compruebo automáticamente las consultas críticas con conjuntos de datos representativos. Así es como empiezo Planes de regresión temprano.

Para los casos delicados, creo que Consejos (FORCE INDEX, STRAIGHT_JOIN, optimizer_switch por consulta) están disponibles como última opción, pero utilízalas con moderación y con una fecha de caducidad. Es mejor solucionar las causas: estadísticas, índices, formulación. En los equipos, una guía sencilla sobre la optimización, el diseño de índices y la disciplina de medición garantiza que las nuevas funcionalidades no introduzcan problemas de rendimiento sin que nos demos cuenta.

Resumen: Del plan al resultado

¿Quién puede utilizar el Plan Comprenderlo permite controlar el rendimiento. Las fases de análisis sintáctico, preparación, optimización y ejecución explican dónde se pierde tiempo. El modelo de costes basado en el tiempo a partir de la versión 11.0, junto con estadísticas y histogramas actualizados, hacen que las estimaciones sean fiables. EXPLAIN, EXPLAIN ANALYZE y el rastreo del optimizador aportan transparencia, que traduzco en medidas concretas. Con una estrategia de índices bien definida, un diseño claro de las consultas y una infraestructura adecuada, las consultas de MariaDB ofrecen respuestas rápidas de forma constante.