MySQL數(shù)據(jù)庫系統(tǒng)與應(yīng)用教程課件 第9章 索引

第9章索引學(xué)習(xí)目標(biāo)：理解索引的基本概念，掌握索引的創(chuàng)建、刪除和使用的方法。9.1概述9.1.1索引的概念1．搜索表中數(shù)據(jù)的方式（1）全表搜索方式（2）索引搜索方式2．索引的基本概念9.1概述9.1.2索引的分類1．按邏輯分類（1）普通索引（INDEX）（2）唯一（UNIQUE）索引（3）主鍵（PRIMARYKEY）索引（4）全文（FULLTEXT）索引（5）空間（SPATIAL）索引9.1概述2．按使用時(shí)的列數(shù)分類（1）單列索引（2）多列索引3．按物理順序分類4．按數(shù)據(jù)結(jié)構(gòu)分類9.1概述9.1.3設(shè)置索引的原則9.1.4查看索引SHOWINDEXFROMtb_name[FROMdb_name];SHOWINDEXFROM[db_name.]tb_name;【例9-1】在數(shù)據(jù)庫student_db中，查看student表上建立的索引。USEstudent_db;SHOWINDEXFROMstudent;9.2創(chuàng)建索引9.2.1創(chuàng)建索引的語句1．使用CREATETABLE語句創(chuàng)建索引CREATE[TEMPORARY]TABLE[db_name.]tb_name(column_namedata_typePRIMARYKEY|UNIQUE[KEY]|[其他列級(jí)完整性約束],[…,][表級(jí)完整性約束]);PRIMARYKEY[index_type](index_column_name1,…)|[UNIQUE[KEY]|FULLTEXT|SPATIAL][INDEX][index_name](index_column_name1[(length)][ASC|DESC])|FOREIGNKEY(index_column_name1,…)REFERENCES參照表名(參照表中的主鍵列,…)9.2創(chuàng)建索引2．使用CREATEINDEX語句創(chuàng)建索引CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_name[index_type]ONtb_name(index_column_name[(length)][ASC|DESC],…);9.2創(chuàng)建索引3．使用ALTERTABLE語句創(chuàng)建索引ALTERTABLEtb_nameADD[UNIQUE|FULLTEXT][INDEX][index_name](index_column_name[(length)][ASC|DESC],…);9.2創(chuàng)建索引4．自動(dòng)創(chuàng)建索引9.2創(chuàng)建索引9.2.2創(chuàng)建索引的實(shí)例1．沒有索引【例9-2】創(chuàng)建“各省名稱”表，其中包含全國各省名稱及其代碼，表中的列包括代碼、省名、簡稱和英文。CREATETABLE各省名稱(

代碼CHAR(2),

省名CHAR(10),

簡稱CHAR(1),

英文VARCHAR(50));SHOWINDEXFROM各省名稱;9.2創(chuàng)建索引9.2創(chuàng)建索引2．普通索引【例9-3】重新創(chuàng)建“各省名稱”表，在定義表的同時(shí)為“代碼”列定義普通索引，并按升序排列；為“省名”列定義普通索引，并按降序排列。創(chuàng)建后為該表的“簡稱”和“英文”列添加普通索引。DROPTABLEIFEXISTS各省名稱;CREATETABLE各省名稱(

代碼CHAR(2),

省名CHAR(10),

簡稱CHAR(1),

英文VARCHAR(50),INDEXcode(代碼ASC),INDEXsheng(省名DESC));SHOWINDEXFROM各省名稱;9.2創(chuàng)建索引#定義表后再創(chuàng)建索引，添加多列普通索引CREATEINDEX省名_indexON各省名稱(簡稱DESC,英文ASC);SHOWINDEXFROM各省名稱;#查看該表上建立的索引3．列值前綴普通索引【例9-4】在“各省名稱”表中，為“省名”列添加前2個(gè)字符的列值前綴普通索引；把“英文”列修改為前12個(gè)字符的列值前綴普通索引。CREATEINDEX省名_index1ON各省名稱(省名(2)ASC);SHOWINDEXFROM各省名稱;

