mysql 进阶深入

视图

查询的结果集,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