mysql架构篇

参考文章

mysql日志系统[等待完成]

错误日志

二进制日志

通用查询日志

慢查询日志

mysql分层

MySQL分为两层：Server层 和 存储引擎层

server 层

负责建立和维护客户端连接、SQL分析、权限校验、SQL执行等功能。

主要功能模块: 连接器—>查询缓存—>分析器—>预处理器—>优化器—>执行器等。

还包括其他跨存储引擎的功能，比如函数、存储过程、触发器等。

存储引擎层

目前MySQL支持的存储引擎有 InnoDB、MyISAM、Memory 等。

索引、锁（部分，比如行锁）、事务等功能都是由存储引擎实现的。

Server层

连接器

mysql -h$IP -P$port -u$USER -p

-- 当你通过上面方式去连接MySQL的时候，由于MySQL是基于TCP协议进行传输数据的，所以会先经历TCP三次握手。当TCP连接建立以后，MySQL会校验你的密码是否正确，如果密码错误你会收到如下错误：

MySQL Error 1045: Access denied for user 'root'@'127.0.0.1' (using password: YES)

-- 密码校验通过之后，紧接着会去权限表查询当前用户的权限，然后将权限保存在内存中，方面下次SQL交互时进行权限验证。

注意：因为在连接建立时MySQL就把用户权限查询到内存中了，并且后续的请求权限校验都是在内存中进行的。所以当连接建立以后即使更改了当前用户的权限，也不会影响当前用户已经建立的连接。

查看有多少连接

通过show processlist ; 语句查询目前有多少存在连接。

mysql> show processlist ;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  2 | root | localhost | NULL | Sleep   |   35 |          | NULL             |
|  3 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

其中Command列为Sleep 值，就表示是一个空闲连接，Time列的值表示空闲了多长时间。

---

以下是mysql服务端如何主动关闭连接

手动关闭连接

mysql> kill connection +2;
Query OK, 0 rows affected (0.00 sec)

自动关闭连接

MySQL主动断开连接，由参数 wait_timeout 控制，默认是8小时，当一个连接空闲时间超过wait_timeout 时，MySQL就会主动断开这个连接。

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

MySQL主动断开的连接不会通知客户端，只是当客户端使用该连接重新操作时会收到如下错误

mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6

MySQL连接上限

show variables like 'max_connections';

输出结果如下：

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

当超过最大连接数时，系统会拒绝新的连接请求，并响应错误Too many connections 。

长连接 和 短连接

MySQL的连接是基于TCP协议的，并且建立连接后会把用户权限查询到内存中，如果我们每执行一条SQL就重新创建连接的话是非常消耗资源的，所以推荐使用长连接

MySQL在执行查询过程中使用的内存并不是查询结束就清理了，而是在断开连接的时候才会释放内存。所以如果长连接过多可能会导致MySQL内存占用过大，然后被系统强制杀掉，现象就是MySQL异常重启。

解决长连接占用内存问题

1：定期断开长连接。既然在断开连接的时候MySQL会释放内存，那我们只需要定期去清理那些空闲的长连接就行了。

2：MySQL 5.7 以后提供了一个重置连接的函数mysql_reset_connection() ，这个函数不会断开重新连接，而是将连接重置到刚建立连接时的状态。所以我们可以在执行完一个大操作以后，调用一下这个函数。

注意，这个函数是编程语言去调用

总结

• 建立和维护客户端的连接
• 建立连接时会将用户权限读取到内存中，后续校验权限的时候从内存中获取权限，所以更改用户权限不会对已经建立好的连接生效。
• 一般情况下建议使用长连接，避免频繁的创建连接和校验权限。但是要注意长连接内存占用问题，要么定期清理空闲的长连接，要么执行大操作以后调用mysql_reset_connection() 函数

查询缓存

查询缓存功能在MySQL 8.0以后已经没有了

当咱们通过连接器建立连接以后，接下来就是执行SQL了。如果SQL是查询语句，MySQL会先查询缓存。如果命中缓存，在返回数据之前会做一次权限校验，权限校验通过以后把数据返回给客户端。

