mysql基础语法之（全文索引）

1.概要

InnoDB引擎对FULLTEXT索引的支持是MySQL5.6新引入的特性，之前只有MyISAM引擎支持FULLTEXT索引。对于FULLTEXT索引的内容可以使用MATCH()…AGAINST语法进行查询。

为了在InnoDB驱动的表中使用FULLTEXT索引MySQL5.6引入了一些新的配置选项和INFORMATION_SCHEMA表。比如，为了监视一个FULLTEXT索引中文本处理过程的某一方面可以查询INNODB_FT_CONFIG，INNODB_FT_INDEX_TABLE，INNODB_FT_INDEX_CACHE,INNODB_FT_DEFAULT_STOPWORD，INNODB_FT_DELETED和INNODB_FT_BEING_DELETED这些表。可以通过innodb_ft_num_word_optimize和innodb_optimize_fulltext_only选项控制OPTIMIZETABLE命令对InnoDB FULLTEXT索引的更新。

2.相关库表

INFORMATION_SCHEMA库中与InnoDB全文索引相关的表如下：

INNODB_SYS_INDEXES

INNODB_SYS_TABLES

INNODB_FT_CONFIG

INNODB_FT_INDEX_TABLE

INNODB_FT_INDEX_CACHE

INNODB_FT_DEFAULT_STOPWORD

INNODB_FT_DELETED

INNODB_FT_BEING_DELETED

Ø INNODB_SYS_INDEXES:提供了InnoDB索引的状态信息。

Ø INNODB_SYS_TABLES：提供了InnoDB表的状态信息。

Ø INNODB_FT_CONFIG：显示一个InnoDB表的FULLTEXT索引及其相关处理的元数据。

Ø INNODB_FT_INDEX_TABLE：转化后的索引信息用于处理基于InnoDB表FULLTEXT索引的文本搜索。一般用于调试诊断目的。使用该表前需先配置innodb_ft_aux_table配置选项,将其指定为想要查看的含FULLTEXT索引的InnoDB表，选项值的格式为database_name/table_name。配置了该选项后INNODB_FT_INDEX_TABLE,INNODB_FT_INDEX_CACHE, INNODB_FT_CONFIG, INNODB_FT_DELETED和INNODB_FT_BEING_DELETED表将被填充与innodb_ft_aux_table配置选项指定的表关联的搜索索引相关信息。

Ø INNODB_FT_INDEX_CACHE：向含FULLTEXT索引的InnoDB表插入数据后新插入数据转后的索引信息。表结构与INNODB_FT_INDEX_TABLE一致。为含FULLTEXT索引的InnoDB表执行DML操作期间重组索引开销很大，因此将新插入的被索引的词单独存储于该表中，当且仅当为InnoDB表执行OPTIMIZE TABLE语句后才将新的转换后的索引信息与原有的主索引信息合并。使用该表前需先配置innodb_ft_aux_table配置选项。

Ø INNODB_FT_DEFAULT_STOPWORD：在InnoDB表上创建FULLTEXT索引所使用的默认停止字表。

Ø INNODB_FT_DELETED：记录了从InnoDB表FULLTEXT索引中删除的行。为了避免为InnoDB的FULLTEXT索引执行DML操作期间重组索引的高开销，新删除的词的信息单独存储于此表。当且仅当为此InnoDB表执行了OPTIMIZE TABLE操作后才会从主搜索索引中移除已删除的词信息。使用该表前需先配置innodb_ft_aux_table选项。

Ø INNODB_FT_BEING_DELETED：为含FULLTEXT索引的InnoDB表执行OPTIMIZE TABLE操作时会根据INNODB_FT_DELETED表中记录的文档ID从InnoDB表的FULLTEXT索引中删除相应的索引信息。而INNOFB_FT_BEING_DELETED表用于记录正在被删除的信息，用于监控和调试目的。

3.相关配置选项

Name	Cmd- Line	Option file	System Var	Status Var	Scope	Dynamic
innodb_ft_aux_table	Yes	Yes	Yes		Global	Yes
innodb_ft_cache_size	Yes	Yes	Yes		Global	No
innodb_ft_enable_diag_print	Yes	Yes	Yes		Global	Yes
innodb_ft_enable_stopword	Yes	Yes	Yes		Global	Yes
innodb_ft_max_token_size	Yes	Yes	Yes		Global	No
innodb_ft_min_token_size	Yes	Yes	Yes		Global	No
innodb_ft_num_word_optimize	Yes	Yes	Yes		Global	Yes
innodb_ft_server_stopword_table	Yes	Yes	Yes		Global	Yes
innodb_ft_sort_pll_degree	Yes	Yes	Yes		Global	No
innodb_ft_user_stopword_table	Yes	Yes	Yes		Both	Yes
innodb_optimize_fulltext_only	Yes	Yes	Yes		Global	Yes

