重温Mysql及部分原理挖掘

矫情吗；* 2023-07-20 12:25 1阅读 0赞

## MySQL 的基本架构 ##

mysql> select * from T where ID=10；

一句sql的执行流程：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNDA0Mzk1_size_16_color_FFFFFF_t_70_pic_center]

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括`连接器、查询缓存、分析器、优化器、执行器`等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如`日期、时间、数学和加密函数`等）。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 `InnoDB、MyISAM、Memory` 等多个存储引擎。现在最常用的存储引擎是 `InnoDB`，它从 `MySQL 5.5.5`版本开始成为了默认存储引擎。

--------------------

## binlog & redo log ##

*  `redo log` 是 `InnoDB` 引擎特有的；`binlog` 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
 *  `redo log` 是物理日志，记录的是“在某个数据页上做了什么修改”；`binlog` 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
 *  redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件`写到一定大小后会切换到下一个，并不会覆盖以前的日志`。

--------------------

## 执行update的流程 ##

`update T set c=c+1 where ID=2;`

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNDA0Mzk1_size_16_color_FFFFFF_t_70]

*  执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。
 *  如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
 *  执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
 *  引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
 *  执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
 *  执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

--------------------

## 自增主键的好处（`InnoDB`） ##

*  自增主键的插入数据模式，是递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶`子节点的分裂`。
 *  主键索引树的叶子节点存储的是整行记录，而其他索引树的叶子节点存的主键的值，当使用自增主键的话，一般占用的空间会比业务主键小，那么普通索引占用的空间也就越小。（`根据主键索引的查询，只需要查询一棵树，而根据非主键索引查询，会先得到主键值，然后再到主键索引获取表数据`）

--------------------

## 事务隔离的实现 ##

###### `多版本并发控制（MVCC）` ######

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。  
假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNDA0Mzk1_size_16_color_FFFFFF_t_70_pic_center 1]  
当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。

--------------------

## 主键索引 & 非主键索引 ##

`主键索引`的叶子节点存的是`整行数据`。在 InnoDB 里，`主键索引`也被称为`聚簇索引`（clustered index）。  
`非主键索引`的叶子节点内容是`主键的值`。在 InnoDB 里，`非主键索引`也被称为`二级索引`（secondary index）。

**注意**：也就是说，基于`非主键索引`的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。`主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小`。

--------------------

## mysql有哪些锁 ##

全局锁、表锁、行锁

--------------------

## 全局锁 ##

全局锁就是对整个数据库实例加锁。命令是：`FTWRL`（Flush tables with read lock）。典型使用场景是，做`全库逻辑备份`。`FTWRL` 前有读写的话 ,`FTWRL` 都会等待 读写执行完毕后才执行。

--------------------

## 表级锁： ##

MySQL 里面表级别的锁有两种：一种是`表锁`，一种是`元数据锁`（meta data lock，MDL)。

*  `表锁`的语法是 `lock tables … read/write`，可以用 `unlock tables` 主动释放锁，也可以在客户端断开的时候自动释放。
 *  `元数据锁`(MDL)不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。  
    `在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。`读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，当无法获取到MDL锁时，对应线程会处于阻塞状态，用来保证变更表结构操作的安全性。

--------------------

## 行锁 ##

`行锁`就是针对数据表中行记录的锁。MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 `MyISAM` 引擎就不支持行锁。这也是 `MyISAM` 被 `InnoDB` 替代的重要原因之一。  
在 `InnoDB` 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。

--------------------

## ☆☆☆思考： ##

mysql> CREATE TABLE `t` (
      `id` int(11) NOT NULL,
      `k` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;
    insert into t(id, k) values(1,1),(2,2);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNDA0Mzk1_size_16_color_FFFFFF_t_70_pic_center 2]  
**事务A、B所查询到的k值是多少呢？（`默认 autocommit=1`）**

