资源简介

(共30张PPT)
第9章 索引
主要内容
9.1 索引概述
9.2 创建索引
9.3 查看索引
9.4 删除索引
9.5 使用EXPLAIN进行索引分析
9.6 本章小结
9.1 索引概述
数据库应用系统中，数据查询及处理速度是衡量系统性能的重要标准，如何提高数据库的性能是数据库设计时需要重点考虑的问题，利用索引来提高数据查询速度是最常用的一种性能优化方法。。
9.1 索引概述
9.1.1 索引的概念
索引是对数据表中一列或多列的值进行排序的一种结构。索引就像图书的目录一样用于快速查找需要的数据，提升数据库的查询性能。
在一个数据表中查找特定的记录也可以采取两种方法：一种是全表扫描，从第一行开始一一查看表中的每一行数据，与查询条件进行对比，返回满足条件的记录；另一种方法是通过对表中的数据创建索引，先在索引中找到符合查询条件的索引值，然后通过索引值对应的位置快速找到表中的记录。
当表中的数据很多的时候，全表扫描的效率很低，而如果合理地创建了索引，就可以利用索引避免全表扫描从而有效提高查询效率。
9.1 索引概述
9.1.1 索引的概念
数据库中索引的作用主要体现在以下几个方面：
索引可以提高查询的速度，这是创建索引的主要原因；
通过创建唯一索引，可以保证表中每一行数据的唯一性；
对有参照关系的父表和子表进行连接查询时，索引可以加速表与表之间的连接；
使用GROUP BY和ORDER BY子句进行查询时，索引可以显著减少分组和排序的时间。
9.1 索引概述
9.1.2 MySQL索引的分类
1．普通索引和唯一索引
普通索引是最基本的索引类型，创建普通索引时对于索引列的数据类型和值是否唯一没有限制。
唯一索引要求索引列的值必须唯一，但允许有空值（除非列的定义中有NOT NULL）。主键是一种特殊的唯一索引，不允许有空值。
2．单列索引和组合索引
可以在表的单个列上创建索引，称为单列索引。
也可以在表的多个列的组合上创建索引，称为组合索引、复合索引或多列索引。如果创建的是组合索引，只有查询条件中用到了索引中的第一个列才会使用该索引。
9.1 索引概述
9.1.2 MySQL索引的分类
3．前缀索引
MySQL中，对于字符串列（数据类型为CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT）可以创建只使用列值的前导部分的索引，使用col_name（length）语法指定索引前缀长度，前缀限制以字节为单位。
使用列前缀索引可以使索引文件小得多，从而节省大量磁盘空间，还可能加快插入操作。
4．函数索引
MySQL 8.0.13及更高版本提供了函数索引，可以对表达式的值进行索引，又称为表达式索引。
9.1 索引概述
9.1.2 MySQL索引的分类
5．全文索引
MySQL中使用参数FULLTEXT设置索引为全文索引。
全文索引基于文本的列（数据类型为CHAR、VARCHAR或TEXT）上创建，以加快对这些列中数据的查询和DML操作。
6．空间索引
MySQL中使用参数SPATIAL设置索引为空间索引。
空间索引只能建立在空间数据类型的列上，提高系统查询空间数据的效率。空间索引中的列必须声明为NOT NULL。
9.1 索引概述
9.1.2 MySQL索引的分类
7．聚集索引和辅助索引
聚集索引是指索引项的排序方式和表中数据记录排序方式一致的索引，每张表只能有一个聚集索引，聚集索引的叶子节点存储了所有的行数据。
聚集索引对表中数据重新组织以按照一个或多个列的值排序。由于聚集索引的叶子节点存储了表中的所有数据，索引搜索直接指向包含行数据的页面，所以使用聚集索引查询数据通常要比使用非聚集索引快。
9.1 索引概述
9.1.2 MySQL索引的分类
7．聚集索引和辅助索引
每个InnoDB表都必须有一个聚集索引：
如果表上定义了主键，那么主键就作为聚集索引；
如果表上没有定义了主键，那么该表的第一个唯一非空索引被作为聚集索引；
如果表上没有主键也没有合适的唯一索引，InnoDB会在包含行ID值的合成列上生成一个名为GEN_CLUST_index的隐藏聚集索引。行ID是一个6字节的字段，随着新行的插入而单调增加。因此，按行ID排序实际上是按插入顺序排列。
9.1 索引概述
9.1.2 MySQL索引的分类
7．聚集索引和辅助索引
聚集索引以外的索引称为辅助索引（二级索引、次索引）。
在InnoDB中，辅助索引中的每条数据都包含该行的聚集索引值（通常为主键值），以及该辅助索引中的列值。
InnoDB使用此聚集索引值搜索聚集索引中的行。如果主键较长，则辅助索引会占用更多空间，因此主键较短是有利的。
9.1 索引概述
9.1.3 索引的设计原则
为查询条件中经常用到的并且重复值较少的列上创建索引以便提高查询效率，重复值较多的列无须创建索引。
考虑为经常作为排序依据、分组依据的列创建索引以便提高排序和分组的效率。
对于取值有唯一性要求的列创建唯一索引，既保证数据的唯一性又能提高查询速度。
创建组合索引的时候要注意索引中列的顺序。
9.1 索引概述
9.1.3 索引的设计原则
索引并不是越多越好，索引太多不仅占用过多的磁盘空间，还会降低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 | DESC],...)
)
9.2 创建索引
9.2.1 使用CREATE TABLE语句创建索引
说明：
FULLTEXT | SPATIAL|UNIQUE：可选项，分别表示全文索引、空间索引和唯一索引；
INDEX | KEY：二选一，作用相同；
index_name：要创建的索引的名称，如果省略，MySQL默认用列名col_name作为索引名称；
col_name [(length)]：索引包含的列的名称和长度，length为可选参数且只有字符串类型的列才可以指定长度；
(expr)：函数索引对应的表达式；
ASC | DESC：索引值的排序方式，ASC表示升序，默认值，DESC表示降序。
9.2 创建索引
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会自动创建唯一索引，因此本例也可以通过创建唯一约束的方法创建唯一索引，语句为：
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数据库中的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.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表的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语句以二维表的形式返回指定表上的索引信息，包括表名、索引名、是否唯一索引、索引中的列名、列序号、排序方式、索引前缀等。因为显示信息较多，可以使用\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_sname。
在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语句的语法格式为：
EXPLAIN FORMAT=TRADITIONAL| JSON | TREE
SELECT statement | TABLE statement | DELETE statement | INSERT statement
| REPLACE statement | UPDATE statement
说明：
FORMAT=TRADITIONAL| JSON | TREE：指定执行计划的输出格式，TRADITIONAL以表格形式返回结果，JSON以JSON格式返回结果，TREE以树形结构返回结果。
EXPLAIN后跟需要查看计划的语句，可以是TABLE、SELECT、 DELETE、INSERT、 REPLACE和UPDATE。
9.5 使用EXPLAIN进行索引分析
【例9.9】使用EXPLAIN查看索引的使用情况。
在MySQL命令行客户端输入命令：
EXPLAIN SELECT * FROM student WHERE ssex='男' \G
9.5 使用EXPLAIN进行索引分析
从结果可以看到，MySQL使用全表扫描的方式执行该查询语句，并没有使用索引。如果我们以学生姓名做条件进行查询，查看其执行计划，MySQL会使用索引ind_sname_ssex查找数据。
9.6 本章小结
索引是数据库中一种重要的数据库对象，使用索引可以提高查询的速度，提升数据库的性能。本章主要介绍了索引的相关知识，包括索引的概念和作用、视图的创建、查看和删除以及使用EXPLAIN进行查询分析。
通过本章的学习，读者应该掌握索引的概念和相关操作，能够在实际应用中合理设计和使用索引。

展开更多......

收起↑