Ø innodb_ft_aux_table:指定包含FULLTEXT索引的InnoDB表的的名称。该变量在运行时设置用于诊断目的。设置该值后INNODB_FT_INDEX_TABLE, INNODB_FT_INDEX_CACHE, INNODB_FT_CONFIG,INNODB_FT_DELETED和INNODB_FT_BEING_DELETED表将被填充与innodb_ft_aux_table指定的表关联的搜索索引相关信息。

Ø innodb_ft_cache_size:当创建一个InnoDB FULLTEXT索引时在内存中存储已解析文档的缓存大小。

Ø innodb_ft_enable_diag_print:是否开启额外的全文搜索诊断输出。

Ø innodb_ft_enable_stopword:是否开启停止字。InnoDB FUllTEXT索引被创建时为其指定一个关联的停止字集。（若设置了innodb_ft_user_stopword_table则停止字由该选项指定的表获取，若没有设置innodb_ft_user_stopword_table而设置了innodb_ft_server_stopword_table则停止字由该选项指定的表获取，否则使用内置的停止字。）

Ø innodb_ft_max_token_size:存储在InnoDB的FULLTEXT索引中的最大词长。设置这样一个限制后可通过忽略过长的关键字等有效降低索引大小从而加速查询。

Ø innodb_ft_min_token_size:存储在InnoDB的FULLTEXT索引中的最小词长。增加该值后会忽略掉一些通用的没有显著意义的词汇从而降低索引大小继而加速查询。

Ø innodb_ft_num_word_optimize:为InnoDB FULLTEXT索引执行OPTIMIZE操作每次所处理的词数。因为在含有全文搜索索引的表中执行批量的插入或更新操作需要大量的索引维护操作来合并所有的变化。因此，一般会运行一系列OPTIMIZE TABLE语句，每次从上一次的位置开始，处理指定数目的词，知道搜索索引被完全更新。

Ø innodb_ft_server_stopword_table:含有停止字的表，在创建InnoDB FULLTEXT索引时或忽略表中的停止字。停止字表需为InnoDB表，且在指定前应当已存在。

Ø innodb_ft_sort_pll_degree:为较大的表构建搜索索引时用于索引和记号化文本的并行线程数。

Ø innodb_ft_user_stopword_table:含有停止字的表，在创建InnoDB FULLTEXT索引时或忽略表中的停止字。停止字表需为InnoDB表，且在指定前应当已存在。

Ø innodb_optimize_fulltext_only:改变OPTIMIZE TABLE语句对InnoDB表操作的方式。对含FULLTEXT 索引的InnoDB表进行维护操作期间，一般临时的开启该选项。默认情况下，OPTIMIZE TABLE语句会重组表的聚集索引中的数据。若开启了该选项则该语句会跳过表数据的重组，而是只处理FULLTEXT索引中新插入的、删除的、更新的标记数据。（在对作为FULLTEXT索引的一部分的InnoDB表列进行了大量的插入、更新或删除操作后，先将innodb_optimize_fulltext_only设置为on以改变OPTIMIZE TABLE的默认行为，然后设置innodb_ft_num_word_optimize为合适的值以将索引维护时间控制在一个合理的可接受范围内，最后执行一系列的OPTIMIZE语句知道搜索索引被完全更新。）

4.全文搜索功能

全文搜索的语法：MATCH(col1,col2,…) AGAINST (expr[search_modifier])。

其中MATCH中的内容为已建立FULLTEXT索引并要从中查找数据的列，AGAINST中的expr为要查找的文本内容，search_modifier为可选搜索类型。search_modifier的可能取值有：

IN NATURAL LANGUAGEMODE、
IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION、
IN BOOLEAN MODE、
WITH QUERY EXPANSION。

search_modifier的每个取值代表一种类型的全文搜索，分别为自然语言全文搜索、带查询扩展的自然语言全文搜索、布尔全文搜索、查询扩展全文搜索（默认使用IN NATURAL LANGUAGE MODE）。

MySQL中全文索引的关键字为FULLTEXT，目前可对MyISAM表和InnoDB表的CHAR、VARCHAR、TEXT类型的列创建全文索引。全文索引同其他索引一样，可在创建表是由CREATE TABLE语句创建也可以在表创建之后用ALTER TABLE或者CREATE INDEX命令创建(对于要导入大量数据的表先导入数据再创建FULLTEXT索引比先创建索引后导入数据会更快)。

4.1自然语言全文搜索

自然语言全文搜索是MySQL全文搜索的默认搜索方式，实现从一个文本集合中搜索给定的字符串。这里，文本集合指的是指由FULLTEXT索引的一个或者多个列。

示例：建表，并给title，body字段加FULLTEXT索引

CREATE TABLE articles (
     id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
     title VARCHAR(200),
     body TEXT,
     FULLTEXT (title,body)
) ENGINE=InnoDB;

导入数据

