查询组合索引与多个单个索引与全文索引的性能 [英] Query performance of combined index vs. multiple single indexes vs. fulltext index

问题描述

背景：我有一个包含500万个地址条目的表格，我想搜索不同的字段（客户名称，联系人姓名，邮编，城市，电话......），向上到8个领域。数据相当稳定，每天最多50次更改，因此几乎只有读取权限。

Background: I have a table with 5 million address entries which I'd like to search for different fields (customer name, contact name, zip, city, phone, ...), up to 8 fields. The data is pretty stable, maximum 50 changes a day, so almost only read access.

用户不应该提前告诉我他要搜索的内容，以及我还希望支持组合搜索（搜索术语的AND连接）。例如，lincoln + lond应该在任何搜索字段中搜索包含搜索词的所有记录，也包括以任何词开头的条目（例如本例中的伦敦）。

The user isn't supposed to tell me in advance what he's searching for, and I also want support of combined search (AND-concatenation of search terms). For example "lincoln+lond" should search for all records containing both search terms in any of the search fields, also those entries starting with any of the terms (like "London" in this example).

问题：现在我需要为此搜索表选择索引策略。 （作为旁注：我试图达到亚秒响应时间，最差的响应时间应该是2秒。）在性能方面有什么好处：

Problem: Now I need to choose an indexing strategy for this search table. (As a side note: I'm trying to achieve sub-second response time, worst response time should be 2 seconds.) What's better in terms of perfomance:

1. 从所有可查询列中获取组合索引（需要其中2个，因为达到900字节的索引限制）


2. 在每个可查询列上放置单个索引


3. 在可查询列上创建全文索引并使用全文查询

我正在丢弃第1点，因为它似乎没有任何优势（索引使用将受到限制，并且不会有索引搜索，因为并非所有字段都适合单个索引）。

I'm discarding point 1, as it doesn't seem to have any advantage (index usage will be limited and there will be no "index seek", because not all fields fit in one single index).

问题：现在，我应该使用多个单一索引变体，还是应该使用全文索引？是否有任何其他方式来实现上述功能？

Question: Now, should I use the multiple single indexes variant or should I go with the fulltext index? Is there any other way to achieve the functionality mentioned above?

推荐答案

回答我自己的问题：

我选择了多个单一索引选项。我结束了每个查询列的索引，每个索引只包含列本身。搜索效果非常好，主要是亚秒响应时间。有时它需要2-3秒，但我将它归因于我的数据库服务器（几年前使用3GB Ram和慢速磁盘的笔记本电脑）。

I've chosen the "multiple single indexes" option. I ended having an index for each of the queried columns, each index containing only the column itself. The search works very good with mostly subsecond response times. Sometimes it takes up to 2-3 seconds, but I'm attributing it to my database server (several years old laptop with 3GB Ram and slow disk).

我没有不测试全文选项，因为它不再需要（我没有时间去做。）

I didn't test the fulltext option as it was not anymore necessary (and I don't have the time to do it.)

这篇关于查询组合索引与多个单个索引与全文索引的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！