c# - 如何优化MySQL bool 全文搜索？ (或者用什么来替换它？) - C#

Question

我有一个包含22000行的表，我使用布尔型全文搜索来找到我感兴趣的内容。我的问题是，我创建了一个“动态搜索感觉”，它由一个DataGridView组成，它在每个TextChanged事件之后都会刷新。因为您可能已经知道，在每次事件之后搜索插入的字符串需要很多时间。
我能做些什么来提高搜索速度？
欢迎提出任何建议！

Answer 1

首先，您应该认识到，rdbms对全文索引的支持是一种强制技术，这种技术旨在允许高效访问结构化数据，以处理非结构化文本。（是的，这只是我的看法。如果需要，我可以为它辩护，因为我非常了解这两种技术。；）
那么，如何提高搜索性能呢？
选项一-“完成任务的最佳工具”
在文档库中处理全文搜索的最佳方法是使用专门设计的技术，例如apache的SOLR（lucene）或err的Sphinx，sphinx。
出于以下明确的原因，我强烈建议采用这种方法。
选项二-预装结果
在构建基于文本的搜索解决方案时，通常的方法是将所有文档索引到一个可搜索的索引中，虽然这可能是最方便的方法，但并不是唯一的方法。
假设你正在搜索的内容可以很容易地量化为一组已知的规则，你可以提供更多的“引导”式搜索，而不是简单的无条件全文搜索。我的意思是，如果您的应用程序可能受益于将用户引导到结果，那么您可以将基于已知规则集的各种结果集预加载到它们自己的表中，从而减少要搜索的数据量。
如果您希望大多数用户都能从一组已知顺序的搜索词中受益，那么可以构建搜索ui来支持这些词。
因此，假设大多数用户都在寻找各种各样的汽车，您可以根据车型、年份、条件等提供预定义的搜索。您的搜索ui将被精心设计为一系列下拉菜单，以“引导”用户找到特定的结果。
或者，如果大多数搜索都是针对某个特定的主题（比如说“汽车”），则可以预先定义一个表，其中只包含先前确定与汽车相关的那些记录。
这两种方法都会减少要搜索的记录的数量，从而增加响应时间。
选项三-“自己滚”
如果您无法将外部搜索技术集成到项目中，并且预加载不是一个选项，那么仍然有很多方法可以大幅度提高搜索查询响应时间，但它们会根据您需要完成的任务和希望如何执行搜索而有所不同。
如果您希望用户使用单个关键字或短语以及它们之间的布尔关系进行搜索，那么您可以考虑构建自己的语料库“inverted index”。（这是mysql的布尔型全文搜索已经做的，但是自己做可以更好地控制搜索的速度和准确性。）
要从现有数据构建反向索引，请执行以下操作：
第一步。创建三个表

    // dict - a dictionary containing one row per unique word in corpus      create table dict (          id int primary key,        word varchar      )    // invert - an inverted_index to map words to records in corpus      create table invert (          id int primary key,        rec_id int,        word_id int      )    // stopwords - to contain words to ignore when indexing (like a, an, the, etc)    create table stopwords (       id int primary key,        word varchar      )

Note: This is just a sketch. You'll want to add indexes and constraints, etc. when you actually create these tables.

The stopwords table is used to reduce the size of your index to only those words that matter to users' expected queries. For example, it's rarely useful to index English articles, like 'a', 'an', 'the', since they do not contribute useful meaning to keyword searches.

Typically, you'll require a stopword list specifically crafted to the needs of your application. If you never expect users to include the terms 'red', 'white' or 'blue' in their queries or if these terms appear in every searchable record, you would want to add them to your stopword list.

See the note at the end of this message for instructions on using your own stopwords list in MySQL.

See also:

• The current list of stopwords supported in MySQL

• A good starting stopword list in English

Step 2. Build the Inverted Index

To build an inverted index from your existing records, you'll need to (pseudo-code):

    foreach( word(w) in record(r) ) {      if(w is not in stopwords) {        if( w does not exist in dictionary) {          insert w to dictionary at w.id        }        insert (r.id, w.id) into inverted_index      }    }

More on stopwords:

nstead of using a specific stopword list, the 'if(w is not in stopwords)' test could make other decisions either instead of or as an adjunct to your list of unacceptable words.

Your application might wish to filter out all words less than 4 characters long or to only include words from a predefined set.

By creating your own inverted index, you gain far greater and finer-grained control over search.

Step 3. Query the Inverted Index Using SQL

This step really depends on how you expect queries to submitted to your index.

If queries are to be 'hard-coded', you can simply create the select statement yourself or if you need to support user-entered queries, you'll need to convert whatever query language you choose into an SQL statement (typically done using a simple parser).

Assuming you wish to retrieve all documents matching the logical query '(word1 AND word2) OR word3', a possible approach might be:

CREATE TEMPORARY TABLE temp_results ( rec_id int, count int ) AS 
    ( SELECT rec_id, COUNT(rec_id) AS count 
      FROM invert AS I, dict AS D 
      WHERE I.word_id=D.id AND (D.word='word1' OR D.word='word2') 
      GROUP BY I.rec_id 
      HAVING count=2
    ) 
    UNION (
      SELECT rec_id, 1 AS count 
      FROM invert AS I, dict AS D
      WHERE I.word_id=D.id AND D.word='word3'
    );

SELECT DISTINCT rec_id FROM temp_results;

DROP TABLE temp_results;

注：这只是我头上的第一次传球。我相信有更有效的方法可以将布尔查询表达式转换为有效的sql语句，欢迎提出任何改进建议。
要搜索短语，您需要在倒排索引中添加一个字段，以表示单词在其记录中出现的位置，并将其放入所选内容中。
最后，在添加新记录或删除旧记录时，需要更新反向索引。
最后一句话
“全文检索”属于一个非常大的研究领域，称为“信息检索”或“信息检索”，有许多关于这一主题的书籍，包括
Information Retrieval: Implementing and Evaluating Search Engines作者Stefan Büttcher、Charles L.A.Clarke和Gordon V.Cormack（2010年7月23日）
Search Engines: Information Retrieval in Practice作者布鲁斯·克罗夫特、唐纳德·梅茨勒和特雷弗·斯特罗曼（2009年2月16日）
Building Search Applications: Lucene, LingPipe, and Gate作者Manu Konchady（2008年6月）
查看亚马逊了解更多信息。
笔记
如何在mysql中使用自己的stopwords列表
在mysql中使用自己的stopword列表：
创建自己的stopWords列表，每行一个单词，并将其保存到服务器上的已知位置，如/usr/local/lib/ir/stopWords.txt
编辑my.cnf以添加或更新以下行：[mysqld]
ft_min_word_len=1
ft_max_word_len=40
停止字文件=/usr/local/lib/ir/stopwords.txt
它将法律单词的最小和最大长度设置为1和40，
分别告诉mysqld在哪里可以找到您自定义的停止词列表。
（注意：默认的ft_max_word_len是84，我认为这太过分了。）
并可能导致非实词字符串的运行被编入索引。）
重新启动mysqld
删除并重新创建所有与全文相关的索引