第7單元索引MySQL數(shù)據(jù)庫應(yīng)用實(shí)戰(zhàn)教程（慕課版）（第2版）目錄【目錄】7.1索引的基本語法7.1.1創(chuàng)建索引7.1.2查看索引7.1.3刪除索引7.2常見的查詢算法7.3索引的數(shù)據(jù)結(jié)構(gòu)7.3.1B-Tree數(shù)據(jù)結(jié)構(gòu)7.3.2B+Tree

數(shù)據(jù)結(jié)構(gòu)7.4索引實(shí)現(xiàn)原理7.4.1MyISAM存儲(chǔ)引擎的索引實(shí)現(xiàn)7.4.2InnoDB

存儲(chǔ)引擎的索引實(shí)現(xiàn)7.4.3MEMORY存儲(chǔ)引擎的索引實(shí)現(xiàn)7.5索引的應(yīng)用7.5.1創(chuàng)建表及添加索引7.5.2使用EXPLAIN語句分析索引7.5.3索引使用策略7.5.4索引應(yīng)用實(shí)例目錄【目錄】7.6索引的類型7.6.1主鍵索引7.6.2普通索引7.6.3唯一索引引7.6.4單列索引和聯(lián)合索引7.6.5聚簇索引和非聚簇索引7.6.6覆蓋索引7.6.7重復(fù)索引等7.6.8降序索引7.6.9隱藏索引7.6.10函數(shù)索引7.7索引不能使用7.7.1前導(dǎo)模糊查詢7.7.2比較不匹配的數(shù)據(jù)類型7.7.3使用OR連接條件表達(dá)式7.7.4條件表達(dá)式與函數(shù)7.8索引的利弊及創(chuàng)建原則7.9綜合實(shí)訓(xùn)：電商平臺(tái)查詢索引應(yīng)用7.10小結(jié)【學(xué)習(xí)導(dǎo)讀】假設(shè)有一個(gè)電商平臺(tái)，擁有大量的商品數(shù)據(jù)，用戶可以根據(jù)關(guān)鍵字搜索商品。為了提高搜索效率，可以使用MySQL索引來加速搜索過程。通過在商品表的名稱字段上創(chuàng)建索引，用戶可以快速定位到包含特定關(guān)鍵字的商品。這樣，用戶可以更快地找到所需的商品，提升了用戶體驗(yàn)和平臺(tái)的整體性能。學(xué)習(xí)導(dǎo)讀【學(xué)習(xí)目標(biāo)】知識目標(biāo)1．掌握MySQL索引的基本語法。2．掌握常見的查詢算法。3．掌握MySQL索引的數(shù)據(jù)結(jié)構(gòu)。4．掌握MySQL索引的實(shí)現(xiàn)原理。5．掌握MySQL索引的應(yīng)用。6．掌握MySQL索引的類型。7．了解索引不能使用的場景。8．了解索引的利弊及建立原則。能力目標(biāo)1．能夠熟練使用MySQL索引來加快數(shù)據(jù)查詢效率。2．能夠熟練正確創(chuàng)建合適的索引。素質(zhì)目標(biāo)1．培養(yǎng)目標(biāo)導(dǎo)向意識，有明確的目標(biāo)和追求，能夠有效地實(shí)現(xiàn)目標(biāo)。2．培養(yǎng)解決問題的能力，能夠分析問題并提出解決方案。學(xué)習(xí)目標(biāo)思維導(dǎo)圖7.1索引的基本語法7.1.1創(chuàng)建索引在創(chuàng)建表的時(shí)候創(chuàng)建索引屬性名數(shù)據(jù)類型[完整性約束條件],......屬性名數(shù)據(jù)類型[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY[別名](屬性名1[(長度)][ASC|DESC]));mysql>CREATETABLEstudent(idintprimarykey,namevarchar(255),sexvarchar(10),index(name));

