1.概述

在應(yīng)用系統(tǒng)開(kāi)發(fā)過(guò)程中，由于初期數(shù)據(jù)量小，開(kāi)發(fā)人員寫(xiě)SQL語(yǔ)句時(shí)更重視功能上的實(shí)現(xiàn)，但是當(dāng)應(yīng)用系統(tǒng)正式上線后，隨著生產(chǎn)數(shù)據(jù)量的急劇增長(zhǎng)，很多SQL語(yǔ)句開(kāi)始逐漸顯露出性能問(wèn)題，對(duì)生產(chǎn)環(huán)境的影響也越來(lái)越大，此時(shí)這些有問(wèn)題的SQL語(yǔ)句就成為整個(gè)系統(tǒng)性能的瓶頸，因此我們必須要對(duì)它們進(jìn)行優(yōu)化，該章節(jié)將詳細(xì)介紹在MySQL中優(yōu)化SQL語(yǔ)句的方法。

2.通過(guò)show status命令了解各種SQL的執(zhí)行頻率

MySQL客戶端連接成功后，通過(guò)show [session|global]status命令可以提供服務(wù)器狀態(tài)信息，也可以在操作系統(tǒng)上使用mysqladmin extended-status命令獲得這些消息。show [session|global] status可以根據(jù)需要加上參數(shù)“session”或者“global”來(lái)顯示session級(jí)（當(dāng)前連接）的統(tǒng)計(jì)結(jié)果和global級(jí)（自數(shù)據(jù)庫(kù)上次啟動(dòng)至今）的統(tǒng)計(jì)結(jié)果。如果不寫(xiě)，默認(rèn)使用參數(shù)是“session”。

下面的命令顯示了當(dāng)前session中所有統(tǒng)計(jì)參數(shù)的值：

-- 查看會(huì)話所有統(tǒng)計(jì)的值
SHOW STATUS LIKE 'Com_%';
Or
SHOW SESSION STATUS LIKE 'Com_%';

下面的命令顯示了當(dāng)前global中所有統(tǒng)計(jì)參數(shù)的值：

-- 查看全局所有統(tǒng)計(jì)的值

SHOW GLOBAL STATUS LIKE 'Com_%';

Com_xxx表示每個(gè)xxx語(yǔ)句執(zhí)行的次數(shù)，我們通常比較關(guān)心的是以下幾個(gè)統(tǒng)計(jì)參數(shù)：

• Com_select：執(zhí)行SELECT操作的次數(shù)，一次查詢只累加1。
• Com_insert：執(zhí)行INSERT操作的次數(shù)，對(duì)于批量插入的INSERT操作，只累加一次。
• Com_update：執(zhí)行UPDATE操作的次數(shù)。
• Com_delete：執(zhí)行DELETE操作的次數(shù)。

上面這些參數(shù)對(duì)于所有存儲(chǔ)引擎的表操作都會(huì)進(jìn)行累計(jì)。下面這幾個(gè)參數(shù)只是針對(duì)InnoDB存儲(chǔ)引擎的，累加的算法也略有不同。

• Innodb_rows_read：SELECT查詢返回的行數(shù)。
• Innodb_rows_inserted：執(zhí)行INSERT操作插入的行數(shù)。
• Innodb_rows_updated：執(zhí)行UPDATE操作更新的行數(shù)。
• Innodb_rows_deleted：執(zhí)行DELETE操作刪除的行數(shù)。

通過(guò)以上幾個(gè)參數(shù)，可以很容易地了解當(dāng)前數(shù)據(jù)庫(kù)的應(yīng)用系統(tǒng)是以插入更新為主還是以查詢操作為主，以及各種類型的SQL大致的執(zhí)行比例是多少。對(duì)于更新操作的計(jì)數(shù)，是對(duì)執(zhí)行次數(shù)的計(jì)數(shù)，不論提交還是回滾都會(huì)進(jìn)行累加。

對(duì)于事務(wù)型的應(yīng)用，通過(guò)Com_commit和Com_rollback可以了解事務(wù)提交和回滾的情況，對(duì)于回滾操作非常頻繁的數(shù)據(jù)庫(kù)，可能意味著應(yīng)用編寫(xiě)存在問(wèn)題。此外，以下幾個(gè)參數(shù)便于用戶了解數(shù)據(jù)庫(kù)的基本情況。

• Connections：試圖連接MySQL服務(wù)器的次數(shù)。
• Uptime：服務(wù)器工作時(shí)間。
• Slow_queries：慢查詢的次數(shù)。

3.定位執(zhí)行效率較低的SQL語(yǔ)句

可以通過(guò)以下兩種方式定位執(zhí)行效率較低的SQL語(yǔ)句。

• 通過(guò)慢查詢?nèi)罩径ㄎ荒切﹫?zhí)行效率較低的SQL語(yǔ)句，用--log-slow-queries[=file_name]選項(xiàng)啟動(dòng)時(shí)，mysqld寫(xiě)一個(gè)包含所有執(zhí)行時(shí)間超過(guò)long_query_time秒的SQL語(yǔ)句的日志文件。
• 慢查詢?nèi)罩驹诓樵兘Y(jié)束以后才紀(jì)錄，所以在應(yīng)用系統(tǒng)反映執(zhí)行效率出現(xiàn)問(wèn)題的時(shí)候查詢慢查詢?nèi)罩静⒉荒芏ㄎ粏?wèn)題，可以使用show processlist命令查看當(dāng)前MySQL在進(jìn)行的線程，包括線程的狀態(tài)、是否鎖表等，可以實(shí)時(shí)地查看SQL的執(zhí)行情況，同時(shí)對(duì)一些鎖表操作進(jìn)行優(yōu)化。

4.通過(guò)EXPLAIN分析低效SQL的執(zhí)行計(jì)劃

