MySQL慢查詢及索引優化一、概述
平時在優化MySQL的時候，通常需要對數據庫進行分析，可以通過工具如使用索引、使用分析查詢以及調整MySQL的內部配置來優化數據庫的性能。常見的分析手段有慢查詢日志，分析查詢，分析以及show命令查詢系統狀態及系統變量等。通過定位分析性能的瓶頸，才能更好地優化數據庫系統的性能。了解和掌握MySQL慢查詢及索引相關的知識不僅可以幫助QA同學在做相關性能優化測試的時候，提供一些分析思路，還可以將分析手段應用在平常測試開發的工作中，分析系統數據庫的配置、審核開發同學寫的SQL腳本等等。在QA環節再把一道關，讓我們的系統更穩定也更健康。這也是整理這篇文章的一個初衷所在。
二、慢查詢—使用show命令查詢系統狀態及變量，找到系統瓶頸
下面以線上優品從庫配置為例，對線上MySQL數據庫相關的配置信息做一個簡單的分析：
1.查詢MySQL服務器全部配置信息：
SHOW
2.查詢MySQL慢查詢相關：
SHOW LIKE '%slow%'：
3.查詢慢查詢數量：
SHOW LIKE '%slow%'
配置中打開了記錄慢查詢，執行時間超過2秒的即為慢查詢，系統顯示有652個慢查詢。可以通過打開log文件分析慢查詢日志，找出哪些SQL語句執行效率低下。PS：慢查詢時間不宜設置過長，否則意義不大。
4.連接請求的變量：
1）最大連接數查詢：SHOW LIKE ''
可以看到這臺MySQL服務器設置的最大連接數是3000。
PS：增加該值增加 要求的文件描述符的數量。如果服務器的并發連接請求量比較大，建議調高此值以增加并行連接數量。當然這建立在機器能支撐的情況下，因為如果連接數越多，介于MySQL會為每個連接提供連接緩沖區，就會開銷越多的內存，所以要適當調整該值，不能盲目提高設值。
2）服務器響應連接數查詢：SHOW LIKE ''
可以看到，服務器響應的最大連接數為12， / * 100%=12/3000*100%=0.4%
一般情況下最大連接數理想的設置是： / * 100% ≈ 85%，即最大連接數占上限連接數的85%左右。目前系統配置的比例為0.4%mysql數據庫查詢慢，在10%以下，則就是服務器的連接數上限設置過高了。
5.
MySQL能暫存的連接數量。當主要MySQL線程在一個很短時間內得到非常多的連接請求時起作用。如果MySQL的連接數達到時，新來的請求將會被存在堆棧中，以等待某一連接釋放資源。該堆棧的數量即為。
可以看到，目前系統設置的的大小為256，相當于MySQL暫時停止回答新請求之前的短時間內，有256個請求可以被存在堆棧中。超過這個數量，將不被授予連接資源。如果期望在一個短時間內有很多連接，就需要增加它。一般默認值為50，可根據實際需要進行調整，對于Linux系統設置范圍為512的整數。
6.緩存區變量
1）全局緩沖：
指定索引緩沖區的大小，決定索引處理的速度，尤其為讀的速度。通過檢查狀態值和，可以知道設置得是否合理。一般情況下/應該盡可能的低，在0.1%左右最佳。
下面可以查詢咱們的系統配置情況：
SHOW LIKE ''
分配了32M的內存給，下面看的使用情況：
SHOW LIKE '%'
可以看到，一共有個索引讀取請求，有14個請求沒有在內存中找到直接從硬盤讀取索引，下面計算索引命中緩存的概率：
= / *100%=14/=0.%
相當于個索引讀取請求才有一個直接讀硬盤，一般控制在0.1%左右最佳，即平均1000次請求有一個直接讀取硬盤。如果在00.01%以下的話，則是分配的過多，可以適當減少。
三、常用的分析慢查詢日志的手段1.命令
如果慢查詢日志記錄的內容很多，可以使用MySQL自帶的慢查詢分析小工具來對慢查詢日志進行分類匯總。例如：
/path/ -s c -t 10 //mysql/slow-query.log 輸出記錄次數最多的10條SQL語句
-s 表示按照何種方式排序，c、t、l、r分別是按照記錄次數、時間、查詢時間、返回的記錄數來排序，ac、at、al、ar，表示相應的倒序；
-t top n的意思，即為返回前面多少條的數據；
-g 后邊可以寫一個正則匹配模式，大小寫不敏感；
再如：
/path/ -s r -t 10 //mysql/slow-log 得到返回記錄集最多的10個查詢
/path/ -s t -t 10 -g “left join”//mysql/slow-log 得到按照時間排序的前10條里面含有左連接的查詢
使用命令可以非常明確的得到各種我們需要的查詢語句，對MySQL查詢語句的監控、分析、優化是MySQL優化非常重要的一步。開啟慢查詢日志后，由于日志記錄操作，在一定程度上會占用CPU資源影響mysql的性能，但是可以階段性開啟來定位性能瓶頸。
2.分析查詢
使用關鍵字可以模擬優化器執行SQL查詢語句，從而知道MySQL是如何處理你的SQL語句的。這樣可以幫助我們分析查詢語句或是表結構的性能瓶頸。
通過命令可以得到：
·每張表有多少行被優化器查詢
例如：
使用分析該語句 * FROM WHERE =1 ='' LIMIT 0,100
根據結果可以得到如下重要參數信息：
（1）table：顯示此行數據關于哪一張表，
（2）type：顯示查詢使用了什么類型。此處使用的是ref：訪問索引，返回某單一行的數據
從最好到最差的連接類型依次為：->const->->ref->range->index->ALL
（3）：顯示可能應用在表中的索引
（4） key：實際使用的索引
（5）：使用的索引的長度
（6）ref：顯示索引被哪一列被使用
（7）rows：MySQL任務檢索的用來返回請求數據的行數，執行該語句返回的結果為3條。
（8）Extra：MySQL如何解析查詢的額外信息。 Using where：使用到where來過濾數據。
3.分析查詢
通過慢日志查詢可以知道哪些SQL語句執行效率低下，通過我們可以得知SQL語句的具體執行情況，索引使用情況等，還可以結合show命令查看執行狀態。
如果覺得的信息不夠詳細，可以通過命令得到更準確的SQL執行消耗系統資源的信息：
默認情況下是關閉的，可以通過 @@語句查看狀態：可以看到目前的狀態為1，已經打開
執行需要執行的SQL語句，然后show for query 1 可以得到對應SQL語句執行的詳細信息；
SHOW 命令格式：SHOW [type [, type] … ] [FOR QUERY n] [ [ ]]
四、索引及查詢優化1.索引的類型：
大多數MySQL索引( KEY、、INDEX和)使用B樹中存儲。空間列類型的索引使用R-樹，表支持hash索引。
在性能優化過程中，選擇在哪列上創建索引是最重要的步驟之一，可以考慮使用索引的主要有兩種類型的列：
（1）在WHERE子句中出現的列
（2）在join子句中出現的列
雖然索引是提高查詢速度的關鍵因素之一，但同時也存在一些缺點，如：
（1）索引需要占用磁盤空間，通常情況下問題不是很突出，但如果建立每一種可能組合的索引，索引文件體積增長的速度遠遠超過數據文件時，可能就會超過操作系統允許的最大文件限制。
（2）對于需要寫入數據的操作mysql數據庫查詢慢，如、以及操作，索引會降低它們的速度。因為MySQL不僅要把改動數據寫入數據文件，而且它還要把這些改動寫入索引文件。
索引是把雙刃劍，在建立和使用索引時要結合業務情況和實際需要去分析哪些可以使用索引以及考慮是否可以改寫和優化的空間，切實地提高系統性能。
2.合理建立索引的建議3.SQL設計的一些建議五、慢查詢優化的基本步驟
先運行看看是否真的很慢，注意設置
where條件單表查，鎖定最小返回記錄表。即把查詢語句的where都應用到表中返回的記錄數最小的表開始查起，單表每個字段分別查詢，看哪個字段的區分度最高
查看執行計劃，是否與2預期一致（從鎖定記錄較少的表開始查詢）
order by limit形式的sql語句讓排序的表優先查
了解業務方使用場景
加索引時參照建索引的幾個建議
觀察結果，不符合預期繼續從1分析
六、結合案例分析
下面，我們還是由3.2中的慢查詢語句為例來解釋如何進行分析和優化
（1）找出慢查詢語句，使用進行分析
從結果中我們可以得到，此行數據關于這張表，查詢使用的是ref類型（訪問索引，返回某行的數據），實際應用的索引列為,執行該語句返回的結果為3條，另外使用到where來過濾數據。
（2）隱式轉換導致索引失效
正常情況下執行上面的語句的耗時接近0.003s，在表字段中定義為(20)。但如果在查詢時把該字段作為類型以where條件傳給MySQL，這樣就會導致索引失效：
可以看到索引失效時的查詢效率，在結果數只有3條的情況下耗時將近1.5s，可以想象在查詢結果量大的情況下會有多慢。這一點需要引起重視，開發同學經常會犯這種錯誤導致查詢時索引失效。
（3）優化where條件
在查詢中，WHERE條件也是一個比較重要的因素。盡量少并且是合理的where條件非常重要，盡量在多個條件的時候，把索引列的查詢條件或區分度高的列放在前面，盡可能地精確縮小查詢記錄，可以減少后一個條件的查詢時間。
下面把索引列的條件調整到最前面，查看優化后的查詢效率。可以看到時間變成了0.001s，效率上提升了0.002s。在數據量大的情況下應該會更明顯。
七、QA審核sql的注意事項
最后，簡單羅列幾點QA在參與審核sql時需要注意的問題：
1、線上數據的修改，最好要明確修改原因，確認語法與業務邏輯
2、審核時注意避免*，審核不通過時可以告知開發進行修改
3、在執行sql前，注意對涉及的數據庫、表進行備份，&drop需謹慎并備份下來
4、&alter需要確認業務邏輯是否正確，對其他功能模塊是否有影響，并注意備份或有回滾語句
5、更改表結構的，可以與線上數據庫當前的表結構進行對比，在線下先執行一遍確認是否有問題
6、sql執行后，檢查相關系統功能是否正常，必要時找開發檢查線上數據庫確認
7、上線后，測試環境再執行一遍sql，主要目的是為了與線上保持一致，更方便地維護測試數據庫