MySQL架构介绍

数据库和数据库实例？

数据库是用来存储数据的，数据库实例是用来操作数据的，从操作系统的角度，数据库实例表现为一个进程，对应多个线程，

在非集群数据库架构中，数据库与数据库实例存在一 一对应关系，在数据库集群中，可能存在多个数据库实例操作一个数据库情况，即多对一关系。

一、MySQL基础架构

MySQL 由连接池、SQL 接口、解析器、优化器、缓存、存储引擎等组成，可以分为三层，即MySQL Server 层、存储引擎层和文件系统层。MySQL Server 层又包括连接层和 SQL 层。如下是官方文档中 MySQL 的基础架构图：

上图中，Connection pool  为连接层，Management Services & Utilities ...Caches & Buffers 为 SQL 层，Pluggable Storage Engines 为存储引擎层，File system、Files & Logs 为文件系统层。

Co​nnectors 不属于以上任何一层，可以将 Co​nnectors 理解为各种客户端、应用服务，主要指的是不同语言与 SQL 的交互。

连接层

应用程序通过接口（如 ODBC、JDBC）来连接 MySQL，最先连接处理的是连接层。连接层包括通信协议、线程处理、用户名密码认证 3 部分。

• 通信协议负责检测客户端版本是否兼容 MySQL 服务端。
• 线程处理是指每一个连接请求都会分配一个对应的线程，相当于一条 SQL 对应一个线程，一个线程对应一个逻辑 CPU，在多个逻辑 CPU 之间进行切换。
• 密码认证用来验证用户创建的账号、密码，以及 host 主机授权是否可以连接到 MySQL 服务器。

Connection Pool（连接池）属于连接层。由于每次建立连接都需要消耗很多时间，连接池的作用就是将用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求缓存下来，下次可以直接用已经建立好的连接，提升服务器性能。

SQL层

SQL 层是 MySQL 的核心，MySQL 的核心服务都是在这层实现的。主要包含权限判断、查询缓存、解析器、预处理、查询优化器、缓存和执行计划。

• 权限判断可以审核用户有没有访问某个库、某个表，或者表里某行数据的权限。
• 查询缓存通过 Query Cache 进行操作，如果数据在 Query Cache 中，则直接返回结果给客户端，不必再进行查询解析、优化和执行等过程。
• 查询解析器针对 SQL 语句进行解析，判断语法是否正确。
• 预处理器对解析器无法解析的语义进行处理。
• 查询优化器对 SQL 进行改写和相应的优化，并生成最优的执行计划，就可以调用程序的 API 接口，通过存储引擎层访问数据。

Management Services & Utilities、SQL Interface、Parser、Optimizer 和 Caches & Buffers 属于 SQL 层，详细说明如下表所示：

名称	说明
Management Services & Utilities	MySQL 的系统管理和控制工具，包括备份恢复、MySQL 复制、集群等。
SQL Interface（SQL 接口）	用来接收用户的 SQL 命令，返回用户需要查询的结果。例如 SELECT FROM 就是调用 SQL Interface。
Parser（查询解析器）	在 SQL 命令传递到解析器的时候会被解析器验证和解析，以便 MySQL 优化器可以识别的数据结构或返回 SQL 语句的错误。
Optimizer（查询优化器）	SQL 语句在查询之前会使用查询优化器对查询进行优化，同时验证用户是否有权限进行查询，缓存中是否有可用的最新数据。它使用“选取-投影-连接”策略进行查询。 例如 SELECT id, name FROM student WHERE gender = "女";语句中，SELECT 查询先根据 WHERE 语句进行选取，而不是将表全部查询出来以后再进行 gender 过滤。SELECT 查询先根据 id 和 name 进行属性投影，而不是将属性全部取出以后再进行过滤，将这两个查询条件连接起来生成最终查询结果。
Caches & Buffers（查询缓存）	如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的，比如表缓存、记录缓存、key 缓存、权限缓存等。

存储引擎层

Pluggable Storage Engines 属于存储引擎层。存储引擎层是 MySQL 数据库区别于其他数据库最核心的一点，也是 MySQL 最具特色的一个地方。主要负责 MySQL 中数据的存储和提取。

因为在关系数据库中，数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。

文件系统层

文件系统层主要是将数据库的数据存储在操作系统的文件系统之上，并完成与存储引擎的交互。

二、MySQL的工作流程

① 客户端连接上 mysql 数据库的连接器，连接器获取权限，维持管理连接；连接完成后如果你没有后续的指令这个连接就会处于空闲状态，如果太长时间不使用这个连接这个连接就会断开，这个空闲时长默认是 8 小时，由 wait_timeout 参数控制。

