Tyecho如何支持大数据?
所谓“大数据”,当然不是平时所说的大数据啦!Typecho作为一个博客程序,当然不适合用作几百T数据的展示前台。
但偶然情况下,可能希望用typecho这个简单的平台来代替公司内部的cms,面对几十万甚至上百万的post,如何才能使得typecho的响应时间小于1s呢?(PS:实测,在Linode2G的同等配置下,100w post的表,页面加载速度大概在30s左右)
先留个关子,等在下整理下再发上来~
======== 7.22更新==================================
不好意思,最近太忙了.... ......
在数据比较大的时候,Typecho运行起来会比较慢,慢主要在两个方面
- 加载分类文章列表
- 搜索
加载分类文章列表页
先来个满满自恋的效果对比:
这个优化效果是如何达到的呢?说穿了其实很简单O(∩_∩)O~数据库优化指南提及,提高查询性能最常用的两个方法就是:简化查询和使用索引。当然,还有优化配置、使用缓存等,这些和我们现在涉及不大,暂且不提。
WidgetArchive.php的categoryHandle函数中,定义了查询分类文章的sql语句
$select->join('table.relationships', 'table.contents.cid = table.relationships.cid')
->where('table.relationships.mid IN ?', $children)
->where('table.contents.type = ?', 'post')
->group('table.contents.cid');
将它打印出来,sql查询语句是大概是这样的:
SELECT te_contents.`cid` , ... FROM te_contents INNER JOIN te_relationships ON te_contents.`cid` = te_relationships.`cid` WHERE ... GROUP BY te_contents.`cid`
为了方便阅读,已经将不影响阅读的sql语句段删除了,以...代替。
可以看出,当contents表数据量很大的时候,join表的性能就成为了瓶颈。譬如上面的效果图,contents表200w的时候,耗时大概17s,此时系统IO已经占满,系统基本上不能再响应其他服务。加之这个语句过于复杂,mysql缓存命中的概率也就比较低,此时如果有两个访客同时在刷分类页面,服务器基本上就相当于挂了。
所以,优化思路为:
- 去掉联表查询
- 使用全文索引
一、修改sql表结构
首先,在contents表增加一个字段,用于记录分类信息,从而去掉联表查询。SQL语句如下:
ALTER TABLE te_contents ADD ext_categories varchar(32)
注意:此sql语句是随手写来的,仅用于表达意思,你可能需要根据你的实际需要进行调优才能应用于生成环境。下同。
然后,对该字段使用索引,加快查询速度
alter table te_contents add fulltext (`ext_categories`)
修改之后的表结构如下:
CREATE TABLE `te_contents` (
`cid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
`slug` varchar(200) DEFAULT NULL,
... ... //省略了部分字段
`ext_categories` varchar(16) DEFAULT '',
... ...
FULLTEXT KEY `ext_categories` (`ext_categories`)
) ENGINE=MyISAM AUTO_INCREMENT=2401192 DEFAULT CHARSET=utf8
接着,想办法将relationships表中的分类信息更新到此ext_categories字段来。办法有很多,譬如你可以写个插件,在创建文章的时候顺便更新此字段,也可以用附件里面的脚本,统一更新。
附件:update_ext_categories.zip
注意,在执行脚本之前,需要修改mysql默认配置以支持索引的一些特性。为了避免跳跃太大,此mysql配置部分放到了后文,请自行参阅。
二、修改查询语句,改为使用索引进行查询
修改/var/Widget/Archive.php
约910行的地方:
/** fix sql92 by 70 */
$select->join('table.relationships', 'table.contents.cid = table.relationships.cid')
->where('table.relationships.mid IN ?', $children)
->where('table.contents.type = ?', 'post')
->group('table.contents.cid');
改成:
//add by teypechodev.com
//检查是否存在ext_categories字段
$clnSelect = $this->db->select()
->from('information_schema.columns')
->where('TABLE_NAME like ?', '%contents%')
->where('COLUMN_NAME = ?','ext_categories')
->limit(1);
$test_result = $this->db->fetchRow($clnSelect);
//如果不存在ext_categories,那么走原来的查询逻辑,否则走优化后的逻辑
if(empty($test_result)){
/** fix sql92 by 70 */
$select->join('table.relationships', 'table.contents.cid = table.relationships.cid')
->where('table.relationships.mid IN ?', $children)
->where('table.contents.type = ?', 'post')
->group('table.contents.cid');
}else{
//通过全文搜索的方式检索,而且仅检索当前分类,不检索子分类
$select->where('match(table.contents.ext_categories) against( ? in boolean mode)', sprintf("%03d",intval($category['mid'])))
->where('table.contents.type = ?', 'post');
}
提醒,将mid格式化成%03d,这样可以保证mid小于100时可以在前面加0,譬如mid为8时可以转换成008,这样使得查询关键词长度大于mysql配置中的ft_min_word_len的值,避免被mysql丢掉。
另外,为了使typecho支持in boolean mode查询,还需要修改Db_Qeury的一处地方。
在/var/Typeco/Db/Query.php
文件中,约155行处,在filterColumn
函数返回前,增加如下代码:
//add by typechodev.com,使Typecho支持mysql全文搜索的in boolean mode查询,避免将boolean mode关键词被转义掉。
if(strpos($result,'match') !== false && strpos($result,'against') !==false){
$result = str_replace('`boolean`','boolean',$result);
$result = str_replace('`mode`','mode',$result);
}
return $result;
三、mysql数据库配置
主要是修改mysql的ft_min_word_len,使支持短字符串索引。
[mysqld]
ft_min_word_len = 2
然后,记得重启Mysql服务哈。
汇总如下:
- 修改content表,增加一个字段ext_categories,用于冗余分类信息
- 修改
/var/Widget/Archive.php
,修改查询语句,使用索引进行查询 - 必要时修改
/var/Typeco/Db/Query.php
,使得Typecho可以支持in boolean mode查询方式 - 修改my.cnf,将ft_min_word_len改到合适的值
- 最后就使用上文的脚本或者自己的方式,将relationships表的分类信息冗余到content表的ext_categories字段中去
可能有人会问,既然冗余了ext_categories,那么用like来查询不就可以了,何必那么复杂?
其实这个方案尝试过,但效果不太好,而且like方式查询,对于where ext_categories like "keyword%"
可以使用索引,但where ext_categories like "%keyword%"
则无法使用索引了,查询起来还是比较慢。
加载分类文章列表页
文章有点长了,搜索逻辑优化改天再写吧。
版权声明:未经书面授权禁止转载、摘编、复制或建立镜像。对既成事实本站将保留所有的权利。
评论已关闭