数据库索引[下]

发表于 | 分类于 |
字数统计: 974 字 | 阅读时长 ≈ 3 min

参考：http://cmsblogs.com/?p=5469

https://blog.csdn.net/mrzhouxiaofei/article/details/79940958

上一篇主要介绍了自增约束、聚簇索引也就是主索引、以及索引的物理结构，一开始说索引分类的时候有提到全文索引和组合索引

全文索引

通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。

你可能会说，用 like + % 就可以实现模糊匹配了，为什么还要全文索引？like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。

你可能没有注意过全文索引，不过至少应该对一种全文索引技术比较熟悉：各种的搜索引擎。虽然搜索引擎的索引对象是超大量的数据，并且通常其背后都不是关系型数据库，不过全文索引的基本原理是一样的。

版本支持

开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。

创建删除全文索引

全文索引的关键字为fulltext

创建

创建表时创建全文索引

1
2
3
4
5
6
CREATE TABLE article ( 
                  id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
                  title VARCHAR(200), 
                  body TEXT, 
                  FULLTEXT(title, body) 
              ) TYPE=MYISAM;

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

1
2
3
4
CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`)
#也可以在创建索引的时候指定索引的长度：

CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`(20))

通过 SQL 语句 ALTER TABLE 创建全文索引

1
2
3
4
ALTER TABLE `student` ADD FULLTEXT INDEX ft_stu_name  (`name`) 
#ft_stu_name是索引名，可以随便起

#或者：ALTER TABLE `student` ADD FULLTEXT ft_stu_name  (`name`)

修改

修改个 O，直接删掉重建。

删除

直接使用 DROP INDEX 删除全文索引

1
2
drop index content_tag_fulltext
    on fulltext_test;

通过 SQL 语句 ALTER TABLE 删除全文索引

1
2
alter table fulltext_test
    drop index content_tag_fulltext;

使用全文索引

如何创建比较简单，这里就不说了，来看一下怎么使用

全文索引的使用方式一般为使用 MATCH()... AGAINST

使用的时候感觉索引名根本没什么用，因为语法是这样的：

select * from tb_name where match(col_name)against('a')

另外插一句，使用全文索引时，测试表里至少要有 4 条以上的记录，否则，会出现意想不到的结果。

缺陷

  • MySql自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx（斯芬克斯）/Coreseek技术来处理中文。

  • 数据表越大，全文索引效果好，比较小的数据表会返回一些难以理解的结果。

  • 只有由字母，数字，单引号，下划线构成的字符串被认为是单词，带注音符号的字母仍是字母，像C++不再认为是单词，且不区分大小写
  • 全文索引创建速度慢，而且对有全文索引的各种数据修改操作也慢