这个问题就涉及到了控制事务隔离级别的`MVCC`相关知识了，大家都知道事务的隔离级别（`可重复读`和`读已提交`）实现在于事务会去查询不同版本的视图，所以取到的结果也不一样。

`begin/start transaction` 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 `start transaction with consistent snapshot` 这个命令。

第一种启动方式，`一致性视图`是在执行第一个快照读语句时创建的；  
第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot 时创建的。

事务 C 没有显式地使用 `begin/commit`，表示这个 `update` 语句本身就是一个事务，语句完成的时候会自动提交。

在`可重复读隔离级`别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；（除非内部有更新操作也会更新一致性视图）  
在`读已提交`隔离级别下，每一个语句执行前都会重新算出一个新的视图（获取到已经提交过得的数据）。

**所以答案是：**  
`读已提交`的情况下， 事务A、B所查询到的k值是 2、3；  
`可重复读`的情况下， 事务A、B所查询到的k值是 1、3；

--------------------

## 前缀索引 ##

-- 使用整个email字段作为索引
    alter table SUser add index index1(email);
    
    -- 前缀索引，使用email的前6位作为索引
    alter table SUser add index index2(email(6));

**优点：**  
前缀索引相对比较节省索引树的空间  
**缺点：**  
由于区分度相比与整个字符串的索引要低一写，所以可能会增加查询扫描的行数；  
还有就是会影响覆盖索引的使用，无法直接从索引树取到想要查询的结果。

--------------------

## mysql突然抖动原因之一 ##

`InnoDB` 在处理更新语句的时候，只做了写日志这一个磁盘操作。这个日志叫作 `redo log`。在更新内存写完 `redo log` 后，就返回给客户端，本次更新成功，所以更新效率很高。此时就可能出现`磁盘`的数据和`redo log`的数据不一致，Mysql会自行将`redo log`中的变化更新到磁盘中，这个操作在Mysql中叫做`flush`。

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

不难想象，平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（`flush`）。

现在你知道了，InnoDB 会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到 MySQL“抖”了一下的原因。

--------------------

## 关于count()函数的用法和性能比较 ##

**`count()` 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。**

*  `count(id)` ，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
 *  `count(1)` ，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
 *  `count(字段)` ，如果这个“字段”是定义为 `not null` 的话，一行行地从记录里面读出这个字段，按行累加；如果这个“`字段”`定义允许为 `null`，那么执行的时候，判断到有可能是 `null`，还要把值取出来再判断一下，不是 `null` 才累加。
 *  `count(*)` 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(\*) 肯定不是 null，按行累加。

\*\*结论：\*\*性能比较结果：`count(字段)<count(主键 id)<count(1)≈count(*)`，所以建议，尽量使用 `count(*)`进行统计。

--------------------

## redo log 一般设置多大？ ##

回答：`redo log` 太小的话，会导致很快就被写满，然后不得不强行刷脏页（`flush`），这样 `WAL` 机制的能力就发挥不出来了。所以，如果是现在常见的几个 TB 的磁盘的话，就不要太小气了，直接将 `redo log` 设置为 `4 个文件`、`每个文件 1GB` 吧。

--------------------

## `order by`排序相关的执行流程 ##

CREATE TABLE `t` (
      `id` int(11) NOT NULL,
      `city` varchar(16) NOT NULL,
      `name` varchar(16) NOT NULL,
      `age` int(11) NOT NULL,
      `addr` varchar(128) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `city` (`city`)
    ) ENGINE=InnoDB;

查询语句：`select city,name,age from t where city='杭州' order by name limit 1000` ;

为了说明这个 SQL 查询语句的执行过程，我们先来看一下 city 这个索引的示意图。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNDA0Mzk1_size_16_color_FFFFFF_t_70_pic_center 3]

从图中可以看到，满足 `city='杭州’`条件的行，是从 `ID_X` 到 `ID_(X+N)` 的这些记录。其执行流程为：

