Início / dba / Perguntas / 254877
Accepted
Artashes Khachatryan
Asked: 2019-12-06 03:20:54 +0800 CST

SQL Server escolhe índice não seletivo

  • 772

Eu estava testando índices do SQL Server e encontrei um comportamento muito estranho. Aqui está o meu código:

DROP TABLE IF EXISTS  dbo._Test
DROP TABLE IF EXISTS  dbo._Newtest
GO
CREATE TABLE _Test(
ID INT NOT NULL, 
UserSystemID INT NOT NULL, 
Age INT
)
GO
INSERT INTO dbo._Test
        ( ID, UserSystemID, Age )

SELECT TOP 10000000 ABS(CHECKSUM(NEWID())) % 5000000, ABS(CHECKSUM(NEWID())) % 2, ABS(CHECKSUM(NEWID())) % 100
FROM sys.all_columns
CROSS JOIN sys.all_objects a
CROSS JOIN sys.all_objects b
CROSS JOIN sys.all_objects c

; WITH cte AS (
SELECT ID, UserSystemID,  age, ROW_NUMBER() OVER(PARTITION BY ID, UserSystemID ORDER BY GETDATE()) rn
FROM dbo._Test
)

SELECT cte.ID ,
       cte.UserSystemID ,
       cte.Age
INTO _newTest
FROM cte
WHERE cte.rn = 1

CREATE UNIQUE NONCLUSTERED INDEX  IX_test ON dbo._NewTest(ID, UserSystemID) INCLUDE(age)
GO
ALTER TABLE dbo._NewTest ADD CONSTRAINT PK_NewTest PRIMARY KEY CLUSTERED(UserSystemID, ID)
GO

Neste ponto, tenho dois índices na mesma tabela e nas mesmas colunas. O primeiro é não clusterizado e o segundo é clusterizado. A Idcoluna é mais seletiva (cerca de 5.000.000 valores únicos) e UserSystemIDnão (dois valores únicos).

Em seguida, executo a seguinte consulta para testar qual índice é usado:

SELECT id,  UserSystemID, age   
FROM _NewTest
WHERE id = 1502945
AND UserSystemID = 1

Ele busca o índice clusterizado. Você pode ver o plano aqui .

A questão é por que o SQL Server prefere o índice clusterizado em vez do índice exclusivo não clusterizado.

Minha coluna principal de índice clusterizado é muito menos seletiva do que o outro índice exclusivo não clusterizado. Portanto, espero que o desempenho seja pior com o índice clusterizado, mas na prática não é.

sql-server index

1 respostas

  1. Best Answer

    Dados os índices exclusivos, sua consulta selecionará no máximo uma linha.

    O otimizador sabe que precisará descer o índice b-tree apenas uma vez e não precisará varrer para frente ou para trás a partir desse ponto para encontrar mais correspondências. Isso é conhecido como busca singleton (teste de igualdade em um índice único).

    A implementação de correspondência de índice atual sempre escolhe o índice clusterizado quando pode usar uma busca singleton.

    A escolha entre índice clusterizado e não clusterizado aqui geralmente não é muito importante. Pode haver um pequeno custo extra à medida que os níveis superiores da b-tree são navegados (usando busca binária ou interpolação linear), mas isso seria um desafio até mesmo para medir. Lembre-se que apenas os componentes IDe UserSystemIDchave estão presentes em páginas de índice não folha.

    Pode-se argumentar que as páginas de folha de índice clusterizado mais amplas são menos propensas a estar na memória, em média. Existem algumas outras consequências de casos extremos, mas não vejo esse comportamento sendo alterado tão cedo.

    Mas minha coluna principal de índice clusterizado é muito menos seletiva do que o outro índice exclusivo não clusterizado. Portanto, espero que o desempenho seja pior com o índice clusterizado, mas na prática não é.

    A seletividade não importa para a busca de igualdade em um índice composto de árvore b.

    Seu índice composto clusterizado exclusivo tem chaves (UserSystemID, id).

    Para localizar uma linha com (UserSystemID = 1 e id = 1502945), o SQL Server não encontra todas as linhas em que UserSystemID = 1 e, em seguida, localiza as linhas em que id = 1502945. Isso seria muito ineficiente.

    Você pode dizer quantas páginas sua consulta de teste toca usando SET STATISTICS IO ON. Seu exemplo cria um índice clusterizado com dois níveis não-folha. No total, encontrar a linha desejada significa tocar em três páginas - uma em cada nível do índice.

    As linhas são ordenadas no índice por UserSystemID e id. Minha cópia da sua tabela de demonstração tem o seguinte layout na página raiz (nível superior) do índice clusterizado:

    página raiz

    Realizar uma pesquisa binária nesta página é fácil:

    • Comece na linha do meio.
    • Compare o UserSystemID com o que você está procurando.
      • Se não for igual, continue a pesquisa binária da maneira usual (escolha um novo ponto médio nas linhas anteriores ou posteriores, conforme apropriado).
      • Se igual em UserSystemID, compare o id com o que você está procurando e continue a pesquisa binária

    Seguindo essa lógica, encontraremos rapidamente a página de índice filho (próximo nível inferior) em que as chaves pesquisadas serão encontradas, se estiverem presentes. Repita a pesquisa binária nessa página e assim por diante até chegarmos à única página em nível de folha que deve conter a linha que estamos procurando, se ela existir.

    • 11

relate perguntas