MySQL數(shù)據(jù)庫應用教程 課件第6章 索引

第6章索引《MySQL數(shù)據(jù)庫應用教程》劉瑞新主編配套資源目錄第6章索引6.1索引概述6.2查看索引6.3創(chuàng)建索引6.4使用索引6.5刪除索引6.1.1表中數(shù)據(jù)的搜索方式1.全表搜索方式2.索引搜索方式6.1索引概述6.1.2索引的概念6.1.3索引的類型1.按數(shù)據(jù)結構分類（1）B樹索引（2）Hash索引6.1索引概述2.按邏輯區(qū)分類（1）普通索引（INDEX）（2）唯一（UNIQUE）索引（3）主鍵（PRIMARYKEY）索引（4）全文（FULLTEXT）索引（5）空間（SPATIAL）索引6.1索引概述3.按使用時分類（1）單列索引（2）多列索引6.1索引概述4.聚簇索引和非聚簇索引（1）聚簇索引（2）非聚簇索引6.1索引概述使用SHOWINDEX語句查看表上建立的索引名、索引類型及相關參數(shù)，其語法格式為：SHOWINDEXFROMtb_name[FROMdb_name];SHOWINDEXFROM[db_name.]tb_name;6.2查看索引【例6-1】在studentinfo數(shù)據(jù)庫中，查看selectcourse表上建立的索引。USEstudentinfo;SHOWINDEXFROMselectcourse;6.2查看索引6.3.1創(chuàng)建索引的語句1.使用CREATETABLE語句創(chuàng)建索引CREATE[TEMPORARY]TABLE[db_name.]tb_name(column_namedata_type[列級完整性約束條件,][…,][表級完整性約束條件,][CONSTRAINTindex_name][UNIQUE|FULLTEXT][INDEX][index_name](index_column));6.3創(chuàng)建索引index_column索引列的語法格式如下：index_column_name[(length)][ASC|DESC]6.3創(chuàng)建索引2.使用CREATEINDEX語句創(chuàng)建索引CREATE[UNIQUE|FULLTEXT][INDEX]index_nameONtb_name(index_column_name[(length)][ASC|DESC]);6.3創(chuàng)建索引3.使用ALTERTABLE語句創(chuàng)建索引ALTERTABLEtb_nameADD[UNIQUE|FULLTEXT][INDEX][index_name](index_column_name[(length)][ASC|DESC]);4.自動創(chuàng)建索引6.3創(chuàng)建索引6.3.2創(chuàng)建索引實例1.沒有索引【例6-2】在studentinfo數(shù)據(jù)庫中，重新創(chuàng)建系表department，列定義有系編號DepartmentID、CHAR(2)；系名稱DepartmentName、VARCHAR(10)；系電話Telephone、VARCHAR(20)。DROPTABLEIFEXISTSdepartment;CREATETABLEdepartment(DepartmentIDCHAR(2),DepartmentNameVARCHAR(10),TelephoneCHAR(20));SHOWINDEXFROMdepartment;6.3創(chuàng)建索引2.普通索引【例6-3】重新創(chuàng)建department表，在定義表的同時為DepartmentID列定義普通索引，并按升序排列；創(chuàng)建表后為該表的DepartmentName列添加普通索引。DROPTABLEIFEXISTSdepartment;CREATETABLEdepartment(DepartmentIDCHAR(2),DepartmentNameVARCHAR(10),TelephoneCHAR(20),INDEXindex_id(IDASC));6.3創(chuàng)建索引CREATEINDEXindex_nameONdepartment(DepartmentName);SHOWINDEXFROMdepartment;6.3創(chuàng)建索引3.列值前綴普通索引【例6-4】在department表中，為DepartmentName列添加前3個字符的列值前綴普通索引；為Telephone列添加前14個字符的列值前綴普通索引。CREATEINDEXindex_name3ONdepartment(DepartmentName(3)DESC);ALTERTABLEdepartmentADDINDEXindex_telephone14(Telephone(14));SHOWINDEXFROMdepartment;6.3創(chuàng)建索引4.唯一索引【例6-5】重新創(chuàng)建department表，定義表的Telephone列是唯一鍵約束，在表的DepartmentName列上創(chuàng)建名為name的唯一索引。DROPTABLEIFEXISTSdepartment;CREATETABLEdepartment(DepartmentIDCHAR(2),DepartmentNameVARCHAR(10),TelephoneCHAR(20)UNIQUE,UNIQUEINDEXname(DepartmentName));6.3創(chuàng)建索引SHOWINDEXFROMdepartment;6.3創(chuàng)建索引【例6-6】使用CREATEINDEX在表department的DepartmentID列上創(chuàng)建唯一索引。CREATEUNIQUEINDEXdepartmentIDONdepartment(DepartmentID);6.3創(chuàng)建索引【例6-7】使用ALTERTABLE語句在表department的電話列Telephone上創(chuàng)建唯一索引。ALTERTABLEdepartmentADDUNIQUEINDEX(TelephoneDESC);6.3創(chuàng)建索引5.多列索引【例6-8】創(chuàng)建teacher表，列有TeacherID、CHAR(6)；TeacherName、CHAR(20)；Age、INT；DepartmentName、VARCHAR(10)，DepartmentID、CHAR(2)。CREATETABLEteacher(TeacherIDCHAR(6),TeacherNameCHAR(20),AgeINT,DepartmentNameVARCHAR(10),DepartmentIDCHAR(2),INDEXname(DepartmentNameASC,TeacherNameDESC));6.3創(chuàng)建索引SHOWINDEXFROMteacher;CREATEINDEXname2ONteacher(DepartmentName,TeacherName);6.3創(chuàng)建索引6.全文索引【例6-9】在teacher表中添加簡歷列Note、VARCHAR(50)，并指定Note列為全文索引。ALTERTABLEteacherADDCOLUMNNoteVARCHAR(50),ADDFULLTEXTINDEXindex_note(Note);6.3創(chuàng)建索引7.主鍵索引和外鍵索引【例6-10】重新創(chuàng)建department表，在定義列時對DepartmentID列設置主鍵約束，對Telephone列設置唯一鍵，對DepartmentName列創(chuàng)建唯一索引。DROPTABLEIFEXISTSdepartment;CREATETABLEdepartment(DepartmentIDCHAR(2)PRIMARYKEY,#設置主鍵約束

DepartmentNameVARCHAR(10),TelephoneCHAR(20)UNIQUE,#設置唯一約束

UNIQUEINDEXname(DepartmentName)#創(chuàng)建唯一索引);6.3創(chuàng)建索引SHOWINDEXdepartment;6.3創(chuàng)建索引【例6-11】在teacher表中，給TeacherID列添加主鍵約束。ALTERTABLEteacherADDPRIMARYKEY(TeacherID);6.3創(chuàng)建索引【例6-12】重新創(chuàng)建teacher表，設置TeachertID列為主鍵約束，設置外鍵約束FK_teacher，通過外鍵DepartmentID列與department表建立外鍵關系。DROPTABLEIFEXISTSteacher;CREATETABLEteacher(TeacherIDCHAR(6),TeacherNameCHAR(20),AgeINT,DepartmentIDCHAR(2),CONSTRAINTPK_teacherPRIMARYKEY(TeacherID),

CONSTRAINTFK_teacherFOREIGNKEY(DepartmentID)REFERENCESdepartment(DepartmentID));6.3創(chuàng)建索引SHOWINDEXFROMteacher;6.3創(chuàng)建索引6.4.1EXPLAIN語句的使用1.EXPLAIN語句的語法EXPLAINSQL_statement;【例6-13】在studentinfo數(shù)據(jù)庫中，顯示student表的索引，然后用EXPLAIN語句執(zhí)行查詢，了解EXPLAIN運行的結果。SHOWINDEXFROMstudent;6.4使用索引6.4使用索引6.4使用索引6.4使用索引6.4使用索引2.使用EXPLAIN語句查看分析結果【例6-14】在studentinfo數(shù)據(jù)庫中，創(chuàng)建一個新表tb3，表結構為：ID列、INT型、非空、主鍵、自動遞增，Name列、VARCHAR(20)型，Age列、INT型，在表的Name列上建立普通索引；并添加幾條記錄。USEstudentinfo;CREATETABLEtb3(IDINTNOTNULLPRIMARYKEYAUTO_INCREMENT,NameVARCHAR(20),AgeINT,INDEXindex_name(NameASC));6.4使用索引INSERTINTOtb3(Name,Age)VALUES('Jack',18),('Lily',19),('Tom',17),('Tina',18),('Alina',18);SELECT*FROMtb3;SHOWINDEXFROMtb3;6.4使用索引EXPLAINSELECT*FROMtb3WHEREID=3;EXPLAINSELECT*FROMtb3WHEREName='Lily';6.4使用索引EXPLAINSELECT*FROMtb3WHEREAge=19;6.4使用索引【例6-15】使用ALTERTABLE命令在tb3表的Name列的前4個字節(jié)上創(chuàng)建降序排序。ALTERTABLEtb3ADDINDEXindex_name4(name(4)DESC);6.4使用索引EXPLAINSELECT*FROMtb3WHEREName='abcdefg';EXPLAINSELECT*FROMtb3WHEREName='abc';6.4使用索引6.4.2指定使用的索引SELECT表達式列表FROMTABLE[{USE|IGNORE|FORCE}INDEX(key_list)]WHERE條件;【例6-16】指定使用index_name4索引用于Name查詢。SELECT*FROMtb3USEINDEX(index_name4)WHEREName='abcdefg';EXPLAINSELECT*FROMtb3USEINDEX(index_name4)WHEREName='abcdefg';6.4使用索引1.使用DROPINDEX語句刪除索引DROPINDEXindex_nameONtb_name;【例6-17】刪除tb3表中創(chuàng)建的索引index_name4。DROPINDEXindex_name4ONtb3;6.5刪除索引2