Skip to content

Commit 7c53640

Browse files
jstarfishjstarfish
jstarfish
authored and
jstarfish
committed
📚mysql-faq
1 parent a867bb7 commit 7c53640

File tree

75 files changed

+7447
-7279
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

75 files changed

+7447
-7279
lines changed

‎docs/.DS_Store‎

0 Bytes
Binary file not shown.

‎docs/data-management/.DS_Store‎

0 Bytes
Binary file not shown.

‎docs/data-management/Big-Data/.DS_Store‎

0 Bytes
Binary file not shown.

‎docs/data-management/MySQL/MySQL-Index.md‎

Lines changed: 24 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -270,7 +270,30 @@ InnoDB 的数据文件可以按照表来切分（只需要开启`innodb_file_per
270270

271271
正如我们上面介绍 InnoDB 存储结构，索引与数据是共同存储的，不管是主键索引还是辅助索引，在查找时都是通过先查找到索引节点才能拿到相对应的数据，如果我们在设计表结构时没有显式指定索引列的话，MySQL 会从表中选择数据不重复的列建立索引，如果没有符合的列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，并且这个字段长度为 6 个字节，类型为整型。
272272

273-
273+
> 你可能在一些建表规范里面见到过类似的描述，要求建表语句里一定要有自增主键。当然事无绝对，我们来分析一下哪些场景下应该使用自增主键，而哪些场景下不应该。
274+
>
275+
> 自增主键的插入数据模式，正符合了递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。
276+
>
277+
> 而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。
278+
>
279+
> 除了考虑性能外，我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？
280+
>
281+
> 由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。
282+
>
283+
> **显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。**
284+
>
285+
> 所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。
286+
>
287+
> 有没有什么场景适合用业务字段直接做主键的呢？还是有的。比如，有些业务的场景需求是这样的：
288+
>
289+
> 1. 只有一个索引；
290+
> 2. 该索引必须是唯一索引。
291+
>
292+
> 你一定看出来了，这就是典型的 KV 场景。
293+
>
294+
> 由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。
295+
>
296+
> 这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。
274297
275298
## 三、索引策略
276299

‎docs/data-management/MySQL/MySQL-Log.md‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
---
2-
Lltitle: MySQL 是怎么想的，搞这么多种日志
2+
title: MySQL 是怎么想的，搞这么多种日志
33
date: 2022-08-01
44
tags:
55
- MySQL
@@ -392,9 +392,9 @@ InnoDB 中其实是把 Undo 当做一种数据来维护和使用的，也就是
392392

393393
- **update undo log 是 update 或 delete 操作中产生。**
394394

395-
由于MVCC需要保留Record的多个历史版本，当某个Record的历史版本还在被使用时，这个Record是不能被真正的删除的。
395+
由于 MVCC 需要保留 Record 的多个历史版本，当某个 Record 的历史版本还在被使用时，这个 Record 是不能被真正的删除的。
396396

397-
因此，当需要删除时，其实只是修改对应Record的Delete Mark标记。对应的，如果这时这个Record又重新插入，其实也只是修改一下Delete Mark标记，也就是将这两种情况的delete和insert转变成了update操作。再加上常规的Record修改，因此这里的Update Undo Record会对应三种Type：
397+
因此，当需要删除时，其实只是修改对应 Record 的Delete Mark标记。对应的，如果这时这个Record又重新插入，其实也只是修改一下Delete Mark标记，也就是将这两种情况的delete和insert转变成了update操作。再加上常规的Record修改，因此这里的Update Undo Record会对应三种Type：
398398

399399
- TRX_UNDO_UPD_EXIST_REC
400400
- TRX_UNDO_DEL_MARK_REC

‎docs/data-management/MySQL/MySQL-Transaction.md‎

Lines changed: 41 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,8 @@ categories: MySQL
2020

2121
事务是由一组 SQL 语句组成的逻辑处理单元，具有 4 个属性，通常简称为事务的 ACID 属性。
2222

23+
![](https://img.starfish.ink/mysql/ACID.png)
24+
2325
- **A (Atomicity) 原子性**：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
2426
- **C (Consistency) 一致性**：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
2527
- **I (Isolation)隔离性**：一个事务所做的修改在最终提交以前，对其他事务是不可见的。这种属性有时称为『串行化』，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
@@ -48,36 +50,35 @@ SET AUTOCOMMIT = {0 | 1}
4850
**事务使用注意点：**
4951

5052
- 如果在锁表期间，用 start transaction 命令开始一个新事务，会造成一个隐含的 unlock tables 被执行。
51-
- 在同一个事务中，最好不使用不同存储引擎的表，否则 ROLLBACK 时需要对非事
52-
务类型的表进行特别的处理，因为 COMMIT、ROLLBACK 只能对事务类型的表进行提交和回滚。
53+
- 在同一个事务中，最好不使用不同存储引擎的表，否则 ROLLBACK 时需要对非事务类型的表进行特别的处理，因为 COMMIT、ROLLBACK 只能对事务类型的表进行提交和回滚。
5354
- 和 Oracle 的事务管理相同，所有的 DDL 语句是不能回滚的，并且部分的 DDL 语句会造成隐式的提交。
5455
- 在事务中可以通过定义 SAVEPOINT（例如：mysql> savepoint test; 定义 savepoint，名称为 test），指定回滚事务的一个部分，但是不能指定提交事务的一个部分。对于复杂的应用，可以定义多个不同的 SAVEPOINT，满足不同的条件时，回滚
5556
不同的 SAVEPOINT。需要注意的是，如果定义了相同名字的 SAVEPOINT，则后面定义的SAVEPOINT 会覆盖之前的定义。对于不再需要使用的 SAVEPOINT，可以通过 RELEASE SAVEPOINT 命令删除 SAVEPOINT， 删除后的 SAVEPOINT， 不能再执行 ROLLBACK TO SAVEPOINT命令。
5657

5758
**自动提交（autocommit）：**
58-
Mysql默认采用自动提交模式，可以通过设置autocommit变量来启用或禁用自动提交模式
59+
Mysql 默认采用自动提交模式，可以通过设置 `autocommit` 变量来启用或禁用自动提交模式
5960

6061
- **隐式锁定**
6162

62-
InnoDB在事务执行过程中，使用两阶段锁协议：
63+
InnoDB 在事务执行过程中，使用两阶段锁协议：
6364

64-
随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；
65+
随时都可以执行锁定，InnoDB 会根据隔离级别在需要的时候自动加锁；
6566

66-
锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在**同一时刻**被释放。
67+
锁只有在执行 commit 或者 rollback 的时候才会释放，并且所有的锁都是在**同一时刻**被释放。
6768

6869
- **显式锁定**
6970

70-
InnoDB也支持通过特定的语句进行显示锁定（存储引擎层）：
71+
InnoDB 也支持通过特定的语句进行显示锁定（存储引擎层）：
7172

7273
```mysql
7374
select ... lock in share mode //共享锁
7475
select ... for update //排他锁
7576
```
7677

77-
​ MySQL Server层的显示锁定：
78+
​ MySQL Server 层的显示锁定：
7879

7980
```mysql
80-
lock table和unlock table
81+
lock table 和 unlock table
8182
```
8283

8384

@@ -178,6 +179,25 @@ mysql> show variables like 'transaction_isolation';
178179

179180

180181

182+
#### demo
183+
184+
```mysql
185+
create table T(t int) engine=InnoDB;
186+
insert into T(t) values(1);
187+
```
188+
189+
![](https://img.starfish.ink/mysql/transaction-demo.png)
190+
191+
- “读未提交”：则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。
192+
- “读提交”：则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。
193+
- “可重复读”：则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
194+
- “串行化”：则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。
195+
196+
> - 读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。
197+
> - 读已提交：别人改数据的事务已经提交，我在我的事务中才能读到。
198+
> - 可重复读：别人改数据的事务已经提交，我在我的事务中也不去读。
199+
> - 串行：我的事务尚未提交，别人就别想改数据。
200+
181201
| 事务隔离级别 | 读数据一致性 | 脏读 | 不可重复读 | 幻读 |
182202
| ---------------------------- | ---------------------------------------- | ---- | ---------- | ---- |
183203
| 读未提交（read-uncommitted） | 最低级被，只能保证不读取物理上损坏的数据 | 是 | 是 | 是 |
@@ -193,14 +213,24 @@ mysql> show variables like 'transaction_isolation';
193213

194214
## 三、MVCC 多版本并发控制
195215

216+
在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。
217+
218+
假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。
219+
220+
![](https://static001.geekbang.org/resource/image/d9/ee/d9c313809e5ac148fc39feff532f0fee.png)
221+
222+
当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。
223+
224+
同时你会发现，即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的。
225+
226+
227+
196228
MySQL 的大多数事务型存储引擎实现都不是简单的行级锁。基于提升并发性考虑，一般都同时实现了多版本并发控制（MVCC），包括Oracle、PostgreSQL。只是实现机制各不相同。
197229

198230
可以认为 MVCC 是行级锁的一个变种，但它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只是锁定必要的行。
199231

200232
MVCC 的实现是通过保存数据在某个时间点的快照来实现的。也就是说不管需要执行多长时间，每个事物看到的数据都是一致的。
201233

202-
![](https://static001.geekbang.org/resource/image/d9/ee/d9c313809e5ac148fc39feff532f0fee.png)
203-
204234
典型的 MVCC 实现方式，分为**乐观（optimistic）并发控制和悲观（pressimistic）并发控制**。
205235

206236
下边通过 InnoDB 的简化版行为来说明 MVCC 是如何工作的。

‎docs/interview/MySQL-FAQ.md‎

Lines changed: 27 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -207,7 +207,26 @@ BLOB 保存二进制数据，TEXT 保存字符数据。
207207

208208

209209

210-
## 四、索引
210+
## 四、日志
211+
212+
### redo log 和 bin log 区别
213+
214+
1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
215+
2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
216+
3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
217+
218+
219+
220+
### 为什么需要两阶段提交
221+
222+
以 `update T set c=c+1 where ID=2;` 为例，反向说明下原因
223+
224+
1. **先写 redo log 后写 binlog**。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。
225+
但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。
226+
然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
227+
2. **先写 binlog 后写 redo log**。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。
228+
229+
## 五、索引
211230

212231
>说说你对 MySQL 索引的理解？
213232
>
@@ -623,7 +642,7 @@ MYSQL 引擎innodb ，设置了唯一索引的列值 允许 多个NULL值存在
623642
624643
625644
626-
## 五、MySQL查询
645+
## 六、MySQL查询
627646
628647
> count(*) 和 count(1)和count(列名)区别 ps：这道题说法有点多
629648
@@ -717,7 +736,7 @@ UNION和UNION ALL都是将两个结果集合并为一个，**两个要联合的S
717736

718737

719738

720-
## 六、MySQL 事务
739+
## 七、MySQL 事务
721740

722741
> 事务的隔离级别有哪些？MySQL的默认隔离级别是什么？
723742
>
@@ -1093,7 +1112,7 @@ MySQL 从 5.0.3 InnoDB 存储引擎开始支持 XA 协议的分布式事务。
10931112

10941113

10951114

1096-
## 七、MySQL 锁机制
1115+
## 八、MySQL 锁机制
10971116

10981117
> 数据库的乐观锁和悲观锁？
10991118
>
@@ -1343,7 +1362,7 @@ InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MyS
13431362

13441363

13451364

1346-
## 八、MySQL 调优
1365+
## 九、MySQL 调优
13471366

13481367
> 日常工作中你是怎么优化SQL的？
13491368
>
@@ -1815,7 +1834,7 @@ https://blog.csdn.net/u011516972/article/details/89098732
18151834

18161835

18171836

1818-
## 九、分区、分表、分库
1837+
## 十、分区、分表、分库
18191838

18201839
### MySQL分区
18211840

@@ -1980,7 +1999,7 @@ https://blog.csdn.net/u011516972/article/details/89098732
19801999

19812000

19822001

1983-
## 十、主从复制
2002+
## 十一、主从复制
19842003

19852004
> 配主从，正经公司的话，也不会让 Javaer 去搞的，但还是要知道
19862005

@@ -2051,7 +2070,7 @@ https://blog.csdn.net/u011516972/article/details/89098732
20512070

20522071

20532072

2054-
## 十一、其他问题
2073+
## 十二f、其他问题
20552074

20562075
### 说一说三个范式
20572076

‎node_modules/@vuepress/core/.temp/app-enhancers/global-components-10.js‎

Lines changed: 6 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎node_modules/@vuepress/core/.temp/app-enhancers/global-components-11.js‎

Lines changed: 6 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎node_modules/@vuepress/core/.temp/app-enhancers/global-components-12.js‎

Lines changed: 6 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

0 commit comments

Comments
 (0)