第一節(jié) 統(tǒng)計SQL執(zhí)行成本

統(tǒng)計SQL的查詢成本: last_query_cost
一條SQL查詢語句在執(zhí)行前需要確定查詢執(zhí)行計劃，如果存在多種執(zhí)行計劃的話，MySQL會計算每個執(zhí)行計劃所需要的成本，從中選擇成本最小的一個作為最終執(zhí)行的執(zhí)行計劃。
如果我們想要查看某條SQL語句的查詢成本，可以在執(zhí)行完這條SQL語句之后，通過查看當(dāng)前會話中的last_query_cost變量值來得到當(dāng)前查詢的成本。它通常也是我們評價一個查詢的執(zhí)行效率的一個常用指標(biāo)。這個查詢成本對應(yīng)的是SQL語句所需要讀取的頁的數(shù)量。
SQL查詢是一個動態(tài)的過程，從頁加載的角度來看，我們可以得到以下兩點結(jié)論:

位置決定效率。如果頁就在數(shù)據(jù)庫緩沖池中，那么效率是最高的，否則還需要從磁盤中進(jìn)行讀取，當(dāng)然針對單個頁的讀取來說，如果頁存在于內(nèi)存中，會比在磁盤中讀取效率高很多。

批量決定效率。如果我們從磁盤中對單一頁進(jìn)行隨機讀，那么效率是很低的(差不多10ms)，而采用順序讀取的方式，批量對頁進(jìn)行讀取，平均一頁的讀取效率就會提升很多，甚至要快于單個頁面在內(nèi)存中的隨機讀取。
所以說，遇到I/O并不用擔(dān)心，方法找對了，效率還是很高的。我們首先要考慮數(shù)據(jù)存放的位置，如果是經(jīng)常使用的數(shù)據(jù)就要盡量放到緩沖池中，其次我們可以充分利用磁盤的吞吐能力，一次性批量讀取數(shù)據(jù)，這樣單個頁的讀取效率也就得到了提升。

第二節(jié) 分析查詢語句：EXPLAIN

2.1 基本語法

如果我們想看看某個查詢的執(zhí)行計劃的話，可以在具體的查詢語句前邊加一個 EXPLAIN ，就像這樣：

EXPLAIN SELECT 1;

EXPLAIN 語句輸出的各個列的作用如下：

列名描述

id	在一個大的查詢語句中每個SELECT關(guān)鍵字都對應(yīng)一個 唯一的id
select_type	SELECT關(guān)鍵字對應(yīng)的那個查詢的類型
table	表名
partitions	匹配的分區(qū)信息
type☆	針對單表的訪問方法
possible_keys	可能用到的索引
key	實際上使用的索引
key_len ☆	實際使用到的索引長度
ref	當(dāng)使用索引列等值查詢時，與索引列進(jìn)行等值匹配的對象信息
rows ☆	預(yù)估的需要讀取的記錄條數(shù)
filtered	某個表經(jīng)過搜索條件過濾后剩余記錄條數(shù)的百分比
Extra ☆	一些額外的信息

2.2 詳細(xì)解釋

為了讓大家有比較好的體驗，下面調(diào)整了下 EXPLAIN 輸出列的順序。

2.2.1 table

不論我們的查詢語句有多復(fù)雜，里邊兒包含了多少個表 ，到最后也是需要對每個表進(jìn)行 單表訪問的，所以MySQL規(guī)定EXPLAIN語句輸出的每條記錄都對應(yīng)著某個單表的訪問方法，該條記錄的table列代表著該表的表名（有時不是真實的表名字，可能是簡稱）。

2.2.2 id

我們寫的查詢語句一般都以 SELECT 關(guān)鍵字開頭，比較簡單的查詢語句里只有一個 SELECT 關(guān)鍵字，比如下邊這個查詢語句：

SELECT * FROM s1 WHERE key1 = 'a'; mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a’;

稍微復(fù)雜一點的連接查詢中也只有一個 SELECT 關(guān)鍵字，比如：

SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a'; mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field= 'a');

查看mysql優(yōu)化后的sql：

EXPLAIN EXTENDED SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field= 'a'); SHOW WARNINGS;

小結(jié):
id如果相同，可以認(rèn)為是一組，從上往下順序執(zhí)行
在所有組中，id值越大，優(yōu)先級越高，越先執(zhí)行
關(guān)注點：id號每個號碼，表示一次獨立的查詢, 一個sql的查詢次數(shù)越少越好

