OddFar's Notes
  • 数据库
  • MySQL
zhiyuan
2021-05-20
目录

MySQL - 逻辑架构简介

# Mysql 逻辑架构简介

# 整体架构图

img

和其它数据库相比，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)
1
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 默认使用的 BTREE 索引，并且一个大致方向是：无论怎么折腾 sql，至少在目前来说，mysql 最多只用到表中的一个索引。

# SQL的执行顺序

手写的顺序：

image-20210520161848827

真正执行的顺序：

​ 随着 Mysql 版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动 态调整执行顺序。下面是经常出现的查询顺序：

image-20210520161931864

image-20210520164022838

# 存储引擎

# 存储引擎概述

和大多数的数据库不同, MySQL中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。

​ 存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。

​ Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。

​ MySQL5.0支持的存储引擎包含 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。

通过指令查询当前数据库支持的存储引擎 ：

show engines
1

1551186043529

创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。

查看Mysql数据库默认的存储引擎 ， 指令 ：

show variables like '%storage_engine%' ；
1

1556086372754

# 各种存储引擎特性

下面重点介绍几种常用的存储引擎， 并对比各个存储引擎之间的区别， 如下表所示 ：

特点 InnoDB MyISAM MEMORY MERGE NDB
存储限制 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;
    1
    2
    3
    4
    5
    #1
start transaction;
#2
insert into goods_innodb(id,name)values(null,'Meta20');
#3
commit;
    1
    2
    3
    4
    5
    6

    1556075130115

    测试发现在 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
    19
    20
    21

    在创建索引时， 可以指定在删除、更新父表时，对子表进行的相应操作，包括 RESTRICT、CASCADE、SET NULL 和 NO ACTION。

    RESTRICT 和NO ACTION 相同， 是指限制在子表有关联记录的情况下， 父表不能更新；

    CASCADE 表示父表在更新或者删除时，更新或者删除子表对应的记录；

    SET NULL 则表示父表在更新或者删除的时候，子表的对应字段被SET NULL 。

    针对上面创建的两个表， 子表的外键指定是ON DELETE RESTRICT ON UPDATE CASCADE 方式的， 那么在主表删除记录的时候， 如果子表有对应记录， 则不允许删除， 主表在更新记录的时候， 如果子表有对应记录， 则子表对应更新 。

    表中数据如下图所示 ：

    1556087540767

    查看外键信息

    show create table city_innodb ;
    1

    1556087611295

    删除 country_id为 1 的 country 数据，有外键时会删除失败

    1556087719145

    更新主表country表的字段 country_id 时

    update country_innodb set country_id = 100 where country_id = 1;
    1

    1556087759615

    更新后， 子表的数据信息为 ：

    1556087793738

  • 存储方式

    InnoDB 存储表和索引有以下两种方式 ：

    ①. 使用共享表空间存储， 这种方式创建的表的表结构保存在 .frm文件中， 数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是多个文件。

    ②. 使用多表空间存储， 这种方式创建的表的表结构仍然存在 .frm 文件中，但是每个表的数据和索引单独保存在 .ibd 中。

    1556075336630

# 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

    1551347590309

    通过测试，我们发现，在MyISAM存储引擎中，是没有事务控制的 ；

  • 文件存储方式

    每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是 ：

    .frm (存储表定义)；

    .MYD(MYData , 存储数据)；

    .MYI(MYIndex , 存储索引)；

    1556075073836

# MEMORY

​ Memory存储引擎将表的数据存放在内存中。每个MEMORY表实际对应一个磁盘文件，格式是.frm ，该文件中只存储表的结构，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。MEMORY 类型的表访问非常地快，因为他的数据是存放在内存中的，并且默认使用HASH索引 ， 但是服务一旦关闭，表中的数据就会丢失。

# MERGE

​ MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有存储数据，对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。

​ 对于MERGE类型表的插入操作，是通过INSERT_METHOD子句定义插入的表，可以有3个不同的值，使用FIRST 或 LAST 值使得插入操作被相应地作用在第一或者最后一个表上，不定义这个子句或者定义为NO，表示不能对这个MERGE表执行插入操作。

​ 可以对MERGE表进行DROP操作，但是这个操作只是删除MERGE表的定义，对内部的表是没有任何影响的。

1556076359503

下面是一个创建和使用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;
1
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,'上海');
1
2
3
4
5

3）. 查询3张表中的数据。

order_1990中的数据 ：

1551408083254

order_1991中的数据 ：

1551408133323

order_all中的数据 ：

1551408216185

4）. 往order_all中插入一条记录 ，由于在MERGE表定义时，INSERT_METHOD 选择的是LAST，那么插入的数据会想最后一张表中插入。

insert into order_all values(100,10000.0,'西安')；
1

1551408519889

# SQL 预热

常见的 Join 查询图

image-20210520162731111

在 GitHub 上编辑此页 (opens new window)
最后更新: 2023/03/26, 13:03:00
MySQL - 知识体系
MySQL - 索引介绍

← MySQL - 索引介绍→

Theme by Vdoing | Copyright © 2021-2023 oddfar | 冀ICP备20001094号