Qual a diferença entre clustered index e nonclustered index? – sql banco-de-dados

Pergunta:


Estudando sobre encontrei um exemplo, e sobre clustered diz:

Primary Keys por padrão usam um indice agrupado(clustered), ou seja, ao realizar uma consulta select * from myTable where IDColumn = 1 , o banco irá fazer uma busca binária para encontrar esse elemento, em vez de uma varredura linha a linha.

Gostaria de uma opinião mais detalhada sobre o assunto, se possível com planos de consulta ou com exemplos na prática.

  • Qual a diferença entre clustered index e nonclustered index?
  • Em que ocasiões devo usar um e outro?
  • Posso dizer que um índice agrupado é realizado uma consulta binária, e um índice não agrupado é usado uma consulta com Árvore B?

Nota: Meu inglês não é muito bom, a pergunta que linquei acima me ajudou um pouco, mas não muito. Achei bem interessante os planos de consulta que o banco faz, até então desconhecidos.

Autor da pergunta Marconi

Resposta Maniero:

Qual a diferença entre clustered index e nonclustered index?

O índice clusterizado é quase um sinônimo de chave primária. De fato só pode usar um por tabela e tem que ser na ordem da inserção dos dados, isto obviamente exclui chaves primárias naturais. Em um índice assim a chave é a posição onde o dado está. Na verdade ele é a própria tabela.

A chave primária está obviamente em ordem, assim possibilitando a pesquisa binária que é muito importante para dar performance.

O índice não clusterizado são todos os outros índices onde você terá uma chave qualquer e um apontador para a tabela de dados. Então há sempre duas pesquisas, uma no índice non-cluster e depois sabendo qual a posição dele dele vai buscar o dado de fato na tabela, que até pode ser um índice clusterizado.

Um índice não clusterizado tem as chaves em ordem também e pode fazer pesquisa binária da mesma forma.

Em que ocasiões devo usar um e outro?

Então não tem muito segredo na escolha. Chave primária quase sempre é o clusterizado, e não tem como outros índices serem clusterizados.

É importante notar que não é obrigatório o uso de índice clusterizado. Mesmo a chave primária pode usar uma chave normal que apontará para a tabela de dados.

Tem banco de dados (SQL Server) que permite o uso de chave natural clusterizada. O SQL Server nem exige que a chave primária seja única porque ele garante unicidade com algum custo extra. No SQL Server a única diferença entre ambos é a garantia da presença de todas colunas no cluterizado, ele é uma árvore binária comum. Há controvérsias se isto é um índice clusterizado. Minha teoria é que inicialmente era clusterizado, depois acharam melhor fazer a tabela ser ordenada de toda forma, mudaram o conceito mas não a terminologia. Por isso tem que tomar cuidado em se valer de detalhes de implementação para definir alguma coisa.

Como otimização um índice não clusterizado pode contar com algumas colunas extras se aquela pesquisa normalmente só precisa acessar algumas colunas específicas, eliminando a necessidade de busca na tabela de dados. Isso funciona em casos bem pensados. Nada impede de todos os índices terem todas colunas e assim qualquer query pode ser feita sem precisar ir na tabela de dados, mas do ponto de vista de espaço e de atualização dos dados isto é insano.

O mais comum é que os índices sejam armazenadas em alguma forma de estrutura de árvore binária (tem várias), mas isto não é obrigatório. O índice clusterizado pode usar um árvore simplificada ou até mesmo não ter uma árvore já que só pode fazer append na tabela e nunca mudará a ordem. Isto é uma detalhe de implementação.

Posso dizer que um índice agrupado é realizado uma consulta binária, e um índice não agrupado é usado uma consulta com Árvore B?

A consulta ser binária nada tem a ver com a estrutura dos dados, a não ser pelo fato que esta estrutura precisa obrigatoriamente classificada.

