Como funciona a indexação de database?

Considerando que a indexação é tão importante quanto seu dataset aumenta de tamanho, alguém pode explicar como a indexação funciona em um nível agnóstico de database?

Para obter informações sobre consultas para indexar um campo, confira Como faço para indexar uma coluna do database .

Por que isso é necessário?

Quando os dados são armazenados em dispositivos de armazenamento baseados em disco, eles são armazenados como blocos de dados. Esses blocos são acessados ​​em sua totalidade, tornando-os a operação de access ao disco atômico. Os blocos de disco são estruturados da mesma maneira que as listas vinculadas; ambos contêm uma seção para dados, um ponteiro para o local do próximo nó (ou bloco) e ambos não precisam ser armazenados contiguamente.

Devido ao fato de que vários registros só podem ser classificados em um campo, podemos afirmar que a pesquisa em um campo que não esteja classificado requer uma Pesquisa Linear que requer N/2 bloqueia accesss (em média), onde N é o número de blocos que a tabela abrange. Se esse campo é um campo não-chave (ou seja, não contém inputs exclusivas), o espaço de tabela inteiro deve ser pesquisado em N blocos de accesss.

Considerando que, com um campo classificado, uma busca binária pode ser usada, que tem log2 N bloqueia accesss. Além disso, como os dados são classificados com um campo não-chave, o restante da tabela não precisa ser pesquisado em busca de valores duplicados, uma vez que um valor mais alto é encontrado. Assim, o aumento de desempenho é substancial.

O que é indexação?

A indexação é uma maneira de classificar um número de registros em vários campos. Criar um índice em um campo em uma tabela cria outra estrutura de dados que contém o valor do campo e um ponteiro para o registro ao qual se relaciona. Essa estrutura de índice é então classificada, permitindo que pesquisas binárias sejam executadas nela.

A desvantagem da indexação é que esses índices exigem espaço adicional no disco, já que os índices são armazenados juntos em uma tabela usando o mecanismo MyISAM. Esse arquivo pode atingir rapidamente os limites de tamanho do sistema de arquivos subjacente se muitos campos da mesma tabela forem indexados .

Como funciona?

Em primeiro lugar, vamos delinear um esquema de tabela de database de amostra;

 Nome do campo Tipo de dados Tamanho no disco
 id (chave primária) INT não assinado 4 bytes
 firstName Char (50) 50 bytes
 lastName Char (50) 50 bytes
 emailAddress Char (100) 100 bytes

Nota : char foi usado no lugar de varchar para permitir um tamanho preciso no valor do disco. Este database de amostra contém cinco milhões de linhas e não é indexado. O desempenho de várias consultas será agora analisado. Estas são uma consulta usando o id (um campo chave classificado) e um usando o firstName (um campo não-ordenado sem chave).

Exemplo 1campos classificados versus não classificados

Dado nosso database de amostra de r = 5,000,000 registros de um tamanho fixo dando um comprimento de registro de R = 204 bytes e eles são armazenados em uma tabela usando o mecanismo MyISAM que está usando o tamanho de bloco padrão B = 1,024 bytes. O fator de bloqueio da tabela seria bfr = (B/R) = 1024/204 = 5 registros por bloco de disco. O número total de blocos necessários para manter a tabela é N = (r/bfr) = 5000000/5 = 1,000,000 blocos.

Uma pesquisa linear no campo id exigiria uma média de N/2 = 500,000 accesss de bloco para encontrar um valor, dado que o campo id é um campo-chave. Mas como o campo id também é classificado, uma pesquisa binária pode ser conduzida exigindo uma média de log2 1000000 = 19.93 = 20 accesss em bloco. Instantaneamente podemos ver que esta é uma melhoria drástica.

Agora, o campo firstName não é classificado nem um campo-chave, portanto, uma pesquisa binária é impossível, e os valores são únicos e, portanto, a tabela exigirá uma pesquisa no final para um número exato de N = 1,000,000 accesss de blocos. É esta situação que a indexação visa corrigir.

Dado que um registro de índice contém apenas o campo indexado e um ponteiro para o registro original, é lógico que ele será menor que o registro de vários campos para o qual ele aponta. Portanto, o próprio índice requer menos blocos de disco do que a tabela original, o que, portanto, requer menos accesss de bloco para percorrer. O esquema para um índice no campo firstName é descrito abaixo;

 Nome do campo Tipo de dados Tamanho no disco
 firstName Char (50) 50 bytes
 (ponteiro de registro) Special 4 bytes

Nota : Os pointers no MySQL têm 2, 3, 4 ou 5 bytes, dependendo do tamanho da tabela.

