程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL慢查詢優化之慢查詢日記剖析的實例教程

MySQL慢查詢優化之慢查詢日記剖析的實例教程

編輯:MySQL綜合教程

MySQL慢查詢優化之慢查詢日記剖析的實例教程。本站提示廣大學習愛好者:(MySQL慢查詢優化之慢查詢日記剖析的實例教程)文章只能為提供參考,不一定能成為您想要的結果。以下是MySQL慢查詢優化之慢查詢日記剖析的實例教程正文


數據庫呼應慢成績最多的就是查詢了。如今年夜部門數據庫都供給了機能剖析的贊助手腕。例如Oracle中會幫你直接找出慢的語句,而且供給優化計劃。在MySQL中就要本身開啟慢日記記載加以剖析(記載可以保留在表或許文件中,默許是保留在文件中,我們體系應用的就是默許方法)。

先看看MySQL慢查詢日記外面的記載長甚麼樣的:

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;

這個日記應當很好懂得了,第一個#記載時光戳,第二個#記載履行敕令的用戶和地址信息,第三個#記載履行查詢的時光、鎖的時光、前往行數、被掃描的行數。接著前面記載真正履行的SQL語句。還可以經由過程以下敕令看看cvs存儲格局每一個字段意義。

SHOW CREATE TABLE mysql.slow_log;

接上去說說若何獲得和剖析慢日記吧。

檢查MySQL慢日記參數

進入啟動好的MySQL,履行以下敕令

mysql> show variables like '%slow_query%';
+---------------------------+----------------------------------------+
| Variable_name       | Value                 |
+---------------------------+----------------------------------------+
| slow_query_log      | OFF                  |
| slow_query_log_file    | /usr/local/mysql/data/cloudlu-slow.log |
+---------------------------+----------------------------------------+

這裡告知我們慢日記的日記寄存地位,慢日記能否有開啟。
那末甚麼樣的查詢須要被日記呢?在MySQL中, 沒有index的查詢 和 跨越指准時間同時跨越指定掃描行數的查詢 須要記載在慢日記查詢外面。

那末它們的參數又是怎樣檢查的呢?

沒有index的查詢記載開關

mysql> show global variables like '%indexes%';
+----------------------------------------+-------+
| Variable_name             | Value |
+----------------------------------------+-------+
| log_queries_not_using_indexes     | OFF  |
| log_throttle_queries_not_using_indexes | 0   |
+----------------------------------------+-------+

第一個參數 表現能否開啟記載沒有index的查詢,第二個

參數用來做日記記載的流量掌握,一分鐘可以記載若干條,默許0是表現不限制。

跨越指准時長的查詢開關

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

就一個參數指定跨越若干時長的查詢須要被記載

跨越指定行數的掃描查詢開關

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

默許是0,代表不如今掃描行數

設置開啟MySQL慢日記參數

進入MySQL,輸出以下敕令或許在MySQL的啟動設置裝備擺設文件外面修正或許給MySQL添加啟動參數,進入MySQL後的修正以下:

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

這裡要推敲的有2點,第一是跨越甚麼時長的日記是有成績的,這個由體系需求來決議。第二是沒有應用indexes的日記每分鐘要記載若干條,要避免日記太多對機能發生影響。

在現實的日記剖析中,平日慢日記的log數目很多,同時雷同的查詢被記載的條數也會許多,這裡就須要若何從慢日記查詢中找到最有成績,最須要優化的日記。在這方面,有許多剖析對象,最根本的剖析對象就是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 

一看就異常清晰,它的輸入重要 統計分歧慢sql的湧現次數(Count 1),履行最長時光(Time 0.00s),累計總消耗時光(Time 0s),期待鎖的時光(Lock 0.00s),期待鎖的總時光(Lock 0s),發送給客戶真個行總數(Rows 3.0),掃描的行總數(Rows 3),用戶(root)和sql語句自己。它最經常使用的參數包含:

  • -s 排序選項:c 查詢次數 r 前往記載行數 t 查詢時光
  • -t n:顯示top n條查詢

關於普通的剖析曾經差不多了,不外關於百分比等等數據mysqldumpslow就不敷完美了。所以世界上多了許多各類MySQL慢日記剖析對象,比擬優良的有mysqlsla(Perl劇本)和pt-query-digest(Perl劇本),可以供給Count, sql的履行次數及占總的slow log數目的百分比,Time, 履行時光, 包含總時光, 均勻時光, 最小, 最年夜時光, 時光占到總慢sql時光的百分比,95% of Time, 去除最快和最慢的sql, 籠罩率占95%的sql的履行時光,Lock Time, 期待鎖的時光,95% of Lock , 95%的慢sql期待鎖時光,Rows sent, 成果行統計數目, 包含均勻, 最小, 最年夜數目,Rows examined, 掃描的行數目,還可以生成表報,存儲剖析成果。這裡就紛歧一引見了。

經由過程這些慢日記剖析軟件定位到了慢查詢語句就曾經完成了SQL優化的一年夜半。接上去經由過程在MySQL中履行explain或許desc敕令檢查慢查詢語句,可以看出為何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) 

它的輸入格局細節可以存眷MySQL explain format,在輸入中最要留意的是:
1. type:ALL是效力最差,最要留意的

2. key:能否有應用Key,key長度若何

3. Extra:最好不要湧現filesort和temporary,最重要是要存眷在orderby和groupby。


Note: SQL優化是個很龐雜的進程,有能夠湧現拆東牆補西牆的情形:好比給數據庫表參加了索引以後,確切查詢快了,可是存儲空間加多了,拔出刪除操作耗時也增長了,假如在一個寫多讀少的體系中,履行這類優化能夠會起到反後果。所以優化完以後萬萬不克不及年夜意,要連續監控體系,避免湧現引入新瓶頸的情形。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved