MySQL数据库教程课件

第7章索引索引是对数据库某个表中的一个字段或多个字段的值进行排序，存放在单独的索引页面上，检索数据时系统先搜索索引页面。索引可以加快检索的速度，提高数据库系统的性能。本章主要介绍索引的概念、创建索引、删除索引等方面的内容。【学习目标】理解索引的概念。理解索引对查询的影响以及哪些字段适合建立索引。掌握常用的索引类型和创建索引的方法。7.1索引概述索引是一个单独的、物理的、存储在磁盘上的数据库结构。7.1.1索引的概念数据库中的索引类似于书中的目录，读者可以通过目录快速找到需要的章节页码，翻到对应的页码即可看到具体的内容。在没有索引的表中查询某个特定记录时需要从第一条记录开始，逐条比较记录值是否匹配，当表中的记录比较多时，得到结果耗时较长，影响查找的效率。在编写SELECT查询语句时，有索引的表与没有索引的表没有任何区别，索引只是提供了一种快速访问指定记录的方法。例如，已经对课程表course的课程编号cno字段创建了索引后，当查询课程编号为“10700140”的记录时，首先在索引中找到“10700140”，然后通过指针直接找到课程表course中对应的记录行（10700140，高等数学，64），不用从第一条记录开始进行全表扫描。索引是按照数据表中某一个字段或多个字段进行排序，并用指针指向记录的位置索引存放在索引页面上，当进行数据检索时，系统从索引页面中找到所需数据的指针，再直接利用指针读取数据因为索引中是排好序的，不必逐一查找，可以采用针对排序数据的查找算法快速检索到数据索引的优点（1）可以提高查询速度。（2）可以确保数据的唯一性。（3）提高ORDERBY和GROUPBY的执行速度。索引设计规则（1）为频繁查询的字段创建索引。（2）数据量较小的表最好不要创建索引。（3）尽量在不同值较多的字段上创建索引，如，姓名字段；不要在不同值很少的字段上创建索引，如，性别字段。（4）一个表中的索引不是越多越好，需要限制索引的数量。（5）对于频繁进行插入、删除、修改操作的表，创建的索引越多，则更新表所耗费的时间就越长。7.1.2索引的分类（1）普通索引这是最基本的索引类型，它没有唯一性的限制，可以有重复值和空值。创建普通索引的关键字是INDEX。（2）唯一索引这种索引与普通索引基本相同，区别在于唯一索引的索引字段的值必须是唯一的，不允许重复，但允许有空值。创建唯一索引的关键字是UNIQUE。（3）主键索引主键索引是一种特殊的唯一索引，不同之处在于每张表只能有一个主键索引，且不允许有空值。创建主键索引的关键字是PRIMARYKEY，即主键。一般在创建表时指定主键，也可以通过修改表的方式添加主键。（4）全文索引全文索引只能在CHAR、VARCHAR或者TEXT类型的字段上创建，并且只能在存储引擎为MyISAM和InnoDB的表中创建。创建全文索引的关键字是FULLTEXT。当查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。创建在一个字段上的索引称为单索引；创建在多个字段上的索引称为组合索引、复合索引或多列索引。如果唯一索引是组合索引，则多个字段的组合必须是唯一的。7.2创建索引1．使用CREATEINDEX语句创建索引使用CREATEINDEX语句可以在一个已有的表上创建索引语法格式：CREATE[UNIQUE

FULLTEXT]INDEX索引名ON表名(字段名称1[(长度1)][ASC

DESC][,字段名称2[(长度2)][ASC

DESC]

]);说明：（1）索引名：指定创建的索引名称，在一个表中可以创建多个索引，但是每个索引名必须是唯一的。（2）UNIQUE

FULLTEXT：可选项，UNIQUE表示创建的是唯一索引；FULLTEXT表示创建的是全文索引。（3）长度：表示使用字段前多少个字符创建索引，这样可以减少索引文件的大小。只能对字符串类型的字段指定长度。（4）ASC

