Início / dba / Perguntas / 50708
Accepted
Hannah Vernon
Asked: 2013-09-29 17:56:23 +0800 CST

As chaves naturais fornecem desempenho superior ou inferior no SQL Server do que chaves inteiras substitutas?

  • 772

Sou fã de chaves substitutas. Existe o risco de minhas descobertas serem tendenciosas de confirmação.

Muitas perguntas que vi aqui e em http://stackoverflow.com usam chaves naturais em vez de chaves substitutas com base em IDENTITY()valores.

Minha experiência em sistemas de computador me diz que executar qualquer operação comparativa em um número inteiro será mais rápido do que comparar strings.

Esse comentário me fez questionar minhas crenças, então pensei em criar um sistema para investigar minha tese de que inteiros são mais rápidos que strings para uso como chaves no SQL Server.

Como é provável que haja muito pouca diferença perceptível em pequenos conjuntos de dados, pensei imediatamente em uma configuração de duas tabelas em que a tabela primária tem 1.000.000 linhas e a tabela secundária tem 10 linhas para cada linha na tabela primária para um total de 10.000.000 linhas em a tabela secundária. A premissa do meu teste é criar dois conjuntos de tabelas como este, um usando chaves naturais e outro usando chaves inteiras, e executar testes de tempo em uma consulta simples como:

SELECT *
FROM Table1
    INNER JOIN Table2 ON Table1.Key = Table2.Key;

O seguinte é o código que criei como uma cama de teste:

USE Master;
IF (SELECT COUNT(database_id) FROM sys.databases d WHERE d.name = 'NaturalKeyTest') = 1
BEGIN
    ALTER DATABASE NaturalKeyTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE NaturalKeyTest;
END
GO
CREATE DATABASE NaturalKeyTest 
    ON (NAME = 'NaturalKeyTest', FILENAME = 
        'C:\SQLServer\Data\NaturalKeyTest.mdf', SIZE=8GB, FILEGROWTH=1GB) 
    LOG ON (NAME='NaturalKeyTestLog', FILENAME = 
        'C:\SQLServer\Logs\NaturalKeyTest.mdf', SIZE=256MB, FILEGROWTH=128MB);
GO
ALTER DATABASE NaturalKeyTest SET RECOVERY SIMPLE;
GO
USE NaturalKeyTest;
GO
CREATE VIEW GetRand
AS 
    SELECT RAND() AS RandomNumber;
GO
CREATE FUNCTION RandomString
(
    @StringLength INT
)
RETURNS NVARCHAR(max)
AS
BEGIN
    DECLARE @cnt INT = 0
    DECLARE @str NVARCHAR(MAX) = '';
    DECLARE @RandomNum FLOAT = 0;
    WHILE @cnt < @StringLength
    BEGIN
        SELECT @RandomNum = RandomNumber
        FROM GetRand;
        SET @str = @str + CAST(CHAR((@RandomNum * 64.) + 32) AS NVARCHAR(MAX)); 
        SET @cnt = @cnt + 1;
    END
    RETURN @str;
END;
GO
CREATE TABLE NaturalTable1
(
    NaturalTable1Key NVARCHAR(255) NOT NULL 
        CONSTRAINT PK_NaturalTable1 PRIMARY KEY CLUSTERED 
    , Table1TestData NVARCHAR(255) NOT NULL 
);
CREATE TABLE NaturalTable2
(
    NaturalTable2Key NVARCHAR(255) NOT NULL 
        CONSTRAINT PK_NaturalTable2 PRIMARY KEY CLUSTERED 
    , NaturalTable1Key NVARCHAR(255) NOT NULL 
        CONSTRAINT FK_NaturalTable2_NaturalTable1Key 
        FOREIGN KEY REFERENCES dbo.NaturalTable1 (NaturalTable1Key) 
        ON DELETE CASCADE ON UPDATE CASCADE
    , Table2TestData NVARCHAR(255) NOT NULL  
);
GO

/* insert 1,000,000 rows into NaturalTable1 */
INSERT INTO NaturalTable1 (NaturalTable1Key, Table1TestData) 
    VALUES (dbo.RandomString(25), dbo.RandomString(100));
GO 1000000 

/* insert 10,000,000 rows into NaturalTable2 */
INSERT INTO NaturalTable2 (NaturalTable2Key, NaturalTable1Key, Table2TestData)
SELECT dbo.RandomString(25), T1.NaturalTable1Key, dbo.RandomString(100)
FROM NaturalTable1 T1
GO 10 

CREATE TABLE IDTable1
(
    IDTable1Key INT NOT NULL CONSTRAINT PK_IDTable1 
    PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Table1TestData NVARCHAR(255) NOT NULL 
    CONSTRAINT DF_IDTable1_TestData DEFAULT dbo.RandomString(100)
);
CREATE TABLE IDTable2
(
    IDTable2Key INT NOT NULL CONSTRAINT PK_IDTable2 
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , IDTable1Key INT NOT NULL 
        CONSTRAINT FK_IDTable2_IDTable1Key FOREIGN KEY 
        REFERENCES dbo.IDTable1 (IDTable1Key) 
        ON DELETE CASCADE ON UPDATE CASCADE
    , Table2TestData NVARCHAR(255) NOT NULL 
        CONSTRAINT DF_IDTable2_TestData DEFAULT dbo.RandomString(100)
);
GO
INSERT INTO IDTable1 DEFAULT VALUES;
GO 1000000
INSERT INTO IDTable2 (IDTable1Key)
SELECT T1.IDTable1Key
FROM IDTable1 T1
GO 10

