Executando verificação de segurança...
3
FelipeIzolan
1 min de leitura ·

Como funciona os indexes em SQL?

Olá pessoal, ultimamente ando estudando sobre banco de dados relacionais, quando me deparei com os indexes.

Eu já sei que isso ajuda na leitura e atrapalha para inserir, atualizar e deletar, também ocupa mais espaço no disco.

O que gostaria de compreender é como funciona o back-end disso, já pensei sobre isso e cheguei nesse resultado:

sem index

local users = {
    { id = 1, name = "Felipe" },
    { id = 2, name = "Roberto" },
    { id = 3, name = "Maria" },
    { id = 4, name = "Ana" }
}

function select_by_name(name)
    for _,u in ipairs(users) do
        if u.name == name then return u end
    end
    
    error("no user found.")
end

local u = select_by_name("Ana") -- 4 steps

com index

local users = {
    { id = 1, name = "Felipe" },
    { id = 2, name = "Roberto" },
    { id = 3, name = "Maria" },
    { id = 4, name = "Ana" }
}

local indexes = {
  ["Ana"] = 4
}

function select_by_name(name)
  local r = users[indexes[name]]

  if r then return r end

  for _,u in ipairs(users) do
        if u.name == name then return u end
  end

    error("no user found.")
end

local u = select_by_name("Ana") -- 1 step

Seria algo assim como os indexes funcionam? ou eu estou errado?
Se alguém puder me explicar eu agradeço.

Carregando publicação patrocinada...
5
kht

Existem vários tipos de índices, mas a ideia básica é que eles criam uma estrutura de dados que abrange todos os registros (embora também existam índices parciais, mas o mais comum é sempre considerar todos).

Ou seja, no seu exemplo o segundo código sempre encontraria o respectivo registro em indexes. Claro que seria uma estrutura diferente, mas enfim.

O mais comum é usar uma árvore B, embora existam outros tipos de índices, como o hash e índice invertido, cada um com características distintas e que servem a situações específicas.

Para uma explicação bem mais detalhada, leia aqui, aqui, aqui, aqui, aqui e aqui.

4
maniero

É importante notar que a ideia básica do exemplo realmente ilustra bem o que é um índice. Ele inverte o que é chave e valor em relação a tabela. A chave da tabela é identificar o único que é usado para chegar na informação que deseja, enquanto que no índice ele é o valor que é usado para referenciar onde está o objeto que realmente se quer ter acesso, a chave é a informação que está procurando.

Como bem respondido, nessa estrutura de Lua não funciona bem como um índice, porque o índice permite chaves duplicadas (ao contrário da tabela), e a estrutura de tabela de Lua não permite chaves duplicadas. E ainda potencialmente pode ter que percorrer toda estrutura para achar o que deseja, já que o hash pode ter colisões, embora na prática será muito rápido e isso 
não acontecerá, então poderá ser tão ou mais rápido que uma árvore B.

Uma árvore B é mais complexa, mas dá melhores garantias, além de permitir a duplicidade. E principalmente funciona melhor para armazenamento de massa organizados em blocos de dados como é um HDD ou SSD.

Existem outras estruturas para certos tipos de índices que podem ser mais otimizadas para certa situação, por exemplo indexar um booleano onde é preferível usar um índice de bitmap. Onde a escrita é mais importante que a leitura pode ser útil um índice, ou mesmo as tabelas, organizadas em log structered merge.

SQL e índices são coisas completamente independentes.

Espero ter ajudado.

Farei algo que muitos pedem para aprender a programar corretamente, gratuitamente. Para saber quando, me segue nas suas plataformas preferidas. Quase não as uso, não terá infindas notificações (links aqui).

4
dougg

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

2
1