本文主要針對中小型應用或網站，重點探討日常程式開發中SQL語句的優化問題，所謂“大數據”、“高併發”僅針對中小型應用而言，專業的資料庫運維大神請無視。以下實踐為個人在實際開發工作中，針對相對“大數據”和相對“高併發”場景的一些應對策略，部分措施並沒有經過嚴格的對比測試和原理分析，如有錯漏歡迎各種批評 ...
本文主要針對中小型應用或網站，重點探討日常程式開發中SQL語句的優化問題，所謂“大數據”、“高併發”僅針對中小型應用而言，專業的資料庫運維大神請無視。以下實踐為個人在實際開發工作中，針對相對“大數據”和相對“高併發”場景的一些應對策略，部分措施並沒有經過嚴格的對比測試和原理分析，如有錯漏歡迎各種批評指教。減少查詢的影響結果集，避免出現全表掃描。影響結果集是SQL優化的核心。影響結果集不是查詢返回的
本文主要針對中小型應用或網站，重點探討日常程式開發中SQL語句的優化問題，所謂“大數據”、“高併發”僅針對中小型應用而言，專業的資料庫運維大神請無視。以下實踐為個人在實際開發工作中，針對相對“大數據”和相對“高併發”場景的一些應對策略，部分措施並沒有經過嚴格的對比測試和原理分析，如有錯漏歡迎各種批評指教。
減少查詢的影響結果集，避免出現全表掃描。
影響結果集是SQL優化的核心。影響結果集不是查詢返回的記錄數，而是查詢所掃描的結果數。通過Explain或Desc分析SQL，rows列的值即為影響結果集(還可以通過慢查詢日誌的Rows_examined後面的數字得到)。
以下是我常用的一些SQL優化策略：
去掉不必要的查詢和搜索。其實在項目的實際應用中，很多查詢條件是可有可無的，能從源頭上避免的多餘功能儘量砍掉，這是最簡單粗暴的解決方案。
合理使用索引和複合索引。建索引是SQL優化中最有效的手段。查找、刪除、更新以及排序時常用的欄位可以適當建立索引。不過要註意，單條查詢不能同時使用多個索引，只能使用一個索引。查詢條件較多時，可以使用多個欄位合併的複合索引。切記，使用複合索引時，查詢條件的欄位順序需要與複合索引的欄位順序保持一致。
謹慎使用not in等可能無法使用索引的條件。索引也不是什麼時候都可以發揮作用的，當出現"not in"，"!="，"like '%xx%'"，"is null"等條件時，索引是無效的。使用這些條件的時候，請放到能有效使用索引的條件的右邊。設計表結構時，個人建議儘可能用int類型代替varchar類型，int類型部分時候可以通過大於或小於代替"!="等條件，同時也方便滿足一些需要按類型排序的需求，至於可讀性的問題，完善好資料庫設計文檔才是明智的選擇。同時建議把所有可能的欄位設置為"not null"，並設置預設值，避免在where字句中出現"is null"的判斷。
不要在where子句中的“=”左邊進行函數、算術運算或其他表達式運算，否則系統將無法正確使用索引。儘可能少用MySQL的函數，類似Now()完全可以通過程式實現並賦值，部分函數也可以通過適當的建立冗餘欄位來間接替代。
在where條件中使用or，可能導致索引無效。可用 "union all" 或者 "union" (會過濾重覆數據，效率比前者低) 代替，或程式上直接分開兩次獲取數據再合併，確保索引的有效利用。
不使用select * ，倒不是能提高查詢效率，主要是減少輸出的數據量，提高傳輸速度。
避免類型轉換，這裡所說的“類型轉換”是指where子句中出現欄位的類型和傳入的參數類型不一致的時候發生的類型轉換。
分頁查詢的優化。頁數比較多的情況下，如limit 10000,10 影響的結果集是10010行，查詢速度會比較慢。推薦的解決方案是：先只查詢主鍵select id from table where .. order by .. limit 10000,10(搜索條件和排序請建立索引)，再通過主鍵去獲取數據。
統計相關的查詢。影響結果集往往巨大，且部分SQL語句本身已經難以優化。因此，應避免在業務高峰期執行統計相關的查詢，或者僅在從庫中執行統計查詢。部分統計數據，可以通過冗餘的數據結構保存，同時建議把數據先保存在記憶體、緩存中(如redis)，再按一定策略寫入資料庫。
不使用任何連表查詢，通過分庫和分表實現負載均衡。
隨著數據量的增加，連表操作往往會導致影響結果集大增，從SQL優化的層面已經解決不了問題了。
此時，分庫和分表是解決資料庫性能壓力的最優選擇(具體分庫和分表的方案通常結合實際業務的應用場景來確定，此處略過)。這裡重點談，如何更好的實現或者過渡到分庫、分表的分散式資料庫架構。
核心點就是必須先去除數據表之間的關聯，即不用外鍵，不使用任何連表查詢。為了確保不進行連表操作，在設計資料庫表結構的時候，就需要設計適度冗餘的欄位來達到不連表的目的。
對於一些操作日誌、支付記錄等，設計一些記錄用戶信息的欄位，個人認為其實不能算冗餘，畢竟用戶信息往往會更改，但是這種類似操作日誌的表確實是需要記錄用戶操作時的信息，並且不需要在用戶更新信息時同步更新。
實際開發中，為了實現不進行連表而冗餘的欄位，往往是需要在原表更新數據的時候同步更新冗餘欄位的數據的，如果應用層沒有對數據表操作做合理封裝，這往往是個棘手的問題，也不方便維護。
當然，現在主流的應用框架，一般採用orm的方式處理數據表，所以問題不大。相反，不連表事實上還可以提高開發效率，比如通過用戶ID獲取用戶姓名操作，如果不連表就可以確保各個業務模塊都通過同樣的方式去獲取用戶姓名，調用同一個封裝好的方法，這樣，就能很方便的統一在應用層加入緩存機制或添加統一的業務邏輯。
同時如果要對用戶表進行分庫分表，通過應用層程式就可以簡單平滑的實現。
使用Innodb。
關於Innodb和Myisam對比，我就不多說了。Myisam的表級鎖是致命問題，考慮到MySQL已經預設使用Innodb作為資料庫引擎，個人建議大部分情況可以直接使用Innodb，其他引擎這裡就不詳細討論了。
使用緩存。
1) 儘可能在程式上實現常用數據的緩存，目前主流的應用框架應該都能快速實現緩存的需求。如果在程式上沒有實現數據緩存，開啟資料庫的query cache也是緩解資料庫壓力的方式之一，如果確認使用，記得定時清理碎片flush query cache。
伺服器相關優化
MySQL服務配置以及分散式架構的實現，請根據實際應用場景和業務需求定製，非本文重點，不做深入探討。
以上內容希望幫助到大家，更多相關知識請關註我的專欄PHP
