MySQL数据库系统与应用教程课件 第9章 索引

第9章索引学习目标：理解索引的基本概念，掌握索引的创建、删除和使用的方法。9.1概述9.1.1索引的概念1．搜索表中数据的方式（1）全表搜索方式（2）索引搜索方式2．索引的基本概念9.1概述9.1.2索引的分类1．按逻辑分类（1）普通索引（INDEX）（2）唯一（UNIQUE）索引（3）主键（PRIMARYKEY）索引（4）全文（FULLTEXT）索引（5）空间（SPATIAL）索引9.1概述2．按使用时的列数分类（1）单列索引（2）多列索引3．按物理顺序分类4．按数据结构分类9.1概述9.1.3设置索引的原则9.1.4查看索引SHOWINDEXFROMtb_name[FROMdb_name];SHOWINDEXFROM[db_name.]tb_name;【例9-1】在数据库student_db中，查看student表上建立的索引。USEstudent_db;SHOWINDEXFROMstudent;9.2创建索引9.2.1创建索引的语句1．使用CREATETABLE语句创建索引CREATE[TEMPORARY]TABLE[db_name.]tb_name(column_namedata_typePRIMARYKEY|UNIQUE[KEY]|[其他列级完整性约束],[…,][表级完整性约束]);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创建索引2．使用CREATEINDEX语句创建索引CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_name[index_type]ONtb_name(index_column_name[(length)][ASC|DESC],…);9.2创建索引3．使用ALTERTABLE语句创建索引ALTERTABLEtb_nameADD[UNIQUE|FULLTEXT][INDEX][index_name](index_column_name[(length)][ASC|DESC],…);9.2创建索引4．自动创建索引9.2创建索引9.2.2创建索引的实例1．没有索引【例9-2】创建“各省名称”表，其中包含全国各省名称及其代码，表中的列包括代码、省名、简称和英文。CREATETABLE各省名称(

代码CHAR(2),

省名CHAR(10),

简称CHAR(1),

英文VARCHAR(50));SHOWINDEXFROM各省名称;9.2创建索引9.2创建索引2．普通索引【例9-3】重新创建“各省名称”表，在定义表的同时为“代码”列定义普通索引，并按升序排列；为“省名”列定义普通索引，并按降序排列。创建后为该表的“简称”和“英文”列添加普通索引。DROPTABLEIFEXISTS各省名称;CREATETABLE各省名称(

代码CHAR(2),

省名CHAR(10),

简称CHAR(1),

英文VARCHAR(50),INDEXcode(代码ASC),INDEXsheng(省名DESC));SHOWINDEXFROM各省名称;9.2创建索引#定义表后再创建索引，添加多列普通索引CREATEINDEX省名_indexON各省名称(简称DESC,英文ASC);SHOWINDEXFROM各省名称;#查看该表上建立的索引3．列值前缀普通索引【例9-4】在“各省名称”表中，为“省名”列添加前2个字符的列值前缀普通索引；把“英文”列修改为前12个字符的列值前缀普通索引。CREATEINDEX省名_index1ON各省名称(省名(2)ASC);SHOWINDEXFROM各省名称;

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

代码CHAR(2)UNIQUE,

省名CHAR(10),

简称CHAR(1),

英文VARCHAR(50),UNIQUEINDEXsheng_name(省名(2)DESC));SHOWINDEXFROM各省名称;9.2创建索引5．多列索引【例9-6】在“各省名称”表中，创建“省名”和“简称”的多列唯一索引。SQL语句和运行结果如下：CREATEUNIQUEINDEX各省名称_index3ON各省名称(省名,简称);SHOWINDEXFROM各省名称;9.2创建索引【例9-7】在“各省名称”表中，在“简称”和“英文”列上创建多列普通索引。SQL语句和运行结果如下：ALTERTABLE各省名称ADDINDEX省名_index4(简称ASC,英文(10)DESC);SHOWINDEXFROM各省名称;9.2创建索引6．全文索引全文索引只能建立在数据类型为CHAR、VARCHAR和TEXT的列上。【例9-8】在“各省名称”表中，为“英文”列添加全文索引。ALTERTABLE各省名称ADDFULLTEXTindex5(英文);SHOWINDEXFROM各省名称;9.2创建索引7．主键索引和外键索引【例9-9】DROPTABLEIFEXISTSscore1;CREATETABLEscore1(StudentIDCHAR(10),CourseIDCHAR(6),ScoreDECIMAL(4,1),TermTINYINT,PRIMARYKEY(StudentID,CourseID),#定义主键约束

FOREIGNKEY(CourseID)REFERENCEScourse(CourseID)#定义外键约束);SHOWINDEXFROMscore1;9.2创建索引【例9-10】在“各省名称”表中，为“省名”列添加主键约束。ALTERTABLE各省名称ADDPRIMARYKEY(省名);SHOWINDEXFROM各省名称;9.2创建索引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;按照最左匹配规则，索引的查询如下：SELECT*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表达式列表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ОРР