Impacto da ordem da coluna no Composite Index SQL Server

Impacto da ordem da coluna no Composite Index SQL Server

Impacto da ordem da coluna no Composite Index SQL Server 1
cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


Neste artigo, exploraremos o Composite Index SQL Server e os principais impactos sobre os pedidos. Também veremos SQL
    Estatísticas de atualização do servidor para determinar um plano de execução otimizado do índice Compositivo.

Introdução

Os índices do SQL Server são um fator vital no desempenho da consulta e no desempenho geral do sistema. Usamos cluster e
    índices não agrupados com configurações diferentes. Você deve passar pela categoria de artigos SQLShack Indexes para se familiarizar com
    índices no SQL Server.

Eu listaria alguns artigos úteis para fins de referência:

Visão geral dos índices compostos SQL Server

Podemos usar colunas únicas ou múltiplas ao criar um índice no SQL Server.

  • Índice único: Ele usa uma única coluna na chave de índice
  • Índice composto: Se usarmos várias colunas no design do índice, ele será conhecido como Composite
            Índice

Vamos entender o índice composto do SQL Server usando um exemplo. Para esta demonstração, crie o [EmpolyeeData] tabela com o script abaixo:

Ele cria o índice principal de chaves em cluster no [EmpID] coluna. Podemos verificar o índice existente em uma tabela usando
    a sp_helpindex procedimento do sistema. É um índice único porque usa uma única coluna para o
    chave de índice em cluster.

procedimento do sistema sp_helpindex

Como podemos ter um índice em cluster em uma tabela SQL, vamos criar um índice não em cluster na [FirstName] e
[LastName] colunas:

É um índice não agrupado composto porque usamos várias colunas na definição de índice.

Exibir índice composto do SQL Server

Na definição de índice composto acima, definimos a chave da ordem como Nome, Sobrenome. Suponha que você criou o
    índice com a chave do pedido como Sobrenome, Nome.

As ordens das colunas fazem alguma diferença no índice composto? Vamos explorá-lo usando vários exemplos.

Leia Também  Como remover horários de datas no SQL Server

Exemplo 1: Use as colunas Nome e Sobrenome na cláusula Where em uma ordem semelhante de chaves de índice

Na consulta abaixo, usamos as colunas em que condição semelhante às chaves de ordem do índice. Antes de executar o
    consulta, pressione CTRL + M para ativar o plano de execução real no SSMS:

Ele usa o índice não clusterizado (índice composto) e o operador de busca de índice para filtrar os resultados.

Exibir plano de execução

No operador de busca de índice, podemos ver a operação de busca usando a mesma ordem de chave de índice.

operador de índice procura

Agora, mude para a guia Mensagens e você verá que foram usadas 4 leituras lógicas para toda a consulta.

leituras lógicas

Exemplo 2: Use as colunas Nome e Sobrenome na cláusula Where na ordem inversa das chaves de índice

Neste exemplo, revertemos a ordem das colunas na cláusula Where em comparação com as ordens de chave de índice. Dá o mesmo
    resultados, mas vamos ver o plano de execução real:

Vemos um operador de busca de índice semelhante usando o índice composto do SQL Server.

Ordem inversa das chaves de índice

Nenhuma diferença nas leituras lógicas também. Ambas as consultas usam as 4 leituras lógicas.

Exibir leituras lógicas

Nos predicados de busca de uma busca de índice, vemos isso usando a ordem das colunas semelhante à chave de índice composta.

Visualizar Predicados de Pesquisa

Exemplo 3: Use a primeira coluna-chave do índice composto do SQL Server na cláusula Where

Neste exemplo, queremos filtrar registros com base no [FirstName] coluna. É a primeira coluna da chave de índice definida
    no índice composto:

Ele ainda usa o mesmo plano de execução com um operador de busca de índice no índice composto do SQL Server.

primeira coluna-chave do índice composto do SQL Server

Ele recuperou 6 registros em comparação com 2 registros nos exemplos anteriores (1 e 2), mas ainda usando as 4 leituras lógicas
    para o resultado.

Não há diferenças, certo! Isso significa que você pode usar colunas em qualquer ordem no índice composto do SQL Server. Esperar. Vamos olhar
    no próximo exemplo antes de tomar a decisão.

Exibir informações de registros e leituras

Ele usa predicados de busca para o [FirstName] colunas e é a primeira coluna especificada no índice composto SQL
    Servidor.

Operador de busca de índice

Exemplo 4: use a segunda coluna de chave do índice composto do SQL Server na cláusula Where

Neste exemplo, queremos filtrar registros com base no [LastName] coluna. É a segunda coluna da chave de índice definida
    no índice composto:

Leia Também  Testes de segurança em torno de nosso modelo de negócios e riscos

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br

Ele está usando o mesmo índice composto, mas desta vez o Index procura é convertido na varredura de índice. Você pode saber que um
    a varredura de índice é um operador caro comparado com uma busca de índice na maioria dos casos.

Exibir plano de execução real

Vamos mudar para a guia Mensagens e ver o impacto dela. Ele recupera duas linhas, mas as leituras lógicas saltam para 3641
    em comparação com 4 leituras lógicas nos exemplos anteriores.

Ir em leituras lógicas

Não vemos predicados de busca de índice porque estava usando o operador de varredura de índice.

A busca de índice é convertida em Verificação de índice

Agora, execute todas as consultas juntas em uma única janela de consulta. Ele mostra o custo da consulta em comparação com outras consultas:

No plano de execução de consulta abaixo, podemos observar o seguinte:

  • A consulta que usa a primeira coluna [FirstName] na cláusula where é otimizada corretamente e causa um custo de 0%

  • A consulta que usa a segunda coluna [LastName] na cláusula where é mais cara, com 100% de custo comparativamente

Compare o custo da consulta

Entenda o impacto da ordem das colunas na sua consulta para obter um índice composto do SQL Server

O SQL Server usa as estatísticas disponíveis para determinar o plano de execução da consulta otimizada. Esse plano de consulta otimizado tem
    operadores como buscas e verificações de índice. Você deve passar pelas estatísticas do SQL Server e como executar o artigo Estatísticas de atualização no SQL para entender as estatísticas de atualização do SQL Server.

Expanda a tabela SQL [EmployeeData] e veja as estatísticas. Ele mostra as seguintes estatísticas.

Estatísticas de atualização do SQL Server

Estatísticas principais do SQL Server

O SQL Server o cria quando definimos uma coluna de chave primária. Clique com o botão direito do mouse na estatística da chave primária
    (PK__Employee__AF2DBB99D43A3022) e verifique se está usando a coluna de chave primária, ou seja, [EmpID] no meu exemplo

Estatísticas de atualização do SQL Server de chave primária

Estatísticas de chave composta para índice composto do SQL Server

Criamos um índice não agrupado com uma chave composta. SQL Server cria as estatísticas para este índice.

Para verificar, visualize também a captura de tela das colunas de estatísticas a seguir.

Principais estatísticas de atualização do SQL Server

Vamos entender melhor esta estatística. Clique na página Detalhes. Fornece informações sobre a densidade do índice e
    etapas do histograma.

Histograma

O SQL Server armazena a primeira coluna-chave do índice no histograma. Como você pode ver acima, etapas do histograma RANGE_HI_KEY mostrando valores do [FirstName] coluna.

O SQL Server usa o subconjunto baseado em esquerda para outras colunas-chave definidas no índice composto do SQL Server. Se nós fizermos
    não filtrar os resultados com base na primeira chave de índice, talvez não ajude o otimizador de consulta a escolher o índice adequado ou
    operador. Você pode ver uma varredura de índice em comparação com um operador de busca de índice, como vimos nos exemplos anteriores.

Principais estatísticas do SQL Server criadas automaticamente

Como sabemos agora que o SQL Server usa a primeira chave para o histograma. Nesse caso, filtramos os registros em [LastName]
coluna, para criar uma nova estatística para ele. Você pode ver as estatísticas criadas automaticamente a partir de _WA_sys. Podemos
    verifique na captura de tela abaixo que ele criou estatísticas para o [LastName] coluna.

Propriedades de estatísticas

Nos detalhes, você pode verificar se o histograma obtém dados dos valores da coluna Sobrenome.

Histograma de estatísticas

Agora, no plano de execução real, clique com o botão direito do mouse e clique em Mostrar XML do plano de execução. Te dá
    planejar em um formato XML. É relativamente complexo entender um plano XML. Neste plano, podemos ver essa consulta
    O otimizador decide criar as novas estatísticas para o [EmployeeData] tabela com base na coluna da cláusula where.

Na saída, ele recupera as colunas necessárias como resultado da operação de varredura de tabela.

Plano de execução XML

Conclusão

Neste artigo, exploramos o impacto das ordens de coluna no índice composto do SQL Server. Também vimos o
    Estatísticas de atualização do SQL Server para determinar o plano de execução otimizado. Depende da carga de trabalho e você precisa projetar suas chaves de índice, dependendo do requisito. Pode exigir testes em um ambiente inferior com simulação de carga de trabalho de produção.

Rajendra Gupta
Últimas mensagens de Rajendra Gupta (ver todos)

*As fotos exibidas neste post pertencem ao post www.sqlshack.com

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br