MySQL日志/索引/锁/事务特性的理解

最新推荐文章于 2022-11-28 23:15:06 发布

卡拉叽里呱啦

最新推荐文章于 2022-11-28 23:15:06 发布

阅读量257

点赞数

分类专栏： JAVA开发计算机基础读书笔记文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_33309098/article/details/120076358

版权

JAVA开发同时被 3 个专栏收录

16 篇文章 0 订阅

订阅专栏

读书笔记

9 篇文章 0 订阅

订阅专栏

计算机基础

6 篇文章 0 订阅

订阅专栏

前言

最近对数据库进行了系统的学习，结合了一些面试问题，在春招理解的基础上，对一些摸棱两可，又很难（在中文博客里）找到相关资料的问题进行了更深入的探究，并进行汇总，特此写下

关于日志

innodb的日志主要有redo和undo两种。undo类似于我们的Ctrl+Z，redo是undo的反操作。但是二者的存储方式有很大不同

redo和undo辅助实现了innodb的acid特性，个人理解如下

redo是持久性的体现。完成事务先写redo，后修改页
undo是原子性和隔离性的体现。这个结论各家有各家理解，我的理由是这样的：undo用于回滚操作和MVCC，回滚是原子性的体现，而MVCC是可重复读的实现方式

在事务执行的过程中，通过undo日志，可以准确地找到事务开始前数据库的状态

在事务执行后，通过redo日志，可以复原出事务结束时数据库的状态

事务执行时：事务执行过程中的状态 ===(undo日志)==> 事务开始时的状态 
事务结束后：物理页的中间状态     ===(redo日志)==> 物理页反映事务结束的状态

Redo

redo是“尽量保持磁盘顺序写”的体现之一。redo在硬盘/缓冲区内都是一个环形数组（队列），每次只会顺序写。因此事务提交时，写redo日志远比修改页更快（修改页可能涉及到随机写、节点分裂等麻烦事）

在事务提交后，innodb主线程会定期（也会在苟不下去的时候）将redo里的修改刷回物理页（也可以简单地认为，刷回b+树）。毕竟redo是采用追加方式来记录修改的，只有把修改刷回去，才能更好地利用树的特性。

Undo

关于undo日志如何实现MVCC，可以参考这篇文章，undo的日志格式可以参考这篇文章

具体来说呢，需要下面这些过程相互配合

undo日志会记录对哪个表的哪个id进行哪个操作，对于更新操作，还会记录更新之前的值以及上一次的事务版本。undo日志是一个按照修改时间顺序串成的链表，
每一行数据，都会有两个隐藏字段，分别是trx_id（上一次修改该行的事务id）和roll_pointer（该行的undo日志，从前一条的说明也知道，这是按修改时间串成的链表）
（在已提交读的隔离级别下）事务开启时，会分配一个递增的事务id，比方说=8；还会记录当前有哪些事务正在执行，比方说现在有{3，4，7}事务正在执行
在进行查询时，找到某一行记录，检查它的trx_id
- trx_id = 2/6，这些修改在当前事务开启前已经提交了（小于当前事务id，且开启时已经执行完毕），可以读行的最新版本
- trx_id = 3/4/9，这些修改在当前事务开启后才提交，需要查找后面的roll_pointer找到事务开启前提交的最新版本的记录，比如，trx_id=1

关于索引

分页查询的优化方式&原理

这是一个经典的问题，为什么分页查询越到后面越慢？

SELECT ... FROM ... LIMIT 10000, 10;

答案也很简单，因为MySQL会先数10000条数据，全部抛弃，然后再选择接下来的10条数据返回给客户端

那怎么优化呢？这就涉及到两种主要的优化方式：子查询优化和位置计算优化

子查询优化

子查询优化有两种表现形式，一种是《高性能MySQL》中描述的排序分页查询；另一种是网上很常见的先选出主键，再通过主键查记录的，解释不清原因的查询。接下来我分别叙述这两种子查询优化方式

根据某个字段排序后分页

这种场景下执行的SQL是这样的：

SELECT id, col2
FROM tab1 
ORDER BY col1 
LIMIT 10000, 10;

其中，col1上有索引（通常会推荐给需要排序的列上索引），col2上无索引。由于col2没有索引，因此默认不会使用覆盖索引扫描

这个场景也是可以理解的，比如，根据时间排序、根据标题排序，然后分页。

这里采用的优化是使用覆盖索引替代聚集索引进行查询，这样每个数据页存储的行数就更多，因此同样要过滤1w行数据，所加载的页就会更少

SELECT id, col2
FROM tab1 
INNER JOIN (
    SELECT id FROM tab1
    ORDER BY col1 LIMIT 10000, 10
) AS lim1 USING(id);

先选出主键，再通过主键查询

