您的位置:新葡亰496net > 网络数据库 > 全文字笔迹核算索,MySQL全文索引

全文字笔迹核算索,MySQL全文索引

发布时间:2019-11-23 15:14编辑:网络数据库浏览(67)

    1.概要

    MySQL 全文搜索

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

    到 3.23.23 时,MySQL 开始支持全文索引和搜索。全文索引在 MySQL 中是一个 FULLTEXT 类型索引。FULLTEXT 索引用于 MyISAM 表,可以在 CREATE TABLE 时或之后使用 ALTER TABLE 或 CREATE INDEX 在 CHAR、VARCHAR 或 TEXT 列上创建。对于大的数据库,将数据装载到一个没有 FULLTEXT 索引的表中,然后再使用 ALTER TABLE (或 CREATE INDEX) 创建索引,这将是非常快的。将数据装载到一个已经有 FULLTEXT 索引的表中,将是非常慢的。

    为了在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索引的更新。

    全文搜索通过 MATCH() 函数完成。

    2.相关库表

    mysql> CREATE TABLE articles (
        ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        ->   title VARCHAR(200),
        ->   body TEXT,
        ->   FULLTEXT (title,body)
        -> );
    Query OK, 0 rows affected (0.00 sec)

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

    mysql> INSERT INTO articles VALUES
        -> (NULL,‘MySQL Tutorial‘, ‘DBMS stands for DataBase ...‘),
        -> (NULL,‘How To Use MySQL Efficiently‘, ‘After you went through a ...‘),
        -> (NULL,‘Optimising MySQL‘,‘In this tutorial we will show ...‘),
        -> (NULL,‘1001 MySQL Tricks‘,‘1. Never run mysqld as root. 2. ...‘),
        -> (NULL,‘MySQL vs. YourSQL‘, ‘In the following database comparison ...‘),
        -> (NULL,‘MySQL Security‘, ‘When configured properly, MySQL ...‘);
    Query OK, 6 rows affected (0.00 sec)
    Records: 6  Duplicates: 0  Warnings: 0

    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

    mysql> SELECT * FROM articles
        ->          WHERE MATCH (title,body) AGAINST (‘database‘);
    ---- ------------------- ------------------------------------------
    新葡亰496net,| id | title             | body                                     |
    ---- ------------------- ------------------------------------------
    |  5 | MySQL vs. YourSQL | In the following database comparison ... |
    |  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
    ---- ------------------- ------------------------------------------
    2 rows in set (0.00 sec)

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

    函数 MATCH() 对照一个文本集(包含在一个 FULLTEXT 索引中的一个或多个列的列集)执行一个自然语言搜索一个字符串。搜索字符串做为 AGAINST() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行,MATCH() 返回一个相关性值。即,在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。

    ? INNODB_SYS_TABLES:提供了InnoDB表的状态信息。

    当 MATCH() 被使用在一个 WHERE 子句中时 (参看上面的例子),返回的记录行被自动地以相关性从高到底的次序排序。相关性值是非负的浮点数字。零相关性意味着不相似。相关性的计算是基于:词在记录行中的数目、在行中唯一词的数目、在集中词的全部数目和包含一个特殊词的文档(记录行)的数目。

    ? 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配置选项指定的表关联的搜索索引相关信息。

    前面的例子是函数 MATCH() 使用上的一些基本说明。记录行以相似性递减的顺序返回。

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

    下一个示例显示如何检索一个明确的相似性值。如果即没有 WHERE 也没有 ORDER BY 子句,返回行是不排序的。

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

    mysql> SELECT id,MATCH (title,body) AGAINST (‘Tutorial‘) FROM articles;
    ---- -----------------------------------------
    | id | MATCH (title,body) AGAINST (‘Tutorial‘) |
    ---- -----------------------------------------
    |  1 |                        0.64840710366884 |
    |  2 |                                       0 |
    |  3 |                        0.66266459031789 |
    |  4 |                                       0 |
    |  5 |                                       0 |
    |  6 |                                       0 |
    ---- -----------------------------------------
    6 rows in set (0.00 sec)

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

    下面的示例更复杂一点。查询返回相似性并依然以相似度递减的次序返回记录行。为了完成这个结果,你应该指定 MATCH() 两次。这不会引起附加的开销,因为 MySQL 优化器会注意到两次同样的 MATCH() 调用,并只调用一次全文搜索代码。

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

    mysql> SELECT id, body, MATCH (title,body) AGAINST
        -> (‘Security implications of running MySQL as root‘) AS score
        -> FROM articles WHERE MATCH (title,body) AGAINST
        -> (‘Security implications of running MySQL as root‘);
    ---- ------------------------------------- -----------------
    | id | body                                | score           |
    ---- ------------------------------------- -----------------
    |  4 | 1. Never run mysqld as root. 2. ... | 1.5055546709332 |
    |  6 | When configured properly, MySQL ... |   1.31140957288 |
    ---- ------------------------------------- -----------------
    2 rows in set (0.00 sec)

    3.相关配置选项

    MySQL 使用一个非常简单的剖析器来将文本分隔成词。一个“词”是由文字、数据、“‘” 和 “_” 组成的任何字符序列。任何在 stopword 列表上出现的,或太短的(3 个字符或更少的)的 “word” 将被忽略。

    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索引时在内存中存储已解析文档的缓存大小。

    mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST (‘MySQL‘);
    Empty set (0.00 sec)

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

    在上面的例子中,搜索词 MySQL 却没有得到任何结果,因为这个词在超过一半的记录行中出现。同样的,它被有效地处理为一个 stopword (即,一个零语义值的词)。这是最理想的行为 -- 一个自然语言的查询不应该从一个 1GB 的表中返回每个次行(second row)。

    ? 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索引中的最大词长。设置这样一个限制后可通过忽略过长的关键字等有效降低索引大小从而加速查询。

    到 4.0.1 时,MySQL 也可以使用 IN BOOLEAN MODE 修饰语来执行一个逻辑全文搜索。

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

    mysql> SELECT * FROM articles WHERE MATCH (title,body)
        ->     AGAINST (‘ MySQL -YourSQL‘ IN BOOLEAN MODE);
    ---- ------------------------------ -------------------------------------
    | id | title                        | body                                |
    ---- ------------------------------ -------------------------------------
    |  1 | MySQL Tutorial               | DBMS stands for DataBase ...        |
    |  2 | How To Use MySQL Efficiently | After you went through a ...        |
    |  3 | Optimising MySQL             | In this tutorial we will show ...   |
    |  4 | 1001 MySQL Tricks            | 1. Never run mysqld as root. 2. ... |
    |  6 | MySQL Security               | When configured properly, MySQL ... |
    ---- ------------------------------ -------------------------------------

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

    这个查询返回所有包含词 MySQL 的记录行(注意: 50% 的阈值没有使用),但是它没有包含词 YourSQL。注意,一个逻辑模式的搜索不会自动地以相似值的降序排序记录行。你可以从上面的结果出看得出来,最高的相似值(包含 MySQL 两次的那个) 最列在最后,而不是第一位。一个逻辑全文搜索即使在没有一个 FULLTEXT 索引的情况下也可以工作,然而它 慢 些。

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

    逻辑全文搜索支持下面的操作符:

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

    • 一个领头的加号表示,该词必须出现在每个返回的记录行中。

    • 一个领头的减号表示,该词必须不出现在每个返回的记录行中。

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

    缺省的 (当既没有加号也没有负号被指定时)词是随意的,但是包含它的记录行将被排列地更高一点。这个模仿没有 IN BOOLEAN MODE 修饰词的 MATCH() ... AGAINST() 的行为。

    ? 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索引

    apple banana
    找至少包含上面词中的一个的记录行
    apple juice
    ... 两个词均在被包含
    apple macintosh
    ... 包含词 “apple”,但是如果同时包含 “macintosh”,它的排列将更高一些
    apple -macintosh
    ... 包含 “apple” 但不包含 “macintosh”
    apple (>pie <strudel)
    ... 包含 “apple” 和 “pie”,或者包含的是 “apple” 和 “strudel” (以任何次序),但是 “apple pie” 排列得比 “apple strudel” 要高一点
    apple*
    ... 包含 “apple”,“apples”,“applesauce” 和 “applet”
    "some words"
    ... 可以包含 “some words of wisdom”,但不是 “some noise words”

    CREATE TABLE articles (

    全文的限制
    MATCH() 函数的所有参数必须是从来自于同一张表的列,同时必须是同一个FULLTEXT 索引中的一部分,除非 MATCH() 是 IN BOOLEAN MODE 的。

         id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

    MATCH() 列列表必须确切地匹配表的某一 FULLTEXT 索引中定义的列列表,除非 MATCH() 是 IN BOOLEAN MODE 的。

         title VARCHAR(200),

    AGAINST() 的参数必须是一个常量字符串。

         body TEXT,

    微调 MySQL 全文搜索
    不幸地,全文搜索仍然只有很少的用户可调参数,虽然增加一些在 TODO 上排列很高。如果你有一个 MySQL 源码发行(查看章节 2.3 安装一个 MySQL 源码发行),你可以发挥对全文搜索的更多控制。

         FULLTEXT (title,body)

    注意,全文搜索为最佳的搜索效果,被仔细地调整了。修改默认值的行为,在大多数情况下,只会使搜索结果更糟。不要修改 MySQL 的源代码,除非你知道你在做什么!

    ) ENGINE=InnoDB;

    被索引的词的最小长度由 MySQL 变量 ft_min_word_len 指定。查看章节 4.5.6.4 SHOW VARIABLES。将它改为你所希望的值,并重建你的 FULLTEXT 索引。 (这个变量只从 MySQL 4.0 开始被支持)

    导入数据

    stopword 列表可以从 ft_stopword_file 变量指定的文件中读取。查看章节 4.5.6.4 SHOW VARIABLES。在修改了 stopword 列表后,重建你的 FULLTEXT 索引。(这个变量只从 MySQL 4.0.10 开始被支持)

    INSERT INTO articles (title,body) VALUES

    50% 阈值选择由所选择的特殊的衡量模式确定。为了禁止它,修改 `myisam/ftdefs.h‘ 文件中下面的一行: #define GWS_IN_USE GWS_PROB

       ('MySQL Tutorial','DBMS stands for DataBase ...'),

    改为: #define GWS_IN_USE GWS_FREQ

       ('How To Use MySQL Well','After you went through a ...'),

    然后重新编译 MySQL。在这种情况下,不需要重建索引。 注意:使用了这个,将严重地减少 MySQL 为 MATCH() 提供足够的相似性值的能力。如果你确实需要搜索这样的公共词,最好使用 IN BOOLEAN MODE 的搜索代替,它不遵守 50% 的阈值。

       ('Optimizing MySQL','In this tutorial we will show ...'),

    有时,搜索引擎维护员希望更改使用于逻辑全文搜索的操作符。这些由变量 ft_boolean_syntax 定义。查看章节 4.5.6.4 SHOW VARIABLES。然而,这个变量是只读的,它的值在 `myisam/ft_static.c‘ 中被设置。
    对于这些更改,要求你重建你的 FULLTEXT 索引,对于一个 MyISAM 表,最容易的重建索引文件的方式如下面的语句:

       ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),

    mysql> REPAIR TABLE tbl_name QUICK;

       ('MySQL vs. YourSQL','In the following database comparison ...'),

    全文搜索 TODO
    使所有对 FULLTEXT 索引的操作更快
    邻近(Proximity)操作符
    对 "always-index words" 的支持。他们可以是用户希望视为一个词处理的任意字符串,例如 "C "、"AS/400"、"TCP/IP",等等
    支持在 MERGE 表中的全文搜索
    对多字节字符的支持
    依照数据的语言建立 stopword 列表
    Stemming (当然,依赖于数据的语言)
    Generic user-suppliable UDF preparser.
    使模式更加灵活 (通过为 CREATE/ALTER TABLE 中的 FULLTEXT 增加某些可调整参数)

       ('MySQL Security','When configured properly, MySQL ...');

    全文搜索 到 3.23.23 时,MySQL 开始支持全文索引和搜索。全文索引在 MySQL 中是一个 FULLTEXT 类型索引。FULLTEXT 索引用于 MyISAM 表,可以在...

    例1:

    SELECT * FROM articles

    WHERE MATCH (title,body)

    AGAINST ('database' IN NATURAL LANGUAGE MODE);

    新葡亰496net 1

     

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

    例2:

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

    SELECT id, MATCH (title,body)

    AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) AS score

    FROM articles;

    新葡亰496net 2

     

    可以看到,所得结果的第二列即为改行与查找内容的相关度。上例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);

    新葡亰496net 3

     

    新葡亰496net 4

     

    可以看到,通过在查找部分和条件部分分别使用相同的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);

    新葡亰496net 5

    新葡亰496net 6

     

    该查询语句中“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);

    新葡亰496net 7

     

    新葡亰496net 8

     

    找到包含MySQL或者YourSQL的行

    例3:

    SELECT * FROM articles

    WHERE MATCH (title,body)

    AGAINST (' MySQL YourSQL' IN BOOLEAN MODE);

    新葡亰496net 9

    新葡亰496net 10

     

    找到包含同时MySQL和YourSQL的行

    全文字笔迹核算索,MySQL全文索引。例4:

    SELECT * FROM articles

    WHERE MATCH (title,body)

    AGAINST (' MySQL YourSQL' IN BOOLEAN MODE);

    新葡亰496net 11

    新葡亰496net 12

     

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

    例5:

    SELECT * FROM articles

    WHERE MATCH (title,body)

    AGAINST (' MySQL ~YourSQL' INBOOLEAN MODE);

    新葡亰496net 13

    全文字笔迹核算索,MySQL全文索引。 

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

    例6:

    SELECT * FROM articles

    WHERE MATCH (title,body)

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

    新葡亰496net 14

     

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

    例7:

    SELECT * FROM articles

    WHERE MATCH (title,body)

    AGAINST ('da*' IN BOOLEAN MODE);

    新葡亰496net 15

     

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

    例8:

    SELECT * FROM articles

    WHERE MATCH (title,body)

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

    新葡亰496net 16

     

    找到包含“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);

    新葡亰496net 17

     

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

    例2:

    SELECT * FROM articles

     WHERE MATCH (title,body)

    AGAINST ('database' WITH QUERY EXPANSION);

    新葡亰496net 18

     

    使用查询扩展全文搜索,不进返回了包含“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搜索:

    新葡亰496net 19

     

     

    FULLTEXT搜索:

    新葡亰496net 20

     

    FULLTEXT搜索与LIKE搜索对比:

    新葡亰496net 21

     

    结果讨论

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

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

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

    本文由新葡亰496net发布于网络数据库,转载请注明出处:全文字笔迹核算索,MySQL全文索引

    关键词: