Guia SQL: Como escrever consultas melhor (parte 1)

Aprenda sobre antipadrões, planos de execução, complexidade de tempo, ajuste de consulta e otimização de SQL

O SQL (Structured Query Language) é uma habilidade indispensável no setor de ciência da computação e, de um modo geral, o aprendizado dessa habilidade é relativamente simples. No entanto, a maioria das pessoas esquece que o SQL não é apenas escrever consultas, é apenas o primeiro passo adiante. Garantir o desempenho da consulta ou corresponder ao contexto em que você trabalha é algo completamente diferente.

É por isso que este guia SQL fornece uma pequena visão geral de algumas das etapas que você pode seguir para avaliar sua consulta:

• Primeiro, você começará com uma breve visão geral da importância do aprendizado de SQL para trabalhar no campo da ciência de dados;
• A seguir, você primeiro aprenderá como processar e executar consultas SQL para entender a importância da criação de consultas de qualidade. Mais especificamente, você verá que a solicitação é analisada, reescrita, otimizada e finalmente avaliada.
• Com isso em mente, você não apenas passará para alguns dos antipadrões de consultas que os iniciantes fazem ao escrever consultas, mas também aprenderá mais sobre alternativas e soluções para esses possíveis erros; Além disso, você aprenderá mais sobre a abordagem de consulta baseada em conjunto.
• Você também verá que esses antipadrões se originam de problemas de desempenho e que, além da abordagem "manual" para melhorar as consultas SQL, é possível analisar suas consultas de maneira mais estruturada e aprofundada, usando algumas outras ferramentas que ajudam a visualizar o plano de consultas; E
• Você aprenderá brevemente sobre a complexidade do tempo e a grande notação O, para ter uma idéia da complexidade do plano de execução a tempo antes de executar a solicitação;
• Você aprenderá brevemente como otimizar sua consulta.

Por que você deve aprender SQL a trabalhar com dados?

O SQL está longe de morrer: essa é uma das habilidades mais procuradas que você encontra nas descrições de cargos do setor de processamento e análise de dados, independentemente de você solicitar análise de dados, engenheiro de dados, especialista em dados ou qualquer outra função. Isso é confirmado por 70% dos participantes da pesquisa de salários da O 'Reilly Data Science para 2016, que indicam que usam SQL em seu contexto profissional. Além disso, nesta pesquisa, o SQL se destaca acima das linguagens de programação R (57%) e Python (54%).

Você entende: SQL é uma habilidade necessária quando você está trabalhando para conseguir um emprego no setor de TI.

Nada mal para uma linguagem que foi desenvolvida no início dos anos 1970, certo?

Mas por que é tão frequentemente usado? E por que ele não morreu, apesar de existir há tanto tempo?

Há vários motivos: um dos primeiros motivos pode ser que as empresas armazenem dados principalmente em sistemas de gerenciamento de banco de dados relacional (RDBMS) ou em sistemas de gerenciamento de fluxo de dados relacionais (RDSMS), e o SQL seja necessário para acessar esses dados. O SQL é a língua franca dos dados: torna possível interagir com quase qualquer banco de dados ou até mesmo criar o seu próprio local!

Se isso ainda não for suficiente, lembre-se de que existem algumas implementações SQL incompatíveis entre os fornecedores e que não estão necessariamente em conformidade com os padrões. Portanto, o conhecimento do SQL padrão é um requisito para você encontrar seu caminho na indústria (ciência da computação).

Além disso, é seguro dizer que as novas tecnologias também aderiram ao SQL, como o Hive, uma interface de linguagem de consulta semelhante ao SQL para consultar e gerenciar grandes conjuntos de dados, ou Spark SQL, que pode ser usado para executar consultas SQL. Novamente, o SQL que você encontrar lá será diferente do padrão que você pode aprender, mas a curva de aprendizado será muito mais simples.

Se você quiser fazer uma comparação, considere isso como aprender álgebra linear: depois de colocar todo esse esforço nesse assunto, você sabe que também pode usá-lo para dominar o aprendizado de máquina!

Em resumo, é por isso que você deve aprender esta linguagem de consulta:

