Início / dba / Perguntas / 87317
Accepted
Justin
Asked: 2015-01-01 03:49:58 +0800 CST

Usando exibições indexadas para agregações - bom demais para ser verdade?

  • 772

Temos um data warehouse com uma contagem de registros bastante grande (10 a 20 milhões de linhas) e geralmente executamos consultas que contam registros entre determinadas datas ou contam registros com determinados sinalizadores, por exemplo

SELECT
    f.IsFoo,
    COUNT(*) AS WidgetCount
FROM Widgets AS w
JOIN Flags AS f
    ON f.FlagId = w.FlagId
WHERE w.Date >= @startDate
GROUP BY f.IsFoo

O desempenho não é terrível, mas pode ser relativamente lento (talvez 10 segundos em um cache frio).

Recentemente, descobri que posso usar GROUP BYem exibições indexadas e tentei algo semelhante ao seguinte

CREATE VIEW TestView
WITH SCHEMABINDING
AS
    SELECT
        Date,
        FlagId,
        COUNT_BIG(*) AS WidgetCount
    FROM Widgets
    GROUP BY Date, FlagId;
GO

CREATE UNIQUE CLUSTERED INDEX PK_TestView ON TestView
(
    Date,
    FlagId
);

Como resultado, o desempenho da minha primeira consulta agora é < 100ms e a exibição e o índice resultantes são < 100k (embora nossa contagem de linhas seja grande, o intervalo de datas e IDs de sinalizador significa que essa exibição contém apenas 1.000 a 2.000 linhas).

Eu pensei que talvez isso prejudicasse o desempenho das gravações na tabela Widget, mas não - o desempenho das inserções e atualizações nesta tabela é praticamente inalterado, tanto quanto eu posso dizer (além disso, sendo um data warehouse, esta tabela é atualizada com pouca frequência de qualquer forma)

Para mim, isso parece bom demais para ser verdade - é? Com o que preciso ter cuidado ao usar exibições indexadas dessa maneira?

sql-server index

2 respostas

  1. Best Answer

    Como você notou, a visão em si materializa apenas um pequeno número de linhas - portanto, mesmo que você atualize a tabela inteira, a E/S adicional envolvida na atualização da visão é insignificante. Você provavelmente já sentiu a maior dor que vai sentir ao criar a visualização. O próximo próximo será se você adicionar um zilhão de linhas à tabela base com vários novos IDs que requerem novas linhas na exibição.

    Isso não é bom demais para ser verdade. Você está usando exibições indexadas exatamente como elas devem ser usadas - ou pelo menos uma das maneiras mais eficazes: pagar por futuras agregações de consulta no momento da gravação. Isso funciona melhor quando o resultado é muito menor que a origem e, claro, quando as agregações são solicitadas com mais frequência do que os dados subjacentes são atualizados (mais comum em DW do que em OLTP, geralmente).

    Infelizmente, muitas pessoas pensam que indexar uma exibição é mágico - um índice não tornará todas as exibições mais eficientes, especialmente exibições que simplesmente juntam tabelas e/ou produzem o mesmo número de linhas que a fonte (ou até multiplicam). Nesses casos, o I/O da exibição é o mesmo ou até pior do que a consulta original, não apenas porque existem as mesmas ou mais linhas, mas também porque geralmente armazenam e materializam mais colunas. Portanto, materializá-los antecipadamente não fornece nenhum ganho, pois - mesmo com SSDs - E/S, rede e processamento/renderização do cliente ainda permanecem os principais gargalos no retorno de grandes conjuntos de resultados para o cliente. A economia obtida ao evitar a junção em tempo de execução não é mensurável em comparação com todos os outros recursos que você ainda está usando.

    Assim como os índices não agrupados, apenas tome cuidado para não exagerar. Se você adicionar 10 exibições indexadas diferentes a uma tabela, verá mais impacto na parte de gravação de sua carga de trabalho, especialmente se a(s) coluna(s) de agrupamento não for(em) a chave de agrupamento.

    Puxa, eu tenho significado para o blog sobre este tema.

    • 29

  2. As respostas de Aaron cobriram bem essa questão. Duas coisas a acrescentar:

    1. Visualizações indexadas de agregação podem levar a conflitos e conflitos entre linhas. Normalmente, duas inserções não causam deadlock (exceto em condições bastante raras, como escalonamento de bloqueio ou colisões de hash de bloqueio). Mas, se ambas as inserções abordarem o mesmo grupo na exibição, elas competirão. O mesmo ponto representa qualquer outra coisa que aceite bloqueios (DML, dicas de bloqueio).
    2. Visualizações indexadas que não agregam também podem ser úteis. Eles permitem que você indexe colunas de várias tabelas. Dessa forma, você pode filtrar com eficiência uma tabela e ordenar por uma coluna de uma tabela unida. Esse padrão pode converter a junção de tabela completa em pequenas consultas de tempo constante.

    Eu usei a agregação e as exibições de junção com extremo benefício.

    Em suma, o seu caso de uso parece um caso perfeito. Visualizações indexadas são uma técnica muito subutilizada.

    • 22

relate perguntas