通過(guò)定位執(zhí)行效率較低的SQL語(yǔ)句后，可以通過(guò)EXPLAIN或者DESC命令獲取MySQL如何執(zhí)行SELECT語(yǔ)句的信息，包括在SELECT語(yǔ)句執(zhí)行過(guò)程中表如何連接和連接的順序，比如想統(tǒng)計(jì)所有庫(kù)存階梯數(shù)量，需要關(guān)聯(lián)goods_stock表和goods_stock_price表，并且對(duì)goods_stock_price.Qty字段做求和（sum）操作，相應(yīng) SQL 的執(zhí)行計(jì)劃如下：

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

如上圖所示每個(gè)列的簡(jiǎn)單解釋如下：

• select_type：表示 SELECT 的類型，常見(jiàn)的取值有：
  • SIMPLE（簡(jiǎn)單表，即不使用表連接 或者子查詢）。
  • PRIMARY（主查詢，即外層的查詢）、UNION（UNION 中的第二個(gè)或 者后面的查詢語(yǔ)句）、◎SUBQUERY（子查詢中的第一個(gè)SELECT）等。
• table：輸出結(jié)果集的表。
• type：表示表的連接類型，性能由好到差的連接類型為：
  • system（表中僅有一行，即常量表）。
  • const（單表中最多有一個(gè)匹配行，例如primary key或者unique index）。
  • eq_ref（對(duì)于前面的每一行，在此表中只查詢一條記錄，簡(jiǎn)單來(lái)說(shuō)，就是多表連接中使用primary key或者unique index）。
  • ref（與eq_ref類似，區(qū)別在于不是使用primary key或者unique index，而是使用普通的索引）。
  • ref_or_null（與ref類似，區(qū)別在于條件中包含對(duì)NULL的查詢）。
  • index_merge（索引合并優(yōu)化）。
  • unique_subquery（in的后面是一個(gè)查詢主鍵字段的子查詢）。
  • index_subquery（與unique_subquery類似，區(qū)別在于in的后面是查詢非唯一索引字段的子查詢）。
  • range（單表中的范圍查詢）。
  • index（對(duì)于前面的每一行，都通過(guò)查詢索引來(lái)得到數(shù)據(jù)）。
  • all（對(duì)于前面的每一行，都通過(guò)全表掃描來(lái)得到數(shù)據(jù)）。
• possible_keys：表示查詢時(shí)，可能使用的索引。
• key：表示實(shí)際使用的索引。
• key_len：索引字段的長(zhǎng)度。
• rows：掃描行的數(shù)量。
• filtered：返回結(jié)果的行占需要讀到的行(rows列的值)的百分比。
• Extra：執(zhí)行情況的說(shuō)明和描述。
  • Using index（此值表示mysql將使用覆蓋索引，以避免訪問(wèn)表）。
  • Using where（mysql 將在存儲(chǔ)引擎檢索行后再進(jìn)行過(guò)濾，許多where條件里涉及索引中的列，當(dāng)(并且如果)它讀取索引時(shí)，就能被存儲(chǔ)引擎檢驗(yàn)，因此不是所有帶where子句的查詢都會(huì)顯示“Using where”。有時(shí)“Using where”的出現(xiàn)就是一個(gè)暗示：查詢可受益于不同的索引）。
  • Using temporary（mysql 對(duì)查詢結(jié)果排序時(shí)會(huì)使用臨時(shí)表）。
  • Using filesort（mysql會(huì)對(duì)結(jié)果使用一個(gè)外部索引排序，而不是按索引次序從表里讀取行。mysql有兩種文件排序算法，這兩種排序方式都可以在內(nèi)存或者磁盤上完成，explain不會(huì)告訴你mysql將使用哪一種文件排序，也不會(huì)告訴你排序會(huì)在內(nèi)存里還是磁盤上完成）。
  • Range checked for each record(index map: N) （沒(méi)有好用的索引，新的索引將在聯(lián)接的每一行上重新估算，N是顯示在possible_keys列中索引的位圖，并且是冗余的）。

5.確定問(wèn)題并采取相應(yīng)的優(yōu)化措施

經(jīng)過(guò)以上定位步驟，我們基本就可以分析到問(wèn)題出現(xiàn)的原因。此時(shí)我們可以根據(jù)情況采取相應(yīng)的改進(jìn)措施，進(jìn)行優(yōu)化提高語(yǔ)句執(zhí)行效率。
在上面的例子中，已經(jīng)可以確認(rèn)是goods_stock是走主鍵索引的，但是對(duì)goods_stock_price子表的進(jìn)行了全表掃描導(dǎo)致效率的不理想，那么應(yīng)該對(duì)goods_stock_price表的GoodsStockID字段創(chuàng)建索引，具體命令如下：

-- 創(chuàng)建索引
CREATE INDEX idx_stock_price_1 ON goods_stock_price (GoodsStockID);
-- 附加刪除跟查詢索引語(yǔ)句
ALTER TABLE goods_stock_price DROP INDEX idx_stock_price_1;
SHOW INDEX FROM goods_stock_price;

創(chuàng)建索引后，我們?cè)倏匆幌逻@條語(yǔ)句的執(zhí)行計(jì)劃，具體如下：

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

可以發(fā)現(xiàn)建立索引后對(duì)goods_stock_price子表需要掃描的行數(shù)明顯減少（從 3 行減少到1行），可見(jiàn)索引的使用可以大大提高數(shù)據(jù)庫(kù)的訪問(wèn)速度，尤其在表很龐大的時(shí)候這種優(yōu)勢(shì)更為明顯。

到此這篇關(guān)于MySQL通過(guò)show status查看、explain分析優(yōu)化數(shù)據(jù)庫(kù)性能的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評(píng)論