美文网首页
mysql索引(八)全文索引

mysql索引(八)全文索引

作者: camellias__ | 来源:发表于2021-07-26 09:39 被阅读0次

    Mysql索引大概有五种类型:

    普通索引(INDEX):最基本的索引,没有任何限制
    唯一索引(UNIQUE):与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。
    主键索引(PRIMARY):它 是一种特殊的唯一索引,不允许有空值。
    全文索引(FULLTEXT ):可用于 MyISAM 表,mysql5.6之后也可用于innodb表, 用于在一篇文章中,检索文本信息的, 针对较大的数据,生成全文索引很耗时和空间。
    联合(组合)索引:为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则。

    这里看下全文索引。

    全文索引,平时用到的不多,字数较多的文章查询基本上我们就使用中文分词器来进行分词查询,而不是使用全文索引来查询。全文索引的效率还是低。

    但是相较于文字较少的数据,使用全文索引的效率要比使用like进行模糊查询的效率要高。

    一:基本使用

    1:建表的时候创建全文索引

    create table fulltext_test (
        id int(11) NOT NULL AUTO_INCREMENT,
        content text NOT NULL,
        tag varchar(255),
        PRIMARY KEY (id),
        FULLTEXT KEY content_tag_fulltext(content,tag)  // 创建联合全文索引列
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    

    2:在已存在的表上创建全文索引

    (1):使用create语句创建索引

    create fulltext index content_tag_fulltext
        on fulltext_test(content,tag);
    

    (2):使用alter语句创建索引

    alter table fulltext_test
        add fulltext index content_tag_fulltext(content,tag);
    

    3:修改全文索引

    修改全文索引有点麻烦,想修改的话,直接删了重新创建就好。

    4:删除全文索引

    (1):直接使用 DROP INDEX 删除全文索引

    drop index content_tag_fulltext
        on fulltext_test;
    

    (2):通过 SQL 语句 ALTER TABLE 删除全文索引

    alter table fulltext_test
        drop index content_tag_fulltext;
    

    5:使用全文索引

    语法:

    MATCH (columnName) AGAINST ('string')
    

    示例:

    SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')
     
    -- 当查询多列数据时:
    -- 建议在此多列数据上创建一个联合的全文索引,否则使用不了索引的。
     
    SELECT * FROM `student` WHERE MATCH(`name`,`address`) AGAINST('聪 广东')
    

    注意: match() 函数中指定的列必须和全文索引中指定的列完全相同,否则就会报错,无法使用全文索引,这是因为全文索引不会记录关键字来自哪一列。如果想要对某一列使用全文索引,请单独为该列创建全文索引。

    二:测试全文索引

    创建数据表:

    create table test (
        id int(11) unsigned not null auto_increment,
        content text not null,
        primary key(id),
        fulltext key content_index(content)
    ) engine=MyISAM default charset=utf8;
    

    写入几条数据:

    insert into test (content) values ('a'),('b'),('c');
    insert into test (content) values ('aa'),('bb'),('cc');
    insert into test (content) values ('aaa'),('bbb'),('ccc');
    insert into test (content) values ('aaaa'),('bbbb'),('cccc');
    

    查看数据表中的数据:

    MariaDB [test]> select * from test;
    +----+---------+
    | id | content |
    +----+---------+
    |  1 | a       |
    |  2 | b       |
    |  3 | c       |
    |  4 | aa      |
    |  5 | bb      |
    |  6 | cc      |
    |  7 | aaa     |
    |  8 | bbb     |
    |  9 | ccc     |
    | 10 | aaaa    |
    | 11 | bbbb    |
    | 12 | cccc    |
    +----+---------+
    12 rows in set (0.00 sec)
    

    我们现在使用全文索引来查询表中数据:注意我查询语句所对应的结果集的区别:

    MariaDB [test]> select * from test where match(content) against('a');
    Empty set (0.01 sec)
     
    MariaDB [test]> select * from test where match(content) against('aa');
    Empty set (0.00 sec)
     
    MariaDB [test]> select * from test where match(content) against('aaa');
    Empty set (0.00 sec)
     
    MariaDB [test]> select * from test where match(content) against('aaaa');
    +----+---------+
    | id | content |
    +----+---------+
    | 10 | aaaa    |
    +----+---------+
    1 row in set (0.00 sec)
     
    

    通过对比,我们可以看到只有select * from test where match(content) against('aaaa');查出了数据,其余三条sql并没有查出数据。

    这是因为mysql的全文索引是有最小搜索长度和最大搜索长度限制的。

    三:全文索引最小搜索长度和最大搜索长度

    MySQL 中的全文索引,有两个变量,最小搜索长度和最大搜索长度,对于长度小于最小搜索长度和大于最大搜索长度的词语,都不会被索引。通俗点就是说,想对一个词语使用全文索引搜索,那么这个词语的长度必须在以上两个变量的区间内。

    1:使用以下命令可以查看当前数据库的配置:

    show

     variables 
    like
     
    '%ft%'
    ;
    

    可以看到这两个变量在 MyISAM 和 InnoDB 两种存储引擎下的变量名和默认值

    // MyISAM
    ft_min_word_len = 4;
    ft_max_word_len = 84;
     
    
    // InnoDB
    innodb_ft_min_token_size = 3;
    innodb_ft_max_token_size = 84;
    

    可以看到最小搜索长度 MyISAM 引擎下默认是 4,InnoDB 引擎下是 3,也即,MySQL 的全文索引只会对长度大于等于 4 或者 3 的词语建立索引,而刚刚搜索的只有 aaaa 的长度大于等于 4。

    2:配置最小搜索长度

    需要修改mysql的配置文件来修改。

    一般的mysql的配置文件是在/etc/my.cnf中。追加以下内容:

    [mysqld]
    innodb_ft_min_token_size = 1
    ft_min_word_len = 1
    

    然后重启 MySQL 服务器,然后,需要修复一下目前已有的全文索引,否则,刚刚的修改不会生效。

    我这里建议,就不要修复了,直接删除重建就好了。

    四:两种全文索引

    1:自然语言的全文索引

    默认情况下,或者使用 in natural language mode 修饰符时,match() 函数对文本集合执行自然语言搜索,上面的例子都是自然语言的全文索引。

    自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里,相关度是基于匹配的关键词的个数,以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语,匹配时的相关度就越高。相反,非常常见的单词将不会被搜索,如果一个词语的在超过 50% 的记录中都出现了,那么自然语言的搜索将不会搜索这类词语。上面提到的,测试表中必须有 4 条以上的记录,就是这个原因。

    这个机制也比较好理解,比如说,一个数据表存储的是一篇篇的文章,文章中的常见词、语气词等等,出现的肯定比较多,搜索这些词语就没什么意义了,需要搜索的是那些文章中有特殊意义的词,这样才能把文章区分开。

    2:布尔全文索引

    在布尔搜索中,我们可以在查询中自定义某个被搜索的词语的相关性,当编写一个布尔搜索查询时,可以通过一些前缀修饰符来定制搜索。

    MySQL 内置的修饰符,上面查询最小搜索长度时,搜索结果 ft_boolean_syntax 变量的值就是内置的修饰符,下面简单解释几个,更多修饰符的作用可以查手册

    1:+ 必须包含该词

    2: -必须不包含该词

    3: 提高该词的相关性,查询的结果靠前

    4: 降低该词的相关性,查询的结果靠后

    5: (*)星号 通配符,只能接在词后面

    对于上面提到的问题,可以使用布尔全文索引查询来解决,使用下面的命令,a、aa、aaa、aaaa 就都被查询出来了。

    select * test where match(content) against('a*' in boolean mode);

    五:注意

    1:使用全文索引前,搞清楚版本支持情况;

    2: 全文索引比 like + % 快 N 倍,但是可能存在精度问题;

    3:如果需要全文索引的是大量数据,建议先添加数据,再创建索引;

    4:对于中文,可以使用 MySQL 5.7.6 之后的版本,或者第三方插件。

    以上大概是全文索引的全部内容。

    有好的建议,请在下方输入你的评论。

    欢迎访问个人博客
    https://guanchao.site

    相关文章

      网友评论

          本文标题:mysql索引(八)全文索引

          本文链接:https://www.haomeiwen.com/subject/vfmlmltx.html