mysql慢查詢開啟及分析方法

        最近服務維護的公司的DB伺服器，總是會出現問題，感覺需要最佳化一下了，登陸上去，發現慢查詢日誌都沒有開，真是慚愧， 故果斷加上慢查詢日誌，經過分析sql記錄，發現問題很多，開發人員很多沒有對sql最佳化，由於現在業務量較少，而且伺服器效能配置較高，所以看不出問題。

        加上慢查詢日誌好後，瞬間日誌暴增，講過分析，其中80%的sql沒有用到索引，執行較慢的sql及小於3秒的，沒有出現，瞬間對網上好多人說，mysql最佳化索引為王甚為感慨，可能你會感覺到，為什麼哪裡的面試都會牽涉到mysql索引最佳化的問題， sql最佳化從研發開始，如果研發團隊不重視代碼品質還沒有一個好的開發架構來約束開發人員隨意編寫未經認證的sql語句，是多麼的恐怖。

        不扯淡了，回到正題，普及一下mysql滿查詢日誌的操作及分析方法：

1. 參數的意義

   log-slow-queries <slow_query_log_file>
   存放slow query日誌的檔案。你必須保證mysql server進程mysqld_safe進程使用者對該檔案有w許可權。

   
   long_query_time
   如果query time超過了該值，則認為是較慢查詢，並被記錄下來。單位是秒，最小值是1,預設值是10秒。10秒對於大多數應用來講，太長了。我們推薦從3秒開始， 依次減少，每次都找出最”昂貴”的10條SQL語句並且最佳化他們。日複一日，一步一步最佳化。一次性找出很多條SQL語句，對於最佳化來講，意義並不大。

   
   log-queries-not-using-indexes
    MySQL會將沒有使用索引的查詢記錄到slow query日誌中。無論它執行有多快，查詢語句沒有使用索引，都會被記錄。有的時候，有些沒有使用引索的查詢非常快(例如掃描很小的表)，但也有可能導致伺服器變慢，甚至還會使用大量的磁碟空間。

    

   log-slow-admin-statements
   一些管理指令，也會被記錄。比如OPTIMEZE TABLE, ALTER TABLE等等。

    

2. 開啟慢查詢

   方法一：在伺服器上找到mysql的設定檔my.cnf , 然後再mysqld模組裡追加一下內容

   log_slow_queries = NOlog-slow-queries = /var/run/mysqld/slow_querys.loglong_query_time = 3log-queries-not-using-indexeslog-slow-admin-statements

   然後重啟mysql伺服器即可，這是通過一下命令看一下慢查詢日誌的情況：

   tail -f /var/run/mysqld/slow_querys.log

   方法二：通過修改myssql的全域變數來處理，這樣做的好處是，不用重啟mysql伺服器，登陸到mysql上執行一下sql指令碼即可

   set global slow_query_log=ON;set global long_query_time=3;

   然後通過一下命令查看是否成功

   mysql> show variables like 'long%';+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.00 sec)

   mysql> show variables like 'slow%';+---------------------+---------------+| Variable_name       | Value         |+---------------------+---------------+| slow_launch_time    | 2             || slow_query_log      | ON            || slow_query_log_file | /tmp/slow.log |+---------------------+---------------+3 rows in set (0.00 sec)

    

3. 分析慢查詢日誌

   方法一：通過查看mysql的慢查詢日誌分析，比如我們可以tail -f  slow_query.log查看裡面的內容，欄位意義

   # Time: 110107 16:22:11# User@Host: root[root] @ localhost []# Query_time: 9.869362 Lock_time: 0.000035 Rows_sent: 1 Rows_examined: 6261774SET timestamp=1294388531;select count(*) from ep_friends;第一行,SQL查詢執行的時間第二行,執行SQL查詢的串連資訊第三行記錄了一些我們比較有用的資訊Query_time SQL執行的時間,越長則越慢Lock_time 在MySQL伺服器階段(不是在儲存引擎階段)等待表鎖時間Rows_sent 查詢返回的行數Rows_examined 查詢檢查的行數

   方法二：使用mysqldumpslow命令分析，例如

   mysqldumpslow -s c -t 10 /tmp/slow-log

   這會輸出記錄次數最多的10條SQL語句，其中：

   -s, 是表示按照何種方式排序，c、t、l、r分別是按照記錄次數、時間、查詢時間、返回的記錄數來排序，ac、at、al、ar，表示相應的倒敘； -t, 是top n的意思，即為返回前面多少條的資料； -g, 後邊可以寫一個正則匹配模式，大小寫不敏感的；

    

   比如
   /path/mysqldumpslow -s r -t 10 /tmp/slow-log
   得到返回記錄集最多的10個查詢。
   /path/mysqldumpslow -s t -t 10 -g “left join” /tmp/slow-log
   得到按照時間排序的前10條裡面含有左串連的查詢語句。

4.  慢查詢日誌的不足

   雖然記錄了slow query能夠協助你最佳化產品。但是MySQL目前版本，還有幾大蹩足的地方。
   1.MySQL5.0版本, long_query_time時間粒紋不夠細,最小值為1秒。對於高並發效能的網頁指令碼而言，1秒出現的意義不大。即出現1秒的查詢比較少。直到mysql5.1.21才提供更細粒度的long_query_time設定.

   
   2.不能將伺服器執行的所有查詢記錄到慢速日誌中。雖然MySQL普通日誌記錄了所有查詢，但是它們是解析查詢之前就記錄下來了。這意味著普通日誌沒辦法包含諸如執行時間，鎖表時間，檢查行數等資訊。

   
   3.如果開啟了log_queries_not_using_indexes選項，slow query日誌會充滿過多的垃圾日誌記錄，這些快且高效的全表掃描查詢(表小)會衝掉真正有用的slow queries記錄。比如select * from category這樣的查詢也會被記錄下來。開啟了log_queries_not_using_indexes選項，slow query日誌會充滿過多的垃圾日誌記錄，這些快且高效的全表掃描查詢(表小)會衝掉真正有用的slow queries記錄。比如select * from category這樣的查詢也會被記錄下來。

 

 

 

 

本文出自 “架構技術文摘” 部落格，請務必保留此出處http://wufaliang.blog.51cto.com/3160882/1247073