Após os testes, uma consulta condicional era realizada em uma tabela contendo mais de 4 milhões de registros, e o tempo de consulta chegava a 40 segundos. Portanto, como melhorar a eficiência da consulta de instruções SQL é muito importante. A seguir, estão vários métodos de otimização de instruções de consulta amplamente distribuídos na Internet:
Primeiramente, quando o volume de dados é grande, você deve tentar evitar escanear a tabela inteira e considerar construir índices nas colunas envolvidas em onde e ordenação por dados, o que pode acelerar muito a recuperação dos dados. No entanto, existem algumas situações em que a indexação não funciona:
1. Tente evitar usar operadores != ou <> na cláusula where, caso contrário o motor abandonará o uso de índices e realizará a varredura completa da tabela.
2. Tente evitar julgamento de valor nulo nos campos da cláusula where, caso contrário o motor abandonará o uso de índices e realizará uma varredura completa da tabela, como:
selecione id de t onde num é nulo
Você pode definir o valor padrão de 0 no num, garantir que não haja valor nulo na coluna número da tabela e então consultar assim:
selecione id de t onde num=0
3. Tente evitar usar OR nas condições de entrada da cláusula where, caso contrário a engine abandonará o uso do índice e realizará uma varredura completa da tabela, como:
selecione id de t, onde num=10 ou num=20
Você pode consultar assim:
selecione id a partir de t, onde num=10
Sindicato de todos
selecione id de t onde num=20
4. A consulta a seguir também resultará em uma varredura completa da tabela:
Selecione ID de T onde o nome seja '%ABC%'
Para melhorar a eficiência, considere a busca por texto completo.
5. O in e o não in também devem ser usados com cautela, caso contrário isso levará à varredura completa da tabela, como:
selecione id de t onde num em(1,2,3)
Para valores contínuos, se você puder usar entre eles, não use em:
selecione id de t onde num está entre 1 e 3
6. Se você usar o parâmetro na cláusula where, ele também fará com que a tabela inteira seja escaneada. Como o SQL resolve apenas variáveis locais em tempo de execução, mas o otimizador não pode adiar a seleção dos planos de acesso para o tempo de execução; Ele deve ser selecionado na época da compilação. No entanto, se um plano de acesso for estabelecido em tempo de compilação, o valor da variável ainda é desconhecido e, portanto, não pode ser usado como item de entrada para seleção de índice. As seguintes declarações serão digitalizadas na íntegra:
selecione id a partir de t, onde num=@num
Você pode forçar a consulta a usar um índice em vez disso:
selecione id de t com(index(index(index name index)) onde num=@num
7. Tente evitar expressar campos na cláusula where, o que fará com que o motor abandone o uso do índice e realize a varredura completa da tabela. Por exemplo:
selecione id de t onde num/2=100
Devem ser alterados para:
selecione id a partir de t, onde num=100*2
8. Tente evitar realizar operações de função em campos da cláusula where, o que fará com que o motor abandone o uso de índices e realize a varredura completa da tabela. Por exemplo:
Select ID de T, onde substring(name,1,3)='abc' – name id que começa com abc
Selecione ID de T, onde datediff(day,createdate,'2005-11-30′)=0–'2005-11-30′ gerou ID
Devem ser alterados para:
Selecione ID a partir de T onde o nome é 'ABC%'
selecione o ID de T onde for criado>='2005-11-30′ e criado<'2005-12-1′
9. Não execute funções, operações aritméticas ou outras operações de expressão à esquerda do "=" na cláusula where, caso contrário o sistema pode não conseguir usar o índice corretamente.
10. Ao usar um campo de índice como condição, se o índice for um índice composto, então o primeiro campo do índice deve ser usado como condição para garantir que o sistema use o índice, caso contrário o índice não será usado, e a ordem dos campos deve ser consistente com a ordem do índice tanto quanto possível.
11. Não escreva algumas consultas sem sentido, como gerar uma estrutura de tabela vazia:
Selecione col1,col2 para #t a partir de T, onde 1=0
Esse tipo de código não retorna nenhum conjunto de resultados, mas consome recursos do sistema, então deve ser alterado para algo assim:
criar tabela #t(...)
12. Muitas vezes é uma boa escolha usar existe em vez de em:
selecione num de a onde num em (selecione num de b)
Substitua pela seguinte afirmação:
selecione num de a onde existe (selecione 1 de b onde num=a.num)
Pontos a prestar atenção ao construir um índice:
1. Nem todos os índices são válidos para consultas, SQL é baseado nos dados da tabela para otimizar a consulta, quando a coluna do índice tem grande duplicação de dados, consultas SQL podem não usar o índice, como uma tabela tem campos sexo, masculino e feminino quase metade cada, e mesmo que o índice seja construído sobre sexo, isso não terá impacto na eficiência das consultas.
2. Quanto mais índices não forem melhor, o índice certamente pode melhorar a eficiência da seleção correspondente, mas também reduz a eficiência de inserir e atualizar, pois o índice pode ser reconstruído ao inserir ou atualizar, então como construir um índice precisa ser cuidadosamente considerado, dependendo da situação específica. É melhor não ter mais de 6 índices em uma tabela e, se houver muitos índices, considere se é necessário construir índices em algumas colunas pouco usadas.
3. Evite atualizar ao máximo as colunas de dados indexados agrupadas, pois a ordem das colunas de dados indexadas agrupadas é a ordem física de armazenamento dos registros de tabela, e uma vez que o valor da coluna mude, isso levará ao ajuste da ordem de todos os registros de tabela, o que consumirá recursos consideráveis. Se o sistema de aplicação precisar atualizar frequentemente as colunas de índice agrupadas, ele precisa considerar se o índice deve ser construído como um índice agrupado.
Outros pontos a serem observados:
1. Tente usar campos numéricos e tente não projetar campos que contenham apenas informações numéricas como caracteres, o que reduzirá o desempenho de consultas e conexões, além de aumentar a sobrecarga de armazenamento. Isso ocorre porque o motor compara cada caractere da string um por um ao processar consultas e joins, enquanto para tipos numéricos, ele só precisa ser comparado uma vez.
2. Não use select * de t em lugar algum, substitua "*" por uma lista de campos específica e não retorne nenhum campo que não seja utilizado.
3. Tente usar variáveis de tabela em vez de tabelas temporárias. Se a variável da tabela contém uma grande quantidade de dados, note que o índice é muito limitado (apenas o índice da chave primária).
4. Evite criar e excluir tabelas temporárias com frequência para reduzir o consumo de recursos das tabelas do sistema.
5. Tabelas temporárias não são inutilizáveis, e usá-las adequadamente pode tornar certas rotinas mais eficazes, por exemplo, quando você precisa referenciar repetidamente uma tabela grande ou um conjunto de dados em uma tabela comumente utilizada. No entanto, para eventos únicos, o melhor é usar uma tabela de exportação.
6. Ao criar uma tabela temporária, se a quantidade de dados inseridos de uma vez for grande, você pode usar selecionar em vez de criar tabela para evitar aumentar a velocidade com um grande número de logs; Se a quantidade de dados não for grande, para facilitar os recursos da tabela do sistema, você deve criar a tabela primeiro e depois inserir.
7. Se uma tabela temporária for usada, certifique-se de excluir explicitamente todas as tabelas temporárias ao final do procedimento armazenado, trunque primeiro a tabela e depois descarte a tabela, para evitar um travamento longo da tabela do sistema.
8. Tente evitar usar o cursor, pois a eficiência do cursor é ruim; se os dados operados pelo cursor excederem 10.000 linhas, você deve considerar reescrever.
9. Antes de usar o método baseado em cursor ou o método de tabela temporária, você deve primeiro procurar soluções baseadas em conjuntos para resolver o problema, e o método baseado em conjuntos geralmente é mais eficaz.
10. Como tabelas temporárias, o cursor não é inutilizável. Usar cursores FAST_FORWARD para pequenos conjuntos de dados costuma ser melhor do que outros métodos de processamento linha por linha, especialmente se você precisa consultar várias tabelas para obter os dados necessários. Rotinas que incluem "total" no conjunto de resultados geralmente são mais rápidas do que aquelas executadas com o cursor. Se o tempo de desenvolvimento permitir, métodos baseados em cursor e em conjuntos podem ser tentados para ver qual funciona melhor.
11. Defina SET NOCOUNT ON no início de todos os procedimentos e triggers armazenados, e defina SET NOCOUNT OFF no final. Não há necessidade de enviar mensagens DONE_IN_PROC ao cliente após executar cada instrução do procedimento armazenado e disparo.
12. Tente evitar retornar grandes volumes de dados ao cliente; se o volume de dados for muito grande, você deve considerar se a demanda correspondente é razoável.
13. Tente evitar grandes operações de transação e melhorar a capacidade de concorrência do sistema.
|
Publicado em 11/05/2018 13:57:06
|
|
|
Publicado em 17/05/2018 10:12:27