如果没有命中缓存，则会继续往下执行，当拿到结果以后会将结果放进缓存。SQL作为缓存的key，结果作为缓存的value。

疑问：

1. 为什么在返回数据之前做权限校验？

   因为查询缓存是所有用户所有连接共享的。有可能用户A对表t有查询权限，但是用户B没有，所以需要做权限校验。

2. 为什么不在查询缓存之前做?

   因为在查询缓存之前没有经过分析器，MySQL这时候是无法识别出表和字段，所以也就无法做权限校验。

3. 为什么MySQL要删除查询缓存功能？

   因为查询缓存失效非常频繁，只要对表有更新，无论是数据更新还是表结构更新，都会导致该表的所有缓存被清空。

手动关闭查询缓存后，也可以在查询的时候使用SQL_CACHE显示指定使用查询缓存。示例如下：

select SQL_CACHE id from t where id > 1;

分析器

主要有两个功能：词法分析 和 语法分析。

词法分析就是把SQL根据空格拆分成一个个字符串，然后识别出每个字符串代表什么。example:

select id from t where id > 1;

MySQL会将select from where识别为语法关键字，从而得知这条SQL是一条查询语句；把id 识别为需要查询的表字段；t 识别为表名；

语法分析会把上面解析出来的结果，根据语法规则检查SQL语句是否正确，如果SQL错误就会收到错误提示。如下所示：

ERROR: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'form user' at line 1

表不存在或者字段不存在，不是在解析器里做的

预处理器

这时候MySQL会做一些预处理操作，其中包括检查表和字段是否存在，以及权限校验。

mysql> select * from tt;
ERROR 1146 (42S02): Table 'gin.tt' doesn't exist

校验完表和字段以后，这里MySQL已经拿到表和字段信息了，所以接着会做一次权限校验，检查用户对表或者字段是否有操作权限。

优化器

example:

select id, name, age from user where name = 'hello' and age < 18;

如果表中有两个索引，一个是name字段的索引，还有一个(name, age)的组合索引，优化器会选择使用哪个索引？还是使用全表扫描？选择索引，这就是优化器的作用。

执行器

在开始执行之前，MySQL还有再做一次权限校验。确保用户对本次操作所涉及的所有表有操作权限。

为什么要再做一次权限校验

虽然在预处理器阶段已经做了权限校验了，但是某些操作可能在预处理阶段是无法知道的，比如触发器。

总结

一条SQL语句执行步骤：