INSERT INTO articles (title,body) VALUES
   ('MySQL Tutorial','DBMS stands for DataBase ...'),
   ('How To Use MySQL Well','After you went through a ...'),
   ('Optimizing MySQL','In this tutorial we will show ...'),
   ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
   ('MySQL vs. YourSQL','In the following database comparison ...'),
   ('MySQL Security','When configured properly, MySQL ...');

例1：

SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('database' IN NATURAL LANGUAGE MODE);

可以看到，语句查找到了包含指定内容的行。实际上，返回的行是按与所查找内容的相关度由高到低的顺序排列的。这个相关度的值由WHERE语句中的MATCH (…) AGAINST (…)计算所得，是一个非负浮点数。该值越大表明相应的行与所查找的内容越相关，0值表明不相关。该值基于行中的单词数、行中不重复的单词数、文本集合中总单词数以及含特定单词的行数计算得出。

例2：

由上例可知MATCH (…) AGAINST (…)实际上会计算一个相关值，可通过下例来验证。

SELECT id, MATCH (title,body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) AS score
FROM articles;

可以看到，所得结果的第二列即为改行与查找内容的相关度。上例1中所得结果的顺序就是按此相关度排列的。

例3：

若想既看到查找到的结果又需要了解具体的相关度，可用下述方法达成。

SELECT id, body, MATCH (title,body) AGAINST
   ('Security implications of running MySQL as root'
   IN NATURAL LANGUAGE MODE) AS score
   FROM articles WHERE MATCH (title,body) AGAINST
   ('Security implications of running MySQL as root'
IN NATURAL LANGUAGE MODE);

可以看到，通过在查找部分和条件部分分别使用相同的MATCH(…) AGAINST(…)可以同时获取两方面的内容（不会增加额外开销，优化器知道两个MATCH(…) AGAINST(..)是相同的，只会执行一次该语句）

注意事项

默认情况下全文搜索大小写不敏感，如上例1，查找的内容为‘database’但含有‘DataBase’的行也会返回。可以通过为FULLTEXT索引列所使用的字符集指定一个特定的校对集来改变这种行为。

考虑下述两个SELECT语句：

1. SELECTCOUNT(*) FROM articles

WHEREMATCH (title,body)

AGAINST('database' IN NATURAL LANGUAGE MODE);

2. SELECTCOUNT(IF(MATCH (title,body)

AGAINST('database' IN NATURAL LANGUAGE MODE), 1, NULL)) AS count

FROMarticles;

这两条查询语句均可返回匹配的行数。但第一条语句可以利用基于WHERE从句的索引查找，因此在匹配的行数较少时速度较第二句更快。第二句执行了全表扫描，因此在匹配的行数较多时较第一句更快。

MATCH()函数中的列必须与FULLTEXT索引中的列相同。如MATCH(title,body)与FULLTEXT(title,body)。若要单独搜索某列，如body列，则需另外单独为该列建全文索引FULLTEXT(body)，然后用MATCH(body)搜索。

对于InnoDB表MATCH()中的列仅能来自于同一个表，因为索引不能快多张表（MyISAM表的的布尔搜索因为可以不使用索引所以可以跨多张表中的列，但速度很慢）。

全文搜索不仅可以搜索类似例1中‘database’这样的单个的单词，还可以搜索句子（这才是其被称为‘全文搜索‘的关键），如例3。全文搜索把任何数字、字母、下划线序列看作是单词，还可以包含“’”如aaa’bbb备解析为一个单词，但aaa’’bbb备解析为两个单词，FULLTEXT解析器自动移除首尾的“’”，如’aaa’bbb’被解析为aaa’bbb。FULLTEXT解析器用“ ”（空格）、“,”（逗号）“.”（点号）作为默认的单词分隔符，因此对于不使用这些分隔符的语言如汉语来说FULLTEXT解析器不能正确的识别单词，对于这种情况需做额外处理。

全文搜索中一些单词会被忽略。首先是过短的单词，InnoDB全文搜索中默认为3个字符，MyISAM默认4个字符，可通过在创建FULLTEXT索引前改变配置参数来改变默认行为，对于InnoDB该参数为：innodb_ft_min_token_size，对于MyISAM为ft_min_word_len；另外stopword列表中的单词会被忽略。stopword列表包含诸如“the”、“or”、“and”等常用单词，这些词通常被认为没有什么语义价值。MySQL由内建的停止字列表，但是可以所使用自定义的停止字列表来覆盖默认列表。对于InnoDB控制停止字的配置参数为innodb_ft_enable_stopword，innodb_ft_server_stopword_table， innodb_ft_user_stopword_table对于MyISAM参数为ft_stopword_file。

文本集合和查询语句中的单词的权重由该单词在集合或语句中的重要性确定。单词在越多的行中出现则该单词的权重越低，因为这表明其在文本集合中的语义价值较小。反之权重越高。例1中提到的相关度计算也与此值有关。

4.2布尔全文搜索

如果在AAGAINST()函数中指定了INBOOLEN MODE模式，则MySQL会执行布尔全文搜索。在该搜索模式下，待搜索单词前或后的一些特定字符会有特殊的含义。

