文章目录

索引

索引是 MySQL 中重要的对象之一，数据库中通过 INDEX 表示索引，主要是为了在数据量较大的数据表中提升数据的查询效率

为什么使用索引

索引是 MySQL 中一种十分重要的数据库对象。它是数据库性能调优的基础，常用于实现数据的快速检索。

索引就是根据表种的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

在 MySQL 中，通常由以下两种方式访问数据库 表的行数据

1. 顺序访问

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，知道在无序的行数据中找到符合条件的目标数据，这种方式实现比较简单，但是当表中有大量数据的时候，效率非常低下。

2. 索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

例如，在学生基本信息表 students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表，当用户需要查找 student_id 为 12022 的数据时，系统现在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率

创建索引

使用 create index

可以使用专门用于创建索引的 create index 语句在一个已有的表上创建索引

语法格式：

create index <索引名> on <表名> (列名)

查看索引

在 MySQL 中，如果要查看已创建的索引的情况，可以使用 show index 语句查看表中创建的索引

语法格式：

show index from <表名> [from <数据库名>]

<表名>：要显示索引的表
<数据库名>：要显示的表所在的数据库

删除索引

当不再需要索引时，可以使用 drop index 语句对索引进行删除

语法格式：

drop index <索引名> on <表名>

索引的使用原则和注意事项

建立索引时应遵循以下原则：

  • 在经常需要搜索的列上建立索引，可以加快搜索的速度
  • 在作为主键的列上创建索引，强制该列的唯一性，并组织表中数据的排列结构
  • 在经常使用表连接的列上创建索引，这些列主要是一些外键，可以加快表连接的速度
  • 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，所以其指定的范围是连续的
  • 在经常需要排序的列上创建索引，因为索引已经排序，所以可以加快排序查询
  • 在经常使用 where 子句的列上创建索引，加快条件的判断速度
  • 闲时添加索引，如果一张数据表中数据量较大，直接添加索引可能会导致锁表，可以先统计数据表对应的业务使用时间，选择闲时添加索引

索引的弊端：：

  • 创建索引和维护索引要耗费时间，这种事件随着数据量的增加而增加
  • 除了数据表占数据空间之外，每一个索引还要占一定的物理空间
  • 当对表种的数据进行增加，删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度

不适合索引的环境：

  • 对于那些在查询中很少使用或参考的列不应该创建索引，因为这些列很少使用到，所以有索引并不能提高查询速度。相反，增加了索引，反而减低了系统的维护速度，并增大了空间要求
  • 对于那些只有很少数据值的列也不应该创建索引，因为这些列的取值很少，例如性别列。查询结果集的数据行占了表中数据行的很大比例，增加索引并不能明显加快检索速度
  • 对于那些定义为 text，image 和 bit 数据类型的列不应该创建索引，因为这些列数据量要么相当大，要么取值很少
  • 当修改性能远远大于检索性能时，不应该创建索引，因为修改性能和检索性能是互相矛盾的，当创建索引时，会提高检索性能，降低修改性能。当减少索引时，会提高修改性能，降低检索性能。

视图

MySQL 视图（view）是一种虚拟存在的表，如同真实表一样，视图也由行和列构成，但视图并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的。

数据库中只存放了视图的定义，并没有存放视图中的数据，这些数据都存放在定义视图查询所引用的真实表中。使用视图查询数据时，数据库会从真实表中取出对应的数据。因此，视图中的数据是依赖于真实表中的数据的，一旦真实表种的数据发生变化，显示在视图中的数据也会发生变化

视图可以从原有的表上选取对用户有用的信息，那些对用户没用，或者用户没有权限了解的信息，都可以直接屏蔽掉，作用类似于筛选。这样左既使应用简单化，也保证了系统的安全。

视图的优点

视图于表在本质上虽然不相同，但视图经过定义以后，机构形式和表一样，可以进行查询，修改，更新和删除等操作，同时视图有以下优点：

  • 定制用户数据，聚焦特定的数据

在实际的应用过程中，不同的用户可能对不同的数据有不同的要求

例如，学生基本信息表，课程表和教师信息表等多种表同时存在时，可以根据需求让不同的用户使用各自的数据。学生查看修改该自己基本信息的视图，安排课程人员查看修改该课程表和教师信息的视图，教师查看学生信息和课程信息表的视图

  • 简化数据操作

在使用查询时，很多时候要使用聚合函数，同时还要显示其它字段的信息，可能还要关联到其它表，语句可能会很长，如果这个动作频繁发生，可以创建视图简化操作

  • 提高数据的安全性

视图是虚拟的，物理上是不存在的，可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全

  • 共享所需数据

通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次

  • 更改数据格式

通过使用视图，可以重新格式化检索出的数据，并组织输出到其它应用程序中

  • 重用 SQL 语句

视图提供的是对查询操作的封装，本身不包含数据，所呈现的数据是根据视图定义从基础表中检索出来的。视图定义后，可以方便地重用该视图

创建视图

可以使用 create view 语句来创建视图

语法格式如下：

create view <视图名> [(字段名)] as <select语句>

视图名： 指定视图的名称，在数据库中必须是唯一的，不能与其它表或视图重名
字段名： （可选）指定字段的名称，和select语句查询出的字段一一对应
select语句： 指定创建视图的 select语句，可用于查询多个基础表或源视图

案例操作：

# 将学生表中 姓名 和 年龄 列抽取到一个视图中
create view student_view (姓名,年龄) as
    select name,age from student;

# 将学生表中 姓名 以及 课程表中该名学生对应的课程抽取到一个视图中

create view student_view (姓名,年龄,课程) as
    select s.name,s.age,c.name from student s left join course c 
        on s.cid=c.id;

查看视图

查看视图详细信息

show create view 视图名;

查看所有视图

# 所有视图的定义都是储存在 information_schema 数据库下的 views 表中
select * from information_schema.views;

修改视图

基本语法：

alter view <视图名> as <select语句>

修改视图内容

视图是一个虚拟表，实际的数据来自于基本表，所以通过插入，修改和删除操作更新视图中的数据，实质上是在更新视图所引用的基本表的数据

对视图的更改就是对基本表的更改，因此在修改时，要满足基本表的数据定义

还有一些特定的其它结构，这些结构会使得视图不可更新。具体地讲，如果视图包含以下结构中一种，它就是不可更新的：

  • 聚合函数 sum()，min()，count() 等
  • distinct 关键字
  • gruop by 子句
  • having 子句
  • union 或 union all 运算符
  • 位于选择列表中的子查询
  • from子句中的不可更新视图或包含多个表
  • where 子句中的子查询，引用 from 子句中的表
  • ALGORITHM 选项为 TEMPTABLE（使用临时表总会使视图成为不可更新的）的时候

删除视图

drop view <视图名1>[ , <视图名2> …]