Heading

En este artículo, nos centraremos en los consejos más fáciles y mejores que los desarrolladores pueden implementar para sus esfuerzos de ajuste de rendimiento de SQL en el día a día. Este artículo se centrará en el ajuste del rendimiento de las consultas de MySQL, pero los mismos conceptos se pueden aplicar a muchas otras bases de datos relacionales.

Ahora más que nunca, los ingenieros de software necesitan tener un vasto conocimiento en el ajuste del rendimiento de SQL.
El cambio se está produciendo tanto en las pequeñas startups como en las grandes empresas. Hoy en día, los desarrolladores son los que escriben las consultas SQL y la capa de acceso a la base de datos.

Realmente no importa si estás usando una capa de abstracción de la base de datos (Hibernate, JOOQ, Entity Framework, Sqlalchemy, Django, u otros) o escribiendo consultas SQL nativas, eventualmente te enfrentarás al desafío de afinar las consultas que estás enviando a tu base de datos.

Entonces, ¿qué puedes hacer para optimizar tus consultas SQL?

Crea índices, pero hazlo de forma inteligente

La indexación es probablemente la parte más importante del proceso de ajuste de consultas. Así que, en primer lugar, asegúrese de que está familiarizado con los diferentes aspectos que debe tener en cuenta a la hora de elegir los índices óptimos para su base de datos.

Cuando piense en qué índices crear, debe prestar mucha atención a la cláusula WHERE de la consulta y a los JOIN de la tabla, ya que esas declaraciones incluyen las partes críticas indexables de la consulta.

También, los principales cuellos de botella pueden originarse en las partes GROUP BY y ORDER BY. Dicho esto, un posible contratiempo será que no puedas indexarlas en algunos casos, como explicamos aquí. Por lo tanto, puede que tengas que replantearte el diseño de tu consulta antes de crear los índices, para asegurarte de que escribes grandes consultas, pero también escribes consultas indexables.

Una vez que tengas la indexación resuelta para una consulta, no te detengas ahí. Amplía tu visión y mira otras consultas importantes en tu aplicación. Asegúrate de combinar los índices siempre que sea posible, y elimina los índices que no se utilicen. Mirar el alcance de toda la aplicación siempre será mejor que mirar el alcance de una sola consulta.

También debes tener en cuenta que crear más índices de los que necesitas también puede ser contraproducente para ti, ya que pueden ralentizar las operaciones de escritura (como las sentencias INSERT / UPDATE). Así que crea índices para optimizar el rendimiento de tus consultas SQL, pero hazlo con prudencia.

No te pongas en el camino de los índices

Se nos acercan mucho los clientes que nos preguntan «¿por qué la base de datos no utiliza mi índice?». Pues bien, esa es una gran pregunta, con infinitas respuestas posibles. Pero, en este artículo, intentaremos cubrir varios escenarios comunes que vemos a menudo, así que esperamos que los encuentres útiles para tu propio caso de uso.

Ejemplo #1 – Evita envolver las columnas indexadas con funciones

Considera esta consulta, que cuenta el número de perritos calientes comprados en Estados Unidos en 2018. Por si tienes curiosidad, en 2018 se vendieron 18.000.000.000 de perritos calientes en EEUU.

SELECT COUNT(*)FROM us_hotdog_purchasesWHERE YEAR(purchase_time) = ‘2018’

Como puedes ver, estamos usando la función YEAR para coger la parte del año de la columna purchase_time. Esta llamada a la función impedirá que la base de datos pueda utilizar un índice para la búsqueda de la columna purchase_time, porque indexamos el valor de purchase_time, pero no el valor de retorno de YEAR(purchase_time).

Para superar este reto y afinar esta consulta SQL, puede indexar el resultado de la función, utilizando Columnas Generadas, que están disponibles a partir de MySQL 5.7.5.

Otra solución puede ser encontrar una forma alternativa de escribir la misma consulta, sin utilizar la llamada a la función. En este ejemplo, podemos transformar esa condición en una condición de rango de 2 vías, que devolverá los mismos resultados:

SELECT COUNT(*)FROM us_hotdog_purchasesWHERE purchased_at >= '2018-01-01' AND purchased_at < '2019-01-01'

Ejemplo #2 – evitar las condiciones OR

Considera esta consulta, que selecciona la cantidad de posts en Facebook publicados después de la víspera de año nuevo, o publicados por un usuario llamado Mark.

SELECT COUNT(*)FROM fb_postsWHERE username = ‘Mark’ OR post_time > ‘2018-01-01’

Tener un índice en las columnas username y post_time puede parecer útil, pero en la mayoría de los casos, la base de datos no lo utilizará, al menos no en su totalidad. La razón será la conexión entre las dos condiciones: el operador OR, que hace que la base de datos obtenga los resultados de cada parte de la condición por separado.