例1：

SELECT * FROM articles

WHERE MATCH (title,body)

AGAINST ('+MySQL-YourSQL' IN BOOLEAN MODE);

该查询语句中“MySQL”前的“+”表明结果中必须包含“MySQL”而“YourSQL”前的“-”表明所得结果中不能含有“YourSQL”。

除了“+”和“-”外还有其他一些特定的字符。如空字符表明后跟的单词是可选的，但出现的话会增加该行的相关性；“@distance”用于指定两个或多个单词相互之间的距离（以单词度量）需在指定的范围内；“>”用于增加后跟单词对其所在行的相关性的贡献“<”用于降低该贡献；“（）”用于将单词分组为子表达式且可以嵌套；“~”是后跟单词对其所在行的相关性的贡献值为负；“*”为普通的通配符，若为单词指定了通配符，那么即使该单词过短或者出现在了停止字列表中它也不会被移除；“””，括在双引号中的短语指明行必须在字面上包含指定的短语，全文搜索将短语分割为词后在FULLTEXT索引中搜索。非字字符无需完全匹配，如”test phrase”可以匹配含”test phrase”和”test phrase”的行，但匹配含”phrase test”的行。

例2：

SELECT * FROM articles

WHERE MATCH (title,body)

AGAINST ('MySQL YourSQL' IN BOOLEAN MODE);

找到包含MySQL或者YourSQL的行

例3：

SELECT * FROM articles

WHERE MATCH (title,body)

AGAINST ('+MySQL+YourSQL' IN BOOLEAN MODE);

找到包含同时MySQL和YourSQL的行

例4：

SELECT * FROM articles

WHERE MATCH (title,body)

AGAINST ('+MySQL YourSQL' IN BOOLEAN MODE);

找到必须包含MySQl的行，YourSQL可有可无，但有YourSQL会增加相关性。

例5：

SELECT * FROM articles

WHERE MATCH (title,body)

AGAINST ('+MySQL ~YourSQL' INBOOLEAN MODE);

找到包含必须包含MySQL的行，YourSQL可有可无，若出现了YourSQL则会降低其所在行的相关性。

例6：

SELECT * FROM articles

WHERE MATCH (title,body)

AGAINST ('+MySQL +(>Security <Optimizing)' IN BOOLEANMODE);

找到必须同时包含MySQL以及Security或Optimizing的行Security会增加所在行的相关性，而Optimizing会降低所在行的相关性。

例7：

SELECT * FROM articles

WHERE MATCH (title,body)

AGAINST ('da*' IN BOOLEAN MODE);

找到包含da*的行。如包含DataBase、database等。

例8：

SELECT * FROM articles

WHERE MATCH (title,body)

AGAINST('"MySQL,Tutorial"' IN BOOLEAN MODE);

找到包含“MySQL Tutorial”短语的行。

布尔全文搜索的一些特点

Ø MyISAM全文搜索会忽略至少在一半以上数据行中出现的单词（也即所谓的50%阈值），InnoDB无此限制。而在布尔全文搜索中MyISAM的50%阈值不生效。

Ø 停止字列表也适用于布尔全文搜索。

Ø 最小和最大词长全文搜索参数也适用于布尔全文搜索

Ø MyISAM中的布尔搜索在FULLTEXT索引不存在的时候仍可工作，但速度很慢。而InnoDB表的各类全文搜索必须有FULLTEXT索引，否则会出现找不到与指定列相匹配的FULLTEXT索引的错误

Ø InnoDB中的全文搜索不支持在单一搜索单词前使用多个操作符如“++MySQL”。MyISAM中全文搜索可以处理这种情况，但是会忽略除了紧邻单词之外的其他操作符。

4.3查询扩展全文搜索

某些时候我们通过全文搜索来查找包含某方面内容的行，比如我们搜索“database”，实际上我们期望返回结果不仅仅是仅包含“database”单词的行，一些包含“MySQL”、“SQLServer”、“Oracle”、“DB2”、“RDBMS”等的行也期望被返回。这个时候查询扩展全文搜索就能大显身手。

通过在AGAINST()函数中指定WITHQUERY EXPANSION 或者IN NATURAL MODE WITH QUERY EXPANSION可以开启查询扩展全文搜索模式。其工作原理是执行两次搜索，第一次用给定的短语搜索，第二次使用给定的短语结合第一次搜索返回结果中相关性非常高的一些行进行搜索。

例1：

SELECT * FROM articles

WHERE MATCH (title,body)

AGAINST ('database' IN NATURAL LANGUAGE MODE);

使用自然语言搜索返回了包含“database”的行。

例2：

SELECT * FROM articles

WHERE MATCH (title,body)

AGAINST ('database' WITH QUERY EXPANSION);

使用查询扩展全文搜索，不进返回了包含“database”的行，也返回了与例1中返回的行的内容相关的行。

注意事项

