优化 mysql 全文搜索

php mysql full-text-search

2022-08-31 01:08:58

我想在我的网络中使用全文进行搜索。我需要使用分页进行搜索。我的数据库每个表有 50，000 多行。我已经改变了我的表格，并成为索引。该表始终更新，仍然有一列是自动增加的。而最新的总是在表的末尾。(title,content,date)iddate

date  varchar(10)
title  text
content  text

但整个查询时间将花费。我通过谷歌搜索了许多文章，有些写道，只有限制字段字长才能帮助搜索更快。但作为一种类型，它改变了一定的长度（我尝试过标题，而不是工作）1.5+ secondsIndextextcan notALTER TABLE table_1 CHANGETEXT(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL

date  varchar(10)
title  text(500)
content  text(1000)

所以，除了和第三部分脚本。如何仅使用sql优化全文搜索？查询代码在这里：Sphinx

(SELECT 
title,content,date 
FROM table_1 
WHERE MATCH (title,content,date) 
AGAINST ('+$Search' IN BOOLEAN MODE)) 
UNION 
(SELECT 
title,content,date 
FROM table_2 
WHERE MATCH (title,content,date) 
AGAINST ('+$Search' IN BOOLEAN MODE)) 
Order By date DESC

谢谢。

答案 1

根据问题的后续注释，您的列上有一个 btree 索引，而不是全文索引。

对于针对搜索的 MATCH（标题、内容），您需要：

CREATE FULLTEXT INDEX index_name ON tbl_name (title,content);

我不确定它会接受那里的日期字段（后者可能无论如何都不相关）。

答案 2

我有一个全面的计划，让您尽可能彻底地优化MySQL以进行全文索引

你应该做的第一件事是：摆脱停用词列表

多年来，这让一些人感到恼火，因为他们不知道超过600个单词被排除在全文索引之外。

下面是这些停用词的表格视图。

有两种方法可以绕过这一点

绕过选项 1）创建自定义非索引字列表。

您实际上可以向mysql提交您首选的停用词列表。这是默认设置：

mysql> show variables like 'ft%';
+--------------------------+----------------+
| Variable_name            | Value          |
+--------------------------+----------------+
| ft_boolean_syntax        | + -><()~*:""&| |
| ft_max_word_len          | 84             |
| ft_min_word_len          | 4              |
| ft_query_expansion_limit | 20             |
| ft_stopword_file         | (built-in)     |
+--------------------------+----------------+
5 rows in set (0.00 sec)

好吧，不让我们创建我们的非索引字列表。我通常将英语文章设置为唯一的停用词。

echo "a"    > /var/lib/mysql/stopwords.txt
echo "an"  >> /var/lib/mysql/stopwords.txt
echo "the" >> /var/lib/mysql/stopwords.txt

接下来，将选项添加到 /etc/my.cnf，并允许 1 个字母、2 个字母和 3 个字母的单词

[mysqld]
ft_min_word_len=1
ft_stopword_file=/var/lib/mysql/stopwords.txt

最后，重新启动 mysql

service mysql restart

如果有任何表已具有 FULLTEXT 索引，则必须删除这些全文索引，然后重新创建它们。

绕过选项 2）重新编译源代码

文件名为 storage/myisam/ft_static.c。只需更改保存600多个单词的C结构，使其为空即可。享受重新编译!!!的乐趣

现在，全文配置已经固化，这是需要考虑的另一个主要方面：

编写适当的重构查询，以便MySQL查询优化器!!!

我现在提到的实际上是没有记录的：每当您执行执行JOIN的查询并且WHERE子句包含用于全文搜索的MATCH函数时，它都会导致MySQL查询优化器在搜索FULLTEXT索引中输入的列时将查询视为完整表扫描。如果计划使用 FULLTEXT 索引查询表，请始终重构查询，以使 FULLTEXT 搜索仅返回子查询中的键，并将这些键连接到主表。否则，FULLTEXT 索引会将 MySQL 查询优化器置于尾旋状态。