• É muito fácil aprender, mesmo para iniciantes. A curva de aprendizado é bastante simples e gradual, portanto você escreverá as perguntas o mais rápido possível.
• Segue o princípio de “aprenda uma vez, use em qualquer lugar”, portanto, este é um grande investimento do seu tempo!
• Este é um ótimo complemento para linguagens de programação; Em alguns casos, escrever uma consulta é ainda preferível a escrever um código, porque é mais eficiente!
• ...

O que você ainda está esperando? :)

Processamento SQL e execução de consultas

Para melhorar o desempenho da sua consulta SQL, primeiro você precisa saber o que acontece dentro quando clica em um atalho para executar a consulta.

Primeiro, a solicitação é analisada em uma árvore de análise; A solicitação é analisada quanto à conformidade com requisitos sintáticos e semânticos. O analisador cria uma representação interna da solicitação de entrada. Essa saída é então transferida para o mecanismo de reescrita.

Em seguida, o otimizador deve encontrar a execução ideal ou o plano de consulta para a consulta especificada. O plano de execução determina com precisão qual algoritmo é usado para cada operação e como as operações são coordenadas.

Para encontrar o plano de execução ideal, o otimizador lista todos os planos de implementação possíveis, determina a qualidade ou o custo de cada plano, recebe informações sobre o estado atual do banco de dados e seleciona o melhor deles como plano de implementação final. Como os otimizadores de consulta podem ser imperfeitos, os usuários e administradores de banco de dados às vezes precisam examinar e ajustar manualmente os planos criados pelo otimizador para melhorar o desempenho.

Agora você provavelmente está se perguntando o que é considerado um "bom plano de consulta".

Como você já leu, a qualidade do custo de um plano desempenha um papel importante. Mais especificamente, coisas como o número de E / Ss de disco necessárias para avaliar o plano, o custo da CPU do plano e o tempo total de resposta que o cliente do banco de dados pode observar e o tempo total de execução são importantes. É aqui que surge o conceito de complexidade do tempo. Você aprenderá mais sobre isso mais tarde.

Em seguida, o plano de consulta selecionado é executado, avaliado pelo mecanismo de execução do sistema e os resultados da consulta são retornados.

Escrevendo consultas SQL

Pode não ter ficado claro na seção anterior que o princípio Garbage In, Garbage Out (GIGO) se manifesta naturalmente no processo de processamento e execução de uma consulta: quem formula a consulta também possui chaves para o desempenho de suas consultas SQL. Se o otimizador receber uma solicitação mal formulada, ele poderá fazer o mesmo ...

Isso significa que há algumas coisas que você pode fazer ao escrever uma solicitação. Como você já viu na introdução, a responsabilidade aqui é dupla: não se trata apenas de escrever consultas que atendem a um determinado padrão, mas também de coletar idéias sobre onde os problemas de desempenho podem estar ocultos na sua consulta.

Um ponto de partida ideal é pensar em "lugares" em suas consultas onde possam surgir problemas. E, em geral, existem quatro palavras-chave nas quais os recém-chegados podem esperar que ocorram problemas de desempenho:

• Condição WHERE ;
• Qualquer INNER JOIN chave INNER JOIN ou LEFT JOIN ; E também
• HAVING condição;

Obviamente, essa abordagem é simples e ingênua, mas, para um iniciante, esses pontos são excelentes indicadores, e é seguro dizer que, quando você inicia, ocorrem erros nesses locais e, curiosamente, onde também é difícil notá-los.

No entanto, você também deve entender que desempenho é algo que deve se tornar significativo. No entanto, apenas dizer que essas frases e palavras-chave são ruins não é o que você precisa quando pensa em desempenho do SQL. Ter uma HAVING WHERE ou HAVING em uma solicitação não significa necessariamente que é uma solicitação incorreta ...

Confira a próxima seção para saber mais sobre antipadrões e abordagens alternativas para criar sua consulta. Essas dicas e truques servem como guia. Como e se você realmente precisa reescrever sua solicitação depende, entre outras coisas, da quantidade de dados, do banco de dados e do número de vezes que você precisa concluir a solicitação. Depende completamente da finalidade da sua solicitação e é crucial ter algum conhecimento prévio sobre o banco de dados com o qual você trabalhará!

1. Recupere apenas os dados necessários

A conclusão “quanto mais dados, melhor” - não precisa ser seguida ao escrever SQL: você corre o risco de não apenas ficar confuso ao obter mais dados do que realmente precisa, mas também de desempenho, porque a sua consulta recebe muitos dados.

