澳门四虎,久久免费视频在线观看30,亚洲精品久久久中文字

Explain 結果解讀與實踐

基于 MySQL 5.0.67 ，存儲引擎 MyISAM 。

注：單獨一行的"%%"及"`"表示分隔內容，就象分開“第一章”“第二章”。

explain 可以分析 select 語句的執行，即 MySQL 的“執行計劃”：

mysql> explain select 1;

用"\G"代替分號可得到豎排的格式：

mysql> explain select 1\G
*************************** 1
?????????? id: 1
? select_type: SIMPLE
??????? table: NULL
???????? type: NULL
possible_keys: NULL
????????? key: NULL
????? key_len: NULL
????????? ref: NULL
???????? rows: NULL
??????? Extra: No tables used

? ?

可以用 desc 代替?explain?：

desc select 1;

--第一章

id 列

建表：

create table a(a_id int);

create table b(b_id int);

create table c(c_id int);

mysql> explain select * from a join b on a_id=b_id where b_id in (select c_id from c);
+----+--------------------+-------+...
| id | select_type??????? | table |...
+----+--------------------+-------+...
|? 1 | PRIMARY??????????? | a???? |...
|? 1 | PRIMARY??????????? | b???? |...
|? 2 | DEPENDENT SUBQUERY | c???? |...
+----+--------------------+-------+...

從 3 個表中查詢，對應輸出 3 行，每行對應一個表， id 列表示執行順序，id 越大，越先執行，id 相同，由上至下執行。此處的執行順序為（以 table 列表示）：c -> a -> b

--第二章

select_type 列

MySQL 把 SELECT 查詢分成簡單和復雜兩種類型，復雜類型又可以分成三個大類：簡單子查詢、所謂的衍生表（子查詢在 FROM 子句里）和 UNION 。

SIMPLE：查詢中不包含子查詢或者UNION

mysql> explain select * from a;
+----+-------------+-------+...
| id | select_type | table |...
+----+-------------+-------+...
|? 1 | SIMPLE????? | a???? |...
+----+-------------+-------+...

SUBQUERY：子查詢

PRIMARY:子查詢上層

mysql> explain select * from a where a_id in (select b_id from b);
+----+--------------------+-------+...
| id | select_type??????? | table |...
+----+--------------------+-------+...
|? 1 | PRIMARY??????????? | a???? |...
|? 2 | DEPENDENT SUBQUERY | b???? |...
+----+--------------------+-------+...

DERIVED：在FROM列表中包含子查詢， MySQL 會遞歸執行這些子查詢，把結果放在 臨時表 里。

mysql> explain select count(*) from (select * from a) as der;
+----+-------------+-------+...
| id | select_type | table |...
+----+-------------+-------+...
|? 1 | PRIMARY???? | NULL? |...
|? 2 | DERIVED???? | a???? |...
+----+-------------+-------+...

--第三章

table 列

顯示每行對應的表名。若在 SELECT 語句中為表起了別名，則會顯示表的別名。

一個很復雜的示例及解釋可參考《高性能 MySQL 》（第二版）中文版 P467(pdf.491) 〈附錄 B.2.3 table 列〉

--第四章

type 列

MySQL 在表里找到所需行的方式。包括（ 由左至右，由最差到最好 ）：

ALL

全表掃描，MySQL 從頭到尾掃描整張表查找行。

mysql> explain select * from a\G

...

? ? ? ? ?type: ALL

如果加上 limit 如 select * from a limit 10 MySQL 會掃描 10 行，但掃描方式不會變，還是從頭到尾掃描。

index

按索引次序掃描表，就是先讀索引，再讀實際的行，其實還是全表掃描。主要優點是避免了排序，因為索引是排好序的。

建表：

create table?a(a_id int not null, key(a_id));

insert into a value(1),(2)

mysql> explain select * from a\G
...
???????? type: index

range

以范圍的形式掃描索引

建表：

create table?a(a_id int not null, key(a_id));

insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

mysql> explain select * from a where a_id > 1\G
...
???????? type: range

...

IN 比較符也會用 range 表示：

mysql> explain select * from a where a_id in (1,3,4)\G
...
???????? type: range
...

ref

非唯一性索引訪問

建表：

create table?a(a_id int not null, key(a_id));

insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

mysql> explain select * from a where a_id=1\G
...
???????? type: ref

...

eq_ref

使用有唯一性索引查找（主鍵或唯一性索引）

建表及插入數據：

create table a(id int primary key);

create table a_info(id int primary key, title char(1));

insert into a value(1),(2);

insert into a_info value(1, 'a'),(2, 'b');

此時 a_info 每條記錄與 a 一一對應，通過主鍵 id 關聯起來，所以 a_info 的 type 為 eq_ref。

刪除 a_info 的主鍵：ALTER TABLE ?`a_info` DROP PRIMARY KEY;

現在 a_info 已經沒有索引了：

mysql> explain select * from a join a_info using(id);
+----+...+--------+--------+...

| id |...| table? | type?? |...

+----+...+--------+--------+...

|? 1 |...| a_info | ALL??? |...

|? 1 |...| a????? | eq_ref |...

+----+...+--------+--------+...

這次 MySQL 調整了執行順序，先全表掃描 a_info 表，再對表 a 進行 eq_ref 查找，因為 a 表 id 還是主鍵。

刪除 a 的主鍵：alter table a drop primary key;

現在 a 也沒有索引了：

mysql> explain select * from a join a_info using(id);
...+--------+------+...
...| table? | type |...
...+--------+------+...
...| a????? | ALL? |...
...| a_info | ALL? |...
...+--------+------+...

現在兩個表都使用全表掃描了。

建表及插入數據：

create table a(id int primary key);

create table a_info(id int, title char(1), key(id));

insert into a value(1),(2);

insert into a_info value(1, 'a'),(2, 'b');

現在 a_info 表 id 列變為普通索引（非唯一性索引）：

mysql> explain select * from a join a_info using(id) where a.id=1;
...+--------+-------+...
...| table? | type? |...
...+--------+-------+...
...| a????? | const |...
...| a_info | ref?? |...
...+--------+-------+...

a_info 表 type 變為 ref 類型了。

所以，唯一性索引才會出現 eq_ref （非唯一性索引會出現 ref ），因為唯一，所以最多只返回一條記錄，找到后無需繼續查找，因此比 ref 更快。

const

被稱為“常量”，這個詞不好理解，不過出現 const 的話就表示發生下面兩種情況：

在整個查詢過程中這個表最多只會有一條匹配的行，比如主鍵 id=1 就肯定只有一行，只需讀取一次表數據便能取得所需的結果，且表數據在分解執行計劃時讀取。

返回值直接放在 select 語句中，類似 select 1 AS f 。可以通過 extended 選擇查看內部過程：

建表及插入數據：

create table a(id int primary key, c1 char(20) not null, c2 text not null, c3 text not null);

insert into a values(1, 'asdfasdf', 'asdfasdf',?'asdfasdf'), (2, 'asdfasdf', 'asdfasdf',?'asdfasdf');

mysql> explain extended select * from a where id=1\G
...
???????? type: const
possible_keys: PRIMARY
????????? key: PRIMARY
...

用 show warnings 查看 MySQL 是如何優化的：

mysql> show warnings\G
...
Message: select '1' AS `id`,'asdfasdf' AS `c1`,'asdfasdf' AS `c2`,'asdfasdf' AS
`c3` from `test`.`a` where 1

查詢返回的結果為：

mysql> select * from a where id=1;
+----+----------+----------+----------+
| id | c1?????? | c2?????? | c3?????? |
+----+----------+----------+----------+
|? 1 | asdfasdf | asdfasdf | asdfasdf |
+----+----------+----------+----------+

可以看出，返回結果中的字段值都以“值 AS 字段名”的形式直接出現在優化后的 select 語句中。

修改一下查詢：

mysql> explain select * from a where id in(1,2)\G
...
???????? type: range

...

當返回結果超過 1 條時， type 便不再為 const 了。

重新建表及插入數據：

create table a (id int not null);

insert into a value(1),(2),(3);

mysql> explain select * from a where id=1\G
...
???????? type: ALL

目前表中只有一條 id=1 的記錄，但 type 已為 ALL ，因為只有唯一性索引才能保證表中最多只有一條記錄，只有這樣 type 才有可能為 const 。

為 id 加普通索引后， type 變為 ref ，改為加唯一或主鍵索引后， type 便變為 const 了。

相關閱讀：

《高性能 MySQL 》第 2 版中文版

P130(pdf.153) 計算和減少常量表達式

P471(pdf.495) B.2.4 type 列

《 MySQL 性能調優與架構設計》

pdf.109

MySQL 幫助手冊?7.2.1. EXPLAIN語法（獲取SELECT相關信息）

system

system 是 const 類型的特例，當表只有一行時就會出現 system 。

建表及插入數據：

create table a(id int primary key);
insert into a value(1);

mysql> explain select * from a\G
...
???????? type: system

...

NULL

在優化過程中就已得到結果，不用再訪問表或索引。

