Em que casos são utilizados índices em bases de dados?

Os índices em bancos de dados são usados para otimizar a velocidade de execução de consultas, nomeadamente:

• Procura de dados: Aceleração de operações SELECT, especialmente com condições WHERE, JOIN e ORDER BY. O índice permite ao SGBD encontrar rapidamente as linhas necessárias, sem escanear toda a tabela.
• Ordenação: Aceleração da ordenação com ORDER BY. Se as colunas de ordenação tiverem um índice, o SGBD pode usar essa ordenação, evitando operações adicionais de ordenação.
• Junções: Aceleração de operações JOIN em colunas indexadas. O índice ajuda a encontrar rapidamente as linhas correspondentes nas tabelas relacionadas.
• Unicidade de dados: Garantia de unicidade dos valores numa coluna (através de um índice único).
• Chaves primárias: A chave primária por padrão é um índice único e é usada para acesso rápido à linha por um identificador único.
• Chaves secundárias: Usadas para garantir a integridade referencial e acelerar operações JOIN entre tabelas relacionadas.

Exemplos de colunas para indexar:

• Colunas frequentemente procuradas (WHERE uid = 123).
• Colunas usadas em condições JOIN (table1.id = table2.ref_id).
• Colunas frequentemente ordenadas (ORDER BY created_at DESC).
• Colunas com alta cardinalidade (muitos valores únicos).

Casos em que os índices podem ser ineficazes ou desnecessários:

• Tabelas muito pequenas: Em tabelas pequenas, a varredura pode ser mais rápida ou comparável em velocidade à procura por índice.
• Colunas com baixa cardinalidade: Um índice numa coluna com poucos valores únicos (por exemplo, um campo booleano TRUE/FALSE) pode não trazer uma aceleração significativa.
• Operações frequentes de escrita (INSERT, UPDATE, DELETE): Os índices requerem atualização ao modificar dados, o que pode atrasar as operações de escrita. Não é aconselhável indexar todas as colunas indiscriminadamente.
• Colunas em condições WHERE com funções: Se uma função for aplicada à coluna em WHERE (por exemplo, WHERE YEAR(date_column) = 2023), um índice normal na date_column não é utilizado. Será necessário um índice funcional (se suportado pelo SGBD) ou reescrever a consulta.