深入理解的第一步：理解Mysql的架构设计

探究技术的本质，追寻技术的乐趣，今天是我们深入Mysql的第一篇文章，我们通过Mysql是如何执行一条SQL语句，来理清楚MySQL的结构。那么接下来，我们开始吧！

上面的那张图片，展示了MySQL的全景图，总体而言，MySQL采用了C/S架构设计，客户端只负责发送指令和展示结果，大部分的功能都集中在Server端，而Server端也采用了我们都熟悉的分层架构，Server端具体被分为了两层，一个是Service层，主要负责管理客户端的连接，解析SQL，以及优化SQL，最后生成执行计划也就是完成一条SQL语句需要做哪些操作。然后这些操作的具体如何执行是由Storage层来完成的，而Storage层的设计很有意思，采用了插件式的设计，提供统一的抽象的数据存取的接口定义。由不同的存储引擎来实现这些读写接口，最终完成数据的读写。Storage层的设计有没有面向接口编程的味道呢？

相信通过上图，你也大致了解了MySQL的基本构成，接下来我们通过一个例子将这些基本组件串联起来。

我们假设在数据库中已经有了这样的表结构设计。

表的名字叫user,表的字段由userId,username,password,其中userId是主键。现在有一个业务，获取指定userId的用户信息，那么我们就会写出下面这样的SQL语句

select userId,username,password from user where userId=1001

我们就通过这条SQL语句，来看看MySQL是怎么运行的吧，要执行这条SQL语句，我们第一步就是要建立Client端与Server端的联系，也就是建立一个网络连接。对应的命令像这样 mysql -u root -p, 这样的客户端连接，会被连接器管理，连接器的工作就是维持这个连接，以及校验用户名和密码是否正确，以及读取这个用户的权限。当这些验证通过，连接器会看看缓存中是否存在这条SQL语句对应的结果集，值得注意的是，缓存功能在MySQL8.0就被完全移除了，后面会解释为什么。如果缓存没有命中，SQL语句就会被交给分析器，分析器的作用和编译器的作用差不多，进行词法分析和语义分析，简单来说就是理解这条SQL要干什么？，而优化器会根据这条SQL要完成的事情并结合表的结构，索引等等信息，做出具体的执行计划，也就是完成这条SQL要做的事情，需要哪些步骤。优化器将这些步骤罗列出来，形成一个计划清单，这就是执行计划。执行器拿到这个执行计划，按部就班的执行，在执行的过程中会调用Storage层提供的服务，也就是读写数据到磁盘的接口。不过在执行之前，会先判断用户有没有执行这些步骤的权限，如果没有就会被驳回。如果有，就会执行。具体的执行过程是这样的，由于这是一条查询语句，所以需要将数据从磁盘中加载到内存中，假设userId不是主键，那么执行引擎就是一行一行的比对数据，判断userId是否等于1001，直到遍历完整个数据集。这里之所以会假设userId不是主键，其实是和索引有关，这里先按下不表，后续会详细介绍。当获取到数据集之后，会在缓存中存一份数据，并把数据返回给客户端。这就是一次MySQL一次通信的过程。通过这次过程，我们将各个组件串联起来，知道了他们具体的责任是什么。这一期我介绍了查询语句如何执行的，下一期我们来聊聊更新语句如何执行的，并通过更新语句的执行，引出MySQL中非常重要的日志系统。那么我们下期见。

相信通过上图，你也大致了解了MySQL的基本构成，接下来我们通过一个例子将这些基本组件串联起来。

我们假设在数据库中已经有了这样的表结构设计。

表的名字叫user,表的字段由userId,username,password,其中userId是主键。现在有一个业务，获取指定userId的用户信息，那么我们就会写出下面这样的SQL语句

select userId,username,password from user where userId=1001

从更新语句中看日志系统

parse card fail

探究技术的本质，享受技术的乐趣！由于时间原因以及自己的原因导致拖更了，不过没关系，我保证后面每天一更，周末休息！好了，闲话少说，今天我们通过一个更新操作去认识一下Mysql的日志系统

首先我们先来看看我们的表结构以及更新语句的执行计划

sql

# 准备工作
CREATE TABLE user_info (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(32) NOT NULL,
    age TINYINT NOT NULL
);
insert into user_info (name,age) values ('pandaer',20)

# 更新语句
update user_info set age = age + 1 where id = 1

准备工作完成之后，我们先来回顾一下Mysql的架构设计，在Server端，一条SQL的语句的执行流程是这样的，SQL语句从连接器到达解析器，解析器进行一定的语法分析与语义分析知道了这条SQL是干什么的，然后优化器进行优化处理，并知道了为了执行这条SQL，应该怎么一步一步来做，将这些步骤组合成一个执行计划，并把这个执行计划交给执行器来做，执行器将具体的需要和磁盘打交道的操作委派给了存储引擎。这就是一条SQL的大致执行流程。更新语句的执行流程类似，只不过在执行过程中还会涉及到两个日志，一个是redo log 一个是bin log, 具体的过程是这样的：当SQL语句流转到执行器的时候，也就是SQL语句变成了一步一步的执行计划，SQL通过id的主键索引，利用范围查找，找到id=1的记录，并更新这条记录的age字段的值。执行计划如下

