[大数据]数据库索引 ( 三 ) MySQL全文索引

2.4.全文索引

查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL5.6开始MyISAM和InnoDB存储引擎均支持。

创建全文索引的方法如下：

CREATE FULLTEXT index 索引名 on 表名(字段名);  -- 默认只能进行英文的模糊查询
CREATE FULLTEXT index 索引名 on 表名(字段名) with parser ngram;  -- 带有中文分词器的全文索引,


ALTER TABLE 表名 ADD FULLTEXT [索引的名字] (字段名);
CREATE TABLE 表名 ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

-- 例如:
CREATE FULLTEXT index index_student_name on student(student_name) with parser ngram;

和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如

select * from user
where match(name) against('aaa');

2.4.1.实例

无索引 查询 student_name(姓名) = ‘%东方%’ 的记录, 用时 4244 ms

注意这里 查询 没有分页

 Consume Time：4244 ms 2022-10-26 22:04:53
 Execute SQL：select student_id,student_name,student_enrollmenttime, student_tel,stu.education_id,student_weight, student_bloodtype,student_sex , student_height , edu.education_name , case student_sex when 1 then '男' when 0 then '女' else '不清楚' end student_sex_name from student stu left join education edu on stu.education_id = edu.education_id 
WHERE student_name like concat('%', '东方' ,'%')

2.4.1.1.创建索引

CREATE FULLTEXT index index_student_name on student(student_name) with parser ngram;

成功 : 用时 23.886s

[SQL]CREATE FULLTEXT index index_student_name on student(student_name) with parser ngram;
受影响的行: 0
时间: 23.886s

2.4.1.2.修改查询语句

select * from student
where match( student_name ) against( '东方' );

2.4.1.3.再测试

查询 , 用进 60 ms

 Consume Time：60 ms 2022-10-26 22:14:39
 Execute SQL：select student_id,student_name,student_enrollmenttime, student_tel,stu.education_id,student_weight, student_bloodtype,student_sex , student_height , edu.education_name , case student_sex when 1 then '男' when 0 then '女' else '不清楚' end student_sex_name from student stu left join education edu on stu.education_id = edu.education_id
 WHERE match( student_name ) against( '东方' )

2.4.2.分词设置

使用全文索引一定要注意中文分词的个数, 默认的个数是2, 即模糊查询的字符串必须是2个字以上.

打开mysql安装目录, 找到my.ini文件, 修改分词个数的属性即可

ngram_token_size=2

修改完毕后,需要重新启动mysql服务.

中文分词器的原理

例如: “新年快乐”, 如果安装不同的分词个数拆字的话,如下

如果分词个数为2, 所以模糊查询中,带有"新年", “年快”, "快乐"的都是结果

项目里应该如何实用全文索引

<if test="studentName != null and studentName != '' and studentName.length == 1">
    and student_name like concat('%',#{studentName},'%')
</if>
<if test="studentName != null and studentName != '' and studentName.length > 1">
    and match(student_name) against(#{studentName})
</if>