MySql explain怎么執(zhí)行

本篇內(nèi)容介紹了“MySql explain怎么執(zhí)行”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細閱讀，能夠?qū)W有所成！

通過執(zhí)行計劃可以了解查詢方式、索引使用情況、需要掃描的數(shù)據(jù)量以及是否需要臨時表或排序操作等信息。

查詢執(zhí)行計劃：explain + SQL 語句

id:  編號

select_type:  查詢類型

table：表

type: 類型

possible_keys: 預(yù)測用到的索引

key: 實際使用的索引

key_len: 實際使用索引的長度

ref: 表之間的引用

rows: 通過索引查詢到的數(shù)據(jù)量

Extra：額外的信息

創(chuàng)建如下表：

CREATE TABLE course(cid INT(3),cname VARCHAR(20),tid INT(3));
CREATE TABLE teacher(tid INT(3),tname VARCHAR(20),tcid INT(3));
CREATE TABLE teacherCard(tcid INT(3),tcdesc VARCHAR(200));

INSERT INTO course VALUES(1,'java',1);INSERT INTO course VALUES(2,'html',1);INSERT INTO course VALUES(3,'sql',2);INSERT INTO course VALUES(4,'web',3);
INSERT INTO teacher VALUES(1,'tz',1);INSERT INTO teacher VALUES(2,'tw',2);INSERT INTO teacher VALUES(3,'tl',3);
INSERT INTO teacherCard VALUES(1,'tzdesc');INSERT INTO teacherCard VALUES(2,'twdesc');INSERT INTO teacherCard VALUES(3,'tldesc');

上面這些數(shù)據(jù)可以使用很多方法查詢出來：

第一種方法：

SELECT t.* FROM teacher t,course c,teacherCard tc WHERE t.tid = c.tid AND t.tcid = tc.tcid AND (c.cid = 2 OR tc.tcid = 3)

查看執(zhí)行計劃：

1. id 編號

<1> id ：id值相同，從上往下，順序執(zhí)行，t3-tc3-c4

下面我們往teacher表中再插入一點數(shù)據(jù)：

INSERT INTO teacher VALUES(4,'tz4',4);INSERT INTO teacher VALUES(5,'tw5',5);INSERT INTO teacher VALUES(6,'tl6',6);

再來看執(zhí)行計劃：

我們發(fā)現(xiàn)執(zhí)行計劃變了，變成了tc3-c4-t6.

表的執(zhí)行順序，因數(shù)量的個數(shù)改變而改變的原因：笛卡爾積

數(shù)據(jù)量小的表優(yōu)先查詢

<2> id值不同：id值越大越優(yōu)先查詢。（本質(zhì)：在嵌套子查詢時，先查內(nèi)層，再查外層）

查詢教授SQL課程的老師描述(desc)

EXPLAIN SELECT tc.tcdesc FROM teacherCard tc ,course c ,teacher t WHERE t.tid = c.tid AND t.tcid = tc.tcid AND c.cname = 'sql'

將以上多表查詢改成子查詢

EXPLAIN SELECT tc.tcdesc FROM teacherCard tc WHERE tc.tcid = (SELECT t.tcid FROM teacher t WHERE t.tid = (SELECT c.cid FROM course c WHERE c.cname = 'sql'))

改成子查詢+多表：

EXPLAIN SELECT t.tname ,tc.tcdesc FROM teacher t, teacherCard tc WHERE t.tcid = tc.tcid AND t.tid = (SELECT c.tid FROM course c WHERE c.cname = 'sql')

<3> id值有相同，又有不同：id值越大越優(yōu)先；id值相同，從上往下，順序執(zhí)行

2. select_type:

<1>PRIMARY: 包含子查詢SQL中的主查詢（最外層）

<2>SUBQUERY: 包含子查詢SQL中的子查詢（非最外層）

<3>SIMPLE：簡單查詢（不包含子查詢、union）

<4>DERIVED: 衍生查詢（使用到了臨時表）

 a: 在from 子查詢中只有一張表

EXPLAIN SELECT cr.cname FROM (SELECT * FROM course WHERE tid IN (1,2)) cr;

b: 在from 子查詢中，如果有table1 union table2，則table1就是derived，

table2就是union

EXPLAIN SELECT cr.cname FROM (SELECT * FROM course WHERE tid = 1 UNION SELECT * FROM course WHERE tid = 2) cr;

<5>UNION: 上例

<6>UNION RESULT：告知開發(fā)人員，哪些表之間存在union查詢

3. type：索引類型、類型

常見的如下：越往左邊，性能越高，排序如下

system>const>eq_ref>ref>range>index>all

其中：system、const只是理想情況；實際能達到ref、range

system（忽略）:只有一條數(shù)據(jù)的系統(tǒng)表或衍生表只有一條數(shù)據(jù)的主查詢。

