引言：

實際項目開發(fā)中溯祸，由于我們不知道實際查詢的時候數(shù)據(jù)庫里發(fā)生了什么事情羽嫡，數(shù)據(jù)庫軟件是怎樣掃描表本姥、怎樣使用索引的，因此杭棵，我們能感知到的就只有

sql語句運行的時間婚惫，在數(shù)據(jù)規(guī)模不大時，查詢是瞬間的魂爪，因此先舷，在寫sql語句的時候就很少考慮到性能的問題。但是當數(shù)據(jù)規(guī)模增大滓侍，如千萬蒋川、億的時候，我們運

行同樣的sql語句時卻發(fā)現(xiàn)遲遲沒有結(jié)果撩笆，這個時候才知道數(shù)據(jù)規(guī)模已經(jīng)限制了我們查詢的速度捺球。所以，查詢優(yōu)化和索引也就顯得很重要了夕冲。

問題：

當我們在查詢前能否預(yù)先估計查詢究竟要涉及多少行懒构、使用哪些索引、運行時間呢耘擂？答案是能的，mysql提供了相應(yīng)的功能和語法來實現(xiàn)該功能絮姆。

分析：

1醉冤、MySQL語法

MySql提供了EXPLAIN語法用來進行查詢分析，在SQL語句前加一個”EXPLAIN”即可篙悯。

默認情況下Mysql的profiling是關(guān)閉的蚁阳，所以首先必須打開profiling

set profiling="ON"
mysql> show variables like "%profi%";
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | ON |

show processlist; 查看現(xiàn)在在運行的所有進程列表，在進程列表中我們唯一需要的是ID
mysql> show processlist;
+----+------+----------------+-----------+---------+------+-------+-------------
-----+
| Id | User | Host | db | Command | Time | State | Info
|
+----+------+----------------+-----------+---------+------+-------+-------------
-----+
| 3 | root | localhost:2196 | click_log | Query | 0 | NULL | show process
list |
+----+------+----------------+-----------+---------+------+-------+-------------
mysql> show profile cpu,memory for query 3;
+--------------------+------------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------+------------+----------+------------+
| freeing items | 0.00001375 | NULL | NULL |
| logging slow query | 0.00001375 | NULL | NULL |
| cleaning up | 0.00000050 | NULL | NULL |
+--------------------+------------+----------+------------+

SHOW PROFILES Syntax:
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type:
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS

2鸽照、Navicat工具

打開profile分析工具：

file

查看是否生效：show variable like ‘%profil%’;

file

查看進程：show processlist;

file

選擇數(shù)據(jù)庫：use db_jiakao;

file

全部分析的類型：show PROFILE all;

file

查看表索引：show index from user_member;##查看表索引

file

使用explain命令查看query語句的性能：

EXPLAIN select * from user_feedback;##查看執(zhí)行計劃中的sql性能

file

file

第一個查詢是全表掃描螺捐，第二個是索引掃描：

區(qū)別在于type:all是全表掃描 index 通過索引掃描

或者在查詢中輸入需要查看執(zhí)行計劃的語句，點擊執(zhí)行，然后點擊解釋定血。選擇解釋標簽赔癌，就可以查看到sql的執(zhí)行計劃了

file

解釋

1、ID澜沟、table

id：Query Optimizer 所選定的執(zhí)行計劃中查詢的序列號灾票；
table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的

2、type

顯示連接使用了何種類型茫虽，對表所使用的訪問方式刊苍。從最好到最差的連接類型為const、eq_reg濒析、ref正什、range、indexhe和ALL

說明：不同連接類型的解釋（按照效率高低的順序排序）
system：系統(tǒng)表号杏，表中只有一行數(shù)據(jù)婴氮。這是const連接類型的特殊情況。

const ：讀常量馒索，且最多只會有一條記錄匹配莹妒。表中的一個記錄的最大值能夠匹配這個查詢（索引可以是主鍵或惟一索引）。因為只有一行绰上，這個值實際就是常數(shù)旨怠，因為MYSQL先讀這個值然后把它當做常數(shù)來對待。

eq_ref：最多只會有一條匹配結(jié)果蜈块，一般是通過主鍵或者唯一鍵索引來訪問鉴腻；在連接中，MYSQL在查詢時百揭，從前面的表中爽哎，對每一個記錄的聯(lián)合都從表中讀取一個記錄，它在查詢使用了索引為主鍵或惟一鍵的全部時使用器一。

