性能測試之Mysql數據庫調優
- 2020 年 3 月 3 日
- 筆記
一、前言
性能調優前提：無監控不調優，對於mysql性能的監控前幾天有文章提到過，有興趣的朋友可以去看一下
二、Mysql性能指標及問題分析和定位
1、我們在監控圖表中關注的性能指標大概有這麼幾個：CPU、內存、連接數、io讀寫時間、io操作時間、慢查詢、系統平均負載以及memoryOver
2、介紹下Grafana模板中各性能指標的意思
這個是Mysql數據庫的連接數
這個圖標表示了慢查詢
上圖就是Mysql數據庫的緩存區，展示了最大緩存以及已使用緩存等數據
3、性能分析
一般在產生Mysql瓶頸的時候往往伴隨着的是CPU使用率急速上升，需要top看一下是哪個線程佔據了大量的CPU資源，如果發現Mysql進程佔用較高，那麼基本可以判斷是Mysql數據庫出現了問題。
接下來就是對問題具體的分析和定位。
對於數據庫的操作基本上就是大量的查詢，會導致數據庫出現性能問題。對有問題的場景使用Jmeter模擬場景進行並發，並觀察Grafana的圖表。
Mysql的幾個問題基本上就是：1、緩存區較小，大量查詢導致了緩存區溢出，使用io進行讀寫，眾所周知，io的讀寫速度遠遠比內存讀寫速度要慢得多。
2、sql語句問題，導致mysql數據庫出現瓶頸的查詢語句類型很多，最後會給大家列舉一些。
那麼怎麼定位到這些問題呢？
（1）在負載測試中，通過Grafana圖表觀察Memory Over這個圖表，如果發現佔用基本佔滿所分配給Mysql數據庫緩存區的內存，然後IO讀寫時間非常長，讀寫頻率非常高，那基本上是可以判斷是緩存區較小導致的問題。（這個問題已經很少出現了）
（2）判斷慢查詢：在mysql數據庫的配置文件中找到
log_output=file,table #二選 1 或者 2 個都選 slow_query_log=on slow_query_log_file = /tmp/mysql-slow.log long_query_time = 1 #設置如何判斷慢查詢，這邊設置超過1s就算慢查詢
#使用完記得關閉
重啟Mysql數據庫
在Grafana圖表中如果看到慢查詢的時間超過1s時，基本判斷為存在慢查詢。
登入數據庫運行命令
select * from mysql.slow_log;#查看慢查詢表數據
運行完這條命令後，可以查看到所有超過1s的查詢語句，這個時候複製這條語句到查詢輸入框中，選中右鍵點擊解釋。
type列，連接類型。一個好的sql語句至少要達到range級別。杜絕出現all級別
key列，使用到的索引名。如果沒有選擇索引，值是NULL。可以採取強制索引方式
key_len列，索引長度
rows列，掃描行數。該值是個預估值
extra列，詳細說明。注意常見的不太友好的值有：Using filesort, Using temporary
三、sql語句調優
一、SQL語句中IN包含的值不應過多
MySQL對於IN做了相應的優化，即將IN中的常量全部存儲在一個數組裏面，而且這個數組是排好序的。但是如果數值較多，產生的消耗也是比較大的。再例如：select id from t where num in(1,2,3) 對於連續的數值，能用 between 就不要用 in 了；再或者使用連接來替換。
三、SELECT語句務必指明字段名稱
SELECT *增加很多不必要的消耗（cpu、io、內存、網絡帶寬）；增加了使用覆蓋索引的可能性；當表結構發生改變時，前斷也需要更新。所以要求直接在select後面接上字段名。
四、當只需要一條數據的時候，使用limit 1
這是為了使EXPLAIN中type列達到const類型
五、如果排序字段沒有用到索引，就盡量少排序
六、如果限制條件中其他字段沒有索引，盡量少用or
or兩邊的字段中，如果有一個不是索引字段，而其他條件也不是索引字段，會造成該查詢不走索引的情況。很多時候使用 union all 或者是union(必要的時候)的方式來代替“or”會得到更好的效果
七、區分in和exists， not in和not exists
select * from 表A where id in (select id from 表B)
上面sql語句相當於
select * from 表A where exists(select * from 表B where 表B.id=表A.id)
區分in和exists主要是造成了驅動順序的改變（這是性能變化的關鍵），如果是exists，那麼以外層表為驅動表，先被訪問，如果是IN，那麼先執行子查詢。所以IN適合於外表大而內表小的情況；EXISTS適合於外表小而內表大的情況。
關於not in和not exists，推薦使用not exists，不僅僅是效率問題，not in可能存在邏輯問題。如何高效的寫出一個替代not exists的sql語句？
原sql語句
select colname … from A表 where a.id not in (select b.id from B表)
高效的sql語句
select colname … from A表 Left join B表 on where a.id = b.id where b.id is null
八、分段查詢
在一些用戶選擇頁面中，可能一些用戶選擇的時間範圍過大，造成查詢緩慢。主要的原因是掃描行數過多。這個時候可以通過程序，分段進行查詢，循環遍歷，將結果合併處理進行展示
九、避免在 where 子句中對字段進行 null 值判斷
對於null的判斷會導致引擎放棄使用索引而進行全表掃描。
十、不建議使用%前綴模糊查詢
例如LIKE “%name”或者LIKE “%name%”，這種查詢會導致索引失效而進行全表掃描。但是可以使用LIKE “name%”。
十一、注意範圍查詢語句
對於聯合索引來說，如果存在範圍查詢，比如between,>,<等條件時，會造成後面的索引字段失效。
type
訪問類型
ALL 掃描全表數據
index 遍歷索引
range 索引範圍查找
index_subquery 在子查詢中使用 ref
unique_subquery 在子查詢中使用 eq_ref
ref_or_null 對Null進行索引的優化的 ref
fulltext 使用全文索引
ref 使用非唯一索引查找數據
eq_ref 在join查詢中使用PRIMARY KEYorUNIQUE NOT NULL索引關聯。
const 使用主鍵或者唯一索引，且匹配的結果只有一條記錄。
system const 連接類型的特例，查詢的表為系統表。
性能從好到差依次為：
system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了ALL之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一個索引。
所以，如果通過執行計劃發現某張表的查詢語句的type顯示為ALL，那就要考慮添加索引，或者更換查詢方式，使用索引進行查詢。
possible_keys
可能使用的索引，注意不一定會使用。查詢涉及到的字段上若存在索引，則該索引將被列出來。當該列為 NULL時就要考慮當前的SQL是否需要優化了。
key
顯示MySQL在查詢中實際使用的索引，若沒有使用索引，顯示為NULL。
TIPS:查詢中若使用了覆蓋索引(覆蓋索引：索引的數據覆蓋了需要查詢的所有數據)，則該索引僅出現在key列表中
一些SQL優化建議
1、SQL語句不要寫的太複雜。
一個SQL語句要盡量簡單，不要嵌套太多層。
2、使用like的時候要注意是否會導致全表掃
3、盡量避免使用!=或<>操作符
在where語句中使用!=或<>，引擎將放棄使用索引而進行全表掃描。
4、盡量避免使用 or 來連接條件
在 where 子句中使用 or 來連接條件，引擎將放棄使用索引而進行全表掃描。
5、盡量避免使用in和not in
在 where 子句中使用 in和not in，引擎將放棄使用索引而進行全表掃描。
6、盡量避免使用表達式、函數等操作作為查詢條件
7、盡量避免大事務操作，提高系統並發能力。
8、任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。
9、盡量使用數字型字段，若只含數值信息的字段盡量不要設計為字符型，這會降低查詢和連接的性能，並會增加存儲開銷。
10、索引並不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率
11、並不是所有索引對查詢都有效，SQL是根據表中數據來進行查詢優化的，當索引列有大量數據重複時，SQL查詢可能不會去利用索引