const：僅僅能查到一條數(shù)據(jù)的SQL，用戶Primary Key或unique索引

eq_ref: 唯一性索引：對于每個索引鍵的查詢，返回匹配唯一行數(shù)據(jù)（有且只有1個，不能多、不能0）,常見于唯一索引和主鍵索引。

ref: 非唯一性索引：對于每個索引鍵的查詢，返回匹配的所有行（0，多個）

range: 檢索指定范圍的行，where后面是一個范圍查詢（between， < , > 特殊：in有時候會失效，從而轉(zhuǎn)化為無索引all）

index：查詢?nèi)克饕臄?shù)據(jù)，查詢的字段是索引

EXPLAIN SELECT tid FROM test01;

all: 查詢?nèi)勘碇袛?shù)據(jù)，查詢的字段不是索引，就要全表掃描

EXPLAIN SELECT tname FROM test01;

system和const: 結(jié)果只有一條數(shù)據(jù)

eq_ref: 結(jié)果多條，但是每條數(shù)據(jù)是唯一的

ref: 結(jié)果多條，但是每條數(shù)據(jù)是0或者多條

4. possible_keys: 可能遇到的索引，不準(zhǔn)，是一種預(yù)測

5. key：實際使用到的索引

6. key_len: 索引的長度，作用是用于判斷復(fù)合索引是否被完全使用。

CREATE TABLE test_k1(  NAME CHAR(20) NOT NULL DEFAULT '');ALTER TABLE test_k1 ADD INDEX index_name(NAME);
EXPLAIN SELECT * FROM test_k1 WHERE NAME = '';

在utf-8中，一個字符占3個字節(jié)，因為name是20的長度，所以是key_len為60，因為name字段不能為null，所以是60字節(jié)，如果name字段可以為null，則會使用一個字節(jié)用于標(biāo)識，則key_len是61字節(jié)，

utf8: 1個字符占3字節(jié)

gbk：1個字符占2字節(jié)

mysql用2個字節(jié)標(biāo)識可變長度

7. ref ：作用是指明當(dāng)前表所參照的字段

select ... from ... where a.c = b.x （其中b.x可以是常量，const）

8. rows：被索引優(yōu)化查詢的數(shù)據(jù)個數(shù)

9. Extra：

    <1>using filesort: 性能消耗大，需要"額外"的一次排序（查詢），常見于order by語句中

    對于單索引，如果排序和查找是同一個字段，則不會出現(xiàn)using filesort，如果排序和查找不是同一個字段，則會出現(xiàn)using filesort。

   復(fù)合索引：不能跨列(最佳左前綴)

ALTER TABLE test_k1 ADD INDEX name_name1_name2_index (NAME,name1,name2);EXPLAIN SELECT * FROM test_k1 WHERE name1 = '' ORDER BY name2; --Using filesort EXPLAIN SELECT * FROM test_k1 WHERE NAME1 = '' ORDER BY name2; --Using filesort EXPLAIN SELECT * FROM test_k1 WHERE NAME = '' ORDER BY name1;

   where 和order by 按照復(fù)合索引的順序使用，不要跨列或無序使用。

<2>using temporary：性能損耗大，用到了臨時表。一般出現(xiàn)在group by語句中

EXPLAIN SELECT NAME FROM test_k1 WHERE NAME IN ('1','2','3') GROUP BY NAME;EXPLAIN SELECT NAME FROM test_k1 WHERE NAME IN ('1','2','3') GROUP BY NAME1; --Using TEMPORARY

注意：查詢哪些列，就用哪些列分組

<3>using index: 性能提升了，索引覆蓋（覆蓋索引），原因是不讀取原文件，只從索引文件中獲取數(shù)據(jù)（不需要回表查詢）

索引覆蓋：只要使用到的列全部都在索引中，就是索引覆蓋

ALTER TABLE test_k1 ADD INDEX name_name1_index (NAME,name1);
EXPLAIN SELECT NAME FROM test_k1 WHERE NAME = '' ;--Using INDEX 不需要回表查詢EXPLAIN SELECT NAME1 FROM test_k1 WHERE NAME = '' ;--Using INDEX 不需要回表查詢
EXPLAIN SELECT NAME2 FROM test_k1 WHERE NAME = '';   -- 需要回表查詢

如果用到了索性覆蓋（using index）,會對possible_keys和key造成影響

a. 如果沒有where,則索引只出現(xiàn)在key中

b. 如果有where,則索引出現(xiàn)在key和possible_keys中

<4> using where(需要回表查詢)

EXPLAIN SELECT name2 FROM test_k1 WHERE NAME2 = '';   -- 需要回表查詢

<5> Using index condition 在5.6版本后加入的新特性

   查詢列不完全被索引覆蓋，查詢條件完全可以使用到索引

“MySql explain怎么執(zhí)行”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！