2.2.3 select_type

名稱描述

SIMPLE	簡單查詢(不使用UNION或子查詢)
PRIMARY	最外層的查詢
UNION	UNION中第二個語句或后面的語句
UNION RESULT	UNION每個結(jié)果集的取出來后，進(jìn)行合并操作
dependentSubquery	子查詢中的第一個 SELECT，依賴了外面的查詢
derived	派生表，子查詢在 FROM子句中

SIMPLE
SIMPLE，最簡單的查詢方式。

EXPLAIN SELECT * FROM `sys_user`

primary、union、union result
PRIMARY，主查詢。
UNION，UNION中第二個語句或后面的語句。
UNION RESULT，UNION每個結(jié)果集的取出來后，進(jìn)行合并操作。

EXPLAIN select * from sys_user where user_id=1 union select * from sys_user where user_id=2

Subquery
子查詢：#如果包含子查詢的查詢語句不能夠轉(zhuǎn)為對應(yīng)的semi-join(多表連接)的形式，并且該子查詢是不相關(guān)子查詢。#該子查詢的第一個SELECT'關(guān)鍵字代表的那個查詢的select type '就是SUBQUERY

EXPLAIN SELECT* FROMcoupon_give_gift WHEREcoupon_id = ( SELECT coupon_id FROM coupon WHERE id = '111’ );

EXPLAIN SELECT* FROMcoupon_give_gift WHEREcoupon_id in ( SELECT coupon_id FROM coupon WHERE id = '111’ );

查詢優(yōu)化器把in換成關(guān)聯(lián)查詢

dependentSubquery
DEPENDENT SUBQUERY，子查詢中內(nèi)層的第一個SELECT，依賴于外部查詢的結(jié)果集。

EXPLAIN SELECT* FROMcoupon_give_gift WHEREcoupon_id = (SELECT sub_title FROM coupon WHERE coupon_give_gift.coupon_id = coupon.coupon_id );

derived
DERIVED，派生表，子查詢在 FROM子句中。

explain select * from ( select user_id,count(*) from sys_user group by user_id ) user1

2.2.4 partitions

EXPLAIN SELECT * FROM user_partitions WHERE id>200;

查詢id大于200（200>100，p1分區(qū)）的記錄，查看執(zhí)行計劃，partitions是p1，符合我們的分區(qū)規(guī)則。

2.2.5 type

完整的訪問方法如下： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ，index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。

system : 該表只有一行（相當(dāng)于系統(tǒng)表），system是const類型的特例
const：當(dāng)我們根據(jù)主鍵或者唯一二級索引列與常數(shù)進(jìn)行等值匹配時，對單表的訪問方法就是`const ’

mysql> EXPLAIN SELECT * FROM interests where interests_id = 'CRDB951AF8FE05641140FA8FC6638A2B62D20210113016’;

eq_ref：#在連接查詢時，如果被驅(qū)動表是通過主鍵或者唯一二級索引列等值匹配的方式進(jìn)行訪問的（如果該主鍵或者唯一二級索引是聯(lián)合索引的話，所有的索引列都必須進(jìn)行等值比較），則對該被驅(qū)動表的訪問方法就是eq _ref。

EXPLAIN SELECT * FROM coupon c LEFT JOIN coupon_put_rule cpr on c.coupon_id = cpr.id;

ref：當(dāng)通過普通的二級索引列與常量進(jìn)行等值匹配時來查詢某個表，那么對該表的訪問方法就可能是`ref’，聯(lián)合索引時只匹配部分字段也為ref。

EXPLAIN SELECT * FROM coupon_store where coupon_id = 'C5BE9A420C9C26F07580DA1EB13D6A28' and store_id = '3570012’;

ref_or_null：當(dāng)對普通二級索引進(jìn)行等值匹配查詢，該索引列的值也可以是NULL值時，那么對該表的訪問方法就是‘ref or null`

EXPLAIN SELECT * FROM coupon where promotion_id = 'C5BE9A420C9C26F07580DA1EB13D6A28' or promotion_id is null;

index_merge：此類型表示使用了索引合并優(yōu)化，表示一個查詢里面用到了多個索引

EXPLAIN SELECT * FROM coupon where promotion_id = 'C5BE9A420C9C26F07580DD6A28' or coupon_id = 'C5BE9A42DA1EB13D6A8';

unique subquery ：是針對在一些包含’IN子查詢的查詢語句中，如果查詢優(yōu)化器決定將IN子查詢轉(zhuǎn)換為EXISTS子查詢，而且子查詢可以使用到主鍵進(jìn)行等值匹配的話，那么該子查詢執(zhí)行計劃的 type '列的值就是unique_subquery

EXPLAIN SELECT* FROMcoupon_give_gift WHEREcoupon_id in (SELECT id FROM coupon WHERE coupon_give_gift.coupon_id = coupon.coupon_id ) or give_quantity = 1;

range：表示使用了范圍類型的查詢，一般出現(xiàn)在使用了<>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, 貨 IN() 之類的字句和常量值作比較時

EXPLAIN SELECT * FROM coupon where coupon_id in ('111','222','333','444')

index:當(dāng)我們可以使用索引覆蓋，但需要掃描全部的索引記錄時，該表的訪問方法就是index。

EXPLAIN SELECT coupon_id FROM coupon_give_gift where give_coupon_id = '111';

ALL：全表掃描。
上述分析的過程為從上至下，效率一次降低。

2.2.6 possible_keys和key

在EXPLAIN語句輸出的執(zhí)行計劃中，possible_keys列表示在某個查詢語句中，對某個表執(zhí)行單表查詢時可能用到的索引有哪些。一般查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用。key列表示實際用到的索引有哪些，如果為NULL，則沒有使用索引。比方說下邊這個查詢:

EXPLAIN SELECT coupon_id FROM coupon WHERE coupon_id = '111' and promotion_id = '111’;

EXPLAIN SELECT coupon_id FROM coupon WHERE coupon_id = '111’or promotion_id = '111';

2.2.7 key_len

實際使用到的索引長度(即:字節(jié)數(shù))#幫你檢查是否充分的利用上了索引，同一個索引`值越大越好’，主要針對聯(lián)合索引。

列類型KEY_LEN備注

id int	key_len = 4+1	int為4bytes,允許為NULL,加1byte（1字節(jié)用來記錄是否為null）
id bigint not null	key_len=8	bigint為8bytes
user char(30) utf8	key_len=30*3+1	utf8每個字符為3bytes,允許為NULL,加1byte
user varchar(30) not null utf8	key_len=30*3+2	utf8每個字符為3bytes,變長數(shù)據(jù)類型,加2bytes（2字節(jié)用來記錄實際長度）
user varchar(30) utf8	key_len=30*3+2+1	utf8每個字符為3bytes,允許為NULL,加1byte,變長數(shù)據(jù)類型,加2bytes

EXPLAIN SELECT * FROM s1 WHERE id = 1;EXPLAIN SELECT * FROM s1 WHERE key2 = 2;EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';

2.2.8 ref

當(dāng)使用索引列等值查詢時，與索引列進(jìn)行等值匹配的對象信息。比如只是一個常數(shù)或者是某個列。

EXPLAIN SELECT coupon_id FROM coupon WHERE coupon_id = '111';

EXPLAIN SELECT* FROMcoupon inner JOINcoupon_give_gift on coupon_give_gift.coupon_id = coupon.coupon_id;

2.2.9 rows

預(yù)估的需要讀取的記錄條數(shù)，值越小越好。

EXPLAIN SELECT * FROM coupon WHERE id < 100;

2.2.10 filtered

某個表經(jīng)過搜索條件過濾后剩余記錄條數(shù)的百分比。如果使用的是索引執(zhí)行的單表掃描，那么計算時需要估計出滿足除使用到對應(yīng)索引的搜索條件外的其他搜索條件的記錄有多少條。

EXPLAIN SELECT * FROM coupon WHERE id < 100 and campaign_coupon_code = '202007071120';

2.2.11 Extra

顧名思義，Extra列是用來說明一些額外信息的，包含不適合在其他列中顯示但十分重要的額外信息。我們可以通過這些額外信息來更準(zhǔn)確的理解MySQL到底將如何執(zhí)行給定的查詢語句。MySQL提供的額外信息有好幾十個，我們就不一個一個介紹了，所以只挑比較重要的額外信息介紹給大家。

No tables used 當(dāng)查詢語句的沒有FROM子句時將會提示該額外信息。

EXPLAIN SELECT 1;

2. Impossible where 查詢語句的WHERE子句永遠(yuǎn)為FALSE時將會提示該額外信息

EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

3. Using where 當(dāng)我們使用全表掃描來執(zhí)行對某個表的查詢，并且該語句的where 子句中有針對該表的搜索條件時，在Extra列中會提示上述額外信息。當(dāng)使用索引訪問來執(zhí)行對某個表的查詢，并且該語句的WHERE子句中有除了該索引包含的列之外的其他搜索條件時，在`Extra '列中也會提示上述額外信息。

EXPLAIN SELECT * FROM coupon WHERE campaign_coupon_code = '111’; EXPLAIN SELECT * FROM coupon WHERE coupon_id = '111' and campaign_coupon_code = '111’;

4. Using index 當(dāng)我們查詢列表以及搜索條件中只包含屬于某個索引的列，也就是在可以使用覆蓋索引的情況下，在Extra列將會提示該額外信息。比方說下邊這個查詢中只需要用到‘coupon_id’而不需要回表操作:

EXPLAIN SELECT coupon_id FROM coupon WHERE coupon_id = '111’;

5. Using index condition 搜索條件中出現(xiàn)了索引列，按最左匹配原則無法匹配上，但是用到了索引下推。

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part3 = 'b';

6. Using join buffer 在連接查詢執(zhí)行過程中，當(dāng)被驅(qū)動表不能有效的利用索引加快訪問速度，MySQL一般會為其分配一塊名叫`join buffer '的內(nèi)存塊來加快查詢速度，也就是我們所講的‘基于塊的嵌套循環(huán)算法’

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;

7. Using union 表示需要進(jìn)行索引合并的索引名稱，如果出現(xiàn)了’Using union(…)提示，說明準(zhǔn)備使用union索引合并的方式執(zhí)行查詢; 出現(xiàn)了Using sort union(…)提示，說明準(zhǔn)備使用’sort-Union `索引合并的方式執(zhí)行查詢。

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' or key3 = 'a';

Using filesort 很多情況下排序操作無法使用到索引，只能在內(nèi)存中（記錄較少的時候）或者磁盤中（記錄較多的時候)進(jìn)行排序，Mysql把這種在內(nèi)存中或者磁盤上進(jìn)行排序的方式統(tǒng)稱為文件排序（英文名: ‘filesort’)。如果某個查詢需要使用文件排序的方式執(zhí)行查詢，就會在執(zhí)行計劃的Extra列中顯示’Using filesort `提示。

EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

Using temporary 在許多查詢的執(zhí)行過程中，MySQL可能會借助臨時表來完成一些功能，比如去重、排序之類的，比如我們在執(zhí)行許多包含DISTINCT '、GROUP BY'、‘UNION等子句的查詢過程中，如果不能有效利用索引來完成查詢，MysQL很有可能尋求通過建立內(nèi)部的臨時表來執(zhí)行查詢。如果查詢中使用到了內(nèi)部的臨時表，在執(zhí)行#計劃的‘Extra列將會顯示Using temporary`提示。

EXPLAIN SELECT DISTINCT common_field FROM s1;

2.3 擴展

2.3.1 最左匹配原則

最左優(yōu)先，以最左邊的為起點任何連續(xù)的索引都能匹配上。同時遇到范圍查詢(>、<、between、like)就會停止匹配。
為什么最左匹配：是因為mysql創(chuàng)建聯(lián)合索引時，首先會對最左邊字段排序，也就是第一個字段，然后再在保證第一個字段有序的情況下，再排序第二個字段，以此類推。所以聯(lián)合索引最左列是絕對有序的，其他字段無序。舉個例子：可以把聯(lián)合索引看成“通訊錄”，姓名作為聯(lián)合索引，姓是第一列，名是第二列，當(dāng)查找人名時，是先確定這個人姓再根據(jù)名確定人。只有名沒有姓就查不到。

2.3.2 索引下推

什么是索引下推
索引下推(Index Condition Pushdown，簡稱ICP)，是MySQL5.6版本的新特性，它能減少回表查詢次數(shù)，提高查詢效率。MySQL服務(wù)層負(fù)責(zé)SQL語法解析、生成執(zhí)行計劃等，并調(diào)用存儲引擎層去執(zhí)行數(shù)據(jù)的存儲和檢索。索引下推的下推其實就是指將部分上層（服務(wù)層）負(fù)責(zé)的事情，交給了下層（引擎層）去處理。

索引下推優(yōu)化的原理
先來看一下MySQL的大致框架

不使用ICP索引掃描的過程


如上圖所示：
storage層：只將滿足index key條件的索引記錄對應(yīng)的整行記錄取出，返回給server層
server 層：對返回的數(shù)據(jù)，使用后面的where條件過濾，直至返回最后一行。

使用ICP掃碼的過程

如圖所示：
storage層：首先將index key條件滿足的索引記錄區(qū)間確定，然后在索引上使用index filter進(jìn)行過 濾。將滿足的index filter條件的索引記錄才去回表取出整行記錄返回server層。不滿足index filter條件的索引記錄丟棄，不回表、也不會返回server層。
server 層：對返回的數(shù)據(jù)，使用table filter條件做最后的過濾。

使用前后的成本差別
使用ICP前，存儲層多返回了需要被index filter過濾掉的整行記錄。使用ICP后，直接就去掉了不滿足index filter條件的記錄，省去了他們回表和傳遞到server層的成本。ICP的 加速效果 取決于在存儲引擎內(nèi)通過 ICP篩選 掉的數(shù)據(jù)的比例。

使用ICP條件
① 只能用于二級索引(secondary index)
② explain顯示的執(zhí)行計劃中type值（join 類型）為 range 、ref 、eq_ref 或者 ref_or_null 。
③ 并非全部where條件都可以用ICP篩選，如果where條件的字段不在索引列中，還是要讀取整表的記錄到server端做where過濾。
④ ICP可以用于MyISAM和InnnoDB存儲引擎
⑤ MySQL 5.6版本的不支持分區(qū)表的ICP功能，5.7版本的開始支持。
⑥ 當(dāng)SQL使用覆蓋索引時，不支持ICP優(yōu)化方法

2.3.2 前綴索引

什么是前綴索引？
前綴索引也叫局部索引，比如給身份證的前 10 位添加索引，類似這種給某列部分信息添加索引的方式叫做前綴索引。
為什么要用前綴索引？
前綴索引能有效減小索引文件的大小，讓每個索引頁可以保存更多的索引值，從而提高了索引查詢的速度。但前綴索引也有它的缺點，不能在 order by 或者 group by 中觸發(fā)前綴索引，也不能把它們用于覆蓋索引。
索引選擇性是什么？
　　索引的選擇性，指的是不重復(fù)的索引值（基數(shù)）和表記錄數(shù)的比值。選擇性是索引篩選能力的一個指標(biāo)。索引的取值范圍是 0—1 ，當(dāng)選擇性越大，索引價值也就越大。
　　舉例說明：假如有一張表格，總共有一萬行的記錄，其中有一個性別列sex，這個列的包含選項就兩個：男/女。那么，這個時候，這一列創(chuàng)建索引的話，索引的選擇性為萬分之二，這時候，在性別這一列創(chuàng)建索引是沒有啥意義的。假設(shè)個極端情況，列內(nèi)的數(shù)據(jù)都是女，那么索引的選擇性為萬分之一，其效率還不如直接進(jìn)行全表掃描。如果是主鍵索引的話，那么選擇性為1，索引價值比較大。可以直接根據(jù)索引定位到數(shù)據(jù)。
什么情況下適合使用前綴索引？
當(dāng)字符串本身可能比較長，而且前幾個字符就開始不相同，適合使用前綴索引；相反情況下不適合使用前綴索引，比如，整個字段的長度為 20，索引選擇性為 0.9，而我們對前 10 個字符建立前綴索引其選擇性也只有 0.5，那么我們需要繼續(xù)加大前綴字符的長度，但是這個時候前綴索引的優(yōu)勢已經(jīng)不明顯，就沒有創(chuàng)建前綴索引的必要了。
索引選擇性怎么計算？
首先我們可以通過如下 SQL 得到全列選擇性：

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

例如：

SELECT COUNT(DISTINCT coupon_id) / COUNT(*) FROM coupon;

然后再通過如下 SQL 得到某一長度前綴的選擇性：

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

例如：

SELECT COUNT(DISTINCT LEFT(coupon_id, 6)) / COUNT(*) FROM coupon; SELECT COUNT(DISTINCT LEFT(coupon_id, 8)) / COUNT(*) FROM coupon;

總結(jié)

以上是生活随笔為你收集整理的MySQL性能分析工具的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。