备注：插图来自discuz!官方LOGO，如果觉得不当还请及时告知 ：)
写在前面：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!官方修改或增加下新索引，并且在代码中判断是否热帖翻页，是的话，就强制使用新的索引，以避免性能问题。
扩展阅读：
最后稍微吐槽一下：最近几天遇到了几起关于MySQL查询优化器的BUG，挺让人摸不着头脑的 ：(
Pingback: Discuz!热帖翻页优化 | 学习笔记
世界喊话/o空格+喊话类容,如果你被坑杀可以打/g fh 就可以直接主城复活自由PK服务！在百人战场PK胜利获得 积分 以及物品奖励VIP均为免费升级！在线泡点可换积分 装备 物品・・・・・VIP均为免费获取，起步简单，装备好混，战场更激情，单刷，副本，BOSS【激情战场】 每隔3小时开启一次 杀人获得10积分 获得战歌峡谷奖励胜利获得胜利者宝箱 宝箱可以开出VIP碎片1—5 开出积分1—10W积分 数量随机失败获得失败者宝箱 宝箱有几率开出VIP碎片1—5 开出积分1—1W积分 数量随机【天降宝箱】中午12点开启 晚上8点开启 宝箱可以开出VIP1—-VIP20的会员碎
三唑仑哪里有海乐神多少钱微信sddf828三唑氯安定酣乐欣
三唑仑350元，保密发货，真实货源，诚信第一，百分百见效
【乙醚800元】【七氟烷1500元】【听话水900元】【GHB 900元】【乖乖水900元】【迷魂药（粉末溶于水）500元】【kkk3 1200元】【γ-羟丁酸900元】
�物 掉落的低级武器,不再是垃圾） 装备合成：———————————————-+9的装备都可以兑换终极装备,这样防止新地图开放后,老装备掉价（所有合到+9的装备都有用途,不会浪费您金子) 宠物合成：———————————————-新手宠物，在伊甸园附近商店可以购买。宠物如何升级：不同级别的宠物升级需要不同的怪物卡片。(阿菜会掉落阿菜卡片,变色龙会掉落变色龙卡片)召唤宝宝/养育系统：———————————————-在游
1:上线秒升Lv95,全天泡点,每日福利,整点福利,全网最全的玩法，起步超快，上线送无CD称号，+31武器2:本服属于材料服.所有的门票均可在赛利亚换取，所有Npc全部分布在暗黑城！3:所有装备都可以爆出来，副本掉落大量材料，VIP副本掉落各种成品装备！4:全装备回收，上千件DIY装备，绝对不会让你枯燥无味，速度加入我们5:全新改版增强异界套装，属性贼拉牛逼，与神装可媲美，免费即可做出!6:全网独家制作个性时装,光环,宠物,称号,全网独家超变版本！全网独家自制版本，翅膀光环，上千件DIY装备，所有装备回收！独家特色:各种新东西、特色装备、超视觉体验时装、皮肤、宠物、超越国服。