因为查询扩展会返回一些不相关的内容，因此会显著的引入噪声。索引仅当要查询的短语较短时才在考虑使用查询扩展全文搜索。

4.4全文搜索的停止字

上文已经简单介绍过了停止字列表，这里做详细介绍。停止字列表用MySQL Server所使用的字符集和校对集（分别由character_set_server和collation_server两个参数控制）载入并执行搜索。若用于全文索引和搜索的停止字文件或者停止字表使用了与MySQL Server不同的字符集和校对集会则导致查找停止字时错误的命中或未命中。

停止字查找的大小写敏感性也依赖于MySQL Server所使用的校对集，例如校对集为latin1_swedish_ci则查找是大小写不敏感的，若校对集为latin1_geberal_cs或者latin1_bin则查找是大小写敏感的。

InnoDB默认的停止字列表相对较短（因为技术上的或者文学等方面的文档常使用较短的词作为关键字或者有其他显著意义）。InnoDB默认的停止字列表存储在information_schema.innodb_ft_default_stopword表中。当然也可以通过自定义与innodb_ft_default_stopword表结构相同的表，填充期望的停止字，然后通过innodb_ft_server_stopword_table选项指定自定义的停止字表db_name/table_name，来改变默认的行为。另外还可以为innodb_ft_user_stopword_table选项指定含停止字的表，若同时指定了innodb_ft_default_stopword和innodb_ft_user_stopword_table则将使用后者指定的停止字表。上述操作改变所使用停止字表的操作需在创建全文索引前完成。且在指定所使用的停止字表时，表必须已经存在。

对于MyISAM可通过 ft_stopword_file选项指定所使用的停止字列表。MyISAM默认的停止字列表可在MySQL源码的 storage/myisam/ft_static.c文件中找到。

4.5全文搜索的限制

Ø 目前只有InnoDB和MyISAM引擎支持全文搜索。其中InnodB表对FULLTEXT索引的支持从MySQL5.6.4开始。

Ø 分区表不支持全文搜索。

Ø 全文索引适用于多数多字节字符集。例外情况是：对于Unicode，utf8字符集可用但ucs2字符集不适用。尽管不能在ucs2列建立FULLTEXT索引，但可以在MyISAM表IN BOOLEAN MODE模式的搜索中搜索没有建立FULLTEXT索引的列。utf8的特性适用于utf8mb4，ucs2的特性适用于utf16、utf16e和utf32。

Ø 表意型语言如汉语、日语没有诸如空格之类的单词定界符。因此FULLTEXT解析器不能确定此类语言中词的起止。对于此种情况要特殊处理（比如将中文转换成一种单字节类似英文习惯的存储方式）。

Ø 允许在同一表中使用多种字符集，但FULLTEXT索引中的列必须使用同一字符集和校对集。

Ø MATCH()函数中的列必须与FULLTEXT索引中定义的列完全一致，除非是在MyISAM表中使用IN BOOLEAN MODE模式的全文搜索（可在没有建立索引的列执行搜索，但速度很慢）。

Ø AGAINST()函数中的参数需为在查询评估期间保持不变的字符串常量。

Ø FULLTEXT搜索的索引提示比non-FULLTEXT搜索的索引提示要多一些限定：对于自然语言模式的全文搜索，索引提示会被忽略而不给出任何提示，比如虽明确在查询语句中给出了IGNORE INDEX(i)指明不使用i索引，但是该索引提示会被忽略掉，最终的查询中仍会使用索引i；对于布尔模式的全文搜索，FOR ORDER BY和FOR GROUP BY的索引提示会被忽略，FOR JOIN和不带FOR修饰符的索引提示不被忽略。

4.6全文搜索参数调整

仅有少量的用户可调参数用于调整MySQL的全文搜索能力。可以通过修改源码来获取更多对MySQL全文搜索行为的控制。但一般情况下不推荐这么做，除非很清楚自己在做什么，因为这些参数已经针对效率做过调整，修改默认的行为多数情况下反而会带来性能下降。

多数全文搜索相关的变量不能在Server运行的时候修改。需在Server启动时指定这些参数，或者修改完参数之后重新启动Server。另外，某些变量修改后需要重建FULLTEXT索引。

控制最小、最大字长的配置选项对于InnoDB为：innodb_ft_min_token_size和innodb_ft_max_token_size，对于MyISAM为：ft_min_word_len 和 ft_max_word_len。改变这些选项中任意一个的值都需重建FULLTEXT索引并重启Server。

用于停止字列表的配置选项对于InnoDB为：innodb_ft_enable_stopword、innodb_ft_server_stopword_table和innodb_ft_user_stopword_table，对于MyISAM为：ft_stopword_file。可以通过改变这些选项的值来开启/关闭停止字过滤并指定停止字列表。修改了这些选项后需重建索引并在必要的时候重启Server。

