Design de índices

Concluído

O SQL Server tem vários tipos de índice para dar suporte a diferentes tipos de cargas de trabalho. Em um alto nível, um índice pode ser considerado uma estrutura em disco associada a uma tabela ou exibição, que permite ao SQL Server encontrar mais facilmente a linha ou linhas associadas à chave de índice (que consiste em uma ou mais colunas na tabela ou exibição), em comparação à verificação de toda a tabela.

Índices cluster

Uma pergunta comum em entrevistas de emprego para DBA é pedir que o candidato explique a diferença entre um índice clusterizado e não clusterizado, pois os índices são uma tecnologia de armazenamento de dados fundamental no SQL Server. Um índice clusterizado é a tabela subjacente, armazenada em ordem de classificação com base no valor da chave. Pode haver somente um índice clusterizado em uma determinada tabela, pois as linhas podem ser armazenadas em uma ordem. Uma tabela sem um índice clusterizado é chamada de heap, e heaps normalmente são usados apenas como tabelas de preparo. Um princípio importante de design de desempenho é manter a chave de índice clusterizado o mais limitada possível. Ao considerar as colunas de chave para seu índice clusterizado, você deve considerar as colunas que são exclusivas ou que contêm muitos valores distintos. Outra propriedade de uma boa chave de índice clusterizado é para registros acessados sequencialmente e usados com frequência para classificar os dados recuperados da tabela. Ter o índice clusterizado na coluna usada para classificação pode impedir o custo de classificação toda vez que a consulta é executada, pois os dados já estarão armazenados na ordem desejada.

Observação

Quando dizemos que a tabela é 'armazenada' em uma ordem específica, estamos nos referindo à ordem lógica, não necessariamente à ordem física em disco. Os índices têm ponteiros entre páginas, que ajudam a criar a ordem lógica. Ao digitalizar um índice 'na ordem', o SQL Server segue os ponteiros de página para página. Imediatamente após a criação de um índice, é mais provável que ele também seja armazenado em ordem física no disco, porém, depois que você começar a fazer modificações nos dados e for preciso adicionar páginas ao índice, os ponteiros ainda fornecerão a ordem lógica correta, mas as novas páginas provavelmente não estarão mais na ordem do disco físico.

Índices não clusterizados

Os índices não clusterizados são uma estrutura separada das linhas de dados. O índice não clusterizado contém os valores de chave definidos para o índice e um ponteiro para a linha de dados que contém o valor de chave. É possível adicionar outra coluna que não é de chave ao nível folha do índice não clusterizado para cobrir mais colunas usando o recurso de colunas incluídas no SQL Server. Você pode criar vários índices não clusterizados em uma tabela.

Um exemplo de quando você precisa adicionar um índice ou adicionar colunas a um índice não clusterizado existente é mostrado abaixo:

O plano de consulta indica que, para cada linha recuperada usando a busca de índice, mais dados precisarão ser recuperados do índice clusterizado (a própria tabela). Há um índice não clusterizado, mas ele só inclui a coluna de produto. Se você adicionar as outras colunas na consulta a um índice não clusterizado, conforme mostrado abaixo, poderá ver uma alteração no plano de execução para eliminar a pesquisa de chave.

O índice criado acima é um exemplo de índice de cobertura. Além da coluna chave, você está incluindo nele colunas extras para cobrir a consulta e eliminar a necessidade de acessar a própria tabela.

Tanto índices não clusterizados quanto clusterizados podem ser definidos como exclusivos, o que significa que não pode haver duplicação dos valores de chave. Os índices exclusivos são criados automaticamente quando você cria uma restrição PRIMARY KEY ou UNIQUE em uma tabela.

O foco desta seção está nos índices da árvore b no SQL Server. Eles também são conhecidos como índices de armazenamento de linha. A estrutura geral de uma árvore b é mostrada abaixo:

Cada página em uma árvore b de índice é chamada de nó de índice e o nó superior da árvore b é chamado de nó raiz. Os nós inferiores em um índice são chamados de nós folha e a coleção de nós folha é o nível folha.

