這篇文章主要介紹了通過MySQL優化Discuz!的熱帖翻頁的技巧,包括更新索引來降低服務器負載等方面,需要的朋友可以參考下

寫在前面：discuz!作為首屈一指的社區系統，為廣大站長提供了一站式網站解決方案，而且是開源的(雖然部分代碼是加密的)，它為這個垂直領域的行業發展作出了巨大貢獻。盡管如此，discuz!系統源碼中，還是或多或少有些坑。其中最著名的就是默認采用MyISAM引擎，以及基于MyISAM引擎的搶樓功能，session表采用memory引擎等，可以參考后面幾篇歷史文章。本次我們要說說discuz!在應對熱們帖子翻頁邏輯功能中的另一個問題。

在我們的環境中，使用的是 MySQL-5.6.6 版本。

在查看帖子并翻頁過程中，會產生類似下面這樣的SQL：

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

這個SQL執行的代價是：

-- 根據索引訪問行記錄次數，總體而言算是比較好的狀態

1. | Handler_read_key | 16 | 

-- 根據索引順序訪問下一行記錄的次數，通常是因為根據索引的范圍掃描，或者全索引掃描，總體而言也算是比較好的狀態

1. | Handler_read_next | 329881 | 

-- 按照一定順序讀取行記錄的總次數。如果需要對結果進行排序，該值通常會比較大。當發生全表掃描或者多表join無法使用索引時，該值也會比較大

1. | Handler_read_rnd | 15 | 

而當遇到熱帖需要往后翻很多頁時，例如：

1. mysql> desc SELECT * FROM pre_forum_post WHERE 
2. tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860, 15/G 
3. *************************** 1. row *************************** 
4. id: 1 
5. select_type: SIMPLE 
6. table: pre_forum_post 
7. type: ref 
8. possible_keys: displayorder 
9. key: displayorder 
10. key_len: 3 
11. ref: const 
12. rows: 593371 
13. 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 的基數太低，所以沒包含在聯合索引中。當然了，也可以考慮一并加上)。

我們再來看下采用新的索引后的執行計劃：

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

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

不過，如果該熱帖翻到較舊的歷史回復時，相應的SQL還是不能使用新的索引：

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

對比下如果建議優化器使用新索引的話，其執行計劃是怎樣的：

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

可以看到，因為查詢優化器認為后者需要掃描的行數遠比前者多了11萬多，因此認為前者效率更高。

事實上，在這個例子里，排序的代價更高，因此我們要優先消除排序，所以應該強制使用新的索引，也就是采用后面的執行計劃，在相應的程序中指定索引。

最后，我們來看下熱帖翻到很老的歷史回復時，兩個執行計劃分別的profiling統計信息對比：

1、采用舊索引(displayorder)：

1. mysql> SELECT * FROM pre_forum_post WHERE 
2. tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15; 
3.  
4. #查看profiling結果 
5. | starting | 0.020203 | 
6. | checking permissions | 0.000026 | 
7. | Opening tables | 0.000036 | 
8. | init | 0.000099 | 
9. | System lock | 0.000092 | 
10. | optimizing | 0.000038 | 
11. | statistics | 0.000123 | 
12. | preparing | 0.000043 | 
13. | Sorting result | 0.000025 | 
14. | executing | 0.000023 | 
15. | Sending data | 0.000045 | 
16. | Creating sort index | 0.941434 | 
17. | end | 0.000077 | 
18. | query end | 0.000044 | 
19. | closing tables | 0.000038 | 
20. | freeing items | 0.000056 | 
21. | cleaning up | 0.000040 | 

2、如果是采用新索引(idx_tid_dateline)：

1. mysql> SELECT * FROM pre_forum_post use index(idx_tid_dateline) WHERE 
2. tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15; 
3.  
4. #對比查看profiling結果 
5. | starting | 0.000151 | 
6. | checking permissions | 0.000033 | 
7. | Opening tables | 0.000040 | 
8. | init | 0.000105 | 
9. | System lock | 0.000044 | 
10. | optimizing | 0.000038 | 
11. | statistics | 0.000188 | 
12. | preparing | 0.000044 | 
13. | Sorting result | 0.000024 | 
14. | executing | 0.000023 | 
15. | Sending data | 0.917035 | 
16. | end | 0.000074 | 
17. | query end | 0.000030 | 
18. | closing tables | 0.000036 | 
19. | freeing items | 0.000049 | 
20. | cleaning up | 0.000032 | 

可以看到，效率有了一定提高，不過不是很明顯，因為確實需要掃描的數據量更大，所以 Sending data 階段耗時更多。

這時候，我們可以再參考之前的一個優化方案：[MySQL優化案例]系列 — 分頁優化

然后可以將這個SQL改寫成下面這樣：

1. mysql> EXPLAIN SELECT * FROM pre_forum_post t1 INNER JOIN ( 
2. SELECT id FROM pre_forum_post use index(idx_tid_dateline) WHERE 
3. tid=8201301 AND `invisible` IN('0','-2') ORDER BY 
4. dateline LIMIT 129860,15) t2 
5. USING (id)/G 
6. *************************** 1. row *************************** 
7. id: 1 
8. select_type: PRIMARY 
9. table:  
10. type: ALL 
11. possible_keys: NULL 
12. key: NULL 
13. key_len: NULL 
14. ref: NULL 
15. rows: 129875 
16. Extra: NULL 
17. *************************** 2. row *************************** 
18. id: 1 
19. select_type: PRIMARY 
20. table: t1 
21. type: eq_ref 
22. possible_keys: PRIMARY 
23. key: PRIMARY 
24. key_len: 4 
25. ref: t2.id 
26. rows: 1 
27. Extra: NULL 
28. *************************** 3. row *************************** 
29. id: 2 
30. select_type: DERIVED 
31. table: pre_forum_post 
32. type: ref 
33. possible_keys: idx_tid_dateline 
34. key: idx_tid_dateline 
35. key_len: 3 
36. ref: const 
37. rows: 703892 
38. Extra: Using where 

再看下這個SQL的 profiling 統計信息：

1. | starting | 0.000209 | 
2. | checking permissions | 0.000026 | 
3. | checking permissions | 0.000026 | 
4. | Opening tables | 0.000101 | 
5. | init | 0.000062 | 
6. | System lock | 0.000049 | 
7. | optimizing | 0.000025 | 
8. | optimizing | 0.000037 | 
9. | statistics | 0.000106 | 
10. | preparing | 0.000059 | 
11. | Sorting result | 0.000039 | 
12. | statistics | 0.000048 | 
13. | preparing | 0.000032 | 
14. | executing | 0.000036 | 
15. | Sending data | 0.000045 | 
16. | executing | 0.000023 | 
17. | Sending data | 0.225356 | 
18. | end | 0.000067 | 
19. | query end | 0.000028 | 
20. | closing tables | 0.000023 | 
21. | removing tmp table | 0.000029 | 
22. | closing tables | 0.000044 | 
23. | freeing items | 0.000048 | 
24. | cleaning up | 0.000037 | 

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

最后說明下，這個問題只會在熱帖翻頁時才會出現，一般只有1,2頁回復的帖子如果還采用原來的執行計劃，也沒什么問題。

因此，建議discuz!官方修改或增加下新索引，并且在代碼中判斷是否熱帖翻頁，是的話，就強制使用新的索引，以避免性能問題。