Como funciona a indexação de bases de dados?
Para informações sobre consultas para indexar um campo, confira Como é que indexo uma coluna de base de dados.
10 answers
Porque é necessário?
Quando os dados são armazenados em dispositivos de armazenamento baseados em disco, são armazenados como blocos de dados. Estes blocos são acessados em sua totalidade, tornando-os a operação de acesso ao disco atômico. Os blocos de disco são estruturados da mesma forma que as listas ligadas; ambos contêm uma seção para dados, um ponteiro para a localização do próximo nó (ou bloco), e ambos não precisam ser armazenados contiguosamente.
Devido ao facto de alguns registos só poderem ser ordenados em um campo, podemos afirmar que a busca em um campo que não está ordenado requer uma busca Linear que requer N/2 acessos em bloco (em média), onde N é o número de blocos que a tabela abrange. Se esse campo for um campo não-Chave (isto é, não contém entradas únicas), então todo o tabuleiro deve ser pesquisado em N acessos em bloco.
Considerando que com um campo ordenado, pode ser usada uma pesquisa binária, que tem acessos em bloco log2 N. Também uma vez que os dados são ordenados dado um não-Chave campo, o resto da tabela não precisa ser pesquisado por valores duplicados, uma vez que um valor mais elevado é encontrado. Assim, o aumento de desempenho é substancial.
O que é indexação?
Indexação é uma forma de classificar um número de registos em vários campos. Criar um índice em um campo em uma tabela cria outra estrutura de dados que detém o valor do campo, e um ponteiro para o registro a que se relaciona. Esta estrutura de índice é então ordenada, permitindo que as pesquisas Binárias sejam actuei nele.
A desvantagem da indexação é que estes índices requerem espaço adicional no disco, uma vez que os índices são armazenados em conjunto numa tabela usando o motor MyISAM, este ficheiro pode rapidamente atingir os limites de tamanho do sistema de ficheiros subjacente se muitos campos dentro da mesma tabela forem indexados.
Como funciona?
Em primeiro lugar, vamos delinear um esquema de tabela de base de dados de amostras;Field name Data type Size on disk id (Primary key) Unsigned INT 4 bytes firstName Char(50) 50 bytes lastName Char(50) 50 bytes emailAddress Char(100) 100 bytes
Nota : char foi utilizado EM substituição de varchar to permitir um tamanho preciso no valor do disco. Esta base de dados de amostras contém cinco milhões de linhas e não é indexada. O desempenho de várias consultas será agora analisado. Estas são uma consulta usando o id (um campo de chave ordenado) e uma usando o nome próprio (um campo não-chave não ordenado).
exemplo 1 - campos ordenados vs não ordenados
Dada a nossa base de dados de amostras de r = 5,000,000 registos de tamanho fixo que dão um comprimento de registo de R = 204 bytes e eles são armazenados em uma tabela usando o motor MyISAM que está usando o tamanho padrão do bloco 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 acessos em bloco N/2 = 500,000 para encontrar um valor, dado que o campo id é um campo chave. Mas uma vez que o campo id também está ordenado, uma busca binária pode ser realizada exigindo uma média de Acessos em bloco. Instantaneamente podemos ver que esta é uma melhoria drástica.
Agora o campo nome próprio não é ordenado nem um campo-chave, por isso uma pesquisa binária é impossível, nem os valores são únicos, e assim a tabela irá necessitar de procurar até ao fim para um EXACTO N = 1,000,000 acessos em bloco. É esta situação que a indexação pretende corrigir.
Dado que um registo de índice contém apenas o campo indexado e um indicador do registo original, é lógico que será menor do que o recorde multi-campo para o qual aponta. Assim, o índice em si requer menos blocos de disco do que a tabela original, o que requer, portanto, menos acessos de bloco para iterar através. O esquema para um índice no campo nome próprio é descrito abaixo;
Field name Data type Size on disk firstName Char(50) 50 bytes (record pointer) Special 4 bytes
Nota : Os ponteiros em MySQL têm 2, 3, 4 ou 5 bytes de comprimento, dependendo do tamanho da tabela.
Exemplo 2 - indexação
Dada a nossa base de dados de amostras de r = 5,000,000 registos com um comprimento de registo de índice de R = 54 bytes e usando o tamanho de bloco por omissão B = 1,024 bytes. O factor de bloqueio do índice seria bfr = (B/R) = 1024/54 = 18 registos 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 nome próprio pode utilizar o índice para aumentar o desempenho. Isto permite uma busca binária do índice com uma média de acessos em bloco log2 277778 = 18.08 = 19. Para encontrar o endereço do o registro real, que requer um outro acesso de bloco para ler, trazendo o total para 19 + 1 = 20 acessos de bloco, um longe dos 1.000.000 de acessos de bloco necessários para encontrar um nome próprio na tabela não-indexada.
Quando Deve ser utilizado?
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 demasiados índices podem causar problemas decorrentes dos limites de tamanho dos sistemas de ficheiros, um pensamento cuidadoso deve ser usado para selecionar os campos corretos para indexar.
Uma vez que os índices só são utilizados para acelerar a procura de um campo correspondente dentro dos registos, é lógico que os campos de indexação utilizados apenas para a saída seriam simplesmente um desperdício de espaço em disco e tempo de processamento ao efectuar uma operação de inserção ou eliminação, pelo que devem ser evitados. Também dada a natureza de uma busca binária, a cardinalidade ou singularidade dos dados é importante. Indexação num campo com um a cardinalidade de 2 dividiria os dados ao meio, enquanto uma cardinalidade de 1.000 retornaria aproximadamente 1.000 registros. Com uma cardinalidade tão baixa a eficácia é reduzida a uma ordenação linear, e o otimizador da consulta evitará usar o índice se a cardinalidade for inferior a 30% do número de registro, efetivamente fazendo do Índice um desperdício de espaço.
UPDATE ou INSERT) com um índice, tem de facto duas operações de escrita no sistema de ficheiros. Um para os dados da tabela e outro para os dados do índice (e o recurso a ele (e - se agrupado - o recurso dos dados da tabela)). Se a tabela e o índice estão localizados no mesmo disco rígido isto custa mais tempo. Assim, uma tabela sem um índice (um heap) , permitiria operações de escrita mais rápidas. (se você tivesse dois índices você acabaria com três operações de escrita, 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. Isto requer a definição de grupos de ficheiros adicionais com os ficheiros de acordo com os discos rígidos desejados e a definição da localização da tabela / índice como pretender.
Outro problema com os índices é a sua fragmentação ao longo do tempo à medida que os dados são inseridos. REORGANIZE ajuda, você deve escrever rotinas para fazer isso.
Em certos cenários, um monte é mais útil do que uma tabela com índices,
Por exemplo, se você tem muitas letras fascinantes, mas apenas uma leitura noturna fora do horário de trabalho para reportar. Além disso, uma diferenciação entre índices agrupados e não agrupados é bastante importante.Ajudou-me: - O que fazer Índice agrupado e não agrupado significa realmente?
Um índice é apenas uma estrutura de dados que torna a busca mais rápida por uma coluna específica numa base de dados. Esta estrutura é geralmente uma árvore b ou uma tabela de hash, mas pode ser qualquer outra estrutura lógica.
Para mais informações, recomendo: como funcionam os índices de bases de dados? E como é que os índices ajudam?
Exemplo clássico "índice em livros"
Considere um" livro " de 1000 páginas, dividido por 100 secções, cada secção com x páginas.
Simples, não é?Agora, sem uma página de índice, para encontrar uma secção em particular que começa com a letra "S", Você não tem outra opção a não ser digitalizar todo o livro. I. e: 1000 páginas
Mas com uma página de índice no início, você está lá. E mais, para ler qualquer seção em particular que importa, você só precisa olhar sobre a página do Índice, uma e outra vez, sempre. Depois de encontrar o índice correspondente, você pode saltar eficientemente para a seção saltando outras seções.Mas então, além de 1000 páginas, você vai precisar de mais ~10 páginas para mostrar a página de índice, então totalmente 1010 páginas.
Assim, o índice é uma secção separada que armazena os valores da coluna indexada + ponteiro para a linha indexada numa ordem ordenada de procura eficiente.
As coisas são simples nas escolas, não são? : PSELECT * FROM Employee
WHERE Employee_Name = 'Abc'
O que aconteceria sem um índice?
O software da Base de dados teria, literalmente, de olhar para cada linha da tabela de empregados para ver se o nome do empregado para essa linha é 'Abc'. E, porque queremos cada fila com o nome 'Abc' dentro dela, não podemos simplesmente parar de procurar uma vez que encontramos apenas uma fila com o nome 'Abc', porque pode haver outra 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 de ser examinadas pela base de dados para encontrar as linhas com o nome 'Abc'. Isto é o que se chama uma varredura de tabela completa
Como um índice de banco de dados pode ajudar o desempenho
O objectivo de se ter um índice é acelerar as consultas de pesquisa, reduzindo essencialmente o número de registos/linhas numa tabela que precisam de ser examinados. Um índice é uma estrutura de dados (mais comumente uma árvore B) que armazena os valores de uma coluna específica em uma tabela.
Como funciona o índice de árvores B?
A razão pela qual as árvores B são a estrutura de dados mais popular para os índices é devido ao fato de que elas são eficientes em termos de tempo - porque pesquisas, supressões e inserções podem ser feitas em tempo logarítmico. E, outra grande razão pela qual as árvores B são mais comumente usadas é porque os dados que são armazenados dentro da árvore B podem ser classificado. O RDBMS normalmente determina qual a estrutura de dados que é realmente utilizada para um índice. Mas, em alguns cenários com certas RDBMS, você pode realmente especificar que estrutura de dados você quer que seu banco de dados para usar quando você criar o próprio índice.
Como funciona um índice de tabela de 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. Assim, as consultas que se comparam para a igualdade com uma string podem recuperar os valores são muito rápidos se usarem um índice de hash.
Por exemplo, a consulta que discutimos anteriormente poderia beneficiar de um índice de hash criado na coluna emprego. A forma como um índice de hash funcionaria é que o valor da coluna será a chave na tabela de hash e o valor real mapeado a essa chave seria apenas um ponteiro para os dados da linha na tabela. Desde uma tabela de hash é basicamente uma matriz associativa, uma entrada típica seria algo como "Abc => 0x28939", onde 0x28939 é uma referência para a linha da mesa onde o Abc é armazenado na memória. Procurar um valor como " Abc "em um índice de tabela de hash e obter de volta uma referência à linha na memória é obviamente muito mais rápido do que digitalizar a tabela para encontrar todas as linhas com um valor de" Abc " na coluna emprego.
As desvantagens de um índice de hash
As 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ê quer descubra todos os funcionários com menos de 40 anos de idade. Como pudeste fazer isso com um índice de hash table? 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 está exactamente dentro de um índice de base 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 nessa coluna específica. Mas, é importante entender que uma base de dados o índice não armazena os valores nas outras colunas da mesma tabela. Por exemplo, se criarmos um índice na coluna Emprego, isto significa que os valores da coluna emprego e emprego não são também armazenados no índice. Se nós apenas armazenássemos todas as outras colunas no índice, então seria como criar outra cópia de toda a tabela – o que ocuparia muito espaço e seria muito ineficiente.
Como uma base de dados sabe quando usar um índice? Quando uma consulta como "SELECT * from Employee_name = 'Abc' " é executada, a base de dados irá verificar se existe um índice na(s) coluna (s) a ser questionada. Assumindo que a coluna emprego-nome tem um índice criado nela, a base de dados terá que decidir se realmente faz sentido usar o índice para encontrar os valores que estão sendo pesquisados-porque há alguns cenários onde é realmente menos eficiente usar o índice de banco de dados, e mais eficiente apenas para digitalizar o índice mesa inteira.
Qual é o custo de ter um índice de banco de dados?
Ocupa espaço – e quanto maior a sua mesa, maior o seu índice. Outro sucesso de 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 para o seu índice. Lembre-se que um índice precisa conter os mesmos dados até os minutos que o que está na(s) coluna (s) da tabela que o índice cobre.
Como um general regra, um índice só deve ser criado em uma tabela se os dados na coluna indexada forem questionados com freqüência.
Ver também
Descrição Simples!!!!!!!!!!
O índice não passa de uma estrutura de dados que guarda os valores de uma coluna específica numa tabela. Um índice é criado em uma coluna de uma tabela.
Exemplo, temos uma tabela de banco de dados chamada User com três colunas-Nome, idade e endereço. Suponha que a tabela de Usuários tem milhares de linhas.
Agora, vamos dizer que queremos executar uma consulta para encontrar todos os detalhes de qualquer usuário que se chama 'John'. Se executarmos a seguinte consulta.SELECT * FROM User
WHERE Name = 'John'
O software da base de dados teria literalmente de olhar para cada linha da tabela de utilizadores para ver se o nome dessa linha é 'John'. Isto vai demorar muito tempo.
É aqui que o index nos ajuda "o index é 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 coluna (P. ex.: John) de uma tabela, e que esses valores são armazenados em estrutura.
Então agora o banco de dados vai usar o índice para encontrar funcionários chamados John porque o índice presumivelmente será ordenado alfabeticamente pelo nome dos usuários. E, porque está ordenada, significa que 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!
Em resumo, um índice é uma estrutura de dados que armazena o valores para uma coluna específica de uma tabela de modo a acelerar a pesquisa da consulta.
O índice SQL é algo relacionado com a aceleração da pesquisa na Base de dados SQL. O Index permite que o programador Recupere dados do banco de dados muito rápido. Suponha que você é um estudante ou algum leitor de livros. O seu livro contém 50 mil páginas. Primeiro dia você leu algum tópico " ABC " no dia seguinte você quer ler um outro tópico "xyz". você nunca irá passar manualmente página a página. O que você vai fazer nesta situação é usar o Índice de livro para olhar o tópico específico e, em seguida, saltar diretamente para o seu tópico. Indice economizou muito tempo para pesquisar o tópico. O mesmo no SQL index, Index permite pesquisar milhões de registros muito rapidamente a partir de banco de dados.
Um índice de banco de dados é uma estrutura de dados que melhora a velocidade das operações de recuperação de dados em uma tabela de banco de dados ao custo de espaço adicional de escrita e armazenamento para manter a estrutura de dados de índice. Os índices são usados para localizar rapidamente os dados sem ter que pesquisar cada linha em uma tabela de banco de dados cada vez que uma tabela de banco de dados é acessada. Índices podem ser criados usando uma ou mais colunas de uma tabela de banco de dados, fornecendo a base para pesquisas rápidas aleatórias e acesso eficiente de Pedidos registro.