《Mysql實例通過MySQL優(yōu)化Discuz!的熱帖翻頁的技巧》要點：
本文介紹了Mysql實例通過MySQL優(yōu)化Discuz!的熱帖翻頁的技巧，希望對您有用。如果有疑問，可以聯(lián)系我們。
MYSQL數(shù)據(jù)庫寫在前面：discuz!作為首屈一指的社區(qū)系統(tǒng),為廣大站長提供了一站式網(wǎng)站辦理方案,而且是開源的(雖然部分代碼是加密的),它為這個垂直領(lǐng)域的行業(yè)發(fā)展作出了巨大貢獻.盡管如此,discuz!系統(tǒng)源碼中,還是或多或少有些坑.其中最著名的就是默認采用MyISAM引擎,以及基于MyISAM引擎的搶樓功能,session表采用memory引擎等,可以參考后面幾篇歷史文章.本次我們要說說discuz!在應(yīng)對熱們帖子翻頁邏輯功能中的另一個問題.
MYSQL數(shù)據(jù)庫在我們的環(huán)境中,使用的是 MySQL-5.6.6 版本.
MYSQL數(shù)據(jù)庫在查看帖子并翻頁過程中,會產(chǎn)生類似下面這樣的SQL：
MYSQL數(shù)據(jù)庫
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
MYSQL數(shù)據(jù)庫這個SQL執(zhí)行的代價是：
MYSQL數(shù)據(jù)庫-- 根據(jù)索引拜訪行記錄次數(shù),總體而言算是比較好的狀態(tài)
MYSQL數(shù)據(jù)庫
| Handler_read_key | 16 |
MYSQL數(shù)據(jù)庫-- 根據(jù)索引順序拜訪下一行記錄的次數(shù),通常是因為根據(jù)索引的范圍掃描,或者全索引掃描,總體而言也算是比較好的狀態(tài)
MYSQL數(shù)據(jù)庫
| Handler_read_next | 329881 |
MYSQL數(shù)據(jù)庫-- 依照一定順序讀取行記錄的總次數(shù).如果需要對結(jié)果進行排序,該值通常會比較大.當(dāng)發(fā)生全表掃描或者多表join無法使用索引時,該值也會比較大
MYSQL數(shù)據(jù)庫
| Handler_read_rnd | 15 |
MYSQL數(shù)據(jù)庫而當(dāng)遇到熱帖必要往后翻很多頁時,例如：
MYSQL數(shù)據(jù)庫
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
MYSQL數(shù)據(jù)庫這個SQL執(zhí)行的代價則釀成了(可以看到Handler_read_key、Handler_read_rnd大了很多)：
MYSQL數(shù)據(jù)庫| Handler_read_key?????????? | 129876 | -- 因為前面必要跳過很多行記錄
| Handler_read_next????????? | 329881 | -- 同上
| Handler_read_rnd?????????? | 129875 | -- 因為必要先對很大一個結(jié)果集進行排序
MYSQL數(shù)據(jù)庫可見,遇到熱帖時,這個SQL的代價會非常高.如果該熱帖被大量的訪問歷史回復(fù),或者被搜素引擎一直反復(fù)哀求并且歷史回復(fù)頁時,很容易把數(shù)據(jù)庫服務(wù)器直接壓垮.
MYSQL數(shù)據(jù)庫小結(jié)：這個SQL不能利用 `displayorder` 索引排序的原因是,索引的第二個列 `invisible` 采用范圍查詢(RANGE),導(dǎo)致沒方法繼續(xù)利用聯(lián)合索引完成對 `dateline` 字段的排序需求(而如果是 WHERE tid =? AND invisible IN(?, ?) AND dateline =? 這種情況下是完全可以用到整個聯(lián)合索引的,注意下二者的區(qū)別).
MYSQL數(shù)據(jù)庫知道了這個原因,相應(yīng)的優(yōu)化解決方法也就清晰了：
創(chuàng)建一個新的索引 idx_tid_dateline,它只包括 tid、dateline 兩個列即可(根據(jù)其他索引的統(tǒng)計信息,item_type 和 item_id 的基數(shù)太低,所以沒包含在聯(lián)合索引中.當(dāng)然了,也可以考慮一并加上).
MYSQL數(shù)據(jù)庫我們再來看下采用新的索引后的執(zhí)行計劃：
MYSQL數(shù)據(jù)庫
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
MYSQL數(shù)據(jù)庫可以看到,之前存在的 Using filesort 消失了,可以通過索引直接完成排序了.
MYSQL數(shù)據(jù)庫不過,如果該熱帖翻到較舊的歷史回復(fù)時,相應(yīng)的SQL還是不能使用新的索引：
MYSQL數(shù)據(jù)庫
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數(shù)據(jù)庫對比下如果建議優(yōu)化器使用新索引的話,其執(zhí)行計劃是怎樣的：
MYSQL數(shù)據(jù)庫
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
MYSQL數(shù)據(jù)庫可以看到,因為查詢優(yōu)化器認為后者必要掃描的行數(shù)遠比前者多了11萬多,因此認為前者效率更高.
MYSQL數(shù)據(jù)庫事實上,在這個例子里,排序的代價更高,因此我們要優(yōu)先消除排序,所以應(yīng)該強制使用新的索引,也便是采用后面的執(zhí)行計劃,在相應(yīng)的程序中指定索引.
MYSQL數(shù)據(jù)庫最后,我們來看下熱帖翻到很老的歷史回復(fù)時,兩個執(zhí)行計劃分其余profiling統(tǒng)計信息對比：
MYSQL數(shù)據(jù)庫1、采用舊索引(displayorder)：
MYSQL數(shù)據(jù)庫
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 |
MYSQL數(shù)據(jù)庫2、如果是采用新索引(idx_tid_dateline)：
MYSQL數(shù)據(jù)庫
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結(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 |
MYSQL數(shù)據(jù)庫可以看到,效率有了必定提高,不過不是很明顯,因為確實需要掃描的數(shù)據(jù)量更大,所以 Sending data 階段耗時更多.
MYSQL數(shù)據(jù)庫這時候,我們可以再參考之前的一個優(yōu)化方案：[MySQL優(yōu)化案例]系列 ― 分頁優(yōu)化
MYSQL數(shù)據(jù)庫然后可以將這個SQL改寫成下面這樣：
MYSQL數(shù)據(jù)庫
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
MYSQL數(shù)據(jù)庫再看下這個SQL的 profiling 統(tǒng)計信息：
MYSQL數(shù)據(jù)庫
| 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 |
MYSQL數(shù)據(jù)庫可以看到,效率提升了1倍以上,還是挺不錯的.
MYSQL數(shù)據(jù)庫最后說明下,這個問題只會在熱帖翻頁時才會出現(xiàn),一般只有1,2頁回復(fù)的帖子如果還采用本來的執(zhí)行計劃,也沒什么問題.
MYSQL數(shù)據(jù)庫因此,建議discuz!官方修改或增加下新索引,而且在代碼中判斷是否熱帖翻頁,是的話,就強制使用新的索引,以避免性能問題.
《Mysql實例通過MySQL優(yōu)化Discuz!的熱帖翻頁的技巧》是否對您有啟發(fā)，歡迎查看更多與《Mysql實例通過MySQL優(yōu)化Discuz!的熱帖翻頁的技巧》相關(guān)教程，學(xué)精學(xué)透。維易PHP學(xué)院為您提供精彩教程。
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/12362.html
