mysql 全文索引学习笔记详解
全文索引是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 手册上面的一个例子,代码如下:
- CREATETABLEarticles(
- idINTUNSIGNEDAUTO_INCREMENTNOTNULLPRIMARYKEY,
- titleVARCHAR(200),
- bodyTEXT,
- FULLTEXT(title,body)
- );
上面这是创建表的 mysql 语句， 其中最后一句 FULLTEXT (title,body)
就是为 title 和 body 创建一个 全文搜索,也就是 以后 方便搜索 标题 和标题正文的内容,复制语句上去 就成功创建一张表了.
查看一下 全文搜索 是否被创建,下面语句作用是:查看表的 主键,索引,全文搜索.
show indexes from 表名,代码如下:
- mysql>showindexesfromarticles;
- +----------+------------+----------+--------------+-------------+
- |Table|Non_unique|Key_name|Seq_in_index|Column_name|
- +----------+------------+----------+--------------+-------------+
- |articles|0|PRIMARY|1|id|
- |articles|1|title|1|title|
- |articles|1|title|2|body|
- +----------+------------+----------+--------------+-------------+
- 3rowsinset(0.01sec)
可以看到 成功创建了,Key_name名字叫做 title,它的字段列名Column_name是 title 和 body.
下面为表添加数据内容,好让我们进行测试,代码如下:
- INSERTINTOarticles(title,body)VALUES
- ('MySQLTutorial','DBMSstandsforDataBase...'),
- ('HowToUseMySQLWell','Afteryouwentthrougha...'),
- ('OptimizingMySQL','Inthistutorialwewillshow...'),
- ('1001MySQLTricks','1.Neverrunmysqldasroot.2....'),
- ('MySQLvs.YourSQL','Inthefollowingdatabasecomparison...'),
- ('MySQLSecurity','Whenconfiguredproperly,MySQL...');
- mysql>select*fromarticles;
- +----+-----------------------+------------------------------------------+
- |id|title|body|
- +----+-----------------------+------------------------------------------+
- |1|MySQLTutorial|DBMSstandsforDataBase...|
- |2|HowToUseMySQLWell|Afteryouwentthrougha...|
- |3|OptimizingMySQL|Inthistutorialwewillshow...|
- |4|1001MySQLTricks|1.Neverrunmysqldasroot.2....|
- |5|MySQLvs.YourSQL|Inthefollowingdatabasecomparison...|
- |6|MySQLSecurity|Whenconfiguredproperly,MySQL...|
- +----+-----------------------+------------------------------------------+
- 6rowsinset(0.00sec)--phpfensi.com
添加好数据库,下面我们就使用 全文搜索 提供的 查询语句 进行测试.
使用语句的模版如下:
SELECT 表字段 FROM 表名 WHERE MATCH (全文搜索表字段) AGAINST ('搜索字符串');
下面 搜索 title 和 body 包含 database 这个字符串,代码如下:
- mysql>SELECT*FROMarticles
- ->
- ->WHEREMATCH(title,body)AGAINST('database');
- +----+-------------------+------------------------------------------+
- |id|title|body|
- +----+-------------------+------------------------------------------+
- |5|MySQLvs.YourSQL|Inthefollowingdatabasecomparison...|
- |1|MySQLTutorial|DBMSstandsforDataBase...|
- +----+-------------------+------------------------------------------+
- 2rowsinset(0.00sec)
MATCH 相当于要找的列,而 AGAINST 就是要找的内容,比起 like 有点不一样,而且 match ... against 还提供很多 操作,对数据 进一步过滤.
一般可以作为 比较精确的搜索,例如下面的这个例子,搜索title和body中包含 MySQL ,但是不能有 YourSQL 的结果,代码如下:
- SELECT*FROMarticlesWHEREMATCH(title,body)
- AGAINST('+MySQL-YourSQL'INBOOLEANMODE);
- ------------------
- |5|MySQLvs.YourSQL|Inthefollowingdatabasecomparison...|
你会发现 可以看到 这个结果被过滤掉了,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);
看到了吧,这样就通过修改现有的表来添加全文搜索功能,希望这个教程可以帮你日后使用到这个搜索功能.
热门评论