Tyecho如何支持大数据?

所谓“大数据”,当然不是平时所说的大数据啦!Typecho作为一个博客程序,当然不适合用作几百T数据的展示前台。

但偶然情况下,可能希望用typecho这个简单的平台来代替公司内部的cms,面对几十万甚至上百万的post,如何才能使得typecho的响应时间小于1s呢?(PS:实测,在Linode2G的同等配置下,100w post的表,页面加载速度大概在30s左右)

先留个关子,等在下整理下再发上来~

======== 7.22更新==================================
不好意思,最近太忙了.... ......

在数据比较大的时候,Typecho运行起来会比较慢,慢主要在两个方面

  1. 加载分类文章列表
  2. 搜索

加载分类文章列表页

先来个满满自恋的效果对比:
Tyecho如何支持大数据?

这个优化效果是如何达到的呢?说穿了其实很简单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缓存命中的概率也就比较低,此时如果有两个访客同时在刷分类页面,服务器基本上就相当于挂了。

所以,优化思路为:

  1. 去掉联表查询
  2. 使用全文索引

一、修改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服务哈。

汇总如下:

  1. 修改content表,增加一个字段ext_categories,用于冗余分类信息
  2. 修改/var/Widget/Archive.php,修改查询语句,使用索引进行查询
  3. 必要时修改/var/Typeco/Db/Query.php,使得Typecho可以支持in boolean mode查询方式
  4. 修改my.cnf,将ft_min_word_len改到合适的值
  5. 最后就使用上文的脚本或者自己的方式,将relationships表的分类信息冗余到content表的ext_categories字段中去

可能有人会问,既然冗余了ext_categories,那么用like来查询不就可以了,何必那么复杂?
其实这个方案尝试过,但效果不太好,而且like方式查询,对于where ext_categories like "keyword%"可以使用索引,但where ext_categories like "%keyword%"则无法使用索引了,查询起来还是比较慢。

加载分类文章列表页

文章有点长了,搜索逻辑优化改天再写吧。


版权声明:未经书面授权禁止转载、摘编、复制或建立镜像。对既成事实本站将保留所有的权利。