如何使用mysql的全文索引搜索

如何使用mysql的全文索引搜索,第1张

你有没有想过如何使用搜索功能在所有整站中实现!互联网博客和网站,大多数都采用MySQL数据库。MySQL提供了一个美妙的方式实施一个小的搜索引擎,在您的网站(全文检索)。所有您需要做的是拥有的MySQL 4.x及以上。MySQL提供全文检索功能,我们可以用它来 ??实现搜索功能。

首先,让我们为我们的例子中设置一个示例表。我们将创建一个名为第一个表。

CREATE TABLE articles (

id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

body TEXT,

FULLTEXT (title,body)

)

在此表中还可以添加一些示例数据。执行后,插入查询。

INSERT INTO articles (title,body) VALUES

('MySQL Tutorial','DBMS stands for DataBase ...'),

('How To Use MySQL Well','After you went through a ...'),

('Optimizing MySQL','In this tutorial we will show ...'),

('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),

('MySQL vs. YourSQL','In the following database comparison ...'),

('MySQL Security','When configured properly, MySQL ...')

一旦样本数据是准备好,我们可以开始我们的全文检索功能。

自然语言全文搜索

尝试我们的示例表上执行下面的SELECT查询。

SELECT * FROM articles

WHERE MATCH (title,body) AGAINST ('database')

你就能看到结果如下:

在下面的数据库比较5 MySQL与YourSQL的...

MySQL教程DBMS 1代表数据库...

我们在上面的SQL查询(标题,正文)反对(“数据库”)的比赛,选择所有的记录,列标题和正文进行全文搜索。

您可以修改该查询,并创建您自己的版本,以自己的数据库中执行全文搜索。

布尔全文搜索

它可能发生,你要指定某些关键字在您的搜索条件。此外,您可能要忽略某些关键字。布尔全文搜索可以用来执行这些要求的全文检索。

检查下面的SELECT查询。

SELECT * FROM articles WHERE MATCH (title,body)

AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE)

如果您发现上述选择查询,我们增加了布尔MODE反对()。这个查询将获取MySQL的关键字,但不YourSQL关键字的所有记录。请注意+和-我们以前指定的关键字!

在执行此功能,MySQL使用什么有时也被称为布尔逻辑作为暗示,其中:+代表与-代表不是[无 *** 作员]暗示或

以下是几个例子布尔搜索条件。

苹果香蕉

查找行至少包含两个词之一。

“+苹果+果汁”

寻找包含两个单词的行。

“+苹果Macintosh

查找行包含“苹果”,但排名的行,如果它们也包含“麦金塔”。

“+苹果Macintosh的”

查找行包含“苹果”这个词,而不是“麦金塔”。

'+苹果Macintosh的“

查找包含单词“苹果”的行,但如果该行也包含单词“麦金塔”,速度比如果行不低。这是“软”比“+苹果Macintosh电脑”,为“麦金塔”的存在,导致该行不能在所有返回的搜索。

'+苹果+(>营业额<馅饼)“

行包含“苹果”和“营业额”,或“苹果”和“馅饼”(任何顺序)的话,但排名“苹果的营业额”比“苹果馅饼“。

限制

支持全文检索的MyISAM表只。MySQL 4.1中,使用多个字符设置一个单一的表内的支持。然而,在一个FULLTEXT索引的所有列,必须使用相同的字符集和校对规则。MATCH()列列表必须匹配完全在一些列清单表的FULLTEXT索引定义,除非这场比赛()是在布尔模式。布尔模式搜索,可以做非索引列,虽然他们很可能是缓慢的。

众所周知, MySQL的驱动表与被驱动表是优化器自动优化选择的结果 (与表连接的前后顺序等无关),我们可以用explain执行计划来知晓:

如上所示,前面一行t1是驱动表,后面一行t2是被驱动表。那么驱动表与被驱动表的选择是否有规律可循呢?下面是百度搜索两个主流的博文对驱动表与被驱动表的阐释:

1. MySQL连接查询驱动表被驱动表以及性能优化 - 阿伟~ - 博客园 博文A 主要结论:

2. mysql驱动表与被驱动表及join优化_java小小小黑的博客-CSDN博客_mysql驱动表和被驱动表 博文B 其主要结论:

两个帖子的结论是都差不多,而且还给出了例子来佐证。那么网上的结论是否权威?是否有普遍性?是否存在缺陷?

让我们来一起打破砂锅问到底。下面有两张表结构一模一样的表t1,t2:其中t1 100条数据,t2 1000条数据;t1(t2)结构如下:

按照上面博文的结论,left join左边是t2表,应该是驱动表。我们查看下结果:

与 博文B 中观点1相违背(同理观点2也违背),与实际不符,但究竟这是为什么呢?

下面发一张MySQL的执行过程(来源于《MySQL实战45讲》中01讲【一条SQL查询语句是如何执行的】)

so die si ne,原来sql执行的过程是这样呀。等等,不对,这跟刚才SQL又有什么关系,上面left join中t2表还是左边的呀。

我们知道MySQL高版本的性能越来越好,它是不断进行优化迭代的。远古的mysql版本可能还需要人工把小表放在前面,大表放在后面等这些需要人工调优的经验早就已经被解决了。也就是说我们写的语句,MySQL为了追求更好的效率,它在执行器执行前已经帮我们优化了。那么实际优化后的sql如何查看呢?用show warning命令:

其中Message就是优化后实际执行的sql语句,格式化后如下:

优化后left join左连接变成了内连接(inner) join。所以用优化后的sql看,表t1是小表所以作为驱动表,与实际结果相符。

left join 竟然优化成了join,太神奇了,但这是为什么呢?原因在于mysql中null与任何值做等值或者不等值比较的时候都是null,即使是select null=null 也是null。这样where 条件t1.a=t2.a查询条件不会包含t2.a为NULL的行,实际效果其实跟join一样,被优化器智能的优化了。

我们直接看执行计划看实际结果吧:

结果显示t2是驱动表,t1是被驱动表。t2是1000条数据按理说是大表应该是被驱动表,与 博文A , 博文B 的结论又不一致了。

《MySQL实战45讲》中34讲【到底可不可以使用join】已经讲的很透彻了,很深入了,我就不在这里献丑了。啰嗦几句大概就是驱动表是全表扫描不走索引,所以选被驱动表t1可以走索引,不会全表扫描,减少IO次数,性能高。里面对大表小表的总结,简直是精髓,特意在此再次着重强调:

在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与join的各个字段的总数据量,数据量小的那个表,就是“小表”,应该作为驱动表。

按照上面分析,我们先独立思考下MySQL会选择哪张表作为驱动表呢?

表t1,t2在字段a上都有索引不会全表扫描,其中t1.a=5条件过滤后只有一条,很显然嘛,t1数据量少是小表,肯定是驱动表,错不了,再说了前面的红色粗体已经强调了,不会有错的。

有冇搞错?事实又被打脸了。还记得在开篇我们说过的mysql优化器会对sql语句进行优化的吗?下面我们看下执行计划与优化的sql语句:

格式化后的优化SQL如下:

优化后两表t1,t2都走索引,并且都只有一条结果返回,因此都只会扫描一行,数据量一样,所以谁在前面谁就是驱动表,也就是上面sql中表t2。一切都释然,豁然开通!

回头再仔细想想,高,实在是高!仔细深思之后MySQL优化后的句子真让人猛拍大腿。高明之处在于:

1. 本来join连接是个M*N的嵌套循环,优化后变成了M+N的判断,两表不再嵌套判断了。

2. 优化后,两表没有多大必然联系,只需把两表的结果集拼接即可,互不干扰。如果mysql未来可以多线程查询,岂不十分快哉!

小伙伴们还记得我们在上一章 MySQL索引初探 中编码类型不一致发生隐式转换时有时候走索引,有时候索引又失效的问题吗?下面我们选取有代表性的一条记录来分析:

其中表demo_test总共有640条数据,demo_test_ass有3条数据。显然经过过滤条件t.rid>1完成后demo_test_ass数据量小,应该作为驱动表。虽然test.c_utf8mb4 = t.c2两字段连接中发生了t.c2字段发生隐式转换,但是实际上并不影响被驱动表test上的c_utf8mb4索引。

好了,本章到此结束,让我们一起 总结一下MySQL驱动表与被驱动表的选取原则

หน ง 同等条件,优先选取有索引的表作为被驱动表。 在此介绍一下什么叫同等条件,比如上面的②中的语句。 两表没有其他额外的过滤条件,因此选关联字段有索引的t1作为被驱动表。但是如果加了条件(and t1.id=3),此时t1数据量少,就选取了t2作为被驱动表。

สอง MySQL选择驱动表与被驱动表是基于优化器优化后的,小表是驱动表,大表是被驱动表。 基于优化器优化后开篇的 博文A与B 结论成立。

当然这都是我一家之言,并不是官方结论,目前暂未找到官方确切对于驱动表与被驱动表的解释,请大家踊跃拍砖!


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/7445659.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-04-05
下一篇 2023-04-05

发表评论

登录后才能评论

评论列表(0条)

保存