UNIQUE：唯一索引。FULLTEXT：全文索引。SPATIAL：空間索引。INDEX和KEY：用來指定某個(gè)字段為索引，使用效果一樣。別名：用來給創(chuàng)建的索引取新名稱。屬性1：用來指定索引對應(yīng)的字段的名稱，該字段必須為前面定義好的字段。長度：指索引的長度，必須是字符串類型才可以使用。ASC：升序排列。DESC：降序排列。使用CREATEINDEX語句創(chuàng)建索引CREATEINDEXindex_nameONtable_name(column_list)CREATEUNIQUEINDEXindex_nameONtable_name(column_list)mysql>CREATEINDEXidx_sexONstudent(sex);7.1索引的基本語法使用ALTERTABLE語句創(chuàng)建索引#普通索引ALTERTABLEtable_nameADDINDEXindex_name(column_list)#唯一索引ALTERTABLEtable_nameADDUNIQUE(column_list)#主鍵索引ALTERTABLEtable_nameADDPRIMARYKEY(column_list)mysql>ALTERTABLEstudentADDINDEXidx_name(name);索引命名建議唯一索引以“uk_”開頭，普通索引以“idx_”開頭，以字段的名稱或縮寫作為后綴。7.1.2查看索引在MySQL中，可以使用SHOWINDEX語句來查看表的索引信息。該語句的語法格式如下。SHOWINDEXFROMtable_name;或如下:SHOWKEYSFROMtable_name;7.1索引的基本語法#查看學(xué)生表student的索引信息mysql>SHOWINDEXFROMstudent;+---------+------+---------------+-------+------------+--------+---------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|Visible|Expression|+---------+------+---------------+-------+------------+--------+---------+|student|0|PRIMARY|1|id|A|0||student|1|idx_name|1|name|A|0||student|1|idx_sex|1|sex|A|0||student|1|id_name_index|1|id|A|0|+---------+------+---------------+-------+------------+--------+---------+#查看學(xué)生表student的索引信息mysql>SHOWKEYSFROMstudent;+---------+------+---------------+-------+------------+--------+---------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|Visible|Expression|+---------+------+---------------+-------+------------+--------+---------+|student|0|PRIMARY|1|id|A|0||student|1|idx_name|1|name|A|0||student|1|idx_sex|1|sex|A|0||student|1|id_name_index|1|id|A|0|+---------+------+---------------+-------+------------+--------+---------+7.1索引的基本語法7.1.3刪除索引使用DROPINDEX語句可以刪除指定表中的索引。執(zhí)行該語句后，相應(yīng)的索引將從表中被刪除。該語句的語法格式如下。DROPINDEXindex_nameONtalbe_nameALTERTABLEtable_nameDROPINDEXindex_nameALTERTABLEtable_nameDROPPRIMARYKEY實(shí)戰(zhàn)演練——給用戶表user添加和刪除索引#使用用戶名root和相應(yīng)密碼，連接本地?cái)?shù)據(jù)庫C:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用數(shù)據(jù)庫shopmysql>USEshop;Databasechanged7.1索引的基本語法#查詢用戶表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+|id|name|sex|age|password|+----+-------+------+------+----------+|3|david|女|28|111111||4|小紅|女|27|123456||5|小明|男|10|123456||6|小剛|男|12|123456||7|小王|男|14|111111||8|小綠|女|34|222222||9|曉峰|男|15|333333||10|小影|女|26|444444||11|大梅|女|27|555555|+----+-------+------+------+----------+9rowsinset(0.00sec)#在姓名name列上添加索引mysql>ALTERTABLEuserADDINDEXidx_name(name);QueryOK,0rowsaffected(0.43sec)Records:0Duplicates:0Warnings:0#在性別sex列上添加索引mysql>ALTERTABLEuserADDINDEXidx_sex(sex);QueryOK,0rowsaffected(0.34sec)Records:0Duplicates:0Warnings:07.1索引的基本語法#查詢用戶表user的索引mysql>SHOWINDEXFROMuser;