mysql> explain select min(a_id) from a\G
...
???????? type: NULL

...

--第五章

possible_keys 列

可能被用到的索引

建表：

create table a (a_id int primary key, a_age int, key (a_id, a_age));

此表有主鍵及普通索引兩個索引。

mysql> explain select * from a where a_id=1\G
...
possible_keys: PRIMARY,a_id

--第六章

key 列

查詢過程中實際使用的索引

mysql> explain select * from a where a_id=1\G
...
possible_keys: PRIMARY,a_id
????????? key: PRIMARY

--第七章

key_len 列

索引字段最大可能使用的長度。

mysql> explain select * from a where a_id=1\G
...
????????? key: PRIMARY
????? key_len: 4

a_id 是 int 類型，int 的長度是 4 字節，所以 key_len 為 4。

--第八章

ref 列

指出對 key 列所選擇的索引的查找方式，常見的值有 const, func, NULL, 具體字段名。當 key 列為 NULL ，即不使用索引時，此值也相應的為 NULL 。

建表及插入數據：

create table a(id int primary key, age int);

insert into a value(1, 10),(2, 10);

mysql> desc select * from a where age=10\G
...
????????? key: NULL
????? key_len: NULL
????????? ref: NULL

...

當 key 列為 NULL ， ref 列也相應為 NULL 。

mysql>?explain?select * from a where id=1\G

...
????????? key: PRIMARY
????? key_len: 4
????????? ref: const

這次 key 列使用了主鍵索引，where id=1 中 1 為常量， ref 列的 const 便是指這種常量。

mysql>?explain?select * from a where id in (1,2)\G

...
possible_keys: PRIMARY
????????? key: PRIMARY
????? key_len: 4
????????? ref: NULL

...

不理解 ref 為 NULL 的含意，比如上面這個查詢， key 列有使用索引，但 ref 列卻為 NULL 。網上搜索及查閱了一下 MySQL 幫助手冊都沒有找到相關的描述。

再建表及插入數據：

create table a(id int primary key, a_name int not null);
create table b(id int primary key, b_name int not null);

insert into a value(1, 1),(2, 2),(3, 3);
insert into b value(1, 111),(2, 222),(3, 333);

mysql> explain select * from a join b using(id);
...+-------+--------+...+---------+...+-----------+...

...| table | type?? |...| key???? |...| ref ??? ? |...

...+-------+--------+...+---------+...+-----------+...

...| a???? | ALL??? |...| NULL??? |...| NULL ?? ? |...

...| b???? | eq_ref |...| PRIMARY |...| test.a.id |...

...+-------+--------+...+---------+...+-----------+...

這里 test.a.id 即為具體字段，意為根據表 a 的 id 字段的值查找表 b 的主鍵索引。

mysql> explain select * from a join b using(id) where b.id=1;
...+-------+-------+...+---------+...+-------+...

...| table | type? |...| key???? |...| ref?? |...

...+-------+-------+...+---------+...+-------+...

...| a???? | const |...| PRIMARY |...| const |...

...| b???? | const |...| PRIMARY |...| const |...

...+-------+-------+...+---------+...+-------+...

因為 a join b 的條件為 id 相等，而 b.id=1 ，就是 a.id 也為 1 ，所以 a,b 兩個表的 ref 列都為 const 。

ref 為 func 的情況出現在子查詢中，暫不明其原理：

mysql> explain select * from a where id in (select id from b where id in (1,2));

+----+--------------------+-------+...+---------+...+------+...

| id | select_type??????? | table |...| key???? |...| ref? |...

+----+--------------------+-------+...+---------+...+------+...

|? 1 | PRIMARY??????????? | a???? |...| NULL??? |...| NULL |...

|? 2 | DEPENDENT SUBQUERY | b???? |...| PRIMARY |...| func |...

+----+--------------------+-------+...+---------+...+------+...

--第七章

rows 列

MySQL 估計的需要掃描的行數。只是一個估計。

--第八章

Extra 列

顯示上述信息之外的其它信息，但卻很重要。

Using index

此查詢使用了覆蓋索引(Covering Index)，即通過索引就能返回結果，無需訪問表。

若沒顯示"Using index"表示讀取了表數據。

建表及插入數據：

create table a (id int primary key, age int);

insert into a value(1, 10),(2, 10);

mysql> explain select id from a\G
...
??????? Extra: Using index

因為 id 為主鍵索引，索引中直接包含了 id 的值，所以無需訪問表，直接查找索引就能返回結果。

mysql> explain select age from a\G
...
??????? Extra:

age 列沒有索引，因此沒有 Using index ，意即需要訪問表。

