mysql性能分析–explain詳解和profiling開銷(資源分配)

mysql性能分析–explain詳解和profiling開銷(資源分配)

explain

id:select標識符。select查詢序列號,即sql語句執行的順序。當有子查詢時,id就會出現不一致,如子查詢sql的id是1,外查詢sql的id是2.
selecttype:select類型。
simple,標示簡單的select,沒有union和子查詢
primary,最外面的select,在有子查詢的語句中,最外面的select查詢就是primary
subquery,子查詢sql
union,在union語句的第二個或者說是後面那一個。
dependent union,union中的第二個或後面的select語句,取決於外面的查詢
union result,union的結果。
EXPLAIN SELECT * FROM table_name t1 WHERE t1.primary_key = (SELECT column_name FROM table_name t2 WHERE t2.primary_key = (SELECT 10 FROM table_name t3 LIMIT 1));
+—-+————-+——-+——-+—————+———+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———+———+——-+——+————-+
| 1 | PRIMARY | t1 | const | PRIMARY | PRIMARY | 8 | const | 1 | |
| 2 | SUBQUERY | t2 | const | PRIMARY | PRIMARY | 8 | | 1 | |
| 3 | SUBQUERY | t3 | index | NULL | PRIMARY | 8 | NULL | 853 | Using index |
+—-+————-+——-+——-+—————+———+———+——-+——+————-+
table:表名,顯示別名,沒有別名的顯示表名本身。
type:連接類型。
system,表隻有一行,這是const類型的特例,可以忽略不計。
const,方案最好最優化的一個類型。必須是用到瞭pk或unique且隻匹配一條記錄。const用於比較primary key或者unique索引。因為隻匹配一行數據,所以很快。
EXPLAIN SELECT * FROM table_name WHERE pk BETWEEN 3 AND 3;–const
EXPLAIN SELECT * FROM table_name WHERE pk BETWEEN 3 AND 4;–range
eq_ref,對於每個來自於前面的表的行組合,從該表中讀取一行。這可能是除const外的最好的鏈接類型。它用在一個索引的所有部分被連接使用並且索引是unique或pk。
如下,使用瞭t2的主鍵來關聯t1,所以t2表使用瞭eq_ref。
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.column_name = t2.pk;
+—-+————-+——-+——–+—————+———+———+—————————+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——–+—————+———+———+—————————+——+——-+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2644 | |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 8 | t1.column_name | 1 | |
+—-+————-+——-+——–+—————+———+———+—————————+——+——-+
ref,如果連接隻使用健的最左邊的前綴,或如果健不是unique或primary key(即連接不能基於關鍵字選擇單個行的話),則使用ref
ref_or_null,該聯接類型如同ref,但是添加瞭MySQL可以專門搜尋包含NULL值的行。在解決子查詢中經常使用該聯接類型的優化。
index_merge,該聯接類型表示使用瞭索引合並優化方法。在這種情況下,key列包含瞭使用的索引的清單,key_len包含瞭使用的索引的最長的關鍵元素
unique_subquery
index_subquery
range,給定范圍內的檢索,使用一個索引來檢查行。看下面兩條語句
explain select * from uchome_space where uid in (1,2)
explain select * from uchome_space where groupid in (1,2)
uid有索引,groupid沒有索引,結果是第一條語句的聯接類型是range,第二個是ALL
index, 該聯接類型與ALL相同,除瞭隻有索引樹被掃描。這通常比ALL快,因為索引文件通常比數據文件小。(也就是說雖然all和Index都是讀全表,但index是從索引中讀取的,而all是從硬盤中讀的)當查詢隻使用作為單索引一部分的列時,MySQL可以使用該聯接類型。
ALL,對於每個來自於先前的表的行組合,進行完整的表掃描。如果表是第一個沒標記const的表,這通常不好,並且通常在它情況下很差。通常可以增加更多的索引而不要使用ALL,使得行能基於前面的表中的常數值或列值被檢索出。
possible_keys:提示使用哪個索引會在該表中找到行
key:mysql使用的索引。顯示MySQL實際決定使用的健,如果沒有索引被選擇,健是NULL。
key_len:mysql使用的索引長度。如果健是NULL,長度就是NULL。
ref:ref列顯示使用哪個列或常數與key一起從表中選擇行
rows:顯示mysql執行查詢的行數,數值越大越不好,說明沒有用好索引。(InnoDB中不是很準確)
extra:mysql解決查詢的詳細信息。(如果想要使查詢盡可能快,應找出Using filesort 和Using temporary的Extra值。)
distinct
not exists
rang checked for each record,沒有找到合適的索引
using filesort
using index,值使用索引樹中的信息而不需要進一步搜尋讀取實際的行來檢索表中的信息。說明使用瞭索引
using temporary,出現using temporary就說明語句需要優化瞭。
EXPLAIN SELECT ads.id FROM ads, city WHERE city.city_id = 8005 AND ads.status = 'online' AND city.ads_id=ads.id ORDER BY ads.id desc
id select_type table type possible_keys key key_len ref rows filtered Extra
—— ———– —— —— ————– ——- ——- ——————– —— ——– ——————————-
1 SIMPLE city ref ads_id,city_id city_id 4 const 2838 100.00 Using temporary; Using filesort
1 SIMPLE ads eq_ref PRIMARY PRIMARY 4 city.ads_id 1 100.00 Using where
這條語句會使用using temporary,而下面這條語句則不會
EXPLAIN SELECT ads.id FROM ads, city WHERE city.city_id = 8005 AND ads.status = 'online' AND city.ads_id=ads.id ORDER BYcity.ads_id desc
id select_type table type possible_keys key key_len ref rows filtered Extra
—— ———– —— —— ————– ——- ——- ——————– —— ——– —————————
1 SIMPLE city ref ads_id,city_id city_id 4 const 2838 100.00 Using where; Using filesort
1 SIMPLE ads eq_ref PRIMARY PRIMARY 4 city.ads_id 1 100.00 Using where
這是為什麼呢?他倆之間隻是一個order by不同,MySQL 表關聯的算法是 Nest Loop Join,是通過驅動表的結果集作為循環基礎數據,然後一條一條地通過該結果集中的數據作為過濾條件到下一個表中查詢數據,然後合並結果。EXPLAIN 結果中,第一行出現的表就是驅動表(Important!)以上兩個查詢語句,驅動表都是 city,如上面的執行計劃所示!
對驅動表可以直接排序,對非驅動表(的字段排序)需要對循環查詢的合並結果(臨時表)進行排序(Important!)
因此,order by ads.id desc 時,就要先 using temporary 瞭!
驅動表的設定
wwh999 在 2006年總結說,當進行多表連接查詢時, [驅動表] 的設定為:
1)指定瞭聯接條件時,滿足查詢條件的記錄行數少的表為[驅動表];
2)未指定聯接條件時,行數少的表為[驅動表](Important!)。
永遠用小結果集驅動大結果集
今天學到瞭一個很重要的一點:當不確定是用哪種類型的join時,讓mysql優化器自動去判斷,我們隻需寫select * from t1,t2 where t1.field = t2.field
using where
Using sort_union(…), Using union(…),Using intersect(…)

