通過(guò)MySQL優(yōu)化Discuz!的熱帖翻頁(yè)的技巧
寫(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：
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ù)索引訪(fǎng)問(wèn)行記錄次數(shù)，總體而言算是比較好的狀態(tài)
| Handler_read_key | 16 |
-- 根據(jù)索引順序訪(fǎng)問(wèn)下一行記錄的次數(shù)，通常是因?yàn)楦鶕?jù)索引的范圍掃描，或者全索引掃描，總體而言也算是比較好的狀態(tài)
| Handler_read_next | 329881 |
-- 按照一定順序讀取行記錄的總次數(shù)。如果需要對(duì)結(jié)果進(jìn)行排序，該值通常會(huì)比較大。當(dāng)發(fā)生全表掃描或者多表join無(wú)法使用索引時(shí)，該值也會(huì)比較大
| Handler_read_rnd | 15 |
而當(dāng)遇到熱帖需要往后翻很多頁(yè)時(shí)，例如：
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ì)非常高。如果該熱帖被大量的訪(fǎng)問(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ì)劃：
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還是不能使用新的索引：
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ì)劃是怎樣的：
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）：
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）：
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ě)成下面這樣：
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ì)信息：
| 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)題。
- php中文本數(shù)據(jù)翻頁(yè)（留言本翻頁(yè)）
- 如何編寫(xiě)翻頁(yè)函數(shù)？
- 點(diǎn)圖片上一頁(yè)下一頁(yè)翻頁(yè)效果
- PHP 翻頁(yè) 實(shí)例代碼
- PHP 長(zhǎng)文章分頁(yè)函數(shù) 帶使用方法，不會(huì)分割段落,翻頁(yè)在底部
- php實(shí)現(xiàn)的仿阿里巴巴實(shí)現(xiàn)同類(lèi)產(chǎn)品翻頁(yè)
- 在圖片上顯示左右箭頭類(lèi)似翻頁(yè)的代碼
- 解析Android中實(shí)現(xiàn)滑動(dòng)翻頁(yè)之ViewFlipper的使用詳解
- PHP翻頁(yè)跳轉(zhuǎn)功能實(shí)現(xiàn)方法
- MongoDB快速翻頁(yè)的方法
相關(guān)文章
在mysql中設(shè)計(jì)表的時(shí)候，mysql官方推薦不要使用uuid或者不連續(xù)不重復(fù)的雪花id(long形且唯一)，而是推薦連續(xù)自增的主鍵id，官方的推薦是auto_increment，那么為什么不建議采用uuid，使用uuid究竟有什么壞處？本篇博客我們就來(lái)分析這個(gè)問(wèn)題，探討一下內(nèi)部的原因2022-03-03
MYSQL必知必會(huì)讀書(shū)筆記第八章之使用通配符進(jìn)行過(guò)濾
這篇文章主要介紹了MYSQL必知必會(huì)讀書(shū)筆記第八章之使用通配符進(jìn)行過(guò)濾的相關(guān)資料,需要的朋友可以參考下2016-05-05
在數(shù)據(jù)庫(kù)中,NULL 值表示缺失或未知的數(shù)據(jù),在 MySQL 中,我們可以使用特定的查詢(xún)語(yǔ)句來(lái)查找包含 NULL 值的數(shù)據(jù),本文將詳細(xì)介紹如何在 MySQL 中查找 NULL 值,并提供相關(guān)實(shí)例和代碼片段,需要的朋友可以參考下2024-05-05
時(shí)間處理是我們?nèi)粘ｉ_(kāi)發(fā)中經(jīng)常遇到的需求,下面這篇文章主要給大家介紹了關(guān)于如何用SQL獲取年、月、周幾、日、時(shí)的相關(guān)資料,文中通過(guò)圖文介紹的非常詳細(xì),需要的朋友可以參考下2022-12-12
MySQL數(shù)據(jù)導(dǎo)入導(dǎo)出的三種辦法總結(jié)
當(dāng)我們需要切換數(shù)據(jù)庫(kù)或備份數(shù)據(jù)時(shí),導(dǎo)入和導(dǎo)出數(shù)據(jù)庫(kù)是一個(gè)常見(jiàn)的操作,下面這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)導(dǎo)入導(dǎo)出的三種辦法,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-05-05
MySQL表鎖定問(wèn)題的原因、檢測(cè)與解決方案
在數(shù)據(jù)庫(kù)管理系統(tǒng)中,鎖是保證數(shù)據(jù)一致性和事務(wù)隔離性的重要機(jī)制,然而,鎖的使用也可能導(dǎo)致性能問(wèn)題,尤其是在高并發(fā)場(chǎng)景下,表鎖定（Table Locking）可能會(huì)成為系統(tǒng)的瓶頸,本文將深入探討MySQL中表鎖定的原因、如何檢測(cè)表鎖定問(wèn)題,并提供有效的解決方案2025-01-01
linux環(huán)境下配置mysql5.6支持IPV6連接的方法
本文主要介紹在linux系統(tǒng)下，如何配置mysql支持IPV6的連接，本文圖文并茂給大家介紹的非常詳細(xì)，具有參考借鑒價(jià)值，需要的朋友參考下吧2018-01-01
Oracle和MySQL中生成32位uuid的方法舉例(國(guó)產(chǎn)達(dá)夢(mèng)同Oracle)
近日遇到朋友問(wèn)及如何生成UUID,UUID是通用唯一識(shí)別碼(Universally Unique Identifier)方法,這里給大家總結(jié)下,這篇文章主要給大家介紹了關(guān)于Oracle和MySQL中生成32位uuid的方法,需要的朋友可以參考下2023-08-08
巧用mysql提示符prompt清晰管理數(shù)據(jù)庫(kù)的方法
隨著管理mysql服務(wù)器越來(lái)越多，同樣的mysql>的提示符有可能會(huì)讓你輸入錯(cuò)誤的命令到錯(cuò)誤的數(shù)據(jù)庫(kù)，這時(shí)候需要巧用mysql的提示符，這是我的提示符root@localhost(mysql) 08:55:21> 用prompt命令實(shí)現(xiàn)(適用于windows和linux環(huán)境)2009-08-08
Can''t connect to local MySQL through socket ''/tmp/mysql.so
今天小編就為大家分享一篇關(guān)于Can't connect to local MySQL through socket '/tmp/mysql.sock'解決方法，小編覺(jué)得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來(lái)看看吧2019-03-03