O código acima cria um banco de dados e 4 tabelas, e preenche as tabelas com dados, prontos para teste. O código de teste que executei é:

USE NaturalKeyTest;
GO
DECLARE @loops INT = 0;
DECLARE @MaxLoops INT = 10;
DECLARE @Results TABLE (
    FinishedAt DATETIME DEFAULT (GETDATE())
    , KeyType NVARCHAR(255)
    , ElapsedTime FLOAT
);
WHILE @loops < @MaxLoops
BEGIN
    DBCC FREEPROCCACHE;
    DBCC FREESESSIONCACHE;
    DBCC FREESYSTEMCACHE ('ALL');
    DBCC DROPCLEANBUFFERS;
    WAITFOR DELAY '00:00:05';
    DECLARE @start DATETIME = GETDATE();
    DECLARE @end DATETIME;
    DECLARE @count INT;
    SELECT @count = COUNT(*) 
    FROM dbo.NaturalTable1 T1
        INNER JOIN dbo.NaturalTable2 T2 ON T1.NaturalTable1Key = T2.NaturalTable1Key;
    SET @end = GETDATE();
    INSERT INTO @Results (KeyType, ElapsedTime)
    SELECT 'Natural PK' AS KeyType, CAST((@end - @start) AS FLOAT) AS ElapsedTime;

    DBCC FREEPROCCACHE;
    DBCC FREESESSIONCACHE;
    DBCC FREESYSTEMCACHE ('ALL');
    DBCC DROPCLEANBUFFERS;
    WAITFOR DELAY '00:00:05';
    SET @start = GETDATE();
    SELECT @count = COUNT(*) 
    FROM dbo.IDTable1 T1
        INNER JOIN dbo.IDTable2 T2 ON T1.IDTable1Key = T2.IDTable1Key;
    SET @end = GETDATE();
    INSERT INTO @Results (KeyType, ElapsedTime)
    SELECT 'IDENTITY() PK' AS KeyType, CAST((@end - @start) AS FLOAT) AS ElapsedTime;

    SET @loops = @loops + 1;
END
SELECT KeyType, FORMAT(CAST(AVG(ElapsedTime) AS DATETIME), 'HH:mm:ss.fff') AS AvgTime 
FROM @Results
GROUP BY KeyType;

Estes são os resultados:

insira a descrição da imagem aqui

Estou fazendo algo errado aqui ou as teclas INT são 3 vezes mais rápidas que as teclas naturais de 25 caracteres?

Observe que escrevi uma pergunta complementar aqui .

sql-server sql-server-2012

