一、準(zhǔn)備環(huán)境

1、新增用戶表，方便后續(xù)測試使用

CREATE TABLE `t_login_user` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`dateint` INT (11) NOT NULL COMMENT '年月份',
	`user_id` VARCHAR (50) NOT NULL COMMENT '用戶id',
	`login_name` VARCHAR (50) DEFAULT NULL COMMENT '登錄名',
	`password` VARCHAR (255) DEFAULT NULL COMMENT '密碼',
	`nick_name` VARCHAR (255) DEFAULT NULL COMMENT '用戶名稱',
	`fail_count` INT (11) NOT NULL DEFAULT '0' COMMENT '當(dāng)前連續(xù)失敗次數(shù)',
	`max_fail_count` INT (11) NOT NULL DEFAULT '6' COMMENT '允許的連續(xù)最大失敗次數(shù)',
	`is_locked` CHAR (1) NOT NULL DEFAULT 'n' COMMENT '是否被鎖定',
	PRIMARY KEY (`id`),
	UNIQUE KEY `unique_user_id` (`user_id`),
	KEY `idx_login_name` (`login_name`),
	KEY `dateint_login_name` (`dateint`, `login_name`)
) ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8 COMMENT = '登錄用戶';

2、數(shù)據(jù)表索引結(jié)構(gòu)

二、Explain執(zhí)行計劃中的列解釋

1、id列

id列的編號是select的序列號，有幾個select就有幾個id，并且id的順序是按select出現(xiàn)的順序增長的。

id越大執(zhí)行優(yōu)先級越高，id相同則從上往下執(zhí)行，id為NULL最后執(zhí)行。

EXPLAIN SELECT * FROM	t_login_user 
WHERE	id = (
	SELECT id FROM t_login_user WHERE user_id = (
		SELECT user_id from t_login_user where nick_name = '張三')
);

案例分析：

如上有3個select查詢那么explain結(jié)果中有3個id，sql得執(zhí)行順序是先執(zhí)行id為3的select（nick_name為張三作為條件查到user_id）,在執(zhí)行id為2的select（拿到上一步user_id作為條件查到id）,最后在執(zhí)行id為1的select (哪到上一步id作為條件查到數(shù)據(jù))。

2、select type列

select type表示對應(yīng)查詢的類型，是簡單還是復(fù)雜的查詢

select type的值有：

• 1、simple：簡單查詢。查詢不包含子查詢和union 2、primary：復(fù)雜查詢中最外層的select
• 3、subquery：包含在select中的子查詢（不在from子句中）
• 4、derived：包含在from子句中的子查詢。MySQL會將結(jié)果存放在一個臨時表中，也稱為派生表
• 5、union：在union關(guān)鍵字隨后的selelct

案例演示：

1、simple：簡單查詢

2、primary：復(fù)雜查詢中最外層的select 3、subquery：包含在select中的子查詢（不在from子句中）

4、derived：包含在from子句中的子查詢。MySQL會將結(jié)果存放在一個臨時表中，也稱為派生表

5、union：在union關(guān)鍵字隨后的selelct

3、table列

這一列表示explain的一行正在訪問哪個表。當(dāng)from子句中有子查詢時，table列是格式，表示當(dāng)前查詢依賴id=N的查詢，于是先執(zhí)行id=N的查詢。

當(dāng)有union時，UNION RESULT的table列的值為<union 1,2>，1和2表示參與union的select行id

4、partitions

partitions查詢涉及到的分區(qū)

5、type列

這一列表示關(guān)聯(lián)類型或訪問類型，即MySQL決定如何查找表中的行，查找數(shù)據(jù)行對應(yīng)的大概范圍。

依次從最優(yōu)到最差的分別為：system>const>eq_ref>ref>range>index>All一般來說，得保證查詢達(dá)到range級別，最好達(dá)到ref。

1、NULL：MySQL能夠在優(yōu)化階段分解查詢語句，在執(zhí)行階段用不著在訪問表或索引。例如：在索引列中選取最小值，可以單獨查找索引來完成，不需在執(zhí)行時訪問表。

2、system

訪問類型最高的,屬于const類型的特例,表只有一條記錄行(系統(tǒng)表)

3、const

表示通過索引一次就能找到, const 用于比較primary或者unique(值是唯一的)。

因為只匹配一條數(shù)據(jù),所以很快。

如果將主鍵置于where 子句中,mysql就能將該查詢轉(zhuǎn)為一個常量

4、eq_ref：eq_ref用于聯(lián)合表的查詢。

primay key或 unique key索引的所有部分被連接使用，最多只會返回一條符合條件的記錄

如下面sql，對于前表t_login_user表中的每一行（row），對應(yīng)后dept 表只有一行被掃描

EXPLAIN select A.id from t_login_user A LEFT JOIN dept B USING(id) --  USING(id)等價于是 on A.id = B.id

5、ref：相比eq_ref，不適用唯一索引，而是使用普通索引或者唯一索引的部分前綴，索引要和某個值相比較，可能會找到多個符合條件的行。

簡單select查詢，name是普通索引（非主鍵索引或唯一索引）

EXPLAIN select id from t_login_user where login_name= "張三"

6、range：范圍掃描通常出現(xiàn)在in(), between,>,<,>=等操作中。

使用一個索引來檢索給定范圍的行。

EXPLAIN select id from t_login_user where id > 1

7、index：掃描全表索引，通常比All快一些

8、all：即全表掃描，意味著MySQL需要從頭到尾去查找所需要的行。

這種情況下需要增加索引來進(jìn)行優(yōu)化。

6、possible_keys列

MySQL 能在該表中可能使用的索引，顯示的是索引的名稱，多個索引用逗號隔開。

在explain時可能會出現(xiàn)possible_keys有索引key值，而key顯示為NULL的情況，這種情況是因為表中的數(shù)據(jù)不多，MySQL認(rèn)為索引對此查詢幫助不大，選擇了全表掃描。

如果該列為NULL，則沒有相關(guān)的索引

7、key列

MySQL決定實際用到的索引，顯示的是索引的名稱，多個索引用逗號隔開，如果沒有，則為null

8、key_len列

這一列顯示了mysql在索引里使用的字節(jié)數(shù)，通過這個值可以估算出具體使用了索引中的哪些列。

ken_len計算規(guī)則如下： 

字符串

• char(n)：n字節(jié)長度
• varchar(n)：n字節(jié)存儲字符串長度，如果是utf-8， 則長度是3n+2

數(shù)值類型

• tinyint：1字節(jié)
• smallint：2字節(jié)
• int：4字節(jié)
• bigint：8字節(jié)

時間類型

• date：3字節(jié)
• timestamp：4字節(jié)
• datetime：8字節(jié)

如果字段允許為NULL，需要1字節(jié)記錄是否為NULL

索引最大長度是768字節(jié)，當(dāng)字符串過長時，MySQL會做一個類似做前綴索引的處理，將前半部分的字符串提取出來做索引。

9、ref

這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量，常見的有： const(常量)，字段名、表名等。

一般是查詢條件或關(guān)聯(lián)條件中等號右邊的值，如果是常量那么ref列是const，非常量的話ref列就是字段名。

條件等號右邊是常量 ref列就顯示const，如下

條件等號右邊是非常量 ref列就顯示字段名或者表名

10、row列

這一列是mysql估計要讀取并檢測的行數(shù)，注意這個不是結(jié)果集的行數(shù)。

11、filtered

filtered表示返回結(jié)果的行數(shù)占需讀取行數(shù)的百分比，filtered列的值依賴于統(tǒng)計信息。

12、Extra列

這一列是額外信息。

• Using index：使用覆蓋索引（查詢的字段是索引字段）

• Using where：使用where語句來處理結(jié)果，查詢的列未被索引覆蓋

• Using temporary：mysql需要創(chuàng)建一張臨時表來處理查詢。出現(xiàn)這種情況一般要進(jìn)行優(yōu)化，首先要想到是索引優(yōu)化。

• Using filesort：將用外部排序而不是索引排序，數(shù)據(jù)較小時從內(nèi)存排序，否則需要在磁盤完成排序。這種情況下一般也是要考慮使用索引來優(yōu)化的。

• select tables optimized away：使用某些聚合函數(shù)（比如：max、min）來訪問存在索引的某個字段

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論