O design do índice é uma combinação de arte e ciência. Um índice estreito com poucas colunas em sua chave requer menos tempo para atualizar e tem menor sobrecarga de manutenção; no entanto, pode não ser útil para tantas consultas quanto um índice maior que inclua mais colunas. Talvez seja necessário experimentar várias abordagens de indexação com base nas colunas selecionadas pelas consultas do seu aplicativo. O otimizador de consulta geralmente escolhe o que considera o melhor índice existente para uma consulta. No entanto, isso não significa que não exista um índice melhor que possa ser criado.

A indexação correta de um banco de dados é uma tarefa complexa. Ao planejar seus índices para uma tabela, você deve manter alguns princípios básicos em mente:

• Entender as cargas de trabalho do sistema. Uma tabela usada principalmente para operações de inserção se beneficiará muito menos de índices extras do que uma tabela usada para operações de data warehouse que são compostas por 90% de atividades de leitura.
• Entender quais consultas são executadas com mais frequência e otimizar seus índices em relação a elas.
• Entender os tipos de dados das colunas usadas em suas consultas. Os índices são ideais para tipos de dados inteiros ou colunas exclusivas ou não nulas.
• Crie índices não clusterizados em colunas que são usadas com frequência em predicados e cláusulas de junção e mantenha esses índices o mais limitados possível para evitar sobrecarga.
• Entenda o tamanho/volume dos seus dados – Uma verificação em uma tabela pequena será uma operação relativamente barata e o SQL Server pode decidir fazê-la simplesmente porque ela é fácil (trivial). Uma verificação de tabela em uma tabela grande seria cara.

Outra opção que o SQL Server fornece é a criação de índices filtrados. Os índices filtrados são mais adequados para colunas em tabelas grandes em que uma grande porcentagem das linhas tem o mesmo valor nessa coluna. Um exemplo prático seria uma tabela de funcionários, conforme mostrado abaixo, que armazenasse os registros de todos os funcionários, inclusive daqueles que se desligaram ou se aposentaram.

