Otimizando consultas SQL em MySql

Otimização de consultas SQL, baseando-se na criação e utilização de índices.

MARCELO BORTH Mestrando em Ciências da Computação pela Universidade Estadual de Maringá - UEM. Especializando-se em Tecnologia Java pela União Pan-Americana de Ensino - UNIPAN. Graduado em Sistemas de Informação pela Universidade Paranaense - UNIPAR. Premiado como...

Ver space do autor

Estatísticas:

Visualizações:

13579

Favoritado:

4 vez(es)

Conteúdo:

Didática:

Utilidade:

0
votos: 11

Serviços:

Inclua seu próprio artigo! (ajuda)

Inclua um comentário

Adicionar aos Favoritos

Marcar como lido/assistido

Incluir anotação pessoal (ajuda)

RSS Feeds

Versão para impressão

Otimizando consultas SQL com MySql

Otimização de queries geralmente se tornam um pouco confusas, por isso algumas vezes é um desafio para o programador. Muitos têm a idéia de que consultas rápidas são as que possuem menos códigos SQL, errado. Uma consulta que tiver performance, qualidade, com certeza vai ter desempenho. Esse é o foco do artigo.

Hoje em dia se dissiparmos mais o assunto e abordarmos gerenciamento de performance, existem ferramentas que medem o tempo de execução da sua execução sql, algumas delas pode-se ser vista pelo próprio site do MySql, acessando http://dev.mysql.com/downloads/, ou até mesmo por uma aplicação de gerenciamento MySql, utilizando a aplicação do mySql Monitor, é possível visualizar e realizar uma avaliação de como está o desempenho, mas estas especificações ficam para um próximo artigo.

Exemplo:

Suponhamos que temos duas tabelas, CLIENTE e VENDAS. Necessitamos selecionar todas as vendas do determinado cliente:

SELECT vendas.*
FROM cliente a INNER JOIN vendas b
WHERE a.# = ‘RODRIGO DE SOUZA’;

Este exemplo requer necessidades especiais, e também potencial. Pois imaginamos que as tabelas contenham 70 registros na tabela cliente e 20.000 registros na tabela vendas. Teríamos o seguinte produto cartesiano para abordar apenas os dados necessários: 70 x 20.000 = 1.400.000. Este valor são todos os registros distintos que podem ser encontrados na consulta, sendo que estamos falando apenas de 20.070 registros no banco de dados.

Suponhamos que o cliente RODRIGO DE SOUZA tenha efetuado apenas 35 compras das tantas registradas; como as execuções SQL iniciam-se de baixo para cima, podemos dizer que restringimos mais rapidamente os resultados, pois são poucos os registros com este cliente. Porém, a leitura continuará sendo feita por todos os 1.400.000 registros, após execução, resultará apenas 35 tuplas encontradas.

Uso de Índices

Certamente, com estas bases de registros, se fossemos realizar a consulta anterior, varrendo os 1.400.00 registros para gerar um historio do cliente das compras compradas por ele, seria custoso, para solucionar este pequeno problema é recomendado o uso de índices, pois com a criação e utilização, o mesmo possibilita o acesso direto às propriedades necessárias da tabela.

De acordo com a álgebra relacional estamos selecionando os registros baseados na pesquisa das propriedades informadas como filtro. Sendo que os registros não possuem nenhuma ordem específica (com exceção da tabela com campo auto-incremento), os mesmos são alocados conforme ordem cronológica de inserção.

Os índices são estruturas opcionais que estão associadas à tabela, sendo que os mesmo aumentam o desempenho na recuperação dos dados. Baseia-se na estruturação de dados onde os mesmos estão organizados mediante alguma propriedade mencionada, ordenados segundo algum critério.

Dicas para criação:

* Crie preferencialmente nas colunas que são inteiros (integer), aumenta o desempenho comparado aos valores varchar, valores de inteiro geralmente tem tamanho menor e são muito mais rápidos para comparar, conseqüentemente ocupará menos espaço na memória para o processamento.

* Crie índices para as colunas de chave estrangeira que serão utilizadas nas clausulas where.

* Considere a criação baseando-se nas colunas freqüentemente usadas nas cláusulas where, order by, group by.

* Não crie índices com possíveis valores duplicados. Ex: Sexo (Masculino ou Feminino).

* Criar índices ao unir duas tabelas na cláusula where (a.id = b.id), caso contrário sua consulta ficará lenta, e será cada vez mais custosa na medida em que o número de registros aumentarem em sua base de dados.

* Crie índices com várias colunas de sua tabela.

Comandos:

Criar índice:

CREATE INDEX ON (, [ ] );

Mostrar índices de uma tabela:

SHOW INDEX FROM ;

Excluir índice:

DELETE INDEX ON ;

Se mesmo assim algum índice não estiver ajudando, ou se preferir verificar a real necessidade das colunas de sua tabela para criá-las, utilize a explicação das consutas sql:

EXPLAIN ;

Criei um índice, e agora?

Para fazer o uso do mesmo e melhorar o desempenho de sua consulta, uma das alternativas é disponibilizar esta propriedade na cláusula WHERE de seu SQL.

Não há regras de inserir índices somente em cláusulas WHERE, pelo contrário, é recomendado criar para os campos nas cláusulas ORDER BY, GROUP BY, onde são usados freqüentemente.

Exemplo 1:

SELECT vendas.*
FROM cliente c INNER JOIN vendas b
WHERE c.idade = 33
AND c.salario > 800
ORDER BY c.salario DESC
LIMIT 10;

Obs: Nesta consulta é recomendado ter índices para o campo salário.

Exemplo 2:

SELECT cliente.*
FROM cliente, ordem
WHERE cliente.id = ordem.id;

Obs: Tenha certeza que deverá ter índice para o cliente (id). Uniões sem índices são extremamente custosos.

Exemplo 3:

SELECT avg(idade)
FROM funcionarios
WHERE salario > 800;

Obs: Crie índice para idade e salario. Index (idade, salario).

Em busca do melhor índice

O melhor índice é aquele que seleciona melhor os campos a serem pesquisados. Quando ocorre uma consulta no banco de dados e ele pode percorrer mais registros do que o necessário para aquela pesquisa, contudo estes registros são desnecessários para nós programadores, isso representará um tempo perdido. Quanto menor o tempo perdido, maior performance. Portanto, o melhor índice será sempre aquele que apresentará uma maior seletividade as nossas consultas.

Conclusão

Aprendemos a melhorar o desempenho das consultas utilizadas diariamente em nossos sistemas baseando-se em índices. Existem N maneiras distintas para atender a este requisito e adaptar em nossas necessidades.

Participe! Inclua um comentário

[Fechar]

Este post é fechado - você precisa ter acesso ao post para incluir um comentário.

Nenhum comentário foi postado - seja o primeiro a comentar ;-)