Início / dba / Perguntas / 222987
Accepted
McNets
Asked: 2018-11-21 07:42:34 +0800 CST

Por que o otimizador escolheria Índice agrupado + classificação em vez de índice não agrupado?

  • 772

Dado o seguinte exemplo:

IF OBJECT_ID('dbo.my_table') IS NOT NULL
    DROP TABLE [dbo].[my_table];
GO

CREATE TABLE [dbo].[my_table]
(
    [id]    int IDENTITY (1,1)  NOT NULL PRIMARY KEY,
    [foo]   int                 NULL,
    [bar]   int                 NULL,
    [nki]   int                 NOT NULL
);
GO

/* Insert some random data */
INSERT INTO [dbo].[my_table] (foo, bar, nki)
SELECT TOP (100000)
    ABS(CHECKSUM(NewId())) % 14,
    ABS(CHECKSUM(NewId())) % 20,
    n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
FROM 
    sys.all_objects AS s1 
CROSS JOIN 
    sys.all_objects AS s2
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_my_table]
    ON [dbo].[my_table] ([nki] ASC);
GO

Se eu buscar todos os registros ordenados por [nki](índice não clusterizado):

SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table ORDER BY nki;
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 266 ms, elapsed time = 493 ms

O otimizador escolhe o índice clusterizado e, em seguida, aplica um algoritmo de classificação.

insira a descrição da imagem aqui

Execution plan

Mas se eu forçá-lo a usar o índice não clusterizado:

SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table WITH(INDEX(IX_my_TABLE));
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 311 ms, elapsed time = 188 ms

Em seguida, ele usa o índice não clusterizado com uma pesquisa de chave:

insira a descrição da imagem aqui

Execution plan

Obviamente, se o índice não clusterizado for transformado em um índice de cobertura:

CREATE UNIQUE NONCLUSTERED INDEX [IX_my_table]
    ON [dbo].[my_table] ([nki] ASC)
    INCLUDE (id, foo, bar);
GO

Em seguida, ele usa apenas este índice:

SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table ORDER BY nki;
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 32 ms, elapsed time = 106 ms

insira a descrição da imagem aqui

Execution plan

Pergunta

  • Por que o SQL Server usa o índice clusterizado mais um algoritmo de classificação em vez de usar um índice não clusterizado, mesmo que o tempo de execução seja 38% mais rápido no último caso?
sql-server sql-server-2012