这种优化的场景很通用，在任何情况（而上一种必须是根据索引字段排序）下，都可以先选出id，然后再根据id选出期望的数据

SELECT id, col2
FROM tab1 
INNER JOIN (
    SELECT id FROM tab1
    LIMIT 10000, 10
) AS lim1 USING(id);

这种查询的速度也很快。但是原因是什么呢？

按我的理解，应该只是避免了在服务器和引擎之间传递大量的数据罢了？还是说主键查有特殊的优化？

位置计算优化

这种优化主要是直接指定查询的起始位置，避免MySQL查询大量不需要的前置数据。

可以有下面的一些方式进行优化

如果id是连续自增的，那可以先取MAX(id)，然后再从{ id=MAX(id)-pageSize*pageNo }处往下取pageSize条记录
设定一个position列，专门用于记录是第几行
应用层缓存上一次分页查询的最后一行的id，下一次以这个id为起始点往下查一页

这种优化方式的生效原因很直接，因此不再过多解释

索引对关联查询的影响

我们都知道，对于关联字段，必须要加索引，否则会造成关联查询效率很低

但是更准确地表述是，执行关联查询的第二个表开始（暂不考虑重排优化的情况），关联字段加索引可以大大提升效率

这是因为，MySQL进行关联查询时，是嵌套查询的。举个例子，假如有A B C三个表关联查询，它的匹配过程如下：

遍历表A中的每一行（这个循环不可避免，因此可以不用索引）
- 根据关联字段找到表B中匹配的某一行（这里用索引可以大大加速匹配过程）
  - 根据关联字段找到表C中匹配的某一行（同样，这里需要索引）
  - 如果可以匹配，从匹配的ABC行记录中，过滤出需要的列，返回给上层
  - 如果没有匹配的C表记录，回溯
- 同样，如果没有匹配的B表记录，回溯

如果表是ABCD四个做匹配，也可以选择先A-B关联，再C-D关联，再把两个中间过程关联，那为什么MySQL不这么做呢？

这是因为，第一种关联方式，可以充分利用索引的优势。我们看到，在索引完备的前提下，只有第一个表需要遍历，后面所有的表都可以走索引进行匹配。而第二种关联方式，一旦产生了中间关联结果，MySQL会将其放在临时表中，而临时表是没有索引的。

关于锁

MySQL的锁有两种上法，一种是显式上锁

# 显式上写锁
SELECT ... FOR UPDATE
# 显式上读锁
LOCK ... IN SHARE MODE

一种是直接UPDATE或者修改某一行时，隐式上锁。

那读呢？如果是直接读，那就是非锁定读，利用MVCC特性即可。也就是说，对于读，可以选择锁定/非锁定；对于写，默认就是锁定。

高性能MySQL指出，第一种上锁方式不符合SQL语句的规范，此外还会造成获取锁的时机过早，影响并发性，因此要尽量避免使用。然而《阿里巴巴编码规约》又指出，对于金额操作推荐上悲观锁。乐观锁和悲观锁的适用范围不太一样，应该根据具体业务场景进行判断，后面我们也将进行讨论

这里我想强调的一点是，进行UPDATE/DELETE时，会隐式获取锁。但是它采用的是即时上锁策略（并在事务结束时释放锁，因此也称为2PC，两阶段锁），也就是这一行SQL执行成功才会上锁，而不像第一种方式，会在事务开始的时候就获取所有的锁

乐观锁和悲观锁

在实际工作中，数据库乐观锁用得很多。乐观锁一般是使用一个version字段表明数据的版本。version是自增的。也就是通常使用以下的语句进行更新

# 假设之前的version读出来是1
UPDATE xxx 
SET yyy=zzz AND version=1+1
WHERE version=1

因为UPDATE是隐式锁定的，因此一旦它执行成功，也就获取了该行的锁。如果这行记录已经被修改或者锁定了，只是单纯更新条数=0而已，不会被阻塞。

而悲观锁，则是显式上锁，也就是SELECT…FOR UPDATE语句

这两种上锁方式的区别在于：悲观锁是预先申请了对资源的独占，而乐观锁只有到真正修改时才会独占资源。因此对于一些简单的长事务，可以采用乐观锁来减少锁被某个事务长期占用的情况

那么，什么叫“简单的长事务”呢？

比方说，我有一个前后端交互系统，前端向后端请求某个配置的详情，展示给用户。用户对这个配置做一些修改之后，提交给后端。

这时，假如有多个用户都在操作这一个配置，可能导致丢失更新。为了解决这一问题，我们可以采用加锁的方式实现。第一种方式就是悲观锁，先对行上锁，这样后续别的用户都休想修改这一条数据——但是，这个锁会一直被持有，直到用户提交了更新。如果此时用户去喝了杯咖啡，那这段时间内谁都别想修改这一行数据了；如果用户断线了，这个锁在没有特别的解锁机制时，只有等到服务器宕机才能解开了。

