Índices compostos e seu comportamento em consultas que utilizam apenas um campo
- Os índices compostos são estruturas que envolvem múltiplas colunas de uma tabela.
- Ao realizar uma consulta utilizando apenas uma coluna de um índice composto, se essa coluna for a primeira posição do índice, a estrutura de índice será utilizada. Caso contrário, o índice não será empregado na busca.
- Para um índice composto IDX(colunaA, colunaB, colunaC, colunaD), quando a consulta utiliza apenas a colunaA, o índice IDX será utilizado. Por outro lado, se a consulta utiliza apenas a colunaB, colunaC ou colunaD, o índice IDX não será aproveitado.
Esta regra é válida tanto em bancos de dados Oracle quanto em MySQL.
Se você frequentemente realiza consultas com múltiplas condições em várias colunas, considerar a criação de índices compostos é uma estratégia eficaz. Geralmente, essa abordagem é recomendada quando, além da primeira coluna, as outras colunas não são frequentemente utilizadas como critérios de filtragem. Por exemplo, para colunas X e Y, se você comumente realiza buscas usando X ou a combinação X+Y, raramente utilizando Y isoladamente, um índice composto (X,Y) seria adequado. Por outro lado, se X e Y são frequentemente utilizados como condições independentes, a criação de índices individuais para cada coluna é mais vantajosa.
A definição de índice é uma estrutura de dados projetada para busca rápida e eficiente de informações.
A essência de um índice é uma estrutura de dados. Pode ser cmopreendida como uma estrutura de dados ordenada, otimizada para buscas rápidas.
No MySQL, existem índices de coluna única e também índices de múltiplas colunas. (1) Índice de coluna única é o tipo mais comum, envolvendo apenas uma coluna. (2) Índice de múltiplas colunas abrange várias colunas em uma única estrutura.
Índices de múltiplas colunas
Exemplo:
WHERE status=3 AND data_criacao='2023-01-15' AND categoria=5 # Todos os campos do índice são utilizados, sem interrupções;
WHERE status=3 AND categoria=7 # Neste caso, data_criacao é o ponto de interrupção; status é utilizado, mas categoria não
WHERE data_criacao='2023-01-15' AND categoria=4 # status é o ponto de interrupção; campos após ele no índice são ignorados
WHERE data_criacao='2023-01-15' AND categoria=5 AND status=2 # Todos os campos são utilizados, independente da ordem na consulta
É importante notar que um índice (status, data_criacao, categoria) é diferente de um índice (status, categoria, data_criacao).
(1) SELECT * FROM produtos WHERE status=3 AND data_criacao='2023-01-15' AND categoria=5;
# As três colunas utilizam o índice e são efetivas
(2) SELECT * FROM produtos WHERE categoria=5 AND status=3 AND data_criacao='2023-01-15';
# O otimizador do MySQL reorganiza as condições, tornando este caso idêntico ao anterior.
(3) SELECT * FROM produtos WHERE status=3 AND categoria=7;
# status utiliza o índice, mas como data_criacao não está presente na consulta, categoria não é utilizada
(4) SELECT * FROM produtos WHERE status=3 AND data_criacao>'2023-01-01' AND categoria=5;
# status e data_criacao utilizam o índice, mas categoria não.
# Como data_criação usa um operador de intervalo, cria um ponto de interrupção, tornando as colunas subsequentes inúteis.
(5) SELECT * FROM produtos WHERE data_criacao='2023-01-15' AND categoria=4;
# Como status não é utilizado na consulta, data_criacao e categoria não podem usar o índice
(6) SELECT * FROM produtos WHERE status>2 AND data_criacao='2023-01-15' AND categoria=5;
# status utiliza o índice (embora como intervalo), criando um ponto de interrupção. data_criacao e categoria não são utilizados.
(7) SELECT * FROM produtos WHERE status=3 ORDER BY data_criacao;
# status é utilizado no índice e data_criacao é aproveitada para ordenação, pois os valores de data_criacao estão ordenados para cada status.
(8) SELECT * FROM produtos WHERE status=3 ORDER BY categoria;
# status utiliza o índice, mas como data_criacao não está presente, a ordenação por categoria requer uma operação adicional (filesort).
(9) SELECT * FROM produtos WHERE data_criacao='2023-01-15' ORDER BY status;
# A consulta começa com data_criacao, que não é a primeira coluna do índice, tornando tanto a busca quanto a ordenamento ineficientes.