② 往 mysql 数据库发送了一条 sql ，这个时候查询缓存开始工作，看看之前有没有执行过这个 sql ，如果有则直接返回缓存数据到客户端，只要对表执行过更新操作缓存都会失效，因此一些很少更新的数据表可考虑使用数据库缓存，对频繁更新的表使用缓存反而弊大于利。使用缓存的方法如以下 sql ，通过 SQL_CACHE 来指定：

select SQL_CACHE * from table where xxx=xxx

③ 当未命中缓存的时候，分析器开始工作；分析器判断你是 select 还是 update 还是 insert ，分析你的语法是否正确。

④ 优化器根据你的表的索引和 sql 语句决定用哪个索引，决定 join 的顺序。

⑤ 执行器执行 sql ，调用存储引擎的接口，扫描遍历表或者插入更新数据。

三、存储引擎层

存储引擎类型有哪些

1、InnoDB存储引擎

从MySQL5.5版本之后，MySQL的默认内置存储引擎已经是InnoDB了，他的主要特点有：

① 灾难恢复性比较好；
② 支持事务。默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。
③ 使用的锁粒度为行级锁，可以支持更高的并发；
④ 支持外键；
⑤ 配合一些热备工具可以支持在线热备份；
⑥ 在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
⑦ 对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；

2、MyISAM存储引擎

在5.5版本之前，MyISAM是MySQL的默认存储引擎，该存储引擎并发性差，不支持事务，所以使用场景比较少，主要特点为：

① 不支持事务；
② 不支持外键，如果强行增加外键，不会提示错误，只是外键不其作用；
③ 对数据的查询缓存只会缓存索引，不会像InnoDB一样缓存数据，而且是利用操作系统本身的缓存；
④ 默认的锁粒度为表级锁，所以并发度很差，加锁快，锁冲突较少，所以不太容易发生死锁；
⑤ 支持全文索引（MySQL5.6之后，InnoDB存储引擎也对全文索引做了支持），但是MySQL的全文索引基本不会使用，对于全文索引，现在有其他成熟的解决方案，比如：ElasticSearch，Solr，Sphinx等。
⑥ 数据库所在主机如果宕机，MyISAM的数据文件容易损坏，而且难恢复；

3、MEMORY存储引擎

将数据存在内存中，和市场上的Redis，memcached等思想类似，为了提高数据的访问速度，主要特点：

① 支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型；
② 支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；
③ 由于数据是存放在内存中，所以在服务器重启之后，所有数据都会丢失；
④ 查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低；

4、ARCHIVE存储引擎

ARCHIVE存储引擎适合的场景有限，由于其支持压缩，故主要是用来做日志，流水等数据的归档，主要特点：

① 支持Zlib压缩，数据在插入表之前，会先被压缩；
② 仅支持SELECT和INSERT操作，存入的数据就只能查询，不能做修改和删除；
③ 只支持自增键上的索引，不支持其他索引；

5、CSV存储引擎

数据中转试用，主要特点：

① 其数据格式为.csv格式的文本，可以直接编辑保存；
② 导入导出比较方便，可以将某个表中的数据直接导出为csv，试用Excel办公软件打开；

可以使用以下语句查询MySQL服务器程序支持的引擎：

SHOW ENGINES;

InnoDB和MyISAM的对比

1. 由于锁粒度的不同，InnoDB比MyISAM支持更高的并发；
2. InnoDB为行级锁，MyISAM为表级锁，所以InnoDB相对于MyISAM来说，更容易发生死锁，锁冲突的概率更大，而且上锁的开销也更大，因为需要为每一行加锁；
3. 在备份容灾上，InnoDB支持在线热备，有很成熟的在线热备解决方案；
4. 查询性能上，MyISAM的查询效率高于InnoDB，因为InnoDB在查询过程中，是需要维护数据缓存，而且查询过程是先定位到行所在的数据块，然后在从数据块中定位到要查找的行；而MyISAM可以直接定位到数据所在的内存地址，可以直接找到数据；
5. SELECT COUNT(*)语句，如果行数在千万级别以上，MyISAM可以快速查出，而InnoDB查询的特别慢，因为MyISAM将行数单独存储了，而InnoDB需要逐行去统计行数；所以如果使用InnoDB，而且需要查询行数，则需要对行数进行特殊处理，如：离线查询并缓存；
6. MyISAM的表结构文件包括：.frm(表结构定义),.MYI(索引),.MYD(数据)；而InnoDB的表数据文件为：.ibd和.frm(表结构定义)；

如何选择合适的存储引擎

1. 使用场景是否需要事务支持；
2. 是否需要支持高并发，InnoDB的并发度远高于MyISAM；
3. 是否需要支持外键；
4. 是否需要支持在线热备；
5. 高效缓冲数据，InnoDB对数据和索引都做了缓冲，而MyISAM只缓冲了索引；
6. 索引，不同存储引擎的索引并不太一样；

四、参考

https://segmentfault.com/a/1190000019400925

http://c.biancheng.net/view/7939.html