Tyecho如何支持大数据？

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

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

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

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

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

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

## 加载分类文章列表页

先来个满满自恋的效果对比：
![优化效果对比][2]

这个优化效果是如何达到的呢？说穿了其实很简单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][1]

>注意，在执行脚本之前，需要修改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%”`则无法使用索引了，查询起来还是比较慢。

## 加载分类文章列表页
文章有点长了，搜索逻辑优化改天再写吧。

[1]: https://www.typechodev.com/usr/uploads/2015/07/3488979741.zip
[2]: https://www.typechodev.com/usr/uploads/2015/07/740271158.png