ref：Join 語句中被驅(qū)動表索引引用查詢课锌，這個連接類型只有在查詢使用了不是惟一或主鍵的鍵或者是這些類型的部分（比如，利用最左邊前綴）時發(fā)生祈秕。對于之前的表的每一個行聯(lián)合渺贤，全部記錄都將從表中讀出。這個類型嚴重依賴于根據(jù)索引匹配的記錄多少—越少越好请毛。

range：索引范圍掃描志鞍，這個連接類型使用索引返回一個范圍中的行，比如使用>或<查找東西時發(fā)生的情況方仿。

ref_or_null：與ref 的唯一區(qū)別就是在使用索引引用查詢之外再增加一個空值的查詢固棚。

unique_subquery：子查詢中的返回結(jié)果字段組合是主鍵或者唯一約束

index_merge：查詢中同時使用兩個（或更多）索引统翩，然后對索引結(jié)果進行merge 之后再讀取表數(shù)據(jù)；

index_subquery：子查詢中的返回結(jié)果字段組合是一個索引（或索引組合）此洲，但不是一個主鍵或者唯一索引厂汗；

index：全索引掃描，這個連接類型對前面的表中的每一個記錄聯(lián)合進行完全掃描（比ALL更好黍翎，因為索引一般小于表數(shù)據(jù)）面徽。

ALL：全表掃描，這個連接類型對于前面的每一個記錄聯(lián)合進行完全掃描匣掸，這一般比較糟糕趟紊，應(yīng)該盡量避免。

3碰酝、possible_keys

顯示可能應(yīng)用在這張表中的索引霎匈。這里的索引名字是創(chuàng)建索引時指定的索引昵稱；如果索引沒有昵稱送爸，則默認顯示的是索引中第一個列的名字铛嘱。
如果為空，沒有可能的索引袭厂，可以為相關(guān)的域從WHERE語句中選擇一個合適的語句

4墨吓、key

實際使用的索引。如果為NULL纹磺，則沒有使用索引帖烘。很少的情況下，MYSQL會選擇優(yōu)化不足的索引橄杨。這種情況下秘症，可以在SELECT語句中使用USE INDEX（indexname）來強制使用一個索引或者用IGNORE INDEX（indexname）來強制MYSQL忽略索引
key_len
使用的索引的長度。在不損失精確性的情況下式矫，長度越短越好

5乡摹、ref

顯示的是列的名字，顯示索引的哪一列被使用了采转，MySQL將根據(jù)這些列來選擇行聪廉，如果可能的話，是一個常數(shù) 故慈。
“對于每一種與另一個表中記錄的組合板熊，MySQL將從當前的表讀取所有帶有匹配索引值的記錄。如果連接操作只使用鍵的最左前綴惯悠，或者如果鍵不是 UNIQUE或PRIMARY KEY類型（換句話說，如果連接操作不能根據(jù)鍵值選擇出唯一行）竣况，則MySQL使用ref連接類型克婶。如果連接操作所用的鍵只匹配少量的記錄筒严，則ref是一 種好的連接類型∏橛”

6鸭蛙、rows

MYSQL認為必須檢查的用來返回請求數(shù)據(jù)的行數(shù) ，這里最理想的數(shù)字就是1筋岛。

7娶视、select_type

所使用的查詢類型，判斷是否是復(fù)雜語句睁宰，主要有以下這幾種查詢類型

DEPENDENT SUBQUERY：子查詢中內(nèi)層的第一個SELECT肪获，依賴于外部查詢的結(jié)果集；
DEPENDENT UNION：子查詢中的UNION柒傻，且為UNION 中從第二個SELECT 開始的后面所有SELECT孝赫，同樣依賴于外部查詢的結(jié)果集；
PRIMARY：子查詢中的最外層查詢红符，注意并不是主鍵查詢青柄；
SIMPLE：除子查詢或者UNION 之外的其他查詢；
** SUBQUERY：子查詢內(nèi)層查詢的第一個SELECT预侯，結(jié)果不依賴于外部查詢結(jié)果集致开；
** UNCACHEABLE SUBQUERY：結(jié)果集無法緩存的子查詢；
UNION：UNION 語句中第二個SELECT 開始的后面所有SELECT萎馅，第一個SELECT 為PRIMARY
UNION RESULT：UNION 中的合并結(jié)果双戳；

8、Extra

關(guān)于MYSQL如何解析查詢的額外信息校坑。將在表4.3中討論拣技，但這里可以看到的壞的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引耍目，結(jié)果是檢索會很慢

