MySQL通過show status檢視、explain分析優化資料庫效能

2022-04-08 22:00:14

1.概述

在應用系統開發過程中，由於初期資料量小，開發人員寫SQL語句時更重視功能上的實現，但是當應用系統正式上線後，隨著生產資料量的急劇增長，很多SQL語句開始逐漸顯露出效能問題，對生產環境的影響也越來越大，此時這些有問題的SQL語句就成為整個系統效能的瓶頸，因此我們必須要對它們進行優化，該章節將詳細介紹在MySQL中優化SQL語句的方法。

2.通過show status命令瞭解各種SQL的執行頻率

MySQL使用者端連線成功後，通過show [session|global]status命令可以提供伺服器狀態資訊，也可以在作業系統上使用mysqladmin extended-status命令獲得這些訊息。show [session|global] status可以根據需要加上引數“session”或者“global”來顯示session級（當前連線）的統計結果和global級（自資料庫上次啟動至今）的統計結果。如果不寫，預設使用引數是“session”。

下面的命令顯示了當前session中所有統計引數的值：

-- 檢視對談所有統計的值
SHOW STATUS LIKE 'Com_%';
Or
SHOW SESSION STATUS LIKE 'Com_%';

下面的命令顯示了當前global中所有統計引數的值：

-- 檢視全域性所有統計的值

SHOW GLOBAL STATUS LIKE 'Com_%';

Com_xxx表示每個xxx語句執行的次數，我們通常比較關心的是以下幾個統計引數：

Com_select：執行SELECT操作的次數，一次查詢只累加1。
Com_insert：執行INSERT操作的次數，對於批次插入的INSERT操作，只累加一次。
Com_update：執行UPDATE操作的次數。
Com_delete：執行DELETE操作的次數。

上面這些引數對於所有儲存引擎的表操作都會進行累計。下面這幾個引數只是針對InnoDB儲存引擎的，累加的演演算法也略有不同。

Innodb_rows_read：SELECT查詢返回的行數。
Innodb_rows_inserted：執行INSERT操作插入的行數。
Innodb_rows_updated：執行UPDATE操作更新的行數。
Innodb_rows_deleted：執行DELETE操作刪除的行數。

通過以上幾個引數，可以很容易地瞭解當前資料庫的應用系統是以插入更新為主還是以查詢操作為主，以及各種型別的SQL大致的執行比例是多少。對於更新操作的計數，是對執行次數的計數，不論提交還是回滾都會進行累加。

對於事務型的應用，通過Com_commit和Com_rollback可以瞭解事務提交和回滾的情況，對於回滾操作非常頻繁的資料庫，可能意味著應用編寫存在問題。此外，以下幾個引數便於使用者瞭解資料庫的基本情況。

Connections：試圖連線MySQL伺服器的次數。
Uptime：伺服器工作時間。
Slow_queries：慢查詢的次數。

3.定位執行效率較低的SQL語句

可以通過以下兩種方式定位執行效率較低的SQL語句。

通過慢查詢紀錄檔定位那些執行效率較低的SQL語句，用--log-slow-queries[=file_name]選項啟動時，mysqld寫一個包含所有執行時間超過long_query_time秒的SQL語句的紀錄檔檔案。
慢查詢紀錄檔在查詢結束以後才紀錄，所以在應用系統反映執行效率出現問題的時候查詢慢查詢紀錄檔並不能定位問題，可以使用show processlist命令檢視當前MySQL在進行的執行緒，包括執行緒的狀態、是否鎖表等，可以實時地檢視SQL的執行情況，同時對一些鎖表操作進行優化。

4.通過EXPLAIN分析低效SQL的執行計劃

通過定位執行效率較低的SQL語句後，可以通過EXPLAIN或者DESC命令獲取MySQL如何執行SELECT語句的資訊，包括在SELECT語句執行過程中表如何連線和連線的順序，比如想統計所有庫存階梯數量，需要關聯goods_stock表和goods_stock_price表，並且對goods_stock_price.Qty欄位做求和（sum）操作，相應 SQL 的執行計劃如下：

