首页 > > 网络编程 > Mysql >

MySQL全文索引 FULLTEXT索引和like的区别

2018-06-17 23:17:09来源：未知阅读 ()

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数据库全文搜索以外的文本搜索解决方案了。

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有