Mysql表使用全文索引

从 Mysql 4.0 开始就支持全文索引功能

首先创建表

CREATE TABLE `fulltext_sample` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL DEFAULT '',
  `keywords` varchar(100) NOT NULL DEFAULT '',
  `content` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `content` (`content`)
) ENGINE=MyISAM AUTO_INCREMENT=55 DEFAULT CHARSET=utf8;
针对content列增加了全文索引，mysql的全文索引只对引擎为MyISAM的表才有效

向表中增加一些数据
INSERT INTO fulltext_sample(title,keywords,content) VALUES
('糖尿病如何防治？','糖尿病','It appears good from here,糖尿病'),
('心血管疾病患者如何做好自我防护','心血管','The here and the past，心血管疾病'),
('脑血管患者如何康复治疗','脑血管','Why are we hear，脑血管疾病'),
('有了皮肤病怎么办？','皮肤病','An all-out alert，皮肤病'),
('压力大亚健康如何调节？','亚健康','All you need is love，亚健康'),
('精神病患者的治疗','精神病','A good alert，精神病');

添加了数据之后需要用以下语句重新生成索引
repair table tablename quick 为有关数据表重新生成全文索引

如果需要修改索引列，可以先删除索引重新添加
alter table fulltext_sample drop index `content`;

//针对keywords增加全文索引
alter table fulltext_sample add fulltext index `fulltext_index` (`keywords`);

//全文索引查询语法

select * from fulltext_sample where id=1 and match(`keywords`) against('糖尿病');

//精确索引，上面这一条语句查不出来内容时可以试一下下面这一条语句，表示精确匹配

select * from fulltext_sample where id=1 and match(`keywords`) against('糖尿病' IN BOOLEAN MODE);

//使用全文索引与like查询的效率对比

mysql> explain select * from fulltext_sample where match(`keywords`) against('糖尿病');
+----+-------------+-----------------+----------+----------------+----------------+---------+------+------+-------------+
| id | select_type | table           | type     | possible_keys  | key            | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+----------+----------------+----------------+---------+------+------+-------------+
|  1 | SIMPLE      | fulltext_sample | fulltext | fulltext_index | fulltext_index | 0       | NULL |    1 | Using where |
+----+-------------+-----------------+----------+----------------+----------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from fulltext_sample where keywords like '%糖尿病%';
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | fulltext_sample | ALL  | NULL          | NULL | NULL    | NULL |   54 | Using where |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

可以看出like查询基本上是查询的所有记录，这在超过万条记录时，效率会相当糟糕的，而全文索引则不会有这个问题。

从 Mysql 4.0 开始就支持全文索引功能，但是 Mysql 默认的最小索引长度是 4。如果是英文默认值是比较合理的，但是中文绝大部分词都是2个字符，这就导致小于4个字的词都不能被索引，全文索引功能就形同虚设了。
在my.cnf中设置为2个中文字符，一个词一般至少两个中文
ft_min_word_len=2