Vou tentar explicar.
Irei usar de exemplo o SQL SERVER.

No SQL SERVER, todos os resgistro são guardados em ficheiros de 8KB . Portanto, você pode imaginar que todos os dados de uma base de dados estão separados em ficheiros deste tamanho ordenados pelo Id (geralmente) que é um CLUSTERED INDEX.

Vamos usar aqui uma tabela de exemplo:

Id INT NOT NULL (aqui nossa PK),
Descricao ...,
DataInicio,
DataFim,
IdENUM_Qualquer
IsActive (se foi removido = 0)
..

Quando você faz um select qualquer, o SQL vai nos ficheiros que tem os dados da tabela que esta fazendo a query, e olha um a um, pela ordem que estão (ID) e vai encontrando um a um conforme os dados de cada registro que batem com as tuas especificações (WHERE).

Agora imagina uma tabela com milhões de registros, fica demorado, e se faz JOIN, piora. Ai entram os indexes.

Você tem essa tabela acima, mas para o dia a dia na tua APP só precisa dos registros que estão como ativos (IsActive = 1), então criamos um index para nos ajudar.

Um index tem dois tipos de colunas, INDEX COLUMNS (colunas usadas para filtro e aqui a ordem das colunas importam) e INCLUDED COLUMNS (colunas que geralmente são necessárias no teu select e a ordem não importa).

Vamos imaginar que tu precisa dos que estão ativos e da descricao, na maioria das vezes. Portanto, nosso index vai ter essas colunas.

A primeira coluna no index vai ser o IsActive decrescente, INDEX COLUMN e as outras colunas serao INCLUDED COLUMNS (Eu avaliaria o geral antes de fazer assim, mas estou usando um exemplo, faria um FILTERED INDEX e faria alguns testes, mas isso é outro papo)

Quando o index for criado, o que vai acontecer na real é que o SQL vai criar mais data pages (aquelas ali de cima de 8KB) com apenas os dados desse index. Por isso que os index ocupam mais espaco e tem que ser analisados ao criar pois afetam updates, deletes e inserts.

Agora, quando tu for fazer a tua query, e requisitar APENAS as colunas que tem no index, ao invez do SQL procurar nas data pages da propria tabela, vai buscar nas data pages do index dessa tabela, afinal, ali tem menos dados, tem só o qe é preciso, estao ordenados e as data pages sao menores. Logo, menos lugar para procurar, mais rápido.

Importante lembrar que não é preciso incluir a PK num index, pois todo index tem automaticamente um "ponto de referencia" que é a PK de onde está guardado todos os dados daquele registro do index nas data pages da tabela.

Index são muito interessantes, usados com inteligencia ajudam um monte. POdem ser CLUSTERED, NON CLUSTERED, ter filtros, ordens e por ai vai.

Espero não ter confundido mais e ter ajudado de alguma forma