ft_stopword_file指定了包含停止字列表的文件，Server默认在数据目录搜索该文件除非用绝对路径指定了文件位置，若文件内容为空，则会关闭MyISAM的停止字过滤功能。停止字文件格式很灵活，可以使用任何非字母或数字的字符来界定停止字，但“_”和“’”例外，它们会被当作字的一部分处理。停止字列表使用Server默认的字符集。

MyISAM全文搜索的50%阈值特性可通过修改源码来关闭，将源码storage/myisam/ftdefs.h中的宏#define GWS_IN_USEGWS_PROB替换为#define GWS_IN_USE GWS_FREQ后重新编译MySQL即可。同样，不推荐上述方式，如果确实需要搜索一些通用的词，可以用布尔模式的全文搜获，此种情况下50%阈值特性不生效。

可以通过修改ft_boolean_syntax选项的值来更改MyISAM布尔全文搜做中默认使用的操作符（InnoDB无此选项）。该选项可动态改变但须超级用户权限，另外，改变了改制后无需重建FULLTEXT索引。

可以通过多种方式更改期望被认作是单词字符成分的字符集合。默认情况下“_”和“’”以及字母和数字被认为是组成单词的字符，其他的被默认为定界符。例如，我们现在想把连字符“-”也作为组成单词的字符处理，那么可以通过如下方式完成：

Ø 修改MySQL源码，在storage/myisam/ftdefs.h文件中找到true_word_char()和misc_word_char()两个宏，在任一个宏定义里添加“-”，重新编译MySQL。

Ø 修改字符集文件，true_word_char()宏实际上利用“character type”表来从其他字符中区分出字母和数字。可以通过编辑字符集对应的XML文件中<ctype><map>节点中的内容来将“-”指定为“字母“，然后将该字符集用于FULLTEXT索引。此种方式无需重新编译MySQL。对于编辑字符集XML文件，可参阅MySQL参考手册CharacterDefinition Arrays部分。
http://dev.mysql.com/doc/refman/5.6/en/character-arrays.html

Ø 对FULLTEXT索引列使用的字符集添加新的校对集，然后更新该列以使用新添加的校对集。具体参阅MySQL手册Adding a Collation to a Character Set以及Adding a Collation for Full-Text Indexing部分。
http://dev.mysql.com/doc/refman/5.6/en/full-text-adding-collation.html
http://dev.mysql.com/doc/refman/5.6/en/adding-collation.html

为InnoDB表重建FULLTEXT索引可以通过带DROP INDEX和ADD INDEX从句的ALTER TABLE语句完成，先删除旧的再创建新的。为MyISAM表重建FULLTEXT索引同样可通过上述语句完成，也可以通过QUICK repair操作来重建（但通常第一种方式会更快），如：

mysql> REPAIR TABLE tbl_name QUICK;

需要特别说明的是，若通过repair表的方式来为MyISAM表重建FULLTEXT索引，则通过上述语句进行即可。用myisamchk工具也可以为MyISAM表重建索引，但是容易导致查询产生错误的结果，对表的修改可能使Server认为该表被损坏了。究其原因是因为通过myisamchk工具执行修改MyISAM表的索引的操作时，除非明确指定了要使用的参数值否则使用默认的全文索引参数值（如最小最大词长等）重建FULLTEXT索引。导致这种情况是因为只有Server才知道这些全文索引参数值，MyISAM索引文件中不存储这些值。若更改过了这些值，如设置了ft_min_word_len=2，则在通过myisamchk工具修复表时要明确指定该修改过的参数值如：

shell> myisamchk --recover--ft_min_word_len=3 tbl_name.MYI

当然也可以通过在MySQL配置文件[myisamchk]节中加入同[mysqld]节中与全文搜索相关参数一致的参数来确保myisamchk使用最新的参数值来重建表的FULLTEXT索引。

用myisamchk为MyISAM表修改索引的替代方式是使用REPAIR TABLE、ANALYZE TABLE、 OPTIMIZE TABLE、ALTER TABLE，这些语句是由Server执行的因此可以读取到正确的全文索引参数值，不会引起问题。

4.7为全文搜索添加校对字符集

参考

10.4. Adding a Collation to a Character Set

http://dev.mysql.com/doc/refman/5.6/en/adding-collation.html

12.9.7. Adding a Collation for Full-Text Indexing

http://dev.mysql.com/doc/refman/5.6/en/full-text-adding-collation.html

5.性能对比测试

5.1测试环境

测试机：SVR644HP380

内存容量：8G

MySQL Server版本：5.6.12

5.2测试设计

词汇量：6个等级，分别用vocab01k、vocab05k、vocab10k、vocab15k,vocab25k、vocab35k标记，每个等级的词汇数如下，1000、5000、10000、15000、25000、35000。(取牛津词典单词部分，去重复后随机打乱顺序，分别截取前1000、5000、10000……作为对应的词汇量)

记录数：20个等级，分别用rec005k、rec010k、rec015k、rec020k、……rec095k、rec100k标记，每个等级的记录数如下，5000、10000、15000、20000、25000、30000、……、95000、100000。

