位置: 编程技术 - 正文
通过MySQL优化Discuz!的热帖翻页的技巧
编辑：rootadmin推荐整理分享通过MySQL优化Discuz!的热帖翻页的技巧，希望有所帮助，仅作参考，欢迎阅读内容。
文章相关热门搜索词:,内容如对您有帮助，希望把文章链接给更多的朋友！
写在前面：discuz!作为首屈一指的社区系统，为广大站长提供了一站式网站解决方案，而且是开源的（虽然部分代码是加密的），它为这个垂直领域的行业发展作出了巨大贡献。尽管如此，discuz!系统源码中，还是或多或少有些坑。其中最著名的就是默认采用MyISAM引擎，以及基于MyISAM引擎的抢楼功能，session表采用memory引擎等，可以参考后面几篇历史文章。本次我们要说说discuz!在应对热们帖子翻页逻辑功能中的另一个问题。
在我们的环境中，使用的是 MySQL-5.6.6 版本。
在查看帖子并翻页过程中，会产生类似下面这样的SQL：
这个SQL执行的代价是：
-- 根据索引访问行记录次数，总体而言算是比较好的状态
-- 根据索引顺序访问下一行记录的次数，通常是因为根据索引的范围扫描，或者全索引扫描，总体而言也算是比较好的状态
-- 按照一定顺序读取行记录的总次数。如果需要对结果进行排序，该值通常会比较大。当发生全表扫描或者多表join无法使用索引时，该值也会比较大
而当遇到热帖需要往后翻很多页时，例如：
这个SQL执行的代价则变成了（可以看到Handler_read_key、Handler_read_rnd大了很多）：
| Handler_read_key | | -- 因为前面需要跳过很多行记录| Handler_read_next | | -- 同上| Handler_read_rnd | | -- 因为需要先对很大一个结果集进行排序
可见，遇到热帖时，这个SQL的代价会非常高。如果该热帖被大量的访问历史回复，或者被搜素引擎一直反复请求并且历史回复页时，很容易把数据库服务器直接压垮。
小结：这个SQL不能利用 `displayorder` 索引排序的原因是，索引的第二个列 `invisible` 采用范围查询（RANGE），导致没办法继续利用联合索引完成对 `dateline` 字段的排序需求（而如果是 WHERE tid =&#; AND invisible IN(&#;, &#;) AND dateline =&#; 这种情况下是完全可以用到整个联合索引的，注意下二者的区别）。
知道了这个原因，相应的优化解决办法也就清晰了：创建一个新的索引 idx_tid_dateline，它只包括 tid、dateline 两个列即可（根据其他索引的统计信息，item_type 和 item_id 的基数太低，所以没包含在联合索引中。当然了，也可以考虑一并加上）。
我们再来看下采用新的索引后的执行计划：
可以看到，之前存在的 Using filesort 消失了，可以通过索引直接完成排序了。
不过，如果该热帖翻到较旧的历史回复时，相应的SQL还是不能使用新的索引：
对比下如果建议优化器使用新索引的话，其执行计划是怎样的：
可以看到，因为查询优化器认为后者需要扫描的行数远比前者多了万多，因此认为前者效率更高。
事实上，在这个例子里，排序的代价更高，因此我们要优先消除排序，所以应该强制使用新的索引，也就是采用后面的执行计划，在相应的程序中指定索引。
最后，我们来看下热帖翻到很老的历史回复时，两个执行计划分别的profiling统计信息对比：
1、采用旧索引（displayorder）：
2、如果是采用新索引（idx_tid_dateline）：
可以看到，效率有了一定提高，不过不是很明显，因为确实需要扫描的数据量更大，所以 Sending data 阶段耗时更多。
这时候，我们可以再参考之前的一个优化方案：[MySQL优化案例]系列 — 分页优化
然后可以将这个SQL改写成下面这样：
再看下这个SQL的 profiling 统计信息：
可以看到，效率提升了1倍以上，还是挺不错的。
最后说明下，这个问题只会在热帖翻页时才会出现，一般只有1,2页回复的帖子如果还采用原来的执行计划，也没什么问题。
因此，建议discuz!官方修改或增加下新索引，并且在代码中判断是否热帖翻页，是的话，就强制使用新的索引，以避免性能问题。
分析MySQL中索引引引发的CPU负载飙升的问题 收到一个mysql服务器负载告警，上去一看，loadaverage都飙到多了，用top一看，CPU跑到了%，不过IO和内存的负载并不高，根据经验，应该又是一起索引
MySQL索引优化的实际案例分析 Orderbydesc/asclimitM是我在mysqlsql优化中经常遇到的一种场景，其优化原理也非常的简单，就是利用索引的有序性，优化器沿着索引的顺序扫描，在扫描到符
浅谈MySQL中的子查询优化技巧 mysql的子查询的优化一直不是很友好，一直有受业界批评比较多,也是我在sql优化中遇到过最多的问题之一，你可以点击这里，这里来获得一些信息，mysql
上一篇：MySQL中一些优化straight_join技巧(mysql常见优化手段)
