全文索引是mysql中一个比较特殊的功能,下面我来给大家介绍我自己在学习mysql全文索引的一些笔记吧,有需要了解的同学可进入参考.

场景:需要做一个关于标题的模糊查询,只是记录有点多,而且需要相对精确,比如搜索:ac,不能出现abc,可以接受acb,bac,之类.

mysql全文搜索有三种模式:

一、自然语言查找,这是mysql默认的全文搜索方式,sql示例:

[code=plain],代码如下:

select  id,title FROM post WHERE MATCH(content) AGAINST ('search keyword')

或者显式声明使用自然语言搜索方式

[code=plain],代码如下:

select  id,title FROM post WHERE MATCH(content) AGAINST ('search keyword' IN NATURAL LANGUAGE MODE)

测试:

1、100万数据,mysql / mongo,在这种情况下,无论是查询什么数据,基本上都在0.00x秒级,mysql的查询是like '%xxxx%' , mongo 是 {title:/xxxx/i}

一般情况下,两者速度真心差不多,但如果查询一下不数据库中不存在的关键字,一般都在0.2秒至2秒左右,mongo会相对好一点,在0.5秒.

2、500万～1000万数据

查询条件如上

mysql 查询的时候 cpu 占40%左右，20多秒 （mysql 1100万数据）

mongo 查询的时候 CPU占50%左右，10秒/8秒左右 （mongo 550万）

这种性能没法用啊.

---下一步

1、xunsearch / coreseek(sphinx)

2、mysql 全文索引

需要再次测试一下,关键mysql虽然100万只有0.00x或者0.0x秒左右,但是如果多个并发的时候就会卡死了.

所以需要再次考虑场景的复杂性,继昨天的处理之后,又来新的笔记,这次的笔记纯粹是个人的测试,与实际条件有关,比如,我要查询的字段不超过varchar的255的长度,所以我才这么做.

昨天做普通索引后,1100万条记录,索引 为220M,改成全文索引后,索引文件为1.1G,存储空间上,涨了5倍左右.

以下是笔记,请不要笑话,场景不同而已.

•经过测试

•title 字段改为全文索引后，在1100万条的时候

•优点:

•速度也为0.0x秒级,速度非常快

•即使有or条件,只要带了limit参数,速度也非常快.

•缺点:

•如果查询不带limit,直接卡死,因为他要计算total count

•select count() 卡死

•如果查询不存在的关键字,卡死

•使用方法 

•尽量不做select count 查询 （数量低于100万时可以考虑，超过100万时，其实已经没有必要）

•查询一定要带上limit条件

•每次查询到不存在的关键字时，记录到关键词库，每次有新增记录时，select 关键词库一下，如果新增房间中有关键字，则将关键词去除，避免卡死

•暂时不使用coreseek（sphinx）/xunsearch等第三方工具

•xunsearch只支持分词查询,不支持完全匹配.

•第三方工具,耗内存,而且增量的时候,不够及时.

费话说了一大篇下面进入测试:

一个SELECT查询中的LIKE语句来执行这种查询,尽管这种方法可行,但对于全文查找而言,这是一种效率极端低下的方法,尤其在处理大量数据的时候.

上面这句话 我是在网上看到的,说得挺有道理的,mysql 本身提供了一种叫做全文搜索的技术吧,不过这好像从后来的版本才有的,比较旧的版本不支持,不过那是很早期的版本了,现在大家使用的版本 应该都支持的,我现在使用 mysql6.0.4 来演示.

全文搜索 比起 索引 我觉得更加全面,索引只对某一个字段,然后在查询时候使用 like 配合.

全文搜索它可以设置多个字段进行搜索,可以说是比起 select .... like 高级吧,好了,既然 全文搜索 有这样的优点,下面我们来看看 是否真的如此.

下面提供的测试例子,是 mysql 手册上面的一个例子,代码如下:

1. CREATE TABLE articles ( 
2.    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 
3.    title VARCHAR(200), 
4.     body TEXT, 
5.      FULLTEXT (title,body) 
6.    ); 

上面这是创建表的 mysql 语句， 其中最后一句  FULLTEXT (title,body)

就是为 title 和 body 创建一个 全文搜索,也就是 以后 方便搜索 标题 和标题正文的内容,复制语句上去 就成功创建一张表了.

