Como a indexação do banco de dados funciona?

votos
1k

Dado que indexingé tão importante quanto o seu conjunto de dados aumenta de tamanho, alguém pode explicar como funciona a indexação em um database-agnosticnível?

Para obter informações sobre as consultas ao índice de um campo, veja Como faço para indexar uma coluna de banco de dados .

Publicado 04/08/2008 em 11:07
fonte usuário
Em outras línguas...                            


10 respostas

votos
2k

Por que é necessário?

Quando os dados são armazenados em dispositivos de armazenamento em disco, que é armazenado como blocos de dados. Estes blocos são acessados ​​em sua totalidade, o que os torna a operação de acesso ao disco atômica. blocos de disco são estruturados da mesma maneira como listas ligadas; ambos contêm uma secção de dados, um ponteiro para a localização do nó seguinte (ou bloco), e ambos não precisam de ser armazenados de forma contígua.

Devido ao fato de que um número de registros só podem ser classificadas em um campo, podemos afirmar que a busca em um campo que não é classificada requer uma pesquisa linear que requer N/2acessos bloco (em média), onde Né o número de blocos que a mesa se estende. Se esse campo é um campo não-chave (ou seja, não contêm entradas exclusivas), em seguida, todo o espaço de tabela deve ser procurado no Nbloco de acessos.

Considerando que, com um campo classificado, uma pesquisa binária pode ser usado, que tem log2 Nbloco de acessos. Também desde que os dados são classificados dado um campo não-chave, o resto da mesa não precisa ser pesquisado para valores duplicados, uma vez que um valor mais alto é encontrado. Assim, o aumento do desempenho é substancial.

O que é indexação?

A indexação é uma maneira de ordenar uma série de registros em vários campos. Criando um índice em um campo de uma tabela cria uma outra estrutura de dados que contém o valor de campo, e um ponteiro para a ficha que se relaciona. Esta estrutura do índice é então classificada, permitindo Pesquisas binários a serem executadas nele.

A desvantagem para a indexação é que esses índices necessitar de espaço adicional no disco uma vez que os índices são armazenados juntos em uma tabela usando o motor MyISAM, este arquivo pode rapidamente atingir os limites de tamanho do sistema de arquivos subjacente se muitos campos dentro da mesma tabela são indexados .

Como funciona?

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

Nome do campo Tipo de dados Tamanho em disco
ID (chave primária) unsigned int 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 exato no valor de disco. Esta base de dados de exemplo contém cinco milhões de linhas e é não indexados. O desempenho de várias consultas serão agora analisadas. Estes são uma consulta usando o id (um campo de chave ordenada) e uma usando o firstName (um campo indiferenciado não-key).

Exemplo 1 - ordenadas vs campos indiferenciados

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

A pesquisa linear no campo id exigiria uma média de N/2 = 500,000bloquear acessos para encontrar um valor, uma vez que o campo id é um campo de chave. Mas desde que o campo id também é classificado, uma pesquisa binária pode ser conduzida exigindo uma média de log2 1000000 = 19.93 = 20bloquear acessos. Instantaneamente, podemos ver esta é uma melhoria drástica.

Agora o firstName campo não é nem ordenada nem um campo de chave, então uma busca binária é impossível, nem são os valores único, e, assim, a mesa vai exigir a procura para o fim para um exato N = 1,000,000bloquear acessos. É esta situação que a indexação visa corrigir.

Tendo em conta que um registro de índice contém apenas o campo indexado e um ponteiro para o registro original, é lógico que ele vai ser menor do que o registro multi-campo que ele aponta. Assim, o índice em si requer menos blocos de disco do que a tabela original, o que, por conseguinte, requer menos bloco acessos para percorrer. O esquema para um índice na firstName campo é descrito a seguir;

Nome do campo Tipo de dados Tamanho em disco
firstName CHAR (50) 50 bytes
(Registo de ponteiro) 4 bytes especiais

Nota : Os ponteiros em MySQL são 2, 3, 4 ou 5 bytes de comprimento, dependendo do tamanho da tabela.

Exemplo 2 - indexação

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