+-------+------------+----------+--------------+-------------+-----------+----------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality+-------+------------+----------+--------------+-------------+-----------+----------+|user|0|PRIMARY|1|id|A|9||user|1|idx_name|1|name|A|9||user|1|idx_sex|1|sex|A|2|+-------+------------+----------+--------------+-------------+-------v---+----------+3rowsinset(0.00sec)#查詢用戶表user的索引mysql>SHOWKEYSFROMuser;+-------+------------+----------+--------------+-------------+-----------+----------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality+-------+------------+----------+--------------+-------------+-----------+----------+|user|0|PRIMARY|1|id|A|9||user|1|idx_name|1|name|A|9||user|1|idx_sex|1|sex|A|2|+-------+------------+----------+--------------+-------------+-----------+----------+3rowsinset(0.00sec)7.1索引的基本語法#刪除姓名name列上的索引mysql>DROPINDEXidx_nameONuser;QueryOK,0rowsaffected(0.18sec)Records:0Duplicates:0Warnings:0#刪除性別sex列上的索引mysql>DROPINDEXidx_sexONuser;QueryOK,0rowsaffected(0.21sec)Records:0Duplicates:0Warnings:0#查詢用戶表user的索引mysql>SHOWKEYSFROMuser;+-------+-----------+----------+-------------+------------+----------+----------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality+-------+-----------+----------+-------------+------------+----------+----------+|user|0|PRIMARY|1|id|A|9|+-------+-----------+----------+-------------+------------+----------+----------+1rowinset(0.00sec)mysql>7.2常見的查詢算法1．順序查找算法順序查找算法比較好理解，就是按順序逐個(gè)查找。例如，有一組數(shù)據(jù)“2、3、5、7、8、9、10、16、21、25、30”，要從這組數(shù)據(jù)里查找“8”這個(gè)數(shù)據(jù)，使用順序查找算法，就需要逐個(gè)比對數(shù)據(jù)，直到找到要查找的數(shù)據(jù)。2．二分查找算法二分查找算法比順序查找算法效率高，它的查找原理是從要查找的數(shù)據(jù)的中間元素開始。例如，有一組數(shù)據(jù)“2、3、5、7、8、9、10、16、21、25、30”，要從這組數(shù)據(jù)里查找“8”這個(gè)數(shù)據(jù)，使用二分查找算法，從中間元素“9”開始比對，以“9”為中心把數(shù)據(jù)分成兩部分“2、3、5、7、8”和“10、16、21、25、30”；“8”是小于“9”的，所以在“2、3、5、7、8”這組數(shù)據(jù)里繼續(xù)查找，同樣使用二分查找算法再將其分成兩部分“2、3”和“7、8”；“8”是大于“5”的，所以在“7、8”里查找數(shù)據(jù)……這樣不斷將數(shù)據(jù)分成兩部分，在其中的一部分里查找。7.2常見的查詢算法3．二叉樹查找算法二叉樹具有如下特點(diǎn)：每個(gè)節(jié)點(diǎn)最多有兩棵子樹，節(jié)點(diǎn)的度最大為2；左子樹和右子樹是有順序的，順序不能顛倒；即使某節(jié)點(diǎn)只有一棵子樹，也要區(qū)分左右子樹。4．哈希查找算法哈希查找算法是將數(shù)據(jù)值通過哈希函數(shù)創(chuàng)建一個(gè)哈希表，如果要查詢某個(gè)數(shù)據(jù)值，也需要先通過哈希函數(shù)生成一個(gè)值，再到哈希表里去查詢，如果查詢到則返回?cái)?shù)值。將通過哈希函數(shù)重新生成的數(shù)據(jù)值存放到哈希表中時(shí)很有可能重復(fù)，從而產(chǎn)生沖突，沖突越少，查詢得越快，如果沒有沖突，則它的時(shí)間復(fù)雜度是O(1)。7.3索引的數(shù)據(jù)結(jié)構(gòu)7.3.1B-Tree數(shù)據(jù)結(jié)構(gòu)B-Tree數(shù)據(jù)結(jié)構(gòu)稱為平衡多路搜索樹，它基于在二叉樹的基礎(chǔ)上采用多叉樹、再使用平衡二叉樹的思想。之所以采用多路搜索樹，是因?yàn)檫@樣可以減小樹的深度，提高查詢速度。B-Tree數(shù)據(jù)結(jié)構(gòu)如圖7.2所示。有一個(gè)根節(jié)點(diǎn)，根節(jié)點(diǎn)只有一條記錄和兩棵子樹，如50就是根節(jié)點(diǎn)，下面有兩棵子樹。方框里的數(shù)字是key，線是指針，指針指向子樹。所有的葉子節(jié)點(diǎn)必須在同一層，也就是說它們具有相同的深度；每個(gè)葉子節(jié)點(diǎn)至少包含一個(gè)key和兩個(gè)指針，最多包含2d－1個(gè)key和2d個(gè)指針，葉子節(jié)點(diǎn)的指針均為NULL。每個(gè)非葉子節(jié)點(diǎn)包含n－1個(gè)key和n個(gè)指針，其中d≤n≤2d。在一個(gè)節(jié)點(diǎn)中，第n棵子樹中的所有key，小于這個(gè)節(jié)點(diǎn)中的第n個(gè)key，大于第n－1個(gè)key，例如15、45這棵子樹，它的子樹有3個(gè)取值范圍：0～15、16～45、46以上。圖7.2B-Tree數(shù)據(jù)結(jié)構(gòu)7.3索引的數(shù)據(jù)結(jié)構(gòu)B-Tree數(shù)據(jù)結(jié)構(gòu)是先從根節(jié)點(diǎn)進(jìn)行二分查找，如果小于根節(jié)點(diǎn)，就從左子樹開始，從相應(yīng)區(qū)間指針指向的節(jié)點(diǎn)遞歸進(jìn)行查找；如果大于根節(jié)點(diǎn)，就從右子樹遞歸進(jìn)行查找，直到找到滿足條件的節(jié)點(diǎn)，才返回相應(yīng)的data。葉子節(jié)點(diǎn)具有相同的深度，葉子節(jié)點(diǎn)的指針為空。所有索引元素不重復(fù)。節(jié)點(diǎn)中的數(shù)據(jù)索引從左到右遞增。7.3索引的數(shù)據(jù)結(jié)構(gòu)7.3.2B+Tree數(shù)據(jù)結(jié)構(gòu)B+Tree數(shù)據(jù)結(jié)構(gòu)是B-Tree數(shù)據(jù)結(jié)構(gòu)的變種，它也是一種平衡多路搜索樹，MySQL普遍使用B+Tree數(shù)據(jù)結(jié)構(gòu)實(shí)現(xiàn)索引。B+Tree數(shù)據(jù)結(jié)構(gòu)如圖7.3所示。非葉子節(jié)點(diǎn)不存儲(chǔ)data，只存儲(chǔ)索引（冗余），可以存儲(chǔ)更多的索引。葉子節(jié)點(diǎn)包含所有索引字段。葉子節(jié)點(diǎn)用指針連接，以提高區(qū)間訪問的性能。圖7.3B+Tree數(shù)據(jù)結(jié)構(gòu)B+Tree數(shù)據(jù)結(jié)構(gòu)的特點(diǎn)：step01step02B-Tree數(shù)據(jù)結(jié)構(gòu)和B+Tree數(shù)據(jù)結(jié)構(gòu)的優(yōu)點(diǎn)：支持快速的插入、刪除和查詢操作，時(shí)間復(fù)雜度通常為O(logn)。適用于大型數(shù)據(jù)集和高并發(fā)的數(shù)據(jù)庫環(huán)境。對數(shù)據(jù)的插入和刪除操作具有較好的平衡性，避免了頻繁的數(shù)據(jù)遷移。7.3索引的數(shù)據(jù)結(jié)構(gòu)B-Tree索引結(jié)構(gòu)和B+Tree索引結(jié)構(gòu)主要區(qū)別：step03B-Tree索引結(jié)構(gòu)和B+Tree索引結(jié)構(gòu)B+Tree索引結(jié)構(gòu)的葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)，非葉子節(jié)點(diǎn)不存儲(chǔ)數(shù)據(jù)B+Tree索引結(jié)構(gòu)的葉子節(jié)點(diǎn)存放表所有數(shù)據(jù)，非葉子節(jié)點(diǎn)作為冗余索引B+Tree索引結(jié)構(gòu)的葉子節(jié)點(diǎn)帶有指針，可以快速范圍遍歷查找B-Tree索引結(jié)構(gòu)的節(jié)點(diǎn)都存儲(chǔ)數(shù)據(jù)，沒有冗余節(jié)點(diǎn)索引，并且葉子節(jié)點(diǎn)沒有指針B+Tree索引結(jié)構(gòu)由于只有葉子節(jié)點(diǎn)存放數(shù)據(jù)，所以用最小深度可以存放更多的節(jié)點(diǎn)數(shù)據(jù)7.4索引實(shí)現(xiàn)原理7.4.1MyISAM存儲(chǔ)引擎的索引實(shí)現(xiàn)1．主索引主索引是以主鍵或者唯一標(biāo)識生成的索引，在創(chuàng)建表的時(shí)候，如果沒有指定主索引，存儲(chǔ)引擎會(huì)自動(dòng)維護(hù)一個(gè)主索引，它的每個(gè)節(jié)點(diǎn)是不允許重復(fù)的，就如同數(shù)據(jù)行的主鍵。MyISAM主索引以主鍵為核心、采用B+Tree數(shù)據(jù)結(jié)構(gòu)來構(gòu)建索引樹，葉子節(jié)點(diǎn)存放數(shù)據(jù)存儲(chǔ)的地址，通過地址可以快速找到相應(yīng)的行數(shù)據(jù)。圖7.4MyISAM主索引7.4索引實(shí)現(xiàn)原理2．輔索引輔索引就是以其他列構(gòu)建的索引，而非以主鍵構(gòu)建的索引。它與主索引的區(qū)別就在于它允許重復(fù)，而主索引是不允許重復(fù)的。與主索引一樣，輔索引在索引樹的葉子節(jié)點(diǎn)存放地址，通過地址找到數(shù)據(jù)。圖7.5MyISAM輔索引7.4索引實(shí)現(xiàn)原理7.4.2InnoDB存儲(chǔ)引擎的索引實(shí)現(xiàn)1．主索引主索引是以主鍵或者唯一列來構(gòu)建的索引，如果沒有指定主鍵或者唯一列，MySQL自己維護(hù)一套主索引，它是不允許重復(fù)的。例如：有一個(gè)學(xué)生表，它有主鍵id列、姓名name列、性別sex列、年齡age列，那么它的主索引如圖7.6所示。圖7.6InnoDB主索引7.4索引實(shí)現(xiàn)原理2．二級索引InnoDB二級索引是以其他列而非主鍵構(gòu)建的索引，所以它是允許重復(fù)的。它的葉子節(jié)點(diǎn)存放主索引的值，通過主索引再直接找到數(shù)據(jù)，這樣就不用在每棵索引樹上存儲(chǔ)數(shù)據(jù)，能夠節(jié)省存儲(chǔ)空間，同時(shí)便于維護(hù)。圖7.7InnoDB二級索引7.4索引實(shí)現(xiàn)原理7.4.3MEMORY存儲(chǔ)引擎的索引實(shí)現(xiàn)MEMORY存儲(chǔ)引擎又稱為HEAP存儲(chǔ)引擎，用于創(chuàng)建特殊用途的表且內(nèi)容存儲(chǔ)在內(nèi)存中。將數(shù)據(jù)存儲(chǔ)在內(nèi)存中，能夠?qū)崿F(xiàn)快速訪問和低延遲。它的默認(rèn)索引方式采用哈希索引的方式來構(gòu)建。哈希索引通過哈希函數(shù)計(jì)算出結(jié)果，然后將該結(jié)果隨機(jī)地存放到磁盤中。通過哈希函數(shù)，對某列要加索引重新計(jì)算出一個(gè)結(jié)果值隨機(jī)記錄到磁盤中，如果計(jì)算出的結(jié)果值沒有重復(fù)的，那么它的時(shí)間復(fù)雜度就是O(1)。隨著數(shù)據(jù)量的增大，通過哈希函數(shù)計(jì)算出的結(jié)果值很可能重復(fù)，這樣查詢效率會(huì)逐漸下降，但是它的檢索速度也是非?？斓?。圖7.8哈希索引7.5索引的應(yīng)用7.5.1創(chuàng)建表及添加索引表7.1student表創(chuàng)建一個(gè)學(xué)生表student，它有主鍵id列、姓名name列、性別sex列、年齡age列、年級grade列、班級class列、學(xué)號num列，如表7.1所示，然后給id、name、sex、age每列單獨(dú)添加索引。idnamesexagegradeclassnum1劉明男19高三6班30012吳倩女18高三6班30023張欣男17高二1班20014孫曉女17高二1班20025趙英俊男16高一2班10016柳師師女17高一2班10027.5索引的應(yīng)用實(shí)戰(zhàn)演練——?jiǎng)?chuàng)建學(xué)生表student并添加索引#使用用戶名root和相應(yīng)密碼，連接本地?cái)?shù)據(jù)庫C:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure#使用數(shù)據(jù)庫shop.mysql>USEshop;Databasechanged#刪除student表mysql>DROPTABLEstudent;QueryOK,0rowsaffected(0.15sec)#創(chuàng)建student表mysql>CREATETABLEstudent(idintnotnullAUTO_INCREMENT,namevarchar(255),sexvarchar(5),agevarchar(5),gradevarchar(10),classvarchar(10),numint,primarykey(id));QueryOK,0rowsaffected(0.42sec)7.5索引的應(yīng)用#插入數(shù)據(jù)mysql>insertintostudentvalues(1,'劉明','男','19','高三','6班',3001),(2,'吳倩','女','18','高三','6班',3002),(3,'張欣','男','17','高二','1班',2001),

