Mysql进阶第二天

Mysql 架构

体系

连接层

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

show VARIABLES like '%max_connections%' ；

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

SQL 处理层

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

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

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

3.优化：优化 SQL 语句，例如重写查询，决定表的读取顺序，以及选择需要的索引等。这一阶段用户是可以查询的，查询服务器优化器是如何进行优化的，便于用户重构查询和修改相关配置，达到最优化。这一阶段还涉及到存储引擎，优化器会询问存储引擎，比如某个操作的开销信息、是否对特定索引有查询优化等。

缓存

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 语句，连接的数据库、协议版本、字符集等因素都会影响。

解析查询

按照鱼骨的方式顺序执行。

优化

通过上面的 sql 大概就能看出，虽然现在还没学执行计划，但通过这个已经看出一个 sql 并不一定会去查询物理数据，sql 解析器会通过优化器来优化程序员写的 sql 。

explain

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

show warnings;

逻辑架构

在 mysql 中其实还有个 schema 的概念，这概念没什么太多作用，只是为了兼容其他数据库，所以也提出了这个。

在 mysql 中 database 和 schema 是等价的

create database demo;

show databases;

drop schema demo;

show databases;

物理存储结构

数据库的数据库（DataDir）

mysql 安装的时候都要指定 datadir,其查看方式为：

show VARIABLES like 'datadir'，其规定所有建立的数据库存放位置。

数据库

创建了一个数据库后，会在上面的 datadir 目录新建一个子文件夹：

表文件

用户建立的表都会在上面的目录中，它和具体的存储引擎相关，但有个共同的就是都有个frm 文件，它存放的是表的数据格式。

mysqlfrm --diagnostic /usr/local/mysql/data/mall/account.frm 。

mysql utilities 安装 (mysql目录下)

tar -zxvf mysql-utilities-1.6.5.tar.gz；

cd mysql-utilities-1.6.5；

python ./setup.py build；

python ./setup.py install 。

存储引擎

#看你的 mysql 现在已提供什么存储引擎:

mysql> show engines;

#看你的 mysql 当前默认的存储引擎:

mysql> show variables like '%storage_engine%';

MyISAM

MySql 5.5 之前默认的存储引擎；

MyISAM 存储引擎由 MYD 和 MYI 组成；

myd: my data ；

myi: my index ；

MyISAM使用的是非聚集索引，它的索引跟数据是分开存放的，我们把它的表称作堆表 ；

create table testmysam (

id int PRIMARY key

) ENGINE=myisam

insert into testmysam VALUES(1),(2),(3) 。

表压缩

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

压缩后再往表里面新增数据就新增不了；

insert into testmysam VALUES(1),(2),(3)。

压缩后，需要还原：

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

在mysql5.5之后innodb也支持了表压缩，所以myisam最大的优势也没有了，所以oracle公司渐渐的没有对myisam存储引擎进行一个维护了。

适用场景：

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

 只读类应用

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

由于现在 innodb 越来越强大，myisam 已经停止维护 (绝大多数场景都不适合) 。

Innodb

Innodb 是一种事务性存储引擎；

 完全支持事务得 ACID 特性

 Redo Log 和 Undo Log

 Innodb 支持行级锁（并发程度更高）

show VARIABLES like 'innodb_log_buffer_size' 。

CSV

 以 csv 格式进行数据存储

 所有列都不能为 null 的

 不支持索引（不适合大表，不适合在线处理）

 可以对数据文件直接编辑（保存文本文件内容）

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) 。

Archive

组成：以 zlib 对表数据进行压缩，磁盘 I/O 更少，数据存储在 ARZ 为后缀的文件中 。

 特点：只支持 insert 和 select 操作； 只允许在自增 ID 列上加索引 。

create table myarchive(id int auto_increment not null,c1 VARCHAR(10),c2 char(10), key(id))

engine = archive;

create index idx_c1 on myarchive(c1) ；

INSERT into myarchive(c1,c2) value('aa','bb'),('cc','dd');

delete from myarchive where id = 1 ；

update myarchive set c1='aaa' where id = 1 ；

Memory

特点：

 文件系统存储特点：也称 HEAP 存储引擎，所以数据保存在内存中 

 支持 HASH 索引和 BTree 索引 

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

 不支持 Blog 和 Text 等大字段

 Memory 存储引擎使用表级锁

 最大大小由 max_heap_table_size 参数决定

show VARIABLES like 'max_heap_table_size' create table mymemory(id int,c1 varchar(10),c2 char(10),c3 text) engine = memory;

create table mymemory(id int,c1 varchar(10),c2 char(10)) engine = memory;

create index idx_c1 on mymemory(c1);

create index idx_c2 using btree on mymemory(c2);

show index from mymemory ；

show TABLE status LIKE ‘mymemory’ 。

与临时表的区别：

使用场景：

 hash 索引用于查找或者是映射表（邮编和地区的对应表）

 用于保存数据分析中产生的中间表

 用于缓存周期性聚合数据的结果表

Ferderated

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

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

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

使用场景：

偶尔的统计分析及手工查询（某些游戏行业），默认禁止，启用需要再启动时增加 federated 参数 。

show ENGINES；

create database local;

create database remote;

create table remote_fed(id int auto_increment not null,c1 varchar(10) not null default '',c2char(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 ；

select * from local_fed ；

delete from local_fed where id = 2 ；

select * from remote.remote_fed 。