MySQL 存储引擎(第七讲)

一.连接层

    这是第一层，可以通过各种语言去连接MySQL，如上图最上面。

二.服务层

    MySQL的核心都在第二层服务层，MySQL的查询解析，SQL执行计划分析，SQL执行计划优化，查询缓存。以及跨存储引擎的功能都在这一层实现：存储过程，触发器，视图等。通过下图来观察服务层的内部结构

连接管理器

    连接管理器的作用是负责管理和维持所以客户端对MySQL的连接，当我们向MySQL发起连接请求时，连接管理器会负责创建并校验用户权限。
    对于已经建立的连接，但太久没发送请求的将自动断开连接。我们可以通过设置配置文件里的wait_timeout决定多久断开不活跃的连接。

查询缓存

    当我们与连接管理器建立连接后，如果我们执行的是select语句，那么连接器会先从查询缓存中进行查询，看看之前是否执行过这条语句，如果没有就往下走，如果有则判断相应的权限，权限符合，直接返回结果 。

    查询缓存其实是吧我们的查询语句当作一个key，查询结果当作一个value，形成的key:value结构

    但是在我们的表数据发生改变的时候，往往查询缓存会失效，所以一般建议不要使用查询缓存

    可能MySQL官方团队也意识到查询缓存的作用不大，在MySQL 8.0版本中已经将查询缓存的整块功能删掉了，所以如果你用的是MySQL 8.0的版本，查询缓存的功能就不存在了。

解析器

    当查询缓存中没有命令查询时，则需要真正的执行语句，这时候就先由解析器进行词法分析，对我们输入的语句先进行拆解，拆解后再进行语法分析，看我们输入的SQL语句是否符合MySQL的语法规则，如果不符合则报出相应的报错。

查询优化器

    当我们的SQL语句经过了解析器的词法和语法分析后，MySQL服务器已经知道了我们想要查询什么，不过在查询之前还要经过查询优化器进行优化。

    在优化过程中，查询优化器会选择使用哪一个索引，如果有连接(join)，会决定表的查询顺序，最终将优化执行计划交由下面的执行器去执行。

执行器

    SQL语句经过优化器优化后，就交由执行器开始执行，不过执行器在执行之前会先确认一下该用户对应的权限。

    如果用户有权限，就开始调用数据。与其他数据库不同，MySQL的的存储发送相应的指令，存储引擎负责具体执行，并将执行结果告诉执行器，然后再返回给客户端。

存储引擎层

    负责MySQL中数据的存储与提取。 服务器中的查询执行引擎通过API与存储引擎进行通信，通过接口屏蔽了不同存储引擎之间的差异。MySQL采用插件式的存储引擎。MySQL为我们提供了许多存储引擎，每种存储引擎有不同的特点。我们可以根据不同的业务特点

• 查看存储引擎

show engines;

三.MySQL存储引擎

一.MyISAM

• MySQL5.5之前的版本默认存储引擎
• MyISAM存储引擎表由MYD(数据文件)和MYI(索引文件)组成

MyISAM存储引擎的特性

1. 并发性与锁级别
2. 表损坏修复
3. 支持数据压缩

myisampack -b -f myIsam.MYI

MyISAM存储引擎限制

• 版本 < MySQL5.0时默认表大小为4G,如存储大表则要修改MAX_Rows和AVG_ROW_LENGTH
• 版本 > MySQL5.0时默认支持256TB

适用场景

• 非事务型应用
• 只读类型应用

二.什么是锁

• 锁主要作用是管理共享资源的并发访问
• 锁用于实现事务的隔离性

锁的类型

• 共享锁(也称读锁),针对同一份数据,多个读操作可以同时进行而不会互相影响
• 独占锁(也称写锁),当前写操作没有完成前,它会阻断其他写锁和读锁

锁的粒度

• 表级锁
• 行级锁

三.InnoDB

• MySQL5.5 及之后版本默认存储引擎,支持事务的ACID特性
• InnoDB使用表空间进行储存

innodb_file_per_table

ON:独立表空间,tablename.ibd
OFF:系统表空间:ibdataX X是一个数字

系统表空间和独立表空间怎么选择

    系统表空间刷新数据是按照顺序进行，所以会产生i/o瓶颈
    独立表空间可以同时向多个文件刷新数据

InnoDB特性

• 支持事务ACID特性
• 支持行级锁，可以进行最大程度的并发

MyISAM与InnoDB对比

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整张表，不适合并发	行锁，操作时只会锁住一行记录。适合高并发
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还缓存真实数据，对内存要求较高，内存大小影响性能
表空间	小	大
关注点	性能	事务
默认安装	是	是

四.CSV

文件系统储存特点：

1. 数据以文本方式存储在文件中
2. .CSV文件存储内容
3. .CSM文件存储表的元数据如表状态和数据量
4. .FRM文件存储表的结构信息

特点

• 以CSV格式存储

适用场景

• 作为数据交换的中间表

五Memory

    也称HEAP存储引擎,所以数据保存在内存中,如果MySQL服务重启数据会丢失,但是表结构会保存下来

功能特点

• 支持HASH索引和BTree索引
• 所有字段都为固定长度 varchar(10)=char(10)
• 不支持BLOB、TEXT等大字段
• Memory存储引擎使用表级锁

六.如何选择存储引擎

    大部分的情况下InnoDB都是正确的选择，除非要用到InnoDB一些不具备的特性，并且没有任何方法进行代替，否则都应该优先选择InnoDB。

参考条件

事务	如果应用需要事务支持，那么InnoDB(或者XtraDB)是目前最稳定并且经过验证的选择
备份	如果可以定期地关闭服务器来执行备份，那么备份的因素可以忽略。反之，如果需要在线热备份，那么选择InnoDB就是基本的要求
崩溃恢复	MyISAM崩溃后发生损坏的概率比InnoDB要高很多，而且恢复速度也要慢

应用举例

• 日志型应用
• 只读或者大部分情况下只读的表
• 订单处理