Início / dba / Perguntas / 279837
Accepted
Joshua
Asked: 2020-11-18 08:48:42 +0800 CST

Varbinary inicia com consulta usando índice

  • 772

Eu tenho essa chave semelhante a uma árvore (pense em OIDs de objetos - é muito semelhante) para armazenar e indexar em uma tabela, para a qual as consultas desejam selecionar subárvores. A maneira mais direta de fazer isso em um btree é usar o caminho da árvore como a chave única e descer a chave com uma operação start-with para encontrar os blocos de linha.

Portanto, a representação óbvia é VARBINARY(n)*. Não consigo pensar em uma maneira de expressar o início de uma maneira que o SQL Server possa descobrir para usar o índice do mesmo.

O próximo mais óbvio é empacotar os dados em alguns VARCHARcom um agrupamento binário e usar uma likeoperação; no entanto descobri que like @n + '%'nunca usará um índice, mas apenas like 'literal%. Adicionar a WITH INDEXdica ainda verifica o índice em sua totalidade porque o SQL Server simplesmente não entende. Eu realmente não quero pensar em como escapar de argumentos no SQL para que eu possa construir um arquivo EXEC. Isso é apenas loucura e um desastre de segurança esperando para acontecer.

Expressar isso em tabelas recursivas comuns e consultar com SQL recursiva está fora de questão. A consulta recursiva irá desligar o servidor de banco de dados.

Na verdade, tenho os dados em uma longa sequência de longvariáveis ​​agora e posso selecionar meu formulário de serialização. Como eu disse, a forma natural é VARBINARYe realmente se pareceria com 0x000000100004A010000000D0000A000. A consulta mais comum é da forma "me dê tudo começando com 0x000000100004A01" ou no modelo natural, os primeiros nvalores da string de longvalores. Eu poderia escrevê-los no formulário, a.b.c.d.e.f...mas os números individuais são longos e vêm de clicar em coisas na GUI.

Grosso modo, estou olhando para uma forma plausível de

CREATE TABLE Record (
   RecordId BIGINT NOT NULL IDENTITY(1,1),
   RecordedDate DATETIME NOT NULL,
   RecordClass CHAR(1) NOT NULL,
   UserId INT NOT NULL,
   ObjectId VARBINARY(384) NOT NULL,
   RecordValue NVARCHAR(100) NULL,
   OwnerId BIGINT NULL, -- Joins to another table
   SubOwnerId BIGINT NULL, -- and yet another table
   PRIMARY KEY (RecordId)
)
CREATE INDEX name ON Record(RecordedDate);
CREATE INDEX name ON Record(OwnerId);
CREATE INDEX name ON Record(SubOwnerId);
CREATE INDEX name ON Record(ObjectId);

Qual é a melhor maneira de fazer um índice começa com?

*Eu calculei o maior n possível e é menor que 400

sql-server index

3 respostas

  1. Best Answer

    Então, na verdade, começar com não é tão difícil de escrever como parece à primeira vista. Dada a chave inicial de 0x000000100004, obviamente equals não funcionará e não há partidas com operação, mas podemos escrever ObjectId >= 0x000000100004 AND ObjectId < 0x000000100005.

    Ao forçar a string de longvalores para VARBINARYconversão a ser big-endian, a forma natural realmente funciona corretamente. Nós queremos >= (value0 & value1 & ... & lastvalue) AND < (value0 & value1 & ... & (lastvalue + 1)). Até onde eu sei, a conversão para VARBINARY deve ser escrita no código do aplicativo, o que é bom para mim.

    • 2

  2. Então, vou em frente e postar outra resposta usando HIERARCHYID porque posso. Não vamos usá-lo porque o ORM não gosta do tipo. Ah bem.

    Esquema:

    CREATE TABLE Record (
   RecordId BIGINT NOT NULL IDENTITY(1,1),
   RecordedDate DATETIME NOT NULL,
   RecordClass CHAR(1) NOT NULL,
   UserId INT NOT NULL,
   ObjectId HIERARCHYID NOT NULL,
   RecordValue NVARCHAR(100) NULL,
   OwnerId BIGINT NULL, -- Joins to another table
   SubOwnerId BIGINT NULL, -- and yet another table
   PRIMARY KEY (RecordId)
)
CREATE INDEX name ON Record(RecordedDate);
CREATE INDEX name ON Record(OwnerId);
CREATE INDEX name ON Record(SubOwnerId);
CREATE INDEX name ON Record(ObjectId);

    A consulta startswith não é facilmente adivinhada, mas bastante trivial:

    SELECT RecordId, RecordedDate, RecordClass, UserId, UserId, CAST(ObjectId AS VARCHAR(800)) ObjectId, RecordValue, OwnerId, SubOwnerId
  FROM Record
 WHERE Record.ObjectId.IsDescendantOf(CAST('/id/id/id/id/' AS HIERARCHYID))

    Eu testei isso e ele realmente lida com nossa previsão de pior caso de componentes inteiros de 40 bits e é pelo menos profundo o suficiente. O otimizador sabe como usar o índice muito bem.

    • 2

  3. Quando você usa variáveis ​​locais, o SQL Server não leva em consideração seus valores porque são desconhecidos no momento em que gera um plano de execução para a consulta e constrói um plano otimizado para valor DESCONHECIDO. Esta é a razão pela qual ele escolhe scan. Você pode usar SQL dinâmico ou procedimento armazenado para tornar o SQL Server capaz de otimizar o plano de execução para um valor específico. Por exemplo:

    DECLARE @LastName nvarchar(50)
DECLARE @sql nvarchar(4000);
SET @LastName = 'A%'
SET @sql = '
SELECT *
FROM Person.Person
WHERE LastName LIKE @LastName';
EXEC sp_executesql @sql, N'@LastName nvarchar(50)', @LastName
    • 1

relate perguntas