优化查询信息管理数据库（索引、视图）

目录

1. 使用索引优化查询性能

1.1 索引基本概念

1.2 索引的类型

1.3 索引的设计原则

1.4 使用SQL语句创建索引

（1）使用CREATE TABLE语句在创建表时创建索引

（2）使用CREATE INDEX语句在现存表中创建索引

1.5 使用SQL语句删除索引

（1）语法格式

（2）示例展示

2. 使用视图优化查询性能

2.1 视图基本概念

2.2 使用CREATE VIEW语句创建视图

（1）语法格式

（2）示例展示

2.3 视图的使用

（1）使用视图进行数据检索

（2）通过视图修改数据

2.4 视图的修改

（1）语法格式

（2）示例展示

2.5 视图的删除

（1）语法格式

（2）示例展示

---

1. 使用索引优化查询性能

1.1 索引基本概念

（1）索引是一个单独的、物理的数据库结构，索引只是提供一种快速访问指定记录的方法。

（2）索引可以提高数据的访问速度，可以确保数据的唯一性。

1.2 索引的类型

（1）普通索引和唯一索引（UNIQUE）

普通索引是基本索引类型，允许在定义索引的列中插入重复值和空值。唯一索引是指索引列的值必须唯一，但允许有空值。

（2）单列索引和组合索引

（3）全文索引（FULLTEXT）

全文索引是指在定义索引的列上支持值的全文查找。

（4）空间索引（SPATIAL）

空间索引是对空间数据类型的字段建立的索引。

1.3 索引的设计原则

（1）索引并非越多越好。

（2）避免对经常更新的表建立过多索引。

（3）数据量小的表上最好不要使用索引。

（4）在不同值少的列上不要建立索引。

（5）指定唯一索引是由某种数据本身的特征来决定的。

（6）为经常需要排列、分组和联合操作的字段建立索引。

1.4 使用SQL语句创建索引

（1）使用CREATE TABLE语句在创建表时创建索引

1.语法格式

CREATE TABLE <表名>

(<字段1> <数据类型1> [<列级完整性约束条件1>]

[,<字段2> <数据类型2> [<列级完整性约束条件2>]] [,…]

[,<表级完整性约束条件1>]

[,<表级完整性约束条件2>] [,…]

[UNIQUE|FULLTEXT|SPATIAL] <INDEX|KEY>

             [索引名](属性名[(长度)] [,…])

);

说明：“UNIQUE|FULLTEXT|SPATIAL”是三选一，分别表示唯一索引、全文索引和空间索引，如果不选，默认为普通索引。“INDEX|KEY”是同义词，用来指定创建索引。

2.示例展示（注：以下语句中省略了创建表的各项语句）

【例1】 为student表sno列创建唯一索引id_sno。

CREATE TABLE student

(…

UNIQUE INDEX id_sno(sno)

);

【例2】为sc表的sno和cno列创建普通索引id_sc。

CREATE TABLE sc

(…

INDEX id_sc(sno,cno)

);

（2）使用CREATE INDEX语句在现存表中创建索引

1.语法格式

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX  <索引名>

ON <表名> (属性名[(长度)] [,…]);

2.示例展示

【例3】为student表sbirthday列创建一个普通索引id_birth。

CREATE INDEX id_birth ON student (sbirthday);

1.5 使用SQL语句删除索引

（1）语法格式

DROP INDEX <索引名> ON <表名>;

（2）示例展示

【例4】删除student表上的id_name索引。

DROP INDEX id_name ON student;

2. 使用视图优化查询性能

2.1 视图基本概念

（1）视图是从一个或者几个基本表或者视图中导出的虚拟表。

（2）在定义一个视图时，只是把其定义存放在数据库中，并不直接存储视图对应的数据。

（3）优点：简化对数据的操作、自定义数据、数据集中显示、导入和导出数据、合并分割数据、安全机制。

2.2 使用CREATE VIEW语句创建视图

（1）语法格式

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语句中指定的选择条件。

（2）示例展示

【例5】 有条件的视图定义。定义视图v_student，查询所有选修数据库课程的学生的学号（sno）、姓名（sname）、课程名称（cname）和成绩（degree）。

CREATE VIEW v_student

 AS

  SELECT A.sno,sname,cname,degree

FROM student A,course B,sc C

  WHERE A.sno=C.sno AND B.cno=C.cno AND cname='数据库';

2.3 视图的使用

（1）使用视图进行数据检索

利用SQL的SELECT命令和Navicat都可以对视图进行查询，其使用方法与基本表的查询完全一样。

（2）通过视图修改数据

视图也可以使用INSERT命令插入行，当执行INSERT命令时，实际上是向视图所引用的基本表插入行。与基本表使用一样。

示例展示：

【例6】 利用V1_student视图向表student中插入一条数据

INSERT INTO V1_student

VALUES('2005020301','王小龙','山东省临沂市');

【例7】 将例6.5中插入的数据删除。

DELETE FROM V1_student WHERE sname='王小龙';

2.4 视图的修改

（1）语法格式

ALTER VIEW view_name [(Column[,…n])]

      AS select_statement

      [WITH CHECK OPTION];

（2）示例展示

【例8】 修改例6.5中的视图V1_student。

ALTER VIEW V1_student

  AS SELECT sno,sname FROM student;

2.5 视图的删除

（1）语法格式

DROP VIEW {view} [,…n];

（2）示例展示

【例9】 删除视图V1_student。

DROP VIEW V1_student;