程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 經由過程MySQL優化Discuz!的熱帖翻頁的技能

經由過程MySQL優化Discuz!的熱帖翻頁的技能

編輯：MySQL綜合教程

經由過程MySQL優化Discuz!的熱帖翻頁的技能。本站提示廣大學習愛好者：（經由過程MySQL優化Discuz!的熱帖翻頁的技能）文章只能為提供參考，不一定能成為您想要的結果。以下是經由過程MySQL優化Discuz!的熱帖翻頁的技能正文

寫在後面：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!官方修正或增長下新索引，而且在代碼中斷定能否熱帖翻頁，是的話，就強迫應用新的索引，以免機能成績。