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.
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.
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
Estude sobre Big O(n) você vai entender um conceito que pode ser usado para entender o uso de Index.
Considere estudar o seguinte livro para um bom entendimento de indicies:
https://dl.acm.org/doi/book/10.5555/550663
Implentar sua propria arvore B, em C, tambêm é um exercicio bem bacana e altamente recomendado!!