EXPLAIN SELECT SUM(sp.Qty)
FROM goods_stock AS s LEFT JOIN goods_stock_price AS sp
ON s.ID=sp.GoodsStockID;

如上圖所示每個列的簡單解釋如下：

select_type：表示 SELECT 的型別，常見的取值有：
- SIMPLE（簡單表，即不使用表連線或者子查詢）。
- PRIMARY（主查詢，即外層的查詢）、UNION（UNION 中的第二個或者後面的查詢語句）、◎SUBQUERY（子查詢中的第一個SELECT）等。
table：輸出結果集的表。
type：表示表的連線型別，效能由好到差的連線型別為：
- system（表中僅有一行，即常數表）。
- const（單表中最多有一個匹配行，例如primary key或者unique index）。
- eq_ref（對於前面的每一行，在此表中只查詢一條記錄，簡單來說，就是多表連線中使用primary key或者unique index）。
- ref（與eq_ref類似，區別在於不是使用primary key或者unique index，而是使用普通的索引）。
- ref_or_null（與ref類似，區別在於條件中包含對NULL的查詢）。
- index_merge（索引合併優化）。
- unique_subquery（in的後面是一個查詢主鍵欄位的子查詢）。
- index_subquery（與unique_subquery類似，區別在於in的後面是查詢非唯一索引欄位的子查詢）。
- range（單表中的範圍查詢）。
- index（對於前面的每一行，都通過查詢索引來得到資料）。
- all（對於前面的每一行，都通過全表掃描來得到資料）。
possible_keys：表示查詢時，可能使用的索引。
key：表示實際使用的索引。
key_len：索引欄位的長度。
rows：掃描行的數量。
filtered：返回結果的行佔需要讀到的行(rows列的值)的百分比。
Extra：執行情況的說明和描述。
- Using index（此值表示mysql將使用覆蓋索引，以避免存取表）。
- Using where（mysql 將在儲存引擎檢索行後再進行過濾，許多where條件裡涉及索引中的列，當(並且如果)它讀取索引時，就能被儲存引擎檢驗，因此不是所有帶where子句的查詢都會顯示“Using where”。有時“Using where”的出現就是一個暗示：查詢可受益於不同的索引）。
- Using temporary（mysql 對查詢結果排序時會使用臨時表）。
- Using filesort（mysql會對結果使用一個外部索引排序，而不是按索引次序從表裡讀取行。mysql有兩種檔案排序演演算法，這兩種排序方式都可以在記憶體或者磁碟上完成，explain不會告訴你mysql將使用哪一種檔案排序，也不會告訴你排序會在記憶體裡還是磁碟上完成）。
- Range checked for each record(index map: N) （沒有好用的索引，新的索引將在聯接的每一行上重新估算，N是顯示在possible_keys列中索引的點陣圖，並且是冗餘的）。

5.確定問題並採取相應的優化措施

經過以上定位步驟，我們基本就可以分析到問題出現的原因。此時我們可以根據情況採取相應的改進措施，進行優化提高語句執行效率。
在上面的例子中，已經可以確認是goods_stock是走主鍵索引的，但是對goods_stock_price子表的進行了全表掃描導致效率的不理想，那麼應該對goods_stock_price表的GoodsStockID欄位建立索引，具體命令如下：

-- 建立索引
CREATE INDEX idx_stock_price_1 ON goods_stock_price (GoodsStockID);
-- 附加刪除跟查詢索引語句
ALTER TABLE goods_stock_price DROP INDEX idx_stock_price_1;
SHOW INDEX FROM goods_stock_price;

建立索引後，我們再看一下這條語句的執行計劃，具體如下：

EXPLAIN SELECT SUM(sp.Qty)
FROM goods_stock AS s LEFT JOIN goods_stock_price AS sp
ON s.ID=sp.GoodsStockID;

可以發現建立索引後對goods_stock_price子表需要掃描的行數明顯減少（從 3 行減少到1行），可見索引的使用可以大大提高資料庫的存取速度，尤其在表很龐大的時候這種優勢更為明顯。

到此這篇關於MySQL通過show status檢視、explain分析優化資料庫效能的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。