*  1、初始化 `sort_buffer`，确定放入 `name、city、age` 这三个字段；
 *  2、从索引 `city` 找到第一个满足 `city='杭州’`条件的主键 id，也就是图中的 ID\_X；
 *  3、到主键 `id` 索引取出整行，取 `name、city、age` 三个字段的值，存入 `sort_buffer` 中；
 *  4、从索引 `city` 取下一个记录的主键 `id`；
 *  5、重复步骤 3、4 直到 `city` 的值不满足查询条件为止，对应的主键 id 也就是图中的 `ID_Y`；
 *  6、对 `sort_buffer` 中的数据按照字段 `name` 做快速排序；
 *  7、按照排序结果取前 1000 行返回给客户端。

执行流程的示意图如下所示:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNDA0Mzk1_size_16_color_FFFFFF_t_70_pic_center 4]  
图中“按 name 排序”这个动作，可能在内存中完成，也可能需要使用外部排序(所需空间大于`sort_buffer`的时候)，这取决于排序所需的内存和参数 `sort_buffer_size`。

--------------------

## 幻读 ##

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行（别的事务在期间新增的行）。隔离级别为`可重复读`的情况，可能出现幻读；

**需要对“幻读”做一个说明：**  
在`可重复读`隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“`当前读（可理解为更新类型的读，在更新时会先一次查询再更新）`”下才会出现。

--------------------

## 如何解决幻读？ ##

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是`间隙锁 (Gap Lock)`。

`间隙锁`，锁的就是两个值之间的空隙。比如初始化插入了 6 个记录，这就产生了 7 个间隙。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNDA0Mzk1_size_16_color_FFFFFF_t_70_pic_center 5]  
当你开启一个事务执行查询的时候，就不止是给数据库中已有的 6 个记录加上了`行锁`，还同时加了 7 个`间隙锁`。这样就确保了无法再插入新的记录。

`间隙锁`跟我们之前碰到过的锁都不太一样。`间隙锁`是防止“往这个间隙中插入一个记录”，间隙锁之间都不存在冲突关系（`即可能存在一个间隙之间加了多把间隙锁`）。

--------------------

## MySQL有哪些常见的影响性能问题？ ##

*  **短连接风暴**:  
    正常的短连接模式就是连接到数据库后，执行很少的 SQL 语句就断开，下次需要的时候再重连。如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。`MySQL 建立连接的过程，成本是很高的。除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限`。
 *  **慢查询性能问题**:  
    会引发性能问题的慢查询，大体有以下三种可能：  
    1、索引没有设计好；  
    2、SQL 语句没写好；  
    3、MySQL 选错了索引。
 *  **QPS 突增问题**：  
    由于业务突然出现高峰，或者应用程序 bug，导致某个语句的 QPS 突然暴涨，也可能导致 MySQL 压力过大，影响服务。

--------------------

## binlog 的写入机制 ##

`binlog` 的写入逻辑比较简单：事务执行过程中，先把日志写到 `binlog cache`，事务提交的时候，再把 `binlog cache` 写到 `binlog` 文件中。

一个事务的 `binlog` 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了 `binlog cache` 的保存问题。

系统给 `binlog cache` 分配了一片内存，每个线程一个，参数 `binlog_cache_size` 用于控制单个线程内 `binlog cache` 所占内存的大小。如果超过了这个参数规定大小的数据，就要暂存到磁盘。

事务提交的时候，执行器把 `binlog cache` 里的完整事务写入到 `binlog` 中，并清空 `binlog cache`。`每个线程有自己 binlog cache，但是共用同一份 binlog 文件。`

--------------------

## MySQL是主备一致的实现流程？ ##

`备库`跟`主库` 之间维持了一个长连接。主库内部有一个线程，专门用于服务备库的这个长连接。

一个事务执行，`主库`和`备库`中间发生了什么？

