主講教師：姜云橋任務4.2索引的開發子項目4數據庫高級應用開發任務背景由于數據庫在執行一條SQL語句的時候，默認的方式是根據搜索條件進行全表掃描，遇到匹配條件的就加入搜索結果集合。在進行涉及多個表連接，包括了許多搜索條件（例如大小比較、Like匹配等），而且表數據量特別大的查詢時，在沒有索引的情況下，MySQL需要執行的掃描行數會很大，速度也會很慢。任務目標1.能創建索引2.能管理索引知識目標1.了解索引2.了解索引的分類3.理解索引的設計原則4.掌握創建索引的方法5.掌握管理索引的方法能力目標任務要求本任務將從認識索引、索引的分類以及索引的設計原則等方面著手，介紹創建和管理索引的方法。特別要注意的是，索引并不是越多越好，要正確認識索引的重要性和設計原則，創建合適的索引。任務必備知識想一想索引是什么？任務必備知識4.2.1索引的概念索引是一種特殊的數據庫結構，可以用來快速查詢數據庫表中的記錄。對于索引的理解可以直接類比查字典。當我們需要查找某個漢字的意思時，我們并不是從頭挨著找到漢字，而是通過拼音索引快速鎖定漢字所在的頁碼，進而找到對應漢字的解釋。如果我們并不知道漢字的讀音，可以通過部首筆畫索引快速鎖定漢字的頁碼，從而找到漢字的讀音和解釋。任務必備知識4.2.1索引的概念日常使用的索引主要包括如下幾種：普通索引（index）：索引的關鍵字是index，這是最基本的索引，它沒有任何限制。唯一性索引（unique）：關鍵字是unique。與普通索引類似，但是unique索引列的值必須唯一，允許有空值。如果是組合索引，則列值的組合必須唯一。在一個表上可以創建多個unique索引。主鍵索引（primarykey）：它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時創建主鍵索引。也可通過修改表的方法增加主鍵，但一個表只能有一個主鍵索引。任務必備知識想一想創建索引有幾種方式，分別是？任務必備知識創建索引有3種方式，分別是:1.創建表的時候創建索引2.在已經存在的表上創建索引3.使用ALTERTABLE語句來創建索引4.2.2索引的建立任務必備知識一、創建表時創建【任務4.2.1】創建表student2，s_no為主鍵索引，s_name為唯一性索引，并在s_address列上前6位字符創建索引創建表的時候可以直接創建索引，這種方式最簡單、方便。4.2.2索引的建立任務必備知識4.2.2索引的建立mysql>createtablestudent2->(->s_nochar(6)primarykeycomment'學號',->s_namevarchar(20)uniquecomment'姓名',->sexchar(2)default'男'comment'性別',->birthdatecomment'出生日期',->d_nochar(6)comment'所在系部',->addressvarchar(20)comment'家庭地址',->phonevarchar(20)comment'聯系電話',->indexaddress_index(address(6))->);任務必備知識二、用createindex語句創建1.創建普通索引如果表已建好，可以使用createindex語句建立索引。createindexindex_nameontbl_name(col_name(length)[asc|desc],...)4.2.2索引的建立【任務4.2.2】student的d_no字段經常作為查詢條件，建立普通索引mysql>createindexd_no_indexonstudent(d_no);任務必備知識2.創建唯一性索引4.2.2索引的建立【任務4.2.3】在course表的c_name列上建立一個唯一性索引c_name_indexmysql>createuniqueindexc_name_indexoncourse(c_name);像課程表的課程名，部門表的部門名，商品表的商品名之類的字段，一般情況下是，可建立一個唯一性索引。任務必備知識3.創建多列索引4.2.2索引的建立【任務4.2.4】在score表的s_no和c_no列上建立一個復合索引score_indexmysql>createindexscore_indexonscore(s_no,c_no);可以在一個索引的定義中包含多個列，中間用逗號隔開，但是它們要屬于同一個表。這樣的索引叫做復合索引。任務必備知識三、通過altertable語句創建索引在已經存在的表上可以用altertable語句創建索引。altertabletbl_nameadd{unique|index|primarykey}[index_name](col_name(length)[asc|desc],...)4.2.2索引的建立任務必備知識4.2.2索引的建立【任務4.2.5】在teacher表上建立t_no主鍵索引（還未建立主鍵），建立t_name和d_no的復合索引，以加速表的檢索速度mysql>altertableteacher ->addprimarykey(t_no), ->addindexmark_index(t_name,d_no);任務必備知識4.2.2索引的建立【任務4.2.6】在department表中的d_name創建唯一性索引mysql>altertabledepartment ->adduniqueindexd_name_index(d_name);分析與討論（1）主鍵索引必定是唯一的，唯一性索引不一定是主鍵。（2）一張表上只能一個主鍵，但可以有一個或者多個唯一性索引。任務必備知識想一想MySQL如何查看索引？任務必備知識如果想要查看表中創建的索引的情況，可以使用showindexfromtbl_name語句4.2.3索引的查看【任務4.2.7】查看表student2中的索引mysql>showindexfromstudent2\g任務必備知識想一想MySQL如何刪除索引？任務必備知識刪除索引是指將表中已經存在的索引刪除掉。一些不再使用的索引會降低表的更新速度，影響數據庫的性能。對于這樣的索引，應該將其刪除。對于已經存在的索引，可以通過dropindexindex_nameontbl_name來刪除。4.2.4索引的刪除【任務4.2.8】刪除course表的mark索引mysql>dropindexmark_indexoncourse;也可用ALTERTABLE語句刪除：altertabletbl_namedrop{indexindex_name|primarykey}任務必備知識4.2.4索引的刪除【任務4.2.9】刪除course表上的主鍵索引mysql>altertablecoursedropprimarykey;【任務4.2.10】刪除course表上的唯一性索引c_name_indexmysql>altertablecoursedropindexc_name_index;任務必備知識（1）刪除唯一性索引，如同刪除普通索引一樣，用dropindex語句即可，不能寫成dropuniqueindex，但是創建唯一性索引要寫成adduniqueindex。（2）刪除主鍵索引，則需要altertable直接使用dropprimarykey子句進行刪除，不需要提供索引名稱，因為一個表中只有一個主鍵分析與討論任務實施在rsgl數據庫中完成下列操作：1.在employee表上通過createindex語句在birth字段創建名為index_birth的索引，創建完成后可查詢到如下輸出（省略了部分輸出）：mysql>showindexfromemployee\G***************************2.row***************************Table:employeeNon_unique:1Key_name:index_birthSeq_in_index:1Column_name:birth……任務實施2.在employee表上通過altertable語句在e_name字段創建名為index_name的唯一性索引，創建完成后可查詢到如下輸出（省略部分輸出）：mysql>showindexfromemployee\G***************************3.row***************************Table:employeeNon_unique:1Key_name:index_nameSeq_in_index:1Column_name:e_name……任務實施3.在employee表上通過createindex語句在d_id和education字段上創建多列索引。***************************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_name:education……任務實施4.刪除employee表上的唯一性索引，刪除完成后查看索引信息，確保刪除。5.刪除employee表上的主鍵索引，刪除完成后查看索引信息，確保刪除。6.刪除employee表上的多列索引，刪除完成后查看索引信息，確保刪除。任務實施要求撰寫實訓總結，把完成任務