Aprenda sobre antipatrones, planes de ejecución, complejidad de tiempo, ajuste de consultas y optimización de SQL
El lenguaje de consulta estructurado (SQL) es una habilidad indispensable en la industria de la informática y, en general, aprender esta habilidad es relativamente simple. Sin embargo, la mayoría de las personas olvidan que SQL no se trata solo de escribir consultas, es solo el primer paso más adelante. Asegurar el rendimiento de la consulta o hacer coincidir el contexto en el que trabaja es algo completamente diferente.
Es por eso que esta guía SQL le proporcionará una pequeña descripción de algunos de los pasos que puede seguir para evaluar su consulta:
- Primero, comenzará con una breve descripción de la importancia del aprendizaje de SQL para trabajar en el campo de la ciencia de datos;
- A continuación, primero aprenderá cómo procesar y ejecutar consultas SQL para comprender la importancia de crear consultas de calidad. Más específicamente, verá que la solicitud se analiza, reescribe, optimiza y finalmente se evalúa.
- Con esto en mente, no solo irá a algunos antipatrones de consultas que los principiantes hacen al escribir consultas, sino que también aprenderá más sobre alternativas y soluciones a estos posibles errores; Además, aprenderá más sobre el enfoque de consulta basado en conjuntos.
- También verá que estos antipatrones surgen de problemas de rendimiento y que, además del enfoque "manual" para mejorar las consultas SQL, puede analizar sus consultas de una manera más estructurada y profunda, utilizando algunas otras herramientas que lo ayudan a ver el plan de consulta; Y
- Aprenderá brevemente sobre la complejidad del tiempo y la notación O grande, para tener una idea de la complejidad del plan de ejecución a tiempo antes de ejecutar la solicitud;
- Aprenderá brevemente cómo optimizar su consulta.
¿Por qué debería aprender SQL para trabajar con datos?
SQL está lejos de estar muerto: esta es una de las habilidades más buscadas que encuentra en las descripciones de trabajo de la industria de procesamiento y análisis de datos, independientemente de si solicita análisis de datos, ingeniero de datos, especialista en datos o cualquier otro rol. Esto lo confirma el 70% de los encuestados en la Encuesta salarial de ciencia de datos O 'Reilly para 2016, quienes indican que usan SQL en su contexto profesional. Además, en esta encuesta, SQL destaca sobre los lenguajes de programación R (57%) y Python (54%).
Usted comprende: SQL es una habilidad necesaria cuando está trabajando para conseguir un trabajo en la industria de TI.
No está mal para un lenguaje que se desarrolló a principios de la década de 1970, ¿verdad?
Pero, ¿por qué se usa con tanta frecuencia? ¿Y por qué no murió, a pesar de que ha existido durante tanto tiempo?
Hay varias razones: una de las primeras razones podría ser que las empresas almacenan principalmente datos en sistemas de gestión de bases de datos relacionales (RDBMS) o en sistemas de gestión de flujo de datos relacionales (RDSMS), y se necesita SQL para acceder a estos datos. SQL es
lingua franca de datos: ¡hace posible interactuar con casi cualquier base de datos o incluso construir la suya localmente!
Si esto aún no es suficiente, tenga en cuenta que hay bastantes implementaciones de SQL que son incompatibles entre los proveedores y no necesariamente se ajustan a los estándares. El conocimiento del SQL estándar, por lo tanto, es un requisito para que pueda encontrar su camino en la industria (informática).
Además, es seguro decir que las nuevas tecnologías también se han unido a SQL, como Hive, una interfaz de lenguaje de consulta similar a SQL para consultar y administrar grandes conjuntos de datos, o Spark SQL, que se puede utilizar para ejecutar consultas SQL. Nuevamente, el SQL que encuentre allí será diferente del estándar que podría aprender, pero la curva de aprendizaje será mucho más simple.
Si desea hacer una comparación, considérelo como aprendizaje de álgebra lineal: después de haber puesto todos estos esfuerzos en este tema, ¡sabe que también puede usarlo para dominar el aprendizaje automático!
En resumen, esta es la razón por la que debe aprender este lenguaje de consulta:
- Es bastante fácil de aprender, incluso para principiantes. La curva de aprendizaje es bastante simple y gradual, por lo que escribirá consultas lo antes posible.
- Sigue el principio de "aprender una vez, usar en todas partes", ¡así que esta es una gran inversión de tu tiempo!
- Esta es una gran adición a los lenguajes de programación; En algunos casos, escribir una consulta es incluso preferible a escribir código, ¡porque es más eficiente!
- ...
¿Qué estás esperando todavía? :)
Procesamiento de SQL y ejecución de consultas
Para mejorar el rendimiento de su consulta SQL, primero necesita saber qué sucede dentro cuando hace clic en un acceso directo para ejecutar la consulta.
Primero, la solicitud se analiza en un árbol de análisis; La solicitud se analiza para verificar el cumplimiento de los requisitos sintácticos y semánticos. El analizador crea una representación interna de la solicitud de entrada. Esta salida se transfiere al mecanismo de reescritura.
Luego, el optimizador debe encontrar la ejecución óptima o el plan de consulta para la consulta dada. El plan de ejecución determina con precisión qué algoritmo se utiliza para cada operación y cómo se coordinan las operaciones.
Para encontrar el plan de ejecución más óptimo, el optimizador enumera todos los planes de implementación posibles, determina la calidad o el costo de cada plan, recibe información sobre el estado actual de la base de datos y luego selecciona el mejor de ellos como el plan de implementación final. Debido a que los optimizadores de consultas pueden ser imperfectos, los usuarios y los administradores de bases de datos a veces tienen que examinar y ajustar manualmente los planes creados por el optimizador para mejorar el rendimiento.
Ahora probablemente se esté preguntando qué se considera un "buen plan de consulta".
Como ya leyó, la calidad del costo de un plan juega un papel importante. Más específicamente, cosas como la cantidad de E / S de disco que se requieren para evaluar el plan, el costo de la CPU del plan y el tiempo de respuesta total que puede observar el cliente de la base de datos y el tiempo de ejecución total son importantes. Aquí es donde surge el concepto de complejidad temporal. Aprenderá más sobre esto más adelante.
Luego, se ejecuta el plan de consulta seleccionado, evaluado por el mecanismo de ejecución del sistema, y se devuelven los resultados de la consulta.
Escribir consultas SQL
Puede que no haya quedado claro en la sección anterior que el principio de Garbage In, Garbage Out (GIGO) se manifiesta naturalmente en el proceso de procesamiento y ejecución de una consulta: el que formula la consulta también tiene claves para el rendimiento de sus consultas SQL. Si el optimizador recibe una solicitud mal formulada, puede hacer lo mismo ...
Esto significa que hay algunas cosas que puede hacer al escribir una solicitud. Como ya ha visto en la introducción, la responsabilidad aquí es doble: no solo se trata de escribir consultas que cumplan un determinado estándar, sino también de recopilar ideas sobre dónde podrían ocultarse los problemas de rendimiento en su consulta.
Un punto de partida ideal es pensar en "lugares" en sus consultas donde puedan surgir problemas. Y, en general, hay cuatro palabras clave en las que los recién llegados pueden esperar que ocurran problemas de rendimiento:
- Condición
WHERE ; - Cualquier
INNER JOIN clave INNER JOIN o LEFT JOIN ; Y tambien HAVING condición;
Por supuesto, este enfoque es simple e ingenuo, pero, para un principiante, estos puntos son excelentes indicadores, y es seguro decir que cuando comienzas, ocurren errores en estos lugares y, curiosamente, también es difícil notarlos.
Sin embargo, también debe comprender que el rendimiento es algo que debería ser significativo. Sin embargo, solo decir que estas oraciones y palabras clave son malas no es lo que necesita cuando piensa en el rendimiento de SQL. Tener una
HAVING WHERE o
HAVING en una solicitud no significa necesariamente que sea una solicitud incorrecta ...
Consulte la siguiente sección para obtener más información sobre antipatrones y enfoques alternativos para crear su consulta. Estos consejos y trucos pretenden ser una guía. Cómo y si realmente necesita reescribir su solicitud depende, entre otras cosas, de la cantidad de datos, la base de datos y la cantidad de veces que necesita completar la solicitud. ¡Depende completamente del propósito de su solicitud y es crucial tener algún conocimiento previo sobre la base de datos con la que trabajará!
1. Recupere solo los datos necesarios
La conclusión "cuantos más datos, mejor" no tiene que seguirse al escribir SQL: corre el riesgo de no solo confundirse al obtener más datos de los que realmente necesita, sino que también el rendimiento puede verse afectado porque su consulta recibe demasiados datos.
Es por eso que, por regla general, debe prestar atención a la
SELECT , la
SELECT DISTINCT y la instrucción
LIKE .
SELECT
Lo primero que puede verificar cuando escribe una consulta es si la
SELECT más compacta posible. El objetivo aquí debería ser eliminar columnas innecesarias de
SELECT . De esta manera, se obliga a recuperar solo los datos que sirven para el propósito de su consulta.
Si ha correlacionado las subconsultas con
EXISTS , debe intentar usar una constante en la
SELECT esta subconsulta en lugar de elegir el valor de la columna real. Esto es especialmente conveniente cuando solo verificas la existencia.
Recuerde que una subconsulta correlacionada es una subconsulta que usa valores de una consulta externa. Y tenga en cuenta que aunque
NULL puede funcionar como una "constante" en este contexto, ¡esto es muy confuso!
Considere el siguiente ejemplo para comprender qué se entiende al usar una constante:
SELECT driverslicensenr, name FROM Drivers WHERE EXISTS (SELECT '1' FROM Fines WHERE fines.driverslicensenr = drivers.driverslicensenr);
Consejo: es útil saber que tener una subconsulta correlacionada no siempre es una buena idea. Siempre puede considerar deshacerse de ellos, por ejemplo, reescribiéndolos usando
INNER JOIN :
SELECT driverslicensenr, name FROM drivers INNER JOIN fines ON fines.driverslicensenr = drivers.driverslicensenr;
Operación DISTINCT
La
SELECT DISTINCT usa para devolver solo valores diferentes.
DISTINCT es un punto que sin duda debe evitarse si es posible. Como en otros ejemplos, el tiempo de ejecución aumenta solo cuando esta oración se agrega a la solicitud. Por lo tanto, siempre es útil considerar si realmente necesita esta operación
DISTINCT para obtener los resultados que desea lograr.
Declaración LIKE
Cuando se usa el operador
LIKE en una consulta, el índice no se usa si el patrón comienza con
% o
_ . Esto evitará que la base de datos use el índice (si existe). Por supuesto, desde otro punto de vista, también se puede argumentar que este tipo de solicitud potencialmente permite obtener demasiados registros que no necesariamente satisfacen el propósito de la solicitud.
Nuevamente, conocer los datos almacenados en la base de datos puede ayudarlo a formular una plantilla que filtre todos los datos correctamente para encontrar solo las filas que son realmente importantes para su consulta.
2. Limita tus resultados
Si no puede evitar filtrar su
SELECT , puede limitar sus resultados de otras maneras. Aquí es donde entran en
LIMIT enfoques como la
LIMIT y las conversiones de tipos de datos.
ROWNUM TOP , LIMIT y ROWNUM
Puede agregar sentencias
LIMIT o
TOP a las consultas para especificar el número máximo de filas para el conjunto de resultados. Aquí hay algunos ejemplos:
SELECT TOP 3 * FROM Drivers;
Tenga en cuenta que, opcionalmente, puede especificar
PERCENT , por ejemplo, si cambia la primera línea de consulta con
SELECT TOP 50 PERCENT * .
SELECT driverslicensenr, name FROM Drivers LIMIT 2;
Alternativamente, puede agregar la
ROWNUM equivalente al uso de
LIMIT en la consulta:
SELECT * FROM Drivers WHERE driverslicensenr = 123456 AND ROWNUM <= 3;
Conversiones de tipo de datos
Siempre se deben usar los más efectivos, es decir los tipos de datos más pequeños. Siempre existe un riesgo cuando proporciona un tipo de datos enorme, mientras que uno más pequeño será más suficiente.
Sin embargo, cuando agrega una conversión de tipo de datos a la consulta, solo aumenta el tiempo de ejecución.
Una alternativa es evitar la conversión del tipo de datos tanto como sea posible. Tenga en cuenta también que no siempre es posible eliminar u omitir la conversión del tipo de datos de las consultas, pero siempre debe esforzarse por incluirlas, y debe verificar el efecto de agregar antes de ejecutar la consulta.
3. No haga que las consultas sean más complicadas de lo que deberían ser
Las conversiones de tipo de datos lo llevan al siguiente punto: no debe diseñar demasiado sus consultas. Intenta hacerlos simples y efectivos. Esto puede parecer demasiado simple o estúpido incluso para ser una pista, principalmente porque las solicitudes pueden ser complejas.
Sin embargo, en los ejemplos mencionados en las siguientes secciones, verá que puede comenzar fácilmente a hacer consultas simples más complejas de lo que deberían ser.
Operador OR
Cuando usa el operador
OR en su consulta, lo más probable es que no esté usando un índice.
Recuerde que un índice es una estructura de datos que mejora la velocidad de búsqueda de datos en una tabla de base de datos, pero es costoso: se requerirán registros adicionales y se necesitará espacio de almacenamiento adicional para mantener la estructura de datos del índice. Los índices se utilizan para buscar o buscar datos rápidamente sin tener que buscar en cada fila de la base de datos cada vez que se accede a la tabla de la base de datos. Los índices se pueden crear usando una o más columnas en una tabla de base de datos.
Si no utiliza los índices incluidos en la base de datos, la ejecución de su consulta inevitablemente llevará más tiempo. Esta es la razón por la cual es mejor buscar alternativas al uso del operador
OR en su consulta;
Considere la siguiente consulta:
SELECT driverslicensenr, name FROM Drivers WHERE driverslicensenr = 123456 OR driverslicensenr = 678910 OR driverslicensenr = 345678;
El operador puede ser reemplazado por:
Condición con
IN ; o
SELECT driverslicensenr, name FROM Drivers WHERE driverslicensenr IN (123456, 678910, 345678);
Dos
SELECT con
UNION .
Consejo: aquí debe tener cuidado de no utilizar la operación
UNION innecesaria, ya que está viendo la misma tabla varias veces. Al mismo tiempo, debe comprender que cuando usa
UNION en su consulta, el tiempo de ejecución aumenta. Alternativas a la operación
UNION : reformule la consulta para que todas las condiciones se coloquen en una sola
SELECT , o use
OUTER JOIN lugar de
UNION .
Sugerencia: Tenga en cuenta que mientras que
OR , y los otros operadores que se mencionarán en las siguientes secciones, probablemente no utilicen un índice, ¡la búsqueda de índice no siempre es preferible!
NOT operador
Cuando su consulta contiene un operador
NOT , es probable que el índice no se use, como con el operador
OR . Esto inevitablemente ralentizará su solicitud. Si no sabe lo que significa aquí, considere la siguiente consulta:
SELECT driverslicensenr, name FROM Drivers WHERE NOT (year > 1980);
Esta consulta ciertamente se ejecutará más lentamente de lo que cabría esperar, principalmente porque está formulada de manera mucho más complicada de lo que puede ser: en casos como este, es mejor buscar una alternativa. Considere reemplazar
NOT operadores de comparación como
> ,
<> o
!> ; El ejemplo anterior se puede reescribir y parecerse a esto:
SELECT driverslicensenr, name FROM Drivers WHERE year <= 1980;
Ya se ve mejor, ¿verdad?
Operador AND
El operador
AND es otro operador que no usa un índice y que puede ralentizar una consulta si se usa de una manera demasiado compleja e ineficiente, como en el siguiente ejemplo:
SELECT driverslicensenr, name FROM Drivers WHERE year >= 1960 AND year <= 1980;
Es mejor reescribir esta consulta usando la instrucción
BETWEEN :
SELECT driverslicensenr, name FROM Drivers WHERE year BETWEEN 1960 AND 1980;
ANY y ALL operadores
Además, los operadores
ANY y
ALL son los que debe tener cuidado, porque si los incluye en sus consultas, el índice no se utilizará. Las funciones de agregación alternativas como
MIN o
MAX son útiles aquí.
Consejo: en los casos en que utilice las alternativas propuestas, debe tener en cuenta que todas las funciones de agregación, como
SUM ,
AVG ,
MIN ,
MAX en muchas líneas, pueden conducir a una consulta larga. En tales casos, puede intentar minimizar el número de filas para procesar o calcular previamente estos valores. Una vez más, verá que es importante saber acerca de su entorno, su propósito de la solicitud, ... ¡Cuando decida qué solicitud utilizar!
Aislar columnas en condiciones
Además, en los casos en que se usa una columna en un cálculo o en una función escalar, no se usa el índice. Una posible solución sería simplemente seleccionar una columna específica para que ya no forme parte del cálculo o la función. Considere el siguiente ejemplo:
SELECT driverslicensenr, name FROM Drivers WHERE year + 10 = 1980;
Se ve gracioso, ¿eh? En su lugar, intente revisar el cálculo y reescribir la consulta de esta manera:
SELECT driverslicensenr, name FROM Drivers WHERE year = 1970;
4. Falta de fuerza bruta
Este último consejo significa que no debe intentar limitar demasiado la solicitud, ya que esto puede afectar su rendimiento. Esto es especialmente cierto para las uniones y para la cláusula HAVING.
Orden de tabla en combinaciones
Al unir dos tablas, puede ser importante considerar el orden de las tablas en la unión. Si ve que una tabla es significativamente más grande que la otra, es posible que deba volver a escribir la consulta para que la tabla más grande se coloque en último lugar en la unión.
Condiciones de conexión excesivas.
Si agrega demasiadas condiciones a las conexiones SQL, debe elegir una ruta específica. Sin embargo, puede ser que este camino no siempre sea más eficiente.
Condición
La
HAVING se agregó originalmente a SQL porque la palabra clave
WHERE no se podía usar con funciones agregadas.
HAVING usa generalmente con la
GROUP BY para restringir los grupos de filas devueltas solo a aquellas que satisfacen ciertas condiciones. Sin embargo, si se usa esta condición en la consulta, el índice no se usa, lo que, como ya sabe, puede llevar al hecho de que la consulta en realidad no funciona tan bien.
Si está buscando una alternativa, intente usar la
WHERE .
Considere las siguientes consultas:
SELECT state, COUNT(*) FROM Drivers WHERE state IN ('GA', 'TX') GROUP BY state ORDER BY state
SELECT state, COUNT(*) FROM Drivers GROUP BY state HAVING state IN ('GA', 'TX') ORDER BY state
La primera consulta usa la
WHERE para limitar el número de filas que deben resumirse, mientras que la segunda consulta suma todas las filas de la tabla y luego usa
HAVING para descartar las cantidades calculadas. En tales casos, la opción de la
WHERE es claramente mejor ya que no está desperdiciando recursos.
Se puede ver que no se trata de limitar el conjunto de resultados, sino de limitar el número intermedio de registros en la consulta.
Cabe señalar que la diferencia entre las dos condiciones es que la
WHERE introduce una condición para filas individuales, mientras que la
HAVING introduce una condición para agregaciones o resultados de selección, donde un resultado, como
MIN ,
MAX ,
SUM , ... fue creado a partir de múltiples líneas.
Verá, la evaluación de la calidad, la escritura y la reescritura de solicitudes no es una tarea fácil, dado que deben ser lo más productivas posible; La prevención de antipatrones y la consideración de opciones alternativas también serán parte de la responsabilidad al escribir consultas que deben realizarse en bases de datos en un entorno profesional.
Esta lista fue solo una pequeña descripción de algunos antipatterns y consejos que espero ayuden a los principiantes; Si desea tener una idea de lo que los desarrolladores más antiguos consideran los antipatrones más comunes, consulte
esta discusión .
Enfoques basados en conjuntos versus procedimientos para escribir consultas
Los antipatterns mencionados anteriormente implicaron que en realidad se reducen a una diferencia en los enfoques basados en conjuntos y de procedimiento para construir sus consultas.
El enfoque de procedimiento para las consultas es muy similar a la programación: le dice al sistema qué hacer y cómo hacerlo.
Un ejemplo de esto son las condiciones excesivas en las conexiones o casos en los que abusa de las condiciones de
HAVING , como en los ejemplos anteriores, en los que consulta una base de datos ejecutando una función y luego llamando a otra función, o usa lógica que contiene condiciones, bucles, funciones definidas por el usuario ( UDF), cursores, ... para obtener el resultado final. Con este enfoque, a menudo solicitará un subconjunto de datos, luego solicitará otro subconjunto de datos, y así sucesivamente.
Como era de esperar, este enfoque a menudo se llama una consulta "paso a paso" o "línea por línea".
Otro enfoque es un enfoque basado en conjuntos, en el que simplemente indica qué hacer. Su función es especificar las condiciones o requisitos para el conjunto de resultados que desea recibir de la consulta. Dejas la forma en que se recuperan tus datos a los mecanismos internos que determinan la implementación de la consulta: dejas que el motor de la base de datos determine los mejores algoritmos o la lógica de procesamiento para ejecutar tu consulta.
Dado que SQL está basado en conjuntos, no es sorprendente que este enfoque sea más eficiente que el procedimiento, y también explica por qué, en algunos casos, SQL puede ejecutarse más rápido que el código.
¡El asesoramiento es un enfoque basado en un conjunto de consultas que también es el que la mayoría de los empleadores líderes en la industria de la tecnología de la información le pedirán que domine! A menudo es necesario cambiar entre estos dos tipos de enfoques.
Tenga en cuenta que si alguna vez necesita una solicitud de procedimiento, debería considerar reescribirla o refactorizarla.
La siguiente parte cubrirá el plan y la optimización de consultas.