MySQL数据库原理及应用（微课版|第4版）
数据库原理及应用
----项目6 优化查询学生信息管理数据库
MySQL数据库原理及应用（微课版|第4版）
情景导入
上节课李老师给同学们布置了一道思考题：向学生基本信息表student_new中插
入100万条记录。
王宁按照题目要求和老师提供的SQL脚本，花费近1个小时的时间，将100万
条记录成功插入到了student_new中。在完成数据的插入后，他尝试使用
select语句查询学号sno为1000000的记录，发现用时秒（不同机器、
不同配置，时间稍有偏差）。这个响应时间太长了，让人无法忍受，可是王
宁不知道怎样才能优化查询速度。
李老师告诉王宁，为了提高学生信息管理系统中数据的安全性、
完整性和查询速度，在应用系统的实际开发过程中，开发人员
一般会利用索引、视图等来提高系统响应速度和其他性能参数。
MySQL数据库原理及应用（微课版|第4版）
使用索引优化查询性能
使用视图优化查询性能
主要内容
MySQL数据库原理及应用（微课版|第4版）
项目1 理解数据库
职业能力目标（含课程思政）
了解索引、视图的作用
掌握索引、视图的创建及使用方法
掌握索引、视图的修改及删除方法
MySQL数据库原理及应用（微课版|第4版）
任务6-1 使用索引优化查询性能
【任务提出】
为了提高查询速度，王宁需要在student_new表
的sno字段上创建唯一索引id_sno，并通过查询sno
为1000000的记录，验证查询速度是否明显提升。
MySQL数据库原理及应用（微课版|第4版）
• 理解索引
（一）索引概述
索引是一个单独的、物理的数据库结构，是某个表中一列
或者若干列的集合以及相应的标识这些值所在的数据页的
逻辑指针清单。
索引依赖于表建立，提供了数据库中编排表中数据的内部方
法。表的存储由两部分组成，一部分是表的数据页面，另一
部分是索引页面。索引就存放在索引页面上。
在某种程度上，可以把数据库看作一本书，把索引看作
书的目录，通过目录查找书中的信息，显然比查找没有
目录的书要方便、快捷。
MySQL数据库原理及应用（微课版|第4版）
• 理解索引
索引一旦创建，将由数据库自动管理和维护。在编写SQL
查询语句时，具有索引的表与不具有索引的表没有任何
区别，索引只是提供一种快速访问指定记录的方法。
索引可以提高数据的访问速度
索引可以确保数据的唯一性。
（一）索引概述
MySQL数据库原理及应用（微课版|第4版）
普通索引和唯一索引
单列索引和组合索引
全文索引
空间索引
（二）索引的类型
MySQL数据库原理及应用（微课版|第4版）
索引并非越多越好
避免对经常更新的表建立过多的索引
数据量小的表最好不要使用索引
在不同值少的列上不要建立索引
指定唯一索引是由某种数据本身的特征来决定
为经常需要排序、分组和联合操作的字段建立索引
（三）索引的设计原则
MySQL数据库原理及应用（微课版|第4版）
（四）使用Navicat工具创建索引
当给表创建UNIQUE约束时，MySQL会自动创建唯一索引。
索引的名称必须符合MySQL的命名规则，且必须是表中唯一的。
可以在创建表时创建索引，或是给现存表创建索引。
只有表的所有者才能给表创建索引。
• 创建索引时的注意事项
MySQL数据库原理及应用（微课版|第4版）
（1）在Navicat中，连接到MySQL服务器。展开【mysql80】|
【gradem】|【表】，在创建student表的窗口中选中【索引】
选项卡。
• 我们以给gradem数据库中的student表创建一个普通索引
“index_sname”为例介绍创建索引的操作步骤：
（四）使用Navicat工具创建索引
（2）分别在【索引】选项卡的【名】、【栏位】、
【索引类型】及【索引方式】等列里输入索引名称、
输入参与索引的字段、选择索引的类型及索引方式等
信息，然后单击【保存】按钮，该索引创建成功。
MySQL数据库原理及应用（微课版|第4版）
（五）使用SQL语句创建索引
语法格式：
CREATE TABLE <表名>
(<字段1> <数据类型1> [<列级完整性约束条件1>]
[,<字段2> <数据类型2> [<列级完整性约束条件2>]] [,…]
[,<表级完整性约束条件1>]
[,<表级完整性约束条件2>] [,…]
[UNIQUE|FULLTEXT|SPATIAL] <INDEX|KEY>
[索引名](属性名[(长度)] [,…])
);
• 1、使用CREATE TABLE语句在创建表时创建索引
MySQL数据库原理及应用（微课版|第4版）
参数说明如下。
① UNIQUE|FULLTEXT|SPATIAL：是可选参数，三者选一，分别表示唯一索
引、全文索引和空间索引。此参数不选，则默认为普通索引。
② INDEX或KEY：为同义词，用来指定创建索引。
③ 索引名：是指定索引的名称，为可选参数，若不指定，MySQL默认字段
名为索引名。
④ 属性名：指定索引对应的字段名称，该字段必须为表中定义好的字段。
⑤ 长度：指索引的长度，必须是字符串类型才可以使用。
• 1、使用CREATE TABLE语句在创建表时创建索引
（五）使用SQL语句创建索引
MySQL数据库原理及应用（微课版|第4版）
CREATE TABLE student
(
…
UNIQUE INDEX id_sno(sno)
);
【例】 为student表sno列创建唯一索引id_sno。
【例】为sc表的sno和cno列创建普通索引id_sc。
CREATE TABLE sc
(
…
INDEX id_sc(sno,cno)
);
（五）使用SQL语句创建索引
MySQL数据库原理及应用（微课版|第4版）
语法格式：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX <索引名>
ON <表名> (属性名[(长度)] [,…]);
• 2、使用CREATE INDEX语句在现存表中创建索引
（五）使用SQL语句创建索引
MySQL数据库原理及应用（微课版|第4版）
CREATE INDEX id_birth ON student (sbirthday);
【例】 为student表sbirthday列创建一个普通索引
id_birth。
（五）使用SQL语句创建索引
MySQL数据库原理及应用（微课版|第4版）
语法格式：
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX
<索引名> (属性名[(长度)] [,…]);
• 3、使用ALTER TABLE语句创建索引
（五）使用SQL语句创建索引
MySQL数据库原理及应用（微课版|第4版）
• 1、使用Navicat管理工具删除索引
（1）在Navicat中，连接到mysql服务器。
（2）展开【mysql80】|【gradem】|【表】，选中要创建
索引的表，进入【设计表】窗口，在窗口中选中【索引】
选项卡，单击工具栏上的【删除索引】按钮，或者用鼠标
右键单击要删除的索引，在快捷菜单中执行【删除索引】
命令即可。
（六）删除索引
MySQL数据库原理及应用（微课版|第4版）
• 2、使用SQL语句删除索引
使用SQL语言的DROP INDEX语句可删除索引，语句格式
如下：
DROP INDEX <索引名> ON <表名>;
例如：DROP INDEX id_name ON student;
（六）删除索引
MySQL数据库原理及应用（微课版|第4版）
【任务实施】
针对本任务提出中的问题，王宁使用SQL语句创建索引来解决，具体
实现代码如下。
（1）使用CREATE INDEX语句创建索引。
CREATE UNIQUE INDEX id_sno ON student_new(sno);
（2）使用WHERE语句查询sno=1000000的记录，观察反应时间。
SELECT * FROM student_new WHERE sno=1000000;
任务6-1 使用索引优化查询性能
MySQL数据库原理及应用（微课版|第4版）
在实际工作中，随着公司业务的高速发展，数据库中单个表的
数据规模可能达到几百万甚至几千万条记录。在这种情况下，
往往会导致业务系统的响应时间过长，引起用户的不满。
思政小贴士
为了缩短响应时间，数据库管理员要想尽各种办法优
化性能，首选方案是为合适的字段创建合适的索引。
建立索引后，如果依旧不能有效解决问题，再尝试其
他方法进行优化。所以，在数据库开发过程中，我们
往往会根据需要，逐步优化表结构，从而使性能达到
最优。这就需要我们
。
MySQL数据库原理及应用（微课版|第4版）
任务6-2 使用视图优化查询性能
【任务提出】
王宁已经能够熟练使用多表连接查询实现“查询
20200101班选修“高等数学”课程且成绩在80-90分的学生
姓名、学号、班级号及成绩”的题目。但是他发现频繁用到
这段代码的时候需要重写代码、重新编译、重新执行，这种
实现方式存在着代码复用性差、效率低等缺点。
因此，王宁需要通过创建视图来解决这些问题。
MySQL数据库原理及应用（微课版|第4版）
• 理解视图
（一）视图概述
视图是从一个或者几个基本表或者视图中导出的虚拟表，是
从现有基表中抽取若干子集组成用户的“专用表”，这种构
造方式必须使用SQL中的SELECT语句来实现。
在定义一个视图时，只是把其定义存放在数据库中，并不
直接存储视图对应的数据，直到用户使用视图时才去查找
对应的数据。
MySQL数据库原理及应用（微课版|第4版）
• 使用视图的优点
简化对数据的操作
自定义数据
数据集中显示
导入和导出数据
合并分割数据
安全机制
（一）视图概述
MySQL数据库原理及应用（微课版|第4版）
例如：为“gradem”数据库创建一个视图
View_stud，要求连接student表、sc表和
course表，视图内容包括所有男生的sno、
sname、ssex、cname和degree。
（二）使用Navicat工具创建视图
MySQL数据库原理及应用（微课版|第4版）
操作步骤：
（1）在Navicat中，连接到mysql服务器。
（2）展开【mysql】|【gradem】|【视图】，右键单击该节点，选择【新建
视图】命令。
（3）打开【视图】对话框，选中【视图创建工具】选项卡，将所需的表
student、sc和course，拖入到右上侧窗口中。
（4）确定视图中的输出列。在此选择student表中的“sno”、“sname”和
“ssex”，sc表中的“degree”，course表中的“cname”。
（二）使用Navicat工具创建视图
（5）设置3个表的连接条件。
（6）设置视图的条件。
（7）单击工具栏上的【保存】按钮，在弹出的【视图名】窗口
中输入视图名称“View_stud”，单击【确定】按钮即可完成。
MySQL数据库原理及应用（微课版|第4版）
语法格式：
CREATE VIEW view_name [(Column [,…n])]
AS select_statement
[WITH CHECK OPTION];
参数说明：
（1）view_name：定义视图名，其命名规则与标识符的相同，并且在
一个数据库中要保证是唯一的，该参数不能省略。
（2）Column：声明视图中使用的列名。
（3）AS：说明视图要完成的操作。
（4）select_statement：定义视图的SELECT命令。
（5）WITH CHECK OPTION：强制所有通过视图修改的数据满足
select_statement语句中指定的选择条件。
（三）使用CREATE VIEW语句创建视图
MySQL数据库原理及应用（微课版|第4版）
【例】 有条件的视图定义。定义视图v_student，查询所
有选修数据库课程的学生的学号（sno）、姓名（sname）、
课程名称（cname）和成绩（degree）。
CREATE VIEW v_student
AS
SELECT ,sname,cname,degree
FROM student A,course B,sc C
WHERE = AND = AND
cname='数据库';
（三）使用CREATE VIEW语句创建视图
MySQL数据库原理及应用（微课版|第4版）
1、使用视图进行数据检索
视图的查询总是转换为对它所依赖的基本表的等价查
询。利用SQL的SELECT命令和Navicat都可以对视图进
行查询，其使用方法与基本表的查询完全一样。
2、通过视图修改数据
视图也可以使用INSERT命令插入行，当执行INSERT命
令时，实际上是向视图所引用的基本表插入行。视图
中的INSERT命令与在基本表中使用INSERT命令的格式
完全一样。
（四）视图的使用
MySQL数据库原理及应用（微课版|第4版）
【例】 利用V1_student视图向表student中插入一条数据。
CREATE VIEW V1_student
AS
SELECT sno,sname,saddress FROM student;
【例】 将例中插入的数据删除。
DELETE FROM V1_student WHERE sname='王小龙';
INSERT INTO V1_student
VALUES('2005020301','王小龙','山东省临沂市');
（四）视图的使用
MySQL数据库原理及应用（微课版|第4版）
1、使用Navicat修改视图
（1）展开服务器，展开数据库。
（2）单击【视图】节点，用鼠标右键单击要修改的视图
名称，在快捷菜单中选择【设计视图】命令，进入视图设
计窗口，用户可以在这个窗口中对视图进行修改。
（五）视图的修改
MySQL数据库原理及应用（微课版|第4版）
2、使用SQL语句修改视图
语法格式：
ALTER VIEW view_name [(Column[,…n])]
AS select_statement
[WITH CHECK OPTION];
【例】 修改例中的视图V1_student。
ALTER VIEW V1_student
AS SELECT sno,sname FROM student;
（五）视图的修改
MySQL数据库原理及应用（微课版|第4版）
1、使用Navicat删除视图
（1）在当前数据库中展开【视图】节点。
（2）用鼠标右键单击要删除的视图（如V1_student），在弹出
的快捷菜单中选择【删除视图】命令；或单击要删除的视图，
然后单击上方的【删除视图】按钮。
（3）在弹出的【确认删除】对话框中单击【删除】按钮即可。
（六）视图的删除
MySQL数据库原理及应用（微课版|第4版）
【例】 删除视图V1_student。
DROP VIEW V1_student;
2、使用SQL语句删除视图
语法格式：
DROP VIEW {view} [,…n];
（六）视图的删除
MySQL数据库原理及应用（微课版|第4版）
【任务实施】
（1）使用CREATE VIEW语句创建视图。
CREATE VIEW view_stuandsc
AS
SELECT sname,,classno,degree
FROM student a,sc b,course c
WHERE = AND =
AND cname = '高等数学' AND degree BETWEEN 80 AND 90;
（2）使用SELECT语句查询view_stuandsc。
SELECT * FROM view_stuandsc;
任务6-2 使用视图优化查询性能
针对本任务提出中的问题，王宁使用SQL语句创建视图来解决，具体实现代码如
下。
MySQL数据库原理及应用（微课版|第4版）
当删除不存在的表、视图、存储过程、函数、触发器时，会提
示“Unknown X X X”,根据提示解决即可。但是SQL经常会被嵌
入到高级程序设计语言中，如C、Java、Python语言。这样，我
们就必须保证每次编译和运行都能够正常进行，因此需要加入
IF EXISTS子句来提高程序的健壮性。
思政小贴士
随着学习的深入和知识的积累，我们还应该
。
MySQL数据库原理及应用（微课版|第4版）
项目总结
本项目带领大家学习了索引和视图的基本操作，包括索引的概念、
类型、设计原则及创建与删除操作，以及视图的概念、优点、创建、
使用、修改与删除操作。
要求大家加强复习，增进理解。把各个知识点学会、
领悟，能举一反三。
主要内容
重点要求大家掌握索引和视图的创建与使用。
重难点要求
MySQL数据库原理及应用（微课版|第4版）
志存高远 自强不息
