【面试】数据库总结1（MySQL介绍+体系结构）

最新推荐文章于 2024-06-28 04:40:14 发布

张某某。paranoia

最新推荐文章于 2024-06-28 04:40:14 发布

阅读量284

点赞数

分类专栏：面试数据库 mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/paranior/article/details/115045747

版权

面试同时被 3 个专栏收录

23 篇文章 1 订阅

订阅专栏

数据库

5 篇文章 0 订阅

订阅专栏

mysql

2 篇文章 0 订阅

订阅专栏

MySQL介绍+体系结构目录

1.2.1 一条查询语句（上图）的执行过程

2.2.1 缓冲池 Buffer Pool

2.2.2 （redo）Log Buffer

1 MySQL

1.1 MySQL简介

关系型数据库与非关系型数据库

1.1.1 关系型数据库：

（1）基于行存储数据

（2）结构化schema

（3）表与表之间存在关联关系：relationship

（4）数据库使用SQL语言

（5）支持事务ACID（原子性、一致性、隔离性、持久性）

关系型数据库：

只能垂直扩容。表结构修改比较困难。并发时受限。

1.1.2 非关系型数据库

（1）存储非结构化数据（音频、视频等）

（2）表之间没有关联关系（扩展性更强）

（3）最终一致性遵循BASE理论

（4）支持数据的海量存储和高并发，支持分布式，扩缩容时相对简单

Redis

MongoDB（文档存储的）

Hbase（列存储的）

1.1.3 newSQL

介于关系型数据库与非关系型数据库之间的

TiDB

1.1.4 mysql

2003年之后，InnoDB成为默认的存储引擎

流行分支：

MariaDB

Percona Server（基于InnoDB提升了性能）XtraDB Engine

1.2 MySQL体系结构

1.2.1 一条查询语句（上图）的执行过程

（1）连接

通信类型：

①同步（一直等待服务端结果，受限于服务端性能，智能实现一对一通信，很难实现一对多通信）

②异步（先发送请求，再接收服务端结果。减少客户端阻塞等待的时间。）

连接方式：长连接（常用，放在连接池里管理）/短连接

通信协议：Socket、TCP/IP（win中海油命名管道和内存共享等方式）

问题：如何查看数据库当前有多少个连接？使用show status命令，模糊匹配Thread
show global status like 'Thread%';

问题：为什么是查看线程？客户端的连接和服务端的线程有什么关系？
每产生一个连接或者一个会话，在服务端就会创建一个线程来处理。反过来，如果要杀死会话，就是 Kill 线程。

字段含义
Threads_cached 缓存中的线程连接数。
Threads_connected 当前打开的连接数。
Threads_created 为处理连接创建的线程数。
Threads_running 非睡眠状态的连接数，通常指并发连接数。

既然是分配线程的话，保持连接肯定会消耗服务端的资源。MySQL 会把那些长时间不活动的连接自动断开。

有两个参数：

show global variables like 'wait_timeout'; -- 非交互式超时时间，如 JDBC 程序
show global variables like 'interactive_timeout'; -- 交互式超时时间，如数据库工具

默认都是 28800 秒，8 小时。

既然连接消耗资源，MySQL 服务允许的最大连接数（也就是并发数）默认是多少呢？
在 5.7 版本中默认是 151 个，最大可以设置成 100000。

show variables like 'max_connections';

参数级别：

MySQL 中的参数分为 session 和 global 级别，分别是在当前会话中生效和全局生效

但是并不是每个参数都有两个级别，比如 max_connections 就只有全局级别。

当没有带参数的时候，默认是 session 级别，包括查询和修改。

比如修改了一个参数以后，在本窗口查询已经生效，但是其他窗口不生效：

show VARIABLES like 'autocommit';
set autocommit = on;

所以，如果只是临时修改，建议修改 session 级别。如果需要在其他会话中生效，必须显式地加上 global 参数。

下面的命令修改称为动态修改（即数据库重启后会恢复修改前状态）
set session/global var_name value;

