MySQL体系架构

1. MySQL的组成

由上图看出MySQL由上面的连接层和下面的服务器组成。服务器由连接池、管理工具和服务， SQL接口、解析器、优化器、缓存、存储引擎、文件系统组成。
连接池：由于每次建立建立需要消耗很多时间，连接池的作用就是将这些连接缓存下来，下次可以直接用已经建立好的连接，提升服务器性能。
管理工具和服务：系统管理和控制工具，例如备份恢复、Mysql 复制、集群等。
SQL接口：接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface。
解析器: SQL命令传递到解析器的时候会被解析器验证和解析。解析器主要 功能：
a . 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的
b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的
优化器：查询优化器，SQL 语句在查询之前会使用查询优化器对查询进行优化。
缓存器：查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直 接去查询缓存中取数据。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存， 权限缓存等。
存储引擎、文件系统接下来细说。

2. 连接层

2.1 服务器处理客户端请求

当MySQL启动（MySQL服务器就是一个进程），等待客户端连接，每一个 客户端连接请求，服务器都会新建一个线程处理（如果是线程池的话，则是分配一个空的线程），每个线程独立，拥有各自的内存处理空间。

连接到服务器，服务器需要对其进行验证，也就是用户名、IP、密码验证， 一旦连接成功，还要验证是否具有执行某个特定查询的权限（例如，是否允许客 户端对某个数据库某个表的某个操作）

3. Server层

这一层的功能： SQL语句的解析、优化，缓存的查询，MySQL内置函数的实现，跨存储引擎功能，如存储过程、触发器、视图等每个引擎都需要提供的功能（引擎需要对外提供接口）。

• 如果是查询语句（select 语句），首先会查询缓存是否已有相应结果，有 则返回结果，无则进行下一步（如果不是查询语句，同样调到下一步）
• 解析查询，创建一个内部数据结构（解析树），这个解析树主要用来 SQL 语句的语义与语法解析；
• 优化：优化SQL语句，例如重写查询，决定表的读取顺序，以及选择需要 的索引等。这一阶段用户是可以查询的，查询服务器优化器是如何进行优化的， 便于用户重构查询和修改相关配置，达到最优化。这一阶段还涉及到存储引擎， 优化器会询问存储引擎，比如某个操作的开销信息、是否对特定索引有查询优化 等。

3.1 缓存

缓存默认是不开启的

缓存默认值是1M

不能通过Global Set来开启缓存

query_cache_type只能配置在 my.cnf 文件中，这大大限制了query cache的作用。
在生产环境建议不开启，除非经常有sql完全一模一样的查询，例如城市列表，但是现在这种数据一般存在第三方缓存比sql查询更高效，所以有第三方缓存的时候他就显得很鸡肋了。
Query Cache严格要求2次SQL请求要完全一样，包括SQL语句，连接的数据库、协议 版本、字符集等因素都会影响
从8.0开始，MySQL不再使用查询缓存，那么放弃它的原因是什么呢？
MySQL 查询缓存是查询结果缓存。它将以SEL开头的查询与哈希表进行比较， 如果匹配，则返回上一次查询的结果。进行匹配时，查询必须逐字节匹配，例如’SELECT * FROM e1; '不等于 ‘select * from e1;’，此外，一些不确定的查询结果无法被缓存，任何对表的修改都会导致这些表的所有缓存无效。因此，适用于查询缓存的最理想的方案是只读，特别是需要检查数百万行后仅返回数行的复杂查询。 如果你的查询符合这样一个特点，开启查询缓存会提升你的查询性能。
随着技术的进步，经过时间的考验，MySQL的工程团队发现启用缓存的好处并不多。
首先，查询缓存的效果取决于缓存的命中率，只有命中缓存的查询效果才能 有改善，因此无法预测其性能。
其次，查询缓存的另一个大问题是它受到单个互斥锁的保护。在具有多个内 核的服务器上，大量查询会导致大量的互斥锁争用。
通过基准测试发现，大多数工作负载最好禁用查询缓存(5.6 的默认设置)：
按照官方所说的：造成的问题比它解决问题要多的多，弊大于利就直接砍掉了

4. 存储引擎层

MySQL 数据库区别于其他数据库的最重要的一个 特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标 准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构和实际文件读写的实现，每个存储引擎开发者可以按照自己的意愿来进行开发。需要特别注意的是，存储引擎是基于表的，而不是数据库。

插件式存储引擎的好处是，每个存储引擎都有各自的特点，能够根据具体的 应用建立不同存储引擎表。由于 MySQL 数据库的开源特性，用户可以根据 MySQL 预定义的存储引擎接口编写自己的存储引擎。若用户对某一种存储引擎的性能或功能不满意，可以通过修改源码来得到想要的特性，这就是开源带给我们的方便与力量。

