MySQL - 逻辑架构简介
# Mysql 逻辑架构简介
# 整体架构图
和其它数据库相比，MySQL 有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可 以根据业务的需求和实际需要选择合适的存储引擎。
各层介绍：
# 连接层
最上层是一些客户端和连接服务，包含本地 sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP 的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于 SSL 的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
# 服务层
Management Serveices & Utilities
系统管理和控制工具
SQL Interface
SQL 接口。接受用户的 SQL 命令，并且返回用户需要查询的结果。比如 select from 就是调用 SQL Interface
Parser
解析器。 SQL 命令传递到解析器的时候会被解析器验证和解析
Optimizer
查询优化器。 SQL 语句在查询之前会使用查询优化器对查询进行优化，比如有 where 条件时，优化器来决定先投影还是先过滤。
Cache 和 Buffer
查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key 缓存， 权限缓存等
注：MySQL 8.X 取消了查询缓存
# 引擎层
存储引擎层，存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
# 存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。
# show profile
利用 show profile 可以查看 sql 的执行周期。
开启 profile
查看 profile 是否开启：show variables like '%profiling%'
mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.01 sec)
2
3
4
5
6
7
8
9
如果没有开启，可以执行 set profiling = 1 开启！
使用 profile
执行 show profiles; 命令，可以查看最近的几次查询。
根据 Query_ID，可以进一步执行 show profile cpu,block io for query Query_id 来查看 sql 的具体执行步骤。
# 大致的查询流程
MySQL 的查询流程大致是：
MySQL 客户端通过协议与 MySQL 服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析，也就是说，在解析查询之前，服务器会先访问查询缓存（query cache），它存储 SELECT 语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。
语法解析器和预处理：首先 MySQL 通过关键字将 SQL 语句进行解析，并生成一颗对应的「解析树」。MySQL 解析器将使用 MySQL 语法规则验证和解析查询；预处理器则根据一些 MySQL 规则进一步检查解析数是否合法。
查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
然后，MySQL 默认使用的 B+Tree 索引，并且一个大致方向是：无论怎么折腾 SQL，至少在目前来说，MySQL 最多只用到表中的一个索引。
# SQL的执行顺序
手写的顺序：
真正执行的顺序：随着 MySQL 版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。下面是经常出现的查询顺序：
# 存储引擎
# 存储引擎概述
和大多数的数据库不同，MySQL 中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎。
存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。
Oracle，SqlServer 等数据库只有一种存储引擎。MySQL 提供了插件式的存储引擎架构。所以 MySQL 存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。
MySQL5.0 支持的存储引擎包含：InnoDB、MyISAM、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED 等，其中 InnoDB 和 BDB 提供事务安全表，其他存储引擎是非事务安全表。
通过指令查询当前数据库支持的存储引擎 ：
show engines
创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5 之前的默认存储引擎是 MyISAM，5.5 之后就改为了 InnoDB。
查看 MySQL 数据库默认的存储引擎，指令 ：
show variables like '%storage_engine%' ；
# 各种存储引擎特性
下面重点介绍几种常用的存储引擎，并对比各个存储引擎之间的区别，如下表所示 ：
| 特点 | InnoDB | MyISAM | MEMORY | MERGE | BDB |
|---|---|---|---|---|---|
| 存储限制 | 64TB | 有 | 有 | 没有 | 有 |
| 事务安全 | 支持 | ||||
| 锁机制 | 行锁(适合高并发) | 表锁 | 表锁 | 表锁 | 行锁 |
| B 树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 哈希索引 | 支持 | ||||
| 全文索引 | 支持(5.6 版本之后) | 支持 | |||
| 集群索引 | 支持 | ||||
| 数据索引 | 支持 | 支持 | 支持 | ||
| 索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 数据可压缩 | 支持 | ||||
| 空间使用 | 高 | 低 | N/A | 低 | 低 |
| 内存使用 | 高 | 低 | 中等 | 低 | 高 |
| 批量插入速度 | 低 | 高 | 高 | 高 | 高 |
| 支持外键 | 支持 |
下面我们将重点介绍最长使用的两种存储引擎：InnoDB、MyISAM，另外两种 MEMORY、MERGE，了解即可。
# InnoDB
InnoDB 存储引擎是 MySQL 的默认存储引擎。
InnoDB 存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比 MyISAM 的存储引擎，InnoDB 写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。
InnoDB 存储引擎不同于其他存储引擎的特点：
事务控制
create table goods_innodb( id int NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL, primary key(id) )ENGINE=innodb DEFAULT CHARSET=utf8;
```sql
#1
start transaction;
#2
insert into goods_innodb(id,name) values(null,'Meta20');
#3
commit;
2
3
4
5
6
7
8
测试发现在 InnoDB 中是存在事务的；
外键约束
MySQL 支持外键的存储引擎只有 InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候，也会自动的创建对应的索引。
下面两张表中，country_innodb 是父表，country_id 为主键索引，city_innodb 表是子表，country_id 字段为外键，对应于 country_innodb 表的主键 country_id。
create table country_innodb( country_id int NOT NULL AUTO_INCREMENT, country_name varchar(100) NOT NULL, primary key(country_id) )ENGINE=InnoDB DEFAULT CHARSET=utf8; create table city_innodb( city_id int NOT NULL AUTO_INCREMENT, city_name varchar(50) NOT NULL, country_id int NOT NULL, primary key(city_id), key idx_fk_country_id(country_id), CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan'); insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18在创建索引时，可以指定在删除、更新父表时，对子表进行的相应操作，包括 RESTRICT、CASCADE、SET NULL 和 NO ACTION。
RESTRICT 和 NO ACTION 相同，是指子表有父表对应记录的情况下，父表无法进行删除、更新等操作
CASCADE 表示父表在更新或者删除时，更新或者删除子表对应的记录
SET NULL 则表示父表在更新或者删除的时候，子表的对应字段被 SET NULL
针对上面创建的两个表，子表的外键指定是 ON DELETE RESTRICT ON UPDATE CASCADE 方式的，那么在主表删除记录的时候，如果子表有对应记录，则不允许删除（RESTRICT），主表在更新记录的时候，如果子表有对应记录，则子表对应更新（CASCADE）。
表中数据如下图所示 ：
查看外键信息
show create table city_innodb ;1
删除 country_id 为 1 的 country 数据，有外键时会删除失败
更新主表 country 表的字段 country_id 时
update country_innodb set country_id = 100 where country_id = 1;1
更新后，子表的数据信息为 ：
存储方式
InnoDB 存储表和索引有以下两种方式：
使用共享表空间存储，这种方式创建的表的表结构保存在
.frm文件中， 数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是多个文件使用多表空间存储，这种方式创建的表的表结构仍然存在
.frm文件中，但是每个表的数据和索引单独保存在.ibd中
# MyISAM
MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以 SELECT、INSERT 为主的应用基本上都可以使用这个引擎来创建表。有以下两个比较重要的特点：
不支持事务
create table goods_myisam( id int NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL, primary key(id) )ENGINE=myisam DEFAULT CHARSET=utf8;1
2
3
4
5
通过测试，我们发现，在 MyISAM 存储引擎中，是没有事务控制的；
文件存储方式
每个 MyISAM 在磁盘上存储成 3 个文件，其文件名都和表名相同，但拓展名分别是：
.frm（存储表定义）
.MYD（MYData，存储数据）
.MYI（MYIndex，存储索引）
# MEMORY
Memory 存储引擎将表的数据存放在内存中。每个 MEMORY 表实际对应一个磁盘文件，格式是 .frm，该文件中只存储表的结构，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。MEMORY 类型的表访问非常地快，因为他的数据是存放在内存中的，并且默认使用 HASH 索引，但是服务一旦关闭，表中的数据就会丢失。
# MERGE
MERGE 存储引擎是一组 MyISAM 表的组合（也就是多个 MyISAM 表合在一起就是 MERGE 表），这些 MyISAM 表必须结构完全相同，MERGE 表本身并没有存储数据，对 MERGE 类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的 MyISAM 表进行的。
对于 MERGE 类型表的插入操作，是通过 INSERT_METHOD 子句定义插入的表，可以有 3 个不同的值：
- 使用 FIRST 或 LAST 值使得插入操作被相应地作用在第一或者最后一个 MyISAM 表上
- 不定义这个子句或者定义为 NO，表示不能对这个 MERGE 表执行插入操作。
可以对 MERGE 表进行 DROP 操作，但是这个操作只是删除 MERGE 表的定义，对内部的表是没有任何影响的。
下面是一个创建和使用 MERGE 表的示例 ：
1）.创建 3 个测试表 order_1990, order_1991, order_all , 其中 order_all 是前两个表的 MERGE 表：
create table order_1990(
order_id int,
order_money double(10,2),
order_address varchar(50),
primary key (order_id)
)engine = myisam default charset=utf8;
create table order_1991(
order_id int,
order_money double(10,2),
order_address varchar(50),
primary key (order_id)
)engine = myisam default charset=utf8;
create table order_all(
order_id int,
order_money double(10,2),
order_address varchar(50),
primary key (order_id)
)engine = merge union = (order_1990,order_1991) INSERT_METHOD=LAST default charset=utf8;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2）. 分别向两张表中插入记录
insert into order_1990 values(1,100.0,'北京');
insert into order_1990 values(2,100.0,'上海');
insert into order_1991 values(10,200.0,'北京');
insert into order_1991 values(11,200.0,'上海');
2
3
4
5
3）. 查询 3 张表中的数据
order_1990 中的数据：
order_1991 中的数据：
order_all 中的数据：
4）. 往 order_all 中插入一条记录 ，由于在 MERGE 表定义时，INSERT_METHOD 选择的是 LAST，那么插入的数据会向最后一张 MyISAM 表（order_1991）中插入：
insert into order_all values(100,10000.0,'西安')；
# 2.3 存储引擎的选择
在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。
InnoDB: 是 MySQL 的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么 InnoDB 存储引擎是比较合适的选择。InnoDB 存储引擎除了有效的降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 是最合适的选择
MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的
MEMORY：将所有数据保存在 RAM 中，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。MEMORY 的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果
MERGE：用于将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE 表的优点在于可以突破对单个 MyISAM 表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善 MERGE 表的访问效率。这对于存储诸如数据仓储等 VLDB 环境十分合适
# SQL 预热
常见的 Join 查询图
