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

《第9章索引电子课件 MySQL数据库管理与应用.pptx》由会员分享，可在线阅读，更多相关《第9章索引电子课件 MySQL数据库管理与应用.pptx（31页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第9章索引电子课件 MySQL数据库管理与应用第9章 索引主要内容9.1 索引概述9.2 创建索引9.3 查看索引9.4 删除索引9.5 使用EXPLAIN进行索引分析9.6 本章小结9.1 索引概述数据库应用系统中，数据查询及处理速度是衡量系统性能的重要标准，如何提高数据库的性能是数据库设计时需要重点考虑的问题，利用索引来提高数据查询速度是最常用的一种性能优化方法。9.1 索引概述9.1.1 索引的概念索引是对数据表中一列或多列的值进行排序的一种结构。索引就像图书的目录一样用于快速查找需要的数据，提升数据库的查询性能。在一个数据表中查找特定的记录也可以采取两种方法：一种是全表扫描，从第一行开

2、始一一查看表中的每一行数据，与查询条件进行对比，返回满足条件的记录；另一种方法是通过对表中的数据创建索引，先在索引中找到符合查询条件的索引值，然后通过索引值对应的位置快速找到表中的记录。当表中的数据很多的时候，全表扫描的效率很低，而如果合理地创建了索引，就可以利用索引避免全表扫描从而有效提高查询效率。9.1 索引概述9.1.1 索引的概念数据库中索引的作用主要体现在以下几个方面：l索引可以提高查询的速度，这是创建索引的主要原因；l通过创建唯一索引，可以保证表中每一行数据的唯一性；l对有参照关系的父表和子表进行连接查询时，索引可以加速表与表之间的连接；l使用GROUP BY和ORDER BY子句

3、进行查询时，索引可以显著减少分组和排序的时间。9.1 索引概述9.1.2 MySQL索引的分类1普通索引和唯一索引l普通索引是最基本的索引类型，创建普通索引时对于索引列的数据类型和值是否唯一没有限制。l唯一索引要求索引列的值必须唯一，但允许有空值（除非列的定义中有NOT NULL）。主键是一种特殊的唯一索引，不允许有空值。2单列索引和组合索引l可以在表的单个列上创建索引，称为单列索引。l也可以在表的多个列的组合上创建索引，称为组合索引、复合索引或多列索引。如果创建的是组合索引，只有查询条件中用到了索引中的第一个列才会使用该索引。9.1 索引概述9.1.2 MySQL索引的分类3前缀索引lMyS

4、QL中，对于字符串列（数据类型为CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT）可以创建只使用列值的前导部分的索引，使用col_name（length）语法指定索引前缀长度，前缀限制以字节为单位。l使用列前缀索引可以使索引文件小得多，从而节省大量磁盘空间，还可能加快插入操作。4函数索引lMySQL 8.0.13及更高版本提供了函数索引，可以对表达式的值进行索引，又称为表达式索引。9.1 索引概述9.1.2 MySQL索引的分类5全文索引lMySQL中使用参数FULLTEXT设置索引为全文索引。l全文索引基于文本的列（数据类型为CHAR、VARCHAR或TEXT）

5、上创建，以加快对这些列中数据的查询和DML操作。6空间索引lMySQL中使用参数SPATIAL设置索引为空间索引。l空间索引只能建立在空间数据类型的列上，提高系统查询空间数据的效率。空间索引中的列必须声明为NOT NULL。9.1 索引概述9.1.2 MySQL索引的分类7聚集索引和辅助索引l聚集索引是指索引项的排序方式和表中数据记录排序方式一致的索引，每张表只能有一个聚集索引，聚集索引的叶子节点存储了所有的行数据。l聚集索引对表中数据重新组织以按照一个或多个列的值排序。由于聚集索引的叶子节点存储了表中的所有数据，索引搜索直接指向包含行数据的页面，所以使用聚集索引查询数据通常要比使用非聚集索引

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

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

8、多的磁盘空间，还会降低INSERT、UPDATE和DELETE的执行速度。数据较少的表最好不要创建索引，因为数据量少，使用索引进行查询的时间相对全表扫描的时间优化效果很小，而索引维护和更新还会带来更多的开销。避免对经常更新的表创建多的索引。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|DES

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

