MySQL 存储引擎

MySQL 存储引擎

用了那么久的Mysql，还没好好学习过MySQL架构，零碎的学习了引擎的东西。但是不成体系，所以学习的时候也整体一下，主要是自己对知识补充以及归纳整理。

一、MySQL架构

1、逻辑架构图

2、分层介绍

（1）连接层。

最上面的一层，负责客户端连接服务，包含本地socket通信和基于C/S 工具实现的通信接口。

主要完成一些类似于连接处理、授权认真、及相关的安全方案。

该层引入了线程池的概念，为通过安全认证接入的客户端提供线程服务。

（2）服务层。

服务层是MySQL服务端的核心，功能包括查询解析、分析、优化、缓存等，存储过程、触发器、视图等都在这一层实现；

主要组件有：

• Management Serveices & Utilities：系统管理和控制工具。

• Connection Pool 连接池：管理缓冲用户连接，线程处理等需要缓存的需求。

• SQL Interface SQL接口： 接受用户的SQL命令，并且返回用户需要的查询结果。select from就是调用SQL Interface。

• Parser 解析器 ：SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。SQL命令传递到解析器的时候会被解析器进行验证解析。

  • （1）词法分析。扫描字符流，根据构词规则识别单个单词。mysql使用Flex来生成词法扫描程序。 在sql/lex.h中定义了MySQL关键字和函数关键字，用两个数组存储。
  • （2）语法分析。在词法分析的基础上将单词序列组成语法短语，最后生成语法树，提交给优化器。语法分析器使用Bison，在sql/sql_yacc.yy中定义了语法规则。根据关系代数理论生成语法树
  • 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的。

• Optimizer 查询优化器：SQL语言在查询之前会使用查询优化器对查询进行优化。常见两种优化策略：CBO，基于成本的优化；RBO，基于规则的优化。比如有where 条件，优化器决定是先定投影还是先过滤。

  “选取-投影-联接”策略进行查询。
         用一个例子就可以理解： select id,user_name from user where sex = 1;
         这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行sex过滤
         这个select查询先根据id和user_name进行属性投影，而不是将属性全部取出以后再进行过滤
         将这两个查询条件联接起来生成最终查询结果

• Executor 执行器：与存储引擎交互，调用储存引擎API，执行SQL，获得结果。

• Cache和Buffer 查询缓存：将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值 做 一个对应。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存、记录缓存、key缓存、权限缓存。

（3）存储引擎

存储引擎主要负责数据存储和提取，服务器通过API与存储引擎进行通信。存储引擎不会去解析SQL，不同存储引擎之间不会通讯，只会简单地响应上层服务器的请求。

（4）存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

二、存储引擎

1、什么是存储引擎

存储引擎就是数据的存储结构，针对不同数据文件使用不同形式的组织结构。

2、存储引擎有哪些

（1）MyISAM存储引擎
在MySQL5.1之前版本，MyISAM是默认的存储引擎。

MyISAM提供了全文索引、压缩、空间函数等，MyISAM是不支持事务和行级锁的，MyISAM会将表存储在两个文件中，数据文件和索引文件，拓展名分别是.MYD和.MYI。

MyISAM虽然没有行级锁，不过是可以支持表锁的，所以在一些业务系统还是可以做到并发控制的

（2）InnoDB存储引擎
InnoDB存储引擎是mysql默认的存储引擎，InnoDB是支持事务的存储引擎。

InnoDB是mysql 5.1才开始出现的，之前名称是称为InnoDB plugin，直到被oracle收购mysql后，才正式命名为InnoDB。

InnoDB采用MVCC来支持事务一致性和并发，并且实现了4个基本的隔离级别，默认级别是PREATABLE READ(可重复读)，并且通过间隙锁(next-key loking)策略来防止欢读的出现，

（3）Archive存储引擎
Archive存储引擎只支持INSERT和SELECT操作，在Mysql5.1版本之前不支持索引的，Archive引擎会缓存所有的写数据，利用zlib对写入的数据进行压缩，所以需要的磁盘/IO比MyISAM更少的，但是SELECT是全表扫描的，因此Archive表适合用于日志和数据采集类的应用，Archive引擎是支持行级锁的

（3）Blackhole存储引擎

Blackhole引擎没有存储机制，不做保存处理，但是服务器还是会记录Blackhole表的日志，所以可以用于复制数据到备库，或者用于记录日志

（4）CSV存储引擎
CSV引擎可以将普通的CSV文件作为MySql的表处理，可以在数据库运行时拷贝文件，将Excel表另存为CSV文件，然后就可以直接复制到数据库，不过这种表是不支持索引的

（5）Federated存储引擎
Federated引擎是访问其它服务器的代理，会 创 建 一 个 到 远 程 MySQL 服 务 器 的 客 户 端 连 接， 并 将 查 询 传 输 到 远 程 服 务 器 执 行， 然 后 提 取 或 者 发 送 需 要 的 数 据。

（6）Memory存储引擎
Memory引擎适用与需要快速访问数据，这些数据是保存在内存的，Memory支持hash索引，所以查询速度非常快，不过不支持BLOB或者TXT类型数据，是用表锁做并发的

（7）Merge存储引擎
Merge引擎是由多个MyISAM表合并而来的表，可以用于存储日志或者用于当数据存库

（8）NDB集群存储引擎
这是Mysql推出的可以用于集群的存储引擎，提供了SQL和NDB协议之间的接口，原先是收购了NDB数据库，然后发展来的。

3、怎么查看引擎？

（1）查看MySQL支持哪些引擎

mysql> show engines;

（2）查看MySQL当前默认的存储引擎

mysql> show variables like '%storage_engine%';

3、两个流行引擎比较

MyISAM 和InnoDB

对比项	MyISAM	InnoDB
主外键	不支持主外键	支持主外键
事务	不支持事务	支持事务
行表锁	表锁，操作一条记录也锁全表	行锁，操作一行对其他行无影响。 适合高并发操作。
缓存	只缓存索引，不缓存数据	缓存索引和数据，对内存要求较高。 内存大小对性能有决定性的影响。
表空间	占用表空间小	占用表空间大
关注点	关注点在性能	关注点在事务