Exemplo 2indexação

Dado nosso database de amostra de r = 5,000,000 registros com um comprimento de registro de índice de R = 54 bytes e usando o tamanho de bloco padrão B = 1,024 bytes. O fator de bloqueio do índice seria bfr = (B/R) = 1024/54 = 18 registros por bloco de disco. O número total de blocos necessários para manter o índice é N = (r/bfr) = 5000000/18 = 277,778 blocos.

Agora, uma pesquisa usando o campo firstName pode utilizar o índice para aumentar o desempenho. Isto permite uma busca binária do índice com uma média de log2 277778 = 18.08 = 19 accesss de bloco. Para encontrar o endereço do registro real, que requer um access adicional ao bloco para leitura, elevando o total para 19 + 1 = 20 bloqueia accesss, muito distante dos 1.000.000 de accesss de bloco necessários para encontrar uma correspondência firstName na tabela não indexada .

Quando deve ser usado?

Dado que a criação de um índice requer espaço em disco adicional (277,778 blocos extra do exemplo acima, um aumento de ~ 28%) e muitos índices podem causar problemas decorrentes dos limites de tamanho dos filesystems, deve-se pensar cuidadosamente campos para indexar.

Como os índices são usados ​​apenas para acelerar a pesquisa de um campo correspondente nos registros, é razoável considerar que os campos de indexação usados ​​somente para saída seriam simplesmente um desperdício de espaço em disco e tempo de processamento ao fazer uma operação de inserção ou exclusão e Deveria ser evitado. Também dada a natureza de uma pesquisa binária, a cardinalidade ou exclusividade dos dados é importante. A indexação em um campo com uma cardinalidade de 2 dividiria os dados pela metade, enquanto uma cardinalidade de 1.000 retornaria aproximadamente 1.000 registros. Com uma cardinalidade tão baixa, a eficácia é reduzida a uma sorting linear, e o otimizador de consulta evitará usar o índice se a cardinalidade for inferior a 30% do número do registro, tornando o índice um desperdício de espaço.

A primeira vez que li isso foi muito útil para mim. Obrigado.

Desde então, ganhei algumas dicas sobre a desvantagem de criar índices: se você escreve em uma tabela ( UPDATE ou INSERT ) com um índice, na verdade você tem duas operações de escrita no sistema de arquivos. Um para os dados da tabela e outro para os dados do índice (e o seu recurso (e – se em cluster – o recurso dos dados da tabela)). Se a tabela e o índice estiverem localizados no mesmo disco rígido, isso custará mais tempo. Assim, uma tabela sem um índice (um heap) permitiria operações de gravação mais rápidas. (se você tivesse dois índices, acabaria com três operações de gravação e assim por diante)

No entanto, a definição de dois locais diferentes em dois discos rígidos diferentes para dados de índice e dados de tabela pode diminuir / eliminar o problema do aumento do custo do tempo. Isso requer a definição de grupos de arquivos adicionais com os arquivos correspondentes nos discos rígidos desejados e a definição da localização da tabela / índice, conforme desejado.

Outro problema com índices é sua fragmentação ao longo do tempo à medida que os dados são inseridos. REORGANIZE ajuda, você deve escrever rotinas para tê-lo feito.

Em certos cenários, um heap é mais útil do que uma tabela com índices,

Por exemplo: – Se você tiver muitas gravações rivais, mas apenas uma leitura noturna, fora do horário comercial, para relatórios.

Além disso, uma diferenciação entre índices clusterizados e não clusterizados é bastante importante.

Ajudou-me: – O que o índice Clustered e Non clustered realmente significa?

Um índice é apenas uma estrutura de dados que torna a pesquisa mais rápida para uma coluna específica em um database. Essa estrutura é geralmente uma b-tree ou uma tabela de hash, mas pode ser qualquer outra estrutura lógica.

Para mais informações, recomendo: Como funcionam os índices do database? E como os índices ajudam?

Agora, digamos que queremos executar uma consulta para encontrar todos os detalhes de todos os funcionários com o nome ‘Abc’?

 SELECT * FROM Employee WHERE Employee_Name = 'Abc' 

O que aconteceria sem um índice?

O software de database literalmente teria que examinar cada linha da tabela Employee para ver se o Employee_Name para essa linha é ‘Abc’. E, como queremos cada linha com o nome ‘Abc’ dentro dela, não podemos simplesmente parar de procurar uma vez que encontramos apenas uma linha com o nome ‘Abc’, porque pode haver outras linhas com o nome Abc . Assim, cada linha até a última linha deve ser pesquisada – o que significa que milhares de linhas neste cenário terão que ser examinadas pelo database para encontrar as linhas com o nome ‘Abc’. Isso é o que é chamado de verificação completa da tabela