永久修改：

修改/etc/my.cnf文件

（2）查询缓存（已取消）

MySQL 内部自带了一个缓存模块，但是 5.7 的版本里面默认是关闭的。

show variables like 'query_cache%';

默认关闭的意思就是不推荐使用，为什么 MySQL 不推荐使用它自带的缓存呢？

主要是因为 MySQL 自带的缓存的应用场景有限，第一个是它要求 SQL 语句必须一模一样，例如中间多一个空格、字母大小写不同都被认为是不同的的 SQL。

第二个是表里面任何一条数据发生变化的时候，这张表所有缓存都会失效，所以对于有大量数据更新的应用，也不适合。

缓存这一块，我们一般交给 ORM 框架（比如 MyBatis 默认开启了一级缓存），或者独立的缓存服务，比如 Redis 来处理更合适。

在 MySQL 8.0 中，查询缓存已经被移除了。

（3）语法解析和预处理（Parser & Preprocessor）

①词法解析

词法分析就是把一个完整的 SQL 语句打碎成一个个的单词。
比如一个简单的 SQL 语句：

select name from user where id = 1;

它会打碎成 8 个符号，每个符号是什么类型，从哪里开始到哪里结束。

②语法解析

语法分析会对 SQL 做一些语法检查，比如单引号有没有闭合，
然后根据 MySQL 定义的语法规则，根据 SQL 语句生成一个数据结构。这个数据结构我们把它叫做解析树（select_lex）。

③预处理器

如果我写了一个词法和语法都正确的 SQL，但是语义有误。

预处理器检查语义正确。

比如表名或者字段不存在。会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。预处理之后得到一个新的解析树。

（4）查询优化与查询执行计划

①优化器

一条 SQL 语句可以有很多种执行方式，最终返回相同的结果，他们是等价的。对于选择最优的执行计划并执行，
即使用 MySQL 的查询优化器的模块（Optimizer）。

查询优化器的主要作用就是根据解析树生成不同的执行计划（Execution Plan），然后选择一种最优的执行计划。
注意：MySQL 里面使用的是基于成本（cost）的优化器，哪种执行计划成本最小，就用哪种。

除了执行路径的选择之外，优化器还可以对 SQL 语句进行自动优化。
如：

1.当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表（先访问哪张表）。
2、有多个索引可以使用的时候，选择哪个索引。
3、对于查询条件的优化，比如移除 1=1 之类的恒等式，移除不必要的括号，表达式的计算，子查询和连接查询的优化。

经过优化器处理之后，就会得到一条执行路径，我们把它叫做执行计划（execution_plans），执行计划也是一个数据结构。

（这个执行计划也不一定是最优的执行计划，因为 MySQL 也有可能覆盖不到所有的执行计划。）

②查询执行计划

在 SQL 语句前面加上 EXPLAIN，就可以看到执行计划的信息。

EXPLAIN select name from user where id=1；

如：查询类型、表名、分区、可能用到的索引、实际用到的索引、长度等等

（5）存储引擎

接下来执行引擎使用API调用存储引擎。

1.3 MySQL存储引擎

数据都放在表中。表中不仅存放数据，还要存储表结构定义信息。

因此，存储引擎又名表类型（Table Type）

存储引擎的使用是以表为单位的。

每一张表都可以指定自己的存储引擎，数据库中也支持很多种存储引擎。不同的存储引擎存放数据的方式不一样，产生的文件也不一样。（InnoDB是一个、Memory没有、Myisam是两个）

1.3.1 存储引擎选择

一张表的存储引擎，是在创建表的时候指定的，使用 ENGINE 关键字。

CREATE TABLE `user_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`gender` tinyint(1) DEFAULT NULL,
`phone` varchar(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `comidx_name_phone` (`name`,`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=utf8mb4;

没有指定的时候，数据库就会使用默认的存储引擎，5.5.5 之前，默认的存储引擎是MyISAM，5.5.5 之后，默认的存储引擎是 InnoDB。