為 age 列添加索引：create index age on a(id, age);

mysql> explain select age from a\G
...
??????? Extra: Using index

現在索引 age 中也包含了 age 列的值，因此不用訪問表便能返回結果了。

建表：create table a(id int auto_increment primary key, age int, name char(10));

插入 100w 條數據：insert into a value(null, rand()*100000000, 'jack');

Using where

表示 MySQL 服務器從存儲引擎收到行后再進行“后過濾”（Post-filter）。所謂“后過濾”，就是先讀取整行數據，再檢查此行是否符合 where 句的條件，符合就留下，不符合便丟棄。因為檢查是在讀取行后才進行的，所以稱為“后過濾”。

建表及插入數據：

create table a (num_a int not null, num_b int not null, key(num_a));

insert into a value(1,1),(1,2),(2,1),(2,2);

mysql> explain select * from a where num_a=1\G
...
???????? type: ref
possible_keys: num_a
????????? key: num_a

? ? ? key_len: 4
...
??????? Extra:

雖然查詢中有 where 子句，但只有 num_a=1 一個條件，且 num_a 列存在索引，通過索引便能確定返回的行，無需進行“后過濾”。

所以，并非帶 WHERE 子句就會顯示"Using where"的。

mysql> explain select * from a where num_a=1 and num_b=1\G
...
???????? type: ref
possible_keys: num_a
????????? key: num_a
...
??????? Extra: Using where

此查詢增加了條件 num_b=1 ，此列沒有索引，但可以看到查詢同樣能使用 num_a 索引。 MySQL 先通過索引 num_a 找到 num_a=1 的行，然后讀取整行數據，再檢查 num_b 是否等于 1 ，執行過程看上去象這樣：

num_a索引|num_b?沒有索引，屬于行數據

+-------+-------+
| num_a | num_b |?where 子句(num_b=1)
+-------+-------+
|???? 1 |???? 1 | 符合
|???? 1 |???? 2 | 不符合
| ? ... |???... | ...
+-------+-------+

在《高性能 MySQL 》（第二版）P144(pdf.167) 頁有更形象的說明圖片（圖 4-5 MySQL 通過整表掃描查找數據）。

字段是否允許 NULL 對 Using where 的影響：

建表及插入數據：

create table a (num_a int null, num_b int null, key(num_a));

insert into a value(1,1),(1,2),(2,1),(2,2);

這次 num_a, num_b 字段允許為空。

在上例 num_a not null 時， num_a 索引的長度?key_len 為 4 ，當 num_a null 時， num_a 索引的長度變為了 5 ：

mysql> explain select * from a where num_a=1\G
...
???????? type: ref
possible_keys: num_a
????????? key: num_a
????? key_len: 5
...
??????? Extra: Using where

并且哪怕只有 num_a=1 一個條件，也會出現 Using where 。原因暫不明白。

Using temporary

使用到臨時表

建表及插入數據：

create table a(a_id int, b_id int);

insert into a values(1,1),(1,1),(2,1),(2,2),(3,1);

mysql> explain select distinct a_id from a\G
...
??????? Extra: Using temporary

MySQL 使用臨時表來實現 distinct 操作。

Using filesort

若查詢所需的排序與使用的索引的排序一致，因為索引是已排序的，因此按索引的順序讀取結果返回，否則，在取得結果后，還需要按查詢所需的順序對結果進行排序，這時就會出現 Using filesort 。

建表及插入數據：

create table a(a_id int, b_id int);

insert into a values(1,1),(1,1),(2,1),(2,2),(3,1);

mysql> explain select * from a order by a_id\G
...
??????? Extra: Using filesort

對于沒有索引的表，只要 order by 必會出現 Using filesort 。

現在增加索引：create index a_id on a(a_id);

把表 a 的記錄增加到約 100w(1048576) 條， a_id 與 b_id 都是隨機生成的數字：

mysql> select * from a order by rand() limit 10;
+-------+--------+
| a_id? | b_id?? |
+-------+--------+
| 61566 | 961297 |
| 33951 | 680542 |

| ..... | ...... |
+-------+--------+

mysql> explain select * from a order by a_id\G
...
???????? type: ALL
...

? ? ? ? ?rows: 1048576
??????? Extra: Using filesort

同樣是 Using filesort ，type 為 ALL ，全表掃描。聽說“取全表數據根據ID排序，走索引一定不如直接查，因為可以減少因為需要索引改變數據訪問順序造成隨機IO的概率，數據庫放棄索引是應該的”，參考：

http://isky000.com/database/mysql_order_by_implement#comment-2981

