Mysql 索引覆蓋及回表查詢

在innoDB中，有兩大索引類，分別是

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來(lái)自于我們對(duì)這個(gè)行業(yè)的熱愛(ài)。我們立志把好的技術(shù)通過(guò)有效、簡(jiǎn)單的方式提供給客戶，將通過(guò)不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長(zhǎng)期合作伙伴，公司提供的服務(wù)項(xiàng)目有：國(guó)際域名空間、網(wǎng)絡(luò)空間、營(yíng)銷軟件、網(wǎng)站建設(shè)、山陰網(wǎng)站維護(hù)、網(wǎng)站推廣。

執(zhí)行上述語(yǔ)句，執(zhí)行過(guò)程如下圖

從圖中，我們可以看出，掃了兩個(gè)索引樹(shù)

（1）先從普通索引name找到lisi

（2）再根據(jù)主鍵值9，再在聚集索引中找到行記錄。

這就是回表查詢，先在普通索引中找到主鍵值，再在聚集索引中找到行記錄。

很顯然，在一棵索引樹(shù)上就能獲取SQL所需的所有列數(shù)據(jù)的，就是索引覆蓋。

如下語(yǔ)句

很顯然，我們可以直接在name索引上直接找到id,name，不用再去回表。

而且我們通過(guò)explain的extra屬性也能觀察到

像我們開(kāi)頭的SQL語(yǔ)句

我們只需要在name索引中再加個(gè)sex，name(name,sex)，這樣變成了聯(lián)合索引，也是索引覆蓋。

用mysql查詢某字段是否有索引怎么做？

顯示一個(gè)表所有索引的SQL語(yǔ)句是：

show index from 數(shù)據(jù)庫(kù)名.表名

查看某表某一列上的索引使用下面的SQL語(yǔ)句：

show index from 數(shù)據(jù)庫(kù)名.表名 where column_name like '列名'

下面的SQL語(yǔ)句在我的數(shù)據(jù)庫(kù)上執(zhí)行成功：

show index from web.clubuser where column_name like 'user'。

怎么查看表的索引mysql

查看索引的語(yǔ)法格式如下：

SHOW INDEX FROM 表名 [ FROM 數(shù)據(jù)庫(kù)名]

語(yǔ)法說(shuō)明如下：

表名：指定需要查看索引的數(shù)據(jù)表名。

數(shù)據(jù)庫(kù)名：指定需要查看索引的數(shù)據(jù)表所在的數(shù)據(jù)庫(kù)，可省略。比如，SHOW INDEX FROM student FROM test; 語(yǔ)句表示查看 test 數(shù)據(jù)庫(kù)中 student 數(shù)據(jù)表的索引。

示例

使用 SHOW INDEX 語(yǔ)句查看《MySQL創(chuàng)建索引》一節(jié)中 tb_stu_info2 數(shù)據(jù)表的索引信息，SQL 語(yǔ)句和運(yùn)行結(jié)果如下所示。

mysql SHOW INDEX FROM tb_stu_info2\G

1. row

Table: tb_stu_info2

Non_unique: 0

Key_name: height

Seq_in_index: 1

Column_name: height

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.03 sec)

其中各主要參數(shù)說(shuō)明如下：

參數(shù) 說(shuō)明

Table 表示創(chuàng)建索引的數(shù)據(jù)表名，這里是 tb_stu_info2 數(shù)據(jù)表。

Non_unique 表示該索引是否是唯一索引。若不是唯一索引，則該列的值為 1；若是唯一索引，則該列的值為 0。

Key_name 表示索引的名稱。

Seq_in_index 表示該列在索引中的位置，如果索引是單列的，則該列的值為 1；如果索引是組合索引，則該列的值為每列在索引定義中的順序。

Column_name 表示定義索引的列字段。

Collation 表示列以何種順序存儲(chǔ)在索引中。在 MySQL 中，升序顯示值“A”（升序），若顯示為 NULL，則表示無(wú)分類。

Cardinality 索引中唯一值數(shù)目的估計(jì)值?；鶖?shù)根據(jù)被存儲(chǔ)為整數(shù)的統(tǒng)計(jì)數(shù)據(jù)計(jì)數(shù)，所以即使對(duì)于小型表，該值也沒(méi)有必要是精確的?；鶖?shù)越大，當(dāng)進(jìn)行聯(lián)合時(shí)，MySQL 使用該索引的機(jī)會(huì)就越大。

Sub_part 表示列中被編入索引的字符的數(shù)量。若列只是部分被編入索引，則該列的值為被編入索引的字符的數(shù)目；若整列被編入索引，則該列的值為 NULL。

