Início / dba / Perguntas / 3728
Accepted
Simon Verbeke
Asked: 2011-07-09 10:27:24 +0800 CST

Otimização de consultas

  • 772

Em outra pergunta eles me disseram que a velocidade de um SGBD depende muito das otimizações de suas consultas. Alguém conhece bons documentos sobre isso? Na escola eu só aprendi o básico.

Por exemplo, uma consulta para localizar um usuário com uma senha.

Eu tiraria isso de uma tabela que contém as informações pessoais ou apenas apenas nome de usuário/senha.

Minha consulta seria algo como:

SELECT username, password
FROM tblUsers
WHERE username = foo

Existe alguma otimização para isso? Ou é apenas em operações muito grandes, como ter muitos JOINS com um grande número de registros?

EDIT: O exemplo acima é apenas isso, um exemplo. Estou procurando documentos sobre otimização de consultas ;)

optimization

3 respostas

  1. Minha única sugestão seria garantir que você tenha um bom índice na coluna de nome de usuário , para que a pesquisa seja o mais rápida possível. Normalmente, as otimizações precisam acontecer em consultas complexas e/ou usadas com frequência. Para alguns bancos de dados (como o Oracle), recalcular as estatísticas em uma tabela também pode ajudar (uma vez que uma tabela cresce, essas estatísticas podem ficar obsoletas, levando à escolha de otimização errada dentro do mecanismo SQL).

    • 3

  2. Sua consulta pode ser otimizada dessa maneira

    @var = foo

SELECT @var AS username, password
FROM tblUsers
WHERE username = @var

    Não há razão para pedir ao banco de dados informações que você já está fornecendo.

    A otimização de consultas é tanto uma arte quanto uma ciência. Tente fazer o mínimo de trabalho possível em uma consulta. Divida uma consulta em várias partes e use tabelas temporárias ou variáveis ​​de tabela para armazenar seu trabalho em vez de tabelas derivadas ou subconsultas. Se algo parece muito complicado, provavelmente é (esta é a peça "arte" da otimização).

    Agora aqui está a "ciência".

    Use o SQL Management Studio para gerar informações de desempenho de consulta.

    Click "Query" / "Include Client Statistics"

    Depois de abrir uma nova janela de consulta, execute esta instrução uma vez:

    SET STATISTICS IO ON

    E execute esta instrução antes de cada consulta para garantir que você sempre comece a partir de uma linha de base:

    DBCC FREEPROCCACHE

    Depois de executar a consulta, visualize a janela de saída "Mensagens" e salve as informações. Depois de modificar a consulta e executá-la novamente, você pode comparar as estatísticas de I/O.

    Por fim, aprenda a usar a ferramenta SQL Profiler para capturar e comparar rapidamente muitas estatísticas, como total de leitura, gravação, cpu e duração (ms) para cada consulta.

    • 1
  3. Best Answer

    Das muitas maneiras de otimizar consultas em um banco de dados relacional, os índices são o melhor lugar para começar. Quando você indexa uma coluna, o banco de dados armazena os registros dessa coluna em ordem de classificação para melhorar a velocidade de pesquisa. O preço que você paga por isso é sobrecarga de armazenamento adicional e velocidade reduzida para instruções INSERT/UPDATE/DELETE.

    Quando você procura um valor em uma lista não ordenada , na pior das hipóteses, você deve examinar todos os registros. Se você tiver uma lista de 1.000.000 itens, deverá verificar todos os 1.000.000. Se, por outro lado, a lista for ordenada , você pode usar uma pesquisa binária , que pode localizar um registro correspondente em até 20 pesquisas.

    A maneira mais fácil de pensar nisso é uma lista telefônica, que é indexada pelo sobrenome. Portanto, se você estiver procurando por "Wilson", pode pular a maioria das páginas e ir direto para "W". Se, por outro lado, você estiver procurando um número para descobrir o nome da pessoa que possui esse número, será forçado a começar do início e ir avançando um de cada vez, o que seria incrivelmente doloroso. .

    Imagine agora que você tem uma segunda lista telefônica classificada por número. Agora você tem o dobro de livros e isso ocupa mais espaço, mas se você costuma procurar nomes com base em um número de telefone, a troca vale a pena.

    Índices de banco de dados são muito parecidos com isso. Como uma regra geral:

    • Colunas de índice que você inclui frequentemente em cláusulas WHERE/GROUP BY.

      select * from t1 where poo = 'smelly'

      select category, count(*) from t2 group by category

    • Chaves estrangeiras de índice (colunas que se referem a chaves em outras tabelas). Por exemplo:

      select * from t1 join t2 on t1.lol = t2.rofl

      Se os dados em ambas as tabelas forem suficientemente grandes, um índice na chave estrangeira t1.lolpode resultar em ganhos perceptíveis. Chaves estrangeiras não devem ser indexadas cegamente: como qualquer coluna, índices inadequados não oferecem benefícios e às vezes podem impedir o desempenho e resultar em problemas como travamento.

    • Os índices podem consistir em várias colunas. Na verdade, se todas as colunas na lista SELECT puderem ser encontradas em um índice, a consulta poderá ser executada no índice em vez da tabela. Os índices de várias colunas podem ser explorados por condições WHERE compostas usando AND (por exemplo, a = 'lol AND b = 'rofl'), mas não OR. Se o predicado anterior fosse reescrito como a = 'lol OR b = 'rofl', você seria melhor atendido por dois índices separados nas colunas ae b.

    Isso apenas arranha a superfície, mas lhe dará ganhos substanciais para o esforço.

    • 1

relate perguntas