MySQL 内部技术架构

MySQL 内部支持缓存查询吗：

当 MySQL 接收到客户端的查询 SQL 之后，仅仅只需要对其进行相应的权限验证之后，就会通过 Query Cache 来查找结果，甚至都不需要经过 Optimizer 模块进行执行计划的分析优化，更不需要发生任何存储引擎的交互

mysql 5.7 支持内部缓存。8.0 之后就废弃掉了

MySQL8 为何废弃掉查询缓存：

缓存的意义在于快速查询提升系统性能，可以灵活控制缓存的一致性

• mysql 基本没有手段灵活的管理缓存失效和生效，尤其对于频繁更新的表
• SQL 必须完全一致才会导致 Cache 命中
• 为了节省内存空间，太大的result set 不会被 cache (小于 query_cache_limit)
• MySQL  缓存在分库分表环境下是不起作用的
• 执行 SQL 里有触发器，自定义函数， MySQL 缓存也是不起作用的
• 在表的结构或数据发生改变时，基于该表相关 cache 立即全部失效

MySQL 核心模块：

概要图：

 官方架构图：

 连接池：由于每次建立建立需要消耗很多时间，连接池的作用就是将这些连 接缓存下来，下次可以直接用已经建立好的连接，提升服务器性能。

管理工具和服务：系统管理和控制工具，例如备份恢复、Mysql 复制、集群 等

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

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

• 将 SQL 语句分解成数据结构，并将这个结构传递到后续步骤，以后 SQL 语句的传递和处理就是基于这个结构的
• 如果在分解构成中遇到错误，那么就说明这个 sql 语句是不合理的

优化器：查询优化器，SQL 语句在查询之前会使用查询优化器对查询进行优 化。

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

MySQL 执行一条查询语句的内部执行过程：

• MySQL 客户端通过协议与MySQL 服务器建立连接，通过SQL接口发送SQL 语句，先检查查询缓存，如果命中，直接返回结果，否则进行语法解析。也就是说，在解析查询之前，服务器会先访问查询缓存，如果某个查询结果已经位于缓存中，服务器就不会在对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高性能
• MySQL 解析器通过关键字将SQL语句进行解析，并生成一颗对应的解析树，解析器使用MySQL 语法规则验证和解析 SQL 语句。例如，它将验证是否使用了错误的关键字，或者使用关键字的顺序是否正确，引号能否前后匹配等；预处理器根据 MySQL 规则进一步检查解析树是否合法，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看看是否有歧义等。然后预处理器会进行查询重写，生成一颗新解析树
• 查询优化器将解析树转换成执行计划。MySQL 优化程序会对我们的语句做一些优化，如子程序转换为连接、表达式简化等等。优化的结果就是生成一个执行计划，这个执行计划表明了应该使用哪些索引执行查询，以及表之间的连接顺序是啥样。 我们可以使用 EXPLAIN 语句来查看某个语句的执行计划
• 进行执行器阶段。完成查询优化后，查询执行引擎会按照生成的执行计划调用存储引擎提供的接口执行SQL查询并将结果返回客户端。在MySQL8以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存，再返回给客户端

 MySQL 自带那些存储引擎：

1. InnoDB 存储引擎：

• InnoDB 是 MySQL 的默认事务型引擎，它被设计用来处理大量短期(short-lived) 事务。可以确保事务的完整提交(Commit) 和 回滚(Rollback)
• 除非有特别的原因需要使用其他的存储引擎， 否则应该优先考虑 InnoDB 引擎
• 数据文件结构： 表名.frm 存储表结构（MySQL 8 时，合并在表名.ibd中）
• 表名.ibd 存储数据和索引
• InnoDB 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响

2. MyISAM 存储引擎

• MyISAM 提供了大量的特性，包括全文检索、压缩、空间函数(GIS)等，但MyISAM 不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复
• 优势是访问速度快，对事务完整性没有要求或者以 SELECT、INSERT 为主的应用
• 数据文件结构：表名.frm 存储表结构，表名.MYD 存储数据、表名.MYI 存储索引
• MyISAM 只缓存索引，不缓存真实数据

3. Archive 引擎

• Archive 档案存储引擎只支持 INSERT 和 SELECT 操作
• Archive 表适合日志和数据采集(档案) 类应用
• 根据英文的测试结论来看， Archive 表比 MyISAM 表要小大约 75%，比支持事务处理的 InnoDB表小大约 83%

4. Blackhole 引擎

• Blackhole 引擎没有实现任务存储机制，它会丢弃所有插入的数据，不做任务保存
• 但是服务器会记录 Blackhole 表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

5. CSV 引擎

• CSV 引擎可以将普通的CSV 文件作为 MySQL的表来处理，但不支持索引
• CSV 引擎可以作为一种数据交换的机制，非常有用
• CSV 存储的数据直接可以在操作系统里，用文本编辑器，或者 excel 读取。

6. Memory 引擎

• 如果需要快速地访问数据，并且这些数据不会被修改，重启以后失效也关系，那么使用 Memory 表是非常有用
• Memory 表至少比 MyISAM 表更快一个数量级

7. Federated 引擎

• Federated 引擎是访问其他 MySQL 服务器的一个代理(跨库关联查询)，尽管改引擎看起来提供一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的

InnoDB 引擎结构图：

 内存区域：

• Buffer Pool: 在 InnoDB 访问表记录和索引时会在 Buffer Pool的页中缓存，以后使用可以减少磁盘 I/O 操作，提升效率。主要用来缓存热数据和索引页
• Log Buffer: 用来缓存 redolog
• Adaptivae Hash Index： 自适应哈希索引
• Change Buffer: 它是一种应用在非唯一普通索引页(non-unique secondary index page) 不在缓冲池中，对页进行了写操作，并不会立刻将磁盘页加载到缓冲池，而仅仅记录缓冲变更(Buffer Changes), 等未来数据被读取时，再将数据合并(Merge) 恢复到缓冲池中的技术。写缓冲的目的是降低写操作的磁盘 I/O， 提升数据库性能。

磁盘区域： 

• 表空间：分为系统表空间（MySQL 目录的 ibdata1 文件), 临时表空间，常规表空间， Undo 表空间以及 file-per-table 表空间(MySQL 5.7 默认打开 file_per_table 配置)。系统表空间又包括了 InnoDB 数据字典，双写缓冲区(DoubleWrite Buffer)，修改缓存(Change Buffer), Undo 日志等。
• Redo 日志： 存储的就是 Log Buffer 刷到磁盘的数据

能否单独为一张表设置存储引擎：

方法1： 设置默认存储引擎

SET DEFAULT_STORAGE_ENGINE=MyISAM;

方法二：

修改 my.cnf 文件： vim /etc/my.cnf

新增一行： default-storage-engine=MyISAM

重启MySQL: systemctl restart mysqld

方法三： 为不同的表设置不同的存储引擎

CREATE TABLE 表名(建表语句; ) ENGINE = 存储引擎名称;
ALTER TABLE 表名  ENGINE = 存储引擎名称;

MyISAM 和 InnoDB 的区别：

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一整条记录也会锁住整个表，不适合高并发操作	行锁，操作时只锁某一行，不对其他行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
关注点	并发查询、节省资源、耗时少、简单业务	并发写、事务、多表关系、更大资源
默认安装	Y	Y
默认使用	N	Y
自带系统表使用	Y	N