数据库原理及应用（MySQL项目式版）- 课件 项目7 索引

项目7索引任务7.1认识索引索引是一种单独的、物理的、存储在磁盘上的数据库结构。数据库中的索引类似于一本书中的目录，读者可以通过目录快速找到需要的章节页码，翻到对应的页码即可看到具体的内容。1．索引的概念

索引是按照数据表中某一个字段或多个字段进行排序，并用指针指向记录的位置。索引存放在索引页面上，当进行数据检索时，系统从索引页面中找到所需数据的指针，再直接利用指针读取数据。因为索引是排好序的，不必逐一查找，可以采用针对排序数据的查找算法快速检索到数据。在没有索引的表中查询某个特定记录时需要从第一条记录开始，逐条比较记录值是否匹配，当表中的记录比较多时，得到结果耗时较长，影响查找的效率。例如，按照读者编号reader_id查找借还书表borrow中的相关记录时，需要从第一条记录开始扫描全表，找到对应的记录，当表中的数据非常多时，查询比较耗时。在读者编号reader_id字段创建索引后，系统会利用该索引找到对应的记录，避免了对借还书表borrow的全表扫描，加快了查询速度。索引具有如下的优点（1）可能会提高查询速度。（2）可以实现数据的唯一性。（3）提高ORDERBY和GROUPBY的执行速度。使用索引可以提高性能，但也要付出一定的代价，主要体现为索引需要占用磁盘的存储空间；索引会降低更新表中数据的速度。当更新表中数据时，系统会自动更新索引页面的数据，可能需要重新组织索引，这会浪费时间。索引设计不当可能会影响系统的性能，合理设置索引对于数据库系统非常重要，需要考虑到以下几点规则。（1）为频繁查询的字段创建索引。（2）数据量较小的表最好不要创建索引。（3）尽量在不同值较多的字段上创建索引，如，读者表reader的姓名name字段；不要在不同值很少的字段上创建索引，如，性别gender字段。（4）一个表中的索引不是越多越好，需要限制索引的数量。（5）尽量不为频繁进行插入、删除、修改操作的表创建索引。2.索引的分类

（1）普通索引这是最基本的索引类型，它没有唯一性的限制，可以有重复值和空值。创建普通索引的关键字是INDEX。（2）唯一索引唯一索引的索引字段的值必须是唯一的，不允许重复，但允许有空值。创建唯一索引的关键字是UNIQUE。（3）主键索引主键索引是一种特殊的唯一索引，特殊之处在于每张表只能有一个主键索引，且不允许有空值。创建主键索引的关键字是PRIMARYKEY，即主键。一般在创建表时指定主键，也可以通过修改表的方式添加主键。（4）全文索引全文索引只能在CHAR、VARCHAR或者TEXT类型的字段上创建，并且只能在存储引擎为MyISAM和InnoDB的表中创建。创建全文索引的关键字是FULLTEXT。当查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。创建在一个字段上的索引称为单索引。创建在多个字段上的索引称为组合索引、复合索引或多列索引。如果唯一索引是组合索引，则多个字段的组合必须是唯一的。在编写SELECT查询语句时，有索引的表与没有索引的表没有任何区别，索引只是提供了一种快速访问指定记录的方法。7.2创建索引

索引是在表中创建的，不可以针对视图创建索引。在MySQL中，可以在已有的表中创建索引，也可以在创建表的同时创建索引。索引创建成功后，将由数据库自动管理和维护，当向表中插入、删除和修改记录时，数据库会自动更新相应的索引。创建索引的目的是加快查询的速度，应该为需要查询的字段创建索引。例如按照馆藏编号stock_id查询，则先在馆藏编号stock_id字段创建索引。（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。【例7-1】在借还书表borrow的读者编号reader_id字段上，创建一个普通索引I_reader_id。mysql>CREATEINDEXI_reader_idONborrow(reader_id);【例7-2】在图书表book的书名title字段值的前5个字符上，创建一个唯一索引I_title，按照title字段值的前5个字符的降序排列。mysql>CREATEUNIQUEINDEXI_titleONbook(title(5)DESC);【例7-3】在借还书表borrow的读者编号reader_id字段和馆藏编号stock_id字段上，创建一个组合索引I_rsid，先按照读者编号reader_id升序排列，如果读者编号reader_id值相同，则按照馆藏编号stock_id降序排列。mysql>CREATEINDEXI_rsidONborrow(reader_id,stock_idDESC);2．使用ALTERTABLE语句创建索引对于已有的表，也可以使用ALTERTABLE语句创建索引，其语法格式如下。ALTERTABLE表名ADDINDEX[索引名](字段名称1[,字段名称2…])

