mysql 全文索引学习笔记详解

昝辉Zac Zac的SEO博客,坚持12年,优化成为生活。

全文索引是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. CREATETABLEarticles(
  2. idINTUNSIGNEDAUTO_INCREMENTNOTNULLPRIMARYKEY,
  3. titleVARCHAR(200),
  4. bodyTEXT,
  5. FULLTEXT(title,body)
  6. );

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

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

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

show indexes from 表名,代码如下:

  1. mysql>showindexesfromarticles;
  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. 3rowsinset(0.01sec)

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

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

  1. INSERTINTOarticles(title,body)VALUES
  2. ('MySQLTutorial','DBMSstandsforDataBase...'),
  3. ('HowToUseMySQLWell','Afteryouwentthrougha...'),
  4. ('OptimizingMySQL','Inthistutorialwewillshow...'),
  5. ('1001MySQLTricks','1.Neverrunmysqldasroot.2....'),
  6. ('MySQLvs.YourSQL','Inthefollowingdatabasecomparison...'),
  7. ('MySQLSecurity','Whenconfiguredproperly,MySQL...');
  8. mysql>select*fromarticles;
  9. +----+-----------------------+------------------------------------------+
  10. |id|title|body|
  11. +----+-----------------------+------------------------------------------+
  12. |1|MySQLTutorial|DBMSstandsforDataBase...|
  13. |2|HowToUseMySQLWell|Afteryouwentthrougha...|
  14. |3|OptimizingMySQL|Inthistutorialwewillshow...|
  15. |4|1001MySQLTricks|1.Neverrunmysqldasroot.2....|
  16. |5|MySQLvs.YourSQL|Inthefollowingdatabasecomparison...|
  17. |6|MySQLSecurity|Whenconfiguredproperly,MySQL...|
  18. +----+-----------------------+------------------------------------------+
  19. 6rowsinset(0.00sec)--phpfensi.com

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

使用语句的模版如下:

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

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

  1. mysql>SELECT*FROMarticles
  2. ->
  3. ->WHEREMATCH(title,body)AGAINST('database');
  4. +----+-------------------+------------------------------------------+
  5. |id|title|body|
  6. +----+-------------------+------------------------------------------+
  7. |5|MySQLvs.YourSQL|Inthefollowingdatabasecomparison...|
  8. |1|MySQLTutorial|DBMSstandsforDataBase...|
  9. +----+-------------------+------------------------------------------+
  10. 2rowsinset(0.00sec)

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

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

  1. SELECT*FROMarticlesWHEREMATCH(title,body)
  2. AGAINST('+MySQL-YourSQL'INBOOLEANMODE);
  3. ------------------
  4. |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);

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

相关广告
  • mysql 全文索引学习笔记详解 mysql 全文索引学习笔记详解 mysql 全文索引学习笔记详解
相关阅读

mysql 全文索引学习笔记详解

2019/10/10 17:35:02 | 谷歌SEO算法 | DNS