O sistema de banco de dados é o núcleo do sistema de informações de gerenciamento. O processamento de transações on-line baseado em banco de dados (OLTP) e o processamento analítico on-line (OLAP) são uma das aplicações de computador mais importantes em bancos, empresas, governos e outros departamentos. Baseado em exemplos de aplicação e combinado com a teoria de banco de dados, este artigo apresenta a aplicação da tecnologia de otimização de consultas em sistemas reais. A julgar pelos exemplos de aplicação da maioria dos sistemas, as operações de consulta representam a maior proporção de várias operações de banco de dados, e a instrução SELECT na qual a operação de consulta se baseia é a instrução mais cara entre as instruções SQL. Por exemplo, se a quantidade de dados se acumular até um determinado nível, como as informações da tabela do banco de dados de contas de um banco acumulando milhões ou até dezenas de milhões de registros, uma verificação completa da tabela geralmente leva dezenas de minutos ou até horas. Se você adotar uma estratégia de consulta melhor do que uma varredura completa da tabela, muitas vezes poderá reduzir o tempo de consulta para alguns minutos, o que mostra a importância da tecnologia de otimização de consulta.
Durante a implementação do projeto de aplicativo, o autor descobriu que ao desenvolver aplicativos de banco de dados usando algumas ferramentas front-end de desenvolvimento de banco de dados (como PowerBuilder, Delphi, etc.), muitos programadores se concentram apenas na beleza da interface do usuário e não pagam atenção à eficiência das declarações de consulta, resultando em todos os problemas. O sistema aplicativo desenvolvido é ineficiente e causa sério desperdício de recursos. Portanto, como projetar declarações de consulta eficientes e razoáveis é muito importante. Baseado em exemplos de aplicação e combinado com a teoria de banco de dados, este artigo apresenta a aplicação da tecnologia de otimização de consultas em sistemas reais.
Analise o problema
Muitos programadores acreditam que a otimização de consultas é tarefa do SGBD (sistema de gerenciamento de banco de dados) e tem pouco a ver com as instruções SQL escritas pelos programadores. Muitas vezes, um bom plano de consulta pode melhorar o desempenho do programa dezenas de vezes. O plano de consulta é uma coleção de instruções SQL enviadas pelo usuário e o plano de consulta é uma coleção de instruções geradas após a otimização. O processo de processamento do plano de consulta do SGBD é o seguinte: após concluir a verificação léxica e sintática da instrução de consulta, a instrução é enviada ao otimizador de consulta do SGBD. Depois que o otimizador conclui a otimização algébrica e a otimização do caminho de acesso, o módulo pré-compilado processa o. declaração e gerar um plano de consulta, em seguida, enviá-lo ao sistema para processamento e execução no momento apropriado e, finalmente, retornar os resultados da execução ao usuário. Em versões superiores de produtos de banco de dados reais (como Oracle, Sybase, etc.), são usados métodos de otimização baseados em custos. Essa otimização pode estimar o custo de diferentes planos de consulta com base nas informações obtidas na tabela de dicionário do sistema e, em seguida, selecionar. um Melhor planejamento. Embora os produtos de banco de dados atuais estejam cada vez melhores na otimização de consultas, as instruções SQL enviadas pelos usuários são a base para a otimização do sistema. Portanto, é difícil imaginar que um plano de consulta originalmente ruim se tornará eficiente após a otimização do sistema. declarações que os usuários escrevem são cruciais. Não discutiremos a otimização de consulta realizada pelo sistema por enquanto. O foco a seguir é soluções para melhorar os planos de consulta do usuário.
resolver problemas
A seguir, tomamos o sistema de banco de dados relacional Informix como exemplo para apresentar métodos para melhorar os planos de consulta do usuário.
1. Uso razoável de índices
O índice é uma estrutura de dados importante no banco de dados e seu objetivo fundamental é melhorar a eficiência da consulta. A maioria dos produtos de banco de dados agora usa a estrutura de índice ISAM proposta pela primeira vez pela IBM. A utilização de índices deve ser adequada e os princípios para sua utilização são os seguintes:
●Crie índices em colunas que são conectadas com frequência, mas não são designadas como chaves estrangeiras, enquanto o otimizador gera automaticamente índices para campos conectados com pouca frequência.
● Crie índices em colunas que são frequentemente classificadas ou agrupadas (ou seja, operações agrupar ou ordenar por).
●Crie pesquisas em colunas com muitos valores diferentes que são frequentemente usados em expressões condicionais. Não crie índices em colunas com poucos valores diferentes. Por exemplo, existem apenas dois valores diferentes na coluna “Gênero” da tabela de funcionários, “Masculino” e “Feminino”, portanto não há necessidade de criar um índice. Se você criar um índice, isso não apenas não melhorará a eficiência da consulta, mas também reduzirá seriamente a velocidade de atualização.
●Se houver múltiplas colunas a serem classificadas, você poderá criar um índice composto nessas colunas.
●Use ferramentas do sistema. Por exemplo, o banco de dados Informix possui uma ferramenta tbcheck que pode verificar índices suspeitos. Em alguns servidores de banco de dados, o índice pode ser inválido ou a eficiência de leitura pode ser reduzida devido a operações frequentes. Se uma consulta usando o índice ficar lenta sem motivo aparente, você pode tentar usar a ferramenta tbcheck para verificar a integridade do índice. e repare-o se necessário. Além disso, quando uma tabela de banco de dados atualiza uma grande quantidade de dados, a exclusão e a reconstrução do índice podem melhorar a velocidade da consulta.
2. Evite ou simplifique a classificação
A classificação repetida de tabelas grandes deve ser simplificada ou evitada. O otimizador evita a etapa de classificação quando pode usar um índice para produzir automaticamente a saída na ordem correta. Aqui estão alguns fatores que influenciam:
●O índice não inclui uma ou mais colunas a serem ordenadas;
●A ordem das colunas na cláusula group by ou order by é diferente da ordem do índice;
●As colunas ordenadas provêm de tabelas diferentes.
Para evitar ordenações desnecessárias, é necessário adicionar índices corretamente e mesclar tabelas de banco de dados de maneira razoável (embora às vezes isso possa afetar a normalização da tabela, a melhoria na eficiência vale a pena). Se a classificação for inevitável, tente simplificá-la, como estreitar o intervalo de colunas para classificação, etc.
3. Elimine o acesso sequencial a grandes dados de linhas de tabelas
Em consultas aninhadas, o acesso sequencial às tabelas pode ter um impacto fatal na eficiência da consulta. Por exemplo, usando uma estratégia de acesso sequencial, se uma consulta com três níveis aninhados consultar 1.000 linhas em cada nível, essa consulta consultará 1 bilhão de linhas de dados. A principal forma de evitar isso é indexar as colunas unidas. Por exemplo, duas tabelas: tabela de alunos (número do aluno, nome, idade...) e tabela de seleção de cursos (número do aluno, número do curso, notas). Caso duas tabelas sejam conectadas, deverá ser criado um índice no campo de conexão “número do aluno”.
Você também pode usar uniões para evitar acesso sequencial. Embora existam índices em todas as colunas de verificação, algumas formas de cláusulas where forçam o otimizador a usar acesso sequencial. A consulta a seguir forçará operações sequenciais na tabela de pedidos: SELECT * FROM ordens WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
Embora existam índices em customer_num e order_num, o otimizador ainda usa o caminho de acesso sequencial para verificar toda a tabela na instrução acima. Como esta instrução recupera uma coleção separada de linhas, ela deve ser alterada para a seguinte instrução:
SELECIONE *FROM pedidos ONDE customer_num=104 AND order_num>1001
UNIÃO
SELECIONE *FROM pedidos WHERE order_num=1008
Isso permite que o caminho do índice seja usado para processar consultas.
4. Evite subconsultas correlacionadas
Se um rótulo de coluna aparecer na consulta principal e na consulta na cláusula where, é provável que a subconsulta deva ser consultada novamente quando o valor da coluna na consulta principal for alterado. Quanto mais níveis aninhados de uma consulta, menor será a eficiência, portanto, subconsultas devem ser evitadas tanto quanto possível. Se uma subconsulta for inevitável, filtre o máximo de linhas possível na subconsulta.
5. Evite expressões regulares difíceis
As palavras-chave MATCHES e LIKE suportam correspondência de curingas, tecnicamente chamadas de expressões regulares. Mas este tipo de correspondência é particularmente demorado. Por exemplo: SELECT * FROM cliente WHERE CEP LIKE “98_ _ _”
Mesmo que um índice seja criado no campo do CEP, a varredura sequencial ainda é usada neste caso. Se você alterar a instrução para SELECT * FROM customer WHERE zipcode > "98000", o índice será usado para consultar ao executar a consulta, o que obviamente melhorará muito a velocidade.
Além disso, evite substrings não iniciais. Por exemplo, a instrução: SELECT * FROM customer WHERE zipcode[2, 3]>"80" usa uma substring não inicial na cláusula where, portanto, esta instrução não usa o índice.
6. Use tabelas temporárias para agilizar as consultas
Classificar um subconjunto da tabela e criar uma tabela temporária às vezes pode acelerar as consultas. Isso ajuda a evitar múltiplas operações de classificação e simplifica o trabalho do otimizador. Por exemplo: SELECT cust.name, rcVBles.balance,...outras colunas
SELECIONE cust.name,rcVBles.balance,...outras colunas
DE custo,rcvbles
ONDE cust.customer_id = rcvlbes.customer_id
E rcvblls.balance>0
E cust.postcode>"98000"
ORDER POR cust.name
Se esta consulta for executada várias vezes em vez de apenas uma vez, você poderá encontrar todos os clientes não pagos em um arquivo temporário e classificá-los por nome de cliente: SELECT cust.name, rcvbles.balance,...outras colunas
SELECIONE cust.name,rcvbles.balance,...outras colunas
DE custo,rcvbles
ONDE cust.customer_id = rcvlbes.customer_id
E rcvblls.balance>0
ORDER POR cust.name
INTO TEMP cust_with_balance
Em seguida, consulte a tabela temporária da seguinte maneira: SELECT * FROM cust_with_balance
ONDE código postal>"98000"
Há menos linhas na tabela temporária do que na tabela principal, e a ordem física é a ordem necessária, o que reduz a E/S do disco, de modo que a carga de trabalho da consulta pode ser bastante reduzida.
Nota: Após a criação da tabela temporária, ela não refletirá a modificação da tabela principal. Quando os dados da tabela principal são modificados com frequência, tome cuidado para não perder dados.
7. Use a classificação para substituir o acesso não sequencial
O acesso não sequencial ao disco é a operação mais lenta e é representado pelo movimento para frente e para trás do braço de acesso ao disco. As instruções SQL ocultam essa situação, facilitando a escrita de consultas que exigem acesso a um grande número de páginas não sequenciais ao escrever aplicativos. Às vezes, usar os recursos de classificação do banco de dados em vez do acesso não sequencial pode melhorar as consultas.
-