京东一面： MySQL写入一条数据的流程是怎样的？

前言

有位朋友去京东面试，被问到这么一道题: MySQL写入一条数据的流程是怎样的？

如果是我，我会按照这几个维度

• MySQL 基本架构
• 连接器
• 查询缓存
• 分析器
• 优化器
• 执行器
• Buffer Pool
• Undo Log
  
• Redo Log
• Binlog
• 事务两阶段提交
• 数据刷入磁盘

1. MySQL 基本架构

总体来说，MySQL大体分为两部分，分别是Server 层和存储引擎层。

Server 层

它包括连接器、查询缓存、分析器、优化器、执行器等。比如存储过程，触发器，视图都是在这一层实现的。

• 连接器Connection Manager）：负责处理客户端与服务器之间的连接。它接受来自客户端的请求，并进行身份验证和权限检查，建立和管理连接。
• 查询缓存（Query Cache）：在旧版 MySQL 中有，但在较新的版本中已不推荐使用。它能够缓存查询和对应的结果，以提高查询性能。然而，在高并发和大型数据库中，它反而可能成为性能瓶颈，因为它在某些情况下会引起锁和不必要的开销。
• 分析器（Parser）：负责分析 SQL 查询语句，验证其语法和语义，确保查询的正确性。它将 SQL 语句转换成内部数据结构供优化器和执行器使用。
• 优化器（Optimizer）：接收来自分析器的查询请求，并决定如何最有效地执行查询。优化器的目标是找到最佳的执行路径，选择合适的索引、连接顺序和访问方法，以提高查询性能。
• 执行器（Executor）：负责执行优化器生成的执行计划，获取存储引擎返回的数据，并处理客户端请求。它与存储引擎交互，执行查询并返回结果给用户。
• 存储引擎层: 它负责数据的存储和提取。Mysql支持InnoDB、MyISAM、Memory 等多个存储引擎。我们日常开发中，一般用的存储引擎就是InnoDB。从 MySQL 5.5 版本开始，InnoDB 就成为了默认的存储引擎。

介绍完MySQL基本架构，带大家看一下，每个组件，一条写入SQL,它主要做什么事~~

2.连接器

我们要执行写入SQL，一般在MySQL客户端, 需要输入连接命令，连接到MySQL服务端。在MySQL服务端，就是连接器负责跟你的客户端建立连接、获取权限、维持和管理连接。

连接命令如下：

mysql -h(ip地址) -P(端口) -u(用户名) -p

输入完连接命令之后，我们接着输入正确的密码，经过经典的TCP握手之后，就可以成功连接到MySQL服务器啦，如下：

C:\MySQL\MySQL Server 8.0\bin>mysql -h 127.0.0.1 -P 3306 -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

如果输入密码错误，则会收到一个 Access denied的错误信息，如下：

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -h 127.0.0.1 -P 3306 -u root -p  
Enter password: *****  
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

连接成功之后，大家就可以直接输入写入SQL，就可以看到结果啦。

    
mysql> insert into user_score_tab(user_id,score) values(888,10);
Query OK, 1 row affected (0.02 sec)

3. 查询缓存（Query Cache）

在5.6 及更早的MySQL版本中，连接成功后，会提供查询缓存，来优化查询SQL。如果你查询的表进行更新、插入的时候，会清空缓存的。因此，当你执行写入SQL，会清空缓存。

其实，MySQL比较新的版本，如8.0 已经彻底废弃了查询缓存。因为在高并发和大型数据库环境下，查询缓存可能导致性能问题，并且在实际测试中发现，禁用查询缓存可能会提高整体性能和可伸缩性。

4. 分析器

你扔个写入SQL给MySQL服务器，它肯定需要先解析，才知道这个SQL是做什么的，对吧。

它会派出分析器，先做词法分析。你提交过来的写入SQL是由很多个字符串和空格组成的，MySQL会先解析出这些字符串表示什么意思。比如这个插入SQL:

insert into user_score_tab(user_id,score) values(888,10); 

