使用mysql作為基礎數據庫的應用可能會遇到一些數據庫方面的性能問題我們可以通過一些方法進行問題定位以下介紹可以定位性能問題的四種方法歡迎拍磚
一開啟慢查詢日志
記錄執行查詢時間大於long_query_time的sqllong_query_time默認為s;
show variables like %slow%
得到圖中所示信息這裡可以查看到慢查詢日志是否開啟慢查詢日志文件的存放目錄
開啟慢查詢日志的方法
vi /etc/f(這個是mysql的默認讀取配置文件目錄一般會將f文件放在這下面)
[mysqld]下添加
slow_query_log=ON
long_query_time=(sql語句執行時間超過該參數值則會打印在慢查詢日志中)默認執行時間超過s的sql會打印在慢查詢日志中
修改了f中的配置項需要重啟數據庫
不重啟數據庫的情況下執行 set global slow_query_log=ON可以開啟慢查詢日志
開啟慢查詢日志後跟蹤慢查詢日志文件中的慢查詢sql再具體分析通過調整sql寫法或者添加正確的索引可以看到意想不到的性能效果
二分析慢查詢sql:
Explain 打印執行計劃
執行的selcet語句前面加上explain可以告訴你mysql如何執行該條語句
這裡需要額外注意typekeyrows extra列展示的內容
其中
Type=all表示使用的是全表掃描在數據量大的情況下全表掃描是非常耗性能的這個需要特別注意;
Type=index表示使用索引掃描只會遍歷索引樹;
Type=range表示使用索引范圍掃描常見於between ><等的查詢
Type=ref非唯一性索引掃返回匹配某個單獨值得所有行
Type=eq_ref 唯一性索引掃描對於每個索引鍵表中只有一條記錄與之匹配
Type=const/system 讀常量最多只會有一條記錄匹配由於是常量實際上只須要讀一次
Type=null 不需要掃描表
訪問類型從上到下由差變為最好
key表示select中使用到的索引如果為null表示沒有使用索引從查詢效率上講使用索引比不使用索引快但並不是所有的都要加索引索引也存在不足這裡就不詳解
rows表示執行該條sql所需要掃描的行數這個沒有絕對值可參考一般來說越小越好如果萬數據量的數據庫rows是萬通過這個可以判斷sql的查詢性能很差如果萬條數據量的數據庫rows是萬從我個人的角度還是能接受的
extra
一些十分重要的額外信息重點關注出現關鍵字
Using filesort當Query 中包含order by 操作而且無法利用索引完成排序操作的時候MySQL Query Optimizer 不得不選擇相應的排序算法來實現
Using temporary:在某些操作中必須使用臨時表時在 Extra 信息中就會出現Using temporary 主要常見於 GROUP BY 和 ORDER BY 等操作中
當執行計劃Extra 出現Using filesort Using temporary 時可以考慮是否需要進行sql優化和調整索引最後再調整f 中與排序或者臨時表相關的參數如sort_buffer_size或者tmp_table_size
show full processlist 查看哪條sql一直占用進程
Time表示執行當前操作所耗費的時間單位為秒(s);
State表面當前線程的狀態Info表示正在執行的操作;
這裡如果發現time值比較大state一直處於一個狀態那麼從Info中我們可以獲得耗時長的操作再具體分析;
注意觀察State中出現關於lock關鍵字的狀態
這個命令可以很直觀地看到正在執行的sql及其當前狀態操作比較方便
show profile 定位sql在數據庫中資源占用情況(注意一個是show profiles一個是show profile)
Show profiles主要展示在當前會話中profiling_history_size條sql執行的時間query_id默認為條最大為條不能設為
SHOW VARIABLES LIKE %profiling_history_size%
SHOW VARIABLES LIKE %profiling或者select @@profiling 查看profiling是否開啟
set profiling= 開啟profiling
執行一條sql
再執行show profiles會把最近執行的sql給展示出來
從圖中找到剛剛執行的sqlquery_id是執行時間是s
如果我們想看sql在各個階段所消耗時間則使用如下
SHOW PROFILE FOR QUERY
各個階段所消耗時間一目了然
show profile具體寫法為 Show profile TYPE for query nn為query_idTYPE可寫可不寫
TYPE的取值有為ALLBLOCK IOCONTEXT SWITCHESCPUIPCMEMORYPAGE FAULTSSOURCESWAPS
如上述例子中
SHOW PROFILE CPUMEMORY FOR QUERY
不加for query n這句則展示執行show profile之前執行過一條語句
Mysqladmin 查詢整個數據庫的狀態
在mysql的bin目錄下執行
/mysqladmin u用戶名 p密碼 proc stat
這裡添加proc就如同show full processlist功效
stat展示當前數據庫的狀態
threads 表示當前線程數Opens 當前打開的表數目Queries per second 每秒執行的查詢數數據庫性能越好這個值就越高
From:http://tw.wingwit.com/Article/program/MySQL/201311/29437.html
