《MySQL实战45讲》—基础篇（1-8讲）—学习记录

极客时间-林晓斌《MySQL实战45讲》

课程链接：《MySQL实战45讲》从原理到实战，丁奇带你搞懂MySQL

注：以下所有图片均来自极客时间《MySQL实战45讲》

---

01 | 基础架构：一条SQL查询语句是如何执行的？

mysql> select * from T where ID=10；

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

Server层： 包括连接器，查询缓存，分析器，优化器和执行器等。所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
存储引擎层： 负责数据的存储和提取，支持 InnoDB、MyISAM、Memory 等多个存储引擎
连接器： 负责与客户端建立连接，获取权限，维持和管理连接

1. show processlist查看所有连接。Command 列显示为“Sleep”即为空闲连接。
2. 空闲连接自动断开由参数 wait_timeout 控制，默认值是 8 小时。
3. 尽量使用长连接，为解决长连接可能导致的内存占用过大，MySQL 5.7 或更新版本中可以执行mysql_reset_connection 来重新初始化连接资源。

查询缓存： key-value对的形式（key是查询的语句，value是查询的结果）。

1. 查询缓存往往弊大于利，因此不推荐使用。
2. 关闭查询缓存可以将参数query_cache_type 设置成 demand。对于确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定。
3. 如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。
4. MySQL 8.0 版本直接将查询缓存的整块功能删掉了。

分析器： 对sql语句做解析，判断其是否正确（包括词法分析与语法分析）。
优化器： 决定使用哪个索引，多表关联的时候，决定各个表的连接顺序。
执行器： 执行语句，在此之前会先判断用户权限，如果没有权限，会抛出没有权限的错误。

---

02 | 日志系统：一条SQL更新语句是如何执行的？

mysql> update T set c=c+1 where ID=2;

与查询流程不一样的是，更新流程还涉及两个重要的日志模块。

redo log（重做日志）： MySQL WAL（Write-Ahead Logging） 技术。先写日志，再写磁盘。固定大小，循环写入。

1. 物理日志，记录的是“在某个数据页上做了什么修改”。
2. 保证crash-safe能力（redo log 是 InnoDB 引擎特有的日志）。
3. innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。
   

binlog（归档日志）： 位于Server 层的日志系统，没有crash-safe能力。不定大小，追加写入。

1. 逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
2. sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。
3. 两种模式，statement 格式的话是记sql语句。row格式会记录行的内容，记两条，更新前和更新后都有。

两阶段提交： 跨系统维持数据逻辑一致性时常用的一个方案。即更新过程中的最后三步（写入redolog处于prepare阶段->写入binlog->提交事务，修改redolog状态为commit）

---

03 | 事务隔离：为什么你改了我还看不见？

事务ACID：

1. 原子性（Atomicity）：指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2. 一致性（Consistency）：事务前后数据的完整性必须保持一致。
3. 隔离性（Isolation）：多个并发事务之间要相互隔离。
4. 持久性（Durability）：指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，即使数据库发生故障也不应该对其有任何影响。

事务隔离级别：

1. 读未提交（read committed）：一个事务还没提交时，它做的变更就能被别的事务看到。
2. 读提交（read uncommitted）：一个事务提交之后，它做的变更才会被其他事务看到。
3. 可重复读（repeatable read）：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。
4. 串行化（serializable）：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

多事务数据读取问题：

1. 脏读：事务A读取到事务B（update后但未提交）的数据。
2. 不可重复读：事务A读取到事务B（update或delete并提交）的数据，导致前后数据不一致（针对同一行数据）。
3. 幻读：事务A读取到事务B（insert并提交）的数据，导致前后数据不一致（针对多行数据）。

读提交与可重复读的区别：

1. 读提交（RC）：解决了脏读问题，可能出现不可重复读与幻读问题。事务在每次执行read操作时，都会建立read-view。
2. 可重复读（RR）：解决了脏读与不可重复读问题，可能出现幻读。事务在执行第一个read操作时，建立read-view。
3. MySQL默认隔离级别：可重复读。
4. Oracle默认隔离级别：读提交。

事务隔离的实现：
在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

不同时刻启动的事务会有不同的 read-view。同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。
当没有事务再需要用到这些回滚日志时，回滚日志会被删除。

为什么建议你尽量不要使用长事务：

1. 长事务意味着系统里面会存在很老的事务视图。事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。
2. 长事务还占用锁资源，也可能拖垮整个库。

事务的启动方式：

1. 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
2. 隐式启动事务，set autocommit=0，这个命令会将这个线程的自动提交关掉（并且如果你只执行一个 select 语句，这个事务就启动了）。
3. 建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务。
4. 解决“多一次交互”的问题：在 autocommit = 1 的情况下，用 begin 显式启动的事务，用 commit work and chain 代替 commit ，即提交事务并自动启动下一个事务，这样省去了再次执行 begin 语句的开销。

查询长事务：
在 information_schema 库的 innodb_trx 这个表中查询长事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

---

04 | 深入浅出索引（上）

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。
索引的常见模型：

1. 哈希表：键-值（key-value）对存储模型。适用于等值查询，不适用区间查找。
2. 有序数组：在等值查询和范围查询场景中的性能就都非常优秀，但插入数据成本太高，故只适用于静态存储引擎。
3. 搜索树：二叉搜索树，N叉树等