Agora uma pesquisa usando o firstName campo 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 = 19acessos de bloco. Para encontrar o endereço do registro real, o que requer um acesso mais bloco para ler, elevando o total para 19 + 1 = 20bloquear acessos, muito longe do bloco de 1.000.000 acessos necessários para encontrar uma firstName jogo na tabela não indexada.

Quando ele 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 que muitos índices podem causar problemas decorrentes dos limites de tamanho de sistemas de arquivos, o pensamento cuidadoso deve ser usado para selecionar a correta campos para indexar.

Desde índices são usados ​​apenas para acelerar a busca por um campo correspondente dentro dos registros, é lógico que os campos de indexação usados ​​apenas para saída seria simplesmente um desperdício de espaço em disco e tempo de processamento ao fazer uma inserção ou excluir a operação, e, assim, Deveria ser evitado. Além disso, dada a natureza de uma pesquisa binária, a cardinalidade ou singularidade dos dados é importante. Indexação em um campo com uma cardinalidade de 2 seria dividir os dados pela metade, enquanto que uma cardinalidade de 1.000 retornaria aproximadamente 1.000 registros. Com uma cardinalidade tão baixo a eficácia é reduzida a um tipo linear, e o otimizador de consulta será evitar o uso do índice se a cardinalidade é inferior a 30% do número de registro, efetivamente tornando o índice de um desperdício de espaço.

Respondeu 04/08/2008 em 11:41
fonte usuário

votos
168

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