shell

mysql> explain UPDATE user_info SET age = age + 1 WHERE id = 1;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | user_info | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

就这么简单的过程就涉及到了 redo log,以及bin log,首先我们需要明确redo log和bin log的作用，redo log又叫重做日志，在存储引擎层，也就是并不是全部的存储引擎都有重做日志，目前只有InnoDB有重做日志，它的作用是缓解IO压力，提升系统性能。拿上面那个例子举例，当执行器将内存中的id=1的记录的age值从20改为21之后，就会调用InnoDB的相关的接口，将数据写入磁盘。但是这个写入过程并没有我们想象的那么简单，第一步我们需要找到这条记录在磁盘的位置，然后再写入。而如果记录很多，那么这个查找过程就有点慢了，如果同时又有很多请求来到数据库，那么数据库的压力就会很大，为了解决这个问题，InnoDB给出了重做日志的解决方案，这里需要补充一个知识，追加写的方式IO性能最好。所以当执行器调用存储引擎的写接口的时候，InnoDB只不过是记录了一条日志，即磁盘中的那一块数据发生了改变。当这个日志写入到重做日志之后，就会给执行器返回OK。即写操作完成。然后等到数据库压力不大的时候，在利用这个重做日志去更新真实的数据记录。接着我们再来说说bin log, bin log也叫归档日志，是Server层来支持的，这就意味着与存储引擎无关。归档日志的作用就是用来做备份以及主从同步的。为了实现这个目的，归档日志不得不在每条SQL语句执行完成之后，记录一下。那么就产生了一个问题，归档日志和重做日志的一致性问题。就比如当写完重做日志之后，Mysql崩溃了，那么归档日志和重做日志中的内容就不一致，这样就会导致，主从同步的时候，从节点的数据与主节点的数据不一致问题。为了解决这个问题，Mysql提出来两阶段提交的方式来解决。大致的逻辑是这样的：当执行器委派InnoDB执行写操作的时候，InnoDB记录一条日志到重做日志中后，将状态设置为prepare,然后执行器写完归档日志之后，InnoDB就会将状态设置为commit,利用这种两阶段提交的方式解决了一致性的问题。那么你肯定会问为什么呢？我们不妨分析一下这样做了之后，由几个时间点可能发生崩溃，可以看下方的图

崩溃时机1，很简单，都没有写入不存在一致性问题
崩溃时机2，重做日志中有记录，但是归档日志中没有记录，由于标记重做日志为prepare状态，所以在故障恢复的时候就知道存在问题，然后就可以根据归档日志中是否存在这条记录来判断是回滚还是直接设置为commit状态
崩溃时机3，和时机2一致。

你绕不过的坎：事务

探究技术的本质，享受学习的乐趣！今天我们来聊聊事务，任何东西，存在就有其道理，事务也不例外，那么为什么会存在事务呢？我们不妨从你已经听烂了的ACID说起，如果你忘记了什么是ACID，那么我来带你回顾一下

A 原子性
C 一致性
I 隔离性
D 持久性

这四个统称为事务的特性，但是这四个并不是同一个级别的概念，在我看来，一致性是事务的目的，保证原子性，持久性以及隔离性是手段。如此，我们就可以回答开篇提出的问题，为什么会存在事务，因为需要解决数据库数据一致性的问题，即数据符合预期。那么究竟哪些情况影响到了数据的一致性呢？我认为有两种，一个经常被拿来举例的例子就是转账，比如用户A给用户B转账，这个业务至少需要两步，用户A的余额减去一个值，用户B的余额加上一个值，如果用户A的值成功修改后，这个时候数据库崩溃了，那么就出现了数据不一致的情况，即没有符合预期。出现这种情况，是因为转账这个过程存在中间态，为了解决这个状态我们就需要保证事务的原子性，当然因为数据库具有持久性的特点，所以保证原子性的同时也必须保证持久性。这样似乎就保证了数据的一致性了，那为什么还需要保证事务的隔离性呢？隔离性与并发有关系，想象一个这样的场景，用户A给用户B转账，用户C也给用户B转账。即使我们保证了转账的原子性操作，但是用户B的余额存在数据竞争，只要存在数据竞争就会出现并发问题，而出现并发问题，最直接的解决办法就是加锁。事实上，隔离性在底层就是这样实现的。如果你了解过隔离性，你就一定知道一个隔离级别这个事情。之所以有隔离级别，其实就是一致性与性能的妥协，反应在实现上就是锁的粒度问题。关系型数据库都有这四种隔离级别

串行化
可重复读
读已提交
读未提交

这里不详细解释，一致性从上到下在减弱，性能从上到下在增强。这里从SQL的规范简单介绍一下不同隔离级别下锁的粒度，但是对应到具体的数据库，比如Mysql，在只读事务与写事务存在并发的时候，会使用MVCC，这类无锁算法提高性能，这个后面说。

针对串行化而言，就是多个事务只能串行执行，这个如果是加锁实现的话，一定是互斥锁。

