MySQL全文索引怎么创建

落日映苍穹つ 2023-09-25 09:59 20阅读 0赞

## mysql全文索引 很慢，速度不如like的百分之一 ##

从explain开始说起吧，很显然第一个sql语句压根没用任何索引(key列内什么都没有)！第二个倒是用到索引，却是主键索引，并非你添加的fulltext索引！

接下来，分析下原因：

sql1：执行步骤：先s\_a和s\_a\_t两表笛卡尔集，然后筛选满足on条件的，接着在从结果集中筛选满足where字句的；该过程中处理的记录条目为69\*105479，并且未用到任何索引，未用到的原因可能是你先定义了一个复合索引a\_concent\_split(a\_title\_split，a\_content\_split)，然后又定义了一个a\_content\_split2(a\_content\_split)，当引擎执行查找优化时候会先用到a\_content\_split，可是又由于复合索引是从最左边开始(不能跳过第一个字段)，而你却忽略了a\_title\_split字段，故未能正常使用索引。

sql2：执行步骤：先调用where字句对s\_a表进行筛选形成新的s\_a表，然后与s\_a\_t表笛卡尔积，再利用on字句筛选，最后再次利用where字句形成最终结果集；经过第一个where，该过程处理结果集会大幅少于sql1，并且该过程还用到了主键索引。你所设置的fulltext索引再次没有用到，原因是like字句中开始部分为模糊匹配%时候用不了全文索引，这与fulltext存储机制有关。

另，你说的删除速度慢，原因：设置fulltext字段设置太多，fulltext索引在更新删除大量数据时候，需要同步更改索引，你的三个fulltext压力太大！

改进方法：1、删除a\_content\_split索引重试2、在删除时候打开delay\_key\_write变量

有关fulltext比较复杂，用的时候要谨慎设置，还有很多参数也对其有影响

另外sql语句中外连接有关onwhere字句也是个比较绕的地方，两者你都占了，唉，所以我写的略复杂，前天看到该问题，思忖两天这才作答

望有结果了予以回复交流！

好的，这个问题我放一下，明后天在处理，今天还有其他事情要做，

谢谢你，这事好歹有点眉路了，

结果一定告诉你

![MySQL全文索引怎么创建\_mysql全文索引][MySQL_mysql]

## MySQL全文索引 FULLTEXT索引和like的区别 ##

fulltext，其实是一个索引，like语句很难用索引，所以提升了效率。

fulltext，支持多个字段检索。

搜索功能一般都用带有中文分词的开源产品，像xunsearch。

一般小项目用like就行了

## MySQL全文索引 FULLTEXT索引和like的区别 ##

LIKE搜索的耗时随着记录数的增加而线性增长，但对于10万行记录以下的表(这里共100000\*50个单词)搜索时间基本上能保持在1秒以内，所以like搜索的性能也不是特别差。由不同词汇量生成的文本对LIKE搜索的性能影响不大，不同词汇量对应的搜索时间基本上在一个很小的时间范围内变化。

FULLTEXT搜索耗时也随表中记录数的增长而线性增加。对于10万行记录以下的表(这里共100000\*50个单词)搜索时间基本上能保持在0.01秒以内。由不同词汇量生成的随机文本对FULLTEXT搜索性能有相对来说比较显著的影响。每行记录中含同样的单词数，这样，较大的词汇量倾向于生成冗余度更低的文本，相应的搜索耗时倾向于更少。这可能与FULLTEXT索引建立单词索引的机制有关，较大的词汇量倾向于生成范围广但相对较浅的索引，因而能快速确定文本是否匹配。

与LIKE搜索相比，FULLTEXT全文搜索的性能要强很多，对于10万行记录的表，搜索时间都在0.02秒以下。因此可以将基于FULLTEXT索引的文本搜索部署于网站项目中的文本搜索功能中。但是，正如上述提到的，无论是LIKE搜索还是FULLTEXT搜索，其性能都会随着记录数的增长而下降，因此，若网站项目中的文本搜索数据库记录数庞大的一定规模后，可能需要考虑使用MySQL数据库全文搜索以外的文本搜索解决方案了。mysql中给text字段创建索引有以下两种方法:

(前提是表引擎应该是myisam的)

1、建表时创建，语法为：

2、通过alter修改表结构，语法为：

使用方法：

select note\_text from tablename

where match(note\_text) against('rabbit') //检索note\_text字段中包含'rabbit'的行

![MySQL全文索引怎么创建\_mysql全文索引\_02][MySQL_mysql_02]

## 怎么MySql添加全文索引 ##

使用索引是数据库性能优化的必备技能之一。在MySQL数据库中，有四种索引：聚集索引(主键索引)、普通索引、唯一索引以及我们这里将要介绍的全文索引(FULLTEXT INDEX)。

全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用「分词技术「等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。在这里，我们就不追根究底其底层实现原理了，现在我们来看看在MySQL中如何创建并使用全文索引。

在MySQL中，创建全文索引相对比较简单。例如，我们有一个文章表(article)，其中有主键ID(id)、文章标题(title)、文章内容(content)三个字段。现在我们希望能够在title和content两个列上创建全文索引，article表及全文索引的创建SQL语句如下：

\--创建article表

CREATE TABLE article (

id INT UNSIGNED AUTO\_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

content TEXT,

FULLTEXT (title, content) --在title和content列上创建全文索引

上面就是在创建表的同时建立全文索引的SQL示例。此外，如果我们想要给已经存在的表的指定字段创建全文索引，同样以article表为例，我们可以使用如下SQL语句进行创建：

\--给现有的article表的title和content字段创建全文索引

\--索引名称为fulltext\_article

ALTER TABLE article

ADD FULLTEXT INDEX fulltext\_article (title, content)

在MySQL中创建全文索引之后，现在就该了解如何使用了。众所周知，在数据库中进行模糊查询是使用LIKE关键字进行查询，例如：

SELECT \* FROM article WHERE content LIKE '%查询字符串%'

那么，我们使用全文索引也是这样用的吗？当然不是，我们必须使用特有的语法才能使用全文索引进行查询。例如，我们想要在article表的title和content列中全文检索指定的查询字符串，可以如下编写SQL语句：

SELECT \* FROM article WHERE MATCH(title, content) AGAINST('查询字符串')

强烈注意：MySQL自带的全文索引只能用于数据库引擎为MyISAM的数据表，如果是其他数据引擎，则全文索引不会生效。此外，MySQL自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。本站将会在后续文章中对Sphinx以及Coreseek进行介绍。

备注1：目前，使用MySQL自带的全文索引时，如果查询字符串的长度过短将无法得到期望的搜索结果。MySQL全文索引所能找到的词的默认最小长度为4个字符。另外，如果查询的字符串包含停止词，那么该停止词将会被忽略。

备注2：如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。你有没有想过如何使用搜索功能在所有整站中实现！互联网博客和网站，大多数都采用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电脑”，为“麦金塔”的存在，导致该行不能在所有返回的搜索。 '+苹果+(>营业额

[MySQL_mysql]: https://img-blog.csdnimg.cn/img_convert/944a840f68be2a8d02e1d53a34a744a2.png
[MySQL_mysql_02]: https://img2.baidu.com/it/u=3789509475,4225200043&fm=26&fmt=auto