Uma estrutura de árvore é útil para facilitar as inserções em qualquer ordem ou de forma distribuída por vários pontos da estrutura possibilitando rapidez em todo tipo de operação.

Entenda que a criação do índice clusterizado não cria nenhuma estrutura extra, apenas estabelece a forma de armazenar a tabela.

Conclusão

Para uso geral acho que é suficiente saber isto. Detalhes só serão úteis para quem vai implementar um banco de dados ou que precisa entender os internals de um DB para algo muito fora da utilização normal.

Marconi, o exemplo que você cita trata especificamente de SQL Server.

Qual a diferença entre clustered index e nonclustered index?

A diferença básica entre índice clustered (agrupado) e nonclustered (não agrupado) é que, nos índices clustered, a estrutura do índice e os dados estão no mesmo arquivo; por isso o termo clustered (agrupado). São duas estruturas implementadas no mesmo arquivo. E, no caso dos índices nonclustered, estes não estão agrupados com os dados, isto é, estão em arquivos separados.

No SQL Server os índices (tanto clustered quanto nonclustered) são implementados utilizando árvore B+. A pesquisa direta (seek) é feita caminhando na árvore, até chegar a um nodo folha. No caso de leitura sequencial (scan), ocorre diretamente no nível folha, pois há uma lista duplamente encadeada nesse nível. Se o índice for do tipo clustered, os dados estão no nodo folha. Se for do tipo nonclustered, há um ponteiro (row locator) indicando onde estão os dados, na tabela.

Nos índices clustered as linhas são mantidas ordenadas logicamente. Para compreender a implementação, sugiro a leitura do item Table and Items Structures, páginas 188 a 197, do livro Inside Microsoft SQL Server 2008: T-SQL Querying, de Itzik Ben-Gan.


Em que ocasiões devo usar um e outro?

Para essa pergunta não há uma resposta única, pois depende do contexto e, principalmente, é necessário compreender os conceitos de chave natural, chave primária, chave substituta etc. Essa conceituação você encontra no artigo Primary Key Primer for SQL Server, de Phil Factor.

Conforme o artigo anteriormente mencionado, chave primária e índice clustered são coisas diferentes. Uma chave primária é uma construção lógica e um índice clustered é um índice com uma implementação física especial. Ao definir índice clustered para uma chave, você determina a forma como a chave é implementada.

E o autor salienta ainda que a escolha do índice clustered pode ter um forte impacto na performance. A chave candidata que faz sentido como uma chave primária pode não ter as características que são requeridas para um índice clustered com bom desempenho. Um bom índice clustered é leve e fácil de realizar comparações com ele. Uma boa chave primária nem sempre é assim.

Embora por padrão (default) no SQL Server as chaves primárias sejam implementadas utilizando índice do tipo clustered, isto não é obrigatório. Pode-se implementar chave primária usando índice nonclustered. Ou mesmo nem ter índice para a chave primária, mas somente uma declaração de unicidade (unique).

A escolha de índice clustered ou nonclustered depende do contexto. As seguintes tarefas compõem a estratégia recomendada para criação de índices:

  • Entenda as características do banco de dados;
  • Entenda as características das consultas mais usadas;
  • Entenda as características das colunas usadas nas consultas;
  • Determine quais opções de índice poderiam aumentar o desempenho na
    criação ou manutenção do índice;
  • Determine o melhor local de armazenamento para o índice.

Posso dizer que um índice agrupado é realizado uma consulta binária, e um índice não agrupado é usado uma consulta com Árvore B?

Pela resposta inicial, já sabe que não há nenhuma relação.


Conforme Guia de criação de índice do SQL Server, um índice é uma estrutura em disco associada a uma tabela, que agiliza a recuperação das linhas. Um índice contém chaves criadas de uma ou mais colunas da tabela. Essas chaves são armazenadas em uma estrutura (árvore B) que habilita o SQL Server a localizar a linha ou as linhas associadas aos valores de chave de forma rápida e eficaz.

