1. Índices Simples

Ao criar tabelas, é importante compreender que independentemente do número de índices definidos em uma única tabela, uma instrução WHERE só pode utilizar um único índice ótimo. Após a filtragem inicial através do índice, o banco de dados realiza uma segunda filtragem sem índice para as demais condições, o que pode ser ineficiente. Quando o conjunto de resultados do índice ótimo for grande e houver necessidade de filtrar adicionalmente esses resultados, considere a criação de índices compostos.

2. Índices Composots

Pergunta comum: Se criarmos um índice composto sobre os campos nomeUsuario e genero, esse índice será utilizado em consultas com condições individuais como WHERE nomeUsuario = 'João' ou WHERE genero = 'M'?

Resposta:

(1) O funcionamento do índice composto depende apenas da correspondência das condições com as colunas mais à esquerda do índice;
(2) O índice composto pode funcionar parcialmente, desde que as colunas iniciais sejam utilizadas; colunas posteriores puladas não serão consideradas.

Para índices compostos: o MySQL utiliza os campos do índice da esquerda para a direita. Uma consulta pode usar apenas parte do índice, mas apenas a porção mais à esquerda. Por exemplo, para um índice chave_idx (a,b,c), são suportadas as combinações a | a,b| a,b,c, mas não b,c. Quando o campo mais à esquerda é uma constante referenciada, o índice se torna muito efiicente.

Os índices compostos são semelhantes a uma lista telefônica: nomes compostos por sobrenome e nome. A lista é ordenada primeiro pelo sobrenome e depois pelo nome. Se você conhece o sobrenome, a lista é útil; se conhece ambos, é ainda mais útil; mas se você conhece apenas o nome sem o sobrenome, a lista não ajuda.

Portanto, ao criar índices compostos, a ordem das colunas deve ser cuidadosamente considerada. Eles são eficazes para pesquisas em todas as colunas ou apenas nas primeiras colunas; pesquisas apenas em colunas posteriores não se beneficiam do índice.

Exemplo:

Criação de um índice composto sobre sobrenome, idade e gênero:

create tabela cadastro(
id int,
sobrenome varchar(50),
idade int,
genero char(1),
INDEX idx_composto (sobrenome,idade,genero));

(1) select * from cadastro where sobrenome = 'Silva' and idade = 30 and genero = 'M';   -- ordem abc
Os três campos do índice são utilizados na cláusula where e todos são eficazes

(2) select * from cadastro where genero = 'F' and idade = 25 and sobrenome = 'Santos';
A ordem das condições na cláusula where é otimizada automaticamente pelo MySQL antes da execução, tendo o mesmo efeito que o exemplo anterior

(3) select * from cadastro where sobrenome = 'Oliveira' and genero = 'M';
O sobrenome utiliza o índice, mas a idade não, então o gênero não se beneficia do índice

(4) select * from cadastro where sobrenome = 'Pereira' and idade > 40 and genero = 'F';     -- idade com valor de intervalo, ponto de interrupção
O sobrenome é utilizado, a idade também, mas o gênero não. Aqui, a idade é um intervalo, considerado um ponto de interrupção, mas ainda utiliza o índice

(5) select * from cadastro where idade = 35 and genero = 'M';   -- o índice composto deve ser usado sequencialmente e completamente
Como o primeiro campo do índice (sobrenome) não é utilizado, os campos idade e gênero não usam o índice

(6) select * from cadastro where sobrenome < 'Costa' and idade = 50 and genero = 'F';
O sobrenome é utilizado, mas idade e gênero não

(7) select * from cadastro where sobrenome = 'Ribeiro' order by idade;
O sobrenome utiliza o índice, e a idade também é usada para ordenar os resultados, pois qualquer segmento do sobrenome tem a idade ordenada

(8) select * from cadastro where sobrenome = 'Ferreira' order by genero;
O sobrenome utiliza o índice, mas a gênero não tem efeito na ordenação devido ao ponto de interrupção. O uso do EXPLAIN mostrará um filesort

(9) select * from cadastro where idade = 45 order by sobrenome;
A idade não utiliza o índice, e a ordenação pelo sobrenome também não beneficia do índice

3. Comando EXPLAIN

O uso do EXPLAIN antes de uma consulta SQL fornece informações detalhadas sobre como a consulta será executada:

id: identificador único do select select_type: tipo de select partitions: partições correspondentes type: tipo de junção possible_keys: possíveis índices selecionáveis key: índice real utilizado key_len: comprimento real do índice ref: colunas comparadas com o índice rows: número estimado de linhas a serem verificadas filtered: porcentagem de linhas filtradas pelas condições da tabela

Principais tipos de select:

Tipos de junção (do melhor para o pior):

system > const > eq_ref > ref > range > index > ALL

4. Quando os índices se tornam ineficientes:
5. Ao usar índices compostos, se não forem utilizados os campos mais à esquerda como condições de busca, o índice se torna ineficaz (princípio da correspondência mais à esquerda).
6. Se for utilizado o operador or, ambos os campos antes e depois dele devem ter índices, caso contrário todos os índices se tornarão ineficazes.
7. Quando o operador like tem o caractere % no início da condição, o índice se torna ineficaz.
8. Problemas de tipo de dados na consulta SQL podem causar falha no índice. Por exemplo, se um campo com índice é do tipo varchar mas o parâmetro passado é numérico, o índice não será utilizado.
9. Quando colunas indexadas são usadas em funções.
10. O uso do operador in em consultas SQL utiliza o índice.
11. Em campos de chave primária, o uso de not in ainda permite a utilização do índice para consultas de intervalo. Já em campos com índices normais, o uso de not in torna o índice ineficaz.

--