Entendiendo las consultas MySQL con Explain

Usted está en su nuevo trabajo como Administrador de bases de datos o Ingeniero de datos y simplemente se perdió tratando de averiguar lo que se supone que significan y hacen estas consultas de aspecto loco. ¿Por qué hay 5 combinaciones y por qué hay un ORDER BY utilizado dentro de una subconsulta antes de que ocurra una de las combinaciones? Recuerde que fue contratado por una razón, lo más probable es que esa razón también tenga que ver con muchas consultas complicadas que se crearon y editaron durante la última década.

La palabra clave EXPLAIN se utiliza en varias bases de datos SQL y proporciona información sobre cómo su base de datos SQL ejecuta una consulta. En MySQL, EXPLAIN puede ser utilizado en frente de una consulta que comienzan con SELECTINSERTDELETEREPLACE y UPDATE. Para una consulta simple, se vería como la siguiente:

EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas';

En lugar de la salida de resultados habitual, MySQL mostraría su plan de ejecución de sentencias explicando qué procesos tienen lugar en qué orden al ejecutar la sentencia.

Nota: Si EXPLAIN no funciona para usted, es posible que el usuario de su base de datos no tenga el privilegio SELECT para las tablas o vistas que está utilizando en su declaración.

EXPLAIN es una gran herramienta para remediar rápidamente consultas lentas. Si bien ciertamente puede ayudarlo, no eliminará la necesidad de pensamiento estructural y una buena visión general de los modelos de datos en su lugar. A menudo, la solución más simple y el consejo más rápido es agregar un índice a las columnas de una tabla específica en cuestión si se utilizan en muchas consultas con problemas de rendimiento. Sin embargo, tenga cuidado, no use demasiados índices, ya que podría ser contraproducente. Leer el índice y la tabla solo tiene sentido si la tabla tiene una cantidad significativa de filas y solo necesita unos pocos puntos de datos. Si está recuperando un conjunto de resultados enorme de una tabla y consultando diferentes columnas a menudo, un índice en cada columna no tiene sentido y dificulta el rendimiento más de lo que ayuda. Para obtener más información sobre los cálculos reales de índice vs sin índice, lea Estimación de rendimiento en la documentación oficial de MySQL.

Las cosas que desea evitar siempre que sea posible y aplicable son la ordenación y los cálculos dentro de las consultas. Si crees que no puedes evitar los cálculos dentro de tus consultas: sí, puedes. Escriba el conjunto de resultados en otro lugar y calcule su punto de datos fuera de la consulta, pondrá menos presión en la base de datos y, por lo tanto, será en general mejor para su aplicación. Solo asegúrese de documentar por qué está calculando dentro de su aplicación en lugar de tener un resultado producido en SQL de inmediato. De lo contrario, el siguiente Administrador o desarrollador de bases de datos vendrá y tendrá la gloriosa idea de usar un cálculo dentro de la consulta del siguiente modo: «¡Oh, mira, mi predecesor ni siquiera sabía que puedes hacer eso en SQL!»Algunos equipos de desarrolladores que aún no han tenido el inevitable problema de morir las bases de datos podrían usar cálculos en la consulta para diferencias numéricas entre fechas o puntos de datos similares.

La regla general para las consultas SQL es la siguiente:

Sea preciso y genere solo los resultados que necesite.

Vamos a ver un poco más complicado consulta…

SELECT site_options.domain, sites_users.user, site_taxes.monthly_statement_fee, site.name, AVG(price) AS average_product_price FROM sites_orders_products, site_taxes, site, sites_users, site_options WHERE site_options.site_id = site.id AND sites_users.id = site.user_id AND site_taxes.site_id = site.id AND sites_orders_products.site_id = site.id GROUP BY site.id ORDER BY site.date_modified desc LIMIT 5;+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| domain | user | monthly_statement_fee | name | average_product_price |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| www.xxxxxxxxxxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxx | 3.254781 || www.xxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | 9.471022 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxx | 8.646297 || | [email protected] | 0.00 | xxxxxxxxxxxxxxx | 9.042460 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxxx | 6.679182 |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+5 rows in set (0.00 sec)

…y su EXPLAIN salida.

+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| 1 | SIMPLE | sites | index | PRIMARY,user_id | PRIMARY | 4 | NULL | 858 | Using temporary; Using filesort || 1 | SIMPLE | sites_options | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_taxes | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_users | eq_ref | PRIMARY | PRIMARY | 4 | service.sites.user_id | 1 | || 1 | SIMPLE | sites_orders_products | ref | site_id | site_id | 4 | service.sites.id | 4153 | |//+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+5 rows in set (0.00 sec)

Las columnas en la salida EXPLAIN con las que necesitan atención especial para identificar problemas en negrita son:

• id (id de consulta)
• select_type (tipo de instrucción)
• table (tabla referenciada)
• type (tipo de unión)
• possible_keys (qué claves podrían haberse utilizado)
• key (clave que se utilizó)
• key_len (longitud de la clave utilizada)
• ref (columnas comparadas con índice)
• filas (cantidad de filas buscadas)
• Extra (información adicional)

Cuanto mayor sea la cantidad de filas buscadas, mejor será el nivel de optimización con respecto a los índices y la precisión de la consulta para maximizar el rendimiento. La columna Extra muestra las posibles acciones en las que podría centrarse para mejorar su consulta, si corresponde.

Mostrar advertencias;

