mysql开启全文索引

0 386

很多网友在全站搜索时遇到搜一个字能搜出结果,搜词反而无结果的情况。

大多数是由于mysql未开启全文索引引起 ,mysql开启全文索引方法:

1、修改mysql配置文件:window服务器为my.ini,linux服务器为my.cnf,在 [mysqld] 后面加入一行“ft_min_word_len=1”,然后重启Mysql。

2、登录后台,模块->全站搜索->重建索引 重建全文索引。



==============================================



引言:
很多情况下,网站的程序需要MySQL支持全文索引,但是MySQL默认的配置中,并没有开启这个功能,或者说对全文索引支持的不够好,下面我们需要修改MySQL的配置文件:



Linux服务器为my.cnf,在 [mysqld] 下面加入一行“ft_min_word_len=1”,然后重启Mysql
以CentOS默认yum安装的MySQL为例[size=1em][size=1em]
1
vi /etc/my.cnf #编辑配置文件

[size=1em]
2
找到[mysqld],在下面添加一行

[size=1em]
3
ft_min_word_len=1

[size=1em]
4
:wq! #保存

[size=1em]
5
service mysqld restart #重启MySQL







==============================================


MySQL在高并发连接、数据库记录数较多的情况下,SELECT ... WHERE ... LIKE '%...%'的全文搜索方式不仅效率差,而且以通配符%和_开头作查询时,使用不到索引,需要全表扫描,对数据库的压力也很大。MySQL针对这一问题提供了一种全文索引解决方案,这不仅仅提高了性能和效率(因为MySQL对这些字段做了索引来优化搜索),而且实现了更高质量的搜索。但是,至今为止,MySQL对中文全文索引无法正确支持。

  中文与西方文字如英文的一个重要区别在于,西方文字以单词为单位,单词与单词之间以空格分隔。而中文以字为单位,词由一个或多个字组成,词与词之间没有空格分隔。当试图在一个含有中文字符的字段中使用全文搜索时,不会得到正确的结果,原因在于中文中没有像英文空格那样对词定界,不能以空格作为分割,对中文词语进行索引。

  一、MySQL中文全文索引插件mysqlcft的特点:
  1、优点:
  ①、精准度很高:采用自创的“三字节交叉切分算法”,对中文语句进行分割,无中文分词词库,搜索精准度远比中文分词算法高,能达到LIKE '%...%"的准确率。
  ②、查询速度快:查询速度比LIKE '%...%"搜索快3~50倍,文章末尾有测试结果;
  ③、标准插件式:以MySQL 5.1全文索引的标准插件形式开发,不修改MySQL源代码,不影响MySQL的其他功能,可快速跟进MySQL新版本;
  ④、支持版本多:支持所有的MySQL 5.1 Release Candidate版本,即MySQL 5.1.22 RC~最新的MySQL 5.1.25 RC;
  ⑤、支持字符集:支持包括GBK、GB2312、UTF-8、Latin1、BIG5在内的MySQL字符集(其他字符集没有测试过);
  ⑥、系统兼容好:具有i386和x86_64两个版本,支持32位(i386)和64位(x86_64)CPU及Linux系统;
  ⑦、适合分布式:非常适合MySQL Slave分布式系统架构,无词库维护成本,不存在词库同步问题。

  2、缺点:
  ①、mysqlcft中文全文索引只适用于MyISAM表,因为MySQL只支持对MyISAM表建立FULLTEXT索引;
  ②、MySQL不能静态编译安装,否则无法安装mysqlcft插件;
  ③、基于“三字节交叉切分算法”的索引文件会比海量、ft-hightman等基于“中文分词算法”的索引文件稍大,但不是大很多。根据我的测试,mysqlcft全文索引的.MYI索引文件是.MYD数据文件的2~5倍。

配置文件中添加
[mysqld]
ft_min_word_len = 1

附:MySQL配置文件在全文索引应用中的优化

[mysqld]
# key_buffer 指定用于索引的缓冲区大小,在全文索引中,增加它可得到更好的索引处理与查询性能
key_buffer = 512M