1.3.2 存储引擎比较

（1）MyISAM（三个文件）

MySQL 自带的存储引擎，由 ISAM 升级而来。

特点：
支持表级别的锁（插入和更新会锁表）。不支持事务。
拥有较高的插入（insert）和查询（select）速度。
存储了表的行数（count 速度更快）。
（怎么快速向数据库插入 100 万条数据？我们有一种先用 MyISAM 插入数据，然后修改存储引擎为 InnoDB 的操作。）
适合：只读之类的数据分析的项目。

（2）InnoDB（2个文件）

特点：
支持事务，支持外键，因此数据的完整性、一致性更高。
支持行级别的锁和表级别的锁。
支持读写并发，写不阻塞读（MVCC）。
特殊的索引存放方式，可以减少 IO，提升查询效率。
适合：经常更新的表，存在并发读写或者有事务处理的业务系统。

（3）Memory （1 个文件）

特点：
把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失。只适合做临时表。
将表中的数据存储到内存中。
默认使用哈希索引。

（4）CSV （3个文件）

特点：不允许空行，不支持索引。格式通用，可以直接编辑，适合在不同数据库之间导入导出。

（5）Archive （2 个文件）

特点：不支持索引，不支持 update delete。

1.3.3 选择存储引擎原则

如果对数据一致性要求比较高，需要事务支持，可以选择 InnoDB。
如果数据查询多更新少，对查询性能要求比较高，可以选择 MyISAM。
如果需要一个用于查询的临时表，可以选择 Memory。

如果所有的存储引擎都不能满足你的需求，并且技术能力足够，可以根据官网内部手册用 C 语言开发一个存储引擎：
https://dev.mysql.com/doc/internals/en/custom-engine.html
按照这个开发规范，实现相应的接口，给执行器操作。

也就是说，为什么能支持这么多存储引擎，还能自定义存储引擎，表的存储引擎改了对 Server 访问没有任何影响，就是因为大家都遵循了一定了规范，提供了相同的操作接口。

1.4 执行引擎

执行器，或者叫执行引擎，它利用存储引擎提供的相应的 API 来完成操作（使用执行计划去操作存储引擎）。
最后把数据返回给客户端，即使没有结果也要返回。

2 MySQL体系结构总结

2.1 架构分层

总体上，我们可以把 MySQL 分成两层，执行操作的服务层（Server 层），和存储管理数据的存储引擎层。
Server 层跟存储引擎做的事情是完全不一样的。存储引擎主要负责数据的存取。对于数据的操作，过滤，计算这些都是在 Server 层。存储引擎再往下就是文件系统、硬件。

MySQL主要模块结构：

问题：一条更新sql是如何执行的？

其基本流程与查询流程基本一致。也要经过解析器、优化器的处理，最后交给执行器。区别就在于拿到符合条件的数据之后的操作。

2.2 InnoDB内存架构

2.2.1 缓冲池 Buffer Pool

InnoDB数据都放在磁盘上，是一个基于磁盘的存储引擎。

存储引擎要操作数据，必须先把磁盘里面的数据加载到内存里面才可以操作。

（1）并不是需要读取多大的数据，就从磁盘加载多大的数据。

无论是操作系统也好，还是存储引擎也好，都有一个预读取的概念。也就是说，当磁盘上的一块数据被读取的时候，很有可能它附近的位置也会马上被读取到，这个就叫做局部性原理。

InnoDB 设定了一个存储引擎从磁盘读取数据到内存的最小的单位，是page，叫做页。操作系统也有页的概念。操作系统的页大小一般是 4K，而在 InnoDB 里面，这个最小的单位默认是 16KB（16384 bytes）大小，它是一个逻辑单位。如果要修改这个值的大小，必须修改源码重新编译安装。

（2）使用了缓冲池（Buffer Pool）

操作数据的时候，每次都要从磁盘读取到内存（再返回给 Server），因此通过把读取过的数据页缓存起来，提高效率。

