Ir al contenido 
Con el «optimizer trace» de MariaDB puedo comprender, paso a paso, por qué el optimizador elige un plan concreto y qué variantes descarta. Este rastro JSON me muestra Decisiones sobre costes, orden de las uniones y filtros, para poder adaptar las consultas SQL de forma específica.
Puntos centrales
- Transparencia: Un seguimiento basado en JSON explica las reescrituras, los costes y los planes descartados.
- Enfoque: «join_preparation» y «join_optimization» proporcionan la información más relevante.
- Sistema de control: Las variables de sesión reducen la sobrecarga y el consumo de memoria.
- Flujo de trabajo: EXPLAIN/ANALYZE para el plan, Trace para el „porqué“.
- Ventajas prácticas: Ajustar de forma fundamentada los índices, las estadísticas y el orden de las uniones.
¿Qué es el rastreo del optimizador de MariaDB?
Desde la versión 10.4, MariaDB incluye un Optimizador Trace, que documenta en formato JSON cada fase importante de optimización de una instrucción SELECT, UPDATE o DELETE. En él puedo ver cómo el motor amplía las consultas, normaliza las condiciones y, finalmente, determina el orden de las uniones, incluidos los accesos a los índices. Esta visión va mucho más allá de EXPLAIN, que muestra principalmente el plan final, y revela las alternativas descartadas junto con sus justificaciones. El rastro se almacena en memoria para cada conexión y está disponible a través de information_schema.OPTIMIZER_TRACE listo. Así obtengo una explicación completa y legible por máquina de la estructura interna Pasos, que han dado lugar a un plan de ejecución.
Activar y leer el rastreo del optimizador
Activo la función de forma específica en cada sesión para poder realizar diagnósticos sin una sobrecarga general y tener un control total sobre Memoria tengo. Normalmente pongo SET SESSION optimizer_trace = 'enabled=on'; y si es necesario SET SESSION optimizer_trace_max_mem_size = 1048576; o superior, si el rastreo es extenso. A continuación, ejecuto la consulta sospechosa y leo el rastreo con SELECT * FROM information_schema.OPTIMIZER_TRACE LIMIT 1\G;. Importante: la tabla solo almacena la última consulta de la conexión activa, y tengo en cuenta campos como MISSING_BYTES_BEYOND_MAX_MEM_SIZE o INSUFFICIENT_PRIVILEGES para obtener indicaciones de diagnóstico. Este método de trabajo mantiene el entorno de producción optimizado y facilita el análisis preciso.
| Variable/Campo | Propósito | Valor de ejemplo |
|---|---|---|
optimizer_trace | Activa el seguimiento por sesión | 'enabled=on' |
optimizer_trace_max_mem_size | Capacidad máxima de almacenamiento por traza | 1048576 (1 MB) |
OPTIMIZER_TRACE.QUERY | Sentencia SQL original | SELECT ... |
OPTIMIZER_TRACE.TRACE | Documento JSON de la optimización | Texto JSON |
MISSING_BYTES_BEYOND_MAX_MEM_SIZE | Bytes recortados cuando el rastreo es demasiado grande | 0 o cantidad |
INSUFFICIENT_PRIVILEGES | ¿Es suficiente con el permiso de lectura? | 0 o 1 |
Estructura JSON: join_preparation y join_optimization
La estructura JSON se divide en los siguientes bloques: preparación de la unión y optimización de uniones, que reviso en primer lugar porque son las más importantes Notas suministrar. En el apartado preparación de la unión Reconozco la consulta ampliada (expanded_query) y compruebo si el motor ha transformado las condiciones o las proyecciones, y de qué manera. El segundo bloque optimización de uniones registra las estimaciones de líneas, los planes analizados, el orden de unión seleccionado y la incorporación de partes selectivas de la cláusula WHERE a las tablas. Los subárboles resultan especialmente útiles rows_estimation, planes_de_ejecución_considerados y asignación de condiciones a tablas, ya que hacen referencia directa a las hipótesis de costes y a los criterios de filtrado. De este modo, puedo detectar rápidamente dónde hay errores de valoración o situaciones desfavorables Índices dar lugar a planes que no son óptimos.
Comparación con EXPLAIN y ANALYZE
Para realizar una evaluación completa, combino EXPLAIN, ANALYZE y el Rastrear siguiendo un procedimiento fijo. Primero utilizo EXPLICAR o EXPLAIN FORMAT=JSON, para ver el plan seleccionado y las rutas clave. A continuación, establezco EXPLAIN ANALYZE para obtener datos reales sobre el tiempo de ejecución y valores de recuento, como bucles y líneas filtradas. Si aún tengo dudas, activo el seguimiento del optimizador y compruebo qué variantes ha evaluado y descartado el optimizador. Este artículo me ofrece una introducción concisa sobre cómo interpretar los resultados: Entender EXPLAIN ANALYZE, al que recurro como complemento cuando es necesario.
Comprender las decisiones de planificación: costes, cardinalidades, filtros
La lógica de la toma de decisiones se basa en las cardinalidades, los modelos de costes y la ubicación de Filtro siguiendo el plan. En el rastreo veo, para cada secuencia de uniones analizada, qué conjuntos de filas espera el motor y cómo calcula a partir de ellos el coste total. Compruebo si unas estadísticas obsoletas o unas correlaciones desfavorables provocan que se subestimen los escaneos por rango y se den prioridad a los escaneos completos. Además, compruebo si el motor aplica las condiciones WHERE a la tabla más selectiva con la suficiente antelación como para reducir los costosos pasos de unión. De este modo, puedo llegar a conclusiones sólidas sobre por qué se ha elegido un plan y cómo puedo mejorarlo con Índices, las reescrituras o el mantenimiento de las estadísticas.
Ejercicio práctico: seguimiento de una consulta de filtro sencilla
En SELECT * FROM t1 WHERE a < 10 Lo compruebo en preparación de la unión, si el motor ha ampliado la proyección y, en su caso, ha consolidado condiciones, lo que me da una primera Indicadores muestra. A continuación, veo en el bloque rows_estimation, cuántas líneas utiliza el motor para el Range-Scan en a en comparación con el escaneo completo de la tabla. Si se detectan valores poco realistas, suelo interpretarlo como un indicio de que las estadísticas están desactualizadas o de que faltan histogramas. En la sección planes_de_ejecución_considerados A continuación, compruebo si el acceso al índice se ha calculado realmente como más económico que el escaneo completo. Por último, muestra asignación de condiciones a tablas, si la condición selectiva se aplica a a se pone en marcha antes de lo previsto, lo que reduce considerablemente la duración disminuye.
Funciones JSON: extraer fragmentos de forma selectiva
Como el rastro está en formato JSON, filtro de forma específica subárboles con JSON_EXTRACT y elaboro pequeños análisis para datos recurrentes Muestra. Por ejemplo, solo reviso la lista de planes considerados para comprobar si determinados órdenes de unión fallan sistemáticamente. Del mismo modo, extraigo los campos de costes de los principales candidatos y los comparo con los datos de ANALYZE para detectar suposiciones erróneas. Mediante vistas sencillas o procedimientos almacenados, automatizo estas comprobaciones para mis sesiones de diagnóstico. De esta forma, me construyo un sencillo Monitoreo para las decisiones del optimizador sin activar el seguimiento permanente.
Casos de uso típicos y ventajas
Recurro al trace cuando EXPLAIN muestra un escaneo completo inesperado y quiero averiguar el motivo del rechazo de un Índice quiero averiguar. Asimismo, el rastreo me proporciona, en el caso de muchas tablas, la justificación del orden de unión elegido, lo que me indica el camino hacia planes alternativos. Al cambiar de versión, guardo los registros de seguimiento anteriores y posteriores a la actualización para evaluar los cambios en el comportamiento del optimizador. Para cuestiones estratégicas de ajuste, esta visión general me ayuda a mecanismos internos del optimizador, que relaciono con los resultados del rastreo. Así decido de forma estructurada si debo modificar los índices, las estadísticas o la formulación de las consultas para tornillo de ajuste pongo.
Buenas prácticas para la producción
Activo el seguimiento de forma sistemática como Sesión-Configuración y finalizo el diagnóstico correctamente en cuanto tenga datos suficientes. Para trazas grandes, aumento optimizer_trace_max_mem_size solo de forma temporal y, después, vuelvo a establecer el valor en un nivel bajo. Antes de compartir archivos JSON, oculto las constantes sensibles, los textos de los comentarios o los indicadores clave de negocio. Utilizo el rastreo de forma selectiva como herramienta de diagnóstico, mientras que para la supervisión continua prefiero los registros de consultas lentas, las vistas de rendimiento o los perfiladores externos. Esta disciplina mantiene los sistemas optimizados y evita el Sobrecarga en el día a día.
Optimizer Trace en la combinación de herramientas
Para lograr un ajuste integral, represento la cadena formada por la comprensión del plan, el análisis de causas y la medición del sistema, y relaciono los Hallazgos. EXPLAIN me muestra el plan, ANALYZE confirma los costes reales y el rastreo proporciona los motivos que hay detrás de la decisión. Al mismo tiempo, analizo los conceptos del plan de ejecución de consultas para identificar patrones en la selección de claves, las cardinalidades y las estrategias de unión. Un buen complemento para esta perspectiva es la visión general concisa sobre Planes de ejecución de consultas, al que recurro cuando tengo dudas sobre arquitectura. De ahí deduzco conclusiones sólidas Prioridades para el trabajo con índices, reescrituras y parámetros.
Profundicemos en el tema: análisis de rango y elección de claves
A menudo hay un bloqueo en el rastreo análisis de rango para cada tabla, en la que puedo identificar qué índices eran aptos para accesos de tipo „range“, „ref“ o „eq-ref“. El optimizador compara allí alternativas como «range en idx_a», «range en idx_b» o «full scan», les asigna costes y el número de filas esperadas, y señala la opción ganadora. Si veo que se ha descartado un índice adecuado debido a su elevado coste, lo siguiente que hago es examinar las selectividades y estadísticas subyacentes. Si las suposiciones no son correctas, puede que un ANALIZAR TABLA (en su caso, con estadísticas persistentes) o la creación de un Índice de cobertura anular la decisión.
También resulta útil analizar las divisiones de los índices compuestos: el rastreo documenta si la condición solo utiliza la primera columna del índice o si hay predicados adicionales que pueden aplicarse y otras columnas clave pasan a ser efectivas. A partir de ahí, decido si reformulo los predicados (por ejemplo, evitando funciones) o si amplío el índice de tal forma que cubra los filtros y ordenaciones habituales.
Las uniones en detalle: semionciones, BKA/MRR y búferes de unión
En las consultas con varias tablas, las secciones de seguimiento muestran si se ha tenido en cuenta alguna estrategia de semijunción y, en caso afirmativo, cuál (por ejemplo, FirstMatch, DuplicateWeedout, LooseScan o Materialization). Allí puedo ver por qué se ha descartado una variante, por ejemplo, debido a unos costes de materialización elevados o a una selectividad demasiado baja. También Acceso a claves por lotes (BKA) y Lectura multirrango (MRR) aparecen en el rastreo, siempre que estén activadas. Estas técnicas agrupan las búsquedas de claves y mejoran la localidad de la caché. Si BKA/MRR no aparecen en el rastreo, compruebo optimizer_switch y parámetros como join_cache_level. En cargas de trabajo con numerosas búsquedas aleatorias de claves, esto permite acelerar notablemente la fase de unión, lo cual se puede comprobar con EXPLAIN ANALYZE.
Además, el tamaño y el tipo del búfer de unión son fundamentales: el traza permite ver si se han ejecutado variantes de bucle anidado con o sin búfer y en qué punto se aplican los filtros. Evalúo si la opción más eficiente es añadir índices adicionales en las claves de unión o reescribir la consulta para reducir los resultados intermedios, en lugar de aumentar el tamaño de los búferes.
Subconsultas, tablas derivadas y vistas
En preparación de la unión Creo que, si las subconsultas en forma de EXISTS/IN en Semijunciones se transformaron (in_to_exists), si las tablas derivadas se han fusionado (derived_merge) o se han materializado y si Condición Pushdown hasta las tablas derivadas. Estos pasos son fundamentales, ya que omitir una fusión puede provocar una materialización costosa. Si en el seguimiento veo repetidamente decisiones de materialización con un coste elevado, compruebo si existe una STRAIGHT_JOIN, una sugerencia o una reorganización de la consulta (por ejemplo, expresiones de tabla común con filtros específicos) que lleve al motor a adoptar una estrategia más adecuada. En el caso de las vistas, compruebo si el optimizador desglosa suficientemente el contenido de la vista o si faltan índices adicionales en la tabla subyacente.
Partición y poda
En el caso de las tablas particionadas, el rastreo muestra qué particiones se han excluido en función de las claves de partición y los predicados (Poda de particiones). Si no se produce la poda esperada, es una señal de que hay que formular los filtros antes y de forma más selectiva en función de la clave de partición. Además, presto atención a la interacción entre la partición y los índices: si faltan índices locales o globales, el motor puede examinar un número excesivo de filas a pesar de la poda, lo que se refleja en el rastreo mediante unos elevados costes de exploración.
Verificar de forma específica las sugerencias, las especificaciones de índice y el parámetro `optimizer_switch`
Utilizo el Trace para comprobar el efecto de las sugerencias y los modificadores de parámetros para ocupa. Si pongo, por ejemplo,. ÍNDICE DE FUERZA o una sugerencia del optimizador, puedo ver en el rastreo si la alternativa se ha forzado realmente y cómo se ha evaluado. A través de optimizer_switch Puedo activar o desactivar estrategias de forma temporal (por ejemplo, para decisiones de semijoin, index_merge o derived_merge). El registro me sirve entonces como prueba para comprobar si el motor ha aceptado las especificaciones o si siguen prevaleciendo otras restricciones (por ejemplo, las cardinalidades). Opcionalmente, utilizo indicadores de formato como one_line o end_markers en optimizer_trace-String, para adaptar la legibilidad a mi herramienta de análisis.
Update/DELETE y rutas de escritura
El rastreo del optimizador no se limita a las sentencias SELECT. En las sentencias UPDATE y DELETE también puedo ver cómo se eligen las rutas de acceso y si los filtros se aplican con la suficiente antelación como para mantener reducido el número de filas afectadas. Compruebo si un filtro WHERE no es sargable o si la falta de un índice provoca una fase de exploración amplia antes de que se ejecute la modificación propiamente dicha. A partir del rastreo, deduzco si un índice compacto (por ejemplo, solo las columnas necesarias) evita accesos innecesarios de ida y vuelta y, con ello, reduce los bloqueos y el volumen de registro.
Seguridad, privilegios y sentencias preparadas
Para poder leer el registro completo, necesito los privilegios de objeto suficientes; si no los tengo, el campo indica INSUFFICIENT_PRIVILEGES Restricciones. Por eso, en entornos cercanos a la producción utilizo los mismos datos de acceso que la aplicación o una cuenta de diagnóstico con permisos especiales. En el caso de las sentencias preparadas, el rastreo suele mostrar la forma optimizada con los parámetros ya vinculados, lo que me permite evaluar la selectividad sin revelar constantes sensibles. Si tengo que compartir los rastreos, enmascaro los valores de los parámetros o los sustituyo por rangos representativos para cumplir con los requisitos de protección de datos.
Automatización: registrar, diferenciar y documentar trazas
Para que los análisis sean reproducibles, guardo trazas de forma aleatoria en una tabla de diagnóstico y les añado metadatos como el esquema, la versión, las variables de sesión y la marca de tiempo. De este modo, puedo comparar los resultados antes y después de los cambios en los índices o las actualizaciones de versión diffen, qué decisiones se han pospuesto. Resulta práctico agrupar los bloques planes_de_ejecución_considerados y rows_estimation guardarlas por separado para poder comparar rápidamente las variaciones en los costes. Unas pequeñas consultas auxiliares me permiten extraer el orden de unión seleccionado y los costes calculados, por ejemplo, con JSON_EXTRACT(TRACE, '$.join_optimization.considered_execution_plans') – y guardamos el resultado junto con los resultados de EXPLAIN y ANALYZE. De este modo se crea una documentación fiable para cada paso del proceso de optimización.
Limitaciones, particularidades de cada versión y comparación con MySQL
Las estructuras clave de la traza se basan en MySQL, aunque los detalles y los nombres de los campos pueden variar ligeramente en función de la versión de MariaDB. Por eso me centro en la semánticos Secciones (Rewrites, Rows-Estimation, planes analizados, Condition-Attachments), en lugar de dejarme confundir por diferencias superficiales. Importante: en MariaDB, la atención se centra en la última instrucción de la conexión activa. Por lo tanto, quien analice muchas sentencias consecutivas debe leerlas inmediatamente después de su ejecución o de forma automatizada mediante un hook, para que no se sobrescriban rastros relevantes. En el caso de archivos JSON muy grandes, tengo en cuenta los requisitos de memoria y comprendo MISSING_BYTES_BEYOND_MAX_MEM_SIZE como una invitación a aumentar temporalmente el límite y volver a ejecutar el análisis.
Extracciones concretas de JSON para el día a día
Para terminar, unos cuantos fragmentos breves que suelo utilizar a menudo en la práctica para ir directamente al grano:
- Orden de unión seleccionado y listas de candidatos: extraigo los prefijos del plan y la tabla asociada a cada uno de ellos para poder seguir el proceso de toma de decisiones.
- Alternativas de rangos y costes: extraigo la lista de índices evaluados para las tablas más selectivas, con el fin de evaluar con precisión las reescrituras o los nuevos índices.
- Filtros aplicados al principio: Estoy leyendo el
asignación de condiciones a tablas-Secciones, para garantizar que los predicados potentes se sitúen lo más cerca posible de la fuente de datos.
Con unas pocas vistas de estas extracciones, dispongo de una „lente de lectura“ sencilla para las decisiones del optimizador, que activo cuando es necesario en las sesiones de diagnóstico y vuelvo a desactivar después.
Tropiezos frecuentes y resolución de problemas
Si faltan histogramas o las estadísticas están desactualizadas, las estimaciones son erróneas y dan lugar a Planes con escaneos completos innecesarios. Si observo cardinalidades muy dispares en el rastreo, actualizo las estadísticas, establezco índices adecuados o redacto filtros que se puedan aplicar de forma selectiva. Detecto los rastreos demasiado escuetos a través de MISSING_BYTES_BEYOND_MAX_MEM_SIZE y respondo aumentando temporalmente el límite. Si ANALYZE ofrece mejores tiempos de ejecución para una ruta alternativa, compruebo en el rastreo qué factor de coste ha favorecido la variante elegida. De este modo, voy subsanando poco a poco las lagunas de conocimiento y consigo Claridad sobre la lógica de la toma de decisiones.
Brevemente resumido
El MariaDB Optimizer Trace me explica, en un documento JSON, cómo el motor reformula las consultas, estima el número de filas, compara planes y, finalmente, genera una Secuencia selecciona. Lo activo en cada sesión, leo la pista y compruebo preparación de la unión y optimización de uniones y relaciono estos hallazgos con EXPLAIN/ANALYZE. A partir de los motivos por los que se rechazan los índices, los filtros tardíos o las estimaciones erróneas, deduzco medidas concretas: mejores índices, estadísticas más actualizadas y formulaciones claras de las consultas. Con funciones JSON, extraigo fragmentos, identifico patrones y documento las decisiones de forma reproducible. De este modo, consigo que incluso las cargas de trabajo de SQL más extensas se ejecuten de forma fiable Actuación y haz que las decisiones sobre el tuning sean comprensibles.
