MySQL 性能優化

信息安全公益宣傳，信息安全知識啟蒙。

由於配置是運行過那麼長時間，很穩定，基本上不考慮，所以本次主要是sql的優化，並且集中在開源的個人空間。下面是這次優化的資料庫版本：

案例一：冬粉查詢優化

SELECT user FROM osc_friends f INNER JOIN osc_users u

ON 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 msgCount

FROM osc_msgs WHERE user = 12 GROUP BY friend ORDER BY id DESC

osc_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

小結

把數據量從大化小的典型案例

案例三 評論優化

SELECT

l1.id

FROM

osc_opt_logs l1

osc_opt_logs l2

WHERE

l1.obj_type IN (101 111 113 116 119 121)

AND l2.obj_type IN (

100

110

112

114

118

120

123

124

122

125

126

127

99

)

AND l1.parent_id = l2.id

AND l2. USER = 12

ORDER BY

l1.id DESC

LIMIT 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出來的結果，可能會出於偏離設計的意料之外

案例五 關注資料庫狀態

▼ 閱讀原文，查看更多精彩文章。