# sort_buffer_size 为查询排序时所能使用的缓冲区大小,全文索引的SQL语句之后通常会使用ORDER BY排序,增加它可以加快SQL语句执行时间。该参数对应的分配内存是每连接独占,100个连接使用的内存将是32M*100=3200M
sort_buffer_size = 32M

# 对大于可用内存的表执行GROUP BY或ORDER BY操作,应增加read_rnd_buffer_size的值以加速排序操作后面的行读取
read_rnd_buffer_size = 64M

# 如果表出现故障或索引出错,REPAIR TABLE时用到的缓冲区大小
myisam_sort_buffer_size = 128M

# 确定使用的filesort算法的索引值大小的限值
max_length_for_sort_data = 64

# MySQL全文索引查询所用关键词最小长度限制(不要改变这项值)
ft_min_word_len = 1

# 降低UPDATE优先级,设置查询优先
low_priority_updates = 1
wget http://mysqlcft.googlecode.com/f ... 0.0-i386-bin.tar.gz
tar zxvf mysqlcft-1.0.0-i386-bin.tar.gz
cp mysqlcft.so /usr/local/mysql1/lib/mysql/plugin/
--安装引擎
INSTALL PLUGIN mysqlcft SONAME 'mysqlcft.so';
--查看是否安装成功
SELECT * FROM mysql.plugin;
SHOW PLUGINS;
--创建索引
use test
ALTER IGNORE TABLE pa_gposts ADD FULLTEXT INDEX full_text_title(title) WITH PARSER mysqlcft;
--修复索引
REPAIR TABLE pa_gposts QUICK;
性能比较
没有添加索引之前
SELECT * FROM pa_gposts WHERE MATCH(title) AGAINST ('医院' IN BOOLEAN MODE) limit 0,30;
4 rows in set (1 min 12.69 sec)
这种查询出的结果单词的前后都要有停止字,查询速度还是很慢,因为没有索引
mysql> explain SELECT * FROM pa_gposts WHERE MATCH(title) AGAINST ('医院' IN BOOLEAN MODE) limit 0,30;
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
" id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | pa_gposts | ALL | NULL | NULL | NULL | NULL | 213193 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
创建索引后
SELECT * FROM pa_gposts WHERE MATCH(title) AGAINST ('医院' IN BOOLEAN MODE) limit 0,30;
30 rows in set (1.07 sec)
SELECT * FROM pa_gposts WHERE title LIKE '%医院%' limit 0,30;
30 rows in set (4.81 sec)

mysql> explain SELECT * FROM pa_gposts WHERE title LIKE '%医院%' limit 0,30;
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | pa_gposts | ALL | NULL | NULL | NULL | NULL | 213193 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

mysql> explain SELECT * FROM pa_gposts WHERE MATCH(title) AGAINST ('医院' IN BOOLEAN MODE) limit 0,30;
+----+-------------+-----------+----------+-----------------+-----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+----------+-----------------+-----------------+---------+------+------+-------------+
| 1 | SIMPLE | pa_gposts | fulltext | full_text_title | full_text_title | 0 | | 1 | Using where |
+----+-------------+-----------+----------+-----------------+-----------------+---------+------+------+-------------+

SELECT * FROM pa_gposts WHERE MATCH(title) AGAINST ('情侣' IN BOOLEAN MODE) limit 0,30;
30 rows in set (1.93 sec)
SELECT * FROM pa_gposts WHERE title LIKE '%情侣%' limit 0,30;
30 rows in set (10.17 sec)
SELECT * FROM pa_gposts WHERE MATCH(title) AGAINST ('月光' IN BOOLEAN MODE) limit 0,30;
13 rows in set (0.56 sec)
SELECT * FROM pa_gposts WHERE title LIKE '%月光%' limit 0,30;
13 rows in set (50.98 sec)
布尔全文搜索具有以下特点:

它们不使用 50% 域值。.
它们不会按照相关性渐弱的顺序将行进行分类。你可以从上述问询结果中看到这一点:相关性最高的行是一个包含两个“MySQL” 的行,但它被列在最后的位置,而不是开头位置。
即使没有FULLTEXT,它们仍然可以工作,尽管这种方式的搜索执行的速度非常之慢。
最小单词长度全文参数和最大单词长度全文参数均适用。
停止字适用。

