我有一个非常简单的表 SQL 表(目前是 Azure SQL,如果重要的话,可能会留在那里或最终出现在本地 SQL Server 19 实例中),大约 100,000 条记录,主要用于读取,但会逐渐写入。
CREATE TABLE [dbo].[xCatalog](
[catalogID] [int] IDENTITY(1,1) NOT NULL,
[title] [nvarchar](255) NOT NULL,
[dateA] [datetime] NULL,
[dateB] [datetime] NULL,
[textA] [nvarchar](25) NULL,
[textB] [nvarchar](255) NULL,
[textC] [nvarchar](255) NULL,
CONSTRAINT [PK_xCatalog] PRIMARY KEY CLUSTERED
(
[catalogID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
值得注意的是[title] nvarchar(255)字段,该字段将是用户使用以下格式的查询主要搜索的内容,使用“ocean”作为示例 [title] 搜索词
SELECT TOP(500) [catalogID],[title],[dateA],[dateB],[textA],[textB],[textC]
FROM [dbo].[xCatalog]
WHERE ([title] LIKE '%ocean%')
ORDER BY [catalogID] ASC
它运行得相当不错,1-3 秒,但我真的很想深入研究如何/是否改进它,因为坦率地说,我对索引和其他优化等内容只有表面的了解。
查询是由前端程序生成的,我不相信我可以更改,因此为什么它选择按键排序的有限数量来返回虚拟滚动,以及为什么我不探索用 Contains 或其他内容替换 LIKE像那样。
因此,我真的试图从数据库方面来解决这个问题,只是了解最佳实践是什么,以及人们对这种情况的想法,即使用哪种索引,或者是否可以切换到列存储(概率太小?)等等。我们还使用基本层 Azure SQL 数据库,因此对于这种操作,我们可能会从稍微提高它中受益。
任何类似的东西,这样我就可以知道它的未来,并能够自信地解释我已经做了 x 或 y 来帮助它,但也许通配符搜索大文本有时很昂贵,所以我的期望应该是什么。
如果有帮助的话,我会加入简单的执行计划:
谢谢大家的意见!我知道还有很多类似的问题,我会继续研究,但有时感觉它们略有不同,我想问也有什么坏处。
执行计划看起来很合理——现有的 PK 已经是最好的了。当我们找到 500 个匹配行时,我们可以按顺序读取该表并进行短路。如果您无法更改查询,那么您在优化方面无能为力 - 更多内存、更快的 CPU 和更快的存储可能会有所帮助。
如果您可以更改查询,则有几个选项可供查看:
如果这对您有用,请使用它。但它的作用与 LIKE %%不同。它适用于搜索一般文本,但并不总是适用于代码等。
使用此技术,您可以保持LIKE %% 的精确行为,并显着提高性能。不幸的是,没有对 ngrams/trigrams 的内置支持,因此这是一项艰巨的工作。您还有与 ngrams 索引相关的额外开销和存储。对于一个有 100K 行的表来说,这可能不值得。我已经使用过它并且它可以很好地工作 - 但全文是一个更简单的选项,除非您确实需要 LIKE %% 的行为。您还可以考虑使用另一个系统,例如 ElasticSearch,它可以使用 ngrams/trigrams。
如果您确实需要的话,这样的事情可能有助于从 LIKE %% 中挤出一点额外的性能。
根据您的要求,还有许多其他事情可能会起作用。例如,添加用于搜索的标签可能会起作用。您也许可以缓存常见的搜索查询 - 如果数据相对静态,这应该会很有用。