MySQL - 存储引擎

一条SQL语句的执行顺序

mysql的体系结构

整个mysql server由下列组件组成:
1. connection pool : 连接池组件
2. management services & utilities: 管理服务和工具组件
3. sql interface : SQL接口组件
4. parser: 查询分析器
5. optimizer: 优化器
6. caches & buffers : 缓冲池组件
7. pluggable stroage engines: 存储引擎
8. file system : 文件系统
9. files & logs : 日志系统

1. 连接层 connectors & connection pool:
   是一些客户端连接服务 主要完成一些类似于连接处理, 授权认证 及相关安全方案,
   在该层引入线程池,为通过认证安全接入的客户端提供线程.

2. 服务层:
   主要完成大多数核心服务功能. 比如 SQL接口,完成缓存查询.SQL分析和优化.

3. 引擎层
   负责MYSQL中数据的存储和读取服务

4. 存储层
   主要讲数据存储在文件系统上,并完成与存储引擎的交互.

重要组件解释

查询缓存组件:
查询缓存主要用来缓存我们所执行的select语句以及该语句的结果集.
执行查询语句时,先查询缓存,MYSQL会先校验这个SQL是否执行过, 以key-value的形式缓存在内存中, key为SQL语句, value为查询出来的结果集.
查询时,如果缓存key被命中,则把value返回给客户端,如果没有命中,则执行后续操作后,把结果集缓存起来.
MYSQL不建议使用缓存, 因为如果表数据被频繁更新,那么表的所有查询缓存都会被清空,这样延长了查询时间,不利于业务.

分析器:
没有命中缓存的话,SQL语句就会经过分析器,分析器主要用来分析SQL语句是干嘛的

    第一步: 词法分析,一条SQL语句有多个字符串组成,首先要提取关键字,,比如select,提出查询的表.字段名,查询条件
    第二步: 语法分析,主要判断输入的SQL语句是否正确,是否符合MYSQL语法.

优化器:
按照MYSQL自认为的最优方案执行SQL语句(并不一定是最优)

执行器:
当选择了执行方案后,MYSQL就准备开始执行了, 首先执行前会校验用户是否有权限,如果没有则返回错误信息,如果有则调用引擎接口,返回接口执行的结果.

语句分析

SQL分为两种:
查询
更新(增加,修改,删除)

查询分析

select * from student  where age = 18 and name = "张三";

执行流程
1. 检查该语句是否有权限,如果没有则抛出错误信息
2. 查询缓存,如果有则直接返回结果.(版本8.0以前)
3. 分析器进行词法分析,提取SQL语句关键元素
4. 优化器确定执行方案.
根据上述的SQL语句,优化器会确定两种方案:
1.先查询满足age为18的所有记录,然后在查询的结果集中再次筛选 name 为张三的结果集.
2.先查询满足name为张三的所有记录,然后在查询的结果集中再次筛选age为18的结果集
5. 权限校验,如果有则调用存储引擎接口,返回引擎执行的结果

权限校验—> 查询缓存—> 分析器—> 优化器—> 权限校验—> 执行器—> 引擎

更新分析

insert into student values (null,"李四",20);

执行流程
1. 检查该语句是否有权限,如果没有则抛出错误信息
2. 查询缓存,如果有则直接返回结果.(版本8.0以前)
3. 分析器进行词法分析,提取SQL语句关键元素
4. 优化器确定执行方案.
5. 权限校验,如果有则调用存储引擎接口,存储引擎把数据保存在内存中,同时记录redo log(重做日志)并预提交,然后通知执行器 随时可以提交
6. 执行器接收到通知后记录binlog(归档日志),然后调用存储引擎 提交redo log并返回结果给客户端

分析器----> 权限校验----> 执行器—> 引擎—redo log 预提交—> binlog—> redo log 提交

redo log 日志用来支持事务
bin log 日志 用来在MYSQL数据库崩溃后,重洗数据

二. 存储引擎

MYSQL提供了插件式(可插拔) 多个的存储引擎.(InnoDB,MyIsAM,Memory,DBD…)
ORACEL和 SQL SERVER 只有一种引擎

存储引擎的作用
存储数据,建立索引,更新查询数据等技术的实现方式,存储引擎是基于表的,而不是基于库.所以存储引擎也称之为表类型.

查询当前数据库支持的存储引擎

show engines;

查询MYSQL数据库默认的存储引擎

show variables like '%storage_engine%';

查看表的存储引擎

show table status like "表名";

存储引擎的特性

特点 InnoDB MyISAM MEMORY MERGE NDB
存储限制 64TB 有 有 没有 有
事务安全 支持
支持外键 支持
锁机制 行锁(适合高并发) 表锁 表锁 表锁 行锁
B树索引 支持 支持 支持 支持 支持
哈希索引 支持
全文索引 支持(5.6版本之后) 支持
集群索引 支持
内存使用 高 低 中等 低 高
批量插入速度 低 高 高 高 高

三. InnoDB和MyIsAM的区别

区别一 :版本支持
InnoDB存储引擎是MYSQL 5.5以后默认的存储引擎, 5.5之前的版本默认的是MyIsAM存储引擎

区别二 : 事务控制

需求: 在InnoDB类型的表和MyIsAM类型的表中分别实现增加记录

因为mysql默认自动提交事务,所以我们如果要演示事务控制,那么需要临时开启手动事务
start transaction;

启动两个MYSQL客户端
A端在InnoDB引擎的表中 insert into 但不commit;
B端去查询数据, 是查询不到的, 因为A端没有提交事务.
这也就证明了, InnoDB是存在事务控制的.

A端在MyIsAM引擎的表中 insert into 但不commit;
B端去查询数据, 是可以查询到的, 因为MyIsAM引擎不支持事务

区别三: 支持外键

如果表有外键关联,那么在删除主表时,是不被允许的

是因为表类型为InnoDB ,他是支持外键约束的

MyIsAM存储引擎根本设置不了外键.因为不支持.

区别四: 锁级别

InnoDB 支持行级锁(默认) ,也支持表锁
MyIsAM支持表锁

区别五: MVCC

InnoDB支持MVCC,应对高并发事务. MVCC比单纯的加锁更高效.
MyIsAM不支持