针对可重复读而言，在每个事务中，在执行SQL语句的时候，就会开始加锁，不过这个锁是读写锁，根据SQL语句的类型加对应的锁，比如是查询操作就加读锁，是写操作就加写锁。但是释放锁的时机很特殊，即事务结束时释放，而不是执行完对应的SQL语句就释放。这样就实现了可重复读。但是可重复读存在一个问题，那就是幻读，之所以会幻读，就是在事务A执行过程中，事务B进行了增加记录的操作，而后事务A使用范围查询的时候就会读到这个新增的记录。

针对读已提交而言，就是在一个事务中，可以看到其他事务已经提交修改的数据。这个的加锁过程是这样的，针对写操作，同样是执行写操作的SQL语句时加锁，直到事务结束释放锁。而针对读操作就是执行读操作的SQL语句时加锁，执行完毕后解锁。正因如此，所以会出现不可重复读。

针对读未提交，就是在一个事务中，可以看到其他事务修改了但是没有提交的数据。这个的加锁过程是这样的，针对写操作和读已提交一样，但是针对读操作，是不加锁的读。比如事务A在进行写操作，那么就会加写锁，而写锁和读锁互斥，但是读未提交直接不加锁读，这样就肯定读到了脏数据，也被叫做脏读。

需要说明的是，针对隔离级别加锁的过程，是参照SQL规范来做的解释，对应到数据库就不一定了。比如Mysql，就会使用MVCC进行优化，MVCC 也叫多版本并发控制。它的核心思路是以空间换时间，你只需要知道使用了MVCC，那么一条记录就会对应多个版本。那么并发的事务可以基于这些版本进行操作，就间接的没有了数据竞争，没有竞争就没有伤害，就不存在并发的一致性问题了。有关MVCC更加详细的内容，我后面会详细展开聊聊，今天就到这里，我们下期见。

高效查询的秘诀：索引

探究技术的本质，享受技术的乐趣！今天我们来聊聊Mysql中的索引。根据万事万物，存在即合理这条不变的法则，我们抛出今天的第一个问题，为什么会出现索引？我们都知道数据库是用来持久化数据的，能够在需要的时候检索出我们需要的数据，然而随着数据越来越多，从海量的数据中查找出我们需要的数据，无异于大海捞针，费时又费力。那么索引就是用来解决这个问题的，索引能够让我们快速从一大堆数据中找到我们需要的。就像目录一样，可以快速定位。那么我们不妨想想我们学习的基础数据结构中，有哪些数据结构可以快速查找到我们需要的数据。如果足够熟悉数据结构，那么Hash表你一定能想到，因为他能够在O(1)的代价下找到你需要的值。那索引的底层实现就是Hash表？很显然不是，因为它存在一个缺陷，那就是范围查询需要遍历整个数据，而往往数据库中的数据是海量的，这不合适。那如果是有序数组呢？我们可以利用二分查找在O(logn)的代价下找到我们需要的值，又因为有序，针对范围查询也不过是O(logn)的代价，这很优秀，但是数组也有一个致命的问题，插入和删除某个元素，需要挪移位置，这代价很大，最坏的时候能够达到O(n)。拥有二分特点，而且对增删友好的数据结构就只有二叉搜索树了。二叉搜索树同样可以借助数组的思路寻找起始位置，如果能够一直维持树的平衡，代价就是O(logn),然后从这里开始中序遍历直到遇到比终止位置大的数。同样增删的操作代价也是O(logn)。这个数据结构很好，等值查询效率也高，范围查询效率也高，而且插入和删除的代价也不是很高。然而他有一个致命的缺陷对磁盘不友好。这和磁盘的原理有关，要将磁盘中数据读入内存中，只能一个一个数据块的读取，如果是二叉搜索树，那么一个数据块的数据就很少，就需要读取多个数据块，而从磁盘读数据到内存这个过程耗时巨大。所以尽管二叉搜索树很完美了，但是针对数据库这个场景还不够好，于是人们利用N叉树来解决这个问题。这个N叉树的子节点从左到右按顺序排列的。而Mysql采用的数据结构就是N叉树的一种B+树。

B+树的具体结构你可以百度出来，这里就不赘述了，在Mysql中索引分为两种，一种是主键索引，一种是非主键索引。主键索引是由B+树根据主键构建出来的，叶子节点是一条条完整的记录，也被成为聚簇索引。由于索引中就有完整的记录，也被叫做索引组织表。而非主键索引仍然是由B+树构建，唯一不同的是，叶子节点不是完整的记录，而是对应主键的值。换句话说如果利用非主键索引查找记录，需要进行两次查找过程，第一次从非主键索引中找到记录的主键值，再从主键索引中根据主键值找到对应的记录，这个过程也被叫做回表。这也是为什么很多Mysql的最佳实践中要求尽量使用主键查询。

好今天的内容就到这里，下一期我们接着聊索引。我们下期见

深入理解的第一步：理解Mysql的架构设计 ​

从更新语句中看日志系统 ​

你绕不过的坎：事务 ​

高效查询的秘诀：索引 ​

深入理解的第一步：理解Mysql的架构设计

从更新语句中看日志系统

你绕不过的坎：事务

高效查询的秘诀：索引