通過MySQL慢查詢優化MySQL性能的方法講解

隨著訪問量的上升，MySQL數據庫的壓力就越大，幾乎大部分使用MySQL架構的web應用在數據庫上都會出現性能問題，通過mysql慢查詢日志跟蹤有問題的查詢非常有用,可以分析出當前程序里有很耗費資源的sql語句。

慢查詢日志我們可以通過my.cnf文件設置開啟，下面先來看一下相關參數的意義

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等等。

開啟慢查詢

方法一：在服務器上找到mysql的配置文件my.cnf , 然后再mysqld模塊里追加一下內容

log_slow_queries = NO
log-slow-queries = /var/run/mysqld/slow_querys.log 
long_query_time = 3 
log-queries-not-using-indexes 
log-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)

分析慢查詢日志

方法一：通過查看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: 6261774 
SET 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條里面含有左連接的查詢語句。

慢查詢日志的不足

雖然記錄了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這樣的查詢也會被記錄下來。

總結

以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，謝謝大家對億速云的支持。如果你想了解更多相關內容請查看下面相關鏈接