WEB后端開發(fā)人員對MySQL優(yōu)化步驟
庫表結(jié)構(gòu)優(yōu)化
- 選擇合適的更小的數(shù)據(jù)類型，因為他們占用更少的磁盤、內(nèi)存
- 盡量避免NULL，當(dāng)檢索該行時有可能不走索引
- 單表不要使用過多的字段，避免大量產(chǎn)生大量死字段
- 盡量使用相同數(shù)據(jù)類型存儲相似或相關(guān)的值，尤其是要在關(guān)聯(lián)條件中使用的列
- 范式和反范式并存
索引優(yōu)化
沒有用到索引我們需要遍歷雙向鏈表來定位對應(yīng)的頁，有了索引之后，我們通過底層數(shù)據(jù)結(jié)構(gòu)為B+樹的目錄進行二分查找，時間復(fù)雜度有O(n)變?yōu)榱薕(logn)，索引雖然能提高查詢速度，同時卻會降低更新表的速度，并且也要占用空間。
創(chuàng)建索引
- 頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引
- 查詢中與其他表關(guān)聯(lián)的字段
- 單鍵、組合索引的選擇問題，組合索引性價比更高
- 查詢中那個排序的字段，排序字段若通過索引去訪問將大大提高排序速度
- 查詢中統(tǒng)計或分組的字段
索引失效情況
- like以%開頭，索引無效；當(dāng)like前綴沒有%，后綴有%時，索引有效
- or使索引失效，數(shù)據(jù)庫引擎會根據(jù)數(shù)據(jù)規(guī)模，左右是否使用索引，是否有主鍵產(chǎn)生不同的現(xiàn)象，考慮使用union優(yōu)化
- 組合索引時，最左前綴規(guī)則
- 數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)換，如varchar不加單引號的話可能會自動轉(zhuǎn)換為int型，使索引無效，產(chǎn)生全表掃描。
- IS NULL和IS NOT NULL根據(jù)命中率不同，走不走索引的情況也不同
- 在索引字段上使用not，<>，!=不會走索引
- 對索引字段進行計算操作，字段上使用函數(shù)不會走索引
- 當(dāng)全表掃描速度大于索引速度時，會走全表
查詢優(yōu)化
查詢性能低下最基本的原因就是數(shù)據(jù)訪問太多，從兩個方面著手。
- 確認應(yīng)用程序是否檢索大量超過需要的數(shù)據(jù)，太多的行或列
- 確認服務(wù)器層是否在分析大量超過需要的數(shù)據(jù)行。
優(yōu)化手段
- SELECT后面緊跟需要的字段，避免使用*
- 使用LIMIT限制返回條數(shù)
- 將大查詢分解多個小查詢，緩存效率更高，減少鎖的競爭，更高的擴展
- 使用IN取代OR
- 優(yōu)化關(guān)聯(lián)查詢，多表篩選時，可以先將數(shù)據(jù)篩選出來再來關(guān)聯(lián)其他表，為關(guān)聯(lián)字段創(chuàng)建索引
- 排序優(yōu)化，盡量使用索引排序，如果不能走索引，mysql自己會排序，當(dāng)數(shù)據(jù)量小于排序緩沖區(qū)的大小時，mysql使用內(nèi)存進行排序，速度極快；但當(dāng)數(shù)據(jù)量很大時，mysql走文件排序，就會消耗很多資源，并且耗時也長。
- 盡量使用關(guān)聯(lián)查詢替代子查詢
- OFFSET優(yōu)化
橫向擴展
集群、負載均衡、主從、讀寫分離
