MySQL查詢(xún)語(yǔ)句優(yōu)化.ppt

MySQL查詢(xún)語(yǔ)句優(yōu)化 Peng xu 2010 11 19 目錄 為什么要對(duì)查詢(xún)進(jìn)行優(yōu)化哪些方面可以?xún)?yōu)化MySQL索引機(jī)制如何發(fā)現(xiàn)慢查詢(xún)?nèi)绾畏治雎樵?xún)?nèi)罩静樵?xún)語(yǔ)句優(yōu)化實(shí)例分析 為什么要對(duì)查詢(xún)進(jìn)行優(yōu)化 雅虎大部分產(chǎn)品都是對(duì)DB讀取量大 寫(xiě)入量少一個(gè)快速的頁(yè)面能顯著增加用戶(hù)體驗(yàn)一個(gè)沒(méi)有調(diào)試過(guò)的SQL會(huì)弄死整個(gè)網(wǎng)站 哪些方面可以?xún)?yōu)化 優(yōu)化硬件 操作系統(tǒng)優(yōu)化MySQL服務(wù)器優(yōu)化DB設(shè)計(jì)優(yōu)化SQL語(yǔ)句優(yōu)化應(yīng)用 MySQL索引機(jī)制 什么是索引與表數(shù)據(jù)分開(kāi)的獨(dú)立的有序的物理結(jié)構(gòu)索引的數(shù)據(jù)結(jié)構(gòu)Btree Hash索引的種類(lèi)主鍵 唯一索引 普通索引 復(fù)合索引 外鍵索引 如何發(fā)現(xiàn)慢查詢(xún) 1 查看MySQLSlowQuery修改f文件long query time 2log slow queries home y logs mysql mysqld slow log 如何發(fā)現(xiàn)慢查詢(xún) 2 用ysar命令查看在db服務(wù)器上安裝包ysar ysar mysql用ysar my命令查看 如何發(fā)現(xiàn)慢查詢(xún) 3 查看yapacheslow日志 為yapache安裝yapache mod yahoo slow log包找出加載慢的頁(yè)面 如何分析慢查詢(xún)?nèi)罩?mysqldumpslow sc t20host slow log按查詢(xún)次數(shù)排序 top20mysqldumpslow st t20host slow log按查詢(xún)時(shí)間排序 top20 使用MySQL自帶的mysqldumpslow命令分析 實(shí)例分析所用環(huán)境 開(kāi)發(fā)機(jī) h07 操作系統(tǒng) RHEL64 bitMySQL版本 mysql server 5 1 45 0 查詢(xún)語(yǔ)句優(yōu)化實(shí)例分析 單表 索引字段上進(jìn)行運(yùn)算會(huì)使索引失效usenengren 查詢(xún)2009年12月的文章explainselectdate format from unixtime create time Y m asdfromarticlewheredate format from unixtime create time Y m 200912 orderbycreate timeasclimit10 測(cè)試 運(yùn)算查詢(xún)更慢explainselectdate format from unixtime create time Y m asdfromarticlewhere create time 100 1259596800andcreate time 1259596800andcreate time 1262275200orderbycreate timeasclimit10 查詢(xún)語(yǔ)句優(yōu)化實(shí)例分析 單表 MySQL選擇的索引不一定是最好的usenengren 原始SQLexplainSELECTcount ascount stock code stock nameFROMarticleWHEREactive type 1ANDcreate time 1259596800GROUPBYstock codeLIMIT10 優(yōu)化后 執(zhí)行看效果 選擇少的記錄集更重要 explainSELECTcount ascount stock code stock nameFROMarticleUSEINDEX idx createtime WHEREactive type 1ANDcreate time 1259596800GROUPBYstock codeLIMIT10 查詢(xún)語(yǔ)句優(yōu)化實(shí)例分析 單表 簡(jiǎn)單的分頁(yè)方法 usenengren 原始SQLexplainselectarticle id titlefromarticleorderbyarticle iddesclimit30000 10 優(yōu)化后explainselectarticle id titlefromarticlewherearticle id 100000orderbyarticle iddesclimit10 查詢(xún)語(yǔ)句優(yōu)化實(shí)例分析 兩表 兩表關(guān)聯(lián)查詢(xún)?cè)黾优判蜃侄?能夠啟用索引 優(yōu)化前explainselecti gallery id i imagewidth t fromimage tagtinnerjoinimageioni imageurl t index urllimit0 10 優(yōu)化后explainselecti gallery id i imagewidth t fromimage tagtinnerjoinimageioni imageurl t index urlorderbyt iddesclimit0 10 查詢(xún)語(yǔ)句優(yōu)化實(shí)例分析 兩表 兩表關(guān)聯(lián)查詢(xún)加上排序字段后有時(shí)也無(wú)法啟用索引 usenengren test 優(yōu)化前 生產(chǎn)機(jī)b和開(kāi)發(fā)機(jī)explain結(jié)果不一樣 需使用AnalyzeTable修復(fù)索引 explainselecta article id a title b telfromarticleainnerjoinnengrenbona yuid b yuidorderbya article iddesclimit10 優(yōu)化后explainselecta article id a title b telfromarticlealeftjoinnengrenbona yuid b yuidorderbya article iddesclimit10 查詢(xún)語(yǔ)句優(yōu)化實(shí)例分析 三表 一個(gè)三表關(guān)聯(lián)查詢(xún)的優(yōu)化 usejack 原始SQLexplainselecti id i icon i nickname i name i gender i applyTime v valuefromcontestant infoasiinnerjoincontestant statusassoni id s idleftjoinvotesasvoni id v uidwheres status 查詢(xún)語(yǔ)句優(yōu)化實(shí)例分析 三表 使用子查詢(xún)優(yōu)化selecta id a icon a nickname a name a gender a applyTime b valuefrom selecti id i icon i nickname i name i gender i applyTimefromcontestant infoasiinnerjoincontestant statusassoni id s idwheres status 查詢(xún)語(yǔ)句優(yōu)化實(shí)例分析 三表 三表關(guān)聯(lián)SQL的簡(jiǎn)化 原始SQLexplainselectcount i id as cnt fromcontestant infoasileftjoincontestant statusassoni id s idleftjoinvotesasvoni id v uidwheres status 查詢(xún)語(yǔ)句優(yōu)化實(shí)例分析 三表 與上面的SQL取得的結(jié)果不同explainselectcount fromcontestant infoasileftjoin selectidfromcontestant statuswhereid 38570 assoni id s id 如果contestant status是contestant info的子集 votes是contestant status的子集 那么優(yōu)化后的SQLexplainselectcount id as cnt fromcontestant statuswherestatus 查詢(xún)語(yǔ)句優(yōu)化實(shí)例分析 三表 有時(shí)MySQL選擇的索引是最好的 減少記錄集比去掉filesort更重要 usenengren 原始SQLexplainSELECTb grade a FROMarticleASaforceindex idx createtime INNERJOIN SELECTm n grade n point n point toplistFROMnengrenASmINNERJOINnengren pointASnONm yuid n yuidWHEREm status 1ANDn grade 4 ASbONa yuid b yuidWHEREa forecast type 1ANDa forecast yn 0ANDa trend 1ANDa active type 1ORDERBYa create timeDESCLIMIT10 優(yōu)化后explainSELECTa article id n grade a titleFROMarticleASaINNERJOINnengrenasmona yuid m yui