(4,'孫曉','女','17','高二','1班',2002),(5,'趙英俊','男','16','高一','2班',1001),(6,'柳師師','女','17','高一','2班',1002);QueryOK,1rowaffected(0.00sec)#查詢數(shù)據(jù)mysql>SELECT*FROMstudent;+----+--------+------+------+-------+-------+------+|id|name|sex|age|grade|class|num|+----+--------+------+------+-------+-------+------+|1|劉明|男|19|高三|6班|3001||2|吳倩|女|18|高三|6班|3002||3|張欣|男|17|高二|1班|2001||4|孫曉|女|17|高二|1班|2002||5|趙英俊|男|16|高一|2班|1001||6|柳師師|女|17|高一|2班|1002|+----+--------+------+------+-------+-------+------+6rowsinset(0.00sec)#給主鍵id列添加唯一索引mysql>ALTERTABLEstudentADDUNIQUEuk_id(id);

QueryOK,0rowsaffected(0.70sec)Records:0Duplicates:0Warnings:0#給姓名name列添加普通索引mysql>ALTERTABLEstudentADDINDEXidx_name(name);QueryOK,0rowsaffected(0.30sec)Records:0Duplicates:0Warnings:0#給性別sex列添加普通索引mysql>ALTERTABLEstudentADDINDEXidx_sex(sex);QueryOK,0rowsaffected(0.63sec)Records:0Duplicates:0Warnings:0#給年齡age列添加普通索引mysql>ALTERTABLEstudentADDINDEXidx_age(age);QueryOK,0rowsaffected(0.42sec)Records:0Duplicates:0Warnings:07.5索引的應(yīng)用#查看索引mysql>SHOWINDEXFROMstudent;+--------+----------+---------+-------------+----------+---------+-----------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality+--------+----------+---------+-------------+----------+---------+-----------+|student|0|PRIMARY|1|id|A|0||student|0|uk_id|1|id|A|0||student|1|idx_name|1|name|A|||student|1|idx_sex|1|sex|A|||student|1|idx_age|1|age|A||+--------+---------+---------+-----------+----------+---------+-----------+mysql>7.5索引的應(yīng)用7.5.2使用EXPLAIN語句分析索引在student表的id、name、sex、age這4列上分別添加索引后，怎么知道使用哪個(gè)索引呢？可以使用EXPLAIN語句來分析索引。實(shí)戰(zhàn)演練——用EXPLAIN語句分析student表的索引#使用用戶名root和相應(yīng)密碼，連接本地?cái)?shù)據(jù)庫C:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用數(shù)據(jù)庫shopmysql>USEshop;Databasechanged#查詢學(xué)生表studentmysql>SELECT*FROMstudent;+----+----------+------+------+--------+-------+-----+|id|name|sex|age|grade|class|num|+----+----------+------+------+--------+-------+-----+|1|劉明|男|19|高三|6班|3001||2|吳倩|女|18|高三|6班|3002||3|張欣|男|17|高二|1班|2001||4|孫曉|女|17|高二|1班|2002||5|趙英俊|男|16|高一|2班|1001||6|柳師師|女|17|高一|2班|1002|+----+----------+------+------+--------+-------+-----+6rowsinset(0.00sec)7.5索引的應(yīng)用#查詢姓名為劉明、性別為男的學(xué)生mysql>SELECT*FROMstudentWHEREname='劉明'andsex='男';+----+-------+------+------+-------+-------+------+|id|name|sex|age|grade|class|num|+----+-------+------+------+-------+-------+------+|1|劉明|男|19|高三|6班|3001|+----+-------+------+------+-------+-------+------+1rowinset(0.09sec)#分析索引使用情況mysql>EXPLAINSELECT*FROMstudentWHEREname='劉明'andsex='男'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_name,idx_sexkey:idx_namekey_len:1023ref:constrows:1filtered:50.00Extra:Usingwhere1rowinset,1warning(0.00sec)mysql>7.5索引的應(yīng)用EXPLAIN語句用于分析SQL語句的查詢情況，返回結(jié)果包含id、select_type、table、type、possible_keys、key、key_len、ref、rows、filtered、Extra等字段，字段說明如表7.2所示。表7.2EXPLAIN語句分析SQL語句的字段說明序號字段名稱字段說明1id用來標(biāo)識SELECT語句的編號，有多條SELECT語句就會(huì)有多個(gè)id，編號從1開始遞增2select_typeSIMPLE：簡單查詢，不包含子查詢和UNION查詢；PRIMARY：主查詢，也就是最外層的SELECT查詢；SUBQUERY：子查詢中的第一個(gè)SELECT查詢，不依賴于外部查詢的結(jié)果集；UNION：UNION查詢中的第二個(gè)或隨后的SELECT查詢，不依賴于外部查詢的結(jié)果集；UNION