Como um índice de database pode ajudar no desempenho

O objective de ter um índice é acelerar as consultas de pesquisa, basicamente reduzindo o número de registros / linhas em uma tabela que precisa ser examinada. Um índice é uma estrutura de dados (mais comumente uma tree B) que armazena os valores de uma coluna específica em uma tabela.

Como o índice B-trees funciona?

A razão pela qual as trees B são a estrutura de dados mais popular para os índices se deve ao fato de que elas são eficientes em termos de tempo – porque buscas, exclusões e inserções podem ser feitas em tempo logarítmico. E, outro motivo importante pelo qual as trees B são mais usadas é porque os dados armazenados dentro da tree B podem ser classificados. O RDBMS normalmente determina qual estrutura de dados é realmente usada para um índice. Mas, em alguns cenários com determinados RDBMS, você pode realmente especificar qual estrutura de dados você deseja que seu database use quando você cria o próprio índice.

Como funciona um índice de tabela hash?

A razão pela qual os índices de hash são usados ​​é porque as tabelas de hash são extremamente eficientes quando se trata apenas de procurar valores. Portanto, as consultas que comparam a igualdade a uma string podem recuperar valores muito rapidamente se usarem um índice de hash.

Por exemplo, a consulta que discutimos anteriormente poderia se beneficiar de um índice de hash criado na coluna Employee_Name. A maneira como um índice hash funcionaria é que o valor da coluna será a chave na tabela de hash e o valor real mapeado para essa chave seria apenas um ponteiro para os dados da linha na tabela. Como uma tabela hash é basicamente uma matriz associativa, uma input típica seria algo como “Abc => 0x28939”, onde 0x28939 é uma referência à linha da tabela onde o Abc está armazenado na memory. Procurar um valor como “Abc” em um índice de tabela hash e recuperar uma referência à linha na memory é obviamente muito mais rápido do que varrer a tabela para localizar todas as linhas com um valor “Abc” na coluna Employee_Name.

As desvantagens de um índice hash

Tabelas de hash não são estruturas de dados ordenadas, e existem muitos tipos de consultas com as quais os índices de hash não podem ajudar. Por exemplo, suponha que você queira descobrir todos os funcionários com menos de 40 anos. Como você pode fazer isso com um índice de tabela de hash? Bem, não é possível porque uma tabela de hash só é boa para procurar pares de valores-chave – o que significa consultas que verificam a igualdade

O que exatamente está dentro de um índice de database? Portanto, agora você sabe que um índice de database é criado em uma coluna em uma tabela e que o índice armazena os valores nessa coluna específica. Mas, é importante entender que um índice de database não armazena os valores nas outras colunas da mesma tabela. Por exemplo, se criarmos um índice na coluna Employee_Name, isso significa que os valores da coluna Employee_Age e Employee_Address não são também armazenados no índice. Se apenas armazenássemos todas as outras colunas no índice, seria como criar outra cópia da tabela inteira – que ocuparia muito espaço e seria muito ineficiente.

Como um database sabe quando usar um índice? Quando uma consulta como “SELECT * FROM Employee WHERE Employee_Name = ‘Abc’” for executada, o database verificará se há um índice na (s) coluna (s) sendo consultada (s). Supondo que a coluna Employee_Name tenha um índice criado, o database terá que decidir se realmente faz sentido usar o índice para encontrar os valores que estão sendo pesquisados ​​- porque há alguns cenários em que é realmente menos eficiente usar o índice do database e mais eficiente apenas para digitalizar a tabela inteira.

Qual é o custo de ter um índice de database?

Ele ocupa espaço – e quanto maior a sua mesa, maior o seu índice. Outro impacto no desempenho com índices é o fato de que sempre que você adicionar, excluir ou atualizar linhas na tabela correspondente, as mesmas operações terão que ser feitas em seu índice. Lembre-se de que um índice precisa conter os mesmos dados até o minuto, como o que está nas colunas da tabela que o índice cobre.

Como regra geral, um índice só deve ser criado em uma tabela se os dados na coluna indexada forem consultados com frequência.

Veja também

  1. Quais colunas geralmente fazem bons índices?
  2. Como funcionam os índices do database

Exemplo clássico “Index in Books”

Considere um “Livro” de 1000 páginas, dividido por 100 seções, cada seção com páginas X.

Simples, né?

