Mysql(二）：架构与存储引擎

1.体系

1.连接层

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

show VARIABLES like '%max_connections%'

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

2.SQL 处理层

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

1.缓存

如果是查询语句（select 语句），首先会查询缓存是否已有相应结果，有则返回结果，无则进行下一步（如果不是查询语句，同样调到下一步）

show variables like '%query_cache_type%' -- 默认不开启

show variables like '%query_cache_size%' --默认值1M

SET GLOBAL query_cache_type = 1; --会报错

query_cache_type 只能配置在my.cnf 文件中，这大大限制了qc 的作用

在生产环境建议不开启，除非经常有sql 完全一模一样的查询

QC 严格要求2 次SQL 请求要完全一样，包括SQL 语句，连接的数据库、协议版本、字符集等因素都会影响

2.解析查询

解析查询，创建一个内部数据结构（解析树），这个解析树主要用来SQL 语句的语义与语法解析；

3.优化

优化SQL 语句，例如重写查询，决定表的读取顺序，以及选择需要的索引等

explain select * from account t where t.id in (select t2.id from account t2);

show warnings;

2.逻辑架构

在mysql中database和schema是等价的

3.物理存储结构

show VARIABLES like 'datadir';

每个数据库对应datadir中一个子文件，每张表文件都在对应数据库的子文件下，具体文件和存储引擎相关，共同点事都有.frm文件，存放数据格式查看提供的引擎

show engines;
show variables like '%storage_engine%';--查看默认引擎

4.存储引擎

1.MyISAM

MySql 5.5 之前默认的存储引擎，MyISAM 存储引擎由.MYD 和.MYI以及.frm文件 组成（非聚集索引，堆表，索引和数据分开存放）

1.表压缩

myisampack -b -f /usr/local/mysql/data/mall/testmysam.MYI;

压缩后再往表里面新增数据就新增不了,压缩后，需要

myisamchk -r -f /usr/local/mysql/data/mall/testmysam.MYI;

2.适用场景及特点

特点：

• 不支持外键，事务

• 支持BTREE索引

• 支持压缩，压缩后不能新增数据

• 表锁

• 只缓存索引，不缓存真实数据

适用场景：

• 非事务型应用（数据仓库，报表，日志数据）

• 只读类应用

• 空间类应用（空间函数，坐标）

2.Innodb

由.ibd,.frm文件组成

1.系统表空间 & 独立表空间

show VARIABLES like 'innodb_file_per_table';

ON:独立的表空间，tablename.ibd，通过optimize table收缩系统文件，可同时向多个文件刷新数据

OFF:系统表空间，ibdataX，无法简单收缩文件大小，会产生IO瓶颈

特点：

• 支持主外键，事务
• 只支持BTree索引
• 索引组织表（索引与数据放一起）
• 行锁
• 缓存索引和数据

适用场景：

• 完全支持事务得 ACID 特性
• Redo Log 和 Undo Log
• Innodb 支持行级锁（并发程度更高）

3.CSV

由.csv（内容），.csm（元数据表状态数据量），.frm文件组成

特点：

• 以 csv 格式进行数据存储
• 所有列都不能为 null 的
• 不支持索引（不适合大表，不适合在线处理）
• 可以对数据文件直接编辑（保存文本文件内容），修改后需要flush TABLES;同步更新.csm文件后生效

create table mycsv(id int not null,c1 VARCHAR(10) not null,c2 char(10) not null) engine=csv;

insert into mycsv values(1,'aaa','bbb'),(2,'cccc','dddd');

vi /usr/local/mysql/data/mall/mycsv.CSV 修改文本数据

flush TABLES; select * from mycsv

create index idx_id on mycsv(id)

4.Archive

由.arz,.frm文件组成，以zlib 对表数据进行压缩，磁盘I/O 更少

特点：

• 只支持 insert 和 select 操作
• 只允许在自增 ID 列上加索引

5.Memory

只有.frm文件

特点：

• 也称 HEAP 存储引擎，所以数据保存在内存中

• 支持 HASH 索引和 BTree 索引（默认hash,hash适用于等值查询，Btree适用于范围）

• 所有字段都是固定长度 varchar(10) = char(10)

• 不支持 Blog 和 Text 等大字段

• Memory 存储引擎使用表级锁

• 最大大小由 max_heap_table_size 参数决定,默认16M

show VARIABLES like 'max_heap_table_size';

Memory存储引擎与临时表关联：

手动创建的自己指定索引，一般在磁盘上

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-G2PyV7kQ-1585465830321)(/Users/zyq/Library/Application Support/typora-user-images/image-20200325083533793.png)]

6.Ferderated

特点：

• 提供了访问远程 MySQL 服务器上表的方法

• 本地不存储数据，数据全部放到远程服务器上

• 本地需要保存表结构和远程服务器的连接信息

默认禁止，启用需要再启动时在my.cnf中增加一行ferderated

show ENGINES

create database local;

create database remote;

create table remote_fed(id int auto_increment not null,c1 varchar(10) not null default '',c2

char(10) not null default '',primary key(id)) engine = INNODB

INSERT into remote_fed(c1,c2) values('aaa','bbb'),('ccc','ddd'),('eee','fff');

CREATE TABLE `local_fed` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`c1` varchar(10) NOT NULL DEFAULT '',

`c2` char(10) NOT NULL DEFAULT '',

PRIMARY KEY (`id`)

) ENGINE=federated CONNECTION

='mysql://root:root1234%@127.0.0.1:3306/remote/remote_fed'

--'mysql://用户名:密码@url:port/库名/表名'

select * from local_fed

delete from local_fed where id = 2

select * from remote.remote_fed