Início / dba / Perguntas / 54636
Accepted
user31402
Asked: 2013-12-08 11:28:57 +0800 CST

As colunas de string com poucas entradas diferentes são compactadas automaticamente?

  • 772

Temos uma tabela que contém uma coluna com apenas uma dúzia de strings diferentes. Existe algo que eu precise fazer para que o banco de dados comprima essas informações? Não temos muito espaço em disco disponível e parece um desperdício armazenar todas essas sequências longas repetidamente.

Eu gostaria que o banco de dados armazenasse essas informações internamente de forma compactada. Por exemplo, os dados atuais se parecem com isto:

Column "TypeInfo"
---------------------------------------------------------
very_long_descriptor_someone_came_up_with_a_long_time_ago
this_desciptor_is_also_very_long
nobody_knows_why_this_descriptor_is_so_long
very_long_descriptor_someone_came_up_with_a_long_time_ago
very_long_descriptor_someone_came_up_with_a_long_time_ago
nobody_knows_why_this_descriptor_is_so_long

Eu gostaria de ver o SQL Server armazenar algum tipo de chave pequena que representasse aquelas strings mais longas que são repetidas:

Column "TypeInfo"
-----------------
1
2
3
1
1
3
(+ mapping information)
sql-server compression

1 respostas

  1. Best Answer

    Não, o SQL Server não faz nada assim automaticamente. Você pode conseguir um pouco do que procura com compactação de dados , mas sua maior granularidade está no nível da página. Portanto, você obterá uma boa compactação nas páginas de índice se tiver um índice apenas nesta coluna (ou pelo menos com esta coluna como a coluna principal), mas supondo que o índice clusterizado não esteja nesta coluna, você obterá muito menos se beneficia disso quando os dados e outras páginas de índice contêm muitas variações da string.

    A compactação de dados usa vários algoritmos, incluindo dicionário, que teria o melhor impacto se todos os valores em uma página fossem iguais. Mesmo na melhor das hipóteses, porém, isso não será extremamente valioso em uma tabela grande - imagine que você tenha um milhão de páginas e cada página tenha duas cópias de um desses valores. Claro, a compactação economiza algumas economias armazenando o valor apenas uma vez em vez de duas vezes em cada página (além de uma sobrecarga de ponteiro insignificante), mas o SQL Server ainda armazena um milhão de cópias - uma por página!

    O que sugiro, em vez de armazenar a mesma dúzia de strings repetidas vezes, é criar uma tabela de pesquisa com uma TINYINTchave, permitindo que você armazene cada string de descrição apenas uma vez, não importa quantas vezes ela seja usada. Você sempre pode recuperar a descrição no momento da consulta sem precisar armazená-la com os dados e pode até criar visualizações para tornar isso mais transparente para consultas, aplicativos e usuários. Essa também pode ser uma opção mais atraente se sua carga de trabalho for limitada à CPU; enquanto a compactação de dados economiza armazenamento e memória, há alguma sobrecarga de CPU envolvida na compactação e descompactação de cada página.

    Por exemplo:

    CREATE TABLE dbo.TypeInfo
(
  TypeInfoID TINYINT PRIMARY KEY,
  Description VARCHAR(64) NOT NULL UNIQUE
);

INSERT dbo.TypeInfo(TypeInfoID, Description) VALUES
  (1,'very_long_descriptor_someone_came_up_with_a_long_time_ago'),
  (2,'this_desciptor_is_also_very_long'),
  (3,'nobody_knows_why_this_descriptor_is_so_long');
-- ... 9 other values ...

    Agora você precisa adicionar a TypeInfoIDcoluna à tabela original:

    ALTER TABLE dbo.OtherTable 
  ADD TypeInfoID TINYINT;

    Então você pode atualizar os dados existentes da seguinte forma:

    UPDATE o
  SET TypeInfoID = t.TypeInfoID
  FROM dbo.OtherTable AS o
  INNER JOIN dbo.TypeInfo AS t
  ON o.TypeInfo = t.Description;

    ( E processe isso em lotes, se a tabela for grande e você quiser minimizar o impacto no log e nas operações bloqueadas .)

    Então você pode adicionar uma restrição de chave estrangeira:

    ALTER TABLE dbo.OtherTable 
  ADD CONSTRAINT fk_TypeInfo 
  FOREIGN KEY (TypeInfoID) REFERENCES dbo.TypeInfo(TypeInfoID);

    Depois que todos os dados forem verificados como corretos, você pode descartar a coluna (primeiro, remova quaisquer restrições e índices que a façam referência):

    ALTER TABLE dbo.OtherTable 
  DROP COLUMN TypeInfo;

    Em seguida, você pode criar uma visualização que suas consultas possam usar para manter a semântica igual:

    CREATE VIEW dbo.vOtherTable
AS
  SELECT /* o.columns */, TypeInfo = t.Description
    FROM dbo.OtherTable AS o
    INNER JOIN dbo.TypeInfo AS t
    ON o.TypeInfoID = t.TypeInfoID;

    (Ou, em alguns cenários, você pode até renomear a tabela e dar à exibição o nome que a tabela costumava ter, tornando-a completamente transparente.)

    E, finalmente, se você não quiser permitir NULLs nesta coluna e se estiver no SQL Server 2012 ou superior, poderá alterar a coluna NOT NULLsem incorrer em atualizações e registros:

    ALTER TABLE dbo.OtherTable 
  ALTER COLUMN TypeInfo TINYINT NOT NULL;

    Se você estiver em uma versão anterior, considere adicionar um CHECK CONSTRAINT:

    ALTER TABLE dbo.OtherTable 
  WITH NOCHECK ADD CONSTRAINT ck_TypeInfoNotNULL
  CHECK (TypeInfoID IS NOT NULL);

    Mas isso pode não ser o ideal, pois uma restrição não confiável pode afetar a capacidade do otimizador de usá-la...

    Acho que muito disso está muito além do escopo de sua pergunta original e você pode nem considerar nenhuma dessas soluções atraentes, mas, se o fizer, poderá fazer sua própria pesquisa e teste para garantir o método menos intrusivo de fazer esta mudança. Você pode começar com as seguintes perguntas aqui e no Stack Overflow:

    • 3

relate perguntas