que - tipos de indices sql server




Como funciona a indexação de banco de dados? (6)

Descrição simples!

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

Exemplo: Temos uma tabela de banco de dados chamada User com três colunas - Name , Age e Address . Suponha que a tabela User tenha milhares de linhas.

Agora, digamos que queremos executar uma consulta para encontrar todos os detalhes de qualquer usuário chamado 'John'. Se executarmos a seguinte consulta:

SELECT * FROM User 
WHERE Name = 'John'

O software do banco de dados literalmente precisaria examinar todas as linhas da tabela User para ver se o Name dessa linha é 'John'. Isso levará muito tempo.

É aqui que o index nos ajuda: o índice é usado para acelerar as consultas de pesquisa, reduzindo essencialmente 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 index consiste em valores de coluna (por exemplo: John) de uma tabela e esses valores são armazenados em uma estrutura de dados .

Portanto, agora o banco de dados usará o índice para encontrar funcionários chamados John, porque o índice provavelmente será classificado em ordem alfabética pelo nome de Usuários. E, por ser classificado, significa que a busca por um nome é muito mais rápida, pois todos os nomes que começam com um "J" estarão próximos um do outro no índice!

Dado que a indexação é tão importante quanto o tamanho do seu conjunto de dados, alguém pode explicar como a indexação funciona em um nível independente de banco de dados?

Para obter informações sobre consultas para indexar um campo, consulte Como indexar uma coluna do banco de dados .


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

Desde então, obtive algumas dicas sobre a desvantagem da criação de índices: se você escreve em uma tabela ( UPDATE ou INSERT ) com um índice, na verdade você tem duas operações de gravação no sistema de arquivos. Um para os dados da tabela e outro para os dados do índice (e o recurso dos mesmos (e - se agrupado - o recurso dos dados da tabela)). Se tabela e í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, terminaria 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 de tempo. Isso requer a definição de grupos de arquivos adicionais com os arquivos correspondentes nos discos rígidos desejados e a definição do local da tabela / índice conforme desejado.

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

Em certos cenários, um heap é mais útil 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 agrupados e não agrupados é bastante importante.

Ajudou-me: - O que realmente significam índices agrupados e não agrupados?


Apenas uma sugestão rápida. Como a indexação custa mais gravações e espaço de armazenamento, por isso, se seu aplicativo exigir mais operações de inserção / atualização, convém usar tabelas sem índices, mas se exigir mais operações de recuperação de dados, você deve procurar indexadas. tabela.


Exemplo clássico "Índice nos livros"

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

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 senão digitalizar o livro inteiro. ie: 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, basta examinar a página de índice, sempre e sempre. Depois de encontrar o índice correspondente, você pode pular eficientemente para a seção pulando outras seções.

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

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

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


Um índice é apenas uma estrutura de dados que agiliza a pesquisa de uma coluna específica em um banco de dados. Essa estrutura geralmente é uma árvore b ou uma tabela de hash, mas pode ser qualquer outra estrutura lógica.


Por que é 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 ​​por inteiro, tornando-os a operação de acesso a 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 não classificado exige uma Pesquisa Linear que requer acessos de bloco N/2 (em média), em que N é o número de blocos que a tabela abrange. Se esse campo for um campo não-chave (ou seja, não contém entradas exclusivas), todo o espaço de tabela deve ser pesquisado em N blocos de acesso.

Enquanto que com um campo classificado, pode ser usada uma Pesquisa Binária, que possui acesso ao bloco log2N. 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 vários 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 ele 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 requerem espaço adicional no disco, uma vez 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 vários campos da mesma tabela forem indexados. .

Como funciona?

Primeiro, vamos descrever um esquema de tabela de banco de dados de amostra;

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 usado no lugar de varchar para permitir um tamanho exato no valor do disco. Este banco de dados de amostra contém cinco milhões de linhas e não é indexado. O desempenho de várias consultas agora será analisado. Trata-se de uma consulta usando o ID (um campo de chave classificada) e uma usando o firstName (um campo não classificado sem chave).

Exemplo 1 - campos classificados versus não classificados

Dado nosso banco de dados de amostra de r = 5,000,000 registros de tamanho fixo, fornecendo um comprimento de registro de R = 204 bytes, 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 de identificação exigiria uma média de N/2 = 500,000 acessos de bloco para encontrar um valor, dado que o campo de identificação é um campo-chave. Porém, como o campo id também é classificado, uma pesquisa binária pode ser realizada, exigindo uma média de acessos log2 1000000 = 19.93 = 20 blocos. Instantaneamente, podemos ver que isso é uma melhoria drástica.

Agora, o campo firstName não é classificado nem é um campo-chave, portanto, uma pesquisa binária é impossível, nem os valores são exclusivos e, portanto, a tabela exigirá uma pesquisa até o final para obter N = 1,000,000 acessos de bloco exatos. É essa 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 será menor que o registro de vários campos para o qual aponta. Portanto, o próprio índice requer menos blocos de disco que a tabela original, o que requer menos acessos de bloco para iterar. O esquema para um índice no campo firstName é descrito abaixo;

Field name       Data type      Size on disk
firstName        Char(50)       50 bytes
(record pointer) Special        4 bytes

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

Exemplo 2 - indexação

Dado o nosso banco de dados 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. Isso permite uma pesquisa binária do índice com uma média de log2 277778 = 18.08 = 19 acessos de bloco. Para localizar o endereço do registro real, que exige um acesso adicional ao bloco para leitura, elevando o total para 19 + 1 = 20 acessos ao bloco, muito distante dos 1.000.000 acessos ao 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 a mais do exemplo acima, um aumento de ~ 28%) e que muitos índices podem causar problemas decorrentes dos limites de tamanho dos sistemas de arquivos, é necessário pensar cuidadosamente para selecionar a opção correta. campos para indexar.

Como os índices são usados ​​apenas para acelerar a pesquisa de um campo correspondente nos registros, é lógico que os campos de indexação usados ​​apenas para saída seriam simplesmente um desperdício de espaço em disco e tempo de processamento ao executar uma operação de inserção ou exclusão e, portanto, Deveria ser evitado. Também dada a natureza de uma pesquisa binária, é importante a cardinalidade ou exclusividade dos dados. A indexação em um campo com 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 para uma classificação linear e o otimizador de consulta evitará o uso do índice se a cardinalidade for menor que 30% do número do registro, tornando o índice um desperdício de espaço.





database-indexes