视图: 视图就是将某个查询语句存储在并将其命名,视图本身并不存储数据,数据还是在基本中存储
格式:
create view 视图名 as 查询语句
select*form 视图名
drop view 视图名
存储过程:
存储过程就是把一段存储逻辑放入到数据库中,使用时由jdbc调用即可
减少应用程序和数据库交互次数,在数据库内部执行,执行效率高.
存储事先需要定义, 有3种类型参: in 入参 out 返回 inout
函数:
函数是一个特殊的存储过程,函数的存储过程需要有输入参数,还要有输出参数,没有返回值,并且不能在普通的sql过程中调用.
函数一般用在查询场景,只有入参,没有输出参数, 必须有返回值,可以在普通的sql中调用使用
触发器:
触发器可以理解为特殊的存储过程,不需要调用,在满足某种条件时自动执行.
对表进行新增,修改,删除操作之前或者之后自动执行, 类似于事件.
作为事务的一部分
DELIMITER $$
CREATE TRIGGER save_user_log
AFTER
INSERT
ON USER
FOR EACH ROW -- 行级触发
BEGIN
INSERT INTO save_user_log(account,oper_time)VALUES(new.account,NOW());
END$$
DELIMITER ;
INSERT INTO USER(account,PASSWORD)VALUES('aaa','1111')
DELIMITER $$
CREATE TRIGGER delete_user_role
BEFORE
DELETE
ON USER
FOR EACH ROW -- 行级触发
BEGIN
DELETE FROM user_role WHERE userid = old.id;
END$$
DELIMITER ;
DELETE FROM USER WHERE id = 14;MySQL架构
连接层:
MySQL的最上层是一些客户端的连接服务,包含了本地的sock通信和大多数基于客户端的通信,如tcp/ip. 主要负责一些连接处理,授权认证,及相关的安全方案.
服务层:
服务层为MySQL完成大多数的核心功能,如SQL接口,并完成缓存查询,SQL的分析和优化及部分内部函数的执行.
引擎层:
存储引擎层,存储引擎真正的负责了MySQL中数据的存储以及提取,服务器可以通过API与数据库进行通信.不同的存储引擎拥有不同的功能,我们可以根据自己的需求选取.
物理文件存储层:
数据存储层,主要是讲数据存储于裸设备的文件系统之上,并完成与数据引擎的交互.
MySQL引擎:
引擎就是实际存储数据的机制,不同的引擎机制不同(例如索引,锁...).
根据不同的需要,选择不同的引擎,使得效率最佳.
mysql常用的存储引擎:
innodb: 默认的存储引擎
支持事务,支持行锁,支持外键,支持缓存,不存储表的总行数.支持全文检索
myisam:
不支持事务,不支持行锁,只支持表锁,不支持外键,不支持缓存,存储表的总行数
索引
为什么用索引?
索引理解为书的目录,通过目录查询效率高
主键默认是添加索引的
什么是索引?
索引是帮助 MySQL 高效获取数据的数据结构。B+
主键 默认会添加索引 主键维护在一个B+数中,保存这条记录的物理地址
姓名, 类型,年龄
索引优点?
提高查询效率(有B+树排序,把索引数据加载到内存中),减少IO次数
索引使用B+树结构,是有序的,排序时比较快捷方便,减少cpu消耗
索引缺点?
索引也是需要空间来存储维护的
执行新增,修改,删除操作时,需要对索引结构进行更新
添加索引的原则?
哪些情况适合建立索引:
主键 默认添加唯一索引
作为查询条件的列
作为外键关联的列
排序的列
分组的列
哪些情况不适合建立索引?
表记录很少(例如系统参数设置表)
不作为查询条件的列
增删改较为频繁的表,最好为需要的列来添加
数据重复较高的(如性别)
order by age
索引分类
主键索引: primary key 主键列自动会添加索引
单值索引/单列索引: 一个索引对应一个列
唯一索引: 列的值是唯一性的
组合索引: 一个索引中包含多个列
组合索引的最左前缀原则
例如a,b,c3列, a,b列创建了组合索引, 在查询时,必须要满足最左侧索引原则,否则索引失效.
最左侧索引原则 在使用组合索引时,最左侧的列必须被使用到,否则索引失效
全文索引 mysql8之后innodb引擎开始支持全文索引
使用全文索引可以代替 like 实现模糊查询, 索引不会失效
mysql索引的数据结构:
mysql使用B+树结构存储索引
B+树是有序的,
每个节点可以存储多个数据(横向扩展)
非叶子节点不存储数据,只存储索引,一个节点中就可以存储多个索引
所有的数据存储在叶子节点中,每个叶子节点之间有指针指向.
有序节省排序时间, 一个节点存储多个元素,树高度降低了,叶子节点之间有指针,便于区间范围查询 id>10 age >10 age<40
