mysql查詢性能優(yōu)化

MYSQL查詢性能優(yōu)化為什么查詢速度會慢1優(yōu)化數(shù)據(jù)訪問基礎2重構查詢的方式3查詢執(zhí)行的基礎4目錄mysql查詢優(yōu)化器的局限性5優(yōu)化特定類型的查詢6andsoon...7結束8目錄為什么查詢速度會慢？1.查詢速度最重要的參數(shù)是響應時間。剖析mysql查詢詳見以后課時。2.是否在檢索大量超過需要的數(shù)據(jù)。太多的行和太多的列。3.mysql服務器是否在分析大量超過需要的數(shù)據(jù)行。優(yōu)化數(shù)據(jù)訪問基礎設計合理的數(shù)據(jù)表結構：適當?shù)臄?shù)據(jù)冗余對數(shù)據(jù)表建立合適有效的數(shù)據(jù)庫索引數(shù)據(jù)查詢：編寫簡潔高效的SQL語句mysql執(zhí)行順序：(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參數(shù)wget/major/MySQLTuner-perl/master/mysqltuner.plchmod+xmysqltuner.pl./mysqltuner.pl//Recommendations2.使用innoDB引擎代替MyISAM

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

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

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

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

匹配滿足條件，減少Where的執(zhí)行：on的執(zhí)行優(yōu)先級高于where

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

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

2.查詢緩存

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

解析查詢語句之前，如果查詢緩存是打開的，優(yōu)先檢查這個查詢是否命中查詢緩存中的數(shù)據(jù)。通過哈希查找。如果命中則檢查用戶權限，跳過解析查詢步驟直接返回結果。3.查詢優(yōu)化器Mysql查詢優(yōu)化器的工作是為查詢語句選擇合適的執(zhí)行路徑。mysql優(yōu)化器優(yōu)化類型：重新定義關聯(lián)表的順序將外連接轉化為內鏈接使用等價交換規(guī)則優(yōu)化count、min、max（無查詢條件）、in(最慢的情況是where包含in的子查詢)預估并轉化為常數(shù)表達式覆蓋索引掃描提前終止查詢（limit）等值傳播4.優(yōu)化器的局限性mysql搞不定的或者做的不好的。1.關聯(lián)子查詢select*fromfilmwherefilm_idin(selectfilm_idfromfile_actorwhereactor_id=1)->innerjoin2.union的限制(select...)unionall(select...)limit..->(select...limit..)unionall(select...limit..)limit..3.并行執(zhí)行.單核多線程。4.哈希關聯(lián)。可以通過memory引擎的索引特性實現(xiàn)類哈希關聯(lián)。4.優(yōu)化器的局限性（續(xù)）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;優(yōu)化特定類型的查詢COUNT()查詢優(yōu)化

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

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

針對偏移量非常大的數(shù)據(jù)非常有效。

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;(據(jù)說這次連數(shù)據(jù)庫都不需要訪問了，網(wǎng)卡慎用不適用)

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