布尔全文搜索的性能支持以下操作符:

+

一个前导的加号表示该单词必须 出现在返回的每一行的开头位置。

-

一个前导的减号表示该单词一定不能出现在任何返回的行中。

(无操作符)

在默认状态下(当没有指定 + 或–的情况下),该单词可有可无,但含有该单词的行等级较高。这和MATCH() ... AGAINST()不使用IN BOOLEAN MODE修改程序时的运作很类似。  

> <

这两个操作符用来改变一个单词对赋予某一行的相关值的影响。 > 操作符增强其影响,而 <操作符则减弱其影响。请参见下面的例子。

( )

括号用来将单词分成子表达式。括入括号的部分可以被嵌套。

~

一个前导的代字号用作否定符, 用来否定单词对该行相关性的影响。这对于标记“noise(无用信息)”的单词很有用。包含这类单词的行较其它行等级低,但因其可能会和-号同时使用,因而不会在任何时候都派出所有无用信息行。

*

星号用作截断符。于其它符号不同的是,它应当被追加到要截断的词上。

"

一个被括入双引号的短语 (‘"’) 只和字面上包含该短语输入格式的行进行匹配。全文引擎将短语拆分成单词,在FULLTEXT索引中搜索该单词。 非单词字符不需要严密的匹配:短语搜索只要求符合搜索短语包含的单词且单词的排列顺序相同的内容。例如, "test phrase" 符合 "test, phrase"。

若索引中不存在该短语包含的单词,则结果为空。例如,若所有单词都是禁用词,或是长度都小于编入索引单词的最小长度,则结果为空。

以下例子展示了一些使用布尔全文符号的搜索字符串:

'apple banana'

寻找包含至少两个单词中的一个的行。

'+apple +juice'

寻找两个单词都包含的行。

'+apple macintosh'

寻找包含单词“apple”的行,若这些行也包含单词“macintosh”, 则列为更高等级。

'+apple -macintosh'

寻找包含单词“apple” 但不包含单词 “macintosh”的行。

'+apple +(>turnover <strudel)'

寻找包含单词“apple”和“turnover” 的行,或包含“apple” 和“strudel”的行 (无先后顺序),然而包含 “apple turnover”的行较包含“apple strudel”的行排列等级更为高。

'apple*'

寻找包含“apple”、“apples”、“applesauce”或“applet”的行。

'"some words"'

寻找包含原短语“some words”的行 (例如,包含“some words of wisdom” 的行,而非包含 “some noise words”的行)。注意包围词组的‘"’ 符号是界定短语的操作符字符。它们不是包围搜索字符串本身的引号




====================================================




MySQL从3.23.23开始就逐渐支持全文索引和搜寻。
全文索引就是建index,全文搜寻就是去查index。
LIKE是用Regular Expression去做查询。
MySQL全文索引是一种index type:FULLTEXT。
全文索引的index只能用在MyISAM表格的char、varchar和text的字段上。
全文索引的index可以在create table、alter table和create index时产生。
create table...
CREATE TABLE article (
   id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT(title, body)
) TYPE=MYISAM;
alter table...
create index...
要倒大量的数据到有全文索引index的table速度会很慢,建议先拿掉全文索引index再倒数据,倒完后再加上全文索引index。
全文搜寻的语法:
MATCH (col1, col2,...) AGAINST (expr [search_modifier])
三种搜寻方式:
IN BOOLEAN MODE
IN NATURAL LANGUAGE MODE
IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION / WITH QUERY EXPANSION


IN NATURAL LANGUAGE MODE
expr就是要搜寻的字符串。
没有特殊字符。
套用Stopwords。
剔除一半row以上都有的字,譬如说,每个row都有mysql这个字的话,那用mysql去查时,会找不到任何row,这在row的数量无敌多时很有用,因为把所有row都找出来是没有意义的,这时,mysql几乎被当作是stopword;但是当row只有两笔时,是啥鬼也查不出来的,因为每个字都出现50%以上,要避免这种状况,请用IN BOOLEAN MODE。
预设的搜寻方式。
SELECT *
FROM article
WHERE MATCH(title, body)
AGAINST ('xxx' IN NATURAL LANGUAGE MODE);
预设搜寻是不分大小写,若要分大小写,columne的character set要从utf8改成utf8_bin。
预设MATCH...AGAINST是以相关性排序,由高到低。
MATCH...AGAINST可以跟所有MySQL语法搭配使用,像是JOIN或是加上其他过滤条件。
-- 第一种count
SELECT COUNT(*)
FROM article
WHERE MATCH(title, body)
AGAINST ('xxx' IN NATURAL LANGUAGE MODE);
-- 第二种count
SELECT COUNT(IF(MATCH(title, body) AGAINST ('xxx' IN NATURAL LANGUAGE MODE), 1, NULL)) AS count
FROM article
当符合的笔数较多时,第一种count比较慢,因为MATCH...AGAINST会先依相关性排序。
当符合的笔数较少时,第二种count比较慢,因为第二种count会扫过所有数据。
MATCH(title, body)里的字段必须和FULLTEXT(title, body)里的字段一模一样,如果只要单查title或body一个字段,那得另外再建一个FULLTEXT(title)或FULLTEXT(body),也因为如此,MATCH()的字段一定不能跨table,但是另外两种搜寻方式好像可以。
SELECT id, MATCH(title, body) AGAINST ('xxx' IN NATURAL LANGUAGE MODE) as score
FROM article;
这样可以取得相关值,而且也因为没有WHERE和ORDER BY,所以不会排序。
SELECT id, MATCH(title, body) AGAINST ('xxx' IN NATURAL LANGUAGE MODE) as score
FROM article
WHERE MATCH(title, body)
AGAINST ('xxx' IN NATURAL LANGUAGE MODE);
排序又取得相关性,虽然MATCH...AGAINST用了两次,但是MySQL知道这两个MATCH...AGAINST是一样的,所以只会用一次。
SELECT id, MATCH(title, body) AGAINST ('xxx' IN NATURAL LANGUAGE MODE) as score
FROM article
ORDER BY score desc;
为啥不这样用就好?
MySQL的FULLTEXT怎么断字:
字母、数字、底线的组合视为一个字,不会把底线断字。
会被断字的字符:空白、逗号(,)与点(.),但不用这些断字的语言,如中文,就得自行手动断字。
可以自行实做一个断字的外挂来取代内建的断字parser。
接受一个单引号,如aaa'bbb视为一个字,但是aaa''bbb就是两个字。
前缀或字尾的单引号会被去掉,如'aaa或aaa'。
全文搜寻时,stopword与少于四个字符的字符串会被忽略。
可以覆写内建的stopword清单。
可以修改最少四个字符的设定。

IN BOOLEAN MODE
expr里有特殊字符辅助特殊的搜寻语法。
SELECT *
FROM article
WHERE MATCH(title, body)
AGAINST ('+mysql -yoursql' IN BOOLEAN MODE);
一定要有msysql,且不要有yoursql。
IN BOOLEAN MODE的特色:
不剔除50%以上符合的row。
不自动以相关性反向排序。
可以对没有FULLTEXT index的字段进行搜寻,但会非常慢。
限制最长与最短的字符串。
套用Stopwords。
搜寻语法:
+:一定要有。
-:不可以有,但这个「不可以有」指的是在符合的row里不可以有指定的字符串,所以不能只下「-yoursql」这样是查不到任何row的,必须搭配其他语法使用。
:(什么都没)预设用法,表示可有可无,有的话排比较前面,没有的排后面。
>:提高该字的相关性。
<:降低相关性。
( ):条件可以巢状。
+aaa +(>bbb <ccc) // 找到有aaa和bbb,或者aaa和ccc,然后aaa&bbb排在aaa&ccc前面
~:将其相关性由正转负,表示拥有该字会降低相关性,但不像「-」将之排除,只是排在较后面。
*:万用字,不像其他语法放在前面,这个要接在字符串后面。
" ":用双引号将一段句子包起来表示要完全相符,不可拆字。

IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
也可以用WITH QUERY EXPANSION。
IN NATURAL LANGUAGE MODE的衍生版。
先用IN NATURAL LANGUAGE MODE做搜寻,得到最相关的字段的字再加到原expr里,再查一次。
神奇功能之一:可以用database查出mysql或oracle,第一次查询用databae得到一些结果,从这些结果里抽取字符串,此时得到mysql与oracle的机率相当高,最后用database和这些出取出来的字符串做一次查询。
神奇功能之二:无法拼出正确字符串时,第一次用「相似」的错误字符串查询,很有可以得到正确的字符串,再用正确的字符串急可以得到想要的结果。
因为这种查询方式会让「噪声」爆增,所以建议第一次的查询字符串尽量精简。
Stopwords请参考http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html
全文搜寻的限制:
只能用在MyISAM表格上。
支援UTF-8。
中文支持问题:
MySQL不会断中文字:MySQL内建的字依据是空白、逗号和点,对此内建机制的白痴解法是,存中文字时自行塞入空白断字,但是还是有下面的限制。
查询字符串最少四个字符的限制:所以一二三个中文字都不能查,必须将ft_min_word_len从预设的4改成1。
虽然同一个表格可以有不同字符集的字段,但是同一个FULLTEXT index里的字段必须是同一个字符集与collation。
MATCH里的字段必须和FULLTEXT里的一模一样,IN BOOLEAN MODE允许不一样,甚至使用未FULLTEXT index的字段,但速度很慢。
AGAINST里必须是字符串,不可以是变量或域名。
全文搜寻使index hint受限。
MySQL全文搜寻设定:
大部分的参数都是启动参数,也就是修改后必须重新启动MySQL。
有些参数修改必须重新产生索引文件。
mysql> SHOW VARIABLES LIKE 'ft%';

ft_boolean_syntax    + -><()~*:""&|
ft_min_word_len    4
ft_max_word_len    84
ft_query_expansion_limit   20 ft_stopword_file    (built-in)

ft_min_word_len:最短的索引字符串,默认值为4,修改后必须重建索引文件。
ft_max_word_len:最长的索引字符串,默认值因版本而不同,余同上一点。
[mysqld]
ft_min_word_len=1
ft_stopword_file:stopword档案路径,若留空白不设定表示要停用stopword过滤,修改后必须重新启动MySQL和重建索引;stopword档案内容可以用分行空白与逗号区隔stopword,但底线和单引号视为合法的字符串字符。
50%的门坎限制:配置文件在storage/myisam/ftdefs.h,将 #define GWS_IN_USE GWS_PROB 改为 #define GWS_IN_USE GWS_FREQ,然后重新编译MySQL,因为近低门坎会影响数据的精准度,所以不建议如此,可用IN BOOLEAN MODE即可以避开50%的限制。
ft_boolean_syntax:改变IN BOOLEAN MODE的查询字符,不用重新启动MySQL也不用重建索引。
修改字符串字符的认定,譬如说将「-」认定为字符串的合法字符:
方法一:修改storage/myisam/ftdefs.h的true_word_char()与misc_word_char(),然后重新编译MySQL,最后重建索引。
方法二:修改字符集档,然后在FULLTEXT index的字段使用该字符集,最后重建索引。
重建索引:
每个有FULLTEXT index的表格都要这么做。
mysql> REPAIR TABLE tbl_name QUICK;
要注意如果用过myisamchk,会导致上述的设定值回复成默认值,因为myisamchk不是用MySQL的设定值。
解法一:将修改过得设定值加到myisamchk的参数里。
shell> myisamchk --recover --ft_min_word_len=1 tbl_name.MYI
解法二:两边都要设定。
[mysqld]
ft_min_word_len=1
[myisamchk]
ft_min_word_len=1
解法三:用REPAIR TABLE、ANALYZE TABLE、OPTIMIZE TABLE与ALTER TABLE取代myisamchk语法,因为这些语法是由MySQL执行的。




================================================================

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

最新活动

  • 体验固件
  • 正式固件

新品速递

  • Win10 专业版|企业版 激活密匙|32位&64位
  • Windows 10 32位|64位 专业版 key激活
  • 论文搜索、查找、下载需用专业工具沸点文库
Archiver|手机版|小黑屋|关于我们|
©2014  自学论坛. All rights reserved. ( 陕ICP备15001769号 )  Powered by Discuz! X3.2  
快速回复 返回顶部 返回列表