Optimización de Laravel Parte 2: Mejore el rendimiento de la consulta con la indexación de bases de datos

Este es el artículo 2 de 4 de la serie “Optimización de Laravel”
Optimización de Laravel Parte 1: elementos básicos
Optimización de Laravel Parte 2: Mejore el rendimiento de la consulta con la indexación de bases de datos
Optimización de Laravel Parte 3: Mejorar el rendimiento del almacenamiento en caché de objetos
Optimización de Laravel Parte 4: Consignaciones en colas, front-end y Opcache
En mi último artículo, analicé algunos pedidos simples y algunos cambios básicos en el código que podríamos usar para optimizar el rendimiento de una aplicación Laravel.En este artículo, analizaremos cómo usar los índices de la base de datos para mejorar el rendimiento de la consulta, para que su aplicación Laravel no se vuelva lenta cuando comienza a interrogar muchos datos.Podría mejorar mi rendimiento en un 100% en el ejemplo de esta publicación, pero su kilometraje variará casi seguramente.
¿Qué es un índice de base de datos? Si nunca ha pensado en indexar las bases de datos, es posible que se sorprenda al descubrir que probablemente use los índices de base de datos durante algún tiempo. Si alguna vez ha creado una tabla en MySQL con una columna de clave primaria (normalmente llamada ID), MySQL realmente ha creado un índice para esa columna (llamada Primay por defecto). Si he leído la publicación anterior de Iain sobre la optimización de los interrogatorios SQL para sitios de WordPress más rápidos, entonces tendrá una idea decente de cuál es la base de datos. Un índice de base de datos mejora la velocidad de recuperación de datos en una tabla al mantener un “índice” de datos. Piense en ello como un índice de un libro, pero para la tabla de la base de datos. El índice se utiliza para encontrar rápidamente la línea correcta o las líneas de datos sin tener que escanear toda la tabla cada vez. Para pequeñas bases de datos, las ganancias de rendimiento son mínimas, porque la consulta de bases de datos pequeñas es muy rápida de todos modos. Sin embargo, una vez que una tabla comienza a crecer en tamaño (piense en millones de filas), comenzará a notar un impacto considerable en el rendimiento cuando interroga la tabla sin índices.
Hay algunos compromisos a considerar al usar índices. Un índice utiliza espacio de almacenamiento, pero el espacio que utiliza normalmente es mínimo en comparación con el tamaño de la base de datos. Sin embargo, para las tablas pequeñas que tienen un índice, puede hacer que MySQL trabaje más duro, ya que tienen que procesar el índice al ejecutar la consulta y las consultas se volverán más lentas, ya que deben reconstruir el índice. Por lo tanto, se recomienda agregar índices solo una vez que una tabla comience a crecer en tamaño. Diagnóstico de un problema de índice En mi último artículo, creé una aplicación Laravel con un modelo de tarea y una relación simple que un usuario puede tener muchas tareas. Continuando con este ejemplo, podemos imaginar que un usuario tiene ~ 2.4 millones de tareas y queremos hacerse cargo de las tareas de un usuario. Podríamos hacer algo como esto: $ user = user :: find (1); $ tareas = $ user-> tareas ()-> get (); Lo primero que probablemente notará es que la página no se cargará porque PHP permanecerá sin memoria (por ejemplo, “el tamaño de memoria permitido por x -bytes”). Por lo tanto, para comenzar a diagnosticar este problema, tendremos que pasar a una aplicación de administración de bases de datos (uso TablePlus en este momento). Ejecutemos la consulta SQL bruta que sería generada por Laravel: Seleccione * de `Tasks` si ‘tareas`.`user_id` = 1 y` tareas`.`ser_id` no está ejecutando esta consulta toma aproximadamente 1 segundo en mi computadora. No es bueno.
Para ayudarnos a solucionar el problema con esta consulta, podemos usar la instrucción de explicación mysql para ayudarnos a comprender lo que está sucediendo detrás de escena. Simplemente ejecute la consulta nuevamente, pero agregue Explicar al comienzo de la interrogación: Explique SELECT * de `Tasks` Wore` Taskks`.`user_id` = 1 y` tareas`_id` no es nula La ejecución de esta consulta nos dará Mucha información, pero los campos importantes para buscar son posibleskeys y campos clave. Estos nos dirán qué índices (claves) están disponibles para esta consulta y qué índice se usa realmente. También es importante tener en cuenta que el campo de las filas nos dice cuántas filas han sido escaneadas durante esta consulta. Entonces, cuando ejecuto esto para nuestra consulta de tareas, obtengo los siguientes resultados: ¡Observe que, en este caso, no se usaron índices y tuvimos que escanear todos los ~ 2.4 millones de líneas! No es de extrañar que la consulta dure tanto. Agregando un índice de base de datos ahora que hemos diagnosticado el problema, agregemos un índice a la columna user_id para la tabla de tareas. Normalmente, lo haría en Laravel creando una nueva migración y agregando un índice a la tabla usando $ table-> index (): :: table (‘tareas’, function (blueprint $ table) {$ table-> index ( ‘user_id’);}); Después de ejecutar la migración para agregar el índice, reduzcamos nuestra consulta nuevamente y veamos cuánto tiempo lleva. En mi computadora, la consulta ahora dura ~ 500 ms. ¡Es la mitad del tiempo requerido antes! Si ejecutamos la instrucción de explicación nuevamente, veamos qué sucede detrás de escena:
Podemos ver que la consulta ahora usa nuestras nuevas tareas_user_id_index que acabamos de crear y, usando este nuevo índice, el número de líneas para escanear ha disminuido de ~ 2.4 millones a ~ 400,000. ¡Éxito! En este momento, vale la pena señalar que su kilometraje puede variar al agregar índices a las tablas de la base de datos, porque la ganancia de rendimiento dependerá no solo de la cantidad de datos en la tabla, sino también del tipo de consulta ejecutada. Como nota secundaria, solo podríamos tener user_id como clave externa para la tabla. Las claves externas indexan automáticamente la columna e imponen la integridad de los datos. Después de esperar, agregar índices de base de datos es un poco de arte. Por ejemplo, si tiene más de unas cláusulas en su consulta, puede tener sentido agregar un índice con múltiples columnas (compuesto) para incluir cada campo en el lugar. Digamos que nuestra pregunta es: Seleccione * de `Tasks` Where` Tasks`.`user_id` = 1 y` taski.cretated_at`> = ‘2019-03-19 11:00’ Podríamos agregar un índice compuesto así: esquema: esquema: :: Tablas (‘tareas’, function (bluePrint $ table) {$ table-> index ([‘user_id’, ‘create_at’);}); Ejecutar el interrogatorio anterior con el índice de compuesto ha mejorado el rendimiento del interrogatorio en mi automóvil en aproximadamente un 50%nuevamente.


Sin embargo, tenga en cuenta:

La adición de varios índices aumentará el tamaño de la base de datos y el uso de RAM de la base de datos (porque MySQL intenta almacenar índices).
MySQL es lo suficientemente inteligente como para usar índices, incluso si los campos de índice pueden no incluir cada campo en el lugar. Si agregué ambos índices anteriores, una explicación mostraría que el índice user_id se usará en lugar del índice compuesto, a pesar de que he tenido varios en mi consulta. MySQL puede usar más índices si es necesario.
El orden de los campos en un índice compuesto también puede tener un impacto. En general, deben ordenarse más comúnmente a los menos comunes.
Por lo tanto, aún puede ver una ganancia de rendimiento sin agregar más índices compuestos. Usar Explica está tu amigo aquí para averiguar qué hace MySQL detrás de escena.
La próxima vez que tenga una comprensión básica de por qué indexar las bases de datos es importante para el rendimiento y cómo usarlas en sus aplicaciones de Laravel, debería poder comenzar a implementarlas en las bases de datos si aún no lo ha hecho. Agregar los índices correctos es un poco de arte, pero, como en la mayoría de las cosas, la práctica hace la perfección. En mi próximo artículo, veremos cómo podemos incluso mejorar el rendimiento de Laravel utilizando el caché de objetos. ¿Alguna vez ha utilizado bases de datos de indexación en Laravel? ¿Tiene consejos para indexar la base de datos compartiendo? Dinos en los comentarios.
Este es el artículo 2 de 4 de la serie “Optimización de Laravel”
Optimización de Laravel Parte 1: elementos básicos
Optimización de Laravel Parte 2: Mejore el rendimiento de la consulta con la indexación de bases de datos
Optimización de Laravel Parte 3: Mejorar el rendimiento del almacenamiento en caché de objetos