當 type 為?rang、 ref 或者 index 的時候才有可能利用索引排序，其它，如 ALL ，都無法通過索引排序，此時若有 order by ，如上例，便會出現 Using filesort 。

現在增加 where 子句：

mysql> explain select * from a where a_id=10 order by a_id\G
...
???????? type: ref
possible_keys: a_id
????????? key: a_id
...

? ? ? ? ?rows: 8
??????? Extra:

查詢走了索引 a_id ，此時 type 為 ref ，直接按索引順序返回，沒有 Using filesort 。

修改 where 子句：

mysql> explain select * from a where a_id>10 and a_id<100 order by a_id\G
...
???????? type: range
possible_keys: a_id
????????? key: a_id
...
???????? rows: 712
??????? Extra: Using where

同樣利用索引排序，沒有 Using filesort 。

再修改 where 子句：

mysql> explain select * from a where a_id >10 order by a_id\G
...
???????? type: ALL
possible_keys: a_id
????????? key: NULL
...
???????? rows: 1048576
??????? Extra: Using where; Using filesort

又出現 Using filesort 且 type 變為 ALL 。注意以上例子的 rows 列，此列表示 MySQL 估計查詢需要讀取的行數，分別為?1048576,?8,?712,?1048576 ，特別注意最后兩個數字： 712, 1048576 。

可見，當索引能為查詢排除大部份行時（ a_id=10 時約讀取 8 行，排除了大部份， a_id>10 and a_id<100 時約讀取 712 行，同樣排除了大部份）便使用索引，否則，如 a_id>10 時約讀取?1048576 ， MySQL 直接改用全表掃描，再 Using filesort 。也就是說， MySQL 會根據表中的信息及查詢來決定使用任種方式。

關于 MySQL 讀取數據表的方式，可參考（暫缺參考資料），就會明白為什么需讀取?1048576 行時，先讀索引再讀表數據還不如全表掃描了。

對于多字段排序(order by a, b)及帶 group by 的查詢，可參考 MySQL 幫助手冊?7.2.12. MySQL如何優化ORDER BY 。

參考資料：

MySQL 幫助手冊

7.2.1. EXPLAIN語法（獲取SELECT相關信息）

7.2.12. MySQL如何優化ORDER BY

《高性能 MySQL 》（第二版）中文版

P463(pdf.487) 附錄 B

《 MySQL 性能調優與架構設計》

MySQL ORDER BY 的實現分析

http://isky000.com/database/mysql_order_by_implement

Mysql執行計劃

http://www.xifenfei.com/954.html

MySQL執行計劃解讀

http://wenku.baidu.com/view/41846439376baf1ffc4fad4f.html

Mysql Explain 詳解

http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html

MYSQL EXPLAIN語句的extended 選項學習體會

http://hi.baidu.com/dearhwj/blog/item/03badf17641a28094a90a78d.html

Show Warnings

MySQL索引類型一覽讓MySQL高效運行起來

http://database.51cto.com/art/200910/156685.htm

Mysql執行計劃中的Using filesort

"指出Mysql可以使用哪個索引在該表中找到行"

http://www.taobaodba.com/html/235_mysql_using_filesort.html

2.order by b，如果b列不在索引中，不管b值是否相同，總會出現Using filesort。

What does Using filesort mean in MySQL?

"任何不能通過index進行的sort都稱之為filesort"

http://iceskysl.1sters.com/?p=638

mysql explain中的using filesort

"只能是如果可能的話修改查詢的排序條件"

http://blog.csdn.net/wdwbw/article/details/5256064

Mysql之EXPLAIN顯示using filesort

"結果昨天看到公司的一個"

http://www.ccvita.com/169.html （可能這個是出處）

多列索引在建立的時候是以B-樹結構建立的，因此建立索引的時候是先建立ID的按順序排的索引，在相同ID的情況下建立FID按順序排的索引，最后在FID 相同的情況下建立按INVERSE_DATE順序排的索引，如果列數更多以此類推。

要在優化一下這個sql就應該為它建立另一個索引IDX（ID,INVERSE_DATE），這樣就消除了using filesort速度也會快很多。

mysql優化Using filesort

"并且用到了room_number這列索引"

http://www.askwan.com/post/151/

MySQL 索引優化 Using filesort

"頁面咔的一下就出來了"

http://www.leakon.com/archives/332

轉自： http://blog.csdn.net/gevolution90/article/details/6856922

MySQL Explain 結果解讀與實踐

更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號聯系： 360901061

您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對您有幫助就好】元

2元

5元

10元

20元

自定義

亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频