導語:
很多情況下,有很多人用各種select語句查詢到了他們想要的數(shù)據(jù)后,往往便以為工作圓滿結(jié)束了。
這些事情往往發(fā)生在一些學生亦或剛?cè)肼殘龅坝譀]有很好數(shù)據(jù)庫基礎(chǔ)的小白身上,但所謂聞道有先后,只要我們小白好好學習,天天向上,還是很靠譜的。
當一個sql查詢語句被寫出來之后,其實你的工作只完成了一小半,接下來更重要的工作是評估你自己寫的sql的質(zhì)量與效率。mysql為我們提供了很有用的輔助武器explain,它向我們展示了mysql接收到一條sql語句的執(zhí)行計劃。根據(jù)explain返回的結(jié)果我們便可以知道我們的sql寫的怎么樣,是否會造成查詢瓶頸,同時根據(jù)結(jié)果不斷的修改調(diào)整查詢語句,從而完成sql優(yōu)化的過程。
雖然 explain返回的結(jié)果項很多,這里我們只關(guān)注三種,分別是type,key,rows。其中key表明的是這次查找中所用到的索引,rows是指這次查找數(shù)據(jù)所掃描的行數(shù)(這里可以先這樣理解,但實際上是內(nèi)循環(huán)的次數(shù))。而type則是本文要詳細記錄的連接類型,前兩項重要而且簡單,無需多說。
type -- 連接類型
type意味著類型,這里的type官方全稱是“join type”,意思是“連接類型”,這樣很容易給人一種錯覺覺得必須需要倆個表以上才有連接類型。事實上這里的連接類型并非字面那樣的狹隘,它更確切的說是一種數(shù)據(jù)庫引擎查找表的一種方式,在《高性能mysql》一書中作者更是覺得稱呼它為訪問類型更貼切一些。
mysql5.7中type的類型達到了14種之多,這里只記錄和理解最重要且經(jīng)常遇見的六種類型,它們分別是all,index,range,ref,eq_ref,const。從左到右,它們的效率依次是增強的。撇開sql的具體應(yīng)用環(huán)境以及其他因素,你應(yīng)當盡量優(yōu)化你的sql語句,使它的type盡量靠右,但實際運用中還是要綜合考慮各個方面的。
接下來,為了演示和重現(xiàn)這幾種連接類型,我新建了一個數(shù)據(jù)測試表,以方面更好的理解這五種類型。
| employee | CREATE TABLE `employee` (
`rec_id` int(11) NOT NULL AUTO_INCREMENT,
`no` varchar(10) NOT NULL,
`name` varchar(20) NOT NULL,
`position` varchar(20) NOT NULL,
`age` varchar(2) NOT NULL,
PRIMARY KEY (`rec_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
all
這便是所謂的“全表掃描”,如果是展示一個數(shù)據(jù)表中的全部數(shù)據(jù)項,倒是覺得也沒什么,如果是在一個查找數(shù)據(jù)項的sql中出現(xiàn)了all類型,那通常意味著你的sql語句處于一種最原生的狀態(tài),有很大的優(yōu)化空間。
為什么這么說呢?因為all是一種非常暴力和原始的查找方法,非常的耗時而且低效。用all去查找數(shù)據(jù)就好比這樣的一個情形:S學校有倆萬人,我告訴你你給我找到小明,然后你怎么做呢!你當然是把全校倆萬人挨個找一遍,即使你很幸運第一個人便找到了小明,但是你仍然不能停下,因為你無法確認是否有另外一個小明存在,直到你把倆萬人找完為止。所以,基本所有情況,我們都要避免這樣類型的查找,除非你不得不這樣做。
以employee表為例,下面一種情形便是all類型的查找:
mysql> explain select * from employee where `no` = '20150001';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employee | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
這是因為no列既不是主鍵也不是索引,因此只能采用全表掃描來查找目標no。
index
這種連接類型只是另外一種形式的全表掃描,只不過它的掃描順序是按照索引的順序。這種掃描根據(jù)索引然后回表取數(shù)據(jù),和all相比,他們都是取得了全表的數(shù)據(jù),而且index要先讀索引而且要回表隨機取數(shù)據(jù),因此index不可能會比all快(取同一個表數(shù)據(jù)),但為什么官方的手冊將它的效率說的比all好,唯一可能的原因在于,按照索引掃描全表的數(shù)據(jù)是有序的。這樣一來,結(jié)果不同,也就沒法比效率的問題了。
如果一定要比效率,只需要獲取這個表的數(shù)據(jù)并且排序便可以看出來誰比誰效率高了:
mysql> explain select * from employee order by `no` ;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | employee | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
mysql> explain select * from employee order by rec_id ;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
| 1 | SIMPLE | employee | index | NULL | PRIMARY | 4 | NULL | 5 | NULL |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
上面可以看出,根據(jù)no列排序的連接類型是all型的,但是注意extra列是用到了排序(Using filesort),而根據(jù)rec_id列排序的連接類型是index,而且得到的結(jié)果自然是有序的,不許額外的排序。可能正是因為這個緣故,index的效率比all高,但注意這需要相同的條件才成立(既需要排序)。
如果連接類型為type,而且extra列中的值為‘Using index',那么稱這種情況為 索引覆蓋;
索引覆蓋意味著什么呢?想象這樣一種場景,如果說一本新華字典是一張表,當然前面的索引部分(假設(shè)按照部首的索引)是這張表的索引,那么索引覆蓋就相當于根據(jù)部首索引獲取第一個字到最后一個字(新華字典的所有字)。我們獲得了字典中所有的字,然而我們并沒有查一次表,因為我們想要的都早索引中,即索引覆蓋。
mysql> explain select rec_id from employee ;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | employee | index | NULL | PRIMARY | 4 | NULL | 5 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
上例獲取的rec_id剛好為索引列,因此無需回表取數(shù)據(jù)。
range
range指的是有范圍的索引掃描,相對于index的全索引掃描,它有范圍限制,因此要優(yōu)于index。關(guān)于range比較容易理解,需要記住的是出現(xiàn)了range,則一定是基于索引的。同時除了顯而易見的between,and以及'>',''外,in和or也是索引范圍掃描。
ref
出現(xiàn)該連接類型的條件是: 查找條件列使用了索引而且不為主鍵和unique。其實,意思就是雖然使用了索引,但該索引列的值并不唯一,有重復。這樣即使使用索引快速查找到了第一條數(shù)據(jù),仍然不能停止,要進行目標值附近的小范圍掃描。但它的好處是它并不需要掃全表,因為索引是有序的,即便有重復值,也是在一個非常小的范圍內(nèi)掃描。下面為了演示這種情形,給employee表中的name列添加一個普通的key(值允許重復)
alter table employee add key I_EMPLOYEE_NAME(`name`);
接下來,在employee表中根據(jù)name查找數(shù)據(jù)的時候,mysql優(yōu)化器便選擇了ref的連接類型。
mysql> explain select * from employee where `name` = '張三';
+----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | employee | ref | I_EMPLOYEE_NAM | I_EMPLOYEE_NAM | 62 | const | 1 | Using index condition |
+----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+
ref_eq
ref_eq 與 ref相比牛的地方是,它知道這種類型的查找結(jié)果集只有一個?什么情況下結(jié)果集只有一個呢!那便是使用了主鍵或者唯一性索引進行查找的情況,比如根據(jù)學號查找某一學校的一名同學,在沒有查找前我們就知道結(jié)果一定只有一個,所以當我們首次查找到這個學號,便立即停止了查詢。這種連接類型每次都進行著精確查詢,無需過多的掃描,因此查找效率更高,當然列的唯一性是需要根據(jù)實際情況決定的。
在單個表中,曾嘗試了很多方法想出現(xiàn)ref_eq的連接類型,然而很多時候出現(xiàn)的都是const,因此不得不隨手連接了一張表得到了想要的連接類型,該表的建表代買為。(博主比較懶,連接了兩個沒有關(guān)系的表,o(╯□╰)o)
CREATE TABLE `score` (
`rec_id` INT(11) NOT NULL AUTO_INCREMENT,
`stu_id` INT(11) NOT NULL,
`mark` INT(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`rec_id`),
UNIQUE KEY `UK_SCORE_STU_ID` (`stu_id`)
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
employee表中有五條數(shù)據(jù),score表中有對應(yīng)的五條數(shù)據(jù),其中employee的rec_id 和score的stu_id 是一一對應(yīng)的。
mysql> explain select ep.name,sc.mark from employee ep,score sc where ep.rec_id = sc.stu_id;
+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+
| 1 | SIMPLE | sc | ALL | UK_SCORE_STU_ID | NULL | NULL | NULL | 5 | NULL |
| 1 | SIMPLE | ep | eq_ref | PRIMARY | PRIMARY | 4 | my_db.sc.stu_id | 1 | NULL |
+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+
上面就可以看到score表是全表掃描的類型,rows=5代表外層表循環(huán)了五次(因為有五條數(shù)據(jù)),但是employee表的rows怎么是1,怎么可能?剛開始也是很疑惑,這與mysql的查詢原理息息相關(guān),rows實際反映的是查詢的內(nèi)循環(huán)數(shù),針對外層的每一條數(shù)據(jù)匹配,employee的確一槍就可以命中,因此rows為1。
const
通常情況下,如果將一個主鍵放置到where后面作為條件查詢,mysql優(yōu)化器就能把這次查詢優(yōu)化轉(zhuǎn)化為一個常量。至于如何轉(zhuǎn)化以及何時轉(zhuǎn)化,這個取決于優(yōu)化器。
總結(jié)
explain 就像一面鏡子,有事沒事寫完sql記得explain一下。同時,在寫文章也發(fā)現(xiàn),有很多東西和細節(jié),想要明白清楚,也是沒有那么簡單的,需要對操作系統(tǒng)以及數(shù)據(jù)庫的底層查詢和運行原理要有一個清楚的理解。同時type的幾種類型幾乎都是基于索引之上的,因此需要對索引有個深入的了解,而且explain的結(jié)果可以指導我們什么時候加索引,什么時候不加索引,從而讓我們更好的使用索引。
以上就是詳解mysql中explain的type的詳細內(nèi)容,更多關(guān)于mysql中explain的type的資料請關(guān)注腳本之家其它相關(guān)文章!
您可能感興趣的文章:- MySQL查詢語句過程和EXPLAIN語句基本概念及其優(yōu)化
- mysql開啟慢查詢(EXPLAIN SQL語句使用介紹)
- mysql explain的用法(使用explain優(yōu)化查詢語句)
- Mysql調(diào)優(yōu)Explain工具詳解及實戰(zhàn)演練(推薦)
- Mysql explain用法與結(jié)果深入分析
- MySQL如何基于Explain關(guān)鍵字優(yōu)化索引功能
- Mysql深入探索之Explain執(zhí)行計劃詳析
- MySQL索引優(yōu)化Explain詳解
- MYSQL 性能分析器 EXPLAIN 用法實例分析
- MySQL中通過EXPLAIN如何分析SQL的執(zhí)行計劃詳解
- MySQL EXPLAIN語句的使用示例