Desde então ganhei alguns insights sobre o lado negativo da criação de índices: se você escrever em uma tabela ( UPDATEou INSERT IGNORE ) com um índice, você tem realmente duas operações de escrita no sistema de arquivos. Um para os dados da tabela e outra para os dados de índice (e a recorrer dele (e - se agrupado - com o recurso dos dados da tabela)). Se a tabela e índice estão localizados no mesmo disco rígido isso custa mais tempo. Assim, uma tabela sem um índice (um montão), iria permitir que para operações de gravação mais rápidos. (se você tivesse dois índices que você iria acabar 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 os dados da tabela podem diminuir / eliminar o problema do aumento do custo de tempo. Isso requer definição de grupos de arquivos adicionais de acordo com os arquivos nos discos rígidos desejados e definição de localização de mesa / index como desejado.

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

Em determinados cenários uma pilha é mais útil do que uma tabela com índices,

por exemplo: - Se você tem um monte de gravações que rivalizam mas apenas um nightly lido fora do horário comercial para relatórios.

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

Ajudou-me: - O que cluster e não agrupado índice realmente significa?

Respondeu 30/04/2013 em 15:31
fonte usuário

votos
124

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

Para mais informações, eu recomendo: Como índices de banco de dados funciona? E, como índices ajudar?

Respondeu 20/02/2014 em 15:40
fonte usuário

votos
86

Agora, vamos dizer que queremos executar uma consulta para encontrar todos os detalhes de todos os empregados que são nomeados 'ABC'?

SELECT * FROM Employee 
WHERE Employee_Name = 'Abc'

O que aconteceria sem um índice?

Software de banco de dados que, literalmente, tem que olhar para cada linha na tabela de funcionários para ver se o Employee_Name para essa linha é 'ABC'. E, porque queremos que cada linha com o nome 'ABC' dentro dele, não podemos simplesmente parar de olhar 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 pesquisado - o que significa que milhares de linhas neste cenário terá que ser examinado pelo banco de dados para encontrar as linhas com o nome 'ABC'. Isto é o que é chamado de uma varredura completa da tabela

Como um índice de banco de dados pode ajudar o desempenho

O ponto inteiro de ter um índice é acelerar consultas de pesquisa essencialmente reduzir o número de registros / linhas em uma tabela que precisam ser examinados. Um índice é uma estrutura de dados (mais comumente uma árvore B-) que armazena os valores de uma coluna específica de uma tabela.

Como é que árvores-B índice de trabalhar?

As árvores B- razão são a estrutura de dados mais popular para os índices é devido ao fato de que eles são tempo eficiente - porque look-ups, exclusões e inserções podem ser feitas em tempo logarítmica. E, outra razão importante árvores B- são mais comumente usados ​​é porque os dados que são armazenados dentro da árvore B- podem ser classificados. O RDBMS tipicamente determina qual a estrutura de dados é efectivamente utilizado para um índice. Mas, em alguns cenários com certos RDBMS de, você pode realmente especificar qual estrutura de dados que você quer que seu banco de dados para usar ao criar o índice em si.

Como é que um índice de tabela hash funciona?

A razão índices hash são usados ​​é porque tabelas hash são extremamente eficiente quando se trata de apenas olhando para cima valores. Assim, as consultas que comparam a igualdade, a uma corda pode recuperar valores muito rápido se eles usam um índice de hash.

Por exemplo, a consulta discutimos anteriormente poderia se beneficiar de um índice hash criado na coluna Employee_Name. A forma como um índice hash iria trabalhar é que o valor de coluna serão a chave na tabela hash e o valor real mapeada para essa chave seria apenas um ponteiro para os dados de linha da tabela. Desde uma tabela hash é basicamente uma matriz associativa, uma entrada típica seria algo parecido com “Abc => 0x28939", onde 0x28939 é uma referência para a linha da tabela onde ABC é armazenado na memória. Olhando-se um valor como “Abc” em um índice de tabela hash e voltar uma referência para a linha na memória é, obviamente, muito mais rápido do que a digitalização da tabela para encontrar todas as linhas com um valor de “Abc” na coluna Employee_Name.

As desvantagens de um índice hash

As tabelas de hash não são ordenados estruturas de dados, e há muitos tipos de consultas que os índices hash não pode mesmo ajudar com. Por exemplo, suponha que você queira descobrir todos os funcionários que têm menos de 40 anos de idade. Como você pôde fazer isso com um índice de tabela de hash? Bem, não é possível, porque uma tabela hash só é bom para procurar pares de chaves de valor - o que significa consultas que verificam a igualdade

O que exatamente está dentro de um índice de banco de dados? Então, agora você sabe que um índice de banco de dados é criado em uma coluna em uma tabela, e que o índice armazena os valores dessa coluna específica. Mas, é importante compreender que um índice de banco de dados não armazena os valores nas outras colunas da mesma tabela. Por exemplo, se se criar um índice na coluna Employee_Name, isto significa que os valores da coluna Employee_Age e Employee_Address não são também armazenadas no índice. Se fizéssemos apenas armazenar todas as outras colunas no índice, então seria apenas como a criação de uma outra cópia de toda a mesa - que iria ocupar maneira muito espaço e seria muito ineficiente.

Como é que um banco de dados de saber quando usar um índice? Quando uma consulta como “SELECT * FROM EMPREGADOS WHERE Employee_Name = 'ABC'” é executado, o banco de dados irá verificar para ver se há um índice na coluna (s) a ser consultado. Assumindo que a coluna Employee_Name tem um índice criado sobre ele, o banco de dados terá que decidir se ele realmente faz sentido usar o índice para encontrar os valores que estão sendo pesquisadas - porque existem alguns cenários onde é realmente menos eficiente usar o índice de banco de dados e mais eficiente apenas para verificar a tabela inteira.

Qual é o custo de ter um índice de banco de dados?

Ele ocupa espaço - e quanto maior sua tabela, maior o índice. Outro hit desempenho com índices é o fato de que sempre que você adicionar, excluir ou atualizar linhas na tabela correspondente, as mesmas operações terão de ser feito para o índice. Lembre-se que um índice precisa conter o mesmo até os dados minuto como tudo o que está na coluna (s) tabela que o índice de cobre.

Como regra geral, um índice só deve ser criado em uma tabela se os dados na coluna indexada será consultado com freqüência.

Veja também

  1. O que colunas geralmente fazem bons índices?
  2. Como índices de banco de dados funciona
Respondeu 13/08/2016 em 18:36
fonte usuário

votos
64

Clássico exemplo "Índice de Livros"

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

Simples, não?

Agora, sem uma página de índice, para encontrar uma seção especial que começa com a letra "S", você não tem nenhuma outra opção do que a digitalização através de todo o livro. ou seja: as páginas 1000

Mas com uma página de índice no início, você está lá. E mais, para ler qualquer seção específica que importa, você só precisa olhar sobre a página do índice, uma e outra vez, cada vez. Depois de encontrar o índice de correspondência você pode eficientemente saltar para a seção saltando outras seções.

Mas, em seguida, para além de 1000 páginas, você vai precisar de outro ~ 10 páginas para mostrar a página de índice, tão totalmente 1010 páginas.

Assim, o índice é uma secção separada que armazena valores de coluna indexada + ponteiro para a linha indexado numa ordem de classificação para look-ups eficientes.

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

Respondeu 23/04/2017 em 14:43
fonte usuário

votos
40

Simples !!!!!!!!!!

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

Exemplo, temos uma tabela de banco de dados chamado usuário com três colunas - nome, idade e endereço. Suponha que a tabela de usuário tem milhares de linhas.

Agora, vamos dizer que queremos executar uma consulta para encontrar todos os detalhes de todos os usuários que são nomeados 'John'. Se executar a seguinte consulta.

SELECT * FROM User 
WHERE Name = 'John'

O software de banco de dados que, literalmente, tem que olhar para cada linha na tabela de usuário para ver se o nome para essa linha é 'John'. Isso vai levar um longo tempo.
Isto é onde o índice nos ajuda "índice é usado para acelerar as consultas de pesquisa, essencialmente, reduzir 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 de valores da coluna (por exemplo, John) a partir de uma tabela, e que estes valores são armazenados numa estrutura de dados.
Portanto, agora o banco de dados irá usar o índice para encontrar funcionários chamado John porque o índice será presumivelmente ser classificados em ordem alfabética pelo nome Users. E, porque ele é classificado, isso significa procurar um nome é muito mais rápido, porque todos os nomes que começam com um “J” será bem próximas umas das outras no índice!

Respondeu 02/08/2016 em 01:30
fonte usuário

votos
18

Apenas uma sugestão rápida .. Como os custos de indexação você escreve adicionais e espaço de armazenamento, por isso, se sua aplicação requer uma operação mais insert / update, você pode querer usar tabelas sem índices, mas se exige mais operações de recuperação de dados, você deve ir para indexada mesa.

Respondeu 14/01/2015 em 06:44
fonte usuário

votos
14

Basta pensar Índice de banco de dados como índice de um livro. Se você tem um livro sobre cães e você quer encontrar uma informação sobre digamos, pastores alemães, você poderia naturalmente folhear todas as páginas do livro e encontrar o que você está procurando, mas isso, claro, é demorado e não muito velozes. Outra opção é que, você pode simplesmente ir para a seção Índice do livro e, em seguida, encontrar o que está procurando usando o nome da entidade que procura (neste caso, os pastores alemães) e também olhando para o número da página para encontrar rapidamente o que você está procurando. No banco de dados, o número da página é referido como um ponteiro que dirige o banco de dados para o endereço no disco onde entidade está localizada. Usando a mesma analogia pastor alemão, poderíamos ter algo como isto ( “pastor alemão”,

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

Respondeu 21/12/2016 em 17:16
fonte usuário

votos
7

índice SQL é algo relacionado para acelerar a busca no banco de dados SQL. Índice permite programador para recuperar dados do banco de dados muito rápido. Suponha que você é um estudante ou um leitor do livro. Seu livro contém 50.000 páginas. Primeiro dia você ler algum tema “ABC” dia seguinte você quiser ler algum outro tópico “xyz”. você nunca vai passar manualmente através de página por página. O que você vai fazer nesta situação é a utilização de índice de livro de olhar a algum tema específico e, em seguida, Ir diretamente para o seu tópico. Índice salvou seus lotes de tempo para procurar tópico. Mesmo no índice de SQL, Índice permite procurar milhões de discos muito rapidamente a partir do banco de dados.

Respondeu 15/02/2018 em 10:17
fonte usuário

votos
2

Um índice de base de dados é uma estrutura de dados que aumenta a velocidade das operações de recuperação de dados de uma tabela de base de dados à custa de operações de escrita e espaço de armazenamento, para manter a estrutura de dados de índice. Os índices são utilizados para localizar dados rapidamente sem ter que procurar cada linha em uma tabela de banco de dados cada vez que uma tabela de banco de dados é acessado. Os índices podem ser criados usando uma ou mais colunas de uma tabela de banco de dados, fornecendo a base para ambas as pesquisas aleatórias rápidas e um acesso eficiente de registros ordenados.

Respondeu 09/07/2018 em 05:33
fonte usuário

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more