1. MySQL逻辑架构介绍
MySQL 的架构可以在多种不同的场景中发挥良好的作用，主要体现在 MySQL 的存储引擎架构上插件式的储存引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离，这种架构可以根据业务和实际需要选择合适的储存引擎。
总结：MySQL 是分层设计；插件式可拔插引擎；系统优化时头脑中有逻辑架构层；
MySQL 自带优化器，执行的顺序不一定是SQL 语句的书写顺序，索引失效可能是优化器的问题。如果不想MySQL 进行优化可以强制使用书写好的SQL语句或者将优化器去除（如阿里内部部分场景去除MySQL的优化器）
2.MySQL 逻辑分层
2.1 连接层
最上层是客户端和连接服务，包含本地 socket 通信和大多数基于客户端/服务端工具实现的类似 TCP/IP 通道，主要完成一些类似于连接处理、授权认证以及相关的安全方案。该层上引入了线程池概念，通过认证安全接入的客户端提供线程。同样在该层上可以实现基于 SSL 的安全连接，服务器也会为安全接入的每个客户端验证它所具有的操作权限。
2.2 服务层
第二层架构主要完成大多数的核心服务功能，如 SQL 接口，并完成缓存的查询，SQL 分析和优化及部分内置函数的执行。所有跨存储引擎功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化，如确定查询表的顺序，是否使用索引等，最后生成相应的执行操作。如果是 SELECT 语句，服务器还会查询内部的缓存。增加缓存空间，在大量读操作环境中能很好的提高系统性能。
| 服务层构成 | 简介 |
|---|---|
| Management Services & Utilities | 系统管理和控制工具 |
| SQL Interface | SQL接口。接受用户的 SQL 命令，并且返回用户需要的结果。如 select from 就是调用 SQL Interface。 |
| Parser | 解析器。SQL 命令传递到解析器的时候会被解析器验证和解析。 |
| Optimizer | 查询优化器。SQL 语句在查询钱会使用查询优化器进行查询优化，比如有 where 条件时，优化器会决定先投影还是先过滤。 |
| Cache & Buffer | 查询缓存。如果缓存有命中查询结果的，查询语句就可以直接在查询缓存中取出数据。这个缓存机制是由一系列小缓存组成的，如：表缓存、记录缓存、key缓存、权限缓存 |
2.3 引擎层
存储引擎层，存储引擎真正负责了 MySQL 中的存储和提取，服务器通过 API 与存储引擎进行通讯。不同的存储引擎具有不同的功能，我们可以根据自己的实际需要进行选择，目前常用的是 MyISAM 和 InnoDB。
查看 MySQL 的存储引擎
# 查看 MySQL 支持的存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
# 查看默认的存储引擎
mysql> show variables like '%storage_engine%';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
| storage_engine | InnoDB |
+------------------------+--------+
2 rows in set (0.00 sec)
MyISAM 和 InnoDB 的主要区别
| 对比项目 | MyISAM | InnoDB |
|---|---|---|
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，即使操作一条记录也会锁住整个表，不适合高并发场景 | 行锁，操作时只锁住某一行，不对其他行有影响，适合高并发操作。 |
| 缓存 | 只缓存索引，不缓存真实数据 | 不仅缓存索引还缓存真实数据，对内存要求较高，而且内存大小对性能有决定性影响。 |
| 关注点 | 读性能 | 并发写、事务、资源 |
| 默认安装 | Y | Y |
| 默认使用 | N | Y |
| 自带系统表使用 | Y | N |
其他的存储引擎：
Percona 为 MySQL数据库服务器做了改进，在功能和性能上较 MySQL 有显著提升。该版本提升了高负载情况下 InnoDB 的性能、为 DBA 提供了一些好用的诊断工具，另外有更多的参数和命令控制服务器行为。
该公司新建了一款新的存储引擎 XtraDB 完全可以替代 InnoDB，并且在性能和并发上表现得更好。
阿里巴巴大部分 MySQL 数据库是对 Percona 的原型加以修改。
阿里开源的存储引擎： ALisql 中使用的 X-Engine 存储引擎。
2.4 存储层
数据存储层，主要是将数据存储在物理磁盘的文件系统中，并通过存储引擎进行交互。
2.5 SQL 查询的大致流程
① 查询缓存
MySQL 客户端通过协议与 MySQL 服务器建立连接，并发送查询语句，MySQL 服务器在接收到查询语句后首先查询缓存，如果缓存命中则直接返回结果，否则进行语句解析。在 MySQL 服务器接收到 SQL 语句后，会首先访问查询缓存（query cache，缓存中同时存储 SELECT 语句和相应的结果集），如果某个查询已经位于缓存中，服务器就不再对查询结果进行解析、优化和执行。而是仅仅将缓存中的结果返回给用户，会大大提升系统性能。
② 语法解析和预处理
MySQL 服务器在接收到 SQL 语句后会根据关键字将 SQL 语句进行解析，并生成一棵对应的**“解析树”**。MySQL 解析器会根据 SQL 语法规则进行语法验证和解析查询；预处理器则根据 SQL 规则进一步验证解析树是否合法。
③ 查询优化
当解析树通过合法性验证后，优化器会将 SQL 查询转化成执行计划。一条查询有很多种执行方式，最终返回的结果都是相同的。优化器的作用是找到这些执行方式中最好的作为执行计划。
④ 使用索引
MySQL 默认使用 BTREE 索引，并且有一个大致的方向：无论怎样折腾 SQL，至少在目前看来 MySQL 最多只使用表中的一个索引。
