O que significa "índice" em RDBMSs? [fechado]

Pense em um índice como "tabela de conteúdos"... que é uma lista ordenada de ponteiros para posições em um arquivo, também conhecido como deslocamentos. Digamos que você tenha milhões de registros armazenados em uma tabela, em vez de pesquisar critérios de correspondência na tabela, é muito mais rápido fazer referência a uma lista ordenada de correspondências e empilhar os ponteiros para as linhas correspondentes específicas. Um exemplo perfeito de um índice é um campo de chave primária de tabelas, geralmente seu campo "id". Se você quiser o id de linha # 11234566, é muito mais rápido pedir ao índice um ponteiro para os dados do que varrer a fonte de dados para a posição 11234566.

Aqui está um uso não tão óbvio da indexação:

CREATE TABLE activity_log (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
activity_type_id SMALLINT UNSIGNED NOT NULL,
datetime_created DATETIME
KEY(activity_type_id),
PRIMARY KEY(id)
);
CREATE TABLE activity_log_to_date_key (
activity_log_id INT UNSIGNED NOT NULL,
date_created_key  INT UNSIGNED NOT NULL REFERENCES dim_datetime(id),
UNIQUE KEY(activity_log_id),
KEY(date_created_key)
);
CREATE TABLE dim_datetime (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
date_hour DATETIME NOT NULL,
PRIMARY KEY(id),
KEY(date_hour)
);

Sua operação pode criar seu registro de log, mas, em seguida, criar uma referência a um datetime indexado que seja mais rápido de pesquisar/classificar do que sua tabela de log. Em seguida, reúna sua tabela de log em sua própria chave primária. Se você precisar que eu expanda isso, me avise. Espero que isto faça sentido.

Exemplo de consulta:

SELECT a.activity_log_id, al.activity_type_id, al.datetime_created
FROM activity_log_to_date_key a 
INNER JOIN dim_datetime d ON (d.id = a.date_created_key)
LEFT JOIN activity_log al ON (al.id = a.activity_log_id)
WHERE d.date_hour BETWEEN '2009-01-01 00:00:00' AND '2009-06-01 12:00:00';

Um ponto que muitas pessoas parecem perder é que um DBMS geralmente (ou só pode) usar apenas um índice por referência de tabela em uma consulta e, se puder e usar vários índices, provavelmente seria mais rápido usar um combinado índice se presente.

Por exemplo, se pesquisar linhas em uma tabela grande, WHERE AnIntegerColumn = 42 AND AnOtherInt = 69a rota mais rápida para essas linhas seria um índice nas duas colunas AnIntegerColumn e AnOtherInt. Se você tiver apenas um índice em cada um individualmente, mas nenhum índice combinado, o banco de dados pesquisará um ou outro índice e filtrará separadamente os resultados com a segunda cláusula ou digitalizará ambos e casará os resultados posteriormente.

Outra operação simples comum que pode ser aprimorada com índices compostos é WHERE SomeColumn = <SomeValue> ORDER BY SomeOtherColumn- se houver um índice em SomeColumn e SomeOtherColumn (na ordem correta), as operações de filtragem e ordenação podem ser executadas ao mesmo tempo em algumas circunstâncias.

Adicionar muitos índices pode ser uma otimização ruim, é claro, pois o espaço extra usado para armazenar os índices (e a carga de E/S para mantê-los se seu banco de dados vê muitas operações de gravação) pode ser um problema pior do que as consultas de leitura um pouco menos ideais , então não exagere.

David e Randy cuidam disso. Eu só queria acrescentar que o EXPLAINcomando pode ser uma grande ajuda para descobrir quando você obterá uma grande economia ao criar um índice, bem como sugerir quais índices são necessários. Ele exibirá as etapas que o banco de dados está executando para executar sua consulta, para que você saiba quais bits estão demorando mais.

Algo que ainda não vi mencionado aqui é que, quando você tem mais de um disco, provavelmente deseja colocar seu índice em um disco diferente daquele em que os dados estão. Isso pode acelerar algumas operações. Acho que isso merece uma pergunta por si só.