《Mysql實例MySQL在大數(shù)據(jù)、高并發(fā)場景下的SQL語句優(yōu)化和"最佳實踐"。》要點：
本文介紹了Mysql實例MySQL在大數(shù)據(jù)、高并發(fā)場景下的SQL語句優(yōu)化和"最佳實踐"。，希望對您有用。如果有疑問，可以聯(lián)系我們。
導讀：本文主要針對中小型應(yīng)用或網(wǎng)站,重點探討日常程序開發(fā)中SQL語句的優(yōu)化問題,所謂大數(shù)據(jù)、高并發(fā)僅針對中小型應(yīng)用而言,專業(yè)的數(shù)據(jù)庫運維大...
本文主要針對中小型應(yīng)用或網(wǎng)站,重點探討日常程序開發(fā)中SQL語句的優(yōu)化問題,所謂“大數(shù)據(jù)”、“高并發(fā)”僅針對中小型應(yīng)用而言,專業(yè)的數(shù)據(jù)庫運維大神請無視.以下實踐為個人在實際開發(fā)工作中,針對相對“大數(shù)據(jù)”和相對“高并發(fā)”場景的一些應(yīng)對策略,部分措施并沒有經(jīng)過嚴格的對比測試和原理分析,如有錯漏歡迎各種批評指教.
減少查詢的影響結(jié)果集,避免出現(xiàn)全表掃描.
影響結(jié)果集是SQL優(yōu)化的核心.影響結(jié)果集不是查詢返回的記錄數(shù),而是查詢所掃描的結(jié)果數(shù).通過Explain或Desc分析SQL,rows列的值即為影響結(jié)果集(還可以通過慢查詢?nèi)罩镜腞ows_examined后面的數(shù)字得到).
以下是我常用的一些SQL優(yōu)化策略：
- 去掉不必要的查詢和搜索.其實在項目的實際應(yīng)用中,很多查詢條件是可有可無的,能從源頭上避免的多余功能盡量砍掉,這是最簡單粗暴的解決方案.
- 合理使用索引和復合索引.建索引是SQL優(yōu)化中最有效的手段.查找、刪除、更新以及排序時常用的字段可以適當建立索引.不過要注意,單條查詢不能同時使用多個索引,只能使用一個索引.查詢條件較多時,可以使用多個字段合并的復合索引.切記,使用復合索引時,查詢條件的字段順序需要與復合索引的字段順序保持一致.
- 謹慎使用not in等可能無法使用索引的條件.索引也不是什么時候都可以發(fā)揮作用的,當出現(xiàn)"not in","!=","like '%xx%'","is null"等條件時,索引是無效的.使用這些條件的時候,請放到能有效使用索引的條件的右邊.設(shè)計表結(jié)構(gòu)時,個人建議盡可能用int類型代替varchar類型,int類型部分時候可以通過大于或小于代替"!="等條件,同時也方便滿足一些需要按類型排序的需求,至于可讀性的問題,完善好數(shù)據(jù)庫設(shè)計文檔才是明智的選擇.同時建議把所有可能的字段設(shè)置為"not null",并設(shè)置默認值,避免在where字句中出現(xiàn)"is null"的判斷.
- 不要在where子句中的“=”左邊進行函數(shù)、算術(shù)運算或其他表達式運算,否則系統(tǒng)將無法正確使用索引.盡可能少用MySQL的函數(shù),類似Now()完全可以通過程序?qū)崿F(xiàn)并賦值,部分函數(shù)也可以通過適當?shù)慕⑷哂嘧侄蝸黹g接替代.
- 在where條件中使用or,可能導致索引無效.可用 "union all" 或者 "union" (會過濾重復數(shù)據(jù),效率比前者低) 代替,或程序上直接分開兩次獲取數(shù)據(jù)再合并,確保索引的有效利用.
- 不使用select * ,倒不是能提高查詢效率,主要是減少輸出的數(shù)據(jù)量,提高傳輸速度.
- 避免類型轉(zhuǎn)換,這里所說的“類型轉(zhuǎn)換”是指where子句中出現(xiàn)字段的類型和傳入的參數(shù)類型不一致的時候發(fā)生的類型轉(zhuǎn)換.
- 分頁查詢的優(yōu)化.頁數(shù)比較多的情況下,如limit 10000,10 影響的結(jié)果集是10010行,查詢速度會比較慢.推薦的解決方案是：先只查詢主鍵select id from table where .. order by .. limit 10000,10(搜索條件和排序請建立索引),再通過主鍵去獲取數(shù)據(jù).
- 統(tǒng)計相關(guān)的查詢.影響結(jié)果集往往巨大,且部分SQL語句本身已經(jīng)難以優(yōu)化.因此,應(yīng)避免在業(yè)務(wù)高峰期執(zhí)行統(tǒng)計相關(guān)的查詢,或者僅在從庫中執(zhí)行統(tǒng)計查詢.部分統(tǒng)計數(shù)據(jù),可以通過冗余的數(shù)據(jù)結(jié)構(gòu)保存,同時建議把數(shù)據(jù)先保存在內(nèi)存、緩存中(如redis),再按一定策略寫入數(shù)據(jù)庫.
不使用任何連表查詢,通過分庫和分表實現(xiàn)負載均衡.
隨著數(shù)據(jù)量的增加,連表操作往往會導致影響結(jié)果集大增,從SQL優(yōu)化的層面已經(jīng)解決不了問題了.
此時,分庫和分表是解決數(shù)據(jù)庫性能壓力的最優(yōu)選擇(具體分庫和分表的方案通常結(jié)合實際業(yè)務(wù)的應(yīng)用場景來確定,此處略過).這里重點談,如何更好的實現(xiàn)或者過渡到分庫、分表的分布式數(shù)據(jù)庫架構(gòu).
核心點就是必須先去除數(shù)據(jù)表之間的關(guān)聯(lián),即不用外鍵,不使用任何連表查詢.為了確保不進行連表操作,在設(shè)計數(shù)據(jù)庫表結(jié)構(gòu)的時候,就需要設(shè)計適度冗余的字段來達到不連表的目的.
對于一些操作日志、支付記錄等,設(shè)計一些記錄用戶信息的字段,個人認為其實不能算冗余,畢竟用戶信息往往會更改,但是這種類似操作日志的表確實是需要記錄用戶操作時的信息,并且不需要在用戶更新信息時同步更新.
實際開發(fā)中,為了實現(xiàn)不進行連表而冗余的字段,往往是需要在原表更新數(shù)據(jù)的時候同步更新冗余字段的數(shù)據(jù)的,如果應(yīng)用層沒有對數(shù)據(jù)表操作做合理封裝,這往往是個棘手的問題,也不方便維護.
當然,現(xiàn)在主流的應(yīng)用框架,一般采用orm的方式處理數(shù)據(jù)表,所以問題不大.相反,不連表事實上還可以提高開發(fā)效率,比如通過用戶ID獲取用戶姓名操作,如果不連表就可以確保各個業(yè)務(wù)模塊都通過同樣的方式去獲取用戶姓名,調(diào)用同一個封裝好的方法,這樣,就能很方便的統(tǒng)一在應(yīng)用層加入緩存機制或添加統(tǒng)一的業(yè)務(wù)邏輯.
同時如果要對用戶表進行分庫分表,通過應(yīng)用層程序就可以簡單平滑的實現(xiàn).
使用Innodb.
關(guān)于Innodb和Myisam對比,我就不多說了.Myisam的表級鎖是致命問題,考慮到MySQL已經(jīng)默認使用Innodb作為數(shù)據(jù)庫引擎,個人建議大部分情況可以直接使用Innodb,其他引擎這里就不詳細討論了.
使用緩存.
1) 盡可能在程序上實現(xiàn)常用數(shù)據(jù)的緩存,目前主流的應(yīng)用框架應(yīng)該都能快速實現(xiàn)緩存的需求.如果在程序上沒有實現(xiàn)數(shù)據(jù)緩存,開啟數(shù)據(jù)庫的query cache也是緩解數(shù)據(jù)庫壓力的方式之一,如果確認使用,記得定時清理碎片flush query cache.
服務(wù)器相關(guān)優(yōu)化
MySQL服務(wù)配置以及分布式架構(gòu)的實現(xiàn),請根據(jù)實際應(yīng)用場景和業(yè)務(wù)需求定制,非本文重點,不做深入探討.轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/5798.html
