1. 原创博文
  2. 案例分析
  3. Tyecho如何支持大数据？

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%"则无法使用索引了，查询起来还是比较慢。

加载分类文章列表页

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


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

高性能海量数据HPTypechoMySQL大数据数据库 @TypechoDev