10、TE TABLE语句创建索引【例9.1】在jwgl数据库中，创建dept表时在dname列创建唯一索引。在MySQL命令行客户端输入命令：USE jwglCREATE 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会自动创建唯一索引，因此本例也可以通过创建唯一约束的方法创建唯一索引，语句为：CREATE TAB

11、LE 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_nameON tbl_name(col_name(length)|(expr)ASC|DESC,.)【例9.2】在jwgl数据库中的student表的sname列上创建普通索引，降序排列。在MySQL命令行客户端输入命令：CREATE INDEX ind_sna

12、me 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.4】在jwgl数据库中的student表的sname和ssex列创建组合索引。在MySQL命令行客户端输入命令：CREATE INDEX ind_sname_ssex ON student(sname,s

13、sex);9.2 创建索引9.2.3 使用ALTER TABLE语句创建索引语法格式：ALTER TABLE tbl_nameADD FULLTEXT|SPATIAL|UNIQUE INDEX|KEY index_name(col_name(length)|(expr)ASC|DESC,.)【例9.5】在jwgl数据库中的course表的cname列上创建普通索引。在MySQL命令行客户端输入命令：ALTER TABLE course ADD INDEX ind_cname(cname);9.3 查看索引可以使用SHOW INDEX语句查看表的索引信息，语法格式如下：SHOW INDEX|IN

14、DEXES|KEYS FROM|IN tbl_name FROM|IN db_name或者：SHOW INDEX|INDEXES|KEYS FROM|IN db_name.tbl_name SHOW INDEX语句以二维表的形式返回指定表上的索引信息，包括表名、索引名、是否唯一索引、索引中的列名、列序号、排序方式、索引前缀等。因为显示信息较多，可以使用G。9.3 查看索引【例9.6】查看jwgl数据库中student表上的索引。在MySQL命令行客户端输入命令：SHOW INDEX FROM jwgl.course G9.4 删除索引9.4.1 使用DROP INDEX语句删除索引语法格式如下

15、：DROP INDEX index_name ON tbl_namelindex_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_namelindex_name为要删除的索引的名称，tbl_name为索引所在的表的名称。【例9.8】删除student表的索引ind_sna

16、me。在MySQL命令行客户端输入命令：ALTER TABLE student DROP INDEX ind_sname;9.5 使用EXPLAIN进行索引分析EXPLAIN是 MySQL提 供 的 内 置 命 令，与 TABLE、SELECT、DELETE、INSERT、REPLACE和UPDATE语句一起使用，获取来自MySQL优化器的有关语句执行计划的信息。实际应用中，我们可以使用EXPLAIN获取语句的执行计划，帮助我们分析需要在表的哪些列上创建索引，以便使用索引查找行从而使语句执行得更快，或者查看哪些索引影响了数据库的性能需要删除。9.5 使用EXPLAIN进行索引分析EXPLAIN

17、语句的语法格式为：EXPLAIN FORMAT=TRADITIONAL|JSON|TREESELECT statement|TABLE statement|DELETE statement|INSERT statement|REPLACE statement|UPDATE statement说明：lFORMAT=TRADITIONAL|JSON|TREE：指定执行计划的输出格式，TRADITIONAL以表格形式返回结果，JSON以JSON格式返回结果，TREE以树形结构返回结果。lEXPLAIN后 跟 需 要 查 看 计 划 的 语 句，可 以 是 TABLE、SELECT、DELETE、IN

18、SERT、REPLACE和UPDATE。9.5 使用EXPLAIN进行索引分析【例9.9】使用EXPLAIN查看索引的使用情况。在MySQL命令行客户端输入命令：EXPLAIN SELECT*FROM student WHERE ssex=男 G9.5 使用EXPLAIN进行索引分析从结果可以看到，MySQL使用全表扫描的方式执行该查询语句，并没有使用索引。如果我们以学生姓名做条件进行 查 询，查 看 其 执 行 计 划，MySQL会 使 用 索 引ind_sname_ssex查找数据。9.6 本章小结索引是数据库中一种重要的数据库对象，使用索引可以提高查询的速度，提升数据库的性能。本章主要介绍了索引的相关知识，包括索引的概念和作用、视图的创建、查看和删除以及使用EXPLAIN进行查询分析。通过本章的学习，读者应该掌握索引的概念和相关操作，能够在实际应用中合理设计和使用索引。