Zi 字媒體

信息安全公益宣傳，信息安全知識啟蒙。由於配置是運行過那麼長時間，很穩定，基本上不考慮，所以本次主要是sql的優化，並且集中在開源的個人空間。下面是這次優化的資料庫版本：案例一：冬粉查詢優化SELECT user FROM osc_friends f INNER JOIN osc_users uON u.id=f.user AND f.friend=? AND f.user<>? ORDER BY create_time DESC--查詢冬粉數量ON u.id=f.user AND f.friend = ? AND f.user <> ?這兩個查詢在業務可以優化，inner join一個osc_users表目的是去掉osc_friends裡面自帶了自己的userid，偏偏osc_users表是比較大的表，為啥這樣設計，可以看看早年紅薯分享的OSChina 用戶動態設計說明優化思路簡化sql，自帶的userid的邏輯放到代碼層去處理優化后SELECT user FROM osc_friends f WHERE f.friend=? ORDER BY create_time DESC SELECT COUNT(*) FROM osc_friends f WHERE f.friend = ?sql簡化了很多，大大提升了查詢速度小結有時候業務處理放到代碼層，能達到意想不到的效果SELECT MAX(id) AS id COUNT(id) AS msgCountFROM osc_msgs WHERE user = 12 GROUP BY friend ORDER BY id DESCosc_msgs表存儲著所有的私信紀錄，隨著時間推移，該錶慢慢變大，一次查詢成本變高，基本都要1秒多優化思路取私信表的最新的兩個人的對話放入一個新建的osc_last_msgs表，每次發私信更新osc_last_msgs表，這個表只記錄最新的私信，這樣優化后的私信列表sql就不需要在msg表裡面找數據，只需要去osc_last_msgs表尋找.優化后SELECT * FROM osc_last_msgs WHERE user=? ORDER BY msg_id DESC小結把數據量從大化小的典型案例案例三 評論優化SELECTl1.idFROMosc_opt_logs l1osc_opt_logs l2WHEREl1.obj_type IN (101 111 113 116 119 121)AND l2.obj_type IN (10011011211411812012312412212512612799)AND l1.parent_id = l2.idAND l2. USER = 12ORDER BYl1.id DESCLIMIT 20;嘗試建立聯合索引進行優化，不過效果不佳，因為optlog表特別的大，因此聯表查詢效率極低，佔用查詢緩存空間極大。優化思路添加一個reply_user欄位，將回復的動彈進行標記，這樣子就可以簡化整個聯表查詢操作優化后SELECT id FROM osc_opt_logs where reply_user = 12 ORDER BY id DESC limit 20;小結適當的冗餘欄位可以降低sql的複雜度案例四 索引優化索引優化主要還是依賴explain命令，關於explain命令相信大家並不陌生，具體用法和欄位含義可以參考官網explain-output，這裡需要強調rows是核心指標，絕大部分rows小的語句執行一般很快。所以優化語句基本上都是在優化rows。一般來說.rows<1000，是在可接受的範圍內的。rows在1000~1w之間，在密集訪問時可能導致性能問題，但如果不是太頻繁的訪問(頻率低於1分鐘一次)，又難再優化的話，可以接受，但需要注意觀察rows大於1萬時，應慎重考慮SQL的設計，優化SQL這個沒有絕對值可參考，一般來說越小越好，，如果100萬數據量的資料庫，rows是70萬，通過這個可以判斷sql的查詢性能很差，如果100萬條數據量的資料庫，rows是1萬，從我個人的角度，還是能接受的。另外就是extra的信息，該列包含MySQL解決查詢的詳細信息 ，重點關注出現關鍵字：Using filesort：當Query 中包含order by 操作，而且無法利用索引完成排序操作的時候，MySQL Query Optimizer 不得不選擇相應的排序演算法來實現。Using temporary：在某些操作中必須使用臨時表時，在 Extra 信息中就會出現Using temporary ,主要常見於 GROUP BY 和 ORDER BY 等操作中當執行計劃Extra 出現Using filesort 、Using temporary 時，可以考慮是否需要進行sql優化和調整索引，最後再調整my.cnf 中與排序或者臨時表相關的參數，如sort_buffer_size或者tmp_table_size.比如下面這個：原因是mysql查詢只使用一個索引，如果where字句用了索引的話，那麼order by 中的列是不會使用索引的。所以order by的條件也需要添加到索引裡面組成聯合索引，優化后還有一個需要注意的點是，索引有個最左前綴的原則：聯合索引（a,b,c）可以匹配(a)、（a,b）、（a,b,c）但不可以匹配(b,c)小結explain SQL語句應該是日常開發中的習慣動作，有時explain出來的結果，可能會出於偏離設計的意料之外案例五 關注資料庫狀態▼ 閱讀原文，查看更多精彩文章。