主页 / dba / 问题 / 279837
Accepted
Joshua
Asked: 2020-11-18 08:48:42 +0800 CST

Varbinary 使用索引开始查询

  • 772

我有这个树状键（想想对象 OID——它非常相似）来在表中存储和索引，查询想要选择其中的子树。在 btree 上执行此操作的最直接方法是使用树路径作为单个键，并使用starts-with 操作使键下降以查找行块。

所以明显的表示是VARBINARY(n)*。我想不出一种方法来表达以 SQL Server 可以弄清楚使用其索引的方式开始。

下一个最明显的是VARCHAR使用二进制排序规则将数据打包并使用like操作；但是我发现它like @n + '%'永远不会使用索引，而只会使用like 'literal%. 添加WITH INDEX提示仍然会完整地扫描索引，因为 SQL 服务器不理解。我真的不想考虑如何在 SQL 中转义参数，以便构建一个EXEC. 这只是疯狂和等待发生的安全灾难。

在普通递归表中表达这一点并使用递归 SQL 进行查询是不可能的。递归查询将为数据库服务器供电。

我现在实际上有一长串long变量中的数据，并且可以选择我的序列化形式。正如我所说，自然形式是VARBINARY，它真的看起来像0x000000100004A010000000D0000A000。最常见的查询是“给我所有以0x000000100004A01”开头的形式，或者在自然模型中，是n值字符串中的第一个long值。我可以将它们写在表格中，a.b.c.d.e.f...但是单个数字很长，并且来自单击 GUI 上的内容。

粗略地说，我正在寻找一种合理的形式

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);

做起始索引的最佳方法是什么？

*我已经计算出最大可能的 n 并且它小于 400

sql-server index

3 个回答

  1. Best Answer

    所以事实上，starts with 并不像最初看起来那样难写。给定初始键 0x000000100004，显然 equals 不起作用，也没有开始操作，但我们可以写 ObjectId >= 0x000000100004 AND ObjectId < 0x000000100005。

    通过强制转换为大端long的值字符串，自然形式实际上可以正常工作。我们想要. 据我所知，到 VARBINARY 的转换必须用应用程序代码编写，这对我来说很好。VARBINARY>= (value0 & value1 & ... & lastvalue) AND < (value0 & value1 & ... & (lastvalue + 1))

    • 2

  2. 因此，我将继续使用HIERARCHYID ，因为我可以发布另一个答案。我们不会使用它，因为 ORM 不喜欢这种类型。那好吧。

    架构：

    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);

    startswith 查询不容易猜到，但很简单：

    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))

    我对此进行了测试，它确实可以处理我们对 40 位整数分量的最坏情况预测，并且至少足够深。优化器知道如何使用索引就好了。

    • 2

  3. 当您使用局部变量时，SQL 服务器不会考虑它们的值，因为它们在为查询生成执行计划并构建针对 UNKNOWN 值优化的计划时是未知的。这就是它选择扫描的原因。您可以使用动态 SQL 或存储过程使 SQL Server 能够针对特定值优化执行计划。例如：

    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

相关问题