编程小贴士

给你的编程提供小点子


MYSQL怎么创建全文索引,怎么使用全文索引

好多mysql全文索引的文章都是在讨论怎么使用mysql的中文全文索引,mysql全文索引对中文是很弱的。本文不讨论如何对中文进行支持,本文就是描述mysql的全文索引是什么,如何创建,如何使用,甚至在末尾告诉你如何调优,希望对你帮助。

文索引在 MySQL 中是一个 FULLTEXT 类型索引。FULLTEXT 索引用于 MyISAM 表,可以在 CREATE TABLE 时或之后使用 ALTER TABLE 或 CREATE INDEX 在 CHAR、VARCHAR 或 TEXT 列上创建。对于大的数据库,将数据装载到一个没有 FULLTEXT 索引的表中,然后再使用 ALTER TABLE (或 CREATE INDEX) 创建索引,这将是非常快的。将数据装载到一个已经有 FULLTEXT 索引的表中,将是非常慢的。

全文搜索通过 MATCH() 函数完成。

mysql> CREATE TABLE articles (
->
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200
),
->
body TEXT,
->
FULLTEXT (title,body)
->
);
Query OK, 0 rows affected (0.00
sec)

mysql> INSERT INTO articles VALUES
-> (NULL,’MySQL Tutorial’, ‘DBMS stands for DataBase …’
),
-> (NULL,’How To Use MySQL Efficiently’, ‘After you went through a …’
),
-> (NULL,’Optimising MySQL’,’In this tutorial we will show …’
),
-> (NULL,’1001 MySQL Tricks’,’1. Never run mysqld as root. 2. …’
),
-> (NULL,’MySQL vs. YourSQL’, ‘In the following database comparison …’
),
-> (NULL,’MySQL Security’, ‘When configured properly, MySQL …’
);
Query OK, 6 rows affected (0.00
sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST (‘database’
);
+—-+——————-+——————————————+

| id | title | body |
+—-+——————-+——————————————+
| 5 | MySQL vs. YourSQL | In the following database comparison … |
| 1 | MySQL Tutorial | DBMS stands for DataBase … |
+—-+——————-+——————————————+
2 rows in set (0.00 sec)

函数 MATCH() 对照一个文本集(包含在一个 FULLTEXT 索引中的一个或多个列的列集)执行一个自然语言搜索一个字符串。搜索字符串做为 AGAINST() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行,MATCH() 返回一个相关性值。即,在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。

当 MATCH() 被使用在一个 WHERE 子句中时 (参看上面的例子),返回的记录行被自动地以相关性从高到底的次序排序。相关性值是非负的浮点数字。零相关性意味着不相似。相关性的计算是基于:词在记录 行中的数目、在行中唯一词的数目、在集中词的全部数目和包含一个特殊词的文档(记录行)的数目。

它也可以执行一个逻辑模式的搜索。这在下面的章节中被描述。

前面的例子是函数 MATCH() 使用上的一些基本说明。记录行以相似性递减的顺序返回。 下一个示例显示如何检索一个明确的相似性值。如果即没有 WHERE 也没有 ORDER BY 子句,返回行是不排序的。

mysql> SELECT id,MATCH (title,body) AGAINST (‘Tutorial’) FROM articles;
+—-+—————————————–+

| id | MATCH (title,body) AGAINST (‘Tutorial’) |
+—-+—————————————–+
| 1 | 0.64840710366884 |
| 2 | 0 |
| 3 | 0.66266459031789 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
+—-+—————————————–+
6 rows in set (0.00 sec)
下面的示例更复杂一点。查询返回相似性并依然以相似度递减的次序返回记录行。为了完成这个结果,你应该指定 MATCH() 两次。这不会引起附加的开销,因为 MySQL 优化器会注意到两次同样的 MATCH() 调用,并只调用一次全文搜索代码。

mysql> SELECT id, body, MATCH (title,body) AGAINST
-> (‘Security implications of running MySQL as root’
) AS score
->
FROM articles WHERE MATCH (title,body) AGAINST
-> (‘Security implications of running MySQL as root’
);
+—-+————————————-+—————–+

| id | body | score |
+—-+————————————-+—————–+
| 4 | 1. Never run mysqld as root. 2. … | 1.5055546709332 |
| 6 | When configured properly, MySQL … | 1.31140957288 |
+—-+————————————-+—————–+
2 rows in set (0.00 sec)

MySQL 使用一个非常简单的剖析器来将文本分隔成词。一个“词”是由文字、数据、“’” 和 “_” 组成的任何字符序列。任何在 stopword 列表上出现的,或太短的(3 个字符或更少的)的 “word” 将被忽略。

在集和查询中的每个合适的词根据其在集与查询中的重要性衡量。这样,一个出现在多个文档中的词将有较低的权重(可能甚至有一个零权重),因为在这个特定的 集中,它有较低的语义值。否则,如果词是较少的,它将得到一个较高的权重。然后,词的权重将被结合用于计算记录行的相似性。

这样一个技术工作可很好地工作与大的集(实际上,它会小心地与之谐调)。 对于非常小的表,词分类不足以充份地反应它们的语义值,有时这个模式可能产生奇怪的结果。

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST (‘MySQL’);
Empty set (0.00
sec)

在上面的例子中,搜索词 MySQL 却没有得到任何结果,因为这个词在超过一半的记录行中出现。同样的,它被有效地处理为一个 stopword (即,一个零语义值的词)。这是最理想的行为 — 一个自然语言的查询不应该从一个 1GB 的表中返回每个次行(second row)。

匹配表中一半记录行的词很少可能找到相关文档。实际上,它可能会发现许多不相关的文档。我们都知道,当我们在互联网上通过搜索引擎试图搜索某些东西时,这会经常发生。因为这个原因,在这个特殊的数据集中,这样的行被设置一个低的语义值。

到 4.0.1 时,MySQL 也可以使用 IN BOOLEAN MODE 修饰语来执行一个逻辑全文搜索。

mysql> SELECT * FROM articles WHERE MATCH (title,body)
-> AGAINST (‘+MySQL -YourSQL’
IN BOOLEAN MODE);
+—-+——————————+————————————-+

| id | title | body |
+—-+——————————+————————————-+
| 1 | MySQL Tutorial | DBMS stands for DataBase … |
| 2 | How To Use MySQL Efficiently | After you went through a … |
| 3 | Optimising MySQL | In this tutorial we will show … |
| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. … |
| 6 | MySQL Security | When configured properly, MySQL … |
+—-+——————————+————————————-+

这个查询返回所有包含词 MySQL 的记录行(注意: 50% 的阈值没有使用),但是它没有包含词 YourSQL。注意,一个逻辑模式的搜索不会自动地以相似值的降序排序记录行。你可以从上面的结果出看得出来,最高的相似值(包含 MySQL 两次的那个) 最列在最后,而不是第一位。一个逻辑全文搜索即使在没有一个 FULLTEXT 索引的情况下也可以工作,然而它慢些。

逻辑全文搜索支持下面的操作符:“+” 一个领头的加号表示,该词必须出现在每个返回的记录行中,“-” 一个领头的减号表示,该词必须不出现在每个返回的记录行中。

缺省的 (当既没有加号也没有负号被指定时)词是随意的,但是包含它的记录行将被排列地更高一点。这个模仿没有 IN BOLEAN MODE 修饰词的 MATCH() … AGAINST() 的行为。

< > 这两个操作符用于改变一个词的相似性值的基值。< 操作符减少基值,> 操作符则增加它。参看下面的示例。

( ) 圆括号用于对子表达式中的词分组。

~一个领头的否定号的作用象一个否定操作符,引起行相似性的词的基值为负的。它对标记一个噪声词很有用。一个包含这样的词的记录将被排列得低一点,但是不会被完全的排除,因为这样可以使用 – 操作符。

* 一个星号是截断操作符。不想其它的操作符,它应该被追加到一个词后,不加在前面。

” 短语,被包围在双引号”中,只匹配包含这个短语(字面上的,就好像被键入的)的记录行。

这里是一些示例:

apple banana

找至少包含上面词中的一个的记录行

1.+apple +juice … 两个词均在被包含
2.+apple macintosh … 包含词 “apple”,但是如果同时包含 “macintosh”,它的排列将更高一些
3.+apple -macintosh … 包含 “apple” 但不包含 “macintosh”
4.+apple +(>pie <strudel) … 包含 “apple” 和 “pie”,或者包含的是 “apple” 和 “strudel” (以任何次序),但是“apple pie” 排列得比 “apple strudel” 要高一点
5.apple* … 包含 “apple”,“apples”,“applesauce” 和 “applet”
6.”some words” … 可以包含 “some words of wisdom”,但不是 “some noise words”

全文的限制

* MATCH() 函数的所有参数必须是从来自于同一张表的列,同时必须是同一个FULLTEXT 索引中的一部分,除非 MATCH() 是 IN BOOLEAN MODE 的。

* MATCH() 列列表必须确切地匹配表的某一 FULLTEXT 索引中定义的列列表,除非 MATCH() 是 IN BOOLEAN MODE 的。

* AGAINST() 的参数必须是一个常量字符串。

微调 MySQL 全文搜索

不幸地,全文搜索仍然只有很少的用户可调参数,虽然增加一些在 TODO 上排列很高。如果你有一个 MySQL 源码发行(查看章节 2.3 安装一个 MySQL 源码发行),你可以发挥对全文搜索的更多控制。

注意,全文搜索为最佳的搜索效果,被仔细地调整了。修改默认值的行为,在大多数情况下,只会使搜索结果更糟。不要修改 MySQL 的源代码,除非你知道你在做什么!

* 被索引的词的最小长度由 MySQL 变量 ft_min_word_len 指定。查看章节 4.5.6.4 SHOW VARIABLES。将它改为你所希望的值,并重建你的 FULLTEXT 索引。 (这个变量只从 MySQL 4.0 开始被支持)

* stopword 列表可以从 ft_stopword_file 变量指定的文件中读取。查看章节 4.5.6.4 SHOW VARIABLES。在修改了 stopword 列表后,重建你的 FULLTEXT 索引。(这个变量只从 MySQL 4.0.10 开始被支持)

* 50% 阈值选择由所选择的特殊的衡量模式确定。为了禁止它,修改 `myisam/ftdefs.h’ 文件中下面的一行:
#define GWS_IN_USE GWS_PROB

改为:

#define GWS_IN_USE GWS_FREQ

然后重新编译 MySQL。在这种情况下,不需要重建索引。 注意:使用了这个,将严重地减少 MySQL 为 MATCH() 提供足够的相似性值的能力。如果你确实需要搜索这样的公共词,最好使用 IN BOOLEAN MODE 的搜索代替,它不遵守 50% 的阈值。

* 有时,搜索引擎维护员希望更改使用于逻辑全文搜索的操作符。这些由变量 ft_boolean_syntax 定义。然而,这个变量是只读的,它的值在 `myisam/ft_static.c’ 中被设置。

对于这些更改,要求你重建你的 FULLTEXT 索引,对于一个 MyISAM 表,最容易的重建索引文件的方式如下面的语句:
mysql> REPAIR TABLE tbl_name QUICK;

全文搜索 TODO * 使所有对 FULLTEXT 索引的操作更快

* 邻近(Proximity)操作符

* 对 “always-index words” 的支持。他们可以是用户希望视为一个词处理的任意字符串,例如 “C++”、”AS/400″、”TCP/IP”,等等

* 支持在 MERGE 表中的全文搜索

* 对多字节字符的支持

* 依照数据的语言建立 stopword 列表

* Stemming (当然,依赖于数据的语言)

* Generic user-suppliable UDF preparser.

* 使模式更加灵活 (通过为 CREATE/ALTER TABLE 中的 FULLTEXT 增加某些可调整参数)

116 Responses to “ MYSQL怎么创建全文索引,怎么使用全文索引 ”

  1. mia pron khalifa说道:

    gdvtjQ Looking forward to reading more. Great blog post. Great.

  2. Kickboxing说道:

    This unique blog is really cool and besides informative. I have chosen many interesting advices out of this blog. I ad love to come back over and over again. Thanks!

  3. animal coloring说道:

    Thanks for sharing, this is a fantastic article.

  4. Lung Health说道:

    Wow, incredible blog layout! How long have you been blogging for? you made blogging look easy. The overall look of your web site is great, as well as the content!

  5. I truly appreciate this blog.Thanks Again. Will read on

  6. Di Outdoor说道:

    This site may Hi there! I just wish to give an enormous thumbs up for the nice info you?ve right here on this post. I shall be coming again to your blog for extra soon.

  7. This very blog is really cool as well as amusing. I have discovered a bunch of helpful things out of it. I ad love to visit it every once in a while. Thanks a lot!

  8. Click to read说道:

    Your style is really unique in comparison to other people I ave read stuff from. Thank you for posting when you have the opportunity, Guess I will just book mark this web site.

  9. here说道:

    Just bookmarked your blog, it is a really great and useful piece of info. I am happy that you shared this helpful tidbit with us. Please keep us up to date like this. Keep writing.

  10. It has been a long time since I ave read anything so informative and compelling. I am waiting for the next article from the writer. Thank you.

  11. weblink说道:

    Say, you got a nice blog article.Really thank you! Want more.

  12. this topic. You realize so much its almost hard to argue with you (not

  13. click说道:

    This is one awesome article post.Really thank you! Really Great.

  14. Im no expert, but I suppose you just crafted an excellent point. You undoubtedly understand what youre talking about, and I can really get behind that. Thanks for being so upfront and so sincere.

  15. You certainly put a fresh spin on a subject that as been discussed for

  16. Way cool! Some extremely valid points! I appreciate you penning this write-up plus the rest of the website is very good.

  17. This is a great tip especially to those new to the blogosphere. Short but very accurate information Many thanks for sharing this one. A must read article!

  18. pre booked说道:

    That is a really good tip particularly to those fresh to the blogosphere. Simple but very precise info Thanks for sharing this one. A must read article!

  19. With havin so much written content do you ever run into

  20. Wow! Thank you! I continuously wanted to write on my site something like that. Can I implement a portion of your post to my site?

  21. This site has got some extremely helpful stuff on it! Cheers for helping me!

  22. Really appreciate you sharing this article post.Thanks Again. Will read on

  23. Muchos Gracias for your article.Thanks Again. Much obliged.

  24. Whoa! This blog looks exactly like my old one! It as on a completely different topic but it has pretty much the same layout and design. Great choice of colors!

  25. It as hard to come by experienced people in this particular topic, however, you sound like you know what you are talking about! Thanks

  26. Nwokolo Collins说道:

    I really liked your blog.Really looking forward to read more. Want more.

  27. Kymberly说道:

    Wow, amazing blog layout! How long have you been blogging for? you made blogging look easy. The overall look of your web site is fantastic, let alone the content!. Thanks For Your article about sex.

  28. Nwokolo说道:

    Thanks for sharing, this is a fantastic article post.Really looking forward to read more. Really Cool.

  29. klm promo code说道:

    Major thankies for the blog.Really thank you! Fantastic.

  30. Very nice blog post. I absolutely appreciate this website. Stick with it!

  31. rather essential That my best companion in addition to i dugg lots of everybody post the minute i notion everyone was useful priceless

  32. metal recycling说道:

    I will immediately seize your rss feed as I can not in finding your e-mail subscription link or e-newsletter service. Do you have any? Please allow me realize so that I may just subscribe. Thanks.

  33. Eminem说道:

    Thank you for another wonderful article. Where else could anyone get that kind of info in such a perfect way of writing? I have a presentation next week, and I am on the look for such info.

  34. kho hang si ann说道:

    I simply could not depart your site prior to suggesting that I actually loved the usual information a person supply on your visitors? Is gonna be back often in order to inspect new posts.

  35. Usa Online Casinos view of Three Gorges | Wonder Travel Blog

  36. You, my pal, ROCK! I found just the information I already searched all over the place and simply couldn at locate it. What a great web-site.

  37. tools for sales说道:

    Say, you got a nice post.Really thank you! Really Cool.

  38. There may be noticeably a bundle to know about this. I assume you made sure good points in options also.

  39. Pierre signe astrologique horoscope septembre balance Here is my homepage; voyance en ligne gratuite

  40. Wonderful article! We are linking to this great

  41. Major thanks for the blog.Much thanks again. Really Great.

  42. Say, you got a nice article.Really looking forward to read more. Great.

  43. Looking forward to reading more. Great blog post.Really looking forward to read more. Much obliged.

  44. sidekick说道:

    Wow! This could be one of the most useful blogs we have ever come across on thesubject. Actually wonderful info! I am also a specialist in this topic therefore I can understand your hard work.

  45. Supermodel Elena说道:

    Some truly prime blog posts on this web site , saved to favorites.

  46. Thanks for any other great post. Where else could anybody get that kind of info in such an ideal means of writing? I ave a presentation next week, and I am at the look for such info.

  47. Thanks for sharing, this is a fantastic blog post.Really thank you! Cool.

  48. seo tips说道:

    There as certainly a lot to know about this topic. I like all of the points you ave made.

  49. healthy说道:

    Normally I don at learn article on blogs, however I wish to say that this write-up very compelled me to check out and do it! Your writing taste has been amazed me. Thank you, quite nice article.

  50. Just what I was looking for, regards for posting.

  51. I really liked your article post.Much thanks again. Really Great.

  52. Tejidos说道:

    Major thankies for the blog post.Really looking forward to read more. Want more.

  53. Polainas tejidas说道:

    Some really fantastic info , Glad I noticed this.

  54. I was able to find good information from your blog posts.

  55. my page说道:

    I truly appreciate this post. I ave been looking all over for this! Thank goodness I found it on Google. You have made my day! Thx again.

  56. white shoes说道:

    You made some decent points there. I looked on the internet for the subject matter and found most persons will approve with your website.

  57. I really liked your article post.Really looking forward to read more. Fantastic.

  58. Weird , this post turns up with a dark color to it, what shade is the primary color on your web site?

  59. It seems too complicated and very broad for me. I am looking forward for your next post,

  60. Waec说道:

    Thanks for sharing, this is a fantastic blog post.Much thanks again. Great.

  61. Waec expo说道:

    I went over this internet site and I think you have a lot of great information, saved to favorites (:.

  62. You ought to really control the comments listed here

  63. Thanks for sharing, this is a fantastic blog article. Keep writing.

  64. very nice publish, i actually love this web site, carry on it

  65. Real estate说道:

    whoah this blog is great i love reading your posts. Keep up the good work! You know, many people are looking around for this information, you can help them greatly.

  66. Open heaven说道:

    Thanks so much for the blog post.Much thanks again. Awesome.

  67. Thanks again for the blog.Much thanks again. Really Cool.

  68. Looking forward to reading more. Great article.Much thanks again. Really Great.

  69. online football games Chelsea hold won online football games systematically in bets. Cross to the brain give or return it on their behalf.

  70. sheets hyperlink whilst beating time. All kinds of common games plus they are of numerous genres.

  71. I value the post.Really looking forward to read more. Cool.

  72. pretty useful stuff, overall I imagine this is worth a bookmark, thanks

  73. junior说道:

    Some genuinely nice as well as utilitarian information on this web site, too In my opinion the layout has amazing features.

  74. hotel说道:

    Some truly fantastic information, Gladiolus I discovered this.

  75. romance understanding. With online video clip clip

  76. Looking forward to reading more. Great post.Thanks Again. Cool.

  77. Really appreciate you sharing this article post.Much thanks again. Will read on

  78. personal injury说道:

    Major thankies for the blog article.Thanks Again. Really Great. this site

  79. It as actually a great and useful piece of info. I am happy that you just shared this helpful tidbit with us. Please stay us up to date like this. Thank you for sharing.

  80. Whats up! I just want to give a huge thumbs up for the good info you could have right here on this post. I might be coming again to your blog for more soon.

  81. What as Happening i am new to this, I stumbled upon this I ave found It absolutely useful and it has helped me out loads. I hope to contribute & help other users like its aided me. Good job.

  82. Chapel说道:

    Thanks for all аАааБТ“our vаА аЂаluablаА аЂа• laboаА аБТ– on this ?аА аЂа•bsite.

  83. view说道:

    Very good article! We are linking to this great content on our website. Keep up the great writing.

  84. wp commission说道:

    quite useful material, on the whole I picture this is worthy of a book mark, thanks

  85. click resources说道:

    Many thanks for sharing this great article. Very inspiring! (as always, btw)

  86. see the website说道:

    later on and see if the problem still exists.

  87. Say, you got a nice article post. Will read on

  88. Your style is so unique compared to other people I ave read stuff from. Thanks for posting when you have the opportunity, Guess I all just bookmark this blog.

  89. Im obliged for the blog article. Want more.

  90. ndu说道:

    Say, you got a nice blog.Thanks Again. Awesome.

  91. browse说道:

    Normally I don at learn article on blogs, but I would like to say that this write-up very forced me to check out and do so! Your writing style has been surprised me. Thanks, very great article.

  92. share. I understand this is off subject nevertheless I simply wanted to ask.

  93. free ads说道:

    Thanks-a-mundo for the blog post.Really thank you! Will read on

  94. Wow, great article.Really looking forward to read more. Want more.

  95. see more说道:

    You made some clear points there. I looked on the internet for the topic and found most people will consent with your blog.

  96. to learn more说道:

    This is very helpful and interesting post. Thx

  97. Major thankies for the article post.Much thanks again. Really Cool.

  98. Very interesting subject , thanks for posting. Not by age but by capacity is wisdom acquired. by Titus Maccius Plautus.

  99. caratulas CD说道:

    Very good post. I definitely appreciate this site. Stick with it!

  100. Muchos Gracias for your article. Will read on

  101. Major thanks for the article post. Want more.

  102. please pay a visit to the web sites we follow, like this one particular, as it represents our picks in the web

  103. Wow! Thank you! I continuously needed to write on my website something like that. Can I take a fragment of your post to my blog?

  104. go there说道:

    Really appreciate you sharing this post.Much thanks again. Want more.

  105. Really informative article post.Really thank you! Really Great.

  106. klik saya说道:

    It as hard to find educated people for this topic, however, you sound like you know what you are talking about! Thanks

  107. Vanda说道:

    Thanks so much for the article.Thanks Again. Much obliged.

  108. Usually I do not read article on blogs, but I wish to say that this write-up very pressured me to take a look at and do it! Your writing style has been surprised me. Thanks, very great article.

  109. Your style is unique compared to other folks I ave read stuff from. I appreciate you for posting when you ave got the opportunity, Guess I all just book mark this blog.

  110. other details说道:

    Say, you got a nice blog post.Much thanks again. Want more.

  111. check here说道:

    It as actually a nice and useful piece of information. I am satisfied that you shared this useful tidbit with us. Please keep us up to date like this. Thank you for sharing.

  112. good day说道:

    Thanks for sharing, this is a fantastic blog article.Much thanks again.

  113. omg! can at imagine how fast time pass, after August, ber months time already and Setempber is the first Christmas season in my place, I really love it!

  114. uvb treatment I want to write and I wonder how to start a blog for people on this yahoo community..

  115. like to find something more safe. Do you have any recommendations?

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>