MySQL慢查詢優(yōu)化之慢查詢?nèi)罩痉治龅膶?shí)例教程_MySQL

先看看MySQL慢查詢?nèi)罩纠锩娴挠涗涢L(zhǎng)什么樣的：

Time Id Command Argument
# Time: 141010 9:33:57
# User@Host: root[root] @ localhost [] Id: 1
# Query_time: 0.000342 Lock_time: 0.000142 Rows_sent: 3 Rows_examined: 3
use test;
SET timestamp=1412904837;
select * from t;

這個(gè)日志應(yīng)該很好理解了，第一個(gè)#記錄時(shí)間戳，第二個(gè)#記錄執(zhí)行命令的用戶和地址信息，第三個(gè)#記錄執(zhí)行查詢的時(shí)間、鎖的時(shí)間、返回行數(shù)、被掃描的行數(shù)。接著后面記錄真正執(zhí)行的SQL語(yǔ)句。還可以通過(guò)以下命令看看cvs存儲(chǔ)格式每個(gè)字段意義。

SHOW CREATE TABLE mysql.slow_log;

接下來(lái)說(shuō)說(shuō)如何獲取和分析慢日志吧。

查看MySQL慢日志參數(shù)

進(jìn)入啟動(dòng)好的MySQL，執(zhí)行以下命令

mysql> show variables like '%slow_query%';

+---------------------------+----------------------------------------+
| Variable_name | Value |
+---------------------------+----------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/cloudlu-slow.log |
+---------------------------+----------------------------------------+

這里告訴我們慢日志的日志存放位置，慢日志是否有開(kāi)啟。
那么什么樣的查詢需要被日志呢？在MySQL中， 沒(méi)有index的查詢 以及 超過(guò)指定時(shí)間同時(shí)超過(guò)指定掃描行數(shù)的查詢 需要記錄在慢日志查詢里面。

那么它們的參數(shù)又是怎么查看的呢？

沒(méi)有index的查詢記錄開(kāi)關(guān)

mysql> show global variables like '%indexes%';

+----------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------+-------+
| log_queries_not_using_indexes | OFF |
| log_throttle_queries_not_using_indexes | 0 |
+----------------------------------------+-------+

第一個(gè)參數(shù) 表示是否開(kāi)啟記錄沒(méi)有index的查詢，第二個(gè)

參數(shù)用來(lái)做日志記錄的流量控制，一分鐘可以記錄多少條，默認(rèn)0是表示不限制。

超過(guò)指定時(shí)長(zhǎng)的查詢開(kāi)關(guān)

mysql> show global variables like '%long_query%';

+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

就一個(gè)參數(shù)指定超過(guò)多少時(shí)長(zhǎng)的查詢需要被記錄

超過(guò)指定行數(shù)的掃描查詢開(kāi)關(guān)

mysql> show variables like '%min_examined_row_limit%';

+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| min_examined_row_limit | 0 |
+------------------------+-------+
1 row in set (0.00 sec)

默認(rèn)是0，代表不現(xiàn)在掃描行數(shù)

設(shè)置開(kāi)啟MySQL慢日志參數(shù)

進(jìn)入MySQL，輸入以下命令或者在MySQL的啟動(dòng)配置文件里面修改或者給MySQL添加啟動(dòng)參數(shù)，進(jìn)入MySQL后的修改如下：

set global long_query_time=0.1;
set global log_queries_not_using_indexes=on;
set global slow_query_log = on;

這里要斟酌的有2點(diǎn)，第一是超過(guò)什么時(shí)長(zhǎng)的日志是有問(wèn)題的，這個(gè)由系統(tǒng)需求來(lái)決定。第二是沒(méi)有使用indexes的日志每分鐘要記錄多少條，要防止日志太多對(duì)性能產(chǎn)生影響。

在實(shí)際的日志分析中，通常慢日志的log數(shù)量不少，同時(shí)相同的查詢被記錄的條數(shù)也會(huì)很多，這里就需要如何從慢日志查詢中找到最有問(wèn)題，最需要優(yōu)化的日志。在這方面，有很多分析工具，最基本的分析工具就是MySQL自帶的mysqldumpslow，mysqldumpslow(Perl腳本)的輸出示例：

[root@cloudlu bin]# ./mysqldumpslow -s t -t 1 /usr/local/mysql/data/cloudlu-slow.log 

Reading mysql slow query log from /usr/local/mysql/data/cloudlu-slow.log 
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=3.0 (3), root[root]@localhost 
 select * from t 

一看就非常清楚，它的輸出主要 統(tǒng)計(jì)不同慢sql的出現(xiàn)次數(shù)(Count 1)，執(zhí)行最長(zhǎng)時(shí)間(Time 0.00s)，累計(jì)總耗費(fèi)時(shí)間(Time 0s)，等待鎖的時(shí)間(Lock 0.00s)，等待鎖的總時(shí)間(Lock 0s)，發(fā)送給客戶端的行總數(shù)(Rows 3.0)，掃描的行總數(shù)(Rows 3)，用戶(root)以及sql語(yǔ)句本身。它最常用的參數(shù)包括：

對(duì)于一般的分析已經(jīng)差不多了，不過(guò)對(duì)于百分比等等數(shù)據(jù)mysqldumpslow就不夠完善了。所以世界上多了很多各種MySQL慢日志分析工具，比較優(yōu)秀的有mysqlsla(Perl腳本)和pt-query-digest(Perl腳本)，可以提供Count, sql的執(zhí)行次數(shù)及占總的slow log數(shù)量的百分比，Time, 執(zhí)行時(shí)間, 包括總時(shí)間, 平均時(shí)間, 最小, 最大時(shí)間, 時(shí)間占到總慢sql時(shí)間的百分比，95% of Time, 去除最快和最慢的sql, 覆蓋率占95%的sql的執(zhí)行時(shí)間，Lock Time, 等待鎖的時(shí)間，95% of Lock , 95%的慢sql等待鎖時(shí)間，Rows sent, 結(jié)果行統(tǒng)計(jì)數(shù)量, 包括平均, 最小, 最大數(shù)量，Rows examined, 掃描的行數(shù)量，還可以生成表報(bào)，存儲(chǔ)分析結(jié)果。這里就不一一介紹了。

通過(guò)這些慢日志分析軟件定位到了慢查詢語(yǔ)句就已經(jīng)完成了SQL優(yōu)化的一大半。接下來(lái)通過(guò)在MySQL中執(zhí)行explain或者desc命令查看慢查詢語(yǔ)句，可以看出為什么SQL查詢慢。

mysql> explain select * from test.t \G 

*************************** 1. row *************************** 
 id: 1 
 select_type: SIMPLE 
 table: t 
 type: ALL 
possible_keys: NULL 
 key: NULL 
 key_len: NULL 
 ref: NULL 
 rows: 2 
 Extra: NULL 
1 row in set (0.00 sec) 

它的輸出格式細(xì)節(jié)可以關(guān)注MySQL explain format，在輸出中最要注意的是：
1. type：ALL是效率最差，最要注意的

2. key：是否有使用Key，key長(zhǎng)度如何

3. Extra：最好不要出現(xiàn)filesort以及temporary，最主要是要關(guān)注在orderby和groupby。

Note: SQL優(yōu)化是個(gè)很復(fù)雜的過(guò)程，有可能出現(xiàn)拆東墻補(bǔ)西墻的情況：比如給數(shù)據(jù)庫(kù)表加入了索引之后，確實(shí)查詢快了，可是存儲(chǔ)空間加多了，插入刪除操作耗時(shí)也增加了，如果在一個(gè)寫(xiě)多讀少的系統(tǒng)中，執(zhí)行這種優(yōu)化可能會(huì)起到反效果。所以優(yōu)化完之后千萬(wàn)不能大意，要持續(xù)監(jiān)控系統(tǒng)，防止出現(xiàn)引入新瓶頸的情況。

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com