由于MySQL数据库开源特性，存储引擎可以分为MySQL官方存储引擎和第三方存储引擎。有些第三方存储引擎很强大，如大名鼎鼎的 InnoDB存储引擎（最早是第三方存储引擎，后被Oracle收购)，其应用就极其广泛，甚至是 MySQL数据库 OLTP(Online Transaction Processing 在线事务处理）应用中使用最广泛的存储引擎。

4.1 MySQL官方引擎

4.1.1 InnoDB存储引擎

InnoDB 是 MySQL 的默认事务型引擎，也是最重要、使用最广泛的存储引擎。 它被设计用来处理大量的短期(short-lived)事务，短期事务大部分情况是正常提交的，很少会被回滚。InnoDB 的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑 InnoDB 引擎。如果要学习存储引擎，InnoDB 是首选。
官方引擎需要花最多的时间去深入学习的对象，收益肯定比将时间平均花在每个存储引擎的学 习上要高得多。所以 InnoDB 引擎也将是我们学习的重点。

4.1.2 Mrg_MylSAM

Merge 存储引擎，是一组 MyIsam 的组合，也就是说，他将 MyIsam 引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是 MyIsam 引擎的表 中，但是可以直接进行查询、删除更新等操作。

4.1.3 Memory引擎

如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有 关系，那么使用 Memory 表(以前也叫做 HEAP 表）是非常有用的。Memory 表至少比 MyISAM 表要快一个数量级，因为每个基于 MEMORY 存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为 frm 类型。该文件中只存 储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理， 提高整个表的效率，不需要进行磁盘 I/O。所以 Memory 表的结构在重启以后还会保留，但数据会丢失。
Memroy 表在很多场景可以发挥好的作用:

• 用于查找(lookup）或者映射(mapping）表，例如将邮编和州名映射的表。
• 用于缓存周期性聚合数据(periodically aggregated data)的结果。
• 用于保存数据分析中产生的中间数据

Memory 表支持 Hash 索引，因此查找操作非常快。虽然 Memory 表的速度非常快，但还是无法取代传统的基于磁盘的表。Memroy 表是表级锁，因此并发写入的性能较低。它不支持 BLOB 或 TEXT 类型的列，并且每行的长度是固定的， 所以即使指定了 VARCHAR 列，实际存储时也会转换成 CHAR，这可能导致部分内存的浪费。

4.1.4 Blackhole引擎

Blackhole 引擎没有实现任何的存储机制，它会丢弃所有插入的数据，不做任何保存。但是服务器会记录 Blackhole 表的日志，所以可以用于复制数据到备库，或者只是简单地记录到日志。这种特殊的存储引擎可以在一些特殊的复制架构和日志审核时发挥作用。但这种引擎在应用方式上有很多问题，因此并不推荐。

4.1.5 MylSAM存储引擎

在 MySQL 5.1及之前的版本，MyISAM 是默认的存储引擎。MyISAM 提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但 MyISAM 不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。尽管 MyISAM 引擎不支持事务、不支持崩溃后的安全恢复，但它绝不是一无是处的。对于只读的数据，或者表比较小、可以忍受修复（repair）操作，则依然可以继续使用 MyISAM （但请不要默认使用 MyISAM，而是应当默认使用 InnoDB)。但是 MyISAM 对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁,写入时则对表加排他锁。MyISAM 很容易因为表锁的问题导致典型的的性能问题。

4.1.6 CSV引擎

CSV 引擎可以将普通的 CSV 文件(逗号分割值的文件）作为 MySQL 的表来处 理，但这种表不支持索引。CSV 引擎可以在数据库运行时拷入或者拷出文件。可以将 Excel 等的数据存储为 CSV 文件，然后复制到 MySQL 数据目录下，就能在 MySQL 中打开使用。同样，如果将数据写入到一个 CSV 引擎表，其他的外部程 序也能立即从表的数据文件中读取 CSV 格式的数据。因此 CSV 引擎可以作为一 种数据交换的机制，非常有用。

4.1.7 Archive引擎

Archive 存储引擎只支持 INSERT 和 SELECT 操作，在 MySQL 5.1 之前也不支持索引。Archive 引擎会缓存所有的写并利用 zlib 对插入的行进行压缩，所以比 MyISAM 表的磁盘 I/O 更少。但是每次 SELECT 查询都需要执行全表扫描。所以 Archive 表适合日志和数据采集类应用，这类应用做数据分析时往往需要全表扫 描。或者在一些需要更快速的 INSERT 操作的场合下也可以使用。Archive 引擎不 是一个事务型的引擎，而是一个针对高速插入和压缩做了优化的简单引擎。

4.1.8 Federated引擎

Federated 引擎是访问其他 MySQL 服务器的一个代理，它会创建一个到远程 MySQL 服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发送需要的数据。最初设计该存储引擎是为了和企业级数据库如 Microsoft SQL Server 和 Oracle 的类似特性竞争的，可以说更多的是一种市场行为。尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

4.2 值得了解的第三方引擎

4.2.1 Percona的XtraDB存储引擎

基于 InnoDB 引擎的一个改进版本，已经包含在 Percona Server 和 MariaDB 中，它的改进点主要集中在性能、可测量性和操作灵活性方面。XtraDB 可以作为 InnoDB 的一个完全的替代产品，甚至可以兼容地读写 InnoDB 的数据文件，并支持 InnoDB 的所有查询。

4.2.2 TokuDB引擎

使用了一种新的叫做分形树(Fractal Trees)的索引数据结构。该结构是缓存无关的，因此即使其大小超过内存性能也不会下降，也就没有内存生命周期和碎片的问题。TokuDB 是一种大数据（Big Data)存储引擎，因为其拥有很高的压缩比， 可以在很大的数据量上创建大量索引。现在该引擎也被 Percona 公司收购。

Tips：分形树，是一种写优化的磁盘索引数据结构。 在一般情况下， 分形 树的写操作（Insert/Update/Delete）性能比较好，同时它还能保证读操作近似于B+树的读性能。据测试结果显示， TokuDB 分形树的写性能优于 InnoDB 的B+树， 读性能略低于B+树。 分形树核心思想是利用节点的 MessageBuffer 缓存更新操作，充分利用数据局部性原理，将随机写转换为顺序写，这样极大的提高了随机写的效率。

4.2.3 Infobright

MySQL 默认是面向行的，每一行的数据是一起存储的，服务器的查询也是以行为单位处理的。而在大数据量处理时，面向列的方式可能效率更高，比如 HBASE 就是面向列存储的。
Infobright 是最有名的面向列的存储引擎。在非常大的数据量（数十TB)时， 该引擎工作良好。Infobright 是为数据分析和数据仓库应用设计的。数据高度压 缩，按照块进行排序，每个块都对应有一组元数据。在处理查询时，访问元数据可决定跳过该块，甚至可能只需要元数据即可满足查询的需求。但该引擎不支持索引，不过在这么大的数据量级，即使有索引也很难发挥作用，而且块结构也是一种准索引 (quasi-index)。Infobright 需要对 MySQL 服务器做定制，因为一些地 方需要修改以适应面向列存储的需要。如果查询无法在存储层使用面向列的模式 执行，则需要在服务器层转换成按行处理，这个过程会很慢。Infobright 有社区 版和商业版两个版本。

4.2.4 其他

针对图操作，全文检索，MySQL下都有对应的存储引擎，需要可以自行查阅。

4.3 如何选择合适的引擎

这么多存储引擎，我们怎么选择?
大部分情况下，InnoDB 都是正确的选择， 所以在 MySQL 5.5 版本将 InnoDB 作为默认的存储引擎了。对于如何选择存储引擎，可以简单地归纳为一句话:“除非需要用到某些 InnoDB 不具备的特性，并且没有其他办法可以替代，否则都应该优先选择 InnoDB 引擎”。比如，MySQL 中只有 MyISAM 支持地理空间搜索。
当然，如果不需要用到 InnoDB 的特性，同时其他引擎的特性能够更好地满足需求，也可以考虑一下其他存储引擎。举个例子，如果不在乎可扩展能力和并发能力，也不在乎崩溃后的数据丢失问题，却对 InnoDB 的空间占用过多比较敏感，这种场合下选择 MyISAM 就比较合适。

除非万不得已，否则建议不要混合使用多种存储引擎，否则可能带来一系列复杂的问题，以及一些潜在的 bug 和边界问题。存储引擎层和服务器层的交互已 经比较复杂，更不用说混合多个存储引擎了。至少，混合存储对一致性备份和服务器参数配置都带来了一些困难。

在微服务应用中可以分库解决混合引擎引起的复杂问题。

4.4 表引擎的转换

有很多种方法可以将表的存储引擎转换成另外一种引擎。每种方法都有其优 点和缺点。常用的有三种方法
1. ALTER TABLE
将表从一个引擎修改为另一个引擎最简单的办法是使用 ALTER TABLE 语句。 下面的语句将 mytable 的引擎修改为 InnoDB :
mysql> ALTER TABLE mytable ENGINE = InnoDB;

表引擎的转换上述语法可以适用任何存储引擎。但需要执行很长时间，在实现上，MySQL 会按行将数据从原表复制到一张新的表中，在复制期间可能会消耗系统所有的 I/O 能力，同时原表上会加上读锁。所以，在繁忙的表上执行此操作要特别小心。
如果转换表的存储引擎，将会失去和原引擎相关的所有特性。
2. 导出与导入
还可以使用 mysqldump 工具将数据导出到文件，然后修改文件中 CREATE TABLE 语句的存储引擎选项，注意同时修改表名，因为同一个数据库中不能存在 相同的表名，即使它们使用的是不同的存储引擎。
3. CREATE 和 SELECT
先创建一个新的存储引擎的表，然后利用 INSERT…SELECT 语法来导数据:
mysql>CREATE TABLE innodb_table LIKE myisam_table;
mysql>ALTER TABLE innodb_table ENGINE=InnoDB;
mysql>INSERT INTO innodb_table SELECT * FROM myisam_table;
如果数据量很大，则可以考虑做分批处理，针对每一段数据执行事务提交操 作。

4.5 检查我的MySQL的引擎