Início / dba / Perguntas / 343106
Accepted
J. Mini
Asked: 2024-10-20 07:34:23 +0800 CST

Considerando que exigem bloqueios de modificação de esquema, por que colunas esparsas são consideradas uma alternativa ao EAV?

  • 772

Pro SQL Server Relational Database Design and Implementation: Best Practices for Scalability and Performance é uma fonte muito confiável de boas ideias de design de banco de dados. Como alternativa ao Entity-Attribute-Value, ele sugere usar SQL dinâmico para permitir que seus usuários adicionem novas colunas esparsas a uma tabela.

Para mim, essa ideia fede. Adicionar novas colunas requer um bloqueio de modificação de esquema. Esse é um bloqueio muito sério e eu preferiria que meus usuários não tivessem a capacidade de obtê-lo.

Existe alguma propriedade de colunas esparsas que faz com que permitir que os usuários assumam um bloqueio tão sério não seja tão terrível quanto seria para outros tipos de coluna?

sql-server

2 respostas

  1. Best Answer

    Embora louvável, sua preocupação em adicionar e remover colunas é, em grande parte, equivocada.

    Adicionar uma coluna anulável é uma operação somente de metadados: ou seja, ela só envolve pequenas alterações na definição da tabela do banco de dados em tabelas internas e não requer a reescrita dos índices clusterizados e todos os índices não clusterizados. Isso significa que tal modificação é muito rápida. Da mesma forma, remover uma coluna também é somente de metadados, pois o mecanismo de armazenamento simplesmente ignorará esses dados até que cada página seja eventualmente substituída sem eles.

    Você pode ver isso em ação neste violino . SET STATISTICS IO ONé usado para mostrar leituras da própria tabela, e você pode ver que isso só acontece na adição de uma coluna não anulável.

    Suas únicas preocupações reais devem ser:

    • Possíveis cadeias de bloqueio.
      Por exemplo, uma longa execução SELECTmantém um Sch-Sbloqueio na tabela. O ALTERtenta pegar um Sch-Mbloqueio e espera. Todas as outras SELECTconsultas de modificação então se acumulam atrás dele, esperando por seu próprio Sch-Sbloqueio que eles podem pegar.
      Infelizmente, isso não pode ser evitado usando WAIT_AT_LOW_PRIORITY, pois isso ainda não foi implementado para esse tipo de ALTER. Sua melhor aposta é colocar o seguinte antes do seu ALTER: 
      SET LOCK_TIMEOUT 10000;  -- milliseconds
    • Todas as visualizações e procedimentos que acessam esta tabela precisarão que seus metadados sejam atualizados. Você pode usar o seguinte script, que obtém todos esses objetos e os atualiza:
    DECLARE @sql nvarchar(max) = (
    SELECT STRING_AGG(N'
EXEC sp_refreshsqlmodule N''' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ''';',
      '')
    FROM sys.objects o
    JOIN sys.schemas s ON s.schema_id = o.schema_id
    WHERE o.object_id IN (
        SELECT ed.referencing_id
        FROM sys.sql_expression_dependencies ed
        WHERE ed.referenced_id = OBJECT_ID(@yourTable)
    )
      AND o.type IN ('P', 'V', 'FN', 'IF', 'TF', 'TR')
);

PRINT @sql;

EXEC sp_executesql @sql;
    • 2

  2. Para mim, essa ideia fede. Adicionar novas colunas requer um bloqueio de modificação de esquema. Esse é um bloqueio muito sério e eu preferiria que meus usuários não tivessem a capacidade de obtê-lo.

    Por que não? (Claro que posso listar um monte de razões teóricas, mas...) Independentemente se seus usuários fizerem uma alteração de esquema ou se você, como desenvolvedor, fizer essas mesmas alterações, um bloqueio de modificação de esquema será necessário. A maioria dos casos de uso em que os usuários finais têm esse acesso estão em um mundo limitado, onde eles estariam apenas atirando em si mesmos ou em sua equipe no pé. Em um ambiente multilocatário, não deve haver cruzamento, e os locatários devem ser separados de qualquer maneira.

    Outras medidas de design de aplicativos podem ser implementadas para minimizar os danos causados ​​pelos usuários finais que também se dão socos no rosto, como execução atrasada das alterações de esquema (por exemplo, uma fila agendada para executar o SQL gerado fora do horário comercial) e limitar o acesso a essa capacidade para usuários privilegiados de administrador do aplicativo.

    Existe alguma propriedade de colunas esparsas que faz com que permitir que os usuários assumam um bloqueio tão sério não seja tão terrível quanto seria para outros tipos de coluna?

    Novamente, não acredito nisso, e a solução deve ser uma solução arquitetônica. Além do que mencionei acima, outras escolhas de design que podem mitigar o risco disso são ter uma quantidade predefinida de colunas de vários tipos de dados que são mapeadas individualmente quando o usuário final precisa de uma coluna personalizada, ou ter uma tabela separada, por exemplo, ObjectNameExtendedonde novas colunas são adicionadas dinamicamente pelos usuários para que o bloqueio de modificação do esquema afete apenas as personalizações e não o aplicativo nativo, pelo menos.

    Além disso, como Erik Darling apontou, as colunas esparsas têm um conjunto de limitações que podem ser bem ruins, como impedir que você compacte quaisquer tabelas/índices dos quais elas fazem parte:

    Colunas esparsas são incompatíveis com a compactação de dados. Portanto, colunas esparsas não podem ser adicionadas a tabelas compactadas, nem tabelas contendo colunas esparsas podem ser compactadas.

    Além disso, de um anúncio de manutenção nesta rede:

    O recurso de colunas esparsas nos impede de adicionar novas colunas não nulas com um valor padrão como uma alteração on-line, somente de metadados.

    • 1

relate perguntas