mysql 全文索引

1.将字段改为支持全文索引

创建表时设置

CREATE TABLE if not exists `fulltext_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `title` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '标题',
  `content` text CHARACTER SET utf8 COMMENT '内容',
  PRIMARY KEY (`id`),
  FULLTEXT KEY `my_title_fc` (`title`) WITH PARSER ngram,
  FULLTEXT KEY `my_content_fc` (`content`) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

修改已有字段

alter table `fulltext_table` add fulltext `my_title_fc` (`title`) WITH PARSER ngram;
alter table `fulltext_table` add fulltext `my_content_fc` (`content`) WITH PARSER ngram;

一百万数量的表创建一个全文索引耗时大概50~60秒。

2.查询权重

select *,MATCH(content) AGAINST(?) as weights from fulltext_table
-- 过滤掉不匹配的记录
where  MATCH(content) AGAINST(? IN BOOLEAN MODE)
--按权重倒序
 order by weights desc

以上两个?参数皆为字符串，且内容应保持一致。

检索方式
1、自然语言检索： IN NATURAL LANGUAGE MODE

2、布尔检索： IN BOOLEAN MODE
剔除一半匹配行以上都有的词，譬如说，每个行都有this这个字的话，那用this去查时，会找不到任何结果，这在记录条数特别多时很有用，原因是数据库认为把所有行都找出来是没有意义的，这时，this几乎被当作是stopword(中断词)；但是若只有两行记录时，是啥鬼也查不出来的，因为每个字都出现50%（或以上），要避免这种状况，请用IN BOOLEAN MODE。
搜索语法规则：

        +   一定要有(不含有该关键词的数据条均被忽略)。 
        -   不可以有(排除指定关键词，含有该关键词的均被忽略)。 
        >   提高该条匹配数据的权重值。 
        <   降低该条匹配数据的权重值。
        ~   将其相关性由正转负，表示拥有该字会降低相关性(但不像 - 将之排除)，只是排在较后面权重值降低。 
        *   万用字，不像其他语法放在前面，这个要接在字符串后面。 
        " " 用双引号将一段句子包起来表示要完全相符，不可拆字。

3.注意编码问题

上面我们设置了字段编码为utf8，所以数据库连接语句也应该添加上&characterEncoding=utf-8,否则查询中文会匹配不上。

4.性能比较

在100万记录里面匹配出65万记录时

count 查询
1.like 耗时8秒

 select  count(1) from base_person_0 bp  where   registered_place  like '%广州%' order by id

2.全文索引耗时3秒

 select count(1)  from base_person_0 where  MATCH(registered_place) AGAINST('广州' IN BOOLEAN MODE) order by id

分页查询
1.like 耗时300ms

 select  * from base_person_0 bp  where   registered_place  like '%广州%' order by id limit 10000,20

2.全文索引耗时14秒

 select *  from base_person_0 where  MATCH(registered_place) AGAINST('广州' IN BOOLEAN MODE) order by id limit 10000,20