如何提升通配符*word*查询性能？500万文档查询耗时26秒求优化

Hey there! Let's dive into optimizing wildcard queries like *word*—especially since you're dealing with 5 million documents and seeing 26-second latency, that's definitely a pain point we can fix with smarter indexing and querying.

First, let's quickly cover why *word* is so slow: Most search engines (like Elasticsearch, Solr) rely on inverted indexes, which are optimized for prefix matches. When you wrap a term with wildcards on both ends, the engine can't use the index efficiently—it has to scan nearly every term in the index to find matches, which is the search equivalent of a full-table scan. No wonder it's slow on 5M docs!

Now, here are the actionable fixes, ordered by impact:

1. Swap Wildcards for N-Gram Tokenization (Biggest Win) #

Instead of using *someWord* at query time, pre-process your text into n-grams during indexing. N-grams are small chunks of text (e.g., 2-3 characters long)—so "someWord" gets split into so, om, me, ew, wo, or, rd, etc.

When you index these chunks, your search engine can quickly look up all documents that contain the full sequence of n-grams from "someWord"—no wildcard scanning needed. For example, if you're using Elasticsearch, you'd define an n-gram analyzer in your mapping, then use a simple match query for "someWord" instead of a wildcard query. This cuts latency drastically because it leverages the inverted index properly.

Here's a quick example of an n-gram analyzer setup in Elasticsearch:

{
  "settings": {
    "analysis": {
      "analyzer": {
        "content_ngram": {
          "tokenizer": "ngram_tokenizer"
        }
      },
      "tokenizer": {
        "ngram_tokenizer": {
          "type": "ngram",
          "min_gram": 2,
          "max_gram": 3
        }
      }
    }
  },
  "mappings": {
    "properties": {
      "content": {
        "type": "text",
        "analyzer": "content_ngram",
        "search_analyzer": "standard"
      }
    }
  }
}

2. Use Optimized Wildcard Field Types #

If you absolutely need to keep wildcard queries (e.g., users input arbitrary patterns), switch your field to a type designed for wildcard performance. For example, Elasticsearch's wildcard field uses a specialized index structure (based on finite state machines) that's way faster than running wildcards on a standard keyword field. It trades a bit of index size for much faster wildcard scans.

3. Filter First, Wildcard Later #

If your query includes other conditions (like date ranges, category filters, or boolean clauses), apply those filters first to reduce the number of documents the wildcard query has to scan. For example, if you only care about documents from the last 6 months, add that filter before the wildcard clause—this can cut the number of docs to scan by 90% or more, making the wildcard step lightning fast.

4. Avoid Double Wildcards When You Can #

If you can adjust your query pattern to use only a prefix (word*) or only a suffix (*word), do it! Prefix queries are natively supported by inverted indexes and are way faster. For suffixes, you can use a reverse analyzer: index the reversed version of your text (e.g., "drowemos" for "someWord"), then query with drow* to match *word.

5. Ditch Wildcards for Full-Text Search (If Possible) #

Wait—are you using *someWord* because you need to find documents containing that exact substring, or just documents that mention "someWord" as a word? If it's the latter, a standard match query on a text field will be orders of magnitude faster. Wildcards should only be used for strict substring matching, not general full-text search.

6. Hardware Tweaks (Last Resort) #

If all else fails, throw more resources at the problem:

• Add more memory to keep more of the index cached in RAM (wildcard scans are faster when the index is in memory).
• Upgrade to faster CPUs—wildcard processing is CPU-intensive.
• Distribute your index across multiple nodes to parallelize the scan.

But remember: hardware fixes are band-aids. The real wins come from optimizing your index and query logic first.

内容的提问来源于stack exchange，提问作者Konrad93