CREATE TABLE [HumanResources].[Employee](
     [BusinessEntityID] [int] NOT NULL,
     [NationalIDNumber] [nvarchar](15) NOT NULL,
     [LoginID] [nvarchar](256) NOT NULL,
     [OrganizationNode] [hierarchyid] NULL,
     [OrganizationLevel] AS ([OrganizationNode].[GetLevel]()),
     [JobTitle] [nvarchar](50) NOT NULL,
     [BirthDate] [date] NOT NULL,
     [MaritalStatus] [nchar](1) NOT NULL,
     [Gender] [nchar](1) NOT NULL,
     [HireDate] [date] NOT NULL,
     [SalariedFlag] [bit] NOT NULL,
     [VacationHours] [smallint] NOT NULL,
     [SickLeaveHours] [smallint] NOT NULL,
     [CurrentFlag] [bit] NOT NULL,
     [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
     [ModifiedDate] [datetime] NOT NULL)

Nessa tabela, há a coluna CurrentFlag, que indica se um funcionário está empregado no momento. Este exemplo usa o tipo de dados de bit, que indica somente dois valores, um para empregado atualmente e zero para não empregado atualmente. Um índice filtrado com WHERE CurrentFlag = 1 na coluna CurrentFlag permitiria consultas eficientes dos funcionários atuais.

Você também pode criar índices em exibições, o que pode proporcionar ganhos de desempenho significativos quando as exibições contêm elementos de consulta como agregações e/ou junções de tabela.

Índices columnstore

O columnstore oferece desempenho aprimorado para consultas que executam grandes cargas de trabalho de agregação. Esse tipo de índice foi originalmente destinado a data warehouses, mas com o tempo os índices columnstore foram usados em muitas outras cargas de trabalho para ajudar a resolver problemas de desempenho de consulta em tabelas grandes. Doo SQL Server 2014 em diante, há índices columnstore não clusterizados e clusterizados. Assim como os índices de árvore b, um índice columnstore clusterizado é a própria tabela armazenada de modo especial e os índices columnstore não clusterizados são armazenados independentemente da tabela. Os índices columnstore clusterizados incluem inerentemente todas as colunas em uma determinada tabela. No entanto, ao contrário dos índices clusterizados do repositório de armazenamento, os índices columnstore clusterizados não são classificados.

Índices columnstore não clusterizados normalmente são usados em dois cenários e o primeiro é quando uma coluna na tabela tem um tipo de dados que não tem suporte em um índice columnstore. A maioria dos tipos de dados tem suporte, mas XML, CLR, sql_variant, ntext, text e imagens não têm suporte em um índice columnstore. Como um columnstore clusterizado sempre contém todas as colunas da tabela (porque ela é a tabela), um não clusterizado é a única opção. O segundo cenário é um índice filtrado. Esse cenário é usado em uma arquitetura chamada HTAP (processamento analítico transacional híbrido), em que os dados estão sendo carregados na tabela subjacente e, ao mesmo tempo, os relatórios estão sendo executados na tabela. Ao filtrar o índice (normalmente em um campo de data), esse design permite um bom desempenho de inserção e relatório.

Os índices columnstore são exclusivos em seu mecanismo de armazenamento, pois cada coluna no índice é armazenada de modo independente. Ele oferece um benefício duplo. Uma consulta usando um índice columnstore só precisa verificar as colunas necessárias para ser atendida, o que reduz o total de E/S executado e permite maior compactação, pois os dados na mesma coluna provavelmente serão de natureza semelhante.

Os índices columnstore têm melhor desempenho em consultas analíticas que examinam grandes quantidades de dados, como tabelas de fatos em um data warehouse. A partir do SQL Server 2016, é possível aumentar um índice columnstore com outro índice não clusterizado de árvore b, o que pode ser útil quando algumas de suas consultas fazem pesquisas em valores singleton.

Os índices columnstore também se beneficiam do modo de execução em lotes, que se refere ao processamento de um conjunto de linhas (geralmente em cerca de 900) por vez, em comparação ao mecanismo de banco de dados, que processa essas linhas uma vez. Em vez de carregar cada registro de modo independente e processá-los, o mecanismo de consulta computa o cálculo nesse grupo de 900 registros. Esse modelo de processamento reduz drasticamente o número de instruções da CPU.

SELECT SUM(Sales) FROM SalesAmount;

O modo em lote pode fornecer um aumento significativo de desempenho em relação ao processamento de linha tradicional. O SQL Server 2019 também inclui o modo de lote para dados de armazenamento. Embora o modo de lote para rowstore não tenha o mesmo nível de desempenho de leitura que um índice columnstore, as consultas analíticas podem ter uma melhoria de desempenho de até 5x.

O outro benefício que os índices columnstore oferecem para data warehouse cargas de trabalho é um caminho de carga otimizado para operações de inserção em massa de 102.400 linhas ou mais. Embora 102.400 seja o valor mínimo a ser carregado diretamente no columnstore, cada coleção de linhas, chamada de rowgroup, pode ter até aproximadamente 1.024.000 linhas. Ter menos rowgroups, porém, mais cheios, torna suas consultas SELECT mais eficientes, pois menos grupos de linhas precisam ser verificados para recuperar os registros solicitados. Essas cargas ocorrem na memória e são carregadas diretamente no índice. Para volumes menores, os dados são gravados em uma estrutura de árvore b chamada armazenamento delta e carregados de modo assíncrono no índice.

Neste exemplo, os mesmos dados são carregados em duas tabelas, FactResellerSales_CCI_Demo e FactResellerSales_Page_Demo. FactResellerSales_CCI_Demo tem um índice columnstore clusterizado e FactResellerSales_Page_Demo tem um índice de árvore b clusterizado com duas colunas e compactado por página. Como você pode ver, cada tabela está carregando 1.024.000 linhas da tabela FaceResellerSalesXL_CCI. Quando SET STATISTICS TIME é ON, o SQL Server acompanha o tempo decorrido da execução da consulta. O carregamento dos dados na tabela columnstore demorou aproximadamente oito segundos, já o carregamento na tabela compactada da página demorou cerca de vinte segundos. Neste exemplo, todas as linhas que vão para o índice columnstore são carregadas em um só rowgroup.

Se, em uma única operação, você carregar menos de 102.400 linhas de dados em um índice columnstore, ele será carregado na estrutura de árvore b conhecida como armazenamento delta. O mecanismo de banco de dados move esses dados para o índice columnstore usando um processo assíncrono chamado de motor de tupla. Ter armazenamentos delta abertos pode afetar o desempenho de suas consultas, pois a leitura desses registros é menos eficiente do que a leitura do columnstore. Também é possível reorganizar o índice com a opção COMPRESS_ALL_ROW_GROUPS para forçar os armazenamentos delta a serem adicionados e compactados nos índices columnstore.