A consulta é a operação mais comumente usada na tecnologia de banco de dados. O processo de operação de consulta é relativamente simples. Primeiro, a instrução SQL da consulta é emitida pelo cliente. Depois de receber a instrução SQL enviada pelo cliente, o servidor de banco de dados executa a instrução SQL e retorna os resultados da consulta ao cliente. Embora o processo seja muito simples, diferentes métodos de consulta e configurações de banco de dados terão um grande impacto no desempenho da consulta.
Portanto, este artigo discute técnicas de otimização de consulta comumente usadas no MySQL. As discussões incluem: melhorar a velocidade da consulta através do buffer de consulta; otimização automática de consultas baseada em índice do MySQL;
1. Melhore a velocidade da consulta por meio do buffer de consulta
Geralmente, quando usamos instruções SQL para consultar, o servidor de banco de dados executará essa instrução SQL sempre que receber SQL do cliente. Mas quando exatamente a mesma instrução SQL for recebida dentro de um determinado intervalo (como dentro de 1 minuto), ela será executada da mesma maneira. Embora isso possa garantir a natureza dos dados em tempo real, na maioria das vezes, os dados não exigem tempo real completo, o que significa que pode haver um certo atraso. Se for esse o caso, executar exatamente o mesmo SQL em um curto período de tempo não vale o ganho.
Felizmente, o MySQL nos fornece a função de buffer de consulta (o buffer de consulta só pode ser usado no MySQL 4.0.1 e superior). Podemos melhorar o desempenho da consulta até certo ponto por meio do cache de consulta.
Podemos definir o buffer de consulta através do arquivo my.ini no diretório de instalação do MySQL. A configuração também é muito simples, basta definir query_cache_type como 1. Após definir este atributo, antes de executar qualquer instrução SELECT, o MySQL irá verificar em seu buffer se a mesma instrução SELECT foi executada. Se sim, e o resultado da execução não expirou, então o resultado da consulta será retornado diretamente ao cliente. Mas ao escrever instruções SQL, observe que o buffer de consulta do MySQL diferencia maiúsculas de minúsculas. As duas instruções SELECT a seguir são as seguintes: SELECT * from TABLE1
SELECIONE * DA TABELA 1
As duas instruções SQL acima são SELECTs completamente diferentes para buffer de consulta. Além disso, o buffer de consulta não trata espaços automaticamente. Portanto, ao escrever instruções SQL, você deve tentar reduzir o uso de espaços, especialmente os espaços no início e no final do SQL (porque o cache de consulta não intercepta automaticamente os espaços em. o começo e o fim).
Embora a não configuração de um buffer de consulta às vezes possa causar perdas de desempenho, existem algumas instruções SQL que precisam consultar dados em tempo real ou não são usadas com frequência (talvez executadas uma ou duas vezes por dia). Isso requer desligar o buffer. Claro, você pode desativar o cache de consulta definindo o valor de query_cache_type, mas isso desativará permanentemente o cache de consulta. MySQL 5.0 fornece um método para desligar temporariamente o cache de consulta: SELECT SQL_NO_CACHE field1, field2 FROM TABLE1
Como a instrução SQL acima usa SQL_NO_CACHE, independentemente de esta instrução SQL ter sido executada antes, o servidor não pesquisará no buffer e a executará sempre.
Também podemos definir query_cache_type em my.ini como 2, para que o cache de consulta seja usado somente após SQL_CACHE ser usado. SELECIONE SQL_CALHE * DA TABELA1
2. Otimização automática de consultas do MySQL
Os índices são muito importantes para bancos de dados. Os índices podem ser usados para melhorar o desempenho durante as consultas. Mas às vezes o uso de índices pode reduzir o desempenho. Podemos observar a seguinte tabela SALES: CREATE TABLE SALES
(
ID INT(10) NÃO ASSINADO NÃO NULO AUTO_INCREMENT,
NOME VARCHAR(100) NÃO NULO,
FLOAT DE PREÇO NÃO NULO,
SALE_COUNT INT NÃO NULO,
SALE_DATE DATA NÃO NULA,
CHAVE PRIMÁRIA (ID),
ÍNDICE (NOME),
ÍNDICE (SALE_DATE)
)
Suponha que existam milhões de dados armazenados nesta tabela e que queiramos consultar o preço médio do produto número 1.000 em 2004 e 2005. Podemos escrever a seguinte instrução SQL: SELECT AVG(PRICE) FROM SALES
ONDE ID = 1000 E SALE_DATE ENTRE '2004-01-01' E '2005-12-31';
Se a quantidade deste produto for muito grande, ele representa quase 50% ou mais dos registros da tabela VENDAS. Então, usar o índice no campo SALE_DATE para calcular a média é um pouco lento. Porque se você usar um índice, terá que ordenar o índice. Quando há muitos registros que atendem às condições (como representar 50% ou mais dos registros em toda a tabela), a velocidade diminuirá, por isso é melhor verificar a tabela inteira. Portanto, o MySQL decidirá automaticamente se deseja usar o índice para consulta com base na proporção de dados que atende às condições da tabela inteira.
Para MySQL, o índice não é usado quando a proporção dos resultados da consulta acima em relação aos registros da tabela inteira é de cerca de 30%. Essa proporção é derivada pelos desenvolvedores do MySQL com base em sua experiência. No entanto, o valor real da escala irá variar dependendo do mecanismo de banco de dados utilizado.
3. Classificação baseada em índice
Um dos pontos fracos do MySQL é a sua classificação. Embora o MySQL possa consultar aproximadamente 15.000 registros em 1 segundo, o MySQL só pode usar no máximo um índice durante a consulta. Portanto, se a condição WHERE já ocupar o índice, o índice não será utilizado na ordenação, o que reduzirá bastante a velocidade da consulta. Podemos observar a seguinte instrução SQL: SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;
O índice no campo NAME foi usado na cláusula WHERE do SQL acima, portanto o índice não será mais usado na classificação de SALE_DATE. Para resolver este problema, podemos criar um índice composto na tabela SALES: ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME, SALE_DATE)
Desta forma, a velocidade será bastante melhorada ao usar a instrução SELECT acima para consultar. Mas tenha cuidado, ao usar este método, certifique-se de que não haja nenhum campo de classificação na cláusula WHERE. No exemplo acima, você não pode usar SALE_DATE para consultar. Caso contrário, embora a classificação seja mais rápida, não há índice separado no campo SALE_DATE. , então a consulta ficará mais lenta.
4. Detecção de consultas inacessíveis
Ao executar instruções SQL, você inevitavelmente encontrará algumas condições que devem ser falsas. A chamada condição obrigatória é que, não importa como os dados da tabela mudem, essa condição é falsa. Como valor WHERE <100 E valor> 200. Nunca podemos encontrar um número que seja menor que 100 e maior que 200.
Se você encontrar tais condições de consulta, não será necessário executar tais instruções SQL. Felizmente, o MySQL pode detectar automaticamente esta situação. Por exemplo, podemos observar a seguinte instrução SQL: SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”
A instrução de consulta acima procura registros cujo NOME seja igual a nome1 e nome2. Obviamente, esta é uma consulta inacessível e a condição WHERE deve ser falsa. Antes do MySQL executar a instrução SQL, ele primeiro analisará se a condição WHERE é uma consulta inacessível. Nesse caso, a instrução SQL não será mais executada. Para verificar isso. Primeiro usamos EXPLAIN para testar o seguinte SQL: EXPLAIN SELECT * FROM SALES WHERE NAME = "name1"
A consulta acima é uma consulta normal. Podemos ver que o item da tabela nos dados de informação de execução retornados por EXPLAIN é SALES. Isso mostra que o MySQL opera SALES. Observe novamente as seguintes afirmações: EXPLAIN SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”
Podemos observar que o item da tabela está vazio, o que significa que o MySQL não operou na tabela SALES.
5. Use várias seleções de consulta para melhorar o desempenho
Além do uso normal da instrução SELECT, o MySQL também nos fornece muitas opções que podem melhorar o desempenho da consulta. Conforme mencionado acima, SQL_NO_CACHE e SQL_CACHE, que são usados para controlar o buffer de consulta, são duas das opções. Nesta seção, apresentarei algumas opções de consulta comumente usadas.
1. STRAIGHT_JOIN: Forçar ordem de conexão
Quando conectamos duas ou mais tabelas para consulta, não precisamos nos preocupar com qual tabela o MySQL conecta primeiro e qual tabela ele conecta por último. Tudo isso é determinado por uma sequência de conexão determinada pelo MySQL através de uma série de cálculos e avaliações internas. Nas instruções SQL a seguir, TABLE1 e TABLE2 não estão necessariamente conectadas entre si: SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1, TABLE2 WHERE…
Se os desenvolvedores precisarem intervir manualmente na ordem das conexões, eles deverão usar a palavra-chave STRAIGHT_JOIN, como a seguinte instrução SQL: SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE…
Como pode ser visto na instrução SQL acima, STRAIGHT_JOIN é usado para forçar o MySQL a unir tabelas na ordem de TABLE1 e TABLE2. Se você acha que é mais eficiente ingressar em sua própria ordem do que a ordem recomendada pelo MySQL, você pode usar STRAIGHT_JOIN para determinar a ordem de conexão.
2. Intervir no uso do índice e melhorar o desempenho
O uso de índices foi mencionado acima. Em circunstâncias normais, o MySQL decidirá se deve usar um índice e qual índice usar durante a consulta. Mas em alguns casos especiais, queremos que o MySQL use apenas um ou alguns índices, ou não queremos usar um determinado índice. Isso requer o uso de algumas opções de consulta do MySQL para controlar o índice.
Limitar o escopo do uso de índices
Às vezes criamos muitos índices na tabela de dados. Quando o MySQL seleciona o índice, todos esses índices são considerados. Mas às vezes queremos que o MySQL considere apenas alguns índices em vez de todos os índices. Isso requer o uso de USE INDEX para definir a instrução de consulta. SELECIONE * DA TABELA1 USAR ÍNDICE (CAMPO1, CAMPO2)…
Como pode ser visto na instrução SQL acima, não importa quantos índices tenham sido estabelecidos em TABLE1, o MySQL considera apenas os índices estabelecidos em FIELD1 e FIELD2 ao selecionar os índices.
Limite o intervalo de índices que não são usados
Se tivermos muitos índices a considerar e poucos índices não utilizados, podemos usar IGNORE INDEX para seleção reversa. No exemplo acima, o índice considerado é selecionado, enquanto usar IGNORE INDEX serve para selecionar o índice que não é considerado. SELECIONE * DA TABELA1 IGNORAR ÍNDICE (CAMPO1, CAMPO2)…
Na instrução SQL acima, apenas os índices FIELD1 e FIELD2 na tabela TABLE1 não são usados.
Forçar o uso de um índice
Os dois exemplos acima fornecem uma escolha ao MySQL, o que significa que o MySQL não precisa usar esses índices. Às vezes esperamos que o MySQL deva usar um determinado índice (já que o MySQL só pode usar um índice durante a consulta, ele só pode forçar o MySQL a usar um índice). Isso requer o uso de FORCE INDEX para concluir esta função. SELECIONE * DA TABELA 1 ÍNDICE DE FORÇA (CAMPO1)…
A instrução SQL acima usa apenas o índice criado em FIELD1, não os índices de outros campos.
3. Use tabelas temporárias para melhorar o desempenho da consulta
Quando há muitos dados no conjunto de resultados de nossa consulta, podemos forçar o conjunto de resultados em uma tabela temporária através da opção SQL_BUFFER_RESULT, para que o bloqueio da tabela MySQL possa ser liberado rapidamente (para que outras instruções SQL possam consultá-los. registros)) e pode servir grandes conjuntos de registros para clientes por longos períodos de tempo. SELECIONE SQL_BUFFER_RESULT * DA TABELA 1 ONDE…
Semelhante à opção SQL_BUFFER_RESULT, existe SQL_BIG_RESULT. Esta opção geralmente é usada para agrupar ou palavras-chave DISTINCT. Esta opção notifica o MySQL que, se necessário, os resultados da consulta serão colocados em uma tabela temporária, ou mesmo classificados na tabela temporária. SELECIONE SQL_BUFFER_RESULT CAMPO1, CONTAGEM(*) DA TABELA1 GRUPO POR CAMPO1
6. Conclusão
Também existe um “princípio 20/80” na programação, ou seja, 20% do código ocupa 80% do tempo. O mesmo vale para o desenvolvimento de aplicativos de banco de dados. A otimização de aplicações de banco de dados concentra-se na eficiência de execução do SQL. O foco da otimização da consulta de dados é fazer com que o servidor de banco de dados leia menos dados do disco e leia as páginas sequencialmente, em vez de não sequencialmente.