因此在这种情况下，采用乐观锁更佳。前端提交修改时，会带上version字段。后端更新这条记录时，如果发现更新成功的总行数为0，就表明这行记录已经被修改了，因此返回前端用户一个并发修改错误，引导用户刷新页面重新获取一次数据。

这种情况就是一个典型的简单长事务。只涉及一行数据，因此一个乐观锁就轻松搞定。但是如果像转账这种场景，同时操作多个数据库，那乐观锁用起来就没那么顺手了，最好还是先上悲观锁，把资源预留出来再操作。

MySQL加锁

有一个经典的面试题就是，在可重复读隔离级别&已提交读隔离级别下，加锁方式有什么区别？

MySQL的加锁是个大问题，和索引有关的主要涉及三种情况：无索引加锁、普通索引加锁、唯一索引加锁；和上锁行数相关的主要涉及范围上锁和单点上锁；此外还要考虑记录存不存在

接下来的讨论，我们都基于默认的可重复读隔离级别

范围上锁

假如我们需要对 {col1 > 3} 的记录上锁

在col1无索引的前提下，需要进行表扫，导致表锁（也可说是上了所有的行锁+间隙锁，导致无法更新和插入）
在col1有索引的前提下，将满足条件的所有记录主键上行锁，索引上间隙锁

为什么无索引直接表锁？因为不上表锁实在没办法了，行锁阻止修改，间隙锁阻止插入，全上一通才能避免幻读

单点上锁

假如我们要对 {col1 = 3} 的记录上锁

在col1无索引的前提下，需要进行表扫，导致表锁（理由同上）
在col1有普通索引的前提下，将满足条件的所有记录主键上行锁，col1索引上间隙锁（阻止同值数据的插入）
在col1有唯一索引的前提下，只将满足条件的行的主键&col1索引上行锁

想要理解这些做法，只要从“如何避免插入{col1=3}的记录”这一角度出发，大致都能想明白

如果记录不存在

假如我们要对 {col1 = 3} 的记录上锁，但是现在还没有{col1 = 3}的记录

在col1无索引的前提下，需要进行表扫，导致表锁（理由同上）
在col1有索引的前提下，将col1=3前后的范围上间隙锁

提高数据库的并发度

从优化锁的角度提高单机数据库的并发度，无非从两个方面入手

只对必要的数据上锁，因此尽量通过主键（唯一键，再次普通索引）去锁，而且尽量锁单行（而不是锁表或者锁范围）
减少上锁的时间，因此尽量采用乐观锁（执行到语句才上锁），并且减少事务的执行时长（早一些释放锁）

此外，单机数据库有其自然的限制，因此进行水平扩展、分散数据CRUD的压力才是高并发系统中提高并发度的王道（单机优化也很重要，但是单机优化需要钻研，而显然很多开发人员并不愿意钻研）

此外，关于MySQL并发插入，还存在自增锁的瓶颈。也就是使用自增主键时，MySQL实际上在每个表里都存了个计数器，获取这个计数器需要获取锁（不过它即时获取即时释放，不用等到事务完结再释放）。这一点也只能通过水平扩展、分片来解决。

关于事务的ACID特性

谁都知道ACID是什么意思，但是实际上，除了原子性，每个概念都有所可挖

一致性

首先是关于C：一致性的理解。我起码看过四五种解释，这里上一个知乎的讨论区：如何理解数据库事务中的一致性的概念？

数据库会从一个一致的状态，转变为另一个一致的状态（这也是最抽象的一种解释）
数据库的状态变化反映了真实世界的变化，是符合程序员、业务场景预期的变化。AID是数据库帮助程序员简化编程，实现C的承诺（保障）。也就是说，ACID中，C是目的，AID是手段。
事务进行的过程中，反复读取记录，结果都相同
数据库在事务执行的过程中会检查数据库的完整性约束（比如唯一键、数据类型、数据长度、非空约束等），只要有一个操作不满足约束，整个事务就会回滚
最终一致性，弱一致性等CAP中的一致性

好吧，对于一致性的理解真是五花八门呢。我先排除掉最后一种解释，个人认为CAP的一致性和数据库的一致性完全不同，比方说，单机MySQL一样具有事务的ACID特性。

第一种解释比较权威，但是也很抽象。正是由于这个定义的抽象，导致了后面的四种解释。

第二种解释我比较认同，但是想不清为什么四个特性里，有三个是特性，而一个是目的呢？

第三种、第四种我也比较认同。它们都是表明事务执行的特性，和AID并列。

这个问题就讨论到这，具体答案就看大家的思考和理解了