mysql 運作狀态分析 運作故障排查

内容轉載:http://www.apeblog.cn/article/18.html

目錄

一、優化概述

二、查詢與索引優化分析

1性能瓶頸定位

    Show指令

    慢查詢日志

    explain分析查詢

    profiling分析查詢

2索引及查詢優化

一、優化概述

MySQL資料庫是常見的兩個瓶頸是CPU和I/O的瓶頸，CPU在飽和的時候一般發生在資料裝入記憶體或從磁盤上讀取資料時候。磁盤I/O瓶頸發生在裝入資料遠大于記憶體容量的時候，如果應用分布在網絡上，那麼查詢量相當大的時候那麼平瓶頸就會出現在網絡上，我們可以用mpstat, iostat, sar和vmstat來檢視系統的性能狀态。

除了伺服器硬體的性能瓶頸，對于MySQL系統本身，我們可以使用工具來優化資料庫的性能，通常有三種：使用索引，使用EXPLAIN分析查詢以及調整MySQL的内部配置。

二、查詢與索引優化分析

在優化MySQL時，通常需要對資料庫進行分析，常見的分析手段有慢查詢日志，EXPLAIN 分析查詢，profiling分析以及show指令查詢系統狀态及系統變量，通過定位分析性能的瓶頸，才能更好的優化資料庫系統的性能。

show 指令

我們可以通過show指令檢視MySQL狀态及變量，找到系統的瓶頸：

Mysql> show status ——顯示狀态資訊（擴充show status like ‘XXX’）

Mysql> show variables ——顯示系統變量（擴充show variables like ‘XXX’）show variables like "slow%"

Mysql> show innodb status ——顯示InnoDB存儲引擎的狀态

Mysql> show processlist ——檢視目前SQL執行，包括執行狀态、是否鎖表等

Shell> mysqladmin variables -u username -p password——顯示系統變量

Shell> mysqladmin extended-status -u username -p password——顯示狀态資訊

慢查詢日志

慢查詢 可以記錄執行時間比較慢的語句 和 未使用索引的語句

一、MySQL資料庫有幾個配置選項可以幫助我們及時捕獲低效SQL語句

1，slow_query_log  這個參數設定為ON，可以捕獲執行時間超過一定數值的SQL語句。

2，long_query_time 當SQL語句執行時間超過此數值時，就會被記錄到日志中，建議設定為1或者更短(機關秒)。

3，slow_query_log_file 記錄日志的檔案名。

4，log_queries_not_using_indexes  這個參數設定為ON，可以捕獲到所有未使用索引的SQL語句，盡管這個SQL語句有可能執行得挺快。

explain分析查詢

使用 EXPLAIN 關鍵字可以模拟優化器執行SQL查詢語句，進而知道MySQL是如何處理你的SQL語句的。這可以幫你分析你的查詢語句或是表結構的性能瓶頸。通過explain指令可以得到:

– 表的讀取順序

– 資料讀取操作的操作類型

– 哪些索引可以使用

– 哪些索引被實際使用

– 表之間的引用

– 每張表有多少行被優化器查詢

SQL

mysql> explain select * from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   85 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
           

EXPLAIN字段：

id: 查詢序号即為sql語句執行的順序

select_type

select類型，它有以下幾種值

2.1 simple 它表示簡單的select,沒有union和子查詢

2.2 primary 最外面的select,在有子查詢的語句中，最外面的select查詢就是primary,上圖中就是這樣

2.3 union union語句的第二個或者說是後面那一個.現執行一條語句，explain 

Table：顯示這一行的資料是關于哪張表的

possible_keys：顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個合适的語句

key：實際使用的索引。如果為NULL，則沒有使用索引。MYSQL很少會選擇優化不足的索引，此時可以在SELECT語句中使用USE INDEX（index）來強制使用一個索引或者用IGNORE INDEX（index）來強制忽略索引

key_len：使用的索引的長度。在不損失精确性的情況下，長度越短越好

ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數

rows：MySQL認為必須檢索的用來傳回請求資料的行數

type：這是最重要的字段之一，顯示查詢使用了何種類型。從最好到最差的連接配接類型為system、const、eq_reg、ref、range、index和ALL

Extra：關于MYSQL如何解析查詢的額外資訊，主要有以下幾種

Extra:參數說明

using index：隻用到索引,可以避免通路表. 

using where：使用到where來過慮資料. 不是所有的where clause都要顯示using where. 如以=方式通路索引.

using tmporary：用到臨時表

using filesort：用到額外的排序. (當使用order by v1,而沒用到索引時,就會使用額外的排序)

range checked for eache record(index map:N)：沒有好的索引.

type：參數說明

system、const：可以将查詢的變量轉為常量.  如id=1; id為 主鍵或唯一鍵.

eq_ref：通路索引,傳回某單一行的資料.(通常在聯接時出現，查詢使用的索引為主鍵或惟一鍵)

ref：通路索引,傳回某個值的資料.(可以傳回多行) 通常使用=時發生

range：這個連接配接類型使用索引傳回一個範圍中的行，比如使用>或<查找東西，并且該字段上建有索引時發生的情況(注:不一定好于index)

index：以索引的順序進行全表掃描，優點是不用排序,缺點是還要全表掃描

ALL：全表掃描，應該盡量避免

profiling分析查詢

通過慢日志查詢可以知道哪些SQL語句執行效率低下，通過explain我們可以得知SQL語句的具體執行情況，索引使用等，還可以結合show指令檢視執行狀态。

如果覺得explain的資訊不夠詳細，可以同通過profiling指令得到更準确的SQL執行消耗系統資源的資訊。

profiling預設是關閉的。可以通過以下語句檢視  select @@profiling;

打開功能： mysql>set profiling=1; 執行需要測試的sql 語句：

開啟後執行了sql語句 就可以通過show profiles;指令檢視執行時間

SQL

mysql> show profiles;
+----------+------------+---------------------------+
| Query_ID | Duration   | Query                     |
+----------+------------+---------------------------+
|        1 | 0.00417000 | select * from user        |
|        2 | 0.00214700 | select count(*) from user |
+----------+------------+---------------------------+
2 rows in set, 1 warning (0.00 sec)
           

mysql> show profiles\G; 可以得到被執行的SQL語句的時間和ID

mysql>show profile for query 1; 得到對應SQL語句執行的詳細資訊

Bash

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.001587 |
| checking permissions | 0.000351 |
| Opening tables       | 0.000015 |
| init                 | 0.000021 |
| System lock          | 0.000264 |
| optimizing           | 0.000014 |
| statistics           | 0.000014 |
| preparing            | 0.000012 |
| executing            | 0.000003 |
| Sending data         | 0.001485 |
| end                  | 0.000018 |
| query end            | 0.000120 |
| closing tables       | 0.000026 |
| freeing items        | 0.000157 |
| cleaning up          | 0.000083 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)