通过MySQL优化Discuz!的热帖翻页的技巧

2024-10-22 11:59:47

写在前面：discuz!作为首屈一指的社区系统，为广大站长提供了一站式网站解决方案，而且是开源的（虽然部分代码是加密的），它为这个垂直领域的行业发展作出了巨大贡献。尽管如此，discuz!系统源码中，还是或多或少有些坑。其中最著名的就是默认采用MyISAM引擎，以及基于MyISAM引擎的抢楼功能，session表采用memory引擎等，可以参考后面几篇历史文章。本次我们要说说discuz!在应对热们帖子翻页逻辑功能中的另一个问题。

在我们的环境中，使用的是 MySQL-5.6.6 版本。

在查看帖子并翻页过程中，会产生类似下面这样的SQL：

mysql> desc SELECT * FROM pre_forum_post WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline DESC LIMIT 15\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: pre_forum_post
 type: ref
 possible_keys: tid,displayorder,first
 key: displayorder
 key_len: 3
 ref: const
 rows: 593371
 Extra: Using index condition; Using where; Using filesort

这个SQL执行的代价是：

-- 根据索引访问行记录次数，总体而言算是比较好的状态

| Handler_read_key   | 16  |

-- 根据索引顺序访问下一行记录的次数，通常是因为根据索引的范围扫描，或者全索引扫描，总体而言也算是比较好的状态

| Handler_read_next   | 329881 |

-- 按照一定顺序读取行记录的总次数。如果需要对结果进行排序，该值通常会比较大。当发生全表扫描或者多表join无法使用索引时，该值也会比较大

| Handler_read_rnd   | 15  |

而当遇到热帖需要往后翻很多页时，例如：

mysql> desc SELECT * FROM pre_forum_post WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860, 15\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: pre_forum_post
 type: ref
 possible_keys: displayorder
 key: displayorder
 key_len: 3
 ref: const
 rows: 593371
 Extra: Using where; Using filesort

这个SQL执行的代价则变成了（可以看到Handler_read_key、Handler_read_rnd大了很多）：

| Handler_read_key           | 129876 | -- 因为前面需要跳过很多行记录
| Handler_read_next          | 329881 | -- 同上
| Handler_read_rnd           | 129875 | -- 因为需要先对很大一个结果集进行排序

可见，遇到热帖时，这个SQL的代价会非常高。如果该热帖被大量的访问历史回复，或者被搜素引擎一直反复请求并且历史回复页时，很容易把数据库服务器直接压垮。

小结：这个SQL不能利用 `displayorder` 索引排序的原因是，索引的第二个列 `invisible` 采用范围查询（RANGE），导致没办法继续利用联合索引完成对 `dateline` 字段的排序需求（而如果是 WHERE tid =? AND invisible IN(?, ?) AND dateline =? 这种情况下是完全可以用到整个联合索引的，注意下二者的区别）。

知道了这个原因，相应的优化解决办法也就清晰了：
创建一个新的索引 idx_tid_dateline，它只包括 tid、dateline 两个列即可（根据其他索引的统计信息，item_type 和 item_id 的基数太低，所以没包含在联合索引中。当然了，也可以考虑一并加上）。

我们再来看下采用新的索引后的执行计划：

mysql> desc SELECT * FROM pre_forum_post WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 15\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: pre_forum_post
 type: ref
 possible_keys: tid,displayorder,first,idx_tid_dateline
 key: idx_tid_dateline
 key_len: 3
 ref: const
 rows: 703892
 Extra: Using where

可以看到，之前存在的 Using filesort 消失了，可以通过索引直接完成排序了。

不过，如果该热帖翻到较旧的历史回复时，相应的SQL还是不能使用新的索引：

mysql> desc SELECT * FROM pre_forum_post WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: pre_forum_post
 type: ref
 possible_keys: tid,displayorder,first,idx_tid_dateline
 key: displayorder
 key_len: 3
 ref: const
 rows: 593371
 Extra: Using where; Using filesort

对比下如果建议优化器使用新索引的话，其执行计划是怎样的：

mysql> desc SELECT * FROM pre_forum_post use index(idx_tid_dateline) WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: pre_forum_post
 type: ref
 possible_keys: idx_tid_dateline
 key: idx_tid_dateline
 key_len: 3
 ref: const
 rows: 703892
 Extra: Using where

可以看到，因为查询优化器认为后者需要扫描的行数远比前者多了11万多，因此认为前者效率更高。