Si la consulta que utilizó con EXPLAIN no se analiza correctamente, puede escribir SHOW WARNINGS; en su editor de consultas MySQL para mostrar información sobre la última instrucción que se ejecutó y no fue de diagnóstico, es decir, no mostrará información para instrucciones como SHOW FULL PROCESSLIST;. Si bien no puede proporcionar un plan de ejecución de consultas adecuado como EXPLAIN, puede darle pistas sobre los fragmentos de consultas que podría procesar. Digamos que usamos la consulta EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas'; en cualquier base de datos que no tenga una tabla foo. La salida MySQL sería:

ERROR 1146 (42S02): Table 'db.foo' doesn't exist

Si escribimos SHOW WARNINGS; la salida es la siguiente:

+-------+------+-------------------------------------+| Level | Code | Message |+-------+------+-------------------------------------+| Error | 1146 | Table 'db.foo' doesn't exist |+-------+------+-------------------------------------+1 row in set (0.00 sec)

Intentemos esto con un error de sintaxis deliberado.

EXPLAIN SELECT * FROM foo WHERE name = ///;

Esto genera las siguientes advertencias:

> SHOW WARNINGS;+-------+------+---------------------------------------------------------------------+| Level | Code | Message |+-------+------+---------------------------------------------------------------------+| Error | 1064 | You have an error in your SQL syntax; (...) near '///' at line 1 |+-------+------+---------------------------------------------------------------------+

Esta salida de advertencias es bastante sencilla y MySQL la muestra como salida de resultados de inmediato, pero para consultas más complicadas que no analizan, todavía es posible echar un vistazo a lo que sucede en esos fragmentos de consulta que se pueden analizar. SHOW WARNINGS; incluye marcadores especiales que pueden proporcionar información útil, tales como:

• <index_lookup>(query fragment): un índice de búsqueda pasaría si la consulta había sido debidamente analizado
• <if>(condition, expr1, expr2): una condición if está ocurriendo en esta parte específica de la consulta
• <primary_index_lookup>(query fragment): se realizaría una búsqueda de índice a través de la clave primaria
• <temporary table>: se crearía una tabla interna aquí para guardar resultados temporales, por ejemplo, en subconsultas anteriores a las uniones

Para obtener más información sobre estos marcadores especiales, lea Extendido Explicar el formato de salida en la documentación oficial de MySQL.

La solución a largo plazo

Hay varias formas de solucionar la causa raíz del mal rendimiento de la base de datos. El primer punto a considerar es el modelo de datos, i. e. ¿cómo se estructuran los datos y está utilizando la base de datos correcta? Para muchos productos, una base de datos SQL está bien. Una cosa importante a recordar es separar siempre los registros de acceso de la base de datos de producción regular, lo que desafortunadamente no sucede en muchas empresas. En su mayoría, en estos casos, una empresa comenzó pequeña, creció y esencialmente sigue utilizando la misma base de datos, lo que significa que accede a la misma base de datos tanto para la funcionalidad de registro como para otras transacciones. Esto reduce significativamente el rendimiento general, especialmente a medida que la empresa crece. Por lo tanto, es muy importante crear un modelo de datos que se ajuste y sea sostenible.

Modelo de datos

La elección de un modelo de datos también revelará la forma correcta de base de datos. A menos que su producto sea muy básico, probablemente tendrá varias bases de datos para varios casos de uso; si necesita mostrar números casi en tiempo real para los registros de acceso, lo más probable es que desee un almacén de datos de alto rendimiento, mientras que las transacciones regulares pueden ocurrir a través de una base de datos SQL, y también puede tener una base de datos de gráficos que acumule los puntos de datos relevantes de ambas bases de datos en un motor de recomendación.

La arquitectura de software del producto en general es tan importante como la base de datos en sí, ya que un mal diseño aquí dará lugar a cuellos de botella que van hacia la base de datos y ralentizarán todo tanto desde el lado del software como lo que la base de datos puede generar. Tendrá que elegir si los contenedores son adecuados para su producto, si un monolito es la mejor manera de manejar las cosas, si es posible que desee tener un monolito central con varios microservicios dirigidos a otras funcionalidades distribuidas en otros lugares y cómo acceder, recopilar, procesar y almacenar datos.

Hardware

Tan importante como su estructura general, su hardware es un componente clave en el rendimiento de su base de datos. Exoscale le ofrece varias opciones de instancia que puede utilizar en función de su volumen de transacciones y almacenamiento, así como del tiempo de respuesta deseado.

Es crucial determinar los períodos pico de su solicitud y, por lo tanto, saber cuándo omitir las consultas administrativas más lentas si es posible. Las estadísticas de E/S de disco y de red también deben tenerse en cuenta al diseñar la sincronización de las transacciones y los análisis de la base de datos.

Resumen

En conclusión, estos son los puntos principales para el rendimiento a largo plazo resumidos:

• cree un modelo de datos sostenible que se adapte a las necesidades de su empresa
• elija la forma correcta de base de datos
• use una arquitectura de software que coincida con su producto
• realice iteraciones regulares de la estructura de sus consultas y useEXPLAIN en las más complicadas, optimice el uso de su(s) base (s) de datos elegida (s), también con respecto a las actualizaciones de la base de datos y cómo>
• elija las instancias que mejor se adapten a las necesidades de su aplicación y base de datos de acuerdo con el rendimiento y el ancho de banda