Packed 指示關(guān)鍵字如何被壓縮。若沒(méi)有被壓縮，值為 NULL。

Null 用于顯示索引列中是否包含 NULL。若列含有 NULL，該列的值為 YES。若沒(méi)有，則該列的值為 NO。

Index_type 顯示索引使用的類型和方法（BTREE、FULLTEXT、HASH、RTREE）。

Comment 顯示評(píng)注。

MySQL——關(guān)于索引的總結(jié)

首先說(shuō)說(shuō)索引的 優(yōu)點(diǎn) ：最大的好處無(wú)疑就是提高查詢效率。有的索引還能保證數(shù)據(jù)的唯一性，比如唯一索引。

而它的 壞處 也很明顯：索引也是文件，我們?cè)趧?chuàng)建索引時(shí)，也會(huì)創(chuàng)建額外的文件，所以會(huì)占用一些硬盤空間。其次，索引也需要維護(hù)，我們?cè)谠黾觿h除數(shù)據(jù)的時(shí)候，索引也需要去變化維護(hù)。當(dāng)一個(gè)表的索引多了以后，資源消耗是很大的，所以必須結(jié)合實(shí)際業(yè)務(wù)再去確定給哪些列加索引。

再說(shuō)說(shuō)索引的基本結(jié)構(gòu)。一說(shuō)到這里肯定會(huì)脫口而出：B+樹(shù)！了解B+樹(shù)前先要了解二叉查找樹(shù)和二叉平衡樹(shù)。 二叉查找樹(shù) ：左節(jié)點(diǎn)比父節(jié)點(diǎn)小，右節(jié)點(diǎn)比父節(jié)點(diǎn)大，所以二叉查找樹(shù)的中序遍歷就是樹(shù)的各個(gè)節(jié)點(diǎn)從小到大的排序。 二叉平衡樹(shù) ：左右子樹(shù)高度差不能大于1。B+樹(shù)就是結(jié)合了它們的特點(diǎn)，當(dāng)然，不一定是二叉樹(shù)。

為什么要有二叉查找樹(shù)的特點(diǎn)？？ 因?yàn)椴檎倚士?，二分查找在這種結(jié)構(gòu)下，查找效率是很快的。 那為什么要有平衡樹(shù)的特點(diǎn)呢？ 試想，如果不維護(hù)一顆樹(shù)的平衡性，當(dāng)插入一些數(shù)據(jù)后，樹(shù)的形態(tài)有可能變得很極端，比如左子樹(shù)一個(gè)數(shù)據(jù)沒(méi)有，而全在右子樹(shù)上，這種情況下，二分查找和遍歷有什么區(qū)別呢？而就是因?yàn)檫@些特點(diǎn)需要去維護(hù)，所以就有了上面提到的缺點(diǎn)，當(dāng)索引很多后，反而增加了系統(tǒng)的負(fù)擔(dān)。

接著說(shuō)B+樹(shù)。 它的結(jié)構(gòu)如下 ：

可以發(fā)現(xiàn)，葉子節(jié)點(diǎn)其實(shí)是一個(gè) 雙向循環(huán)鏈表 ，這種結(jié)構(gòu)的好處就是，在范圍查詢的時(shí)候，我只用找到一個(gè)數(shù)據(jù)，就可以直接返回剩余的數(shù)據(jù)了。比如找小于30的，只用找到30，其余的直接通過(guò)葉子節(jié)點(diǎn)間的指針就可以找到。再說(shuō)說(shuō)其他特點(diǎn)： 數(shù)據(jù)只存在于葉子節(jié)點(diǎn) 。當(dāng)葉子節(jié)點(diǎn)滿了，如果再添加數(shù)據(jù)，就會(huì)拆分葉子節(jié)點(diǎn)，父節(jié)點(diǎn)就多了個(gè)子節(jié)點(diǎn)。如果父節(jié)點(diǎn)的位置也滿了，就會(huì)擴(kuò)充高度，就是拆分父節(jié)點(diǎn)，如25 50 75拆分成：25為左子樹(shù)，75為右子樹(shù)，50變成新的頭節(jié)點(diǎn)，此時(shí)B+樹(shù)的高度變成了3。它們的擴(kuò)充的規(guī)律如下表，Leaf Page是葉子節(jié)點(diǎn)，index Page是非葉子節(jié)點(diǎn)。

