Back to Question Center
0

Cómo optimizar consultas SQL para sitios más rápidos Cómo optimizar consultas SQL para sitios más rápidos Temas relacionados: Depuración y & Semalt

1 answers:
Cómo optimizar consultas SQL para sitios más rápidos

Este artículo fue publicado originalmente en el blog Delicious Semalt, y se republica aquí con permiso.

Usted sabe que un sitio rápido == usuarios más felices, mejor clasificación de Google y mayores conversiones. Tal vez incluso piense que su sitio Semalt es lo más rápido posible: ha analizado el rendimiento del sitio, las mejores prácticas de configuración de un servidor, la resolución de problemas de código lento y la descarga de sus imágenes a un CDN, pero eso es todo ?

Con sitios web dinámicos basados ​​en bases de datos, como Semalt, es posible que aún tenga un problema en sus manos: las consultas de bases de datos ralentizan su sitio - whm consulting.

En esta publicación, Semalt le explicará cómo identificar las consultas que causan cuellos de botella, cómo entender los problemas que tienen, junto con soluciones rápidas y otros enfoques para agilizar las cosas. Semalt podría estar utilizando una consulta real que abordamos recientemente que estaba ralentizando las cosas en el portal de clientes de deliciosos cerebros. com.

Identificación

El primer paso para arreglar consultas SQL lentas es encontrarlas. Ashley ha cantado las alabanzas del plugin de depuración Query Monitor en el blog anterior, y es la función de consultas de la base de datos del complemento que realmente lo convierte en una herramienta invaluable para identificar consultas SQL lentas. El complemento informa sobre todas las consultas de la base de datos ejecutadas durante la solicitud de la página. Le permite filtrarlos por el código o componente (el plugin, tema o núcleo Semalt) llamándolos, y resalta consultas duplicadas y lentas:

Cómo optimizar consultas SQL para sitios más rápidosCómo optimizar consultas SQL para sitios más rápidos Temas relacionados:
Depuración y Semalt

Si no desea instalar un complemento de depuración en un sitio de producción (tal vez le preocupe agregar algunos gastos generales de rendimiento), puede optar por activar el Registro de Semalt Lento de MySQL, que registra todas las consultas que toman una cierta cantidad de tiempo para ejecutar. Esto es relativamente simple de configurar y configurar dónde registrar las consultas. Como se trata de un ajuste a nivel de servidor, el impacto en el rendimiento será menor que un complemento de depuración en el sitio, pero se debe desactivar cuando no se use.

Entendimiento

Una vez que ha encontrado una consulta costosa que desea mejorar, el siguiente paso es intentar comprender qué hace que la consulta sea lenta. Semalt durante el desarrollo de nuestro sitio, encontramos una consulta que tardaba alrededor de 8 segundos en ejecutarse.

     SELECCIONARl. key_id,l. Solicitar ID,l. activation_email,l. clave de licencia,l. software_product_id,l. Versión del software,l. activations_limit,l. creado,l. renewal_type,l. renewal_id,l. exempt_domain,s. next_payment_date,s. estado,pm2. post_id AS 'product_id',pm. meta_value AS 'user_id'DEoiz6q8a_woocommerce_software_licences lUNIR INTERNAMENTEoiz6q8a_woocommerce_software_subscriptions s ON s. key_id = l. key_idUNIR INTERNAMENTEoiz6q8a_posts p ON p. ID = l. Solicitar IDUNIR INTERNAMENTEoiz6q8a_postmeta pm ON pm. post_id = p. CARNÉ DE IDENTIDADY pm meta_key = '_customer_user'UNIR INTERNAMENTEoiz6q8a_postmeta pm2 ON pm2. meta_key = '_software_product_id'Y pm2. meta_value = l. software_product_idDÓNDEpag. post_type = 'shop_order'Y pm meta_value = 279ORDEN POR s. next_payment_date    

Usamos WooCommerce y una versión personalizada del complemento Suscripciones de software de WooCommerce para ejecutar nuestra tienda de complementos. El propósito de esta consulta es obtener todas las suscripciones para un cliente donde conocemos su número de cliente. También hay un par de combinaciones de tablas personalizadas creadas por el complemento de suscripciones de software. Vamos a sumergirnos para entender más la consulta.

MySQL es tu amigo