Using index for group-by,類似於訪問表的Using index方式,Using index for group-by表示MySQL發現瞭一個索引,可以用來查詢GROUP BY或DISTINCT查詢的所有列,而不要額外搜尋硬盤訪問實際的表。並且,按最有效的方式使用索引,以便對於每個組,隻讀取少量索引條目。

profiling執行開銷

查看配置:

SELECT @@PROFILING; +————-+
| @@PROFILING |
+————-+
| 0 |
+————-+

SHOW VARIABLES LIKE '%profiling%'; +————————+——-+
| Variable_name | Value |
+————————+——-+
| have_profiling | YES | 是否支持profiling
| profiling | OFF |是否開啟瞭profiling
| profiling_history_size | 15 |保留最近執行的記錄數。0-100,默認15,0是禁用。
+————————+——-+

開啟及使用:

1)SET PROFILING = 1; 2)執行sql,如select * from table_name where … 3)SHOW PROFILES; +———-+————+—————————————–+
| query_id | duration | query |
+———-+————+—————————————–+
| 1 | 0.00705950 | show variables like '%profiling%' |
| 2 | 0.00127400 | select * from … |
+———-+————+—————————————–+
4)SHOW PROFILE;顯示最後一個執行記錄的開銷明細 5)SHOW PROFILE CPU,BLOCK IO;顯示執行時間,和cpu、block io的相親 6)SET PROFILING=0;

You May Also Like