再說(shuō)說(shuō)B樹(shù) ，B樹(shù)相比較B+樹(shù)，它所有節(jié)點(diǎn)都存放數(shù)據(jù)，所以在查找數(shù)據(jù)時(shí)，B樹(shù)有可能沒(méi)到達(dá)葉子節(jié)點(diǎn)就結(jié)束了。再者，B樹(shù)的葉子節(jié)點(diǎn)間不存在指針。

最后說(shuō)說(shuō)Hash索引 ，相較于B+樹(shù)，Hash索引最大的優(yōu)點(diǎn)就是查找數(shù)據(jù)快。但是Hash索引最大的問(wèn)題就是不支持范圍查詢。試想，如果查詢小于30的數(shù)據(jù)，hash函數(shù)是根據(jù)數(shù)據(jù)的值找到其對(duì)應(yīng)的位置，誰(shuí)又知道小于30的有哪幾個(gè)數(shù)據(jù)。而B(niǎo)+樹(shù)正好相反，范圍查詢是它的強(qiáng)項(xiàng)。

附錄： Hash到底是啥？？ 哈希中文名散列，哈希只是它的音譯。 為啥都說(shuō)Hash快？？ 首先有一塊哈希表（散列表），它的數(shù)據(jù)結(jié)構(gòu)是個(gè)數(shù)組，一個(gè)任意長(zhǎng)度的數(shù)據(jù)通過(guò)hash函數(shù)都可以變成一個(gè)固定長(zhǎng)度的數(shù)據(jù)，叫hash值。然后通過(guò)hash值確定在數(shù)組中的位置，相同數(shù)據(jù)的hash值是相同的，所以我們存儲(chǔ)一個(gè)數(shù)據(jù)以后，只需O(1)的時(shí)間復(fù)雜度就可以找到數(shù)據(jù)。 那hash函數(shù)又是啥？？ 算術(shù)運(yùn)算或位運(yùn)算，很多應(yīng)用里都有hash函數(shù)，但實(shí)際運(yùn)算過(guò)程大不一樣。這是Java里String的hashCode方法：

publicint hashCode() {

}

還有一個(gè)問(wèn)題，hash函數(shù)計(jì)算出來(lái)的hash值有可能存在碰撞，即兩個(gè)不同的數(shù)據(jù)可能存在相同的hash值，在MySQL或其他的應(yīng)用中，如Java的HashMap等，如果存在碰撞就會(huì)以當(dāng)前數(shù)組位置為頭節(jié)點(diǎn)，轉(zhuǎn)變成一個(gè)鏈表。

說(shuō)到這里也清楚了為啥Java中引用類型要同時(shí)重寫(xiě)hashCode和equals了。兩個(gè)對(duì)象，實(shí)例就算一模一樣，它們的hash值也不相等， 為啥不相等？？ 默認(rèn)的Object的hashCode方法會(huì)根據(jù)對(duì)象來(lái)計(jì)算hash值的，實(shí)例相同，但它們還是兩個(gè)不同的對(duì)象啊，所以我們重寫(xiě)hashCode時(shí)，最簡(jiǎn)單的方法就是調(diào)用Object的hashCode方法，然后傳入該引用類型的屬性，讓hashCode方法只根據(jù)這幾個(gè)屬性來(lái)計(jì)算，那么實(shí)例相同的話，它們的hash值也會(huì)相等。等hashCode比較完后，如果相等再比較實(shí)例內(nèi)容，也就是equals，確保不是hash碰撞。

索引的分類

如果我們指定了一個(gè)主鍵，那么這個(gè)主鍵就是主鍵索引。如果我們沒(méi)有指定，Mysql就會(huì)自動(dòng)找一個(gè)非空的唯一索引當(dāng)主鍵。如果沒(méi)有這種字段，Mysql就會(huì)創(chuàng)建一個(gè)大小為6字節(jié)的自增主鍵。如果有多個(gè)非空的唯一索引，那么就讓第一個(gè)定義為唯一索引的字段當(dāng)主鍵，注意，是第一個(gè)定義，而不是建表時(shí)出現(xiàn)在前面的。

對(duì)于輔助索引來(lái)說(shuō)，它們的B+樹(shù)結(jié)構(gòu)稍微有點(diǎn)特殊，它們的葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵，而不是整個(gè)數(shù)據(jù)。所以在大部分情況下，使用輔助索引查找數(shù)據(jù)，需要二次查找。但并不是所有情況都需要二次查找。比如查找的數(shù)據(jù)正好就是當(dāng)前索引字段的值，那么直接返回就行。這里提一句，B+樹(shù)的key就是對(duì)應(yīng)索引字段的內(nèi)容。

