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

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

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 '用戶(hù)id',
	`login_name` VARCHAR (50) DEFAULT NULL COMMENT '登錄名',
	`password` VARCHAR (255) DEFAULT NULL COMMENT '密碼',
	`nick_name` VARCHAR (255) DEFAULT NULL COMMENT '用戶(hù)名稱(chēng)',
	`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 = '登錄用戶(hù)';

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

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

1、id列

id列的編號(hào)是select的序列號(hào)，有幾個(gè)select就有幾個(gè)id，并且id的順序是按select出現(xiàn)的順序增長(zhǎng)的。

id越大執(zhí)行優(yōu)先級(jí)越高，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個(gè)select查詢(xún)那么explain結(jié)果中有3個(gè)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表示對(duì)應(yīng)查詢(xún)的類(lèi)型，是簡(jiǎn)單還是復(fù)雜的查詢(xún)

select type的值有：

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

案例演示：

1、simple：簡(jiǎn)單查詢(xún)

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

4、derived：包含在from子句中的子查詢(xún)。MySQL會(huì)將結(jié)果存放在一個(gè)臨時(shí)表中，也稱(chēng)為派生表

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

3、table列

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

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

4、partitions

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

5、type列

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

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

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

2、system

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

3、const

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

因?yàn)橹黄ヅ湟粭l數(shù)據(jù),所以很快。

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

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

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

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

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

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

簡(jiǎn)單select查詢(xún)，name是普通索引（非主鍵索引或唯一索引）

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

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

使用一個(gè)索引來(lái)檢索給定范圍的行。

EXPLAIN select id from t_login_user where id > 1

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

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

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

6、possible_keys列

MySQL 能在該表中可能使用的索引，顯示的是索引的名稱(chēng)，多個(gè)索引用逗號(hào)隔開(kāi)。

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

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

7、key列

MySQL決定實(shí)際用到的索引，顯示的是索引的名稱(chēng)，多個(gè)索引用逗號(hào)隔開(kāi)，如果沒(méi)有，則為null

8、key_len列

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

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

字符串

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

數(shù)值類(lèi)型

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

時(shí)間類(lèi)型

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

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

索引最大長(zhǎng)度是768字節(jié)，當(dāng)字符串過(guò)長(zhǎng)時(shí)，MySQL會(huì)做一個(gè)類(lèi)似做前綴索引的處理，將前半部分的字符串提取出來(lái)做索引。

9、ref

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

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

條件等號(hào)右邊是常量 ref列就顯示const，如下

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

10、row列

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

11、filtered

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

12、Extra列

這一列是額外信息。

• Using index：使用覆蓋索引（查詢(xún)的字段是索引字段）

• Using where：使用where語(yǔ)句來(lái)處理結(jié)果，查詢(xún)的列未被索引覆蓋

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

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

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

總結(jié)

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

最新評(píng)論