3 respostas

  1. Se você comparar o número de leituras necessárias em 100.000 pesquisas com o que está envolvido em uma classificação, poderá obter rapidamente uma ideia sobre por que o Otimizador de consulta calcula que o CIX+Sort seria a melhor escolha.

    A execução do Lookup acaba sendo mais rápida porque as páginas que estão sendo lidas estão na memória (mesmo se você limpar o cache, você tem muitas linhas por página, então você está lendo as mesmas páginas repetidamente, mas com diferentes quantidades de fragmentação ou pressão de memória diferente de outra atividade, isso pode não ser o caso). Realmente não precisaria muito para que o CIX+Sort fosse mais rápido, mas o que você está vendo é porque o custo de uma leitura não leva em consideração o relativo baixo custo de acessar as mesmas páginas repetidamente.

    • 9
  2. Best Answer

    Por que o SQL Server usa o índice clusterizado mais um algoritmo de classificação em vez de usar um índice não clusterizado, mesmo que o tempo de execução seja 38% mais rápido no último caso?

    Como o SQL Server usa um otimizador baseado em custo com base em estatísticas, não em informações de tempo de execução.

    Durante o processo de estimativa de custo para essa consulta, ele realmente avalia o plano de pesquisa, mas estima que exigirá mais esforço. (Observe o "Custo estimado da subárvore" ao passar o mouse sobre SELECT no plano de execução). Isso também não é necessariamente uma suposição ruim - na minha máquina de teste, o plano de pesquisa leva 6X a CPU da classificação/varredura.

    Veja a resposta de Rob Farley sobre por que o SQL Server pode custar mais caro ao plano de pesquisa.

    • 9

  3. Resolvi me aprofundar um pouco nessa questão e descobri alguns documentos interessantes falando sobre como e quando usar ou talvez melhor, não (forçar o) uso de um índice não clusterizado.

    Como sugerido por comentários de John Eisbrener , um dos mais referenciados, inclusive em outros blogs, é este interessante artigo de Kimberly L. Tripp:

    mas não é o único, se você estiver interessado pode dar uma olhada nestas páginas:

    Como você pode ver, todos eles se movem em torno do conceito de Ponto de Virada .

    Citado no artigo de KL Tripp

    Qual é o ponto de inflexão?

    É o ponto em que o número de linhas retornadas " não é mais seletivo o suficiente ". O SQL Server opta por NÃO usar o índice não clusterizado para pesquisar as linhas de dados correspondentes e, em vez disso, executa uma verificação de tabela.

    Quando o SQL Server usa um índice não clusterizado em um heap, basicamente ele obtém uma lista de ponteiros para as páginas da tabela base. Em seguida, ele usa esses ponteiros para recuperar as linhas com uma série de operações chamadas Row ID Lookups (RID). Isso significa que, pelo menos, ele usará tantas leituras de página quanto o número de linhas retornadas e talvez mais. O processo é um pouco semelhante com um índice clusterizado como a tabela base, com o mesmo resultado: mais leituras.

    Mas, quando esse ponto de inflexão ocorre?

    Claro que como a maioria das coisas nesta vida, depende...

    Não sério, isso ocorre entre 25% e 33% do número de páginas na tabela, dependendo de quantas linhas por página. Mas há mais fatores que você deve considerar:

    Citado no artigo do ITPRoToday

    Outros fatores que afetam o ponto de inflexão Embora o custo das pesquisas RID seja o fator mais importante que afeta o ponto de inflexão, há vários outros fatores:

    • A E/S física é muito mais eficiente ao varrer um índice clusterizado. Os dados de índice clusterizados são colocados sequencialmente no disco em ordem de índice. Conseqüentemente, há muito pouco deslocamento lateral do cabeçote no disco, o que melhora o desempenho de E/S.
    • Quando o mecanismo de banco de dados está verificando um índice clusterizado, ele sabe que há uma alta probabilidade de que as próximas páginas na trilha do disco ainda contenham os dados necessários. Então, ele começa a ler à frente em pedaços de 64 KB em vez das páginas normais de 8 KB. Isso também resulta em E/S mais rápida.

    Agora, se eu executar minhas consultas novamente usando estatísticas IO:

    SET STATISTICS IO ON;
SELECT id, foo, bar, nki FROM my_table WHERE nki < 20000 ORDER BY nki ;
SET STATISTICS IO OFF;

Logical reads: 312

SET STATISTICS IO ON;
SELECT id, foo, bar, nki FROM my_table WITH(INDEX(IX_my_TABLE));
SET STATISTICS IO OFF;

Logical reads: 41293

    A segunda consulta precisa de mais leituras lógicas do que a primeira.

    Devo evitar o índice não clusterizado?

    Não, um índice clusterizado pode ser útil, mas vale a pena dedicar algum tempo e fazer um esforço extra analisando o que você está tentando alcançar com ele.

    Citado no artigo de KL Tripp

    Então o que você deveria fazer? Depende. Se você conhece bem seus dados e faz alguns testes extensivos, pode considerar usar uma dica (há algumas coisas inteligentes que você pode fazer programaticamente em sps, tentarei dedicar um post a isso em breve). No entanto, uma escolha muito melhor (se possível) é considerar a cobertura (esse é realmente o meu ponto principal :)). Em minhas consultas, a cobertura não é realista porque minhas consultas querem todas as colunas (o malvado SELECT *), mas, se suas consultas forem mais restritas E forem de alta prioridade, é melhor usar um índice de cobertura (em muitos casos) em vez de uma dica porque um índice que cobre uma consulta, nunca dicas.

    Essa é a resposta para o quebra-cabeça por enquanto, mas definitivamente há muito mais para mergulhar. O Ponto de Virada pode ser uma coisa muito boa – e geralmente funciona bem. Mas, se você descobrir que pode forçar um índice e obter um melhor desempenho, talvez queira fazer algumas investigações e ver se é isso. Em seguida, considere a probabilidade de uma dica ajudar e agora você sabe onde pode se concentrar.

    • 4

relate perguntas