DESC：指定索引按照升序ASC或者降序DESC排序。默认值为ASC。【例7-1】在学生表student的出生日期birthdate字段上，创建一个普通索引I_birthdate。mysql>CREATEINDEXI_birthdateONstudent(birthdate);【例7-2】在课程表course的课程名称cname字段上，创建一个唯一索引I_cname，按照cname字段值的前5个字符的降序排列。mysql>CREATEUNIQUEINDEXI_cnameONcourse(cname(5)DESC);【例7-3】在学生表student的院系代码deptno字段和入学总分enterscore字段上，创建一个组合索引I_deptscore，按照院系代码deptno升序，如果院系代码deptno值相同，则按照入学总分enterscore降序排列。mysql>CREATEINDEXI_deptscoreONstudent(deptno,enterscoreDESC);2.使用ALTERTABLE语句添加索引对于已有的表，也可以使用ALTERINDEX语句向表中添加索引语法格式：ALTERTABLE表名ADDINDEX[索引名](字段1[,字段2

])

ADDPRIMARYKEY(字段1[,字段2

])

ADDUNIQUE[索引名](字段1[,字段2

])

ADDFULLTEXT[索引名](字段1[,字段2

]);说明：（1）索引名：如果省略则采用默认索引名。默认索引名的命名规则是：主键的索引名为PRIMARY，其他索引使用索引的第一个字段名称为索引名。如果多个索引都是相同字段，则在字段后加顺序数字进行区别。（2）一次添加多个索引时，各个索引之间用逗号分隔。【例7-4】在学生表student的姓名sname字段上，添加一个普通索引I_sname，并按照降序排列。mysql>ALTERTABLEstudent->ADDINDEXI_sname(snameDESC);【例7-5】在课程表course的课程名称cname字段上添加一个唯一索引，在学时hours字段上添加一个普通索引并按照降序排列。mysql>ALTERTABLEcourse->ADDUNIQUE(cname),->ADDINDEX(hoursDESC);默认索引名为字段名称，相应的索引名分别是cname和hours。3．使用CREATETABLE语句创建索引使用CREATETABLE语句可以在创建表的同时创建索引，索引项语法格式如下。视频7-4PRIMARYKEY(字段1[,字段2

])

{INDEX

KEY}[索引名](字段1[,字段2

])

UNIQUE[INDEX][索引名](字段1[,字段2

])

[FULLTEXT][INDEX][索引名](字段1[,字段2

]);【例7-6】创建课程表course1，设置课程编号cno字段为主键，课程名称cname为唯一索引，学时hours为普通索引。mysql>CREATETABLEcourse1->(->cnoCHAR(8)NOTNULL,->cnameVARCHAR(50),->hoursTINYINT,->PRIMARYKEY(cno),->UNIQUE(cname),->INDEX(hours)->);7.3查看索引使用SHOWINDEX语句查看表中的索引语法格式：SHOW{INDEX

INDEXES

KEYS}{FROM

IN}表名[{FROM

IN}数据库名];SHOWINDEX语句以二维表的形式显示指定表中的所有索引信息由于显示的信息较多不易查看，使用\G参数可以将每一行垂直显示，查看效果更好。7.4删除索引1.使用DROPINDEX语句删除索引DROPINDEX语句的语法格式如下。DROPINDEX索引名ON表名;【例7-8】删除课程表course1中在hours字段上创建的索引。mysql>DROPINDEXhoursONcourse1;2.使用ALTERTABLE语句删除索引ALTERTABLE语句不仅能创建索引，也可以删除索引，其语法格式如下。ALTERTABLE表名DROPINDEX索引名DROPPRIMARYKEY;【例7-9】在课程表course中删除课程编号cno字段和课程名称cname字段的索引。mysql>ALTERTABLEcourse1->DROPPRIMARYKEY,->DROPINDEXcname;使用DROPPRIMARYKEY子句时不需要索引名，因为每一张表中只有一个主键。7.5课堂案例：学生成绩管理的数据索引（1）使用CREATEINDEX语句在教师表teacher的姓名tname字段上，创建一个普通索引I_tname。mysql>CREATEINDEXI_tnameONteacher(tname);（2）使用CREATEINDEX语句在教师表teacher的院系代码deptno字段和职称title字段上，创建一个组合索引。mysql>CREATE