根据词汇量等级和记录数等级分别生成含不同记录数且表中文本列是由对应的词汇量生成的随机文本的表，共6*20=120个。表的存储引擎使用InnoDB。表由id和body两个字段组成，分别为整型和文本型，且在body列创建了FULLTEXT索引。表名的命名规则为vocab01k_rec005k，表示该表中共含有5千条记录，每条记录中的body列由vocab01k对应的词汇量生成的随机单词组成，以此类推。每行记录中的body列定为由50个随机单词组成。

比较两类查询：LIKE从句查询以及使用FULLTEXT索引的MATCH()AGAINST()查询。在每个表上分别执行LIKE查询和MATCH() AGAINST()全文查询，每个表上的每个查询分别执行50次，记录每次所耗费的时间。对于每50个消耗的时间，删除其最大两个值和最小两个值，取剩余值的均值作为查询耗时的最终结果。这样一共可获得120*2 = 240个时间数据，根据这些数据绘图。在每个表上执行的查询如下（其中random_word1、random_word2、random_word3是根据查询时表对应的词汇量生成的随机单词。）：

LIKE搜索：
SELECT body FROM table_name WHERE body LIKE "%random_word1%" AND bodyLIKE "% random_word2%" AND body LIKE "% random_word3%";

FULLTEXT搜索：
SELECT body FROM table_name WHERE MATCH(body) AGAINST("+random_word3 + random_word3+ random_word3" IN BOOLEAN MODE)

5.3测试结果

图示

LIKE搜索：

FULLTEXT搜索：

FULLTEXT搜索与LIKE搜索对比：

结果讨论

LIKE搜索的耗时随着记录数的增加而线性增长，但对于10万行记录以下的表（这里共100000*50个单词）搜索时间基本上能保持在1秒以内，所以like搜索的性能也不是特别差。由不同词汇量生成的文本对LIKE搜索的性能影响不大，不同词汇量对应的搜索时间基本上在一个很小的时间范围内变化。

FULLTEXT搜索耗时也随表中记录数的增长而线性增加。对于10万行记录以下的表（这里共100000*50个单词）搜索时间基本上能保持在0.01秒以内。由不同词汇量生成的随机文本对FULLTEXT搜索性能有相对来说比较显著的影响。每行记录中含同样的单词数，这样，较大的词汇量倾向于生成冗余度更低的文本，相应的搜索耗时倾向于更少。这可能与FULLTEXT索引建立单词索引的机制有关，较大的词汇量倾向于生成范围广但相对较浅的索引，因而能快速确定文本是否匹配。

与LIKE搜索相比，FULLTEXT全文搜索的性能要强很多，对于10万行记录的表，搜索时间都在0.02秒以下。因此可以将基于FULLTEXT索引的文本搜索部署于网站项目中的文本搜索功能中。但是，正如上述提到的，无论是LIKE搜索还是FULLTEXT搜索，其性能都会随着记录数的增长而下降，因此，若网站项目中的文本搜索数据库记录数庞大的一定规模后，可能需要考虑使用MySQL数据库全文搜索以外的文本搜索解决方案了。

一、全文搜索与全文索引

1.首先我们实现中文的模糊匹配，比如我要搜索“北京china”，把所有包含“北京china”的相关文章都搜索出来。

首先，大家最容易想到的就是用like模式匹配了。假设我们的这个字段叫做title.表名称叫做ft_table

则最简单的语句应该是：select * from ft_table where title like "%北京china%" 这样。

但是这样存在的这样一个缺陷，就是数据库要进行全表扫描，不能使用索引，如果数据量大了的话，搜索效率非常低下。

改进方案：利用mysql的全文索引来达到同样的目的。

1.分词。我们知道全文索引的基础是以词为基础的，所以我们第一步要做的就是分词。

2.怎么分词。一想到分词我们肯定会说用很多开源的现成分词工具。这里我们的分词指的是将字符串中的每个字作为一个词来对待。比如“北京china”我们会分成“北京 c h i n a"。因为只有这样我们才能让任意两个相邻的字组成一个词来达到模糊匹配的结果。如果按传统的分词是这样”北京 china“，那如果我们搜索”京“的话，就搜索不到这个词，也就无法找到结果。但是在数据库中确实存在包含”京“的内容。

3.区位码转化。在mysql的全文索引中，是不支持中文的。所以我们必须要将中文转换成mysql可以支持的字符--区位码，因为每个汉字的区位码是唯一的，而且是4位的。所以我们将每个单字都转换成对应的区位码。

4.如何处理英文字符。在mysql中，对于类似这样的词”a，ag，b，c...“等等是忽略不计的。那么如果我们输入”a“的话，怎么样才能将包含”a“的内容都搜索出来呢？对，就是将英文字符也转换成对应的ascii码，因为英文的ascii码是用数字表示的，而且范围是0-127，所以，mysql不会忽略。

