文末領取【MySQL測試題+答案】
性能調優前提：無監控不調優
01
Mysql性能指標及問題分析和定位
1.我們在監控圖表中關注的性能指標
CPU、內存、連接數、io讀寫時間、io操作時間、慢查詢、系統平均負載以及memoryOver。
2.Grafana模板中各性能指標的意思
Mysql數據庫的連接數
圖標表示了慢查詢
Mysql數據庫的緩存區，展示了最大緩存以及已使用緩存等數據
3.性能分析
一般在產生Mysql瓶頸的時候往往伴隨著的是CPU使用率急速上升，需要top看一下是哪個線程佔據了大量的CPU資源，如果發現Mysql進程佔用較高，那麼基本可以判斷是Mysql數據庫出現了問題。
接下來就是對問題具體的分析和定位。
對於數據庫的操作基本上就是大量的查詢，會導致數據庫出現性能問題。對有問題的場景使用Jmeter模擬場景進行併發，並觀察Grafana的圖表。
Mysql的幾個問題基本上就是：
緩存區較小，大量查詢導致了緩存區溢出，使用io進行讀寫，眾所周知，io的讀寫速度遠遠比內存讀寫速度要慢得多。
sql語句問題，導致mysql數據庫出現瓶頸的查詢語句類型很多，最後會給大家列舉一些。
那麼怎麼定位到這些問題呢？
（1）在負載測試中，通過Grafana圖表觀察Memory Over這個圖表，如果發現佔用基本佔滿所分配給Mysql數據庫緩存區的內存，然後IO讀寫時間非常長，讀寫頻率非常高，那基本上是可以判斷是緩存區較小導致的問題。（這個問題已經很少出現了）
（2）判斷慢查詢：在mysql數據庫的配置文件中找到
<code>log_output
=file,table /<code><code>/<code><code>/<code><code>/<code>
重啟Mysql數據庫：在Grafana圖表中如果看到慢查詢的時間超過1s時，基本判斷為存在慢查詢。
登入數據庫運行命令
<code>select
*from
mysql.slow_log;/<code>
運行完這條命令後，可以查看到所有超過1s的查詢語句，這個時候複製這條語句到查詢輸入框中，選中右鍵點擊解釋。
type列，連接類型。一個好的sql語句至少要達到range級別。杜絕出現all級別
key列，使用到的索引名。如果沒有選擇索引，值是。可以採取強制索引方式
key_len列，索引長度
rows列，掃描行數。該值是個預估值
extra列，詳細說明。注意常見的不太友好的值有：Using filesort, Using temporary
02 sql語句調優
1.SQL語句中IN包含的值不應過多
MySQL對於IN做了相應的優化，即將IN中的常量全部存儲在一個數組裡面，而且這個數組是排好序的。但是如果數值較多，產生的消耗也是比較大的。再例如：select id from t where num in(1,2,3) 對於連續的數值，能用between就不要用in了；再或者使用連接來替換。
2.SELECT語句務必指明字段名稱
SELECT *增加很多不必要的消耗（cpu、io、內存、網絡帶寬）；增加了使用覆蓋索引的可能性；當表結構發生改變時，前斷也需要更新。所以要求直接在select後面接上字段名。
3.當只需要一條數據的時候，使用limit 1
這是為了使EXPLAIN中type列達到const類型。
4.如果排序字段沒有用到索引，就儘量少排序
5.如果限制條件中其他字段沒有索引，儘量少用or
or兩邊的字段中，如果有一個不是索引字段，而其他條件也不是索引字段，會造成該查詢不走索引的情況。很多時候使用union all 或者是union(必要的時候)的方式來代替“or”會得到更好的效果。
6.區分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語句
<code>select
colname …from
A表where
a.id not
in
(select
b.idfrom
B表)/<code>
高效的sql語句
<code>select
colname …from
A表Left
join
B表on
where
a.id = b.idwhere
b.idis
/<code>
7.分段查詢
在一些用戶選擇頁面中，可能一些用戶選擇的時間範圍過大，造成查詢緩慢。主要的原因是掃描行數過多。這個時候可以通過程序，分段進行查詢，循環遍歷，將結果合併處理進行展示。
8.避免在 where 子句中對字段進行 值判斷
對於的判斷會導致引擎放棄使用索引而進行全表掃描。
9.不建議使用%前綴模糊查詢
例如LIKE“%name”或者LIKE“%name%”，這種查詢會導致索引失效而進行全表掃描。但是可以使用LIKE“name%”。
10.注意範圍查詢語句
對於聯合索引來說，如果存在範圍查詢，比如between,>,
type 訪問類型
ALL 掃描全表數據
index 遍歷索引
range 索引範圍查找
index_subquery 在子查詢中使用 ref
unique_subquery 在子查詢中使用 eq_ref
ref_or_ 對進行索引的優化的 ref
fulltext 使用全文索引
ref 使用非唯一索引查找數據
eq_ref 在join查詢中使用PRIMARY KEYorUNIQUE NOT 索引關聯。
const 使用主鍵或者唯一索引，且匹配的結果只有一條記錄。
system const 連接類型的特例，查詢的表為系統表。
性能從好到差依次為：
system，const，eq_ref，ref，fulltext，ref_or_，unique_subquery，index_subquery，range，index_merge，index，ALL。（除了ALL之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一個索引。）
所以，如果通過執行計劃發現某張表的查詢語句的type顯示為ALL，那就要考慮添加索引，或者更換查詢方式，使用索引進行查詢。
possible_keys
可能使用的索引，注意不一定會使用。查詢涉及到的字段上若存在索引，則該索引將被列出來。當該列為 時就要考慮當前的SQL是否需要優化了。
key
顯示MySQL在查詢中實際使用的索引，若沒有使用索引，顯示為。TIPS:查詢中若使用了覆蓋索引(覆蓋索引：索引的數據覆蓋了需要查詢的所有數據)，則該索引僅出現在key列表中。
03
SQL優化建議
1.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查詢可能不會去利用索引。
End.
作者：白加黑呀
來源：博客園
本文為轉載分享，如有侵權請聯繫後臺刪除。
長按下方海報領取 【MySQL測試題+答案】
8個SQL常被忽視的錯誤用法，你踩過坑嗎？
疫情時期，互聯網人如何選擇行業？獵聘給出了這些方向
10個常用數據分析思路，看看是否有你沒用過的？
Python實戰|5步實現客戶細分