InnoDB 设计了一个内存的缓冲区。读取数据的时候，先判断是不是在这个内存区域里面，如果是，就直接读取，然后操作，不用再次从磁盘加载。如果不是，读取后就写到这个内存的缓冲区。

这个内存区域有个专属的名字，叫 Buffer Pool。

LRU原则（最近最少使用原则）

修改数据的时候，也是先写入到 buffer pool，而不是直接写到磁盘。内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。那脏页什么时候才同步到磁盘呢？

InnoDB 里面有专门的后台线程把 Buffer Pool 的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。

总结一下：Buffer Pool 的作用是为了提高读写的效率。

Buffer Pool 默认大小是 128M（134217728 字节），可以调整。

查看参数：

SHOW VARIABLES like '%innodb_buffer_pool%';

2.2.2 （redo）Log Buffer

因为刷脏不是实时的，如果 Buffer Pool 里面的脏页还没有刷入磁盘时，数据库宕机或者重启，这些数据就会丢失。

所以内存的数据必须要有一个持久化的措施。为了避免这个问题，InnoDB 把所有对页面的修改操作专门写入一个日志文件。
如果有未同步到磁盘的数据，数据库在启动的时候，会从这个日志文件进行恢复操作（实现 crash-safe）。我们说的事务的 ACID 里面 D（持久性），就是用它来实现的。

这个日志文件就是磁盘的 redo log（叫做重做日志）。

问题：同样是写磁盘，为什么不直接写到 db file 里面去？为什么先写日志再写磁盘？写日志文件和和写到数据文件有什么区别？

我们先来了解一下随机 I/O 和顺序 I/O 的概念。

如果我们操作的数据是随机分散在磁盘上不同页的不同扇区中，那么找到相应的数据需要等到磁臂旋转到指定的页，然后盘片寻找到对应的扇区，才能找到我们所需要的一块数据，进行此过程直到找完所有数据，这个就是随机 IO，读取数据速度较慢。

假设我们已经找到了第一块数据，并且其他所需的数据就在这一块数据后边，那么就不需要重新寻址，可以依次拿到我们所需的数据，这个就叫顺序 IO。

刷盘是随机 I/O，而记录日志是顺序 I/O（连续写的），顺序 I/O 效率更高。因此先把修改写入日志文件，在保证了内存数据的安全性的情况下，可以延迟刷盘时机，进而提升系统吞吐。

redo log 有什么特点？
1、redo log 是 InnoDB 存储引擎实现的，并不是所有存储引擎都有。支持崩溃恢复。是 InnoDB 的一个特性。
2、redo log 是物理日志，记录的是“在某个数据页上做了什么修改”。
3、redo log 的大小是固定的，前面的内容会被覆盖，一旦写满，就会触发 buffer pool到磁盘的同步，以便腾出空间记录后面的修改。

除了 redo log 之外，还有一个跟修改有关的日志，叫做 undo log。

redo log 和 undolog 与事务密切相关，统称为事务日志。

undo log tablespace
undo log（撤销日志或回滚日志）记录了事务发生之前的数据状态（不包括 select）。
如果修改数据时出现异常，可以用 undo log 来实现回滚操作（保持原子性）。在执行 undo 的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，属于逻辑格式的日志。
undo log的数据默认在系统表空间ibdata1文件中，因为共享表空间不会自动收缩，也可以单独创建一个 undo 表空间。

2.3 InnoDB磁盘结构

主要包含了表空间。

分为系统表空间、独占表空间、通用表空间、临时表空间、Redo log、Undo 表空间。

系统表空间包括：InnoDB 数据字典、双写缓冲（Doublewriter Buffer）、Change Buffer、Undo Logs。

2.3.1 双写缓冲

InnoDB的page大小【16k】与操作系统的page大小【4k】是不同的。因此需要写四次。

如果在InnoDB写操作还没完成的时候，系统宕机了。此时就发生了部分写失效。会发生数据丢失。