Agora, sem uma página de índice, para encontrar uma seção específica que comece com a letra “S”, você não tem outra opção além de digitalizar todo o livro. ou seja: 1000 páginas

Mas com uma página de índice no começo, você está lá. E mais, para ler qualquer seção em particular que seja importante, você só precisa examinar a página de índice, sempre e sempre. Depois de encontrar o índice correspondente, você pode pular de maneira eficiente para a seção ignorando outras seções.

Mas além de 1000 páginas, você precisará de mais 10 páginas para exibir a página de índice, totalizando 1010 páginas.

Assim, o índice é uma seção separada que armazena valores de coluna indexada + ponteiro para a linha indexada em uma ordem classificada para pesquisas eficientes.

As coisas são simples nas escolas, não é? : P

Descrição simples !!!!!!!!!!

O índice nada mais é do que uma estrutura de dados que armazena os valores de uma coluna específica em uma tabela. Um índice é criado em uma coluna de uma tabela.

Exemplo, temos uma tabela de database chamada Usuário com três colunas – Nome, Idade e Endereço. Suponha que a tabela Usuário tenha milhares de linhas.

Agora, digamos que queremos executar uma consulta para encontrar todos os detalhes de qualquer usuário chamado ‘John’. Se nós executarmos a consulta a seguir.

 SELECT * FROM User WHERE Name = 'John' 

O software de database literalmente teria que examinar cada linha da tabela User para ver se o nome dessa linha é ‘John’. Isso levará muito tempo.
Este é o local onde o índice nos ajuda “o índice é usado para acelerar as consultas de pesquisa essencialmente reduzindo o número de registros / linhas em uma tabela que precisa ser examinada”.
Como criar um índice

 CREATE INDEX name_index ON User (Name) 

Um índice consiste em valores de colunas (por exemplo, John) de uma tabela e que esses valores são armazenados em uma estrutura de dados.
Portanto, agora o database usará o índice para localizar funcionários chamados João, porque o índice provavelmente será classificado em ordem alfabética pelo nome do usuário. E, porque é classificado, significa procurar um nome é muito mais rápido porque todos os nomes que começam com um “J” estarão ao lado um do outro no índice!

Apenas uma sugestão rápida. Como a indexação custa gravações adicionais e espaço de armazenamento, portanto, se seu aplicativo exigir mais operações de inserção / atualização, talvez você queira usar tabelas sem índices, mas se precisar de mais operações de recuperação de dados, mesa.

Basta pensar em índice de database como índice de um livro. Se você tem um livro sobre cães e quer encontrar uma informação sobre o que dizer, pastores alemães, você pode folhear todas as páginas do livro e encontrar o que está procurando, mas isso, é claro, é demorado e não muito velozes. Outra opção é que você pode simplesmente ir até a seção Index do livro e depois encontrar o que está procurando usando o Nome da entidade que está procurando (neste caso, os Pastores Alemães) e também olhando o número da página para Encontre rapidamente o que você está procurando. Em Banco de Dados, o número da página é referido como um ponteiro que direciona o database para o endereço no disco onde a entidade está localizada. Usando a mesma analogia do Pastor Alemão, poderíamos ter algo como isto (“Pastor Alemão”, 0x77129) onde 0x77129 é o endereço no disco onde os dados de linha do Pastor Alemão são armazenados.

Em suma, um índice é uma estrutura de dados que armazena os valores de uma coluna específica em uma tabela, de modo a acelerar a pesquisa de consultas.

O índice SQL é algo relacionado a acelerar a pesquisa no database SQL. Index permite ao programador recuperar dados do database muito rapidamente. Suponha que você seja um estudante ou algum leitor de livros. Seu livro contém 50.000 páginas. Primeiro dia você leu algum tópico “ABC” no dia seguinte você quer ler algum outro tópico “xyz”. você nunca passará manualmente por página a página. O que você fará nessa situação é usar o índice do Book para procurar o tópico específico e depois Ir diretamente para o tópico. O índice economizou muito tempo para pesquisar o tópico. Mesmo no índice SQL, o Index permite pesquisar milhões de registros muito rapidamente a partir do database.

Um índice de database é uma estrutura de dados que melhora a velocidade das operações de recuperação de dados em uma tabela de database ao custo de gravações adicionais e espaço de armazenamento para manter a estrutura de dados do índice. Os índices são usados ​​para localizar rapidamente os dados sem precisar pesquisar todas as linhas em uma tabela de database toda vez que uma tabela de database é acessada. Os índices podem ser criados usando uma ou mais colunas de uma tabela de database, fornecendo a base para pesquisas aleatórias rápidas e access eficiente de registros ordenados.