一榄檬、Mysql索引基于B+樹(shù)
B+樹(shù)基于平衡二叉查找樹(shù)和B+樹(shù)。所謂平衡二叉查找樹(shù)衔统,就是任意節(jié)點(diǎn)的2個(gè)子樹(shù)的最大高度差是1鹿榜。平衡二叉樹(shù)比非平衡二叉樹(shù)的查找效率要高的多,平均時(shí)間復(fù)雜度是O(log2n)锦爵。為了保持二叉樹(shù)的平衡性舱殿,插入和刪除節(jié)點(diǎn)時(shí)往往要進(jìn)行左旋和右旋操作。
B+樹(shù)是為磁盤(pán)和其他直接存取輔助設(shè)備設(shè)計(jì)的一種平衡查找樹(shù)棉浸。在B+樹(shù)中怀薛,所有記錄節(jié)點(diǎn)都是按鍵值大小順序存放在同一層的葉子節(jié)點(diǎn)上刺彩,由各葉子節(jié)點(diǎn)指針進(jìn)行連接迷郑。B+樹(shù)的查找時(shí)間復(fù)雜度是O(logmn)枝恋,其中m是節(jié)點(diǎn)的子樹(shù)個(gè)數(shù)。
?B+樹(shù)的特點(diǎn):1. 是一棵n叉樹(shù)嗡害;2. 節(jié)點(diǎn)有序焚碌;3. 非葉子節(jié)點(diǎn)不存儲(chǔ)數(shù)據(jù)只存儲(chǔ)索引,同樣大小的磁盤(pán)頁(yè)可以容納更多的節(jié)點(diǎn)元素霸妹。所以數(shù)據(jù)量相同的情況下十电,B+樹(shù)比B樹(shù)更加“矮胖“,因此使用的IO查詢(xún)次數(shù)更少叹螟。
B+樹(shù)的插入操作是通過(guò)頁(yè)的拆分和旋轉(zhuǎn)來(lái)維持B+樹(shù)的平衡鹃骂,刪除操作由于受填充因子的影響需要頁(yè)合并來(lái)維持平衡。
在數(shù)據(jù)庫(kù)中B+樹(shù)的高度一般在2-4層罢绽,也就是說(shuō)查找某一鍵值的行記錄時(shí)最多需要2到4次IO畏线。
二、索引分為聚集索引和輔助索引
聚集索引:葉子節(jié)點(diǎn)存放整行數(shù)據(jù)良价,通常主鍵索引即為聚集索引寝殴。在Innodb存儲(chǔ)引擎中,存儲(chǔ)引擎表就是索引組織表明垢,表中數(shù)據(jù)就是按照主鍵順序存放的蚣常。頁(yè)內(nèi)的數(shù)據(jù)以及頁(yè)之間都是以雙向鏈表連接。通過(guò)聚集索引樹(shù)進(jìn)行搜索痊银,可以直接查找到整行數(shù)據(jù)抵蚊。
輔助索引:葉子節(jié)點(diǎn)存儲(chǔ)的是鍵值以及主鍵。在Innodb存儲(chǔ)引擎中溯革,通過(guò)輔助索引樹(shù)進(jìn)行搜索找到的是聚集索引鍵泌射,因此如果要找到整行數(shù)據(jù)還需要通過(guò)聚集索引樹(shù)搜索,這個(gè)過(guò)程稱(chēng)為回表鬓照。
三熔酷、查看索引
查看索引的命令:show index from table。一個(gè)只有主鍵索引的表執(zhí)行改命令得到的結(jié)果可能如下:
結(jié)果中字段解釋如下:
1. Table:表名稱(chēng)豺裆;
2. Non_unique:該索引是否包含重復(fù)值拒秘。主鍵索引的列值是唯一的。
3. Key_name:索引名稱(chēng)臭猜。PRIMARY代表主鍵索引躺酒。
4. Seq_in_index: 索引中序列的序列號(hào),從1開(kāi)始,如果是組合索引 那么按照字段在建立索引時(shí)的順序排列 如 ('c1', 'c2', 'c3') 那么 分別為 1, 2, 3。
5. Column_name:列的名稱(chēng)蔑歌。
6. Collation: 列以什么方式存儲(chǔ)在索引中羹应。在MySQL中,有值‘A’(升序)或NULL(無(wú)分序)次屠。
7. Cardinality:索引中唯一值的數(shù)目的估計(jì)值园匹,優(yōu)化器會(huì)根據(jù)這個(gè)值來(lái)判斷是否使用這個(gè)索引雳刺。對(duì)于大表而言計(jì)算這個(gè)值可能需要很長(zhǎng)時(shí)間,因此這個(gè)值并不是實(shí)時(shí)更新的裸违。通過(guò)運(yùn)行ANALYZE TABLE 或者 myisamchk -a 來(lái)更新掖桦。
8. Sub_part:索引的長(zhǎng)度,如果是部分被編入索引 則該值表示索引的長(zhǎng)度 ,如果是整列被編入索引則為null。
9. Packed:指示關(guān)鍵字如何被壓縮供汛。如果沒(méi)有被壓縮枪汪,則為NULL。
10. Null:如果該列的值有NULL怔昨,則是YES雀久,否則為NO。
11. Index_type:索引類(lèi)型(BTREE, FULLTEXT, HASH, RTREE)
12. Commnet:關(guān)于在其列中沒(méi)有描述的索引的信息趁舀。
13. Index_comment:為索引創(chuàng)建時(shí)提供了一個(gè)注釋屬性的索引的任何評(píng)論岸啡。
四、管理索引
1. 創(chuàng)建索引
創(chuàng)建索引可以使用 create index 或者 alter table xxx add index 語(yǔ)句來(lái)實(shí)現(xiàn)赫编。
用戶(hù)可以設(shè)置對(duì)整個(gè)列進(jìn)行索引巡蘸,也可以只索引一個(gè)列的開(kāi)頭部分?jǐn)?shù)據(jù)。
一般來(lái)說(shuō)擂送,在WHERE和JOIN中出現(xiàn)的列需要建立索引悦荒,但也不完全如此,因?yàn)镸ySQL只對(duì)<嘹吨,<=搬味,=,>蟀拷,>=碰纬,BETWEEN,IN问芬,以及某些時(shí)候的LIKE才會(huì)使用索引悦析。
2. 刪除索引
刪除索引可以使用 drop index 或者 alter table xxx drop index 語(yǔ)句來(lái)實(shí)現(xiàn)。
五此衅、cardinality
一般而言强戴,高選擇性的列創(chuàng)建索引比較合適。所謂高選擇性挡鞍,即不同值的各數(shù)和記錄總數(shù)越接近越好骑歹。可以通過(guò) show index 的 cardinality 來(lái)觀察墨微。
1. cardinality 何時(shí)更新:表中 1/16的數(shù)據(jù)發(fā)生過(guò)變化 或者 表中數(shù)據(jù)發(fā)生活 20億次變化道媚;執(zhí)行語(yǔ)句 analyze table、show table status、show index 時(shí)會(huì)觸發(fā)更新最域。
2. cardinality 如何計(jì)算:該值是基于采樣統(tǒng)計(jì)出來(lái)的(在葉子節(jié)點(diǎn)中隨機(jī)取8個(gè)節(jié)點(diǎn)谴分,按照 n *N /8 計(jì)算而來(lái)。n為這8個(gè)節(jié)點(diǎn)中不同值的個(gè)數(shù))羡宙,且每次結(jié)果可能不同狸剃。
六掐隐、聯(lián)合索引
假定2個(gè)鍵值的名稱(chēng)分別是a狗热、b,創(chuàng)建索引(a,b)虑省,其B+樹(shù)如下圖所示匿刮。
????? 聯(lián)合索引的鍵值也是按照邏輯順序排序的。
????? 對(duì)于查詢(xún) SELECT * FROM TABLE WHERE a=xxx and b=xxx探颈,可以使用到(a,b)這個(gè)聯(lián)合索引熟丸,對(duì)于單個(gè)a列的查詢(xún) SELECT * FROM TABLE WHERE a=xxx也可以使用(a,b)這個(gè)聯(lián)合索引,但是對(duì)于b列的查詢(xún)則無(wú)法使用到這個(gè)聯(lián)合索引伪节。因?yàn)閎列的值分別為1光羞、2、1怀大、4纱兑、1、2化借,顯然不是排過(guò)序的潜慎。
????? 當(dāng)有單個(gè)索引和聯(lián)合索引都可以使用時(shí),優(yōu)化器可能會(huì)選擇單個(gè)索引蓖康,因?yàn)閱蝹€(gè)索引理論上一頁(yè)存放的節(jié)點(diǎn)更多铐炫,IO次數(shù)更少。
????? 如果聯(lián)合索引能夠覆蓋排序字段的話(huà)蒜焊,優(yōu)化器可能會(huì)選擇聯(lián)合索引倒信,因?yàn)檫@樣無(wú)須再做額外的排序。仍然以上圖為例泳梆,假設(shè)有列a上的索引index_a以及列(a,b)上的聯(lián)合索引index_a_b堤结,對(duì)于查詢(xún)語(yǔ)句 SELECT * FROM TABLE WHERE a=xxx order by b,優(yōu)化器將會(huì)選擇index_a_b鸭丛。
七竞穷、索引覆蓋
????? 所謂索引覆蓋指的是不需要通過(guò)回表,只需要查詢(xún)非聚集索引就能夠查到需要的所有字段鳞溉。在SQL執(zhí)行計(jì)劃里瘾带,如果Extra信息包括 Using Index 則表明使用了覆蓋索引。
????? 優(yōu)化器會(huì)根據(jù)where條件和select_list里面的字段決定在使用一個(gè)索引(sta)后熟菲,是否需要回表—回到聚集索引取數(shù)據(jù)看政∑涌遥基本的做法是:在確定了一個(gè)索引后,將select_list和where中出現(xiàn)的所有字段都拿來(lái)判斷一下允蚣,如果字段都存在于sta索引中于颖,則可以使用覆蓋索引。如現(xiàn)有索引(a,b)嚷兔,語(yǔ)句 select count (*) from table where b > 10 and b < 20 是可以用到覆蓋索引的森渐,因?yàn)槁?lián)合索引 (a, b) 覆蓋了查詢(xún)需要的字段b,換句話(huà)說(shuō)冒晰,通過(guò)索引(a,b)的索引樹(shù)查找到葉子節(jié)點(diǎn)并按照條件進(jìn)行過(guò)濾即可得到結(jié)果同衣,無(wú)需回表。
????? 當(dāng)有多個(gè)索引能夠覆蓋掃描時(shí)壶运,優(yōu)化器會(huì)選擇效率最高的 ( IO次數(shù)最少的)耐齐。例如對(duì)于 SELECT COUNT(*) FROM TABLE 這樣的查詢(xún),如有主鍵索引和非聚簇索引蒋情,優(yōu)化器會(huì)選擇非聚簇索引埠况,因?yàn)榉蔷鄞厮饕?jié)點(diǎn)數(shù)據(jù)更小,io次數(shù)更少棵癣。
八辕翰、不使用索引的情況
??????? 對(duì)于不能進(jìn)行索引覆蓋的情況,優(yōu)化器選擇輔助索引的情況是:通過(guò)輔助索引查找到的數(shù)據(jù)是少量的浙巫。這是由當(dāng)前硬盤(pán)的特性所決定的金蜀,即利用順序讀來(lái)替換隨機(jī)讀的查找。若用戶(hù)使用的磁盤(pán)是固態(tài)硬盤(pán)的畴,隨機(jī)讀操作非吃ǔ快,同時(shí)有足夠的自信來(lái)確認(rèn)使用輔助索引可以帶來(lái)更好的性能丧裁,那么可以使用關(guān)鍵字 FORCE INDEX 來(lái)強(qiáng)制使用某個(gè)索引护桦。
??????? 假設(shè)表employees上有索引 index_hire_date(hire_date),語(yǔ)句 select * from employees where hire_date > '1990-01-01' 并不會(huì)使用輔助索引而是全表掃描煎娇,正是因?yàn)椴樵?xún)條件查找到的數(shù)據(jù)量可能很大二庵,對(duì)這些數(shù)據(jù)隨機(jī)讀取數(shù)據(jù)行會(huì)很耗時(shí)。而 語(yǔ)句 select * from employees where hire_date = '1990-01-01' 則會(huì)使用輔助索引缓呛。
九催享、MRR優(yōu)化
??????? Multi-Range Read優(yōu)化的目的是減少磁盤(pán)隨機(jī)訪問(wèn),將隨機(jī)訪問(wèn)轉(zhuǎn)換為順序訪問(wèn)哟绊。其工作方式為:1. 將通過(guò)輔助索引查到的輔助索引鍵和主鍵對(duì)存儲(chǔ)在緩存中因妙;2. 緩存滿(mǎn)了后根據(jù)主鍵進(jìn)行排序;3. 根據(jù)排序后的主鍵來(lái)訪問(wèn)表中數(shù)據(jù)。
??????? 給出一個(gè)簡(jiǎn)單的例子攀涵,在innodb表執(zhí)行下面的查詢(xún):
SELECT non_key_column FROM tbl WHERE key_column=x
??????? 在沒(méi)有MRR的情況下铣耘,它是這樣得到結(jié)果的:
1. select key_column, pk_column from tb where key_column=x order by key_column ---> 假設(shè)這個(gè)結(jié)果集是t
2. for each row in t ; select non_key_column from tb where pk_column = pk_column_value。(在oracle里第2步叫回表)
??????? 在有MRR的情況下以故,它是這樣執(zhí)行的:
1. select key_column, pk_column from tb where key_column = x order by key_column ---> 假設(shè)這個(gè)結(jié)果集是t
2. 將結(jié)果集t放在buffer里面(直到buffer滿(mǎn)了)蜗细,然后對(duì)結(jié)果集t按照pk_column排序 ---> 假設(shè)排序好的結(jié)果集是t_sort
3. select non_key_column fromtb where pk_column in (select pk_column from t_sort)
??????? 兩者的區(qū)別主要是兩點(diǎn):
1. 沒(méi)有MRR的情況下,隨機(jī)IO增加怒详,因?yàn)閺亩?jí)索引里面得到的索引元組是有序炉媒,但是他們?cè)谥麈I索引里面卻是無(wú)序的,所以每次去主鍵索引里面得到non_key_column的時(shí)候都是隨機(jī)IO棘利。(如果索引覆蓋橱野,那也就沒(méi)必要利用MRR的特性了朽缴,直接從索引里面得到所有數(shù)據(jù))
2. 沒(méi)有MRR的情況下善玫,訪問(wèn)主鍵索引的次數(shù)增加。沒(méi)有MRR的情況下密强,二級(jí)索引里面得到多少行茅郎,那么就要去訪問(wèn)多少次主鍵索引(也不能完全這樣說(shuō),因?yàn)閙ysql實(shí)現(xiàn)了BNL)或渤,而有了MRR的時(shí)候系冗,次數(shù)就大約減少為之前次數(shù)t/buffer_size。
??????? 此外薪鹦,MRR還會(huì)將某些范圍查詢(xún)拆分成鍵值對(duì)掌敬,以此來(lái)進(jìn)行批量的數(shù)據(jù)查詢(xún)。例如:SELECT * FROM t WHERE key_column1 > 1000 AND key_column1 < 2000 and key_column2 = 10000池磁,表中有(key_column1, key_column2)的聯(lián)合索引奔害。如果沒(méi)有MRR,優(yōu)化器會(huì)在聯(lián)合索引樹(shù)中取出 key_column1 在1000到2000之間的數(shù)據(jù)地熄,再過(guò)濾出來(lái) key_column2 =10000的數(shù)據(jù)华临。這會(huì)導(dǎo)致大量無(wú)用數(shù)據(jù)被取出。啟用MRR后端考,優(yōu)化器將查詢(xún)條件拆分為 (1000, 100000), (1001, 10000)……(1999, 10000)雅潭,直接查詢(xún)出數(shù)據(jù)。
十却特、Index Condition Pushdown 優(yōu)化
??????? 在開(kāi)啟ICP之前扶供,優(yōu)化器根據(jù)索引取得主鍵后,再根據(jù)主鍵取得相應(yīng)記錄后再進(jìn)行篩選裂明。
??????? 開(kāi)啟ICP后椿浓,優(yōu)化器在取出索引的同時(shí)會(huì)判斷是否可以進(jìn)行where條件的過(guò)濾,如果可以過(guò)濾則直接進(jìn)行過(guò)濾,然后再取得相應(yīng)的記錄轰绵。
??????? 假設(shè)某張表有聯(lián)合索引(zip_code, last_name, first_name)粉寞,對(duì)于查詢(xún)語(yǔ)句 SELECT * FROM TABLE WHERE zip_code = '90988' And last_name LIKE '%etrunia%' AND address LIKE '%Main Street%'。由于2個(gè)like條件是非固定前綴匹配左腔,因此只能通過(guò)該索引匹配出zip_code=90988的索引節(jié)點(diǎn)唧垦。如果不開(kāi)啟ICP,則數(shù)據(jù)庫(kù)需要先通過(guò)索引取出所有zip_code = 90988 的記錄液样,然后再過(guò)濾where條件振亮。開(kāi)后ICP后,取出 zip_code = 90988的同時(shí)會(huì)在索引中過(guò)濾出來(lái) last_name 和 address 符合條件的索引鞭莽,再取出相關(guān)記錄坊秸。
??????? 使用了ICP的執(zhí)行計(jì)劃Extra列會(huì)顯示 Using Index Condition。
十一澎怒、哈希索引
??????? Innodb引擎本身不支持手動(dòng)創(chuàng)建哈希索引褒搔,Innodb存儲(chǔ)引擎會(huì)監(jiān)控對(duì)表上二級(jí)索引的查找,如果發(fā)現(xiàn)某二級(jí)索引被頻繁訪問(wèn)喷面,二級(jí)索引成為熱數(shù)據(jù)星瘾,會(huì)自動(dòng)建立哈希索引來(lái)提升速度。
??????? 哈希表也為散列表惧辈,又直接尋址改進(jìn)而來(lái)琳状。在哈希的方式下,一個(gè)元素k處于h(k)中盒齿,即利用哈希函數(shù)h念逞,根據(jù)關(guān)鍵字k計(jì)算出槽的位置。函數(shù)h將關(guān)鍵字域映射到哈希表T[0...m-1]的槽位上边翁。有可能將兩個(gè)不同的關(guān)鍵字映射到相同的位置翎承,這叫做碰撞,在數(shù)據(jù)庫(kù)中一般采用鏈接法來(lái)解決倒彰。在鏈接法中审洞,將散列到同一槽位的元素放在一個(gè)鏈表中。
??????? Hash索引結(jié)構(gòu)的特殊性待讳,其檢索效率非常高芒澜,索引的檢索可以一次定位,不像B-Tree索引需要從根節(jié)點(diǎn)到枝節(jié)點(diǎn)创淡,最后才能訪問(wèn)到頁(yè)節(jié)點(diǎn)這樣多次的IO訪問(wèn)痴晦,所以Hash索引的查詢(xún)效率要遠(yuǎn)高于B-Tree索引。
??????? 哈希索引的限制:
??????? 1. 哈希索引只包含哈希值和行指針琳彩,而不存儲(chǔ)字段值誊酌,所以不能使用索引中的值來(lái)避免讀取行(即不能使用哈希索引來(lái)做覆蓋索引掃描)部凑,不過(guò),訪問(wèn)內(nèi)存中的行的速度很快(因?yàn)閙emory引擎的數(shù)據(jù)都保存在內(nèi)存里)碧浊,所以大部分情況下這一點(diǎn)對(duì)性能的影響并不明顯涂邀。
??????? 2. 哈希索引數(shù)據(jù)并不是按照索引列的值順序存儲(chǔ)的,所以也就無(wú)法用于排序
??????? 3. 哈希索引也不支持部分索引列匹配查找箱锐,因?yàn)楣K饕冀K是使用索引的全部列值內(nèi)容來(lái)計(jì)算哈希值的比勉。如:數(shù)據(jù)列(a,b)上建立哈希索引,如果只查詢(xún)數(shù)據(jù)列a驹止,則無(wú)法使用該索引浩聋。
??????? 4. 哈希索引只支持等值比較查詢(xún),如:=,in(),<=>(注意臊恋,<>和<=>是不同的操作)衣洁,不支持任何范圍查詢(xún)(必須給定具體的where條件值來(lái)計(jì)算hash值,所以不支持范圍查詢(xún))抖仅。
??????? 5. 訪問(wèn)哈希索引的數(shù)據(jù)非撤环颍快,除非有很多哈希沖突岸售,當(dāng)出現(xiàn)哈希沖突的時(shí)候践樱,存儲(chǔ)引擎必須遍歷鏈表中所有的行指針厂画,逐行進(jìn)行比較凸丸,直到找到所有符合條件的行。
??????? 6. 如果哈希沖突很多的話(huà)袱院,一些索引維護(hù)操作的代價(jià)也很高屎慢,如:如果在某個(gè)選擇性很低的列上建立哈希索引(即很多重復(fù)值的列),那么當(dāng)從表中刪除一行時(shí)忽洛,存儲(chǔ)引擎需要遍歷對(duì)應(yīng)哈希值的鏈表中的每一行腻惠,找到并刪除對(duì)應(yīng)的引用,沖突越多欲虚,代價(jià)越大集灌。
十二、 Mysql 執(zhí)行計(jì)劃
各字段詳解
id
select查詢(xún)的序列號(hào)复哆,包含一組數(shù)字欣喧,表示查詢(xún)中執(zhí)行select子句或操作表的順序?。
select_type
查詢(xún)的類(lèi)型梯找,主要是用于區(qū)分普通查詢(xún)唆阿、聯(lián)合查詢(xún)、子查詢(xún)等復(fù)雜的查詢(xún)
1锈锤、SIMPLE:簡(jiǎn)單的select查詢(xún)驯鳖,查詢(xún)中不包含子查詢(xún)或者union
2闲询、PRIMARY:查詢(xún)中包含任何復(fù)雜的子部分,最外層查詢(xún)則被標(biāo)記為primary
3浅辙、SUBQUERY:在select 或 where列表中包含了子查詢(xún)
4扭弧、DERIVED:在from列表中包含的子查詢(xún)被標(biāo)記為derived(衍生),mysql或遞歸執(zhí)行這些子查詢(xún)记舆,把結(jié)果放在零時(shí)表里
5寄狼、UNION:若第二個(gè)select出現(xiàn)在union之后,則被標(biāo)記為union氨淌;若union包含在from子句的子查詢(xún)中泊愧,外層select將被標(biāo)記為derived
6、UNION RESULT:從union表獲取結(jié)果的select
type
訪問(wèn)類(lèi)型盛正,sql查詢(xún)優(yōu)化中一個(gè)很重要的指標(biāo)删咱,結(jié)果值從好到壞依次是:
system>const>eq_ref>ref> fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >range>index>ALL
一般來(lái)說(shuō),好的sql查詢(xún)至少達(dá)到range級(jí)別豪筝,最好能達(dá)到ref
1痰滋、system:表只有一行記錄(等于系統(tǒng)表),這是const類(lèi)型的特例续崖,平時(shí)不會(huì)出現(xiàn)敲街,可以忽略不計(jì)
2、const:表示通過(guò)索引一次就找到了严望,const用于比較primary key 或者 unique索引多艇。因?yàn)橹恍杵ヅ湟恍袛?shù)據(jù),所有很快像吻。如果將主鍵置于where列表中峻黍,mysql就能將該查詢(xún)轉(zhuǎn)換為一個(gè)const
3、eq_ref:唯一性索引掃描拨匆,對(duì)于每個(gè)索引鍵姆涩,表中只有一條記錄與之匹配。常見(jiàn)于主鍵 或 唯一索引掃描惭每。
4骨饿、ref:非唯一性索引掃描,返回匹配某個(gè)單獨(dú)值的所有行台腥。本質(zhì)是也是一種索引訪問(wèn)宏赘,它返回所有匹配某個(gè)單獨(dú)值的行,然而他可能會(huì)找到多個(gè)符合條件的行览爵,所以它應(yīng)該屬于查找和掃描的混合體置鼻。
5、range:只檢索給定范圍的行蜓竹,使用一個(gè)索引來(lái)選擇行箕母。key列顯示使用了那個(gè)索引储藐。一般就是在where語(yǔ)句中出現(xiàn)了bettween、<嘶是、>钙勃、in等的查詢(xún)。這種索引列上的范圍掃描比全索引掃描要好聂喇。只需要開(kāi)始于某個(gè)點(diǎn)辖源,結(jié)束于另一個(gè)點(diǎn),不用掃描全部索引希太。
6克饶、index:Full Index Scan,index與ALL區(qū)別為index類(lèi)型只遍歷索引樹(shù)誊辉。這通常為ALL塊矾湃,應(yīng)為索引文件通常比數(shù)據(jù)文件小。(Index與ALL雖然都是讀全表堕澄,但index是從索引中讀取邀跃,而ALL是從硬盤(pán)讀取)蛙紫。
7拍屑、ALL:Full Table Scan,遍歷全表以找到匹配的行坑傅。
possible_keys
查詢(xún)涉及到的字段上存在索引僵驰,則該索引將被列出,但不一定被查詢(xún)實(shí)際使用裁蚁。
key
實(shí)際使用的索引矢渊,如果為NULL,則沒(méi)有使用索引枉证。
查詢(xún)中如果使用了覆蓋索引,則該索引僅出現(xiàn)在key列表中移必。
key_len
表示索引中使用的字節(jié)數(shù)室谚,查詢(xún)中使用的索引的長(zhǎng)度(最大可能長(zhǎng)度),并非實(shí)際使用長(zhǎng)度崔泵,理論上長(zhǎng)度越短越好秒赤。key_len是根據(jù)表定義計(jì)算而得的,不是通過(guò)表內(nèi)檢索出的憎瘸。
ref
顯示索引的那一列被使用了入篮,如果可能,是一個(gè)常量const幌甘。
rows
根據(jù)表統(tǒng)計(jì)信息及索引選用情況潮售,大致估算出找到所需的記錄所需要讀取的行數(shù)痊项。
Extra
不適合在其他字段中顯示,但是十分重要的額外信息
1酥诽、Using filesort:
mysql對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序鞍泉,而不是按照表內(nèi)的索引進(jìn)行排序讀取。也就是說(shuō)mysql無(wú)法利用索引完成的排序操作成為“文件排序”肮帐。
2咖驮、Using temporary:
使用臨時(shí)表保存中間結(jié)果,也就是說(shuō)mysql在對(duì)查詢(xún)結(jié)果排序時(shí)使用了臨時(shí)表训枢,常見(jiàn)于order by 和 group by托修。
3、Using index:
表示相應(yīng)的select操作中使用了覆蓋索引(Covering Index)恒界,避免了訪問(wèn)表的數(shù)據(jù)行诀黍,效率高
如果同時(shí)出現(xiàn)Using where,表明索引被用來(lái)執(zhí)行索引鍵值的查找(參考上圖)
如果沒(méi)用同時(shí)出現(xiàn)Using where仗处,表明索引用來(lái)讀取數(shù)據(jù)而非執(zhí)行查找動(dòng)作眯勾。
覆蓋索引(Covering Index):也叫索引覆蓋。就是select列表中的字段婆誓,只用從索引中就能獲取吃环,不必根據(jù)索引再次讀取數(shù)據(jù)文件,換句話(huà)說(shuō)查詢(xún)列要被所建的索引覆蓋洋幻。
注意:
a郁轻、如需使用覆蓋索引,select列表中的字段只取出需要的列文留,不要使用select *
b好唯、如果將所有字段都建索引會(huì)導(dǎo)致索引文件過(guò)大,反而降低crud性能
4燥翅、Using where :
使用了where過(guò)濾
5骑篙、Using join buffer :
使用了鏈接緩存
6、Impossible WHERE:
where子句的值總是false森书,不能用來(lái)獲取任何元祖靶端。
7、select tables optimized away:
在沒(méi)有g(shù)roup by子句的情況下凛膏,基于索引優(yōu)化MIN/MAX操作或者對(duì)于MyISAM存儲(chǔ)引擎優(yōu)化COUNT(*)操作杨名,不必等到執(zhí)行階段在進(jìn)行計(jì)算,查詢(xún)執(zhí)行計(jì)劃生成的階段即可完成優(yōu)化
8猖毫、distinct:
優(yōu)化distinct操作台谍,在找到第一個(gè)匹配的元祖后即停止找同樣值得動(dòng)作