SQL Server - 计算列上的索引？

Question

我多次连接一张表格，每次都会基于其中一列的 SUBSTRING（它是一个字符串，但左边用零填充，且我不关心最后四位数字）的结果进行连接（或过滤）。尽管该列已被索引，我的查询也会使用索引，但由于 SUBSTRING 本身并没有被索引，因此 SQL Server 必须在连接之前为每一行计算它而进行表扫描。

我正在寻找任何加速这个过程的想法。目前，该表上有一个视图（它是 "SELECT * FROM"，只是为了给表起个友好的名字），我正在考虑添加一个计算列到视图中，然后对其进行索引。不过，我也乐意听取其他建议 - 有什么想法吗？

更多细节: 我应该一开始就分享这个内容。该表从我们的计费系统接收复制，因此编辑底层表以添加计算列不是一个选项。任何计算列都必须添加到表的视图中。此外，前导零并不总是前导零 - 它们有时是我不感兴趣的其他数据。我想真正的问题是 "如何连接 VARCHAR 列中间的数据并同时利用索引？全文搜索？"

澄清我的例子 我在简化，但本质上，假设我正在尝试查找具有以下值的列中的值：

00000012345MoreStuff
00000012345Whatever
19834212345
Houses12345837443GGD
00000023456MoreStuff

我对SUBSTRING(7,5)="12345"的行感兴趣，因此我想要第1-4行，但不想要第5行。我的想法是在我的“SELECT *”视图中添加一个包含此子字符串的列，然后基于该列进行索引。这样做更有意义吗？

- SqlRyan

---

为了使用索引进行JOIN，您应该转换列，使其以您要搜索的条件开头。目前，您的算法过于模糊。 "不总是前导零的前导零" 很难向 SQL Server 解释。 FULLTEXT 索引可用于搜索单词中的前缀（而不是整个列），但仍应将数据拆分为单词。您能否更清楚地定义您的搜索算法？ - Quassnoi

如果您正在考虑使用索引_SEEK_，那么您还需要知道您的数据是否足够选择性。也就是说，数据库中总值与不同值数量的比率是多少。"交叉点"实际上非常低（取决于表有多宽）。此外，如果您的索引不包括您从中进行选择的列，则您从书签查找中进行的读取次数将使SQL Server忽略您全新的索引。您能提供选择性/选择列表的详细信息吗？ - Anon246

Answer 1

假设您的字段格式为：

00Data0007
000000Data0011
0000Data0015

• 创建一个计算列：ndata AS RIGHT(REVERSE(data), LEN(data) - 4)

  这将把你的列转换成以下内容：

ataD00
ataD000000
ataD0000

在该列上创建索引

发出此查询以搜索字符串Data：

SELECT  *
FROM    mytable
WHERE   ndata LIKE N'ataD%'
        AND SUBSTRING(ndata, LEN(N'ataD') + 1, LEN(ndata)) = REPLICATE('0', LEN(ndata) - LEN('ataD'))

第一个条件将使用索引进行粗略过滤。

第二个条件将确保所有前导字符（在计算列中变为尾随字符）都是零。

查看我博客上的这篇文章以了解详细的性能信息：

• SQL Server：使用索引进行前置通配符匹配

更新

如果您只想在SUBSTRING上创建索引而不更改模式，则可以创建视图。

CREATE VIEW v_substring75
WITH SCHEMABINDING
AS
SELECT  s.id, s.data, SUBSTRING(data, 7, 5) AS substring75
FROM    mytable

CREATE UNIQUE CLUSTERED INDEX UX_substring75_substring_id ON (substring75, id)

SELECT  id, data
FROM    v_substring75
WHERE   substring75 = '12345'

- Quassnoi

---

这就是我最终要采用的方案。我只需要将视图与模式绑定，然后就可以开始了。感谢你的指引。 - SqlRyan

Answer 2

在你的表中添加一个计算列，并在该列上创建索引。

ALTER TABLE MyTable
Add Column CodeHead As LEFT(Code,Len(Code)-4)

然后在此上创建一个索引。

CREATE INDEX CodeHeadIdx ON MyTable.CodeHead

- pjp

Answer 3

你能否用“LIKE 'something%'”的语句重新表达一下你的筛选条件吗？（这适用于索引）

- Mitch Wheat

Answer 4

将列更改为两列 - 您要加入的数据和额外的4个字符。 使用列的部分会减慢速度，正如您所看到的那样。

- mmmmmm