RESULT：UNION查詢的結(jié)果集；DEPENDENTUNION：UNION查詢中的第二個(gè)SELECT查詢，依賴于外部查詢；DEPENDENTSUBQUERY：子查詢中的第一個(gè)SELECT查詢，依賴于外部查詢3table查詢所使用的表名，它可以是實(shí)際的表名，也可以是表的別名；當(dāng)有UNION（不是UNIONALL）時(shí)，UNIONRESULT的table字段的值為<union1,2>，1和2表示參與UNION的SELECT行id；NULL，MySQL能夠在優(yōu)化階段分解查詢語句，在執(zhí)行階段不用再訪問表或索引4type掃描類型，可以決定查詢速度，下面按最優(yōu)到最差排序。system＞const＞eq_ref＞ref＞range＞index＞ALLsystem：表僅有一行；const：用于常數(shù)值比較主鍵時(shí)，當(dāng)查詢的表僅有一行時(shí)，使用system；eq_ref：使用主鍵索引或者唯一索引進(jìn)行兩個(gè)表關(guān)聯(lián)查詢，得到唯一滿足的數(shù)據(jù)。ref：使用普通索引進(jìn)行兩個(gè)表關(guān)聯(lián)查詢。range：檢索給定范圍的行；index：全表掃描索引樹的節(jié)點(diǎn)；ALL：從頭到尾全表掃描7.5索引的應(yīng)用序號字段名稱字段說明5possible_

keys可能用到的索引，最終只能使用一個(gè)索引。如果為NULL，說明沒有可用的索引6key實(shí)際用到的索引，只能使用一個(gè)索引。如果為NULL，則沒有使用索引7key_len用到的索引長度，在不損失精確性的情況下，長度越短越好。key_len計(jì)算規(guī)則如下。（1）字符串：char(n)和varchar(n)，n均代表字符數(shù)，而不是字節(jié)數(shù)。如果是UTF-8，一個(gè)數(shù)字或字母占1個(gè)字節(jié)，一個(gè)漢字占3個(gè)字節(jié)；（2）char(n)：如果存漢字長度就是3n個(gè)字節(jié)；（3）varchar(n)：如果存漢字則長度是3n+2個(gè)字節(jié)，加的2個(gè)字節(jié)用來存儲(chǔ)字符串長度，因?yàn)関archar是變長字符串類型；（4）數(shù)值類型：tinyint，1個(gè)字節(jié)；smallint，2個(gè)字節(jié)；int，4個(gè)字節(jié)；bigint，8個(gè)字節(jié)；（5）時(shí)間類型：date，3個(gè)字節(jié)；timestamp，4個(gè)字節(jié)；datetime：8個(gè)字節(jié)；（6）如果字段允許為NULL，需要1個(gè)字節(jié)記錄是否為NULL8ref連接查詢時(shí)表與表之間的連接關(guān)系，顯示在key字段記錄的索引中，表查找值所用到的列或常量，常見的有const（常量）、字段名9rows掃描的行數(shù)，估計(jì)要讀取并檢測的行數(shù)，注意這個(gè)不是結(jié)果集里的行數(shù)10filtered一個(gè)百分比值，rows*filtered/100可以估算出將要和EXPLAIN語句中前一個(gè)表進(jìn)行連接的行數(shù)（前一個(gè)表指EXPLAIN語句中的id值比當(dāng)前表id值小的表）11Extra附加信息，查看索引的使用情況。Usingindex：使用索引，沒有查詢數(shù)據(jù)表、只用索引表完成查詢，也叫覆蓋索引。如果同時(shí)出現(xiàn)Usingwhere，代表使用索引來查找記錄，但是需要查詢到數(shù)據(jù)表；Usingwhere：條件查詢，使用WHERE語句來處理結(jié)果，并且查詢的列未被索引覆蓋；Usingindexcondition：查詢的列不完全被索引覆蓋，WHERE條件中是一個(gè)前導(dǎo)列的范圍；Usingfilesort：文件排序是一種排序策略，使用ORDERBY排序語句會(huì)出現(xiàn)該信息；Usingtemporary：為了得到結(jié)果，使用了臨時(shí)表，出現(xiàn)這種情況一般要進(jìn)行優(yōu)化；Selecttablesoptimizedaway：使用某些聚合函數(shù)（比如MAX()、MIN()）來訪問存在索引的某個(gè)字段7.5索引的應(yīng)用1．id查詢編號id是用來標(biāo)識SELECT語句的編號，有多條SELECT語句就會(huì)有多個(gè)id，但是如果查詢的SELECT語句的select_type和table相同，其id也不會(huì)自增，因?yàn)樗鼈儽徽J(rèn)為是一條語句。#id自增語句mysql>EXPLAINSELECT*FROMstudentWHEREid=(SELECTidFROMstudentWHEREid=3)\G***************************1.row***************************id:1select_type:PRIMARYtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:NULL***************************2.row***************************id:2select_type:SUBQUERYtable:studentpartitions:NULLtype:const7.5索引的應(yīng)用possible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:Usingindex2rowsinset,1warning(0.01sec)2rowsinset,1warning(0.08sec)#id不自增語句mysql>EXPLAINSELECT*FROMstudentWHEREidin(SELECTidFROMstudentWHEREid=3)\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:NULL***************************2.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:Usingindex2rowsinset,1warning(0.00sec)7.5索引的應(yīng)用2．select_type查詢類型SIMPLE為簡單查詢，不包含子查詢；PRIMARY為主查詢，也就是最外層的SELECT查詢；SUBQUERY為子查詢中的第一個(gè)SELECT查詢，不依賴于外部查詢的結(jié)果集；UNION為UNION查詢中的第二個(gè)或隨后的SELECT查詢，不依賴于外部查詢的結(jié)果集；UNIONRESULT為UNION查詢的結(jié)果集；DEPENDENTUNION為UNION查詢中的第二個(gè)SELECT查詢，依賴于外部查詢。mysql>EXPLAINSELECT*FROMstudent\G1rowinset,1warning(0.00sec)***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:6filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)（1）簡單查詢SIMPLE7.5索引的應(yīng)用mysql>EXPLAINSELECT*FROMstudentWHEREid=(SELECTidFROMstudentWHEREid=3)\G***************************1.row***************************id:1select_type:PRIMARYtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:NULL***************************2.row***************************id:2select_type:SUBQUERYtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:Usingindex2rowsinset,1warning(0.00sec)（2）主查詢PRIMARY、子查詢SUBQUERY7.5索引的應(yīng)用mysql>EXPLAINSELECT*FROMstudentWHEREid=2UNIONSELECT*FROMstudentWHEREid=3\G***************************1.row***************************id:1select_type:PRIMARYtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1

filtered:100.00Extra:NULL***************************2.row***************************（3）UNION查詢，在UNION之后的SELECT查詢，不依賴于外部查詢。7.5索引的應(yīng)用id:2select_type:UNIONtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:NULL***************************3.row***************************id:3select_type:UNIONRESULTtable:<union1,2>partitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:NULLfiltered:NULLExtra:Usingtemporary3rowsinset,1warning(0.01sec)mysql>7.5索引的應(yīng)用mysql>EXPLAINSELECT*FROMstudentWHEREidIN(SELECTidFROMstudentWHEREid=2UNIONSELECTidFROMstudentWHEREid=3)\G***************************1.row***************************id:1select_type:PRIMARYtable:studentpartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:6filtered:100.00Extra:Usingwhere***************************2.row***************************id:2select_type:DEPENDENTSUBQUERYtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:Usingindex***************************3.row***************************（4）DEPENDENTUNION查詢、DEPENDENTSUBQUERY查詢。7.5索引的應(yīng)用id:3select_type:DEPENDENTUNIONtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:Usingindex***************************4.row***************************id:4select_type:UNIONRESULTtable:<union2,3>partitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:NULLfiltered:NULLExtra:Usingtemporary4rowsinset,1warning(0.00sec)7.5索引的應(yīng)用3．table表名table是查詢的表名。它可以是實(shí)際的表名，也可以是表的別名，還可以為NULL。#實(shí)際表名mysql>EXPLAINSELECT*FROMstudent\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:6filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)#表的別名mysql>EXPLAINSELECT*FROMstudentastemp\G***************************1.row***************************7.5索引的應(yīng)用id:1select_type:SIMPLEtable:temppartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:6filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)#表名為NULLmysql>EXPLAINSELECT1\G***************************1.row***************************id:1select_type:SIMPLEtable:NULLpartitions:NULLtype:NULLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:NULLfiltered:NULLExtra:Notablesused1rowinset,1warning(0.00sec)7.5索引的應(yīng)用4．type掃描類型type是用在查詢過程中的掃描類型，不同的掃描類型可以決定查詢的速度。mysql>EXPLAINSELECT*FROMstudent\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:6filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)（1）type=ALL，掃描全表數(shù)據(jù)，速度最慢。7.5索引的應(yīng)用mysql>EXPLAINSELECTnameFROMstudent\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:indexpossible_keys:NULLkey:idx_namekey_len:1023ref:NULLrows:6filtered:100.00Extra:Usingindex1rowinset,1warning(0.00sec)（2）type=index，掃描索引所有節(jié)點(diǎn)。7.5索引的應(yīng)用mysql>EXPLAINSELECTnameFROMstudentWHEREid>2\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:rangepossible_keys:PRIMARY,uk_id,idx_namekey:PRIMARYkey_len:4ref:NULLrows:4filtered:100.00Extra:Usingwhere1rowinset,1warning(0.01sec)（3）type=range，掃描給定范圍的行。7.5索引的應(yīng)用mysql>EXPLAINSELECT*FROMstudentWHEREname='小明'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_namekey:idx_namekey_len:1023ref:constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)（4）type=ref，非唯一索引掃描，返回匹配某個(gè)單獨(dú)值的所有行。7.5索引的應(yīng)用mysql>EXPLAINSELECT*FROMstudentWHEREidIN(SELECTidFROMstudent)\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:ALLpossible_keys:PRIMARY,uk_idkey:NULLkey_len:NULLref:NULLrows:6filtered:100.00Extra:NULL***************************2.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:eq_refpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:shop.student.idrows:1filtered:100.00Extra:Usingindex2rowsinset,1warning(0.00sec)（5）type=eq_ref，唯一索引掃描，只能返回滿足條件的一行數(shù)據(jù)。7.5索引的應(yīng)用mysql>EXPLAINSELECT*FROMstudentWHEREid=1\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:NULL1rowinset,1warn