寫(xiě)在前面：discuz!作為首屈一指的社區(qū)系統(tǒng)，為廣大站長(zhǎng)提供了一站式網(wǎng)站解決方案，而且是開(kāi)源的（雖然部分代碼是加密的），它為這個(gè)垂直領(lǐng)域的行業(yè)發(fā)展作出了巨大貢獻(xiàn)。盡管如此，discuz!系統(tǒng)源碼中，還是或多或少有些坑。其中最著名的就是默認(rèn)采用MyISAM引擎，以及基于MyISAM引擎的搶樓功能，session表采用memory引擎等，可以參考后面幾篇?dú)v史文章。本次我們要說(shuō)說(shuō)discuz!在應(yīng)對(duì)熱們帖子翻頁(yè)邏輯功能中的另一個(gè)問(wèn)題。
在我們的環(huán)境中，使用的是 MySQL-5.6.6 版本。
在查看帖子并翻頁(yè)過(guò)程中，會(huì)產(chǎn)生類(lèi)似下面這樣的SQL：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 |
這個(gè)SQL執(zhí)行的代價(jià)是：
-- 根據(jù)索引訪問(wèn)行記錄次數(shù)，總體而言算是比較好的狀態(tài)
|
1
|
| Handler_read_key | 16 | |
-- 根據(jù)索引順序訪問(wèn)下一行記錄的次數(shù)，通常是因?yàn)楦鶕?jù)索引的范圍掃描，或者全索引掃描，總體而言也算是比較好的狀態(tài)
|
1
|
| Handler_read_next | 329881 | |
-- 按照一定順序讀取行記錄的總次數(shù)。如果需要對(duì)結(jié)果進(jìn)行排序，該值通常會(huì)比較大。當(dāng)發(fā)生全表掃描或者多表join無(wú)法使用索引時(shí)，該值也會(huì)比較大
|
1
|
| Handler_read_rnd | 15 | |
而當(dāng)遇到熱帖需要往后翻很多頁(yè)時(shí)，例如：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 |
這個(gè)SQL執(zhí)行的代價(jià)則變成了（可以看到Handler_read_key、Handler_read_rnd大了很多）：
| Handler_read_key | 129876 | -- 因?yàn)榍懊嫘枰^(guò)很多行記錄
| Handler_read_next | 329881 | -- 同上
| Handler_read_rnd | 129875 | -- 因?yàn)樾枰葘?duì)很大一個(gè)結(jié)果集進(jìn)行排序
可見(jiàn)，遇到熱帖時(shí)，這個(gè)SQL的代價(jià)會(huì)非常高。如果該熱帖被大量的訪問(wèn)歷史回復(fù)，或者被搜素引擎一直反復(fù)請(qǐng)求并且歷史回復(fù)頁(yè)時(shí)，很容易把數(shù)據(jù)庫(kù)服務(wù)器直接壓垮。
小結(jié)：這個(gè)SQL不能利用 `displayorder` 索引排序的原因是，索引的第二個(gè)列 `invisible` 采用范圍查詢(xún)（RANGE），導(dǎo)致沒(méi)辦法繼續(xù)利用聯(lián)合索引完成對(duì) `dateline` 字段的排序需求（而如果是 WHERE tid =? AND invisible IN(?, ?) AND dateline =? 這種情況下是完全可以用到整個(gè)聯(lián)合索引的，注意下二者的區(qū)別）。
知道了這個(gè)原因，相應(yīng)的優(yōu)化解決辦法也就清晰了：
創(chuàng)建一個(gè)新的索引 idx_tid_dateline，它只包括 tid、dateline 兩個(gè)列即可（根據(jù)其他索引的統(tǒng)計(jì)信息，item_type 和 item_id 的基數(shù)太低，所以沒(méi)包含在聯(lián)合索引中。當(dāng)然了，也可以考慮一并加上）。
我們?cè)賮?lái)看下采用新的索引后的執(zhí)行計(jì)劃：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 消失了，可以通過(guò)索引直接完成排序了。
不過(guò)，如果該熱帖翻到較舊的歷史回復(fù)時(shí)，相應(yīng)的SQL還是不能使用新的索引：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 |
對(duì)比下如果建議優(yōu)化器使用新索引的話(huà)，其執(zhí)行計(jì)劃是怎樣的：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 |
可以看到，因?yàn)椴樵?xún)優(yōu)化器認(rèn)為后者需要掃描的行數(shù)遠(yuǎn)比前者多了11萬(wàn)多，因此認(rèn)為前者效率更高。
事實(shí)上，在這個(gè)例子里，排序的代價(jià)更高，因此我們要優(yōu)先消除排序，所以應(yīng)該強(qiáng)制使用新的索引，也就是采用后面的執(zhí)行計(jì)劃，在相應(yīng)的程序中指定索引。
最后，我們來(lái)看下熱帖翻到很老的歷史回復(fù)時(shí)，兩個(gè)執(zhí)行計(jì)劃分別的profiling統(tǒng)計(jì)信息對(duì)比：
1、采用舊索引（displayorder）：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql> SELECT * FROM pre_forum_post WHERE tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15;#查看profiling結(jié)果 | 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）：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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;#對(duì)比查看profiling結(jié)果 | 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 | |
可以看到，效率有了一定提高，不過(guò)不是很明顯，因?yàn)榇_實(shí)需要掃描的數(shù)據(jù)量更大，所以 Sending data 階段耗時(shí)更多。
這時(shí)候，我們可以再參考之前的一個(gè)優(yōu)化方案：[MySQL優(yōu)化案例]系列 — 分頁(yè)優(yōu)化
然后可以將這個(gè)SQL改寫(xiě)成下面這樣：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
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 |
再看下這個(gè)SQL的 profiling 統(tǒng)計(jì)信息：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
| 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倍以上，還是挺不錯(cuò)的。
最后說(shuō)明下，這個(gè)問(wèn)題只會(huì)在熱帖翻頁(yè)時(shí)才會(huì)出現(xiàn)，一般只有1,2頁(yè)回復(fù)的帖子如果還采用原來(lái)的執(zhí)行計(jì)劃，也沒(méi)什么問(wèn)題。
因此，建議discuz!官方修改或增加下新索引，并且在代碼中判斷是否熱帖翻頁(yè)，是的話(huà)，就強(qiáng)制使用新的索引，以避免性能問(wèn)題。