它会把关键字insert into解析出来，然后把user_score_tab解析成表，user_id、score解析成列名。做完词法分析之后，开始做语法分析。语法分析主要就是判断，你的SQL是否满足MYSQL的语法。

如果你的SQL写错了，语法分析就会报错误提示：ERROR 1064 (42000): You have an error in your SQL syntax;

mysql> inser into user_score_tab(user_id,score) value(888,10);
ERROR 1064 (42000): 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 'inser into user_score_tab(user_id,score) value(888,10)' at line 1 

平时大家看到这个错误的时候，只需要，关注关键词 syntax to use near 就可以快速知道哪里写错啦。比如这个例子，就是我的insert写错了，少了t。

做完词法分析和语法分析解析，就知道这是一条插入SQL。

5. 优化器

对于简单的插入SQL语句，优化器并不会执行复杂的查询计划生成工作。

优化器会处理索引的选择与维护，但并不涉及复杂的查询优化。如果表中存在主键或索引，优化器会确保索引的更新以保证数据一致性，并在插入数据时检查约束条件。

INSERT语句也会生成执行计划，它详细描述了数据库如何访问数据、使用哪些索引、以及数据的处理顺序等

6. 执行器

执行器负责执行具体的 SQL 操作，是数据库系统的核心执行模块。对于INSERT语句，执行器会负责实际的数据写入过程。

• 确定插入位置：根据优化器的执行计划，执行器会决定将数据插入表的具体位置，比如根据主键或唯一索引找到插入点。
• 加载数据页：如果要插入的数据页在内存（Buffer Pool）中，则直接使用；如果不在内存中，则需要从磁盘加载对应的数据页到内存。
• 更新索引：如果表中有索引（如主键、唯一索引或其他索引），执行器也会相应更新这些索引。

7. Buffer Pool

Buffer Pool是 MySQL InnoDB 存储引擎中的一块内存区域，专门用来缓存数据库表的数据页、索引页等内容。它的主要目的是提高数据读写性能，减少磁盘 I/O 操作.

• 数据写入内存中的数据页：执行器将新数据插入到Buffer Pool中的相应数据页。这是一个内存操作，而不是直接修改磁盘上的文件。

8. undo log

生成 Undo Log在真正插入数据之前，InnoDB 会生成undo log。对于插入操作，undo log 记录的是如何删除当前插入的记录（这用于事务回滚时撤销插入操作）。为什么生成 undo log？

在事务回滚时，MySQL 需要撤销未提交的操作。通过 undo log，MySQL 能够删除已经插入但未提交的记录，确保事务的原子性。

9. Redo Log

执行器在插入数据后，立即将这个操作记录在redo log中

• 写入 redo log：为了确保数据的可靠性，MySQL采用了预写日志（Write-Ahead Logging, WAL）机制。在数据真正写入磁盘前，首先会将这个操作记录在redo log中。

过程是怎样的呢？

MySQL首先将操作写入redo log，并标记为预提交（prepare）状态。这意味着如果崩溃，MySQL可以通过redo log将操作重做，从而恢复数据。

10. 写入 Binlog

• 写入 binlog：在写入 redo log 的同时，MySQL还会将这次操作写入binlog，用于数据库复制和灾难恢复。

binlog是 MySQL 的逻辑日志，记录了SQL操作细节，比如（INSERT INTO）。不同于redo log的物理日志。

11. 事务提交（两阶段提交）

在两阶段提交机制下，MySQL会在事务提交时更新 redo log 为commit状态。

为什么需要两阶段提交？

确保 binlog 和 redo log 的一致性。如果系统崩溃，MySQL可以通过 redo log 重做操作，并通过 binlog 进行恢复。

12. 数据刷入磁盘

执行器不会立即将内存中的脏页同步到磁盘。后台线程会根据一定的策略（如定时刷新等），异步地将 Buffer Pool 中的脏页刷到磁盘上的表空间文件中。- 这样可以避免频繁的磁盘 I/O 提高性能。