第9章索引电子课件 MySQL数据库管理与应用

《第9章索引电子课件 MySQL数据库管理与应用》由会员分享，可在线阅读，更多相关《第9章索引电子课件 MySQL数据库管理与应用（31页珍藏版）》请在装配图网上搜索。

1、单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,#,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,第9章索引电子课件 MySQL数据库管理与应用,第,9,章 索引,主要内容,9.1,索引概述,9.2,创建索引,9.3,查看索引,9.4,删除索引,9.5,使用,EXPLAIN,进行索引分析,9.6,本章小结,9.1,索引概述,数据库应用系统中，数据查询及处理速度是衡量系统性能的重要标准，如何提高数据库的性能是数据库设计时需要重点考虑的问题，利用索引来提高数据查询速度是最常用的一种性能优化方法。,。,9.1,索引概述,9.1

2、.1,索引的,概念,索引是对数据表中一列或多列的值进行排序的一种结构。索引就像图书的目录一样用于快速查找需要的数据，提升数据库的查询性能,。,在,一个数据表中查找特定的记录也可以采取两种方法：一种是全表扫描，从第一行开始一一查看表中的每一行数据，与查询条件进行对比，返回满足条件的记录；另一种方法是通过对表中的数据创建索引，先在索引中找到符合查询条件的索引值，然后通过索引值对应的位置快速找到表中的记录。,当表中的数据很多的时候，全表扫描的效率很低，而如果合理地创建了索引，就可以利用索引避免全表扫描从而有效提高查询效率。,9.1,索引概述,9.1.1,索引的,概念,数据库中索引的作用主要体现在以下

3、几个方面：,索引,可以提高查询的速度，这是创建索引的主要原因；,通过,创建唯一索引，可以保证表中每一行数据的唯一性；,对,有参照关系的父表和子表进行连接查询时，索引可以加速表与表之间的连接；,使用,GROUP BY,和,ORDER BY,子句进行查询时，索引可以显著减少分组和排序的时间。,9.1,索引概述,9.1.2 MySQL,索引的,分类,1,普通索引和唯一,索引,普通索引是最基本的索引类型，创建普通索引时对于索引列的数据类型和值是否唯一没有限制,。,唯一索引要求索引列的值必须唯一，但允许有空值（除非列的定义中有,NOT NULL,）。主键是一种特殊的唯一索引，不允许有空值,。,2,单列索

4、引和组合,索引,可以在表的单个列上创建索引，称为单列索引,。,也,可以在表的多个列的组合上创建索引，称为组合索引、复合索引或多列索引。如果创建的是组合索引，只有查询条件中用到了索引中的第一个列才会使用该索引。,9.1,索引概述,9.1.2 MySQL,索引的,分类,3,前缀,索引,MySQL,中，对于字符串列（数据类型为,CHAR,、,VARCHAR,、,BINARY,、,VARBINARY,、,BLOB,、,TEXT,）可以创建只使用列值的前导部分的索引，使用,col_name,（,length,）语法指定索引前缀长度，前缀限制以字节为单位。,使用列前缀索引可以使索引文件小得多，从而节省大量

5、磁盘空间，还可能加快插入操作。,4,函数,索引,MySQL 8.0.13,及更高版本提供了函数索引，可以对表达式的值进行索引，又称为表达式索引。,9.1,索引概述,9.1.2 MySQL,索引的,分类,5,全文,索引,MySQL,中使用参数,FULLTEXT,设置索引为全文索引,。,全文索引基于,文本的列（数据类型为,CHAR,、,VARCHAR,或,TEXT,）上,创建，,以加快对这些列中数据的查询和,DML,操作,。,6,空间,索引,MySQL,中使用参数,SPATIAL,设置索引为空间索引,。,空间,索引只能建立在空间数据类型的列上，提高系统查询空间数据的效率。空间索引中的列必须声明为,

6、NOT NULL,。,9.1,索引概述,9.1.2 MySQL,索引的,分类,7,聚集索引和,辅助索引,聚集索引是指索引项的排序方式和表中数据记录排序方式一致的索引，每张表只能有一个聚集索引，聚集索引的叶子节点存储了所有的行数据,。,聚集索引对表中数据重新组织以按照一个或多个列的值排序。由于聚集索引的叶子节点存储了表中的所有数据，索引搜索直接指向包含行数据的页面，所以使用聚集索引查询数据通常要比使用非聚集索引快。,9.1,索引概述,9.1.2 MySQL,索引的,分类,7,聚集索引和,辅助索引,每个,InnoDB,表都必须有一个聚集索引：,如果,表上定义了主键，那么主键就作为聚集索引；,如果,

7、表上没有定义了主键，那么该表的第一个唯一非空索引被作为聚集索引；,如果,表上没有主键也没有合适的唯一索引，,InnoDB,会在包含行,ID,值的合成列上生成一个名为,GEN_CLUST_index,的隐藏聚集索引。行,ID,是一个,6,字节的字段，随着新行的插入而单调增加。因此，按行,ID,排序实际上是按插入顺序排列。,9.1,索引概述,9.1.2 MySQL,索引的,分类,7,聚集索引和,辅助索引,聚集索引以外的索引称为辅助索引（二级索引、次索引）,。,在,InnoDB,中，辅助索引中的每条数据都包含该行的聚集索引值（通常为主键值），以及该辅助索引中的列值,。,InnoDB,使用此聚集索引值

