MySQL查詢語句優(yōu)化.ppt

MySQL查詢語句優(yōu)化 Peng xu 2010 11 19 目錄 為什么要對查詢進(jìn)行優(yōu)化哪些方面可以優(yōu)化MySQL索引機(jī)制如何發(fā)現(xiàn)慢查詢?nèi)绾畏治雎樵內(nèi)罩静樵冋Z句優(yōu)化實(shí)例分析 為什么要對查詢進(jìn)行優(yōu)化 雅虎大部分產(chǎn)品都是對DB讀取量大 寫入量少一個快速的頁面能顯著增加用戶體驗(yàn)一個沒有調(diào)試過的SQL會弄死整個網(wǎng)站 哪些方面可以優(yōu)化 優(yōu)化硬件 操作系統(tǒng)優(yōu)化MySQL服務(wù)器優(yōu)化DB設(shè)計(jì)優(yōu)化SQL語句優(yōu)化應(yīng)用 MySQL索引機(jī)制 什么是索引與表數(shù)據(jù)分開的獨(dú)立的有序的物理結(jié)構(gòu)索引的數(shù)據(jù)結(jié)構(gòu)Btree Hash索引的種類主鍵 唯一索引 普通索引 復(fù)合索引 外鍵索引 如何發(fā)現(xiàn)慢查詢 1 查看MySQLSlowQuery修改f文件long query time 2log slow queries home y logs mysql mysqld slow log 如何發(fā)現(xiàn)慢查詢 2 用ysar命令查看在db服務(wù)器上安裝包ysar ysar mysql用ysar my命令查看 如何發(fā)現(xiàn)慢查詢 3 查看yapacheslow日志 為yapache安裝yapache mod yahoo slow log包找出加載慢的頁面 如何分析慢查詢?nèi)罩?mysqldumpslow sc t20host slow log按查詢次數(shù)排序 top20mysqldumpslow st t20host slow log按查詢時間排序 top20 使用MySQL自帶的mysqldumpslow命令分析 實(shí)例分析所用環(huán)境 開發(fā)機(jī) h07 操作系統(tǒng) RHEL64 bitMySQL版本 mysql server 5 1 45 0 查詢語句優(yōu)化實(shí)例分析 單表 索引字段上進(jìn)行運(yùn)算會使索引失效usenengren 查詢2009年12月的文章explainselectdate format from unixtime create time Y m asdfromarticlewheredate format from unixtime create time Y m 200912 orderbycreate timeasclimit10 測試 運(yùn)算查詢更慢explainselectdate format from unixtime create time Y m asdfromarticlewhere create time 100 1259596800andcreate time 1259596800andcreate time 1262275200orderbycreate timeasclimit10 查詢語句優(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 查詢語句優(yōu)化實(shí)例分析 單表 簡單的分頁方法 usenengren 原始SQLexplainselectarticle id titlefromarticleorderbyarticle iddesclimit30000 10 優(yōu)化后explainselectarticle id titlefromarticlewherearticle id 100000orderbyarticle iddesclimit10 查詢語句優(yōu)化實(shí)例分析 兩表 兩表關(guān)聯(lián)查詢增加排序字段 能夠啟用索引 優(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 查詢語句優(yōu)化實(shí)例分析 兩表 兩表關(guān)聯(lián)查詢加上排序字段后有時也無法啟用索引 usenengren test 優(yōu)化前 生產(chǎn)機(jī)b和開發(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 查詢語句優(yōu)化實(shí)例分析 三表 一個三表關(guān)聯(liá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 查詢語句優(yōu)化實(shí)例分析 三表 使用子查詢優(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 查詢語句優(yōu)化實(shí)例分析 三表 三表關(guān)聯(lián)SQL的簡化 原始SQLexplainselectcount i id as cnt fromcontestant infoasileftjoincontestant statusassoni id s idleftjoinvotesasvoni id v uidwheres status 查詢語句優(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 查詢語句優(yōu)化實(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