查看一下 全文搜索 是否被创建,下面语句作用是:查看表的 主键,索引,全文搜索.

show indexes from 表名,代码如下:

1. mysql> show indexes from articles; 
2. +----------+------------+----------+--------------+-------------+ 
3. | Table    | Non_unique | Key_name | Seq_in_index | Column_name | 
4. +----------+------------+----------+--------------+-------------+ 
5. | articles |          0 | PRIMARY  |            1 | id          | 
6. | articles |          1 | title    |            1 | title       | 
7. | articles |          1 | title    |            2 | body        | 
8. +----------+------------+----------+--------------+-------------+ 
9. 3 rows in set (0.01 sec) 

可以看到 成功创建了,Key_name名字叫做 title,它的字段列名Column_name是 title 和 body.

下面为表添加数据内容,好让我们进行测试,代码如下:

1. INSERT INTO articles (title,body) VALUES 
2.     ('MySQL Tutorial','DBMS stands for DataBase ...'), 
3.     ('How To Use MySQL Well','After you went through a ...'), 
4.     ('Optimizing MySQL','In this tutorial we will show ...'), 
5.     ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), 
6.     ('MySQL vs. YourSQL','In the following database comparison ...'), 
7.     ('MySQL Security','When configured properly, MySQL ...'); 
8. mysql> select * from articles; 
9. +----+-----------------------+------------------------------------------+ 
10. | id | title                 | body                                     | 
11. +----+-----------------------+------------------------------------------+ 
12. |  1 | MySQL Tutorial        | DBMS stands for DataBase ...             | 
13. |  2 | How To Use MySQL Well | After you went through a ...             | 
14. |  3 | Optimizing MySQL      | In this tutorial we will show ...        | 
15. |  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      | 
16. |  5 | MySQL vs. YourSQL     | In the following database comparison ... | 
17. |  6 | MySQL Security        | When configured properly, MySQL ...      | 
18. +----+-----------------------+------------------------------------------+ 
19. 6 rows in set (0.00 sec)  --phpfensi.com 

添加好数据库,下面我们就使用 全文搜索 提供的 查询语句 进行测试.

使用语句的模版如下:

SELECT 表字段 FROM 表名 WHERE MATCH (全文搜索表字段) AGAINST ('搜索字符串');

下面 搜索 title 和 body 包含 database 这个字符串,代码如下:

1. mysql> SELECT * FROM articles 
2.     -> 
3.     ->     WHERE MATCH (title,body) AGAINST ('database'); 
4. +----+-------------------+------------------------------------------+ 
5. | id | title             | body                                     | 
6. +----+-------------------+------------------------------------------+ 
7. |  5 | MySQL vs. YourSQL | In the following database comparison ... | 
8. |  1 | MySQL Tutorial    | DBMS stands for DataBase ...             | 
9. +----+-------------------+------------------------------------------+ 
10. 2 rows in set (0.00 sec) 

MATCH 相当于要找的列,而  AGAINST 就是要找的内容,比起 like 有点不一样,而且 match ... against 还提供很多 操作,对数据 进一步过滤.

一般可以作为 比较精确的搜索,例如下面的这个例子,搜索title和body中包含 MySQL ,但是不能有 YourSQL 的结果,代码如下:

1.  SELECT * FROM articles WHERE MATCH (title,body) 
2.     AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE); 
3. ------------------ 
4. |  5 | MySQL vs. YourSQL     | In the following database comparison ... | 

你会发现 可以看到 这个结果被过滤掉了,fulltext 还提供了 更加多的逻辑搜索,也就是一些 模糊搜索 等等,要求更加高的匹配字符搜索,大家可以上 mysql 官方网站查看.

上面 建立全文搜索 是在 建立表的同时建立的,如果你已经建好表了,但是想加入这个功能的话,可以使用下面语句:

mysql> alter table articles add fulltext index(title,body);

为了演示,我先把刚才的删除掉,代码如下:

mysql> drop index title on articles;

再查看一下,已经被删除了,代码如下:

mysql> show indexes from articles;

再为表添加 fulltext 上去,代码如下:

mysql> alter table articles add fulltext index(title,body);

看到了吧,这样就通过修改现有的表来添加全文搜索功能,希望这个教程可以帮你日后使用到这个搜索功能.

Tags: mysql全文索引 mysql索引

分享到：