說明：extra列返回的描述的意義

Distinct ：一旦mysql找到了與行相聯(lián)合匹配的行膏斤，就不再搜索了。

Not exists ：mysql優(yōu)化了LEFT JOIN邪驮，一旦它找到了匹配LEFT JOIN標準的行莫辨，就不再搜索了。

No tables：Query 語句中使用FROM DUAL 或者不包含任何FROM 子句毅访；

**Using filesort **：當我們的Query 中包含ORDER BY 操作沮榜，而且無法利用索引完成排序操
作的時候，MySQL Query Optimizer 不得不選擇相應(yīng)的排序算法來實現(xiàn)喻粹◇∪冢看到這個的時候，查詢就需要優(yōu)化了守呜。mysql需要進行額外的步驟來發(fā)現(xiàn)如何對返回的行排序型酥。它根據(jù)連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行山憨。

**Using index **：所需要的數(shù)據(jù)只需要在Index 即可全部獲得而不需要再到表中取數(shù)據(jù)。列數(shù)據(jù)是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的弥喉，這發(fā)生在對表的全部的請求列都是同一個索引的部分的時候郁竟。

Using temporary ：當MySQL 在某些操作中必須使用臨時表的時候，在Extra 信息中就會
出現(xiàn)Using temporary 由境。主要常見于GROUP BY 和ORDER BY 等操作中棚亩。看到這個的時候虏杰，查詢需要優(yōu)化了讥蟆。這里，mysql需要創(chuàng)建一個臨時表來存儲結(jié)果嘹屯，這通常發(fā)生在對不同的列集進行ORDER BY上攻询，而不是GROUP BY上。

Using where：如果我們不是讀取表的所有數(shù)據(jù)州弟，或者不是僅僅通過索引就可以獲取所有需
要的數(shù)據(jù)钧栖，則會出現(xiàn)Using where 信息；

**Where used **：使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶婆翔。如果不想返回表中的全部行拯杠，并且連接類型ALL或index，這就會發(fā)生啃奴，或者是查詢有問題潭陪。

Using index for group-by：數(shù)據(jù)訪問和Using index 一樣，所需數(shù)據(jù)只需要讀取索引即
可最蕾，而當Query 中使用了GROUP BY 或者DISTINCT 子句的時候依溯，如果分組字段也在索引
中，Extra 中的信息就會是Using index for group-by瘟则；

Using where with pushed condition：這是一個僅僅在NDBCluster 存儲引擎中才會出現(xiàn)
的信息黎炉，而且還需要通過打開Condition Pushdown 優(yōu)化功能才可能會被使用〈着。控制參數(shù)
為engine_condition_pushdown 慷嗜。

Full scan on NULL key：子查詢中的一種優(yōu)化方式，主要在遇到無法通過索引訪問null
值的使用使用丹壕；

Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通過
收集到的統(tǒng)計信息判斷出不可能存在結(jié)果庆械；

Select tables optimized away：當我們使用某些聚合函數(shù)來訪問存在索引的某個字段的
時候，MySQL Query Optimizer 會通過索引而直接一次定位到所需的數(shù)據(jù)行完成整個查
詢菌赖。當然缭乘，前提是在Query 中不能有GROUP BY 操作。如使用MIN()或者MAX（）的時
候琉用；

Range checked for each Record（index map:#） ：沒有找到理想的索引堕绩，因此對從前面表中來的每一個行組合薄啥，mysql檢查使用哪個索引，并用它來從表中返回行逛尚。這是使用索引的最慢的連接之一。

總結(jié)

因此刁愿，弄明白了explain語法返回的每一項結(jié)果绰寞，我們就能知道查詢大致的運行時間了，如果查詢里沒有用到索引铣口、或者需要掃描的行過多滤钱，那么可以感到明顯的延遲。因此需要改變查詢方式或者新建索引脑题。mysql中的explain語法可以幫助我們改寫查詢件缸，優(yōu)化表的結(jié)構(gòu)和索引的設(shè)置，從而最大地提高查詢效率叔遂。當然他炊，在大規(guī)模數(shù)據(jù)量時，索引的建立和維護的代價也是很高的已艰，往往需要較長的時間和較大的空間痊末，如果在不同的列組合上建立索引，空間的開銷會更大哩掺。因此索引最好設(shè)置在需要經(jīng)常查詢的字段中

參考：
https://blog.csdn.net/y41992910/article/details/79888276
https://www.cnblogs.com/xu-xiang/p/5833349.html