数据库--数据库基础7--视图、索引、事务、存储过程

一、视图

1、视图(view)定义

• 视图是基于某个查询结果的虚表
• 起作用就是方便用户对数据的操作(如果在原表中不允许修改数据，创建这个表的视图；如果修改一个表需要同时修改相关联的别的表，可以创建一个这些关联表的大视图)

2、创建(create)视图

格式：
create view 视图名字 as select语句；
根据对某个表的查询结果而创建的视图
注意：

• 视图既然作为一张虚表，那么对于实表的增删改查操作，视图同样成立
• 视图既然根据实表得到，那么对视图中数据的增删改操作也会影响实表
• 向视图中插入数据时，主要约束的影响

修改视图结构：
alter view 视图名字 as select查询语句；

3、插入数据

格式：
insert into 视图名字 values(值1，值2，值3，……)；

4、修改视图

格式：
update 视图名字 set 列名=值 where 条件；

5、删除视图

删除视图中的数据：
delete from 视图名字 where 条件；
删除视图：
drop view 视图名；
删除两个视图：
drop view 视图1，视图2；
drop对原表无影响

二、索引

1、索引

1）索引的定义

类似于书本中的目录

• MySQL的索引是对数据库表中一个或多个列的值进行排序的结构。
• 索引有助于更快的获取信息。
• 是用来定位的。

2）索引的意义

对表中的列是否创建索引，以及创建何种索引，对于查询的响应速度会有很大差别。创建了索引的列几乎是立即响应，而不创建索引的列则需要较长时间的等待。

针对海量数据

• 加快数据检索
• 保证数据的唯一性
• 实现表与表之间的参照完整性
• 在使用group by、order by子句进行查询时，利用索引可以减少排序和分组的时间

3）索引的优缺点

• 优点
  1. 加快查询速度（定位）
  2. 保证数据唯一性（唯一约束）
  3. 实现表与表之间的参照完整性（进行外键约束参照的列）
  4. 在使用group by、order by子句进行查询时，利用索引可以减少排序和分组的时间
• 缺点：
  1. 索引需要占物理空间
  2. 当对表中数据进行增加、删除和修改时，索引也要动态的维护，降低了数据的维护速度

4）索引结构

索引是在MySQL的存储引擎层中实现的，而不是服务器层实现的，所以每种存储引擎的索引都不一定完全相同，也不是所有的引擎都支持所有的索引类型的。

• BTREE索引：最常见的索引类型，大部分索引都支持B树索引
• HASH索引：只有Memory引擎支持，使用场景简单
• R-tree索引(空间索引)： 是MylSAM引擎支持的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
• Full-text（全文索引）：全文索引也是MylSAM的一个特殊索引类型，主要用于全文索引

我们常说的索引，如果没有特别指明，都是指B+树(多路搜索树，并不一定是二叉的)结构组织的索引，其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+tree索引，统称为索引

i、BTREE结构

BTREE又叫多路平衡搜索树，一棵M叉的Btree特性如下：

• 树中每个节点最多包含m个孩子
• 除根节点与叶子节点外，每个节点至少有(m/2)(小数向上取整)个孩子
• 若根节点不是叶子节点，则至少有两个孩子
• 所有的叶子节点都在同一层
• 每个非叶子节点由n个key与n+1个指针组成，其中(m/2)-1<=n<=m-1

以5叉Btree为例，key的数量：公式推导得，n的范围为[2,4]，当n>4时，中间节点分裂到父节点，两边节点分裂。

ii、B+TREE结构

B+tree为BTREE的变种，B+tree与BTREE的区别为

• n叉B+tree最多含有n个key，而Btree最多含有n-1个key
• B+tree点的叶子节点保存所有的key信息，按照Key的大小顺序排列
• 所有的非叶子节点都可以看作时key的索引部分

由于B+tree只有叶子节点保存key信息，查询任何key都要从root走到叶子，所以B+tree的查询效率更加稳定

iii、MySQL中的B+tree

MySQL索引数据结构对经典的B+tree进行了优化。在原B+tree的基础上，增加了指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+tree，提高区间访问的性能

在访问

2、索引的分类

1）按存储类型分类

• B-树索引：innoDB 、MylSAM均支持
• 哈希索引

2）MySQL的常用索引类型

• 普通索引：
  • 基本索引类型
  • 允许在定义索引的列中插入重复值和空值
• 唯一索引：
  • 索引列数据不重复
  • 允许有空值，（创建一个唯一约束unique的列时，自动创建唯一索引）
• 主键索引
  • 主键列中的每个值是非空、唯一的
  • 一个主键将自动创建主键索引（primary key）
• 复合索引
  • 将多个列组合作为索引
• 全文索引
  • 支持值的全文查找(全库)
  • 允许重复值和空值
  • MySQL使用innoDB搜索引擎不支持全文索引，MylSAM支持全文索引
• 空间索引
  • 对空间数据类型的列建立的索引（全磁盘空间）

3、创建索引

格式：
create [unique|full text|spatial] index 索引名 on 表名(列名1，列名2)；
如果方括号中的全都不写就是普通索引，写unique为唯一索引，full text为全文索引，spatial为空间索引，同时写多个列名时为复合索引

4、查看所有已经创建的索引

格式：
show index from 表名；

5、删除索引

格式：
drop index 索引名 on 表名；
删除表时，表中所有的索引都被删除

6、索引的使用

查询所有数据时不使用索引
只有在以有索引的列为条件查询时才使用索引

7、哪些列需要创建索引

1）按照下列标准选择建立索引的列

• 频繁搜索的列
• 经常用作查询选择的列
• 经常排序、分组的列
• 经常用作连接的列（主键、外键）

2）请不要使用下面的列创建索引

• 仅包含几个不同值得列，比如性别
• 表中仅包含几行

8、使用索引得经验

1. 查询时减少使用*返回全部列，不要返回不需要得列
2. 索引应该尽量校，在字节数小得列上建立索引
3. where子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前
4. 避免在order by子句中使用表达式，尽量使用单纯的列

三、事务

事务指一组操作要么全部成功，要么全部失败

1）事物的四大特性ACID

• 原子性 atomic
• 一致性 consitent
• 隔离性 isolation
• 持久性 duration