2.5 索引与视图是性能优化的好帮手——数据库性能优化

  索引类似于一本书的目录，它记录了数据在数据库中存放的位置，创建索引可以帮助用户快速找出某个字段中特定值的行，由此提高MySQL查询数据的速度。视图是由一个或多个数据表组合而成的一张虚拟表，用户可以从这张虚拟表中查询到需要的数据，但其本身不存储数据。本节将详细介绍索引和视图，主要内容包括：创建索引、修改索引、查看索引、删除索引、创建视图、查看视图、修改视图、删除视图。

2.5.1 索引

问题A：索引的含义及作用是什么？
回答A：索引是对数据表中一个或多个字段的值进行排序的一种数据结构，使用索引可以提高数据库中特定数据的查询效率。例如一张有3万条记录的学生信息表，现在想要查询id编号为10000的学员信息，如果没有对id列建立索引，则计算机需要逐行遍历整个数据表，直到找出id等于10000这一行为止，相反如果已经在id列上创建索引，则MySQL不需要逐行遍历全表，而是直接在索引字段里找到10000，从而得知这一行的位置。

问题B：索引的存储类型及常见分类有哪些？
回答B：MySQL中索引的存储类型包括B-Tree和HASH两种，具体和表的存储引擎相关，例如MyISAM、InnoDB存储引擎只支持B-Tree索引，而Memory存储引擎可以同时支持B-Tree和HASH索引。

约束与索引
（1）约束与索引到底有什么联系和区别？
约束与索引是两种不同的概念，前者是为了维护数据库的完整性，后者是为了提高数据库查询效率，但在创建约束的同时相当于在指定列上创建了一个索引。
（2）唯一约束与唯一索引又有什么关系？
创建唯一约束时，系统会自动创建唯一索引，都要求唯一字段不重复但可以为空，程序实现起来并没有任何区别。
（3）主键约束与主键索引有什么关系？
同理，创建主键约束时，系统也会自动创建主键索引，都要求主键字段不重复且不为空，程序实现起来同样没有任何区别。
注意：索引能够有效缩短数据检索、分组和排序的时间，加速多表连接的查询速度，但过多的索引会占用大量磁盘空间，并降低数据表增删改的效率，因此在设计数据库时，需要合理的添加索引，避免索引的滥用。

问题C：索引创建、查看、删除的方法有哪些？
回答C：MySQL提供多样化的索引创建、查看、删除方法，具体方法如下：
（1）创建索引
  MySQL支持3种索引创建的方式：
① 创建表create table时指定索引字段，基本SQL语法格式如下：

create table table_name( 
column_name column_type [key_type],
index_type(column_name)
);

【例】创建一张名为stu_inf4的学员信息表，在id列上创建普通索引，输入语句如下：

【例】创建一张名为stu_inf5的学员信息表，在id列上创建唯一索引，输入语句如下：

【例】创建一张名为stu_inf6的学员信息表，在id列上创建主键索引，输入语句如下：

创建索引注意事项
（1）同一张表中，用户自定义创建的索引名称不能一样；
（2）同一张表中，有且只能创建一个主键索引；
（3）同一张表中，可以创建多个唯一索引；
（4）同一个字段，若已设置索引则需要删除后再创建索引，而不能直接覆盖；

② 修改表alter table时创建索引字段，基本SQL语法格式如下：

alter table table_name add index_type index_name(column_name);

【例】修改名为stu_inf4的学员信息表，在stu_name列上创建普通索引，输入语句如下：

【例】修改名为stu_inf5的学员信息表，在stu_name列上创建唯一索引，输入语句如下：

【例】创建一张名为stu_inf7的学员信息表，修改表时在id列上创建主键索引，输入语句如下：

③ 使用create index语句添加索引，基本SQL语法格式如下：

create index_type index_name on table_name (column_name);

【例】在stu_inf4的学员信息表中stu_sex列上创建普通索引，输入语句如下：

【例】在stu_inf5的学员信息表中stu_sex列上创建唯一索引，输入语句如下：

（2）查看索引
  上述例子中都采用查看表结构的方法来查看索引，其实查看索引有标准的SQL语法：

show index from table_name;

【例】查看stu_inf4的学员信息表中已创建的索引，输入语句如下：

（3）删除索引
  MySQL支持2种索引的删除方式：
① 修改表alter table时删除索引字段，基本SQL语法格式如下：

alter table table_name drop index index_name;

【例】删除stu_inf4的学员信息表中名为idx的索引，输入语句如下：

② 使用drop index语句删除索引，基本SQL语法格式如下：

drop index index_name on table_name;

【例】删除stu_inf4的学员信息表中名为idx1的索引，输入语句如下：

2.5.2 “增删改查”视图

  视图是由数据库中一个或多个表中组合而成的虚拟表，视图一经定义便存储在数据库，但与其对应的数据并没有再复制一份，而是存储在原始数据表中。如果把数据库比作一间房子，那么视图就是这间房子上的一扇窗户，我们可以从窗户外看到房内的设施，但具体的设施还在摆在房间里。因此想要修改视图中看到的数据，必须修改原始数据表，换句话说原始表中数据发生变化会自动地反映到视图中。
（1）创建视图
  MySQL既支持单个数据表上创建视图，也支持在多个表上创建视图，基本SQL语法格式如下：

create view view_name as select column_name from table_name;

① 单表上创建视图
【例】在order_sale订单表上创建一张名为view_t的视图，输入语句如下：

注意：默认情况下创建的视图与原始表的字段名称是一样的，但也可以在创建视图的同时修改视图字段名称，输入语句如下：

② 多表上创建视图
【例】在order订单表和product产品表上创建一张名为view_sum的视图，输入语句如下：

（2）查看视图
  MySQL支持多样化的视图查看方式，本书介绍2种常用的视图查看方法：
① 使用describe语句查看视图基本信息，基本SQL语法格式如下：

describe view_name;

【例】查看名为view_t视图的基本信息，输入语句如下：

② 使用show create view语句查看视图详细信息，基本SQL语法格式如下：

show create view view_name;

【例】查看名为view_t视图的详细信息，输入语句如下：

（3）修改视图
  MySQL支持2种视图修改方法：
① 使用create or replace view语句修改视图，基本SQL语法格式如下：

create or replace view view_name as select column_name from table_name;

注意：修改视图本质上是创建新视图然后覆盖原始视图的过程。
【例】修改视图view_t，令该视图字段与原始表字段保持一致，输入语句如下：

② 使用alter语句修改视图，基本SQL语法格式如下：

alter view view_name as select column_name from table_name;

【例】修改视图view_t1，令该视图字段与原始表字段保持一致，输入语句如下：

（4）删除视图 删除视图与删除数据表SQL语法类似：

drop view view_name;

【例】删除名为view_t的视图，输入语句如下：

练习

练习使用数据表store库存信息表，表结构如下：

（1）利用3种不同的方式，在store库存信息表中p_name字段上创建普通索引。
（2）基于store库存信息表创建视图，视图名为view_store。

练习答案

（1）利用3种不同的方式，在store库存信息表中p_name字段上创建普通索引，输入语句如下：

（2）基于store库存信息表创建视图，视图名为view_store，输入语句如下：