MySQL數(shù)據(jù)庫 課件 4.2 索引的開發(fā)

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