Mysql慢查詢使用與參數詳解

慢查詢是mysql中一個非常重要的日志了，我們可以通過mysql慢查詢日志來分析具體導致mysql查詢慢的原因與sql語子，下面就和小編來簡單的學習一下。

慢查詢為系統中查詢時間超過long_query_time的值，分析慢查詢是優化SQL的基礎，默認清下Mysql慢查詢為關閉狀態，可以通過

show variables where Variable_name = 'log_slow_queries';

來查看是否開啟，如果為OFF則需要修改mysql配置文件，在mysqld下面增加以下參數

#慢查詢日志地址,需要mysql運行帳號對該目錄有寫權限
log-slow-queries="/log/slow.log"
#當query語句大於2s時記錄慢查詢日志
long_query_time=2
#沒有使用索引的query也計入慢查詢日志（可根據情況增加）
log-queries-not-using-indexes

慢查詢分析

可以使用mysql自帶的mysqldumpslow來進行分析，該命令可帶3個參數
-s, 是表示按照何種方式排序，c、t、l、r分別是按照記錄次數、時間、查詢時間、返回的記錄數來排序，ac、at、al、ar，表示相應的倒敘；
-t, 是top n的意思，即為返回前面多少條的數據；
-g, 後邊可以寫一個正則匹配模式，大小寫不敏感的；
比如 按照時間返回前10條裡面含有左連接的sql語句

mysqldumpslow -s t -t 10 -g "left join" /alidata/log/mysql/slow.log

mysql運行狀態和變量查看

通過查看mysql的status和variables來優化mysql

1、慢查詢配置，未打開的情況下建議打開，可發現系統中的慢查詢語句以及慢查詢的條數

mysql> show variables like '%slow%';
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| log_slow_queries    | ON                          |
| slow_launch_time    | 2                           |
| slow_query_log      | ON                          |
| slow_query_log_file | /alidata/log/mysql/slow.log |
+---------------------+-----------------------------+
4 rows in set (0.00 sec)
mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 4     |
| Slow_queries        | 3     |
+---------------------+-------+
2 rows in set (0.00 sec)

2、連接數查看，max_connections為允許的最大連接數，Max_used_connections 系統中出現過的最大連接數，據說理想的設置是

Max_used_connections / max_connections * 100% ≈ 85%
mysql> show variables like '%connections%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_connections      | 2000  |
| max_user_connections | 0     |
+----------------------+-------+
2 rows in set (0.00 sec)
mysql> show global status like '%connections%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Connections          | 35049 |
| Max_used_connections | 12    |
+----------------------+-------+
2 rows in set (0.00 sec)

分析日志 ? mysqldumpslow

分析日志，可用mysql提供的mysqldumpslow，使用很簡單，參數可?help查看

# -s：排序方式。c , t , l , r 表示記錄次數、時間、查詢時間的多少、返回的記錄數排序；
#                             ac , at , al , ar 表示相應的倒敘；
# -t：返回前面多少條的數據；
# -g：包含什麼，大小寫不敏感的；
mysqldumpslow -s r -t 10  /slowquery.log     #slow記錄最多的10個語句
mysqldumpslow -s t -t 10 -g "left join"  /slowquery.log     #按照時間排序前10中含有"left join"的
推薦用分析日志工具 ? mysqlsla
wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
tar zvxf mysqlsla-2.03.tar.gz
cd mysqlsla-2.03
perl Makefile.PL
make
make install
mysqlsla /data/mysqldata/slow.log
# mysqlsla會自動判斷日志類型，為了方便可以建立一個配置文件“~/.mysqlsla”
# 在文件裡寫上：top=100，這樣會打印出前100條結果。

【說明】
queries total: 總查詢次數 unique:去重後的sql數量
sorted by : 輸出報表的內容排序
最重大的慢sql統計信息, 包括 平均執行時間, 等待鎖時間, 結果行的總數, 掃描的行總數.
Count, sql的執行次數及占總的slow log數量的百分比.
Time, 執行時間, 包括總時間, 平均時間, 最小, 最大時間, 時間占到總慢sql時間的百分比.
95% of Time, 去除最快和最慢的sql, 覆蓋率占95%的sql的執行時間.
Lock Time, 等待鎖的時間.
95% of Lock , 95%的慢sql等待鎖時間.
Rows sent, 結果行統計數量, 包括平均, 最小, 最大數量.
Rows examined, 掃描的行數量.
Database, 屬於哪個數據庫
Users, 哪個用戶,IP, 占到所有用戶執行的sql百分比
Query abstract, 抽象後的sql語句
Query sample, sql語句

 

