教案首頁課程名稱MySQL數(shù)據(jù)庫開發(fā)本節(jié)課題任務(wù)4.2索引的開發(fā)授課方式理實(shí)一體化教學(xué)參考及教具姜云橋主編.MySQL數(shù)據(jù)庫開發(fā).臨科院：教務(wù)處,2023計算機(jī)MySQL數(shù)據(jù)庫軟件多媒體教學(xué)系統(tǒng)資料包教學(xué)目標(biāo)及基本要求了解索引了解索引的分類理解索引的設(shè)計原則掌握創(chuàng)建索引的方法掌握管理索引的方法能創(chuàng)建索引、管理索引培養(yǎng)自主學(xué)習(xí)能力教學(xué)重點(diǎn)教學(xué)難點(diǎn)索引的分類、創(chuàng)建索引、管理索引管理索引教學(xué)小結(jié)作業(yè)及要求按照任務(wù)書做好預(yù)習(xí)任務(wù)創(chuàng)建和管理索引教后反思注意：用微課視頻每個典型操作做示范

教案用紙教學(xué)內(nèi)容、方法和過程附記任務(wù)4.2索引的開發(fā)【任務(wù)要求】本任務(wù)將從認(rèn)識索引、索引的分類以及索引的設(shè)計原則等方面著手，介紹創(chuàng)建和管理索引的方法。特別要注意的是，索引并不是越多越好，要正確認(rèn)識索引的重要性和設(shè)計原則，創(chuàng)建合適的索引。【任務(wù)必備知識】4.2.1索引的概念索引是？索引：是一種特殊的數(shù)據(jù)庫結(jié)構(gòu)，可以用來快速查詢數(shù)據(jù)庫表中的特定記錄。在MySQL中，所有的數(shù)據(jù)類型都可以被索引。MySQL支持的索引主要有？Hash索引和B-Tree索引。目前，大部分MySQL索引都是以B-樹（BTREE）方式存儲的，是MySQL數(shù)據(jù)庫中使用最為頻繁的索引類型，除了Archive存儲引擎之外的其他所有的存儲引擎都支持B-Tree索引。不僅在MySQL中如此，在其他的很多數(shù)據(jù)庫管理系統(tǒng)中B-Tree索引也同樣是作為最主要的索引類型的，這主要是因?yàn)锽-Tree索引的存儲結(jié)構(gòu)在數(shù)據(jù)庫的數(shù)據(jù)檢索中有著非常優(yōu)異的表現(xiàn)。一般來說，MySQL中的B-Tree索引的物理文件大多是以BalanceTree的結(jié)構(gòu)來存儲的，也就是所有實(shí)際需要的數(shù)據(jù)都存放于Tree的葉子結(jié)點(diǎn)，而且到任何一個葉子結(jié)點(diǎn)的最短路徑的長度都是完全相同的，所以把它稱為B-Tree索引。MySQLHash索引相對于B-Tree索引，檢索效率要高上不少。雖然Hash索引效率高，但是，由于Hash索引本身的特殊性也帶來了很多限制和弊端，主要有以下內(nèi)容。區(qū)別（1）MySQLHash索引僅僅能滿足“=”，“IN”和“<=>”查詢，不能使用范圍查詢。（2）MySQLHash索引無法被用來避免數(shù)據(jù)的排序操作。（3）MySQLHash索引不能利用部分索引鍵查詢。（4）MySQLHash索引在任何時候都不能避免表掃描。（5）MySQLHash索引遇到大量Hash值相等的情況后，性能并不一定就會比B-Tree索引高。索引分類MySQL的索引包括普通索引（INDEX）、唯一性索引（UNIQUE）、主鍵（PRIMARYKEY）、全文索引（FULLTEXT）和空間索引（SPATIAL）。（1）普通索引（INDEX） 索引的關(guān)鍵字是INDEX，這是最基本的索引，它沒有任何限制。（2）唯一性索引（UNIQUE） 關(guān)鍵字是UNIQUE。與普通索引類似，但是UNIQUE索引列的值必須唯一，允許有空值。如果是組合索引，則列值的組合必須唯一。在一個表上可以創(chuàng)建多個UNIQUE索引。（3）主鍵索引（PRIMARYKEY） 它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時創(chuàng)建主鍵索引。也可通過修改表的方法增加主鍵，但一個表只能有一個主鍵索引。（4）全文索引（FULLTEXT） FULLTEXT索引只能對CHAR、VARCHAR和TEXT類型的列編制索引，并且只能在MyISAM表中編制。在MySQL默認(rèn)情況下，對于中文作用不大。（5）空間索引（SPATIAL） SPATIAL索引只能對空間列編制索引，并且只能在MyISAM表中編制。本書不討論。另外，按索引建立在一列還是多列上，又可以分為單列索引、多列索引（復(fù)合索引）。4.2.2索引的建立創(chuàng)建索引有3種方式，分別是創(chuàng)建表的時候創(chuàng)建索引、在已經(jīng)存在的表上創(chuàng)建索引和使用ALTERTABLE語句來創(chuàng)建索引。下面將詳細(xì)講解這3種創(chuàng)建索引的方法。一、創(chuàng)建表時創(chuàng)建創(chuàng)建表的時候可以直接創(chuàng)建索引，這種方式最簡單、方便。主鍵索引和唯一性索引在前面講解約束的時候?qū)嶋H上已經(jīng)完成了創(chuàng)建，也就說我們在創(chuàng)建主鍵約束和唯一性約束的時候會自動添加索引，我們主要講解普通索引的創(chuàng)建方式，語法同創(chuàng)建表時一樣，不過關(guān)鍵字是index（col_name,…）。【任務(wù)4.2.1】創(chuàng)建表student2，s_no為主鍵索引，s_name為唯一性索引，并在s_address列上前6位字符創(chuàng)建索引。mysql>createtablestudent2->(->s_nochar(6)primarykeycomment'學(xué)號',->s_namevarchar(20)uniquecomment'姓名',->sexchar(2)default'男'comment'性別',->birthdatecomment'出生日期',->d_nochar(6)comment'所在系部',->addressvarchar(20)comment'家庭地址',->phonevarchar(20)comment'聯(lián)系電話',->indexaddress_index(address(6))->);查看創(chuàng)建的索引：mysql>showindexfromstudent2\G二、用createindex語句創(chuàng)建如果表已建好，可以使用createindex語句建立索引。createindexindex_nameontbl_name(col_name(length)[asc|desc],...)1.創(chuàng)建普通索引【任務(wù)4.2.2】student的d_no字段經(jīng)常作為查詢條件，建立普通索引。mysql>createindexd_no_indexonstudent(d_no);2.創(chuàng)建唯一性索引像課程表的課程名，部門表的部門名，商品表的商品名之類的字段，一般情況下是，可建立一個唯一性索引。【任務(wù)4.2.3】在course表的c_name列上建立一個唯一性索引c_name_index。mysql>createuniqueindexc_name_indexoncourse(c_name);3.創(chuàng)建多列索引可以在一個索引的定義中包含多個列，中間用逗號隔開，但是它們要屬于同一個表。這樣的索引叫做復(fù)合索引。【任務(wù)4.2.4】在score表的s_no和c_no列上建立一個復(fù)合索引score_index。mysql>createindexscore_indexonscore(s_no,c_no);分析與討論：1）對于char和varchar列，只用一列的一部分就可創(chuàng)建索引。創(chuàng)建索引時，使用col_name(length)語法。blob和text列也可以編制索引，但是必須給出前綴長度。2）createindex語句并不能創(chuàng)建主鍵。3）索引名可以不寫，若不寫索引名，則默認(rèn)與列名相同。三、通過altertable語句創(chuàng)建索引在已經(jīng)存在的表上可以用altertable語句創(chuàng)建索引。altertabletbl_nameadd{unique|index|primarykey}[index_name](col_name(length)[asc|desc],...)【任務(wù)4.2.5】在teacher表上建立t_no主鍵索引（還未建立主鍵），建立t_name和d_no的復(fù)合索引，以加速表的檢索速度。mysql>altertableteacher->addprimarykey(t_no),->addindexmark_index(t_name,d_no);【任務(wù)4.2.6】在department表中的d_name創(chuàng)建唯一性索引。mysql>altertabledepartment->adduniqueindexd_name_index(d_name);4.2.3索引的查看如果想要查看表中創(chuàng)建的索引的情況，可以使用showindexfromtbl_name語句。4.2.4索引的刪除刪除索引是指將表中已經(jīng)存在的索引刪除掉。一些不再使用的索引會降低表的更新速度，影響數(shù)據(jù)庫的性能。對于這樣的索引，應(yīng)該將其刪除。對于已經(jīng)存在的索引，可以通過dropindexindex_nameontbl_name來刪除。分析與討論:1)刪除唯一性索引，如同刪除普通索引一樣，用dropindex語句即可，不能寫成dropuniqueindex，但是創(chuàng)建唯一性索引要寫成adduniqueindex。2)刪除主鍵索引，則需要altertable直接使用dropprimarykey子句進(jìn)行刪除，不需要提供索引名稱，因?yàn)橐粋€表中只有一個主鍵。盡管索引能夠顯著提高查找效率，但也并非越多越好，在設(shè)計索引的時候應(yīng)該遵循如下原則：1)在主鍵上創(chuàng)建索引，在InnoDB中如果通過主鍵來訪問數(shù)據(jù)效率是非常高的。2)為經(jīng)常需要排序、分組和聯(lián)合操作的字段建立索引，即那些將用于join、where判斷和orderby排序的字段上。3)盡量不要對數(shù)據(jù)庫中某個含有大量重復(fù)的值的字段建立索引，如“性別”字段，在這樣的字段上建立索引將不會有什么幫助；相反，還有可能降低數(shù)據(jù)庫的性能。4)盡量使用數(shù)據(jù)量少的索引。5)盡量使用前綴來索引。【項(xiàng)目實(shí)踐】在rsgl數(shù)據(jù)庫中完成下列操作。1.在employee表上通過createindex語句在birth字段創(chuàng)建名為index_birth的索引，創(chuàng)建完成后可查詢到如下輸出（省略了部分輸出）：mysql>showindexfromemployee\G***************************2.row***************************Table:employeeNon_unique:1Key_name:index_birthSeq_in_index:1Column_name:birth……2.在employee表上通過altertable語句在e_name字段創(chuàng)建名為index_name的唯一性索引，創(chuàng)建完成后可查詢到如下輸出（省略部分輸出）：mysql>showindexfromemployee\G***************************3.row***************************Table:employeeNon_unique:1Key_name:index_nameSeq_in_index:1Column_name:e_name……3.在employee表上通過createindex語句在d_id和education字段上創(chuàng)建多列索引。***************************4.row***************************Table:employeeNon_unique:1Key_name:index_mulSeq_in_index:1Column_name:d_id……***************************5.row***************************Table:employeeNon_unique:1Key_name:index_mulSeq_in_index:2Column_n