none
Explicacão de índices RRS feed

  • Pergunta

  •          Bom dia pessoal, estou entrando no SQL já faz alguns dias e estou parado no assunto índices. A idéia que eu tenho de índices é que ajuda na hora de fazer consultas melhorando a perfomance da busca. Agora tenho várias dúvidas do que são e a diferença de um índice: Cluster, Noncluster, sei que índices Full-Text são feitas para buscas mais específicas podendo colocar palavras, tenho dúvida também o que é Popular um índice Full-Text e quais os tipos, só responderem quaisquer uma das questões ficarei grato, obrigado.
    quinta-feira, 6 de outubro de 2011 13:42

Respostas

  • Tiago,

    Esta é uma imagem clássica da estrutura de um índice. (Fonte: http://msdn.microsoft.com/en-us/library/ms177443.aspx)

    O primeiro nível é o Root Level ou nível raiz. Depois temos os níveis intermediários até chegar nos níveis folha.

    Um índice CLUSTER realiza uma ordenação física na tabela, sendo que o nível folha vai conter os dados da tabela na ordenação escolhida. É por esta razão que você só pode ter um índice CLUSTER por tabela. Outra observação, num índice CLUSTER, no nível folha, você tem TODOS OS DADOS DA TABELA. (Guarde esta informação, vai ser útil logo mais...)

    Já o índice NÃO CLUSTER trabalha um pouco diferente. Ele cria seus índices ordenados também mas a grande diferença está no nível folha. O índice NÃO CLUSTER só possui as informações da chave do índice no nível folha. Para que ele localize outras informações da tabela ele precisa de uma chave adicional. Esta chave pode ser uma chave de um índice CLUSTER (como o Luan citou). Mas e se a tabela não tiver nenhum índice CLUSTER??? Neste caso, chamamos nossa tabela de HEAP e o nosso índice NÃO CLUSTER vai conter uma chave adicional chamada RID, ou RowID, que é um identificador único para cada registro da nossa tabela.

    Quanto à utilização de índices, considere que no geral sempre farão bem para consultas, mas reafirmo o que foi dito pelo Luan, não adianta sair criando vários índices pois isso vai ter um preço a ser pago. Toda vez que um índice CLUSTER for alterado, ou a tabela tiver registros inseridos e/ou deletados, todos os índices NÃO CLUSTER tem que ser atualizados. Você vai perceber que seus INSERTS, DELETES e UPDATES vão demorar mais aparentemente sem explicação...

    Bem, é isto. Espero ter complementado as informações fornecidas pelo Luan e ter te ajudado.

    Bons Estudos!

     

    []'s

     

     


    Erickson Ricci
    Microsoft MCP, MCTS SQL Server 2005, 2008
    Visite o meu blog: http://ericksonricci.wordpress.com
    Me siga no twitter: @EricksonRicci
    LinkedIN: http://br.linkedin.com/in/ericksonricci
    e-mail: ericksonfabricio@gmail.com
    **Ajude a melhorar o sistema de busca do fórum.Marque a(s) resposta(s) que foram úteis**
    **Se esta resposta solucionou a questão, então, por favor, marque-a como resposta.**
    • Marcado como Resposta Tiago A sexta-feira, 7 de outubro de 2011 17:46
    quinta-feira, 6 de outubro de 2011 23:24
  •  


    Tiago A, bom dia!
     

    Vamos lá FULL TEXT... 

    É um serviço integrado do SQL Sever que permite buscas avançadas em consulta que utilizam mais comumente o LIKE. 

    Essas pesquisas pode ser realizadas por verbos, arquivos, sinonimos e outras formas. 

    Para esse essa solução temos um conjunto de componentes que são

    Term = Frase, Conjunto de letras....

    Full-Text Catalog = Grupo contendo Full-Text Indexes

    Full-Text Index = Esse grupo contem colunas indexadas

    Stemmer = Verbos Conjugais

    Theasurus = Um XML que define sinonimos

    StopWord = Palavras como A, The....

    Population = População das palavras dentro do Full text indes

     

    Ou seja nesse momento temos a estrutura como

     

    Full-Text Catalog contendo Full-Text Indexes

    Sendo assim temos que realizar a criação de um Catalog e depois disso sim criar os indexes

    Lembrando que você não pode criar essa estrutura em banco de dados de sistema.

     

    Criando um full-text catalog

     

    USE DatabaseName

    go           

    CREATE FULLTEXT CATALOG NomeFULLTextCatalog

    AS DEFAULT

     

    Depois da estrutura criada,  algumas precauções tem que ser verificadas.

     

    Só pode conter um Full-Text index por tabela

    A tabela tem que conter uma UNIQUE KEY e não pode permitir null

    E tem que ter um fulltext-catalog para isso.

    Depois dessa verificação você está apto para realizar a criação do FULL-TEXT Index

     

    CREATE FULLTEXT INDEX ON NomeTabela (NomeCampo)

    KEY INDEX UNIQUEKEYNOME

    ON NomeFULLTextCatalog

    WITH STOPLIST = SYSTEM, CHANGE_TRACKING AUTO;

     

     

    Pronto agora você pode utilizar consultas tendo com busca o CONTAINS, CONTAINSTABLE, FREETEXT e FRETEXTTABLE, aqui segue alguns exemplos...

     

    CREATE FULLTEXT CATALOG ftc_Person_Address

    AS DEFAULT

     

    CREATE FULLTEXT INDEX ON Person.Address (AddressLine1)

    KEY INDEX PK_Address_AddressID

    ON ftc_Person_Address

    WITH STOPLIST = SYSTEM, CHANGE_TRACKING AUTO;

     

    --Verbo

    SELECT A.AddressID, A.AddressLine1, A.City, A.PostalCode

    FROM AdventureWorks.Person.Address AS A

    WHERE CONTAINS(AddressLine1,'FORMSOF(INFLECTIONAL, Boulevard)')

     

    --Sinónimos

    SELECT A.AddressID, A.AddressLine1, A.City, A.PostalCode

    FROM AdventureWorks.Person.Address AS A

    WHERE CONTAINS(AddressLine1,'FORMSOF(THESAURUS, Boulevard)')

     

    ----------------------------------------------------

    SELECT A.AddressID, A.AddressLine1, A.City, A.PostalCode

    FROM AdventureWorks.Person.Address AS A

    WHERE CONTAINS(A.AddressLine1,'Firestone')

     

    SELECT A.AddressID, A.AddressLine1, A.City, A.PostalCode

    FROM AdventureWorks.Person.Address AS A

    WHERE FREETEXT (AddressLine1,'1142 Firestone Dr.')

     

    SELECT DB_NAME(database_id) AS 'Database Name'

           , OBJECT_NAME(table_id) AS 'Table Name'

    FROM sys.dm_fts_index_population;

     

     

    Para popular o Full-Text Index você pode utlizar tres metodos durante a criação

     

    AUTO -  Quando ocorrer mudanças, o mesmo já será propagado

    MANUAL - utilizando a cláusula ALTER FULLTEXT INDEX

    OFF - Nenhuma alteração é realizada,


    Espero ter ajudado J

     

     

     

     


    sexta-feira, 7 de outubro de 2011 15:32

Todas as Respostas

  • Tiago A, bom dia!

     

    Vou tentar aqui te passar uma breve explicação de índices.

     

    Índice Cluster - Somente um por tabela, esse índice é criado em uma estrutura chamada B-Tree (Balanced Tree) ou seja Arvóre Balanceada.  (Esse índice geralmente funciona como chave de uma tabela).

    Índice Non-Cluster - A mesma estrutura do índice cluster, esse geralmente funciona para otimizar as consultas como nomes, datas e outras. Sendo assim quando esse índice é criado o mesmo tem que ter referencia com o índice cluster e essa referência se dá pela chave do índice cluster. Sendo assim todos os índíces non-cluster estão ligados ao índice-cluster. Quando por exemplo são realizados operações de DML (Data Manipulation Language) ou seja Insert, Update, Delete todos os índices non-cluster que possuem os campos selecionados seram tocados, ou seja ter uma quantidade alta de índices não quer dizer ter sempre muita eficiência para todas as operações.

     

    O índice é uma estrutura criada para otimizar consultas a sua base de dados. Não realize a criação de índices desordenada isso pode dar dor de cabeça depois.

     

    Full-Text Search - É uma estrutura separada, há toda uma engine responsável por essa feature, ela é usada normalmente para a otimização de campos como Nome, Descrição, campos de texto, aonde é necessário que seja realizado uma pesquisa por sinônimo, verbo, palavras específicas dentros dos campos e assim por diante. 

    Aqui tentei ajudar você somente com algumas informações bem básicas, agora para melhorar mais seus conhecimentos, veja essa séria criada no SQL Sever Central e não deixe de ver tambêm

    http://www.sqlservercentral.com/stairway/72399/

    http://gustavomaiaaguiar.wordpress.com

     

    Espero ter ajudado.

     

     

     


    Luan.Moreno [SQL Soul]|| Especialista SQL Server || MCTS SQL Server Admin e Dev @luansql
    quinta-feira, 6 de outubro de 2011 15:16
  • Tiago,

    Esta é uma imagem clássica da estrutura de um índice. (Fonte: http://msdn.microsoft.com/en-us/library/ms177443.aspx)

    O primeiro nível é o Root Level ou nível raiz. Depois temos os níveis intermediários até chegar nos níveis folha.

    Um índice CLUSTER realiza uma ordenação física na tabela, sendo que o nível folha vai conter os dados da tabela na ordenação escolhida. É por esta razão que você só pode ter um índice CLUSTER por tabela. Outra observação, num índice CLUSTER, no nível folha, você tem TODOS OS DADOS DA TABELA. (Guarde esta informação, vai ser útil logo mais...)

    Já o índice NÃO CLUSTER trabalha um pouco diferente. Ele cria seus índices ordenados também mas a grande diferença está no nível folha. O índice NÃO CLUSTER só possui as informações da chave do índice no nível folha. Para que ele localize outras informações da tabela ele precisa de uma chave adicional. Esta chave pode ser uma chave de um índice CLUSTER (como o Luan citou). Mas e se a tabela não tiver nenhum índice CLUSTER??? Neste caso, chamamos nossa tabela de HEAP e o nosso índice NÃO CLUSTER vai conter uma chave adicional chamada RID, ou RowID, que é um identificador único para cada registro da nossa tabela.

    Quanto à utilização de índices, considere que no geral sempre farão bem para consultas, mas reafirmo o que foi dito pelo Luan, não adianta sair criando vários índices pois isso vai ter um preço a ser pago. Toda vez que um índice CLUSTER for alterado, ou a tabela tiver registros inseridos e/ou deletados, todos os índices NÃO CLUSTER tem que ser atualizados. Você vai perceber que seus INSERTS, DELETES e UPDATES vão demorar mais aparentemente sem explicação...

    Bem, é isto. Espero ter complementado as informações fornecidas pelo Luan e ter te ajudado.

    Bons Estudos!

     

    []'s

     

     


    Erickson Ricci
    Microsoft MCP, MCTS SQL Server 2005, 2008
    Visite o meu blog: http://ericksonricci.wordpress.com
    Me siga no twitter: @EricksonRicci
    LinkedIN: http://br.linkedin.com/in/ericksonricci
    e-mail: ericksonfabricio@gmail.com
    **Ajude a melhorar o sistema de busca do fórum.Marque a(s) resposta(s) que foram úteis**
    **Se esta resposta solucionou a questão, então, por favor, marque-a como resposta.**
    • Marcado como Resposta Tiago A sexta-feira, 7 de outubro de 2011 17:46
    quinta-feira, 6 de outubro de 2011 23:24
  • Muito obrigado Luan e Erickson! porém ainda tenho dúvidas sobre Índice Full-Text e o que seria/como popular um Full-Text? valeu!

    sexta-feira, 7 de outubro de 2011 13:46
  •  


    Tiago A, bom dia!
     

    Vamos lá FULL TEXT... 

    É um serviço integrado do SQL Sever que permite buscas avançadas em consulta que utilizam mais comumente o LIKE. 

    Essas pesquisas pode ser realizadas por verbos, arquivos, sinonimos e outras formas. 

    Para esse essa solução temos um conjunto de componentes que são

    Term = Frase, Conjunto de letras....

    Full-Text Catalog = Grupo contendo Full-Text Indexes

    Full-Text Index = Esse grupo contem colunas indexadas

    Stemmer = Verbos Conjugais

    Theasurus = Um XML que define sinonimos

    StopWord = Palavras como A, The....

    Population = População das palavras dentro do Full text indes

     

    Ou seja nesse momento temos a estrutura como

     

    Full-Text Catalog contendo Full-Text Indexes

    Sendo assim temos que realizar a criação de um Catalog e depois disso sim criar os indexes

    Lembrando que você não pode criar essa estrutura em banco de dados de sistema.

     

    Criando um full-text catalog

     

    USE DatabaseName

    go           

    CREATE FULLTEXT CATALOG NomeFULLTextCatalog

    AS DEFAULT

     

    Depois da estrutura criada,  algumas precauções tem que ser verificadas.

     

    Só pode conter um Full-Text index por tabela

    A tabela tem que conter uma UNIQUE KEY e não pode permitir null

    E tem que ter um fulltext-catalog para isso.

    Depois dessa verificação você está apto para realizar a criação do FULL-TEXT Index

     

    CREATE FULLTEXT INDEX ON NomeTabela (NomeCampo)

    KEY INDEX UNIQUEKEYNOME

    ON NomeFULLTextCatalog

    WITH STOPLIST = SYSTEM, CHANGE_TRACKING AUTO;

     

     

    Pronto agora você pode utilizar consultas tendo com busca o CONTAINS, CONTAINSTABLE, FREETEXT e FRETEXTTABLE, aqui segue alguns exemplos...

     

    CREATE FULLTEXT CATALOG ftc_Person_Address

    AS DEFAULT

     

    CREATE FULLTEXT INDEX ON Person.Address (AddressLine1)

    KEY INDEX PK_Address_AddressID

    ON ftc_Person_Address

    WITH STOPLIST = SYSTEM, CHANGE_TRACKING AUTO;

     

    --Verbo

    SELECT A.AddressID, A.AddressLine1, A.City, A.PostalCode

    FROM AdventureWorks.Person.Address AS A

    WHERE CONTAINS(AddressLine1,'FORMSOF(INFLECTIONAL, Boulevard)')

     

    --Sinónimos

    SELECT A.AddressID, A.AddressLine1, A.City, A.PostalCode

    FROM AdventureWorks.Person.Address AS A

    WHERE CONTAINS(AddressLine1,'FORMSOF(THESAURUS, Boulevard)')

     

    ----------------------------------------------------

    SELECT A.AddressID, A.AddressLine1, A.City, A.PostalCode

    FROM AdventureWorks.Person.Address AS A

    WHERE CONTAINS(A.AddressLine1,'Firestone')

     

    SELECT A.AddressID, A.AddressLine1, A.City, A.PostalCode

    FROM AdventureWorks.Person.Address AS A

    WHERE FREETEXT (AddressLine1,'1142 Firestone Dr.')

     

    SELECT DB_NAME(database_id) AS 'Database Name'

           , OBJECT_NAME(table_id) AS 'Table Name'

    FROM sys.dm_fts_index_population;

     

     

    Para popular o Full-Text Index você pode utlizar tres metodos durante a criação

     

    AUTO -  Quando ocorrer mudanças, o mesmo já será propagado

    MANUAL - utilizando a cláusula ALTER FULLTEXT INDEX

    OFF - Nenhuma alteração é realizada,


    Espero ter ajudado J

     

     

     

     


    sexta-feira, 7 de outubro de 2011 15:32
  • Ficou bem claro, obrigado.
    sexta-feira, 7 de outubro de 2011 17:45