show status中文詳解 Mark

狀態名作用域詳細解釋Aborted_clientsGlobal由於客戶端沒有正確關閉連接導致客戶端終止而中斷的連接數Aborted_connectsGlobal試圖連接到MySQL服務器而失敗的連接數Binlog_cache_disk_useGlobal使用臨時二進制日志緩存但超過binlog_cache_size值並使用臨時文件來保存事務中的語句的事務數量Binlog_cache_useGlobal使用臨時二進制日志緩存的事務數量Bytes_receivedBoth從所有客戶端接收到的字節數。Bytes_sentBoth發送給所有客戶端的字節數。com*各種數據庫操作的數量CompressionSession客戶端與服務器之間只否啟用壓縮協議ConnectionsGlobal試圖連接到(不管是否成功)MySQL服務器的連接數Created_tmp_disk_tablesBoth服務器執行語句時在硬盤上自動創建的臨時表的數量Created_tmp_filesGlobalmysqld已經創建的臨時文件的數量Created_tmp_tablesBoth服務器執行語句時自動創建的內存中的臨時表的數量。如果Created_tmp_disk_tables較大，你可能要增加tmp_table_size值使臨時 表基於內存而不基於硬盤Delayed_errorsGlobal用INSERT DELAYED寫的出現錯誤的行數(可能為duplicate key)。Delayed_insert_threadsGlobal使用的INSERT DELAYED處理器線程數。Delayed_writesGlobal寫入的INSERT DELAYED行數Flush_commandsGlobal執行的FLUSH語句數。Handler_commitBoth內部提交語句數Handler_deleteBoth行從表中刪除的次數。Handler_discoverBothMySQL服務器可以問NDB
CLUSTER存儲引擎是否知道某一名字的表。這被稱作發現。Handler_discover說明通過該方法發現的次數。Handler_prepareBothA counter for the prepare phase of two-phase commitoperations.Handler_read_firstBoth索引中第一條被讀的次數。如果較高，它建議服務器正執行大量全索引掃描；例如，SELECT col1 FROM
foo，假定col1有索引。Handler_read_keyBoth根據鍵讀一行的請求數。如果較高，說明查詢和表的索引正確。Handler_read_nextBoth按照鍵順序讀下一行的請求數。如果你用范圍約束或如果執行索引掃描來查詢索引列，該值增加。Handler_read_prevBoth按照鍵順序讀前一行的請求數。該讀方法主要用於優化ORDER BY ... DESC。Handler_read_rndBoth根據固定位置讀一行的請求數。如果你正執行大量查詢並需要對結果進行排序該值較高。你可能使用了大量需要MySQL掃描整個表的查詢或你的連接沒有正確使用鍵。Handler_read_rnd_nextBoth在數據文件中讀下一行的請求數。如果你正進行大量的表掃描，該值較高。通常說明你的表索引不正確或寫入的查詢沒有利用索引。Handler_rollbackBoth內部ROLLBACK語句的數量。Handler_savepointBoth在一個存儲引擎放置一個保存點的請求數量。Handler_savepoint_rollbackBoth在一個存儲引擎的要求回滾到一個保存點數目。Handler_updateBoth在表內更新一行的請求數。Handler_writeBoth在表內插入一行的請求數。Innodb_buffer_pool_pages_dataGlobal包含數據的頁數(髒或干淨)。Innodb_buffer_pool_pages_dirtyGlobal當前的髒頁數。Innodb_buffer_pool_pages_flushedGlobal要求清空的緩沖池頁數Innodb_buffer_pool_pages_freeGlobal空頁數。Innodb_buffer_pool_pages_latchedGlobal在InnoDB緩沖池中鎖定的頁數。這是當前正讀或寫或由於其它原因不能清空或刪除的頁數。Innodb_buffer_pool_pages_miscGlobal忙的頁數，因為它們已經被分配優先用作管理，例如行鎖定或適用的哈希索引。該值還可以計算為Innodb_buffer_pool_pages_total
- Innodb_buffer_pool_pages_free - Innodb_buffer_pool_pages_data。Innodb_buffer_pool_pages_totalGlobal緩沖池總大小（頁數）。Innodb_buffer_pool_read_ahead_rndGlobalInnoDB初始化的“隨機”read-aheads數。當查詢以隨機順序掃描表的一大部分時發生。Innodb_buffer_pool_read_ahead_seqGlobalInnoDB初始化的順序read-aheads數。當InnoDB執行順序全表掃描時發生。Innodb_buffer_pool_read_requestsGlobalInnoDB已經完成的邏輯讀請求數。Innodb_buffer_pool_readsGlobal不能滿足InnoDB必須單頁讀取的緩沖池中的邏輯讀數量。Innodb_buffer_pool_wait_freeGlobal一般情況，通過後台向InnoDB緩沖池寫。但是，如果需要讀或創建頁，並且沒有干淨的頁可用，則它還需要先等待頁面清空。該計數器對等待實例進行記數。如果已經適當設置緩沖池大小，該值應小。Innodb_buffer_pool_write_requestsGlobal向InnoDB緩沖池的寫數量。Innodb_data_fsyncsGlobalfsync()操作數。Innodb_data_pending_fsyncsGlobal當前掛起的fsync()操作數。Innodb_data_pending_readsGlobal當前掛起的讀數。Innodb_data_pending_writesGlobal當前掛起的寫數。Innodb_data_readGlobal至此已經讀取的數據數量（字節）。Innodb_data_readsGlobal數據讀總數量。Innodb_data_writesGlobal數據寫總數量。Innodb_data_writtenGlobal至此已經寫入的數據量（字節）。Innodb_dblwr_pages_writtenGlobal已經執行的雙寫操作數量Innodb_dblwr_writesGlobal雙寫操作已經寫好的頁數Innodb_log_waitsGlobal我們必須等待的時間，因為日志緩沖區太小，我們在繼續前必須先等待對它清空Innodb_log_write_requestsGlobal日志寫請求數。Innodb_log_writesGlobal向日志文件的物理寫數量。Innodb_os_log_fsyncsGlobal向日志文件完成的fsync()寫數量。Innodb_os_log_pending_fsyncsGlobal掛起的日志文件fsync()操作數量。Innodb_os_log_pending_writesGlobal掛起的日志文件寫操作Innodb_os_log_writtenGlobal寫入日志文件的字節數。Innodb_page_sizeGlobal編譯的InnoDB頁大小(默認16KB)。許多值用頁來記數；頁的大小很容易轉換為字節。Innodb_pages_createdGlobal創建的頁數。Innodb_pages_readGlobal讀取的頁數。Innodb_pages_writtenGlobal寫入的頁數。Innodb_row_lock_current_waitsGlobal當前等待的待鎖定的行數。Innodb_row_lock_timeGlobal行鎖定花費的總時間，單位毫秒。Innodb_row_lock_time_avgGlobal行鎖定的平均時間，單位毫秒。Innodb_row_lock_time_maxGlobal行鎖定的最長時間，單位毫秒。Innodb_row_lock_waitsGlobal一行鎖定必須等待的時間數。Innodb_rows_deletedGlobal從InnoDB表刪除的行數。Innodb_rows_insertedGlobal插入到InnoDB表的行數。Innodb_rows_readGlobal從InnoDB表讀取的行數。Innodb_rows_updatedGlobalInnoDB表內更新的行數。Key_blocks_not_flushedGlobal鍵緩存內已經更改但還沒有清空到硬盤上的鍵的數據塊數量。Key_blocks_unusedGlobal鍵緩存內未使用的塊數量。你可以使用該值來確定使用了多少鍵緩存Key_blocks_usedGlobal鍵緩存內使用的塊數量。該值為高水平線標記，說明已經同時最多使用了多少塊。Key_read_requestsGlobal從緩存讀鍵的數據塊的請求數。Key_readsGlobal從硬盤讀取鍵的數據塊的次數。如果Key_reads較大，則Key_buffer_size值可能太小。可以用Key_reads/Key_read_requests計算緩存損失率。Key_write_requestsGlobal將鍵的數據塊寫入緩存的請求數。Key_writesGlobal向硬盤寫入將鍵的數據塊的物理寫操作的次數。Last_query_costSession用查詢優化器計算的最後編譯的查詢的總成本。用於對比同一查詢的不同查詢方案的成本。默認值0表示還沒有編譯查詢。 默認值是0。Last_query_cost具有會話范圍。Max_used_connectionsGlobal服務器啟動後已經同時使用的連接的最大數量。ndb*ndb集群相關Not_flushed_delayed_rowsGlobal等待寫入INSERT DELAY隊列的行數。  Open_filesGlobal打開的文件的數目。Open_streamsGlobal打開的流的數量(主要用於記錄)。Open_table_definitionsGlobal緩存的.frm文件數量Open_tablesBoth當前打開的表的數量。Opened_filesGlobal文件打開的數量。不包括諸如套接字或管道其他類型的文件。 也不包括存儲引擎用來做自己的內部功能的文件。Opened_table_definitionsBoth已經緩存的.frm文件數量Opened_tablesBoth已經打開的表的數量。如果Opened_tables較大，table_cache 值可能太小。Prepared_stmt_countGlobal當前的預處理語句的數量。 (最大數為系統變量: max_prepared_stmt_count) Qcache_free_blocksGlobal查詢緩存內自由內存塊的數量。Qcache_free_memoryGlobal用於查詢緩存的自由內存的數量。Qcache_hitsGlobal查詢緩存被訪問的次數。Qcache_insertsGlobal加入到緩存的查詢數量。Qcache_lowmem_prunesGlobal由於內存較少從緩存刪除的查詢數量。Qcache_not_cachedGlobal非緩存查詢數(不可緩存，或由於query_cache_type設定值未緩存)。Qcache_queries_in_cacheGlobal登記到緩存內的查詢的數量。Qcache_total_blocksGlobal查詢緩存內的總塊數。QueriesBoth服務器執行的請求個數，包含存儲過程中的請求。QuestionsBoth已經發送給服務器的查詢的個數。Rpl_statusGlobal失敗安全復制狀態(還未使用)。Select_full_joinBoth沒有使用索引的聯接的數量。如果該值不為0,你應仔細檢查表的索引Select_full_range_joinBoth在引用的表中使用范圍搜索的聯接的數量。Select_rangeBoth在第一個表中使用范圍的聯接的數量。一般情況不是關鍵問題，即使該值相當大。Select_range_checkBoth在每一行數據後對鍵值進行檢查的不帶鍵值的聯接的數量。如果不為0，你應仔細檢查表的索引。Select_scanBoth對第一個表進行完全掃描的聯接的數量。Slave_heartbeat_periodGlobal復制的心跳間隔Slave_open_temp_tablesGlobal從服務器打開的臨時表數量Slave_received_heartbeatsGlobal從服務器心跳數Slave_retried_transactionsGlobal本次啟動以來從服務器復制線程重試次數Slave_runningGlobal如果該服務器是連接到主服務器的從服務器，則該值為ON。Slow_launch_threadsBoth創建時間超過slow_launch_time秒的線程數。Slow_queriesBoth查詢時間超過long_query_time秒的查詢的個數。Sort_merge_passesBoth排序算法已經執行的合並的數量。如果這個變量值較大，應考慮增加sort_buffer_size系統變量的值。Sort_rangeBoth在范圍內執行的排序的數量。Sort_rowsBoth已經排序的行數。Sort_scanBoth通過掃描表完成的排序的數量。ssl＊ssl連接相關Table_locks_immediateGlobal立即獲得的表的鎖的次數。Table_locks_waitedGlobal不能立即獲得的表的鎖的次數。如果該值較高，並且有性能問題，你應首先優化查詢，然後拆分表或使用復制。Threads_cachedGlobal線程緩存內的線程的數量。Threads_connectedGlobal當前打開的連接的數量。Threads_createdGlobal創建用來處理連接的線程數。如果Threads_created較大，你可能要增加thread_cache_size值。緩存訪問率的計算方法Threads_created/Connections。Threads_runningGlobal激活的（非睡眠狀態）線程數。UptimeGlobal服務器已經運行的時間（以秒為單位）。Uptime_since_flush_statusGlobal最近一次使用FLUSH STATUS 的時間（以秒為單位）