上海野生动物园 狐猴

在WordPress中运用Mysql Fulltext Index

Posted onLeave a commentCategories技术文章

前一阵子给公司的某个网站做开发时,DBA 反应搜索的语句慢的不像样子。

SELECT SQL_CALC_FOUND_ROWS
 wp_posts.ID
FROM
 wp_posts
WHERE
 1 = 1
 AND (((wp1_posts.post_title LIKE '%123%')
 OR (wp_posts.post_excerpt LIKE '%123%')
 OR (wp_posts.post_content LIKE '%123%')))
 AND wp_posts.post_type IN ('post' , 'page', 'attachment')
 AND (wp_posts.post_status = 'publish'
 OR wp_posts.post_author = 1756
 AND wp_posts.post_status = 'private')
ORDER BY wp_posts.post_title LIKE '%123%' DESC , wp_posts.post_date DESC
LIMIT 0 , 5

经过 mysql query 分析,Wordpress 原生的搜索功及其简单,完全是基于 LIKE 语句在 wp_posts 表里做模糊匹配。这特么就尴尬了。公司网站的规模跟我这个没几篇博文的文章是不一样的。表里的数据万行以内基本 LIKE 语句也不会慢的太离谱,可是再上一个数量级几十万行扫描就有点不可以接受了。基本随便搜点什么页面响应都要达到7~10秒。

搜索效率问题的解决方案自然是一大堆,solr, elastic search, sphinx 等。但是当时项目也是急着上线,没给我留很多时间来研究各种解决方案优劣。和 IT 部门协调添加这类服务,能不能实施下来也是个来来回回的麻烦事情。

后来经过和同事讨论并咨询 DBA, 应用 Mysql fulltext index 是个比较好的方案。DBA 认为 Mysql 全文索引千万行行级别也是能 handle 住的。 那就没什么问题了,可预见的未来我们的站也达不到这个维度。

实施起来倒是简单。基本就是两个步骤。

  1. 对 wp_posts 表的 post_title, post_content, post_excerpt 做 fulltext index
  2. 开发 WordPress 插件,利用 WordPress hook 替换 WordPress 默认的搜索功能。用到的 hook 是 pre_get_posts

因为是工作中的成果具体代码就不贴了,熟悉 WordPress 和 Mysql 的话看了上面两点随便做点功课应该可以写出来。

项目上线后结果是相当理想的。从几十万条数据中搜索从7~10秒降到了1~3秒。这事情我非常高兴,因为在非常有限的开发周期里,用最有限的资源把这件事给做到预期了。可惜这种成就感也只能自嗨,产品才不鸟你做了什么牛逼的事情。他们的逻辑是你一开始弄出来的东西那么慢,那么差劲,又花了点工时才矫正过来,马马虎虎就不扣你 KPI 了。。。真是尴尬没地方讲理。

有两点要提一下的, 一是 Mysql fulltext index 适用于 英文等有空格分词的语言,像我们中文没这种东西就白瞎了;二是搜索结果和 LIKE 语句还是有点小区别的,因为是基于分词的搜索,LIKE 方式搜 jeans 能搜出来 bluejeans,但是 fulltext 方式是搜不出来的。

发表评论

电子邮件地址不会被公开。 必填项已用*标注