6、mysql逻辑架构

MySQL架构图

支持接口(Connectors)

​ 连接器,指的是不同语言中与SQL的交互

企业管理服务和工具(Management Serveices & Utilities)

​ 系统管理和控制工具

连接池(Connection Pool)

​ 管理缓冲用户连接，线程处理等需要缓存的需求。 负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。 而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。

SQL接口(SQL Interface)

​ 接受用户的SQL命令，并且返回用户需要查询的结果。比如select XX from就是调用SQL Interface

解析器(Parser)

​ SQL命令传递到解析器的时候会被解析器验证和解析。 主要功能：

• 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。

• 如果在分解过程中遇到错误，那么就说明这个sql语句是不合理的。

查询优化器(Optimizer)

​ SQL语句在查询之前会使用查询优化器对查询进行优化。explain语句查看的SQL语句执行计划，就是由查询优化器生成的。

​ 优化：

• 索引只能使用一个，使用最优的一个索引

• 多表关联：小表驱动大表

• where 从左到右，先执行查找力度最大的条件

  where sex = ‘1’ and id = 1 经过优化器之后变成 where id = 1 and sex = ‘1’

缓存和缓存池(Cache和Buffer)

​ 它的主要功能是将客户端提交给MySQL的 select 请求的返回结果集 cache 到内存中，与该 Query 的一个 hash 值做一个对应。该 Query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 Query 的Cache 失效。在读写比例非常高的应用系统中，查询缓存对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。

​ 如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。

存储引擎(Pluggable Storage Engines)

​ 与其他数据库例如Oracle 和SQL Server等数据库中只有一种存储引擎不同的是，MySQL有一个被称为**“Pluggable Storage Engine Architecture”(可插拔的存储引擎架构)的特性，也就意味着MySQL数据库提供了多种存储引擎。而且存储引擎是针对表**的，用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。也就是说，同一数据库不同的表可以选择不同的存储引擎。

create table xxx() engine=InnoDB/Memory/MyISAM

​ 简而言之，存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。

• MySQL存储引擎种类

MySQL的存储引擎是针对表进行指定的。（engine=InnoDB\MyISAM）

存储引擎	说明
MyISAM	高速引擎，拥有较高的插入，查询速度，但不支持事务、不支持行锁、支持3种 不同的存储格式。包括静态型、动态型和压缩型。
InnoDB	5.5版本后MySQL的默认数据库，支持事务和行级锁定，事务处理、回滚、崩溃 修复能力和多版本并发控制的事务安全，比MyISAM处理速度稍慢、支持外键 （FOREIGN KEY）
ISAM	MyISAM的前身，MySQL5.0以后不再默认安装
MRG_MyISAM（MERGE）	将多个表联合成一个表使用，在超大规模数据存储时很有用
Memory	内存存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。只在内存上保存数据，意味着数据可能会丢失
Falcon	一种新的存储引擎，支持事物处理，传言可能是InnoDB的替代者
Archive	将数据压缩后进行存储，非常适合存储大量的独立的，作为历史记录的数据，但 是只能进行插入和查询操作
CSV	CSV 存储引擎是基于 CSV 格式文件存储数据(应用于跨平台的数据交换)

• 查看存储引擎：

mysql> show engines;

• InnoDB和MyISAM存储引擎区别：

	Innodb	MyISAM
存储文件	.frm 表定义文件 .ibd 数据文件和索引文件	.frm表定义文件 .myd数据文件 .myi索引文件
锁	表锁、行锁	表锁
事务	支持	不支持
CRDU	读、写	读多
count	扫表	专门存储的地方(加where也扫表)
索引结构	B+ Tree	B+ Tree
外键	支持	不支持

• 存储引擎的选型

  • InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银 行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库， 也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

  • MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

  • MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，不需要持久保存，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

  • 注意，同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择 MEMORY 存储引擎。