Início / dba / Perguntas / 39866
Accepted
IvanP
Asked: 2013-04-13 11:39:36 +0800 CST

Como otimizo uma tabela grande para que as consultas que visam apenas dados recentes sejam executadas de maneira ideal?

  • 772

Então eu tenho essa mesa que está sempre crescendo. A maioria das consultas visa apenas dados recentes, digamos, com um mês de idade. Suponho que este seja um problema comum, mas não tenho ideia de como isso pode ser resolvido.

Estou aberto a mudança de design ou se existe mecanismo no MsSql para resolver isso. Tenho opções limitadas para tentar soluções diferentes, pois o banco de dados está em produção e é difícil de reproduzir.

CREATE TABLE [dbo].[mydata](
[ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Code] [varchar](20) NOT NULL,   -- index1 UNIQUE NONCLUSTERED INDEX
[Data2] [varchar](20) NULL,      
[Data3] [nvarchar](50) NOT NULL,
... bunch of DATA around 5kb
[Time_1] [datetime] NULL,    -- time created, -- index2 NONCLUSTERED INDEX
[Time_2] [datetime] NULL,    -- time finished ( usualy within few days ) -- index3 NONCLUSTERED INDEX
[Status] [int] NOT NULL,     -- active 
[Modid] [timestamp] NOT NULL
)
sql-server sql-server-2008

1 respostas

  1. Best Answer

    As séries temporais devem ser agrupadas por tempo:

    CREATE TABLE [dbo].[mydata](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Code] [varchar](20) NOT NULL,   -- index1 UNIQUE NONCLUSTERED INDEX
[Data2] [varchar](20) NULL,      
[Data3] [nvarchar](50) NOT NULL,
... bunch of DATA around 5kb
[Time_1] [datetime] NULL,    -- time created, 
[Time_2] [datetime] NULL,    -- time finished ( usualy within few days ) -- index3 NONCLUSTERED INDEX
[Status] [int] NOT NULL,     -- active 
[Modid] [timestamp] NOT NULL,
CONSTRAINT NONCLUSTERED PRIMARY KEY ([ID])
);

CREATE CLUSTERED INDEX cdxMyDataTime_1 on dbi.mydata (Time_1);

    Em dados de séries temporais, o tempo é quase sempre especificado em consultas e geralmente como um intervalo. Com uma chave agrupada com base no intervalo de tempo, as consultas verificarão apenas a parte relevante da tabela.

    O ID pode continuar a servir a função de chave primária lógica, mas há poucos benefícios em ter a tabela agrupada por ele, pois o ID nunca é usado como um intervalo. Então ele entra em uma restrição não agrupada. Pesquisas de singleton baseadas em ID precisarão de duas leituras, mas quem se importa, são duas leituras rápidas .

    Se você não pode ter o Time_1 como chave agrupada, um truque frequente usado é recuperar o intervalo de ID para cada dia, por exemplo. criar tabela de dias e min_ID/max_ID. Em seguida, use o intervalo de ID que cobre o intervalo de tempo em que você está interessado para restringir o intervalo da varredura na tabela. A vantagem dessa abordagem é que ela funciona para várias colunas de tempo (você não pode agrupar por Time_1 e Time_2...) e é menos invasiva (pode ser experimentada imediatamente sem modificar a tabela). Mas essa abordagem é muito invasiva no design da consulta do aplicativo, exige disciplina em lembrar de usar os intervalos de ID para os dias desejados. Observe que, como os IDs geralmente não mudam, eles podem ser armazenados em cache no aplicativo.

    Índices simples em Time_1 e Time_2 não funcionam porque atingem o ponto de inflexão do índice . O índice de cobertura (com colunas INCLUDE) em Time_1 e Time_2 explode o tamanho dos dados, pois frequentemente a coluna incluída necessária é ... todas as colunas.

    • 5

relate perguntas