5.解决mysql全文索引最小词长度的限制。默认情况下，全文索引的最小词的长度是4个字节。即ft_min_word_len=4.但是每个英文字符的ascii码最长也就3位数字，即三个字节。所以我们应该是将

ft_min_word_len的值更改成3。但是有的ascii码值是两位或者一位的，所以我们在转换的时候应该将不足三位的数字补齐成三位，加前导0数字即可。

6.如何将任意两个相邻的字看成一个词来进行搜索呢？利用mysql中的模式against('"keyword1 keyword2"')

例如，我们要的分词列表为”北京 c h i n a“。输入的关键词为"京c" ，则利用这样的模式against('"京 c"' in boolean mode) 即可得到包含”京c“的内容。对于用户来说，搜索到的就是”京c“的关键词的结果。

二、操作示例

一、如何设置？

如图点击结尾处的{全文搜索}即可设置全文索引，不同MYSQL版本名字可能不同。

二、设置条件
1.表的存储引擎是MyISAM，默认存储引擎InnoDB不支持全文索引（新版本MYSQL5.6的InnoDB支持全文索引）
2.字段类型：char、varchar和text

三、配置
my.ini配置文件中添加
# MySQL全文索引查询关键词最小长度限制
[mysqld]
ft_min_word_len = 1
保存后重启MYSQL，执行SQL语句

SHOW VARIABLES

查看ft_min_word_len是否设置成功，如果没设置成功请确保
1.确认my.ini正确配置，注意不要搞错my.ini的位置
2.确认mysql已经重启，实在不行重启电脑
其他相关配置请自行百度。
注：重新设置配置后，已经设置的索引需要重新设置生成索引

四、SQL语法
首先生成temp表

CREATE TABLE IF NOT EXISTS `temp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `char` char(50) NOT NULL,
  `varchar` varchar(50) NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `char` (`char`),
  FULLTEXT KEY `varchar` (`varchar`),
  FULLTEXT KEY `text` (`text`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

INSERT INTO `temp` (`id`, `char`, `varchar`, `text`) VALUES
(1, 'a bc 我 知道 1 23', 'a bc 我 知道 1 23', 'a bc 我 知道 1 23');

搜索`char`字段 'a' 值:

SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('a')

但是你会发现查询无结果,原因：
如果一个关键词在50%的数据出现，那么这个词会被当做无效词。
如果你想去除50%的现在请使用IN BOOLEAN MODE搜索

SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('a' IN BOOLEAN MODE)

这样就可以查询出结果了，但是我们不推荐使用。
全文索引的搜索模式的介绍自行百度。

我们先加入几条无用数据已解除50%限制

INSERT INTO  `temp` (
`id` ,
`char` ,
`varchar` ,
`text`
)
VALUES (
NULL ,  '7',  '7',  '7'
), (
NULL ,  '7',  '7',  '7'
), (
NULL ,  'a,bc,我,知道,1,23',  'a,bc,我,知道,1,23',  'a,bc,我,知道,1,23'
), (
NULL ,  'x',  'x',  'x'
);

这时你执行以下SQL语句都可以查询到数据

SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('a');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('bc');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('我');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('知道');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('1');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('23');

以下SQL搜索不到数据

SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('b');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('c');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('知');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('道');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('2');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('3');

如果搜索多个词，请用空格或者逗号隔开

SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('a x');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('a,x');

上面的SQL都可以查询到三条数据

五、分词
看到这里你应该发现我们字段里的值也是分词，不能直接插入原始数据。
全文索引应用流程：
1.接收数据-数据分词-入库
2.接收数据-数据分词-查询
现在有个重要的问题：怎么对数据分词？
数据分词一般我们会使用一些成熟免费的分词系统，当然如果你有能力也可以自己做分词系统，这里我们推荐使用SCWS分词插件。

首先下载
1.php_scws.dll 注意对应版本
2.XDB词典文件
3.规则集文件
下载地址

安装scws
1.先建一个文件夹，位置不限，但是最好不要中文路径。
2.解压{规则集文件}，把xdb、三个INI文件全部扔到 D:\scws
3.把php_scws.dll复制到你的PHP目录下的EXT文件夹里面
4.在 php.ini 的末尾加入以下几行：
[scws]

; 注意请检查 php.ini 中的 extension_dir 的设定值是否正确, 否则请将 extension_dir 设为空，
; 再把 php_scws.dll 指定为绝对路径。

extension = php_scws.dll
scws.default.charset = utf8
scws.default.fpath = "D:\scws"
5.重启你的服务器
测试

复制代码代码如下:

$str="测试中文分词";
$so = 
scws_new();
$so->send_text($str);
$temp=$so->get_result();
$so->close();
var_dump($temp);

如果安装未成功，请参照官方说明文档
--------------------------------------------------------------------------------
这样我们就可以使用全文索引技术了。

posted on 2016-03-14 17:30 duanxz 阅读(5359) 评论(1) 编辑收藏举报