先簡單介紹一下項(xiàng)目背景。這是一個在線考試練習(xí)平臺，數(shù)據(jù)庫使用MySQL，表結(jié)構(gòu)如圖所示：

Question是存儲題目的表，數(shù)據(jù)量在3萬左右。AnswerResult表是存儲用戶作答結(jié)果的表，分表之后單表記錄大概在300萬-400萬。

需求：根據(jù)用戶的作答結(jié)果出練習(xí)卷，題目的優(yōu)先級為：未做過的題目>只做錯的題目>做錯又做對的題目>只做對的題目。

在“做錯又做對的題目”中，會按錯誤次數(shù)和正確次數(shù)的比例進(jìn)行權(quán)重計(jì)算，比如：A、做錯10次，做對100次；B、做錯10次，做對20次。這時B被選中出給用戶練習(xí)的概率就大。

備注：AnswerResult表中不存在QuestionId的記錄，則代表該題沒有做過。

之前使用的方法：

　　SELECT Question.題目標(biāo)識,IFNULL((0-正確次數(shù))/(正確次數(shù)+錯誤次數(shù)),1) AS 權(quán)重 FROM Question

　　LEFT JOIN AnswerResult ON AnswerResult.題目標(biāo)識 = Question.題目標(biāo)識

　　WHERE 用戶標(biāo)識={UserId}

說明：IFNULL((0-正確次數(shù))/(正確次數(shù)+錯誤次數(shù)),1)這個函數(shù)式分2部分，

公式：(0-正確次數(shù))/(正確次數(shù)+錯誤次數(shù))得到題目的權(quán)重，這個區(qū)間為[0,-1]，0表示只做錯的題目，-1表示只做對的題目。IFNULL(value,1)則將未做過的題目權(quán)重設(shè)置為1，根據(jù)這個權(quán)重進(jìn)行排序列出題目。

由于AnswerResult表是多達(dá)300、400百萬的表，所以通過LEFT JOIN進(jìn)行左連接時，迪卡爾乘積過大，又加上AnswerResult是頻繁讀寫的表，很容易導(dǎo)致這條SQL變成慢查詢。

性能問題被提上日程后，這條SQL語句就變成的優(yōu)化點(diǎn)。

1、IFNULL()這個函數(shù)計(jì)算可以調(diào)整成冗余字段。

2、LEFT JOIN的迪卡爾乘積太大，可以調(diào)整為冗余或者使用INNER JOIN以提高查詢速度。

3、根據(jù)需求，其實(shí)可以調(diào)整出題策略，不同的情況執(zhí)行不同的SQL，而不需要在同一條SQL中實(shí)現(xiàn)。

解決方案針對以上三個點(diǎn)進(jìn)行調(diào)整。雖然Question表有3萬條數(shù)據(jù)，但是出題的場景其實(shí)是針對知識點(diǎn)出題，單個知識點(diǎn)題目最多也只有1000題左右，所以獲取未做過的題目時，完全可以使用NOT IN走索引來完成。SQL語句如：

　　A:SELECT 題目標(biāo)識 FROM Question WHERE 知識點(diǎn)={KnowledgePointCode} AND 題目標(biāo)識 NOT IN (

　　　　SELECT 題目標(biāo)識 FROM AnswerResult INNER JOIN Question AND Question.知識點(diǎn)={KnowledgePointCode}

　　　　WHERE AnswerResult.用戶標(biāo)識 = {UserId}

　　)

針對只做錯的題目出題練習(xí)就簡單了（正確次數(shù) = 0代表只做錯），SQL如：

　　B:SELECT 題目標(biāo)識 FROM AnswerResult INNER JOIN Question AND Question.知識點(diǎn)={KnowledgePointCode}

　　WHERE AnswerResult.用戶標(biāo)識 = {UserId} AND 正確次數(shù) = 0 ORDER BY 錯誤次數(shù) DESC

若要對做錯、做對或者只做對的題目進(jìn)行出題，SQL就是這樣的（已經(jīng)對權(quán)重進(jìn)行冗余=IFNULL((0-正確次數(shù))/(正確次數(shù)+錯誤次數(shù)),1)）：

　　C:SELECT 題目標(biāo)識 FROM AnswerResult INNER JOIN Question AND Question.知識點(diǎn)={KnowledgePointCode}

　　WHERE AnswerResult.用戶標(biāo)識 = {UserId} AND 正確次數(shù) > 0 ORDER BY 權(quán)重 DESC

不足：SQL語句A的查詢速度依然是較慢的，雖然有縮小NOT IN的結(jié)果集，但這里還是有優(yōu)化點(diǎn)。園子里的朋友們能不能給點(diǎn)建議？

有人說JOIN是SQL的性能殺手，我覺得主要還是怎么去使用JOIN，MySQL的索引優(yōu)化相當(dāng)重要，如果JOIN成為性能瓶頸，可以EXPLAIN看看是不是索引沒有建好，并且盡量讓迪卡爾乘積盡量小。使用冗余數(shù)據(jù)避免JOIN，當(dāng)可能變化的冗余數(shù)據(jù)被分表之后，更新這些冗余數(shù)據(jù)就是一件非常頭痛的事了。海量數(shù)據(jù)高并發(fā)，確實(shí)是一件挺頭痛的事。

望園子里有這方面經(jīng)驗(yàn)的朋友不吝賜教。謝謝。

最新評論