Una forma alternativa de ver esta consulta puede ser «dividir» la condición OR y «combinarla» utilizando una cláusula UNION. Esta alternativa le permitirá indexar cada una de las condiciones por separado, por lo que la base de datos utilizará los índices para buscar los resultados y luego combinará los resultados con la cláusula UNION.

SELECT …FROM …WHERE username = ‘Mark’ UNIONSELECT …FROM …WHERE post_time > ‘2018-01-01’

Tenga en cuenta que si no le importan los registros duplicados en su conjunto de resultados, también puede utilizar UNION ALL (que tendrá un mejor rendimiento que el UNION DISTINCT predeterminado).

Ejemplo #3 – Evitar la ordenación con un orden mixto

Considera esta consulta, que selecciona todas las publicaciones de Facebook y las ordena por el nombre de usuario en orden ascendente, y luego por la fecha de publicación en orden descendente.

SELECT username, post_typeFROM fb_postsORDER BY username ASC , post_type DESC

MySQL (y tantas otras bases de datos relacionales), no puede utilizar índices cuando se ordena con un orden mixto (tanto ASC como DESC en la misma cláusula ORDER BY). Esto cambió con el lanzamiento de la funcionalidad de índices invertidos y MySQL 8.x.

Entonces, ¿qué puedes hacer si no has actualizado a la última versión de MySQL todavía? En primer lugar, le recomendamos que reconsidere la ordenación por orden mixto. ¿Realmente lo necesita? Si no es así, evítelo.

Así que ha decidido que lo necesita, o su director de producto le ha dicho: «No hay manera de arreglárselas sin ella»? Otra opción será utilizar las columnas generadas (disponibles en MySQL 5.7.5+) para crear una columna invertida y ordenar sobre esa columna en lugar de la original. Como ejemplo, suponga que está ordenando en una columna numérica, puede crear una columna generada con el valor numérico negativo que se correlaciona con el número original y ordenar en esa nueva columna en el orden inverso. De esta manera, todas las columnas tendrán el mismo orden de clasificación en la cláusula ORDER BY, pero la ordenación se producirá como se definió originalmente por el requisito de su producto.

La última solución potencial no siempre será una opción, por lo que su último recurso será actualizar a la última versión de MySQL que soporta la ordenación de orden mixto utilizando índices.

Ejemplo #4 – Evitar condiciones con diferentes tipos de columnas

Considere esta consulta, que selecciona el número de frutos rojos en un bosque.

SELECT COUNT(*)FROM forestWHERE fruit_color = 5; /* 5 = red */

Suponiendo que el tipo de la columna color_fruta es VARCHAR, o simplemente cualquier cosa no numérica, indexar esa columna no será muy útil, ya que el reparto implícito requerido impedirá que la base de datos utilice el índice para el proceso de filtrado.

Entonces, ¿cómo puede afinar esta consulta SQL? Tienes dos opciones para optimizar esta consulta. La primera sería comparar la columna con un valor constante que coincida con el tipo de la columna, por lo que si es una columna VARCHAR, compárela con ‘5’ (con comillas simples) y no con 5 (que es una comparación numérica que dará lugar a un reparto implícito).

Una mejor opción será ajustar el tipo de la columna para que coincida con el tipo más adecuado para los valores que la columna contiene. En este ejemplo, la columna debe ser alterada a un tipo INT. Ten en cuenta que alterar el tipo de una columna puede ser una tarea complicada, así que lee sobre los retos de esa tarea antes de dirigirte a ella.

Evita las búsquedas LIKE con comodines de prefijo

Considera esta consulta, que busca todas las publicaciones de Facebook de un nombre de usuario que incluya la cadena ‘Mar’, por lo que estamos buscando todas las publicaciones escritas por usuarios llamados Mark, Marcus, Almar, etc.

SELECT *FROM fb_postsWHERE username LIKE '%Mar%'

Tener un comodín ‘%’ al principio del patrón evitará que la base de datos utilice un índice para la búsqueda de esta columna. Tales búsquedas pueden tardar un tiempo.

En este caso, hay dos opciones para mejorar el rendimiento de esta consulta. La primera es trivial – considerar si el prefijo comodín es lo suficientemente importante. Si puede prescindir de él, elimínelo.

Otra opción será utilizar índices de texto completo. Sin embargo, tenga en cuenta que estos índices y la sintaxis MATCH … AGAINST no están libres de desafíos y tienen algunas diferencias cuando se comparan con las conocidas expresiones LIKE en MySQL.

Conclusión

En esta primera parte de nuestra serie de optimización de consultas SQL, cubrimos la importancia de la indexación sabia, pasamos por varios ejemplos de posibles obstáculos mientras se utilizan columnas indexadas en las consultas, y también detallamos varios otros consejos y trucos que pueden ser útiles para un mejor rendimiento de las consultas. Nos vemos en el próximo post.