Como é que o índice é utilizado numa tabela grande?

O índice numa tabela grande acelera significativamente as operações de seleção de dados (SELECT), bem como as operações de modificação (UPDATE, DELETE) ao usar a condição WHERE com colunas indexadas. Sem um índice, o banco de dados precisa percorrer toda a tabela (varredura completa da tabela), o que é muito lento em grandes volumes de dados.

Ao usar um índice, o banco de dados primeiro acessa a estrutura do índice (geralmente uma árvore B), que é muito menor do que a própria tabela e contém referências ordenadas às linhas de dados. Encontrando as referências necessárias no índice, o banco de dados acessa rapidamente diretamente às linhas requeridas.

Importante:

• Os índices ocupam espaço no disco.
• Criar e manter índices durante operações de inserção (INSERT) e atualização (UPDATE) requer recursos adicionais.
• Muitos índices ou índices mal escolhidos podem desacelerar operações de escrita.

Exemplo de criação de índice em Ruby on Rails:

# Criação de um índice na coluna 'email' na tabela 'users'
add_index :users, :email

# Criação de um índice único na coluna 'username'
add_index :users, :username, unique: true

# Criação de um índice composto nas colunas 'user_id' e 'product_id'
add_index :orders, [:user_id, :product_id]

Exemplo de uso de índice em SQL (executado pelo banco de dados):

-- Consulta que usará o índice na coluna email
SELECT * FROM users WHERE email = 'test@example.com';