Mysql慢查詢使用與參數詳解

慢查詢為系統中查詢時間超過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"的推薦用分析日誌工具 ? mysqlslawget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gztar zvxf mysqlsla-2.03.tar.gzcd mysqlsla-2.03perl Makefile.PLmakemake installmysqlsla /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_clients	Global	由於用戶端沒有正確關閉串連導致用戶端終止而中斷的串連數
Aborted_connects	Global	試圖串連到MySQL伺服器而失敗的串連數
Binlog_cache_disk_use	Global	使用臨時二進位日誌緩衝但超過binlog_cache_size值並使用臨時檔案來儲存事務中的語句的事務數量
Binlog_cache_use	Global	使用臨時二進位日誌緩衝的事務數量
Bytes_received	Both	從所有用戶端接收到的位元組數。
Bytes_sent	Both	發送給所有用戶端的位元組數。
com*		各種資料庫操作的數量
Compression	Session	用戶端與伺服器之間只否啟用壓縮協議
Connections	Global	試圖串連到(不管是否成功)MySQL伺服器的串連數
Created_tmp_disk_tables	Both	伺服器執行語句時在硬碟上自動建立的暫存資料表的數量
Created_tmp_files	Global	mysqld已經建立的臨時檔案的數量
Created_tmp_tables	Both	伺服器執行語句時自動建立的記憶體中的暫存資料表的數量。如果Created_tmp_disk_tables較大，你可能要增加tmp_table_size值使臨時 表基於記憶體而不基於硬碟
Delayed_errors	Global	用INSERT DELAYED寫的出現錯誤的行數(可能為duplicate key)。
Delayed_insert_threads	Global	使用的INSERT DELAYED處理器線程數。
Delayed_writes	Global	寫入的INSERT DELAYED行數
Flush_commands	Global	執行的FLUSH語句數。
Handler_commit	Both	內部提交語句數
Handler_delete	Both	行從表中刪除的次數。
Handler_discover	Both	MySQL伺服器可以問NDB CLUSTER儲存引擎是否知道某一名字的表。這被稱作發現。Handler_discover說明通過該方法發現的次數。
Handler_prepare	Both	A counter for the prepare phase of two-phase commitoperations.
Handler_read_first	Both	索引中第一條被讀的次數。如果較高，它建議伺服器正執行大量全索引掃描；例如，SELECT col1 FROM foo，假定col1有索引。
Handler_read_key	Both	根據鍵讀一行的請求數。如果較高，說明查詢和表的索引正確。
Handler_read_next	Both	按照鍵順序讀下一行的請求數。如果你用範圍約束或如果執行索引掃描來查詢索引列，該值增加。
Handler_read_prev	Both	按照鍵順序讀前一行的請求數。該讀方法主要用於最佳化ORDER BY ... DESC。
Handler_read_rnd	Both	根據固定位置讀一行的請求數。如果你正執行大量查詢並需要對結果進行排序該值較高。你可能使用了大量需要MySQL掃描整個表的查詢或你的串連沒有正確使用鍵。
Handler_read_rnd_next	Both	在資料檔案中讀下一行的請求數。如果你正進行大量的表掃描，該值較高。通常說明你的表索引不正確或寫入的查詢沒有利用索引。
Handler_rollback	Both	內部ROLLBACK語句的數量。
Handler_savepoint	Both	在一個儲存引擎放置一個儲存點的請求數量。
Handler_savepoint_rollback	Both	在一個儲存引擎的要求復原到一個儲存點數目。
Handler_update	Both	在表內更新一行的請求數。
Handler_write	Both	在表插入入一行的請求數。
Innodb_buffer_pool_pages_data	Global	包含資料的頁數(髒或乾淨)。
Innodb_buffer_pool_pages_dirty	Global	當前的髒頁數。
Innodb_buffer_pool_pages_flushed	Global	要求清空的緩衝池頁數
Innodb_buffer_pool_pages_free	Global	空頁數。
Innodb_buffer_pool_pages_latched	Global	在InnoDB緩衝池中鎖定的頁數。這是當前正讀或寫或由於其它原因不能清空或刪除的頁數。
Innodb_buffer_pool_pages_misc	Global	忙的頁數，因為它們已經被分配優先用作管理，例如行鎖定或適用的雜湊索引。該值還可以計算為Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free - Innodb_buffer_pool_pages_data。
Innodb_buffer_pool_pages_total	Global	緩衝池總大小（頁數）。
Innodb_buffer_pool_read_ahead_rnd	Global	InnoDB初始化的“隨機”read-aheads數。當查詢以隨機順序掃描表的一大部分時發生。
Innodb_buffer_pool_read_ahead_seq	Global	InnoDB初始化的順序read-aheads數。當InnoDB執行順序全表掃描時發生。
Innodb_buffer_pool_read_requests	Global	InnoDB已經完成的邏輯讀請求數。
Innodb_buffer_pool_reads	Global	不能滿足InnoDB必須單頁讀取的緩衝池中的邏輯讀數量。
Innodb_buffer_pool_wait_free	Global	一般情況，通過後台向InnoDB緩衝池寫。但是，如果需要讀或建立頁，並且沒有乾淨的頁可用，則它還需要先等待頁面清空。該計數器對等待執行個體進行記數。如果已經適當設定緩衝池大小，該值應小。
Innodb_buffer_pool_write_requests	Global	向InnoDB緩衝池的寫數量。
Innodb_data_fsyncs	Global	fsync()運算元。
Innodb_data_pending_fsyncs	Global	當前掛起的fsync()運算元。
Innodb_data_pending_reads	Global	當前掛起的讀數。
Innodb_data_pending_writes	Global	當前掛起的寫數。
Innodb_data_read	Global	至此已經讀取的資料數量（位元組）。
Innodb_data_reads	Global	資料讀總數量。
Innodb_data_writes	Global	資料寫總數量。
Innodb_data_written	Global	至此已經寫入的資料量（位元組）。
Innodb_dblwr_pages_written	Global	已經執行的雙寫運算元量
Innodb_dblwr_writes	Global	雙寫操作已經寫好的頁數
Innodb_log_waits	Global	我們必須等待的時間，因為日誌緩衝區太小，我們在繼續前必須先等待對它清空
Innodb_log_write_requests	Global	日誌寫請求數。
Innodb_log_writes	Global	向記錄檔的物理寫數量。
Innodb_os_log_fsyncs	Global	向記錄檔完成的fsync()寫數量。
Innodb_os_log_pending_fsyncs	Global	掛起的記錄檔fsync()運算元量。
Innodb_os_log_pending_writes	Global	掛起的記錄檔寫操作
Innodb_os_log_written	Global	寫入記錄檔的位元組數。
Innodb_page_size	Global	編譯的InnoDB頁大小(預設16KB)。許多值用頁來記數；頁的大小很容易轉換為位元組。
Innodb_pages_created	Global	建立的頁數。
Innodb_pages_read	Global	讀取的頁數。
Innodb_pages_written	Global	寫入的頁數。
Innodb_row_lock_current_waits	Global	當前等待的待鎖定的行數。
Innodb_row_lock_time	Global	行鎖定花費的總時間，單位毫秒。
Innodb_row_lock_time_avg	Global	行鎖定的平均時間，單位毫秒。
Innodb_row_lock_time_max	Global	行鎖定的最長時間，單位毫秒。
Innodb_row_lock_waits	Global	一行鎖定必須等待的時間數。
Innodb_rows_deleted	Global	從InnoDB表刪除的行數。
Innodb_rows_inserted	Global	插入到InnoDB表的行數。
Innodb_rows_read	Global	從InnoDB表讀取的行數。
Innodb_rows_updated	Global	InnoDB表內更新的行數。
Key_blocks_not_flushed	Global	金鑰快取內已經更改但還沒有清空到硬碟上的鍵的資料區塊數量。
Key_blocks_unused	Global	金鑰快取內未使用的塊數量。你可以使用該值來確定使用了多少金鑰快取
Key_blocks_used	Global	金鑰快取內使用的塊數量。該值為高水平線標記，說明已經同時最多使用了多少塊。
Key_read_requests	Global	從緩衝讀鍵的資料區塊的請求數。
Key_reads	Global	從硬碟讀取鍵的資料區塊的次數。如果Key_reads較大，則Key_buffer_size值可能太小。可以用Key_reads/Key_read_requests計算緩衝損失率。
Key_write_requests	Global	將鍵的資料區塊寫入緩衝的請求數。
Key_writes	Global	向硬碟寫入將鍵的資料區塊的物理寫操作的次數。
Last_query_cost	Session	用查詢最佳化工具計算的最後編譯的查詢的總成本。用於對比同一查詢的不同查詢方案的成本。預設值0表示還沒有編譯查詢。 預設值是0。Last_query_cost具有會話範圍。
Max_used_connections	Global	伺服器啟動後已經同時使用的串連的最大數量。
ndb*		ndb叢集相關
Not_flushed_delayed_rows	Global	等待寫入INSERT DELAY隊列的行數。
Open_files	Global	開啟的檔案的數目。
Open_streams	Global	開啟的流的數量(主要用於記錄)。
Open_table_definitions	Global	緩衝的.frm檔案數量
Open_tables	Both	當前開啟的表的數量。
Opened_files	Global	檔案開啟的數量。不包括諸如通訊端或管道其他類型的檔案。 也不包括儲存引擎用來做自己的內部功能的檔案。
Opened_table_definitions	Both	已經緩衝的.frm檔案數量
Opened_tables	Both	已經開啟的表的數量。如果Opened_tables較大，table_cache 值可能太小。
Prepared_stmt_count	Global	當前的預先處理語句的數量。 (最大數為系統變數: max_prepared_stmt_count)
Qcache_free_blocks	Global	查詢快取內自由記憶體塊的數量。
Qcache_free_memory	Global	用於查詢快取的自由記憶體的數量。
Qcache_hits	Global	查詢快取被訪問的次數。
Qcache_inserts	Global	加入到緩衝的查詢數量。
Qcache_lowmem_prunes	Global	由於記憶體較少從緩衝刪除的查詢數量。
Qcache_not_cached	Global	非緩衝查詢數(不可緩衝，或由於query_cache_type設定值未緩衝)。
Qcache_queries_in_cache	Global	登記到緩衝內的查詢的數量。
Qcache_total_blocks	Global	查詢快取內的總塊數。
Queries	Both	伺服器執行的請求個數，包含預存程序中的請求。
Questions	Both	已經發送給伺服器的查詢的個數。
Rpl_status	Global	失敗安全複製狀態(還未使用)。
Select_full_join	Both	沒有使用索引的聯結的數量。如果該值不為0,你應仔細檢查表的索引
Select_full_range_join	Both	在引用的表中使用範圍搜尋的聯結的數量。
Select_range	Both	在第一個表中使用範圍的聯結的數量。一般情況不是關鍵問題，即使該值相當大。
Select_range_check	Both	在每一行資料後對索引值進行檢查的不帶索引值的聯結的數量。如果不為0，你應仔細檢查表的索引。
Select_scan	Both	對第一個表進行完全掃描的聯結的數量。
Slave_heartbeat_period	Global	複製的心跳間隔
Slave_open_temp_tables	Global	從伺服器開啟的暫存資料表數量
Slave_received_heartbeats	Global	從伺服器心跳數
Slave_retried_transactions	Global	本次啟動以來從伺服器複製線程重試次數
Slave_running	Global	如果該伺服器是串連到主伺服器的從伺服器，則該值為ON。
Slow_launch_threads	Both	建立時間超過slow_launch_time秒的線程數。
Slow_queries	Both	查詢時間超過long_query_time秒的查詢的個數。
Sort_merge_passes	Both	排序演算法已經執行的合并的數量。如果這個變數值較大，應考慮增加sort_buffer_size系統變數的值。
Sort_range	Both	在範圍內執行的排序的數量。
Sort_rows	Both	已經排序的行數。
Sort_scan	Both	通過掃描表完成的排序的數量。
ssl＊		ssl串連相關
Table_locks_immediate	Global	立即獲得的表的鎖的次數。
Table_locks_waited	Global	不能立即獲得的表的鎖的次數。如果該值較高，並且有效能問題，你應首先最佳化查詢，然後拆分表或使用複製。
Threads_cached	Global	線程緩衝內的線程的數量。
Threads_connected	Global	當前開啟的串連的數量。
Threads_created	Global	建立用來處理串連的線程數。如果Threads_created較大，你可能要增加thread_cache_size值。緩衝訪問率的計算方法Threads_created/Connections。
Threads_running	Global	啟用的（非睡眠狀態）線程數。
Uptime	Global	伺服器已經啟動並執行時間（以秒為單位）。
Uptime_since_flush_status	Global	最近一次使用FLUSH STATUS 的時間（以秒為單位）