而輔助索引又有一些分類：唯一索引：不能出現(xiàn)重復(fù)的值，也算一種約束。普通索引：可以重復(fù)、可以為空，一般就是查詢時(shí)用到。前綴索引：只適用于字符串類型數(shù)據(jù)，對(duì)字符串前幾個(gè)字符創(chuàng)建索引。全文索引：作用是檢測(cè)大文本數(shù)據(jù)中某個(gè)關(guān)鍵字，這也是搜索引擎的一種技術(shù)。

注意，聚集索引、非聚集索引和前面幾個(gè)索引的分類并不是一個(gè)層面上的。上面的幾個(gè)分類是從索引的作用來(lái)分析的。聚集、非聚集索引是從索引文件上區(qū)分的。主鍵索引就屬于聚集索引，即索引和數(shù)據(jù)存放在一起，葉子節(jié)點(diǎn)存放的就是數(shù)據(jù)。數(shù)據(jù)表的.idb文件就是存放該表的索引和數(shù)據(jù)。

輔助索引屬于非聚集索引，說(shuō)到這也就明白了。索引和數(shù)據(jù)不存放在一起的就是非聚集索引。在MYISAM引擎中，數(shù)據(jù)表的.MYI文件包含了表的索引， 該表的 葉子節(jié)點(diǎn)存儲(chǔ)索引和索引對(duì)應(yīng)數(shù)據(jù)的指針，指向.MYD文件的數(shù)據(jù)。

索引的幾點(diǎn)使用經(jīng)驗(yàn)

經(jīng)常被查詢的字段；經(jīng)常作為條件查詢的字段；經(jīng)常用于外鍵連接或普通的連表查詢時(shí)進(jìn)行相等比較字段；不為null的字段；如果是多條件查詢，最好創(chuàng)建聯(lián)合索引，因?yàn)槁?lián)合索引只有一個(gè)索引文件。

經(jīng)常被更新的字段、不經(jīng)常被查詢的字段、存在相同功能的字段

如何查看MySQL索引

查看索引

mysql show index from tblname;

mysql show keys from tblname;

· Table

表的名稱。

· Non_unique

如果索引不能包括重復(fù)詞，則為0。如果可以，則為1。

· Key_name

索引的名稱。

· Seq_in_index

索引中的列序列號(hào)，從1開(kāi)始。

· Column_name

列名稱。

· Collation

列以什么方式存儲(chǔ)在索引中。在MySQL中，有值‘A’（升序）或NULL（無(wú)分類）。

· Cardinality

索引中唯一值的數(shù)目的估計(jì)值。通過(guò)運(yùn)行ANALYZE TABLE或myisamchk -a可以更新?；鶖?shù)根據(jù)被存儲(chǔ)為整數(shù)的統(tǒng)計(jì)數(shù)據(jù)來(lái)計(jì)數(shù)，所以即使對(duì)于小型表，該值也沒(méi)有必要是精確的?；鶖?shù)越大，當(dāng)進(jìn)行聯(lián)合時(shí)，MySQL使用該索引的機(jī) 會(huì)就越大。

· Sub_part

如果列只是被部分地編入索引，則為被編入索引的字符的數(shù)目。如果整列被編入索引，則為NULL。

· Packed

指示關(guān)鍵字如何被壓縮。如果沒(méi)有被壓縮，則為NULL。

· Null

如果列含有NULL，則含有YES。如果沒(méi)有，則該列含有NO。

· Index_type

用過(guò)的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

· Comment

mysql 創(chuàng)建索引后怎么根據(jù)索引去查詢

mysql 添加索引后 在查詢的時(shí)候是mysql就自動(dòng)從索引里面查詢了。如果需要也可以單獨(dú)根據(jù)索引查詢，這個(gè)看使用場(chǎng)景

MYSQL在創(chuàng)建索引后對(duì)索引的使用方式分為兩種：

1 由數(shù)據(jù)庫(kù)的查詢優(yōu)化器自動(dòng)判斷是否使用索引；

2 用戶可在寫(xiě)SQL語(yǔ)句時(shí)強(qiáng)制使用索引

本文標(biāo)題：mysql索引怎么查數(shù)據(jù) mysql如何使用索引查詢
文章源于：http://chinadenli.net/article10/hgpcgo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供服務(wù)器托管、網(wǎng)站維護(hù)、電子商務(wù)、網(wǎng)站收錄、網(wǎng)站營(yíng)銷、網(wǎng)站策劃

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)