É por isso que, como regra, você deve prestar atenção à SELECT , à SELECT DISTINCT e à instrução LIKE .

SELECT

A primeira coisa que você já pode verificar ao escrever uma consulta é se a SELECT mais compacta possível. O objetivo aqui deve ser remover colunas desnecessárias do SELECT . Dessa forma, você se força a recuperar apenas os dados que atendem ao seu objetivo de consulta.

Se você correlacionou subconsultas com EXISTS , tente usar uma constante na SELECT desta subconsulta em vez de escolher o valor da coluna real. Isso é especialmente conveniente quando você verifica apenas a existência.

Lembre-se de que uma subconsulta correlacionada é uma subconsulta que usa valores de uma consulta externa. E observe que, embora o NULL possa funcionar como uma "constante" nesse contexto, isso é muito confuso!

Considere o exemplo a seguir para entender o que significa usar uma constante:

 SELECT driverslicensenr, name FROM Drivers WHERE EXISTS (SELECT '1' FROM Fines WHERE fines.driverslicensenr = drivers.driverslicensenr); 

Dica: é útil saber que ter uma subconsulta correlata nem sempre é uma boa ideia. Você sempre pode se livrar deles, por exemplo, reescrevendo-os usando INNER JOIN :

 SELECT driverslicensenr, name FROM drivers INNER JOIN fines ON fines.driverslicensenr = drivers.driverslicensenr; 

Operação DISTINCT

A SELECT DISTINCT usada para retornar apenas valores diferentes. DISTINCT é um ponto que certamente deve ser evitado, se possível. Como em outros exemplos, o tempo de execução aumenta apenas quando essa frase é adicionada à solicitação. Portanto, é sempre útil considerar se você realmente precisa desta operação DISTINCT para obter os resultados que deseja alcançar.

LIKE

Ao usar o operador LIKE em uma consulta, o índice não será usado se o padrão iniciar com % ou _ . Isso impedirá que o banco de dados use o índice (se houver). Obviamente, de outro ponto de vista, também pode ser argumentado que esse tipo de solicitação potencialmente permite obter muitos registros que não necessariamente atendem ao objetivo da solicitação.

Novamente, conhecer os dados armazenados no banco de dados pode ajudá-lo a formular um modelo que filtre todos os dados corretamente para encontrar apenas as linhas que são realmente importantes para sua consulta.

2. Limite seus resultados

Se você não conseguir evitar filtrar sua SELECT , poderá limitar seus resultados de outras maneiras. É aqui que entram abordagens como a LIMIT e as conversões de tipo de dados.

ROWNUM TOP , LIMIT e ROWNUM

Você pode adicionar instruções LIMIT ou TOP às consultas para especificar o número máximo de linhas para o conjunto de resultados. Aqui estão alguns exemplos:

  SELECT TOP 3 * FROM Drivers; 

Observe que você pode opcionalmente especificar PERCENT , por exemplo, se alterar a primeira linha de consulta com SELECT TOP 50 PERCENT * .

 SELECT driverslicensenr, name FROM Drivers LIMIT 2; 

Como alternativa, você pode adicionar a ROWNUM equivalente ao uso de LIMIT na consulta:

 SELECT * FROM Drivers WHERE driverslicensenr = 123456 AND ROWNUM <= 3; 

Conversões de tipo de dados

Os mais eficazes devem sempre ser usados, ou seja, menor, tipos de dados. Sempre existe um risco quando você fornece um grande tipo de dados, enquanto um menor é mais suficiente.

No entanto, ao adicionar uma conversão de tipo de dados à consulta, apenas o tempo de execução aumenta.

Uma alternativa é evitar a conversão do tipo de dados, tanto quanto possível. Observe também que nem sempre é possível remover ou ignorar a conversão de tipo de dados das consultas, mas você deve sempre se esforçar para incluí-las e deve verificar o efeito da adição antes de executar a consulta.

3. Não torne as consultas mais complicadas do que deveriam

As conversões de tipo de dados levam você ao seguinte ponto: você não deve criar excessivamente suas consultas. Tente torná-los simples e eficazes. Isso pode parecer simples ou estúpido demais para ser uma dica, principalmente porque os pedidos podem ser complexos.

No entanto, nos exemplos mencionados nas seções a seguir, você verá que é fácil começar a tornar as consultas simples mais complexas do que deveriam.