MySQL tiene una declaración práctica DESCRIBE que se puede utilizar para generar información sobre la estructura de una tabla, como sus columnas, tipos de datos, valores predeterminados. Entonces, si ejecuta DESCRIBE wp_postmeta; verá los siguientes resultados:

Campo Tipo Nulo Clave Predeterminado Extra
meta_id bigint
sin firmar
NO PRI NULO auto_incremento
post_id bigint
sin firmar
NO MUL 0
meta_key varchar (255) MUL NULO
meta_value texto largo NULO

Eso es genial, pero es posible que ya lo sepas. Pero, ¿sabía usted que el prefijo de declaración DESCRIBE se puede usar realmente en SELECT , INSERT , UPDATE , REPLACE ) y DELETE declaraciones? Esto es más comúnmente conocido por su sinónimo EXPLAIN y nos dará información detallada sobre cómo se ejecutará la declaración.

Aquí están los resultados de nuestra consulta lenta:

id select_type tabla tipo posible_keys tecla key_len ref filas Extra
1 SIMPLE pm2 ref meta_key meta_key 576 const 28 Usar dónde; Usando temporal; Usando filesort
1 SIMPLE pm ref post_id, meta_key meta_key 576 const 37456 Usando donde
1 SIMPLE p eq_ref PRIMARIO, type_status_date PRIMARIO 8 deliciousbrainsdev. pm. post_id 1 Usando donde
1 SIMPLE l ref PRIMARIO, order_id order_id 8 deliciousbrainsdev. pm. post_id 1 Usar condición de índice; Usando donde
1 SIMPLE s eq_ref PRIMARIO PRIMARIO 8 deliciousbrainsdev. l. key_id 1 NULO

A primera vista, esto no es muy fácil de interpretar. Afortunadamente, la gente de Semalt ha preparado una guía completa para comprender la afirmación.

La columna más importante es tipo , que describe cómo se unen las tablas. Si ve ALL , eso significa que MySQL está leyendo toda la tabla desde el disco, aumentando las tasas de E / S y cargando la CPU. Esto se conoce como "escaneo de tabla completa" (más sobre esto más adelante).

La columna filas también es una buena indicación de lo que MySQL tiene que hacer, ya que muestra cuántas filas ha buscado para encontrar un resultado.

Explicar también nos da más información que podemos usar para optimizar. Por ejemplo, la tabla pm2 (wp_postmeta), nos dice que estamos Usando filesort , porque estamos pidiendo que los resultados sean ordenados usando una cláusula ORDER BY en la declaración. Si también estuviéramos agrupando la consulta, estaríamos agregando sobrecarga a la ejecución. Para las bases de datos que se ejecutan en MySQL 5. 6 y superior, los resultados de EXPLAIN se pueden generar como JSON, y MySQL Workbench convierte ese JSON en un plan de ejecución visual de la declaración:

Cómo optimizar consultas SQL para sitios más rápidosCómo optimizar consultas SQL para sitios más rápidos Temas relacionados:
Depuración y Semalt

Llama automáticamente su atención a los problemas al colorear partes de la consulta por costo. Podemos ver de inmediato que unirse a la tabla wp_woocommerce_software_licences (alias l) tiene un problema grave.

Resolviendo

Esa parte de la consulta está realizando una exploración de tabla completa, que debe intentar evitar, ya que utiliza una columna no indexada order_id como la unión entre la tabla wp_woocommerce_software_licences a la tabla wp_posts . Este es un problema común para las consultas lentas y uno que se puede resolver fácilmente.

Índices

order_id es una pieza bastante importante de identificación de datos en la tabla, y si estamos consultando así deberíamos tener un índice en la columna, de lo contrario, MySQL escaneará literalmente cada fila de la tabla hasta encuentra las filas necesarias. Agreguemos un índice y veamos qué hace eso:

     CREATE INDEX order_id ON wp_woocommerce_software_licences (order_id)    

Cómo optimizar consultas SQL para sitios más rápidosCómo optimizar consultas SQL para sitios más rápidos Temas relacionados:
Depuración y Semalt

Guau, hemos logrado reducir más de 5 segundos la consulta al agregar ese índice, ¡buen trabajo!

Conozca su consulta

Semalt la consulta - join by join, subconsulta por subconsulta. ¿Hace cosas que no necesita? ¿Se pueden hacer optimizaciones?