*  在`备库` 上通过 `change master` 命令，设置`主库` 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含`文件名和日志偏移量`。
 *  在`备库` 上执行 `start slave` 命令，这时候备库会启动两个线程，`io_thread` 和 `sql_thread`。其中 io\_thread 负责与主库建立连接。
 *  `主库` 校验完用户名、密码后，开始按照`备库` 传过来的位置，从本地读取 binlog，发给 `备库`。
 *  `备库` 拿到 binlog 后，写到本地文件，称为`中转日志（relay log）`。
 *  `sql_thread` 读取中转日志，解析出日志里的命令，并执行。
 *  备份完成！

--------------------

## JOIN查询语句 ##

-- 假设在表 t1和t2上的字段a都是有索引的
    select * from t1 straight_join t2 on (t1.a=t2.a);

如果直接使用 `join` 语句，MySQL 优化器可能会选择表 t1 或 t2 作为驱动表，这样会影响我们分析 SQL 语句的执行过程。为了便于分析执行过程中的性能问题，我改用 straight\_join 让 MySQL 使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去 join。在这个语句里，t1 是驱动表，t2 是被驱动表。

这个语句的执行流程是这样的：

*  从表 t1 中读入一行数据 R；
 *  从数据行 R 中，取出 a 字段到表 t2 里去查找；
 *  取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分；
 *  重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。

**结论（前提是“可以使用被驱动表的索引”）：**

*  join 语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。
 *  使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；
 *  如果使用 join 语句的话，需要让小表做驱动表。

**提示：**  
`但是，当被驱动表没有走索引的时候，那么驱动表和被驱动表的选择就不重要了，而且效率也会相当低，不建议使用`。

--------------------

## 自增主键为什么可能不是连续的？ ##

*  唯一键冲突会导致自增主键 id 不连续；
 *  事务回滚也会导致自增主键 id 不连续。

--------------------

## `distinct` 和 `group by`执行流程 ##

select a from t group by a order by null;
    select distinct a from t;

由于 `group by`没有结合类似`count()`等函数（如：`select a, count(id) from t group by a order by null;`）的操作一起执行，所以两条语句的执行流程是一致的。（需要借助临时表实现）

*  1、创建一个临时表，临时表有一个字段 a，并且在这个字段 a 上创建一个`唯一索引`；
 *  2、遍历表 t，依次取数据插入临时表中：`如果发现唯一键冲突，就跳过`，否则插入成功；
 *  3、遍历完成后，将临时表作为结果集返回给客户端。

--------------------

## 自增id用完会怎么样？ ##

最后`insert` 一条语句插入数据成功后，这个表的 AUTO\_INCREMENT 对应的值没有该表，就导致了下一次的 `insert` 语句又拿到相同的自增 id 值，再试图执行插入语句，报`主键冲突错误`。

对于一个频繁插入删除数据的表来说，是可能会被用完的。因此在建表的时候你需要考察你的表是否有可能达到这个上限，如果有可能，就应该创建成 8 个字节的 `bigint unsigned`。

**注：**  
`bigint`的取值范围为：`-9223372036854775808 ~ 9223372036854775807`  
`bigint unsigned`（无符号bigint）取值范围为：`0 ~ 18446744073709551615`

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNDA0Mzk1_size_16_color_FFFFFF_t_70_pic_center]: /images/20230528/1be2ae77042848829dd0161207609470.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNDA0Mzk1_size_16_color_FFFFFF_t_70]: /images/20230528/d3dfae42b1b740e4aa59c743c7e1fab0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNDA0Mzk1_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20230528/0360ce2860e947fdaf4cf6a5b9dbec40.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNDA0Mzk1_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20230528/6a145639a67743b88a907fd66a57534d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNDA0Mzk1_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20230528/96f02e56ed1948ea90b6634f0f981d67.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNDA0Mzk1_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20230528/3576da52a17848b2864a4866f46c523c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNDA0Mzk1_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20230528/48ca7e71cd944f139134449f7b01fb31.png