No documento Índices clusterizados e não clusterizados descritos consta que índices agrupados classificam e armazenam as linhas de dados da tabela com base em seus valores de chave.


Fonte

Related Posts:

Qual a diferença entre AppCompatActivity e Activity? – android android-activity
Pergunta: Qual a diferença da AppCompatActivity para Activity ? A partir de qual versão a AppCompatActivity foi adicionada ao Android? Autor da pergunta Luhhh A diferença reside ...
Como abreviar palavras em PHP? – php string
Pergunta: Possuo informações comuns como nome de pessoas e endereços, e preciso que elas contenham no máximo 30 caracteres sem cortar palavras. Exemplo: 'Avenida Natalino João Brescansin' ...
Qual é a finalidade de um parêntese vazio numa declaração Lambda? – c# expressões-lambda característica-linguagem
Pergunta: Criei um exemplo de uma declaração Lambda sem argumentos, entretanto, estou com duvidas referente a omissão do parêntese vazio () na declaração. Veja o exemplo: class ...
Boas práticas para URI em API RESTful – api rest restful
Pergunta: Estou com dúvida em relação às URIs de alguns recursos da api que estou desenvolvendo. Tenho os recursos projetos e atividades com relação 1-N, ...
Dúvidas sobre a integração do MySQL com Java – java mysql netbeans
Pergunta: Estou criando um sistema no NetBeans, utilizando a linguagem Java e o banco de dados MySQL. Escrevi o seguinte código para realizar a conexão ...
Qual é a finalidade da pasta Model do framework Inphinit? – php inphinit
Pergunta: No Inphinit micro-framework existe a pasta Model que fica dentro da pasta application, e nela é onde ficam as classes, mas eu estou muito ...
Uso do ‘@’ em variáveis – javascript typescript coffeescript
Pergunta: Vejo em algumas linguagens que compilam para javascript, como TypeScript e CoffeeScript, o uso do @ em variáveis, como também, casos em que o ...
Qual tamanho máximo um arquivo JSON pode ter? – json arquivo
Pergunta: Vou dar um exemplo para conseguir explicar minha duvida: Preciso recuperar informação de imagens vindas de uma API, esse banco de imagens me retorna JSON's ...
O que é Teste de Regressão? – terminologia engenharia-de-software testes
Pergunta: Na matéria de Teste de Software o professor abordou um termo chamado Teste de Regressão, isto dentro da disciplina de teste de software. Sendo ...
O que é um construtor da linguagem? – php característica-linguagem
Pergunta: Em PHP, já li e ouvi várias vezes a respeito dos Construtores da Linguagem. Os casos que sempre ouvi falar deles foi em casos ...
Função intrínseca para converter numérico para string – cobol
Pergunta: Estou a tentar saber se existe alguma função intrínseca do COBOL para converter um data numérico para string sem precisar usar a cláusula REDEFINES: ( ...
Porque usar implements? – java android
Pergunta: Qual a diferença entre usar btn.setOnClickListener(new OnClickListener() { e public class MainActivity extends Activity implements OnClickListener{ Estive fazendo um curso de Android e meu professor falou que ...
O que é XHTML e quando deve ser usado? – html xml xhtml
Pergunta: O que eu sei é que o XHTML precisa ser XML válido. Isso implica, por exemplo, que todas as tags precisam ser fechadas. Por ...
Uma placa aceleradora de vídeo pode melhorar o desempenho não-gráfico? [fechada] – desempenho
Pergunta: Para desenvolver em Ruby on Rails, eu utilizo aqui uma máquina virtual do VirtualBox com Ubuntu Server 14.04 sem interface gráfica instalada. Recentemente descobri uma ...
Concat() VS Union() – c# .net
Pergunta: Qual a diferença entre Concat() e Union() ? Quando usar Concat() e quando usar Union() ? Somente pode ser usado em list ? ...

Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios marcados com *