事实上，在这个例子里，排序的代价更高，因此我们要优先消除排序，所以应该强制使用新的索引，也就是采用后面的执行计划，在相应的程序中指定索引。

最后，我们来看下热帖翻到很老的历史回复时，两个执行计划分别的profiling统计信息对比：

1、采用旧索引（displayorder）：

mysql> SELECT * FROM pre_forum_post WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15;

#查看profiling结果
 | starting    | 0.020203 |
 | checking permissions | 0.000026 |
 | Opening tables  | 0.000036 |
 | init     | 0.000099 |
 | System lock   | 0.000092 |
 | optimizing   | 0.000038 |
 | statistics   | 0.000123 |
 | preparing   | 0.000043 |
 | Sorting result  | 0.000025 |
 | executing   | 0.000023 |
 | Sending data   | 0.000045 |
 | Creating sort index | 0.941434 |
 | end     | 0.000077 |
 | query end   | 0.000044 |
 | closing tables  | 0.000038 |
 | freeing items  | 0.000056 |
 | cleaning up   | 0.000040 |

2、如果是采用新索引（idx_tid_dateline）：

mysql> SELECT * FROM pre_forum_post use index(idx_tid_dateline) WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15;

#对比查看profiling结果
 | starting    | 0.000151 |
 | checking permissions | 0.000033 |
 | Opening tables  | 0.000040 |
 | init     | 0.000105 |
 | System lock   | 0.000044 |
 | optimizing   | 0.000038 |
 | statistics   | 0.000188 |
 | preparing   | 0.000044 |
 | Sorting result  | 0.000024 |
 | executing   | 0.000023 |
 | Sending data   | 0.917035 |
 | end     | 0.000074 |
 | query end   | 0.000030 |
 | closing tables  | 0.000036 |
 | freeing items  | 0.000049 |
 | cleaning up   | 0.000032 |

可以看到，效率有了一定提高，不过不是很明显，因为确实需要扫描的数据量更大，所以 Sending data 阶段耗时更多。

这时候，我们可以再参考之前的一个优化方案：[MySQL优化案例]系列 — 分页优化

然后可以将这个SQL改写成下面这样：

mysql> EXPLAIN SELECT * FROM pre_forum_post t1 INNER JOIN (
 SELECT id FROM pre_forum_post use index(idx_tid_dateline) WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY
 dateline LIMIT 129860,15) t2
 USING (id)\G
 *************************** 1. row ***************************
 id: 1
 select_type: PRIMARY
 table:
 type: ALL
 possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 129875
 Extra: NULL
 *************************** 2. row ***************************
 id: 1
 select_type: PRIMARY
 table: t1
 type: eq_ref
 possible_keys: PRIMARY
 key: PRIMARY
 key_len: 4
 ref: t2.id
 rows: 1
 Extra: NULL
 *************************** 3. row ***************************
 id: 2
 select_type: DERIVED
 table: pre_forum_post
 type: ref
 possible_keys: idx_tid_dateline
 key: idx_tid_dateline
 key_len: 3
 ref: const
 rows: 703892
 Extra: Using where

再看下这个SQL的 profiling 统计信息：

| starting    | 0.000209 |
| checking permissions | 0.000026 |
| checking permissions | 0.000026 |
| Opening tables  | 0.000101 |
| init     | 0.000062 |
| System lock   | 0.000049 |
| optimizing   | 0.000025 |
| optimizing   | 0.000037 |
| statistics   | 0.000106 |
| preparing   | 0.000059 |
| Sorting result  | 0.000039 |
| statistics   | 0.000048 |
| preparing   | 0.000032 |
| executing   | 0.000036 |
| Sending data   | 0.000045 |
| executing   | 0.000023 |
| Sending data   | 0.225356 |
| end     | 0.000067 |
| query end   | 0.000028 |
| closing tables  | 0.000023 |
| removing tmp table | 0.000029 |
| closing tables  | 0.000044 |
| freeing items  | 0.000048 |
| cleaning up   | 0.000037 |

可以看到，效率提升了1倍以上，还是挺不错的。

最后说明下，这个问题只会在热帖翻页时才会出现，一般只有1,2页回复的帖子如果还采用原来的执行计划，也没什么问题。

因此，建议discuz!官方修改或增加下新索引，并且在代码中判断是否热帖翻页，是的话，就强制使用新的索引，以避免性能问题。

如何编写翻页函数？

