歡迎您光臨本站 註冊首頁

MySQL explain獲取查詢指令信息原理及實例

←手機掃碼閱讀     techdo @ 2020-06-04 , reply:0

explain用於獲取查詢執行計劃信息,

一、語法

只需要在select前加上explain即可,如:

 mysql> explain select 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

二、explain中的信息

1、id:表示SELECT所屬的行。id越大,則執行順序越高,id相同時,從上到下執行。

2、select_type:顯示對應行是簡單還是複雜查詢

1)SIMPLE:簡單查詢,意味著不包含子查詢和UNION

2)SUBQUERY:表示是個子查詢

3)DERIVED:用來表示包含在FORM子句的子查詢中的SELECT

4)UNION:

3、table:表示訪問哪個表

4、partitions:訪問分區

5、type:關聯類型,就是如何查找表中的行。

1)ALL:全表掃描。為了查找數據必須從頭帶尾的掃描所有的數據(limit關鍵字不會掃描所有數據)

2)index:索引掃描。這個跟全表掃描一樣,只是掃描表時按索引次序而不是行,主要優點是避免了排序,最大缺點是承擔按索引次序讀取整個表的開銷。

3)range:範圍掃描。就是一個有限制的索引掃描,它開始於索引的某一點,不用遍歷全部索引。

4)ref:索引訪問。它返回所有匹配某個單值的行。只有使用非唯一性所有或者唯一性所有的非唯一性前綴時才會發生。

5)eq_ref:使用這個索引查找,最多返回一條記錄,如果主鍵索引和唯一性索引。

6)const,system:當MySQL能對查詢的某一部分進行優化並將其轉換成一個常量時,就會使用這些訪問類型

6、possible_keys:顯示查詢可以用哪些所有

7、key:MySQL決定採用哪個索引來優化這個該表的訪問,如果這個索引沒有出現在possible_keys中,它可能選擇了一個覆蓋索引,如果沒有使用索引,這個值為NULL

8 、key_len:索引的字節數,越短越好。一般來說key_len等於索引列字段類型長度,如int是4字節,bigint是8字節,date是3個字節,datetime是8個字節;如果索引列是字符串類型,則需要考慮他的字符集,utf8每個字符佔3個字段,可變類型(varchar)額外需要2個字節;如果索引列可為空,則額外需要1個字段。

9、ref:

10、rows:MySQL估計為了找到所需的行而要讀取的行

11、filtered:返回結果的行數佔讀取行數的百分比(估算),值越大越好

12、Extra:顯示不適合其他列但也重要的信息,常見得值有:

1)Using index:表示使用覆蓋索引,以避免訪問表

2)Using where:MySQL服務器將在存儲引擎檢索行後再進行過濾

3)Using temporary:表示MySQL對查詢結果排序時會使用一個臨時表。

三、示例

示例1:

 mysql> explain select * from bd_dept; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | bd_dept | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+

可以看出該語句進行了全表掃描,沒有用到索引

示例2:

 mysql> explain select * from bd_dept where id=1; +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | bd_dept | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

該語句用到了主鍵索引,只需掃描一條記錄就可以得到結果,int類型佔4個字節,所以ken_len=4。

示例3:

 mysql> explain select * from bd_dept where dept_code='01'; +----+-------------+---------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | bd_dept | NULL | const | dept_code | dept_code | 32 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+

dept_code是一個唯一性索引字段,字段類型為varchar(10),不為空,所以索引長度為10*3+2=33。

示例4:

 mysql> explain select * from bd_dept where create_date>'2020-04-29'; +----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | bd_dept | NULL | range | create_date | create_date | 4 | NULL | 1 | 100.00 | Using index condition | +----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+

create_date是date類型,普通索引字段,可為空,查詢條件為大於,所以關聯類型為range,索引長度為3+1=4。

示例5:

 mysql> explain select a.id, a.dept_name, b.dept_name parent_name from bd_dept a inner join bd_dept b on a.id=b.parent_id; +----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+ | 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where | | 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | zhi_test.b.parent_id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+

可以看出MySQL先執行一個全表掃描,再通過主鍵進行關聯


[techdo ] MySQL explain獲取查詢指令信息原理及實例已經有231次圍觀

http://coctec.com/docs/mysql/show-post-236957.html