1. 连接器：建立和维护连接、校验用户信息（密码、登录权限、主机权限）。
2. 查询缓存：使用SQL语句为key查询value为结果，如果命中缓存还会校验权限（8.0以后没了）。
3. 分析器(解析sql）：词法分析和语法分析，把SQL语句拆分得到关键字、表名、字段等信息，然后根据语法规则校验语法是否正确。
4. 预处理器（执行sql）：检查表和字段是否存在，以及校验用户对表和字段是否有操作权限。
5. 优化器（执行sql）：选择索引，以及多表join时，选择join顺序。
6. 执行器（执行sql）：校验用户对操作所有涉及的表是否有权限（包括触发器涉及的表），调用存储引擎的接口操作数据，将操作结果返回客户端。

存储引擎层

1. 采用的是插拔式的插件方式；
2. 存储引擎是指定在表之上的，即一个库中的每一个表都可以指定选择存储引擎；
3. 不管表采用什么样的存储引擎，都会在数据区，产生对应的一个.frm文件（表结构定义描述文件）

查看存储引擎

show engines;

查看数据文件位置

mysql> show variables like 'datadir';

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+--------

查看表数据文件位置

root@LAPTOP-HI3T64V4:/var/lib/mysql/test# pwd
/var/lib/mysql/test   								-- 数据库test的所有表存放目录


root@LAPTOP-HI3T64V4:/var/lib/mysql/test# ls -l
total 112
-rw-r----- 1 mysql mysql    65 Feb 28 13:27 db.opt
-rw-r----- 1 mysql mysql  8556 Feb 28 13:28 user.frm
-rw-r----- 1 mysql mysql 98304 Feb 28 13:28 user.ibd

InnoDB存储引擎

在 MySQL 5.5 及以后版本后，MySQL 选择使用 InnoDB为默认存储引擎。在创建数据库表时，不指定存储引擎时，使用的就是 InnoDB。如需使用其他存储引擎，可以手动来指定。

特点：

1. InnoDB 支持事务操作；（每一条SQL都默认封装成事务，自动提交，会影响速度）
2. InnoDB 支持外键；
3. InnoDB 是聚集索引（聚簇索引）；
4. InnoDB 不保存表的总条数；
5. InnoDB 5.7版本之前不支持全文检索；
6. InnoDB 支持表级锁、行级锁，默认为行级锁；
7. InnoDB 表必须有主键（如果我们没有明确去指定创建主键索引。它会帮我们隐藏的生成一个 6 byte 的 int 型的索引作为主键索引）；
8. InnoDB 文件存储方式为.frm文件存储表结构，ibd文件存储数据内容。

聚簇索引（Clustered Index）

• 数据存储: 在聚簇索引中，表中的数据行物理上按索引键（Key）的顺序存储。这意味着，聚簇索引的叶子节点直接包含了表中的数据行。因此，在一个表中，只能有一个聚簇索引，因为你不能以两种不同的顺序来物理存储同一组数据行。
• 主键索引: 在 InnoDB 存储引擎中，聚簇索引通常就是表的主键索引。如果表定义了主键，InnoDB 就会使用这个主键作为聚簇索引；如果没有定义主键，MySQL 会选择一个唯一索引代替；如果这些都不存在，MySQL 会生成一个隐藏的唯一ID作为聚簇索引。
• 性能优势: 聚簇索引能提供快速的数据访问速度，因为索引搜索可以直接定位到包含数据的叶子节点。对于按索引顺序的扫描操作，聚簇索引也非常高效。
• 更新成本: 由于数据是按照聚簇索引的顺序存储的，因此在插入或更新数据时，如果需要改变数据的物理顺序，这可能导致数据页的分裂或合并，增加了维护索引的成本。

非聚簇索引（Non-Clustered Index）

• 数据存储: 非聚簇索引的叶子节点并不直接存储数据行。相反，它们包含索引键和对应数据行的指针（在 InnoDB 中是主键的值），这意味着非聚簇索引需要通过索引键找到主键，然后再通过主键定位到实际的数据行。
• 多个索引: 一个表可以拥有多个非聚簇索引，因为它们不影响数据的物理存储顺序。
• 辅助索引: 在 InnoDB 中，非聚簇索引有时也被称为辅助索引（Secondary Index）。它们为数据提供了另一种检索路径，有助于提高查询的性能，尤其是对那些不涉及聚簇索引键的查询。

.frm 和 .ibd 格式文件介绍.frm 和 .ibd 格式文件介绍

.frm 文件

• 作用：.frm 文件用于存储表的结构定义信息。这包括了表的列定义（比如列名、数据类型）、索引信息、表的约束（如主键、外键约束）等元数据信息。
• 每个表一个：在 MySQL 中，每个表都会有一个对应的 .frm 文件来描述该表的结构。
• 版本兼容性：.frm 文件的格式设计得相对独立，这意味着即使在不同版本的 MySQL 之间迁移数据，只要 .frm 文件没有损坏，就能被识别和使用。

.ibd 文件

• 作用：.ibd 文件是 InnoDB 存储引擎的表空间文件，用于存储表的实际数据和索引数据。这包括了行数据、B+树索引等。
• 文件格式：.ibd 文件是 InnoDB 特有的，它支持 InnoDB 的高级特性，如事务、行级锁定、外键等。
• 表空间配置：默认情况下，InnoDB 存储引擎会为每个表创建一个独立的 .ibd 文件（这是在 MySQL 5.6.6 以后的版本中引入的“文件-每表”表空间模式）。但是，也可以配置 InnoDB 使用共享表空间模式，这种情况下，多个表的数据可能存储在同一个 .ibd 文件或几个 .ibd 文件中。

内存结构

参考文章

缓冲池 Buffer Pool

InnoDB使用了一种缓冲池的技术，也就是把磁盘读到的页放到一块内存区域里面。这个内存区域就叫Buffer Pool。

修改数据的时候，先修改缓冲池里面的页。内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。InnoDB里面有专门的后台线程把Buffer Pool的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。

修改缓冲 Change Buffer

如果这个数据页不是唯一索引（注：唯一索引就是在同一字段下不能有相同值），也就不需要从磁盘加载索引页判断数据是不是重复（唯一性检查）。这种情况下可以先把修改记录在内存的缓冲池中，从而提升更新语句（Insert、Delete、Update）的执行速度。

自适应哈希索引

InnoDB会根据访问的频率和模式，为热点页建立哈希索引，来提高查询效率。InnoDB存储引擎会监控对表上各个索引页的查询，如果观察到建立哈希索引可以带来速度上的提升，则建立哈希索引，所以叫做自适应哈希索引。

日志缓冲

MySQL 在更新数据时，为了减少磁盘的随机 IO，因此并不会直接更新磁盘上的数据，而是先更新 Buffer Pool 中缓存页的数据，等到合适的时间点，再将这个缓存页持久化到磁盘。而 Buffer Pool 中所有缓存页都是处于内存当中的，当 MySQL 宕机或者机器断电，内存中的数据就会丢失，因此 MySQL 为了防止缓存页中的数据在更新后出现数据丢失的现象，引入了 redo log 机制。

当进行增删改操作时，MySQL 会在更新 Buffer Pool 中的缓存页数据时，会记录一条对应操作的 redo log 日志，这样如果出现 MySQL 宕机或者断电时，如果有缓存页的数据还没来得及刷入磁盘，那么当 MySQL 重新启动时，可以根据 redo log 日志文件，进行数据重做，将数据恢复到宕机或者断电前的状态，保证了更新的数据不丢失，因此 redo log 又叫做重做日志。它的本质是保证事务提交后，更新的数据不丢失。——用它来实现事务的持久性。

磁盘文件表空间 [等待完成]

参考文章

系统表空间

共享表空间是由ibdata1文件（或者配置中指定的其他文件）表示的，它是系统的默认设置，在这种配置下，所有InnoDB表的数据和索引会被存储在一个共享的文件中。这种设计在早期版本的MySQL中更常见。共享表空间的优点是简化了文件管理，但它也有缺点，如恢复单个表变得更复杂，而且随着数据的增加，ibdata1文件可能会变得非常大，并且即使删除了数据，文件大小也不会减小。

独占表空间

独占表空间是指每个InnoDB表都使用自己的.ibd文件来存储数据和索引的配置。这是通过设置innodb_file_per_table参数为1来实现的，从MySQL 5.6.6版本开始，这个参数的默认值就是1。独占表空间提供了更好的数据管理灵活性，如更易于表级别的备份和恢复，以及更高效的磁盘空间使用，因为删除表会立即回收空间。

磁盘文件存储结构

从 InnoDB 逻辑存储结构来看，所有的数据都被逻辑的存放在一个空间中，这个空间就叫做表空间（tablespace）。表空间有 段（segment）、区（extent）、页（page）组成。

2.1、段（segment）
段(Segment)分为索引段，数据段，回滚段等。其中索引段就是非叶子结点部分，而数据段就是叶子结点部分，回滚段用于数据的回滚和多版本控制。一个段包含256个区(256M大小)。

2.2、区（extent）
区是页的集合，一个区包含64个连续的页，默认大小为 1MB (64*16K)。

2.3、页（page）
页是 InnoDB 管理的最小单位，常见的有 FSP_HDR，INODE, INDEX 等类型。所有页的结构都是一样的，分为文件头(前38字节)，页数据和文件尾(后8字节)。页数据根据页的类型不同而不一样。

默认每个页的大小为 16KB，也就是最多能保证 16KB 的连续存储空间。

内存数据落盘[等待完成]

1. 改内存: 通过事务，修改内存的数据，形成脏页。

   日志记录完整修改的过程。事务提交，必须将记录的日志顺序写入磁盘!

2. CheckPoint:同步内存的脏页到磁盘的数据机制

日志写道磁盘中不是IO么？？为什么他就没事，其他写道磁盘就必须用检查点机制?