3 respostas

  1. Best Answer

    Em geral, o SQL Server usa B+Trees para índices. A despesa de uma busca de índice está diretamente relacionada ao comprimento da chave neste formato de armazenamento. Portanto, uma chave substituta geralmente supera uma chave natural em buscas de índice.

    O SQL Server agrupa uma tabela na chave primária por padrão. A chave de índice clusterizado é usada para identificar linhas, portanto, é adicionada como coluna incluída a todos os outros índices. Quanto maior a chave, maior será o índice secundário.

    Pior ainda, se os índices secundários não forem explicitamente definidos como UNIQUEa chave do índice clusterizado automaticamente se tornará parte da chave de cada um deles. Isso geralmente se aplica à maioria dos índices, pois geralmente os índices são declarados como exclusivos somente quando o requisito é impor a exclusividade.

    Portanto, se a questão for índice clusterizado natural versus substituto, o substituto quase sempre vencerá.

    Por outro lado, você está adicionando essa coluna substituta à tabela, tornando-a maior. Isso fará com que as varreduras de índice clusterizado fiquem mais caras. Portanto, se você tiver apenas alguns índices secundários e sua carga de trabalho exigir que verifique todas (ou a maioria das) linhas com frequência, talvez seja melhor usar uma chave natural para salvar esses poucos bytes extras.

    Por fim, as chaves naturais geralmente facilitam a compreensão do modelo de dados. Ao usar mais espaço de armazenamento, chaves primárias naturais levam a chaves estrangeiras naturais que, por sua vez, aumentam a densidade de informações locais.

    Portanto, como costuma acontecer no mundo dos bancos de dados, a resposta real é "depende". E - sempre teste em seu próprio ambiente com dados realistas.

    • 22

  2. Eu acredito que o melhor está no meio .

    Visão geral das chaves naturais:

    1. Eles tornam o modelo de dados mais óbvio porque vêm da área de assunto e não da cabeça de alguém.
    2. Chaves simples (uma coluna, entre CHAR(4)e CHAR(20)) estão economizando alguns bytes extras, mas você precisa observar sua consistência ( ON UPDATE CASCADEtorna-se crítico para essas chaves, que podem ser alteradas).
    3. Muitos casos, quando as chaves naturais são complexas: consiste em duas ou mais colunas. Se essa chave puder migrar para outra entidade como uma chave externa, isso adicionará sobrecarga de dados (índices e colunas de dados podem ficar grandes) e perda de desempenho.
    4. Se a chave for uma string grande, provavelmente sempre perderá para uma chave inteira, porque a condição de pesquisa simples se torna uma comparação de matriz de bytes em um mecanismo de banco de dados, que na maioria dos casos é mais lento do que a comparação inteira.
    5. Se a chave for uma string multilíngue, também será necessário observar os agrupamentos.

    Benefícios: 1 e 2.

    Cuidados: 3, 4 e 5.

    Visão geral das chaves de identidade artificial:

    1. Você não precisa se preocupar com sua criação e manuseio (na maioria dos casos), pois esse recurso é gerenciado pelo mecanismo de banco de dados. Eles são exclusivos por padrão e não ocupam muito espaço. Operações personalizadas como ON UPDATE CASCADEpodem ser omitidas porque os valores-chave não são alterados.

    2. Eles (frequentemente) são os melhores candidatos para migração como chaves estrangeiras porque:

      2.1. consiste em uma coluna;

      2.2. usando um tipo simples que tem um peso pequeno e age rápido para operações de comparação.

    3. Para entidades de associação, cujas chaves não são migradas para lugar nenhum, pode se tornar uma sobrecarga de dados pura, pois sua utilidade é perdida. Chave primária natural complexa (se não houver colunas de string lá) será mais útil.

    Benefícios: 1 e 2.

    Cuidados: 3.

    CONCLUSÃO:

    As chaves artificiais são mais fáceis de manter, confiáveis ​​e rápidas porque foram projetadas para esses recursos. Mas em alguns casos não são necessários. Por exemplo, CHAR(4)o candidato de coluna única na maioria dos casos se comporta como INT IDENTITY. Portanto, há outra questão aqui também: sustentabilidade + estabilidade ou obviedade ?

    Pergunta "Devo injetar uma chave artificial ou não?" sempre depende da estrutura da chave natural:

    • Se contiver uma string grande, será mais lento e adicionará sobrecarga de dados se migrar como estrangeiro para outra entidade.
    • Se consistir em várias colunas, será mais lento e adicionará sobrecarga de dados se migrar como estrangeiro para outra entidade.
    • 11

  3. Uma chave é um recurso lógico de um banco de dados, enquanto o desempenho é sempre determinado pela implementação física no armazenamento e pelas operações físicas executadas nessa implementação. Portanto, é um erro atribuir características de desempenho às teclas.

    Neste exemplo particular, no entanto, duas possíveis implementações de tabelas e consultas são comparadas entre si. O exemplo não responde à questão colocada no título aqui. A comparação que está sendo feita é de junções usando dois tipos de dados diferentes (inteiro e caractere) usando apenas um tipo de índice (B-tree). Um ponto "óbvio" é que, se um índice de hash ou outro tipo de índice for usado, possivelmente não haverá diferença de desempenho mensurável entre as duas implementações. No entanto, existem problemas mais fundamentais com o exemplo.

    Duas consultas estão sendo comparadas quanto ao desempenho, mas as duas consultas não são logicamente equivalentes porque retornam resultados diferentes! Um teste mais realista compararia duas consultas retornando os mesmos resultados, mas usando diferentes implementações.

    O ponto essencial sobre uma chave substituta é que ela é um atributo extra em uma tabela onde a tabela também possui atributos de chave "significativos" usados ​​no domínio de negócios. São os atributos não substitutos que são de interesse para que os resultados da consulta sejam úteis. Um teste realista, portanto, compararia tabelas usando apenas chaves naturais com uma implementação alternativa com chaves naturais e substitutas na mesma tabela. As chaves substitutas geralmente exigem armazenamento e indexação adicionais e, por definição, exigem restrições de exclusividade adicionais. Os substitutos requerem processamento adicional para mapear os valores de chave natural externa em seus substitutos e vice-versa.

    Agora compare esta possível consulta:

    UMA.

    SELECT t2.NaturalTable2Key, t2.NaturalTable1Key
FROM Table2 t2;

    Para seu equivalente lógico se o atributo NaturalTable1Key em Table2 for substituído pelo substituto IDTable1Key:

    B.

    SELECT t2.NaturalTable2Key, t1.NaturalTable1Key
FROM Table2 t2
INNER JOIN Table1 t1
ON t1.IDTable1Key = t2.IDTable1Key;

    A consulta B requer uma junção; A consulta A não. Esta é uma situação familiar em bancos de dados que (sobre) usam substitutos. As consultas tornam-se desnecessariamente complexas e muito mais difíceis de otimizar. A lógica de negócios (especialmente restrições de integridade de dados) torna-se mais difícil de implementar, testar e verificar.

    • 7

relate perguntas