文章目录
一、视图
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)索引的优缺点
- 优点
- 加快查询速度(定位)
- 保证数据唯一性(唯一约束)
- 实现表与表之间的参照完整性(进行外键约束参照的列)
- 在使用group by、order by子句进行查询时,利用索引可以减少排序和分组的时间
- 缺点:
- 索引需要占物理空间
- 当对表中数据进行增加、删除和修改时,索引也要动态的维护,降低了数据的维护速度
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、使用索引得经验
- 查询时减少使用*返回全部列,不要返回不需要得列
- 索引应该尽量校,在字节数小得列上建立索引
- where子句中有多个条件表达式时,包含索引列的表达式应置于其他条件表达式之前
- 避免在order by子句中使用表达式,尽量使用单纯的列
三、事务
事务指一组操作要么全部成功,要么全部失败
1)事物的四大特性ACID
- 原子性 atomic
- 一致性 consitent
- 隔离性 isolation
- 持久性 duration