MYSQL查詢性能優化為什么查詢速度會慢1優化數據訪問基礎2重構查詢的方式3查詢執行的基礎4目錄mysql查詢優化器的局限性5優化特定類型的查詢6andsoon...7結束8目錄為什么查詢速度會慢？1.查詢速度最重要的參數是響應時間。剖析mysql查詢詳見以后課時。2.是否在檢索大量超過需要的數據。太多的行和太多的列。3.mysql服務器是否在分析大量超過需要的數據行。優化數據訪問基礎設計合理的數據表結構：適當的數據冗余對數據表建立合適有效的數據庫索引數據查詢：編寫簡潔高效的SQL語句mysql執行順序：(8)SELECT(9)DISTINCT(11)<TOP_specification><select_list>(1)FROM<left_table>(3)<join_type>JOIN<right_table>(2)ON<join_condition>(4)WHERE<where_condition>(5)GROUPBY<group_by_list>(6)WITH{CUBE|ROLLUP}(7)HAVING<having_condition>(10)ORDERBY<order_by_list>1.借助MySQLTuner配置mysql參數wget/major/MySQLTuner-perl/master/mysqltuner.plchmod+xmysqltuner.pl./mysqltuner.pl//Recommendations2.使用innoDB引擎代替MyISAM

全文檢索使用替代方案來避免通過數據庫來查詢，譬如借助第三方搜索工具。重構查詢的方式1.切分查詢大的語句一次性完成，可能需要一次鎖住很多數據、占滿整個事務日志、耗盡系統資源、阻塞很多小的但重要的查詢。切分可以盡可能小的影響mysql性能，減少mysql復制的延遲。舉例刪除15年12月以前的消息，count(*)=300w：deletefrommessagewhereadd_time<'1449852756'row_affected=0do{row_affected=do_query("deletefrommessagewhereadd_time<'1449852756'")}whilerow_affected>0一次刪除1w條數據，可以放寬執行時間、減少鎖的持有時間。2.分解關聯查詢

*盡量拆分join關聯查詢：減少鎖競爭、高效可拓展、減少冗余

盡量用innerjoin避免LEFTJOIN：匹配查詢和主表全查

在使用Left(right)join的時候，一定要在先給出盡可能多的

匹配滿足條件，減少Where的執行：on的執行優先級高于where

盡量避免子查詢，而用join：select...where...(select...)查詢執行的基礎1.mysql客戶端/服務器通信協議

半雙工通信協議：服務器和客戶端發送數據的動作不能同時發生，客戶端發送單獨的請求數據包，查詢語句大小受max_allowed_packet影響。服務器獲取響應數據緩存到內存中分多個數據包多次發送，所有數據發送結束釋放查詢所占資源。當查詢結果過大時可以采用不緩存數據直接進行處理以便盡早釋放資源：mysql_query('select*from...',$con);tomysql_unbuffered_query('select*from...',$con);

2.查詢緩存

緩存完整的select查詢結果。表更新緩存失效。簡單代價小。需要配置。

解析查詢語句之前，如果查詢緩存是打開的，優先檢查這個查詢是否命中查詢緩存中的數據。通過哈希查找。如果命中則檢查用戶權限，跳過解析查詢步驟直接返回結果。3.查詢優化器Mysql查詢優化器的工作是為查詢語句選擇合適的執行路徑。mysql優化器優化類型：重新定義關聯表的順序將外連接轉化為內鏈接使用等價交換規則優化count、min、max（無查詢條件）、in(最慢的情況是where包含in的子查詢)預估并轉化為常數表達式覆蓋索引掃描提前終止查詢（limit）等值傳播4.優化器的局限性mysql搞不定的或者做的不好的。1.關聯子查詢select*fromfilmwherefilm_idin(selectfilm_idfromfile_actorwhereactor_id=1)->innerjoin2.union的限制(select...)unionall(select...)limit..->(select...limit..)unionall(select...limit..)limit..3.并行執行.單核多線程。4.哈希關聯。可以通過memory引擎的索引特性實現類哈希關聯。4.優化器的局限性（續）5.最大值最小值selectmin(actor_id)fromactorwherefirst_name='aa'->selectmin(actor_id)fromactorwherefirst_name='aa'limit16.同一表上的查詢和更新updateaasbsetcnt=(selectcount(*)fromaascwherec.type=b.type)(error)->updateainnerjoin(selecttype,count(*)ascntfromtblgroupbytype)asdUSING(type)sett=t;優化特定類型的查詢COUNT()查詢優化

myisam的count()函數非常快的前提是在沒有where條件的情況下count(*)會忽略所有的列而直接統計所有的行數，在任何情況下都大于等于count(col_name)

優化的幾個例子：selectcount(*)fromcitywherecity_id>5->select(selectcount(*)fromcity)-count(*)fromcitywherecity_id<=5selectsum(if(color='blue',1,0))...->selectcount(color='blue'ornull)...limit查詢優化

針對偏移量非常大的數據非常有效。

select*fromalimit100000,5;掃描10005條記錄丟掉10000條。->select*fromainnerjoin(selctfile_idfromalimit10000,5)asbusing(file_id)->select*fromawherepositionbetween10000and10004用戶自定義變量作為一些功能補充來完成特定的場景：排行榜:set@mingci:=0;selcet@mingci:=@mingci+1asmingcifroma;避免重復查詢:updateasetaa=NOW()whereid=33and@now:=NOW();select@now;(據說這次連數據庫都不需要訪問了，網卡慎用不適用)

ANDSOON...排行榜加強版：id(演員id)，film(電影)set@cur_cnt:=0,@pre_cnt:=0,@rank:=0;//當前數量，前一個數量,排名selectid,@cur_cnt:=cntascnt