视图
查询的结果集,select语句执行后返回的结果集.
查询的表叫基表
查询的结果集称为虚拟表
是对复杂的查询语句进行封装,对视图进行查询,达到简化查询语句
Create view 视图名 as select........
drop view 视图名
存储过程
把编写在数据库中的sql语句集称为存储过程(plsql); 一段sql集合 提高数据处理效率
将一些逻辑代码事先存储在数据库中,然后使用时直接调即可.可以减少应用程序与数据库之间的交互次数
eg:当你需要把账号存到数据库中时,有一段逻辑 账号存在不做任何操作,账号不存在则要存到数据库中.如果使用java写逻辑判断需要调两次数据库第一次查询是否存在 不存在的话 再调 而你使用存储过程就可以直接调用一次数据库 逻辑都写在存储过程 ------->好处 减少了数据库压力 提高了效率
IN OUT INOUT
存储过程实现需要创建好(包括逻辑) 可以向存储过程中传入参数
语法格式:create procedure 存储过程名 ([in 变量名 类型, out 参数........])
begin
[declare 变量名 类型 [DEFAULT 值];]
存储过程语句块;
end;
调用:
call 存储过程名;
函数(定义)
create function 函数名([参数列表]) returns 数据类型 begin DECLARE 变量； sql 语句; return 值; end;
触发器
CREATE TRIGGER 触发器名称 触发时机 触发事件 ON 表名称 FOR EACH ROW -- 行级触发 BEGIN 语句 END;
MySql架构
-
连接层
连接服务 socket通信 tcp/ip(负责接收客户端连接请求,可以进行认证)
-
服务层
接收sql语言解析,优化,缓存
-
引擎层
本层真正落地实现的方式,不同的存储引擎特点不同
-
物理文件存储层
使用各种文件存储数据以及日志文件
查看支持的引擎:show engines
查看表引擎:show table status like'表名'
修改引擎
| InnoDB(事物) | MyISAM(性能) | |
|---|---|---|
| 事物 | √ | × |
| 行级锁(只锁定某一行) | √(可以表锁 但是要改配置) | ×(支持 表锁) |
| 外键约束 | √ | × |
| 缓存 | √ | (只缓存索引不缓存真实数据) |
| 全文索引 | √ | √ |
| 表的总行数 | × | √ |
| 索引设计 | 聚簇索引 | 非聚簇索引 |
索引
类似于书的目录,可以通过目录快速定位到数据的真实位置
数据存储在数据页 一页是16kb
排好序的快速查询的数据结构(B+树)
维护一个树形结构 在树形结构里面存放数据的id和物理地址 (简化搜索效率 降低排序成本 劣势:占空间 增删改还需要额外维护索引树 消耗时间)
主键索引:设定主键后数据库会自动的建立索引(drop primary key)
单值索引:一个索引只包含一个列,一个表可以有多个单列索引
唯一索引:保证列数据不重复,可以为null
组合索引(复合索引):即一个索引包含多个列组合索引最左前缀原则:在使用组合索引的列作为条件时,必须要出现最左侧列为条件,否则不生效
全文索引:模糊查询会导致索引失效 FULLTEXT INDEX
match() against() //会使用全文索引
查看索引:show index from 表名;
索引创建原则(背背背)
索引数据结构
B+树(二叉搜索树,平衡二叉树,平衡多路搜索树(B树))由上面这些优化而来.
(InnoDB存储就是用B+树实现其索引结构)
B树:自平衡多路树
B+树:排好序的 非叶子节点放索引 叶子节点放数据 所有的叶子节点之间都有一个链指针 优点:一个节点可以存多个指针 高度不高 并且叶子节点有指针,可以很好的支持全盘扫描,范围查找
聚簇索引和非聚簇索引
从结构来看的区别
聚簇索引:找到了索引就找到了数据,所以主键就是聚簇索引
非聚簇索引:索引的存储和数据的存储是分离的,找到了索引但是没有找到数据,需要根据索引上的值再次回表查询,也叫作辅助索引
InnoDB是聚簇索引(一个文件) MyISAM是非聚簇索引(两个文件 索引文件和数据文件)
事务
数据库事物:原子性 一致性 隔离性 持久性
概念:数据库为了保证数据操作的原子性,隔离性,持久性,一致性,数据库提供一套机制,在同一事物中,有多条sql执行,事务确保执行的可靠性
事务特性
原子性:要么全部完成 要么全部不完成
一致性:事务结束后,对数据的修改是永久的,即便系统故障也不会丢失
隔离性:数据库允许并发事务 隔离性可以防止多个事务交叉执行从而导致数据不一致,隔离的级别是:读未提交 读提交 可重复读 串行化
持久性:事务开始和结束后,写入数据必须符合预设规则(前三个性质都是为了保证这个性质)
事务隔离级别
select @@global.transaction,@@transaction_isolation
串行化:加锁,只要有一个事务进行操作,其他事务都要等待,即使是查询操作
读提交:不可重复读(B事务在开启后的两次查询中,两次查询结果不一样)
事务实现原理(ACID)
InnoDB提供了两种事务日志:redolog(重做日志) 用于保证事务的持久性
undolog(回滚日志) 保证事务的原子性和隔离性
原子性实现:undo log
如果事务执行失败或调用了rollback,导致事务回滚,便可以利用undolog日志修改回修改前的样子;和sql语句做相反的操作
持久性实现:redo log
每次执行修改操作语句,先会将语句保存到redo log中,即使停电,正常后也可以从日志中恢复数据
隔离级别实现原理(MVCC)
MVCC:多版本并发控制 Multi-Version Concurrent Control = 保证读写可以并发操作保证了效率(配合Undo log 和 版本链)
主要针对:读已提交 可重复读
读已提交:只要别的事务提交了 那么另一个事务就可以看到,有可能同一个事务两次查询数据不一致,实时访问到的是最新的数据 当前读 每次读是都会给版本链拍照
可重复读:事务开始后,第一次读到的数据和之后读到的数据一致 快照读:第一次读的时候 会把版本链拍照 下次读的时候 从版本快照中读 所以两次读的一致
(TRX_ID事务ID ROLL_PT回滚指针)
ReadView
快照读
锁 (数据库)
行锁 间隙锁 表锁
行锁:是锁定粒度最细的一种锁,只表示对当前行进行加锁,行锁能大大减少数据库冲突,但是开销也大,行锁分为共享锁(S允许其他事务获取数据)和 独享锁(X排他锁 不允许)
间隙锁:锁的是一个区间而不是相等的条件检索数据,InnoDB会给符合条件的索引加锁(Next-key锁)
表锁:锁定粒度最大的一种锁,资源消耗少,也分为共享锁和独享锁
共享锁:读锁 如果我们事务在读数据时 不想其他事务写时又想读可以加 lock in share mode
排它锁: (独占锁)写操作默认加独占锁 读数据想足够准确加for update
sql优化
为什么要sql优化
当用户量增大时,sql的执行效率对程序的运行效率的影响逐渐增大
sql优化的一些方法
-
查询尽量不要使用select *
-
避免在where字句中使用or来连接条件
-
使用数值代替字符串类型
-
使用varchar代替char
-
查询优化 首先考虑where及order by 涉及的列建立索引
-
尽量避免索引失效(null or in not in 模糊查询 函数)
-
优先使用 inner join
-
先过滤后分组
-
清空表 truncate
-
索引不要太多(不超过5个)
-
不要在索引列使用内置函数
-
使用explain分析你的sql执行计划
EXPLAIN
输出执行过程
id值越大优先值越高
possible_keys 可能使用到的索引
keys 实际使用的索引
select_type
type
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