Operador OR

Quando você usa o operador OR na sua consulta, provavelmente você não está usando um índice.

Lembre-se de que um índice é uma estrutura de dados que melhora a velocidade de pesquisa de dados em uma tabela de banco de dados, mas é caro: registros adicionais serão necessários e espaço de armazenamento adicional será necessário para manter a estrutura de dados do índice. Os índices são usados ​​para pesquisar ou procurar dados rapidamente, sem ter que pesquisar todas as linhas do banco de dados sempre que a tabela do banco de dados for acessada. Os índices podem ser criados usando uma ou mais colunas em uma tabela de banco de dados.

Se você não usar índices incluídos no banco de dados, a execução da sua consulta inevitavelmente levará mais tempo. É por isso que é melhor procurar alternativas para usar o operador OR na sua consulta;

Considere a seguinte consulta:

 SELECT driverslicensenr, name FROM Drivers WHERE driverslicensenr = 123456 OR driverslicensenr = 678910 OR driverslicensenr = 345678; 

O operador pode ser substituído por:

Condição com IN ; ou

 SELECT driverslicensenr, name FROM Drivers WHERE driverslicensenr IN (123456, 678910, 345678); 

Duas SELECT com UNION .

Dica: aqui você deve ter cuidado para não usar a operação UNION desnecessária, porque está visualizando a mesma tabela várias vezes. Ao mesmo tempo, você deve entender que, quando você usa UNION em sua consulta, o tempo de execução aumenta. Alternativas à operação UNION : reformule a consulta para que todas as condições sejam colocadas em uma única SELECT ou use OUTER JOIN vez de UNION .

Dica: Lembre-se de que, embora OR - e os outros operadores mencionados nas seções a seguir - provavelmente não usem um índice, a pesquisa de índice nem sempre é preferível!

Operador NOT

Quando sua consulta contém um operador NOT , é provável que o índice não seja usado, como no operador OR . Isso inevitavelmente desacelerará sua solicitação. Se você não souber o que significa aqui, considere a seguinte consulta:

 SELECT driverslicensenr, name FROM Drivers WHERE NOT (year > 1980); 

Essa consulta certamente será mais lenta do que você poderia esperar, principalmente porque é formulada muito mais complicada do que pode ser: em casos como esse, é melhor procurar uma alternativa. Considere substituir NOT operadores de comparação, como > , <> ou !> ; O exemplo acima pode realmente ser reescrito e mais ou menos assim:

 SELECT driverslicensenr, name FROM Drivers WHERE year <= 1980; 

Já parece melhor, certo?

Operador AND

O operador AND é outro operador que não usa um índice e pode desacelerar uma consulta se for usada de uma maneira excessivamente complexa e ineficiente, como no exemplo a seguir:

 SELECT driverslicensenr, name FROM Drivers WHERE year >= 1960 AND year <= 1980; 

É melhor reescrever esta consulta usando a instrução BETWEEN :

 SELECT driverslicensenr, name FROM Drivers WHERE year BETWEEN 1960 AND 1980; 

ANY e ALL operadores

Além disso, você deve ter cuidado com os operadores ANY e ALL , pois se você incluí-los em suas consultas, o índice não será usado. Funções de agregação alternativas, como MIN ou MAX são úteis aqui.

Dica: nos casos em que você usa as alternativas propostas, lembre-se de que todas as funções de agregação, como SUM , AVG , MIN , MAX em muitas linhas, podem levar a uma consulta longa. Nesses casos, você pode tentar minimizar o número de linhas para processar ou pré-calcular esses valores. Mais uma vez, você percebe que é importante conhecer seu ambiente, seu objetivo da solicitação ... Quando você decide qual solicitação usar!

Isolar colunas em condições

Além disso, nos casos em que uma coluna é usada em um cálculo ou em uma função escalar, o índice não é usado. Uma solução possível seria simplesmente selecionar uma coluna específica para que não faça mais parte do cálculo ou da função. Considere o seguinte exemplo:

 SELECT driverslicensenr, name FROM Drivers WHERE year + 10 = 1980; 

Parece engraçado, né? Em vez disso, tente revisar o cálculo e reescrever a consulta da seguinte maneira:

 SELECT driverslicensenr, name FROM Drivers WHERE year = 1970; 

4. Falta de força bruta