page.inc<% Sub ShowPageBar(TotalPage,CurPage,strUrl) ' 参数TotalPage:全部页数;CurPage:当前页数;strUrl:翻页使用的连接地址. DIM strPage CurPage=GetValidPageNO(TotalPage,CurPage) Response.Write "<TABLE WIDTH=''100%''>" Response.Write "<tr><td w
点图片上一页下一页翻页效果

<script language="javascript" defer> var zhang=2 function next(){ if(zhang==7){ alert("这已经是最后一张了!") zhang=6 } document.getElementById("tu").src=zhang+".jpg" text.innerHTML="当前是第"+zhang+"张图片"
PHP 长文章分页函数带使用方法，不会分割段落,翻页在底部

复制代码代码如下: <?php function ff_page($content,$page) { global $expert_id; $PageLength = 2000; //每页字数 $CLength = strlen($content); $PageCount = floor(($CLength / $PageLength)) + 1; //计算页数 $PageArray=array(); $Seperator = array("\n","\r"
php实现的仿阿里巴巴实现同类产品翻页

如果左边的记录条数小于$space(页码区段)的值,页码$start从1开始向右增值.如果左则的记录条数多于$left(左右各显示页数)的值,$start将从左边记录数减去$left值开始记数. 复制代码代码如下: <?php /** 实现同类产品翻页 **/ class pager { protected $space; protected $left; protected $DB; protected $pageName; public function setSpace($num) {
PHP翻页跳转功能实现方法

我们都知道用php+mysql在web 页实现数据库资料全部显示是非常简单而有趣的,数据库资料很少的情况下页面显示还是让人满意的,但是当数据库资料非常多的情况下,页面的显示情况将会变的非常糟糕,下面就来介绍一下如何实现当前页面数据资料显示数量及如何实现动态的翻转功能. 这里将介绍两种翻页显示功能的实现: 先介绍一下在翻页中用到的数据库语法: mysql_query("select * from table order by id desc"); 这条数据库语句再熟悉不过了,是用来搜索记
php中文本数据翻页（留言本翻页）

在mysq中介绍翻页的文章不少,而文本数据表格式的翻页介绍的很少,这里我就简单的说一下翻页主要介绍以下翻页思想1.留言本的翻页2.文本论坛的翻页 ------------------------留言本的翻页:------------------------------这个在文本数据表中的翻页是最简单的翻页,这样说明一下 golbal file Data.dat --- NOTE FILE USE user.dat --- Forum File useDat
在图片上显示左右箭头类似翻页的代码

使用JS实现在图片上显示左右箭头的翻页代码,预览效果网址:http://www.keleyi.com/keleyi/phtml/picnext/ 本实例使用了javascript的onmousemove 事件.onmousemove 事件会在鼠标指针移动时发生. 语法 onmousemove="SomeJavaScriptCode" SomeJavaScriptCode是必需参数.规定该事件发生时执行的 JavaScript. 下面是核心代码(完整代码请在效果页面查看源代码): 复制代
MongoDB快速翻页的方法

翻阅数据是MongoDB最常见的操作之一.一个典型的场景是需要在你的用户界面中显示你的结果.如果你是批量处理的数据,同样重要的是要让你的分页策略正确,以便你的数据处理可以规模化. 接下来,让我们通过一个例子来看在MongoDB中翻阅数据的不同方式.在这个例子中,我们有一个CRM数据库的用户数据,我们需要通过翻阅浏览和在同一时间显示10个用户.所以实际上,我们的页面大小是10.下方是我们的用户文档的结构: { _id, name, company, state } 方法一:Using skip()
PHP 翻页实例代码

复制代码代码如下: <?php class Page { private $pageSize;//每一页多少行 private $absolutePage;//当前页 private $pageCount;//总页码数 private $totalNum;//总行数 private $prePage;//上一页 private $nextPage; //下一页 private $sqlStr; private $baseUrl; //不带GET参数的URL get传输网址 function _
解析Android中实现滑动翻页之ViewFlipper的使用详解

1)View切换的控件-ViewFlipper介绍 ViewFilpper类继承于ViewAnimator类.而ViewAnimator类继承于FrameLayout. 查看ViewAnimator类的源码可以看出此类的作用主要是为其中的View切换提供动画效果.该类有如下几个和动画相关的方法. setInAnimation:设置View进入屏幕时候使用的动画.该方法有两个重载方法,即可以直接传入Animation对象,也可以传入定义的Animation文件的resourceID. setOut

通过MySQL优化Discuz!的热帖翻页的技巧

相关推荐

随机推荐