Múltiplas colunas em um único índice versus múltiplos índices

Question

A versão curta da minha pergunta é qual é a diferença entre três índices cada indexando uma única coluna e um índice indexando três colunas. O fundo segue.

Sou basicamente um programador, mas tenho que trabalhar com DBA porque não temos um DBA. Estou avaliando nossos índices versus as consultas executadas em uma tabela específica. A tabela como 3 colunas que eu estou filtrando ou obtendo o valor máximo de. Na maioria das vezes, as consultas parecem

select max(col_a) from table where col_b = 'avalue'

ou

select col_c from table where col_b = 'avalue' and col_a = 'anothervalue'

Todas as colunas são independentemente indexadas. Minha pergunta é que eu veria alguma diferença se eu tivesse um índice que indexou col_b e col_a juntos, pois eles podem aparecer em uma cláusula where juntos?

    

Answer 1

Para exemplos simples de perguntas como essas, é fácil dizer qual é o melhor. Para circunstâncias do mundo real, sua milhagem pode variar significativamente com base em outras partes da consulta.

No MySQL, e talvez em outros bancos de dados, você poderia criar um índice em (col_b, col_a) e usá-lo para ambas as suas consultas - qualquer parte do índice de múltiplas colunas mais à esquerda é um índice. Um índice em (col1, col2, col3) é também um índice em (col1) e (col1, col2).

Para responder à sua pergunta específica, acho que você obteria benefícios significativos da indexação de col_b e col_a juntos. Se você ficar apenas com os índices independentes para cada coluna, uma de duas coisas acontecerá: ou o banco de dados usa um dos índices para considerar um subconjunto de linhas que correspondem a um dos valores e, em seguida, analisa-os para o segundo valor ou alguma fusão de índice on-the-fly fantasia para emular um índice em ambas as colunas. O MySQL 4 faria o primeiro, e o MySQL pode fazer o segundo. Em qualquer um desses casos, você também pode criar o índice combinado, porque espera usá-lo.

    

Answer 2

No MySQL, na maioria dos casos, um único índice é mais rápido. Você pode ver alguns benchmarks aqui:

link