ADDPRIMARYKEY(字段名称1[,字段名称2…])

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

ADDFULLTEXT[索引名](字段名称1[,字段名称2…]);

说明

（1）索引名：如果省略则采用默认索引名。默认索引名的命名规则是主键的索引名为PRIMARY，其他索引使用索引的第一个字段名称为索引名。如果多个索引的第一个字段名称相同，则在字段名称后加递增的数字进行区别。（2）一次添加多个索引时，各个索引之间用英文逗号分隔。可以使用ALTERTABLE语句一次添加多个索引。【例7-5】在读者表reader的姓名name字段上添加一个唯一索引，并按照降序排列，在所属院系dept字段上添加一个普通索引。mysql>ALTERTABLEreader

->ADDUNIQUE(nameDESC),ADDINDEX(dept);该语句中没有给这两个索引命名，则默认索引名为字段名称，分别是name和dept。【例7-4】在图书表book的定价price字段上添加一个普通索引，并按照降序排列。mysql>ALTERTABLEbook->ADDINDEX(priceDESC);3．使用CREATETABLE语句创建索引使用CREATETABLE语句可以在创建表的同时创建索引，其中的索引项语法格式如下。PRIMARYKEY(字段名称1[,字段名称2…])

{INDEX

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

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

[FULLTEXT][INDEX][索引名](字段名称1[,字段名称2…]);【例7-6】创建读者表reader1（包含读者编号reader_id、姓名name和所属院系dept字段），设置reader_id字段为主键，name为唯一索引，dept为普通索引。mysql>CREATETABLEreader1->(->reader_idCHAR(6)PRIMARYKEY,->nameVARCHAR(50),->deptVARCHAR(50),->UNIQUE(name),->INDEX(dept)->);7.3查看索引使用SHOWINDEX语句可以查看表中的索引，其语法格式如下。SHOW{INDEX

INDEXES

KEYS}{FROM

IN}表名[{FROM

IN}数据库名];SHOWINDEX语句以二维表的形式显示指定表中的所有索引信息，由于显示的信息较多且不易查看，使用\G参数可以让每一行信息垂直显示，便于查看。【例7-7】查看读者表reader1中创建的索引。

mysql>SHOWINDEXFROMreader1\G;任务7.4删除索引使用DROPINDEX语句或者使用ALTERTABLE语句均可删除索引。1．使用DROPINDEX语句删除索引DROPINDEX语句的语法格式如下。DROPINDEX索引名ON表名;【例7-8】删除读者表reader1中在所属院系dept字段上创建的索引。mysql>DROPINDEXdeptONreader1;因为在dept字段上创建索引时未指定索引名，系统自动以字段名称为索引名，所以索引名为dept。2．使用ALTERTABLE语句删除索引ALTERTABLE子句不仅能创建索引，也可以删除索引，其语法格式如下。ALTERTABLE表名DROPINDEX索引名

DROPPRIMARYKEY;使用DROPINDEX语句可以删除各种类型的索引，使用DROPPRIMARYKEY子句时不需要指定索引名，因为每一张表中只有一个主键。【例7-9】在读者表reader1中删除读者编号reader_id字段和姓名name字段上的索引。mysql>ALTERTABLEreader1->DROPPRIMARYKEY,DROPINDEXname;【项目案例】图书馆借还书管理数据库的索引（1）使用CREATEINDEX语句在借还书表borrow的读者编号reader_id字段、馆藏编号stock_id字段和借出时间borrowtime字段上，创建一个唯一组合索引I_rsidborrow。mysql>CREATEUNIQUEINDEXI_rsidborrowONborrow(reader_id,stock_id,borrowtime);（2）使用ALTERTABLE语句在图书表book的作者