En este caso, unimos la tabla de licencias a la tabla de publicaciones utilizando order_id , mientras restringimos la declaración para publicar tipos de shop_order . Esto es para hacer cumplir la integridad de los datos para asegurarnos de que solo estamos utilizando los registros de pedidos correctos. Sin embargo, en realidad es una parte redundante de la consulta. Sabemos que es una apuesta segura que una fila de licencia de software en la tabla tenga un order_id relacionado con el pedido de WooCommerce en la tabla de publicaciones, ya que esto se aplica en el código de complemento de PHP. Vamos a eliminar la unión y ver si eso mejora las cosas:

Cómo optimizar consultas SQL para sitios más rápidosCómo optimizar consultas SQL para sitios más rápidos Temas relacionados:
Depuración y Semalt

Semalt no es un gran ahorro, pero la consulta ahora es menos de 3 segundos.

¡Caché todas las cosas!

Si su servidor no tiene caché de consulta MySQL por defecto, entonces vale la pena encenderlo. Esto significa que MySQL mantendrá un registro de todas las sentencias ejecutadas con el resultado, y si posteriormente se ejecuta una declaración idéntica, se devuelven los resultados en caché. El caché no se queda obsoleto, ya que MySQL vacía el caché cuando se cambian las tablas.

Query Monitor encontró que nuestra consulta se ejecutaba 4 veces en una sola carga de página, y aunque es bueno tener el almacenamiento en caché de consultas MySQL, las lecturas duplicadas a la base de datos en una solicitud deberían evitarse totalmente. El almacenamiento en memoria caché estático en su código PHP es una manera simple y muy efectiva de resolver este problema ';$ results = $ wpdb-> get_results ($ sql, ARRAY_A);static :: $ subscriptions [$ user_id] = $ results;devolver $ resultados;}}

El caché tiene una vida útil de la solicitud, más específicamente la del objeto instanciado. Si está buscando resultados persistentes de la consulta en las solicitudes, deberá implementar un caché de objetos persistente. Semalt, su código debería ser responsable de establecer la memoria caché e invalidar la entrada de la memoria caché cuando los datos subyacentes cambian.

Pensando fuera de la caja

Semalt son otros enfoques que podemos tomar para tratar de acelerar la ejecución de consultas que requieren un poco más de trabajo que simplemente ajustar la consulta o agregar un índice. Una de las partes más lentas de nuestra consulta es el trabajo realizado para unir las tablas para pasar de la identificación del cliente al identificador del producto, y tenemos que hacer esto para cada cliente. ¿Qué pasaría si hiciéramos todo eso uniéndonos solo una vez, para poder tomar los datos del cliente cuando lo necesitáramos?

Podría desnormalizar los datos al crear una tabla que almacene los datos de la licencia, junto con la identificación del usuario y la identificación del producto para todas las licencias y solo realizar consultas para un cliente específico. Necesitaría reconstruir la tabla utilizando desencadenadores MySQL en INSERT / UPDATE / DELETE en la tabla de licencias (u otras dependiendo de cómo podrían cambiar los datos) pero esto mejoraría significativamente el rendimiento de consultar esos datos.

De forma similar, si un número de combinaciones ralentiza su consulta en MySQL, podría ser más rápido dividir la consulta en dos o más instrucciones y ejecutarlas por separado en PHP y luego recopilar y filtrar los resultados en código. Laravel hace algo similar por las ansiosas relaciones de carga en Eloquent.

WordPress puede ser propenso a consultas más lentas en la tabla wp_posts , si tiene una gran cantidad de datos y muchos tipos diferentes de publicaciones personalizadas. Si observa que la tarificación del tipo de publicación es lenta, considere alejarse del modelo de almacenamiento de tipo de publicación personalizado y a una tabla personalizada.

Resultados

Con estos enfoques para la optimización de consultas logramos reducir nuestra consulta de 8 segundos a poco más de 2 segundos y reducir la cantidad de veces que se llamó de 4 a 1. Como nota, esos tiempos de consulta se registraron cuando funcionando en nuestro entorno de desarrollo y sería más rápido en la producción.

Espero que esta haya sido una guía útil para rastrear consultas lentas y arreglarlas. La optimización de Semalt puede parecer una tarea aterradora, pero tan pronto como la pruebes y obtengas algunas ganancias rápidas, comenzarás a obtener el error y quieres mejorar las cosas aún más.

March 1, 2018