InnoDB索引模型： 在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。

4. InnoDB 使用了 B+ 树索引模型。
5. 主键索引（聚簇索引）的叶子节点存的是整行数据。
6. 非主键索引（二级索引）的叶子节点内容是主键的值。
7. 基于非主键索引的查询很多时候需要多扫描一棵索引树，该过程称为回表。

索引维护： B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。

8. 插入到索引数最后 ，直接追加。
9. 插入到索引树中间，逻辑移动后面的数据。若当前数据页满了，申请一个新的数据页，移动部分数据过去（即页分裂）。
10. 与页分裂对应的，当删除数据导致两个数据页的利用率很低之后，会将数据页进行合并。
11. 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
12. 由于InnoDB 是索引组织表，一般情况下建议创建一个自增主键，这样非主键索引占用的空间最小。但事无绝对，当表中只有一个索引且该索引必须是唯一索引时，优先考虑“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

重建索引：

mysql> alter table t engine=InnoDB

---

05 | 深入浅出索引（下）

回到主键索引树搜索的过程，我们称为回表。
覆盖索引： 当查询结果已经在二级索引上时，不需要回表。

• 由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

最左前缀原则： 联合索引合理安排顺序，可以少维护索引或者减少存储消耗。（联合索引(a,b）意味着不需要再单独建立一个a的索引）。
索引下推： MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

---

06 | 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。
全局锁： 对整个数据库实例加锁。典型使用场景是，做全库逻辑备份。

1. MySQL让整个库只读命令：Flush tables with read lock (FTWRL)。
2. 官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。
3. single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。
4. 不推荐使用set global readonly=true来使数据表只读，原因有二，一是readonly可能被其他逻辑使用（比如判断主库还是从库）；二是readonly发生异常时会保持该状态。

表级锁： MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。
表锁的语法： lock tables … read/write。释放锁：unlock tables（也可以在客户端断开的时候自动释放）。
元数据锁（MDL）： 不需要显示使用，在访问一个表的时候会被自动加上。

1. 作用：保证读写的正确性。
2. MDL读锁：对表做增删改查操作时加上。
3. MDL写锁：对表做结构变更操作时加上。
4. 事务不提交，就会一直占着 MDL 锁（读写互斥，写写互斥）

如上图，由于长事务A未提交，一直占用MDL读锁。会导致事务C获取MDL写锁时被阻塞。而事务D在事务C之后启动，继而又会被阻塞。
如何安全地给小表加字段：

1. 避免长事务。
2. 在alter table语句里面设定等待时间。

mysql> ALTER TABLE tbl_name NOWAIT add column ...
mysql> ALTER TABLE tbl_name WAIT N add column ...

---

07 | 行锁功过：怎么减少行锁对性能的影响？

行锁： 针对数据表中行记录的锁。MySQL 的行锁是在引擎层由各个引擎自己实现的。
两阶段锁： 在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。（如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。）。
死锁和死锁检测： 当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

死锁策略：

1. 策略一，直接进入等待，直到超时。通过参数 innodb_lock_wait_timeout 来设置（默认值50s）。
2. 策略二，发现死锁后，主动回滚死锁链条中的某一个事务（将参数 innodb_deadlock_detect 设置为 on（默认为on），表示开启这个逻辑）。

关于策略二，假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。即使没有死锁，检测操作也会消耗大量的CPU资源。所以怎么解决由这种热点行更新导致的性能问题呢？

1. 若确定业务不会出现死锁，可以临时关闭死锁检测。
2. 在客户端控制并发。
3. 修改MySQL源码，并发进入引擎之前先排队。
4. 将一行数据该为多行，比如将一个余额账户分为多个（但在数据减少操作时需要考虑小于0等情况）。

---

08 | 事务到底是隔离的还是不隔离的？

事务的启动时机： begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动（如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令）。
“快照”在 MVCC 里是怎么工作的： InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。
而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

语句更新会生成 undo log（回滚日志）。上中的三个虚线箭头，就是 undo log。
快照实现：

1. InnoDB在事务启动的瞬间，构造了一个数组来保存当前正在活跃的所有事务ID（活跃：启动但还未提交）。
2. 数组ID最小值记为低水位。当前系统最大事务ID+1记为高水位。
3. 数组和高水位组成了当前事务的一致性视图（read-view）。

对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
3. 如果落在黄色部分，那就包括两种情况（[1] 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；[2] 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见）。

简单来讲，一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

4. 版本未提交，不可见；
5. 版本已提交，但是是在视图创建后提交的，不可见；
6. 版本已提交，而且是在视图创建前提交的，可见。

当前读： 更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）（如果当前记录的行锁被其他事务占用，此时会进入锁等待）。
除了 update 语句外，select 语句如果加锁，也是当前读： 下面这两个 select 语句，就是分别加了读锁（S 锁，共享锁）和写锁（X 锁，排他锁）。

mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;

普通查询语句是一致性读，一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性：

1. 对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
2. 对于读提交，查询只承认在语句启动前就已经提交完成的数据；
3. 而当前读，总是读取已经提交完成的最新版本。

---