9.2創(chuàng)建索引ALTERTABLE各省名稱ADDINDEX省名_index2(英文(12));SHOWINDEXFROM各省名稱;9.2創(chuàng)建索引4．唯一索引【例9-5】DROPTABLEIFEXISTS各省名稱;CREATETABLE各省名稱(

代碼CHAR(2)UNIQUE,

省名CHAR(10),

簡稱CHAR(1),

英文VARCHAR(50),UNIQUEINDEXsheng_name(省名(2)DESC));SHOWINDEXFROM各省名稱;9.2創(chuàng)建索引5．多列索引【例9-6】在“各省名稱”表中，創(chuàng)建“省名”和“簡稱”的多列唯一索引。SQL語句和運(yùn)行結(jié)果如下：CREATEUNIQUEINDEX各省名稱_index3ON各省名稱(省名,簡稱);SHOWINDEXFROM各省名稱;9.2創(chuàng)建索引【例9-7】在“各省名稱”表中，在“簡稱”和“英文”列上創(chuàng)建多列普通索引。SQL語句和運(yùn)行結(jié)果如下：ALTERTABLE各省名稱ADDINDEX省名_index4(簡稱ASC,英文(10)DESC);SHOWINDEXFROM各省名稱;9.2創(chuàng)建索引6．全文索引全文索引只能建立在數(shù)據(jù)類型為CHAR、VARCHAR和TEXT的列上。【例9-8】在“各省名稱”表中，為“英文”列添加全文索引。ALTERTABLE各省名稱ADDFULLTEXTindex5(英文);SHOWINDEXFROM各省名稱;9.2創(chuàng)建索引7．主鍵索引和外鍵索引【例9-9】DROPTABLEIFEXISTSscore1;CREATETABLEscore1(StudentIDCHAR(10),CourseIDCHAR(6),ScoreDECIMAL(4,1),TermTINYINT,PRIMARYKEY(StudentID,CourseID),#定義主鍵約束

FOREIGNKEY(CourseID)REFERENCEScourse(CourseID)#定義外鍵約束);SHOWINDEXFROMscore1;9.2創(chuàng)建索引【例9-10】在“各省名稱”表中，為“省名”列添加主鍵約束。ALTERTABLE各省名稱ADDPRIMARYKEY(省名);SHOWINDEXFROM各省名稱;9.2創(chuàng)建索引9.3使用索引9.3.1多列索引的使用【例9-11】CREATETABLEtelephone_book(LastNameVARCHAR(20),FirstNameVARCHAR(20),GenderCHAR(10),TelephoneVARCHAR(20)UNIQUE,INDEXidx_name_gender(LastName,FirstName,Gender,Telephone));SHOWINDEXFROMtelephone_book;按照最左匹配規(guī)則，索引的查詢?nèi)缦拢篠ELECT*FROMtelephone_bookWHERELastName='?';SELECT*FROMtelephone_bookWHERELastName='?'ANDFirstName='?';SELECT*FROMtelephone_bookWHERELastName='?'ANDFirstName='?'ANDGender='?';SELECT*FROMtelephone_bookWHERELastName='?'ANDFirstName='?'ANDGender='?'ANDTelephone='?';9.3使用索引9.3.2EXPLAIN語句EXPLAIN的語法格式：EXPLAINSQL_statement;9.3使用索引9.3.3指定要使用的索引SELECT表達(dá)式列表FROMTABLE[{USE|IGNORE|FORCE}INDEX(key_list)]WHERE條件;【例9-12】指定使用index_name8索引用于Name查詢。SELECT*FROMname_listUSEINDEX(index_name8)WHEREName='1234567890';EXPLAINSELECT*FROMname_listUSEINDEX(index_name8)WHEREName='1234567890';9.3使用索引9.4刪除索引9.4.1DROPINDEX語句DROPINDEXindex_nameONtb_name;【例9-13】刪除name_list表中的索引index_name8。DROPINDEXindex_name8ONname_list;SHOWINDEXFROMname_list;9.4刪除索引9.4.2ALTERTABLE語句ALTERTABLEtb_nameDROPINDEXindex_name;ALTERТАВLЕtb_nameDRОРР