Esta última dica significa que você não deve tentar limitar demais a solicitação, pois isso pode afetar seu desempenho. Isto é especialmente verdade para junções e para a cláusula HAVING.

Ordem da tabela nas junções

Ao ingressar em duas tabelas, pode ser importante considerar a ordem das tabelas na associação. Se você perceber que uma tabela é significativamente maior que a outra, talvez seja necessário reescrever a consulta para que a maior tabela seja colocada por último na associação.

Condições de conexão excessivas

Se você adicionar muitas condições às conexões SQL, deverá escolher um caminho específico. No entanto, pode ser que esse caminho nem sempre seja mais eficiente.

HAVING Condição

A HAVING foi originalmente adicionada ao SQL porque a palavra-chave WHERE não pôde ser usada com funções agregadas. HAVING geralmente HAVING usado com a GROUP BY para restringir grupos de linhas retornadas apenas àquelas que satisfazem determinadas condições. No entanto, se essa condição for usada na consulta, o índice não será usado, o que, como você já sabe, pode levar ao fato de que a consulta realmente não funciona tão bem.

Se você está procurando uma alternativa, tente usar a WHERE .

Considere as seguintes 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 

A primeira consulta usa a WHERE para limitar o número de linhas que precisam ser resumidas, enquanto a segunda consulta soma todas as linhas da tabela e, em seguida, usa HAVING para descartar os valores calculados. Nesses casos, a opção da WHERE é claramente melhor, pois você não está desperdiçando recursos.

Pode-se observar que não se trata de limitar o conjunto de resultados, mas de limitar o número intermediário de registros na consulta.

Deve-se notar que a diferença entre as duas condições é que a WHERE introduz uma condição para linhas individuais, enquanto a HAVING introduz uma condição para agregações ou resultados de seleção, onde um resultado, como MIN , MAX , SUM , ... foi criado a partir de várias linhas.

Veja bem, solicitações de avaliação de qualidade, gravação e reescrita não são uma tarefa fácil, uma vez que devem ser o mais produtivas possível; A prevenção de antipadrões e a consideração de opções alternativas também farão parte da responsabilidade ao escrever consultas que precisam ser executadas em bancos de dados em um ambiente profissional.

Esta lista foi apenas uma pequena visão geral de alguns antipadrões e dicas que, espero, ajudem os iniciantes; Se você quiser ter uma idéia do que os desenvolvedores mais antigos consideram os antipadrões mais comuns, consulte esta discussão .

Abordagens baseadas em conjuntos versus procedimentos para escrever consultas

Os antipatterns mencionados acima implicam que eles realmente se resumem a uma diferença nas abordagens processual e baseada em conjuntos para construir suas consultas.

A abordagem processual das consultas é muito semelhante à programação: você diz ao sistema o que fazer e como fazê-lo.

Um exemplo disso são condições excessivas em conexões ou casos em que você abusa das condições HAVING , como nos exemplos acima, nos quais você consulta um banco de dados executando uma função e depois chamando outra função, ou usa uma lógica que contém condições, loops, funções definidas pelo usuário ( UDF), cursores, ... para obter o resultado final. Com essa abordagem, você geralmente solicita um subconjunto dos dados, solicita outro subconjunto dos dados e assim por diante.

Sem surpresa, essa abordagem é frequentemente chamada de consulta "passo a passo" ou "linha por linha".

Outra abordagem é uma abordagem baseada em conjuntos, onde você simplesmente indica o que fazer. Sua função é especificar as condições ou requisitos para o conjunto de resultados que você deseja receber da consulta. Você deixa a maneira como seus dados são recuperados para os mecanismos internos que determinam a implementação da consulta: permite que o mecanismo de banco de dados determine os melhores algoritmos ou lógica de processamento para executar sua consulta.

Como o SQL é baseado em conjunto, não é de surpreender que essa abordagem seja mais eficiente do que processual e também explica por que, em alguns casos, o SQL pode executar mais rápido que o código.

O aconselhamento é uma abordagem baseada em conjuntos para a consulta também é a que os empregadores mais importantes do setor de tecnologia da informação solicitarão que você domine! Muitas vezes é necessário alternar entre esses dois tipos de abordagens.

Observe que, se você precisar de uma solicitação de procedimento, considere reescrevê-la ou refatorá-la.

A próxima parte abordará o plano e a otimização de consultas.