Início / dba / Perguntas / 287535
Accepted
Caius Jard
Asked: 2021-03-24 06:18:02 +0800 CST

O SQLServer pode combinar implicitamente índices clusterizados e não clusterizados para consultas que atingem o NCI com um WHERE e o CI com um ORDERBY?

  • 772

Recentemente, fiz uma pergunta sobre a melhor estratégia de indexação para uma tabela que deve registrar eventos, em ordem, em relação a um GUID. O GUID é um incômodo, mas é inevitável porque é um FK para outro lugar.

Em termos das consultas que quero fazer, elas são essencialmente da formaSELECT TOP N SomeColumn FROM t WHERE GuidColumn = 'someguid' ORDER BY AutoIncInt DESC

Eu fiz algumas experiências e uma boa abordagem de compensação parece ser:

  • Crie a AutoIncIntcoluna na tabela como a chave de clustering exclusiva
  • Crie um índice não clusterizado não exclusivo em GuidColumneINCLUDE SomeColumn

No começo eu tinha o índice nonclustering em SomeGuid ASC, AutoIncInt DESC, pensando que ajudaria a ORDER BY AutoIncInt DESCparte da consulta mas o plano permaneceu o mesmo quando eu o removi - nenhuma operação de classificação foi necessária e a consulta apenas atingiu o índice NC, fez um top e um select; parece que o SQLServer é de alguma forma inteligente o suficiente para saber, mesmo quando AutoIncInt não é citado no índice de não clustering, há uma maneira de obter facilmente os dados em ordem / calcular facilmente quais últimas N linhas são necessárias para o TOP

Como esse mecanismo funciona na prática? O índice sem cluster tem uma ordem implícita graças à referência ao índice de cluster, de modo que o SQLS possa deduzir rápida e facilmente de quais nós de índice N eu quero a SomeColumn?

sql-server azure-sql-database

1 respostas

  1. Best Answer

    Cada linha de nó folha em um índice de árvore b do SQL Server identifica exclusivamente uma única linha na tabela por uma chave exclusiva. Em um índice não clusterizado, essa chave exclusiva consiste nas colunas de chave declaradas mais o localizador de linha. O localizador de linha é a chave do índice clusterizado (incluindo o exclusivo para quando o valor da chave do CI não é exclusivo). Com um heap (sem índice clusterizado na tabela), o localizador de linha é o local físico da linha (arquivo, página, slot).

    A DBCC PAGEsaída deste script de demonstração mostra ambas GuidColumne AutoIncIntcomo chaves de índice, embora AutoIncIntnão tenha sido especificado:.

    DROP TABLE IF EXISTS dbo.t;
CREATE TABLE dbo.t (
      AutoIncInt int IDENTITY NOT NULL CONSTRAINT pk_t PRIMARY KEY CLUSTERED
    , GuidColumn uniqueidentifier NOT NULL
    , SomeColumn char(5000) NOT NULL
)
CREATE INDEX idx ON t(GuidColumn) INCLUDE (SomeColumn);
INSERT INTO t (GuidColumn, SomeColumn) VALUES('00000000-0000-0000-0000-000000000001','3')
INSERT INTO t (GuidColumn, SomeColumn) VALUES('00000000-0000-0000-0000-000000000001','1')
INSERT INTO t (GuidColumn, SomeColumn) VALUES('00000000-0000-0000-0000-000000000001','2')

DECLARE @dbid int, @fileid int, @pageid int;
SELECT TOP (1) 
      @dbid = DB_ID()
    , @fileid = allocated_page_file_id
    , @pageid = allocated_page_page_id
FROM sys.dm_db_database_page_allocations( DB_ID(), OBJECT_ID(N'dbo.t','U'), 2, 1, 'DETAILED')
WHERE 
    page_type_DESC = 'INDEX_PAGE'
ORDER BY 
      allocated_page_file_id
    , allocated_page_page_id;

DBCC PAGE(@dbid,@fileid,@pageid,3);
GO

    Resultados:

    +--------+--------+-----+-------+--------------------------------------+------------------+------------+----------------+----------+
| FileId | PageId | Row | Level |           GuidColumn (key)           | AutoIncInt (key) | SomeColumn |  KeyHashValue  | Row Size |
+--------+--------+-----+-------+--------------------------------------+------------------+------------+----------------+----------+
|      1 |    424 |   0 |     0 | 00000000-0000-0000-0000-000000000001 |                1 |          3 | (63f9557c63d5) |     5038 |
+--------+--------+-----+-------+--------------------------------------+------------------+------------+----------------+----------+

    Essa arquitetura de índice tem implicações de desempenho positivas para sua consulta porque a AutoIncIntcoluna não é apenas incluída implicitamente no índice não clusterizado como o localizador de linha, mas também faz parte da chave exclusiva e, portanto, ordenada. Isso permite que o plano de sua consulta use uma busca de índice para o GuidColumn WHEREpredicado mais uma varredura para trás para a ORDER BY...DESCcláusula.

    Abaixo está um trecho do plano de consulta real que mostra uma verificação inversa do índice. O índice não clusterizado resultante será o mesmo, independentemente de AutoIncIntestar explicitamente especificado como uma coluna de chave.

    <IndexScan Ordered="true" ScanDirection="BACKWARD"
    • 1

relate perguntas