Otimizando consultas SQL em MySql

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

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 <nome_ indice=""> ON <nome_tabela> (<nome _coluna="">, [ <nome_coluna> ] );</nome_coluna></nome></nome_tabela></nome_>
 
Mostrar índices de uma tabela:
SHOW INDEX FROM <nome_tabela>;</nome_tabela>
 
Excluir índice:
DELETE INDEX <nome_indice> ON <nome_tabela>;</nome_tabela></nome_indice>
 
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 <consulta_sql>;</consulta_sql>
 
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.

Artigos relacionados