因此需要建立一个当前page的副本，使用副本对当前page做还原，再使用redo log 恢复故障。

这个副本就称为“双写缓冲”。

用于实现数据页的可靠性，避免数据页本身被破坏（导致使用redo log恢复不了）。

双写缓冲是顺序I/O的，效率较高。

2.3.2 独占表空间

当设置参数innodb_file_per_table = ON时，一张表会使用一个表空间，即“独占表空间”。一个表一个数据文件。

2.3.3 通用表空间

也是一个共享的表空间。

创建一个通用表空间：

create tablespace ts2673 add datafile '/var/lib/mysql/ts2673.ibd' file_block_size = 16k engine = innodb;

在创建一张表时，就可以指定这张表使用创建好的通用表空间。

create table t2673(id integer) tablespace ts2673;

且：通用表空间中的表是可以移动的。（可以将表移动到其他表空间中。）

删除通用表空间需要先删除掉所有通用表空间中的表。

2.3.4 临时表空间

对应文件夹ibtmp1中的文件。

2.3.5 Undo表空间

Undo表空间既可以放入系统表空间，也可以独立出来。

与Redo log一起，称为事务日志。

Redo log 实现事务的持久性。

Undo log 实现事务的原子性。其中记录的是事务发生之前的状态。在修改数据的过程中发生了异常，就可以进行回滚，恢复数据到未修改之前的状态。

redo log 和 undolog 与事务密切相关，统称为事务日志。

undo log tablespace
undo log（撤销日志或回滚日志）记录了事务发生之前的数据状态（不包括 select）。
如果修改数据时出现异常，可以用 undo log 来实现回滚操作（保持原子性）。在执行 undo 的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，属于逻辑格式的日志。
undo log的数据默认在系统表空间ibdata1文件中，因为共享表空间不会自动收缩，也可以单独创建一个 undo 表空间。

2.4 更新操作流程

update user set name = 'newName' where id=1;

1、事务开始，从内存（buffer pool）或磁盘(data file)取到包含这条数据的数据页，返回给 Server 的执行器；
2、Server 的执行器修改数据页的这一行数据的值为 newName；
3、记录 name=oldName到 undo log；
4、记录 name=newName到 redo log；
5、调用存储引擎接口，记录数据页到 Buffer Pool（修改 name=newName）；
6、事务提交。

2.5 后台线程

内存和磁盘之间，工作着很多后台线程。

后台线程的主要作用是负责刷新内存池中的数据和把修改的数据页刷新到磁盘。

后台线程分为：master thread，IO thread，purge thread，page cleaner thread。

2.6 Binlog（二进制日志）

除了 InnoDB 架构中的日志文件，MySQL 的 Server 层也有一个日志文件，叫做Binlog，它可以被所有的存储引擎使用。

binlog 以事件的形式记录了所有的 DDL 和 DML 语句。（逻辑日志——仅记录操作而非数据值）

binlog 有两个非常重要的作用：1、主从复制，2、数据恢复。

2.6.1 主从复制

主从复制的原理就是从服务器读取主服务器的 Binlog，然后执行一遍。

2.6.2 数据恢复

在开启了 Binlog 功能的情况下，我们可以把 Binlog 导出成 SQL 语句，把所有的操作重放一遍，来实现数据的恢复。

举例：每天凌晨 1 点做数据库的全量备份，上午 9 点，数据库被删除跑路了。
首先，恢复到凌晨 1 点的数据。然后利用 1 点到 9 点之间的 Binlog，提出 drop，恢复数据。

2.7 加入Binlog的更新操作流程

update teacher set name='盆鱼宴' where id=1;

1、先从内存或者磁盘拿到这条数据。
2、把 name 改成盆鱼宴，然后调用存储引擎的 API 接口，写入这一行数据到内存，
同时记录 redo log。这时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，
可以随时提交。
3、执行器收到通知后记录 binlog
4、然后调用存储引擎接口，设置 redo log 为 commit 状态。更新完成。