8、搜索聚集索引中的行。如果主键较长，则辅助索引会占用更多空间，因此主键较短是有利的。,9.1,索引概述,9.1.3,索引的设计,原则,为,查询条件中经常用到的并且重复值较少的列上创建索引以便提高查询效率，重复值较多的列无须创建索引,。,考虑,为经常作为排序依据、分组依据的列创建索引以便提高排序和分组的效率。,对于,取值有唯一性要求的列创建唯一索引，既保证数据的唯一性又能提高查询速度。,创建,组合索引的时候要注意索引中列的顺序,。,9.1,索引概述,9.1.3,索引的设计,原则,索引,并不是越多越好，索引太多不仅占用过多的磁盘空间，还会降低,INSERT,、,UPDATE,和,DELETE,的执行

9、速度。,数据,较少的表最好不要创建索引，因为数据量少，使用索引进行查询的时间相对全表扫描的时间优化效果很小，而索引维护和更新还会带来更多的开销。,避免,对经常更新的表创建多的索引。,9.2,创建,索引,9.2.1,使用,CREATE TABLE,语句创建,索引,语法,格式：,CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name,(column_definition,.,FULLTEXT|SPATIAL|UNIQUE INDEX|KEY index_name(col_name(length)|(expr)ASC|DESC,.),),9.2,创建,索引,9.2

10、.1,使用,CREATE TABLE,语句创建,索引,说明：,FULLTEXT,|SPATIAL|UNIQUE,：可选项，分别表示全文索引、空间索引和唯一索引；,INDEX,|KEY,：二选一，作用相同；,index_name,：要创建的索引的名称，如果省略，,MySQL,默认用列名,col_name,作为索引名称；,col_name,(length),：索引包含的列的名称和长度，,length,为可选参数且只有字符串类型的列才可以指定长度；,(,expr),：函数索引对应的表达式；,ASC,|DESC,：索引值的排序方式，,ASC,表示升序，默认值，,DESC,表示降序。,9.2,创建,索引

11、,9.2.1,使用,CREATE TABLE,语句创建,索引,【,例,9.1】,在,jwgl,数据库中，创建,dept,表时在,dname,列创建唯一索引。,在,MySQL,命令行客户端输入命令：,USE jwgl,CREATE TABLE dept,(,dno CHAR(2)PRIMARY KEY,dname VARCHAR(20),dloc VARCHAR(20),dphone CHAR(8),UNIQUE INDEX ind_dname(dname),);,9.2,创建,索引,9.2.1,使用,CREATE TABLE,语句创建,索引,定义主键约束或唯一性约束后，,MySQL,会自动创建

12、唯一索引，因此本例也可以通过创建唯一约束的方法创建唯一索引，语句为：,CREATE TABLE dept,(,dno CHAR(2)PRIMARY KEY,dname VARCHAR(20)UNIQUE,dloc VARCHAR(20),dphone CHAR(8),);,9.2,创建,索引,9.2.2,使用,CREATE INDEX,语句创建,索引,语法,格式：,CREATE UNIQUE|FULLTEXT|SPATIAL INDEX index_name,ON tbl_name(col_name(length)|(expr)ASC|DESC,.),【,例,9.2】,在,jwgl,数据库中的

13、,student,表的,sname,列上创建普通索引，降序排列。,在,MySQL,命令行客户端输入命令：,CREATE INDEX ind_sname ON student(sname DESC);,9.2,创建,索引,9.2.2,使用,CREATE INDEX,语句创建,索引,【,例,9.3】,在,jwgl,数据库中的,major,表的,mname,列上创建唯一索引。,在,MySQL,命令行客户端输入命令：,CREATE UNIQUE INDEX ind_mname ON major(mname);,9.2,创建,索引,9.2.2,使用,CREATE INDEX,语句创建,索引,【,例,9.

14、4】,在,jwgl,数据库中的,student,表的,sname,和,ssex,列创建组合索引。,在,MySQL,命令行客户端输入命令：,CREATE INDEX ind_sname_ssex ON student(sname,ssex);,9.2,创建,索引,9.2.3,使用,ALTER TABLE,语句创建,索引,语法,格式：,ALTER TABLE tbl_name,ADD FULLTEXT|SPATIAL|UNIQUE INDEX|KEY index_name(col_name(length)|(expr)ASC|DESC,.),【,例,9.5】,在,jwgl,数据库中的,course

15、,表的,cname,列上创建普通索引。,在,MySQL,命令行客户端输入命令：,ALTER TABLE,course ADD,INDEX ind_cname(cname);,9.3,查看索引,可以使用,SHOW INDEX,语句查看表的索引信息，语法格式如下：,SHOW INDEX|INDEXES|KEYS FROM|IN tbl_name FROM|IN db_name,或者：,SHOW INDEX|INDEXES|KEYS FROM|IN db_name.tbl_name,SHOW INDEX,语句以二维表的形式返回指定表上的索引信息，包括表名、索引名、是否唯一索引、索引中的列名、列序号、

16、排序方式、索引前缀等。因为显示信息较多，可以使用,G,。,9.3,查看索引,【,例,9.6】,查看,jwgl,数据库中,student,表上的索引。,在,MySQL,命令行客户端输入命令：,SHOW INDEX FROM jwgl.course G,9.4,删除,索引,9.4.1,使用,DROP INDEX,语句删除,索引,语法格式如下：,DROP INDEX index_name ON tbl_name,index_name,为要删除的索引的名称，,tbl_name,为索引所在的表的名称。,【,例,9.7】,删除,course,表的索引,ind_cname,。,在,MySQL,命令行客户端输入命令：,DROP INDEX ind_cname ON course;,9.4,删除,索引,9.4.2,使用,ALTER TABLE,语句删除,索引,语法格式如下：,ALTER TABLE tbl_name DROP INDEX index_name,index_name,为要删除的索引的名称，,tbl_name,为索引所在的表的名称。,【,例,9.8】,删除,student,表的索引,ind_