《Mysql應(yīng)用MySQL性能優(yōu)化 出題業(yè)務(wù)SQL優(yōu)化》要點：
本文介紹了Mysql應(yīng)用MySQL性能優(yōu)化 出題業(yè)務(wù)SQL優(yōu)化，希望對您有用。如果有疑問，可以聯(lián)系我們。

先簡單介紹一下項目背景.這是一個在線考試練習平臺,數(shù)據(jù)庫使用MySQL,表結(jié)構(gòu)如圖所示：MYSQL應(yīng)用

MYSQL應(yīng)用

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

需求：根據(jù)用戶的作答結(jié)果出練習卷,題目的優(yōu)先級為：未做過的題目>只做錯的題目>做錯又做對的題目>只做對的題目.MYSQL應(yīng)用

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

備注：AnswerResult表中不存在QuestionId的記錄,則代表該題沒有做過.MYSQL應(yīng)用

之前使用的方法：MYSQL應(yīng)用

　　SELECT Question.題目標識,IFNULL((0-正確次數(shù))/(正確次數(shù)+錯誤次數(shù)),1) AS 權(quán)重?FROM QuestionMYSQL應(yīng)用

　　LEFT JOIN AnswerResult ON AnswerResult.題目標識 = Question.題目標識MYSQL應(yīng)用

　　WHERE 用戶標識={UserId}MYSQL應(yīng)用

說明：IFNULL((0-正確次數(shù))/(正確次數(shù)+錯誤次數(shù)),1)這個函數(shù)式分2部分,MYSQL應(yīng)用

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

由于AnswerResult表是多達300、400百萬的表,所以通過LEFT JOIN進行左連接時,迪卡爾乘積過大,又加上AnswerResult是頻繁讀寫的表,很容易導致這條SQL變成慢查詢.MYSQL應(yīng)用

性能問題被提上日程后,這條SQL語句就變成的優(yōu)化點.MYSQL應(yīng)用

1、IFNULL()這個函數(shù)計算可以調(diào)整成冗余字段.MYSQL應(yīng)用

2、LEFT JOIN的迪卡爾乘積太大,可以調(diào)整為冗余或者使用INNER JOIN以提高查詢速度.MYSQL應(yīng)用

3、根據(jù)需求,其實可以調(diào)整出題策略,不同的情況執(zhí)行不同的SQL,而不需要在同一條SQL中實現(xiàn).MYSQL應(yīng)用

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

　　A:SELECT 題目標識 FROM Question WHERE?知識點={KnowledgePointCode} AND?題目標識 NOT IN (MYSQL應(yīng)用

　　　　SELECT 題目標識 FROM AnswerResult INNER JOIN Question AND Question.知識點={KnowledgePointCode}MYSQL應(yīng)用

　　　　WHERE AnswerResult.用戶標識 = {UserId}MYSQL應(yīng)用

　　)MYSQL應(yīng)用

針對只做錯的題目出題練習就簡單了(正確次數(shù) = 0代表只做錯),SQL如：MYSQL應(yīng)用

　　B:SELECT 題目標識 FROM AnswerResult INNER JOIN Question AND Question.知識點={KnowledgePointCode}MYSQL應(yīng)用

　　WHERE AnswerResult.用戶標識 = {UserId} AND?正確次數(shù) = 0 ORDER BY 錯誤次數(shù) DESCMYSQL應(yīng)用

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

　　C:SELECT 題目標識 FROM AnswerResult INNER JOIN Question AND Question.知識點={KnowledgePointCode}MYSQL應(yīng)用

　　WHERE AnswerResult.用戶標識 = {UserId} AND?正確次數(shù)?> 0?ORDER BY?權(quán)重 DESCMYSQL應(yīng)用

?MYSQL應(yīng)用

不足：SQL語句A的查詢速度依然是較慢的,雖然有縮小NOT IN的結(jié)果集,但這里還是有優(yōu)化點.園子里的朋友們能不能給點建議?MYSQL應(yīng)用

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

望園子里有這方面經(jīng)驗的朋友不吝賜教.謝謝.MYSQL應(yīng)用

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/3401.html