萧十一郎
所有文章 标签 分类 系列 小册 KeepLearning LeetCode 友链 关于
萧十一郎

MySQL 常见问题（二）

xiaobinqt avatarxiaobinqt  收录于  类别 Mysql 和 系列 转载
     约 8082 字   预计阅读 36 分钟 

在 InnoDB 引擎中，索引的底层数据结构是 B+ 树。MySQL 的数据是存储在硬盘的，在查询时一般是不能「一次性」把全部数据加载到内存中。红黑树是「二叉查找树」的变种，一个 Node 节点只能存储一个Key和一个Value。B 和 B+ 树跟红黑树不一样，它们算是「多路搜索树」，相较于「二叉搜索树」而言，一个 Node 节点可以存储的信息会更多，「多路搜索树」的高度会比「二叉搜索树」更低。

了解了区别之后，其实就很容易发现，在数据不能一次加载至内存的场景下，数据需要被检索出来，选择 B 或 B+ 树的理由就很充分了，一个 Node 节点存储信息更多（相较于二叉搜索树）， 树的高度更低， 树的高度影响检索的速度。

B+ 树相对于 B 树而言，它又有两种特性：

  • B+ 树非叶子节点不存储数据，在相同的数据量下，B+ 树更加矮壮。数据都存储在叶子节点上，非叶子节点的存储能存储更多的索引，所以整棵树就更加矮壮。树的高度能够决定磁盘 IO 的次数，磁盘 IO 次数越少，对于性能的提升就越大。

  • 因为叶子节点存储所有数据，所以 B+ 树的全局扫描能力更强一些，因为它只需要扫描叶子节点。但是 B 树需要遍历整个树。B+ 树叶子节点之间组成一个链表，方便于遍历查询，遍历操作在 MySQL 中比较常见。

b+树
b+树

在 MySQL InnoDB 引擎下，每创建一个索引，相当于生成了一颗 B+ 树。如果该索引是「聚集（聚簇）索引」，那当前 B+ 树的叶子节点存储着「主键和当前行的数据」，如果该索引是「非聚簇索引」，那当前 B+ 树的叶子节点存储着「主键和当前索引列值」

比如写了一句👇

shell

select * from user where id >= 10

那只要定位到 id 为 10 的记录，然后在叶子节点之间通过遍历链表（叶子节点组成的链表），即可找到往后的记录了。由于 B 树是会在非叶子节点也存储数据，要遍历的时候可能就得跨层检索，相对麻烦些。基于树的层级以及业务使用场景的特性，所以 MySQL 选择了 B+ 树作为索引的底层数据结构。

当我们使用索引查询数据时，检索出来的数据可能包含其他列，但走的索引树，叶子节点只能查到当前列值以及主键 ID，所以需要根据主键 ID 再去查一遍数据，得到SQL 所需的列。

举个例子，如果给订单号字段orderId建了个索引，但查询的 SQL 是

shell

select orderId,orderName from orderdetail where orderId = 123

在orderId的索引树的叶子节点只有orderId和主键Id，而我们还想检索出orderName，所以 MySQL 会拿到 ID 再去查出orderName给我们返回，这种操作就叫回表。

回表
回表

想要避免回表，也可以使用覆盖索引（能使用就使用，因为可以避免回表操作）。

所谓的覆盖索引，实际上就是你想要查出的列刚好在叶子节点上都存在，比如建了orderId和orderName联合索引，刚好我需要查询也是orderId和orderName，这些数据都存在索引树的叶子节点上，就不需要回表操作了。所以，如果查询的字段恰好命中联合索引的字段，则可以避免回表操作。

如果有联合索引 (a,b,c,d)，实际上其实新建了 a，ab，abc，abcb 四个索引，因此只要查询中使用了这 4 组字段，都可以让联合索引生效。

查询条件 a=1 and b=2 and c>3 and d=4，这里只能使用a，b和a、b索引，而不能使用a、b、c或a、b、c、d索引。

先匹配最左边的，索引只能用于查找key是否存在或相等，遇到范围查询>、<、between、like左匹配等就不能进一步匹配了，后续退化为线性查找，这就是最左匹配原则。

用自增主键。首先主键得保证它的唯一性和空间尽可能短，这两块是需要考虑的。由于索引的有序特性，id 本身具有连续性使得对应的数据也会按照顺序存储在磁盘上，写入性能和检索性能都很高。如果使用 uuid 这种随机 id，那么在频繁插入数据的时候，就会导致随机磁盘 IO，从而导致性能较低。

事务可以使「一组操作」要么全部成功，要么全部失败。事务其目的是为了「保证数据最终的一致性」。

ACID，分别是原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

原子性指的是：当前事务的操作要么同时成功，要么同时失败。原子性由 undo log 日志来保证，因为 undo log 记载着数据修改前的信息。

比如我们要 insert 一条数据了，那 undo log 会记录的一条对应的 delete 日志。我们要 update 一条记录时，那 undo log 会记录之前的「旧值」的 update 记录。

如果执行事务过程中出现异常的情况，那执行「回滚」。InnoDB 引擎就是利用 undo log 记录下的数据，来将数据「恢复」到事务开始之前。

原子性
原子性

隔离性指的是：在事务「并发」执行时，他们内部的操作不能互相干扰。如果多个事务可以同时操作一个数据，那么就会产生脏读、不可重复读、幻读的问题。于是，事务与事务之间需要存在「一定」的隔离。

在 InnoDB 引擎中，定义了四种隔离级别供我们使用，分别是：read uncommitted读未提交、read committed读已提交、repeatable read可重复复读、serializable串行。

不同的隔离级别对事务之间的隔离性是不一样的，级别越高事务隔离性越好，但性能就越低，而隔离性是由 MySQL 的各种锁来实现的，只是它屏蔽了加锁的细节。

隔离性
隔离性

持久性指的就是：一旦提交了事务，它对数据库的改变就应该是永久性的。说白了就是，会将数据持久化在硬盘上。

而持久性由 redo log 日志来保证。当我们要修改数据时，MySQL 是先把这条记录所在的「页」找到，然后把该页加载到内存中，将对应记录进行修改。

为了防止内存修改完了，MySQL 就挂掉的问题。如果内存改完，直接挂掉，那这次的修改相当于就丢失了。对于这个问题，MySQL引入了 redo log，内存写完了，然后会写一份 redo log，这份 redo log 记载着这次在某个页上做了什么修改。即便 MySQL 在中途挂了，我们还可以根据 redo log 来对数据进行恢复。redo log 是顺序写的，写入速度很快。并且它记录的是物理修改（xxxx 页做了 xxx 修改），文件的体积很小，恢复速度很快。

持久性
持久性

一致性任何一个事务发生的前后，库中的数据变化必须一致。可以理解为我们使用事务的「目的」，而「隔离性」「原子性」「持久性」均是为了保障「一致性」的手段，保证一致性需要由应用程序代码来保证。比如，如果事务在发生的过程中，出现了异常情况，此时你就得回滚事务，而不是强行提交事务来导致数据不一致。

一致性
一致性

MySQL 的 bin log 只能用于归档，不足以实现崩溃恢复crash-safe，需要借助 InnoDB 引擎的 redo log 才能拥有崩溃恢复的能力。所谓崩溃恢复就是，即使在数据库宕机的情况下，也不会出现操作一半的情况。

bin log 和 redo log 的区别👇

bin log 是 MySQL 的 Server 层实现的，对所有引擎都可以使用，而 redo log 是 InnoDB 引擎特有的。

bin log 是可以追加写入的。“追加写” 是指 bin log 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志，保存的是全量的日志。但是这就会导致一个问题，就是没有标志能让 InnoDB 从 bin log 中判断哪些数据已经刷入磁盘了，哪些数据还没有。

redo log 是循环写的，redo log 只会记录未刷入磁盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。

bin log 因为是全量日志，所以可以作为恢复数据使用，主从复制搭建。redo log 可以作为异常宕机或者故障后的数据恢复使用。

以下面的 SQL 语句为例，来解释下执行器和 InnoDB 存储引擎在更新时做了哪些事情：

shell

update table set age = age + 1 where id = 1;

两阶段提交
两阶段提交

所谓两阶段提交，其实就是把 redo log 的写入拆分成了两个步骤prepare和commit。根据两阶段提交，崩溃恢复时的判断规则是这样的：

如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交。

如果 redo log 里面的事务处于 prepare 状态，则判断对应的事务 binlog 是否存在并完整。

  1. 如果 binlog 存在并完整，则提交事务；
  2. 否则，回滚事务。

两阶段提交主要是为了解决主从数据同步的问题。如果没有两阶段提交，那么 binlog 和 redolog 的提交，无非就是两种形式👇

  1. 先写 bin-log 再写 redo-log
  2. 先写 redo-log 再写 bin-log

假设我们要向表中插入一条记录 R，如果是先写 bin-log 再写 redo-log，那么假设 bin-log 写完后崩溃了，此时 redo-log 还没写。那么重启恢复的时候就会出问题：bin-log 中已经有 R 的记录了，当从机从主机同步数据的时候或者我们使用 bin-log 恢复数据的时候，就会同步到 R 这条记录；但是 redo-log 中没有关于 R 的记录，所以崩溃恢复之后，插入 R 记录的这个事务是无效的，即数据库中没有该行记录，这就造成了数据不一致。

相反，假设我们要向表中插入一条记录 R，如果是先写 redo-log 再写 bin-log，那么假设 redo-log 写完后崩溃了，此时 bin-log 还没写。那么重启恢复的时候也会出问题：redo-log 中已经有 R 的记录了，所以崩溃恢复之后，插入 R 记录的这个事务是有效的，通过该记录将数据恢复到数据库中；但是 bin-log 中还没有关于 R 的记录，所以当从机从主机同步数据的时候或者我们使用 bin-log 恢复数据的时候，就不会同步到 R 这条记录，这就造成了数据不一致。

  1. Read uncommitted/RU：读未提交，处于该隔离级别的数据库，脏读、不可重复读、幻读问题都有可能发生。
  2. Read committed/RC：读已提交，处于该隔离级别的数据库，解决了脏读问题，不可重复读、幻读问题依旧存在。
  3. Repeatable read/RR：可重复读，处于该隔离级别的数据库，解决了脏读、不可重复读、幻读问题。
  4. Serializable：序列化/串行化，处于该隔离级别的数据库，解决了脏读、不可重复读、幻读问题都不存在。

数据库事务的隔离级别，由低到高依次为 Read uncommitted 、Read committed、Repeatable read 、Serializable。

上述四个级别，越靠后并发控制度越高，也就是在多线程并发操作的情况下，出现问题的几率越小，但对应的也性能越差，MySQL 的事务隔离级别，默认第三级别：Repeatable read可重复读。

首先来看下read uncommitted读未提交。比如说：A 向 B 转账，A 执行了转账语句，但 A 还没有提交事务，B 读取数据，发现自己账户钱变多了！B 跟 A 说，我已经收到钱了。A 回滚事务【rollback】，等 B 再查看账户的钱时，发现钱并没有多。

简单的定义就是：事务 B 读取到了事务 A 还没提交的数据，这种用专业术语来说叫做「脏读」。

对于锁的维度而言，其实就是在 read uncommitted 隔离级别下，读不会加任何锁，而写会加排他锁。读什么锁都不加，这就让排他锁无法排它了。

读未提交
读未提交

而我们知道，对于更新操作而言，InnoDB 是肯定会加写锁的（数据库是不可能允许在同一时间，更新同一条记录的）。而读操作，如果不加任何锁，那就会造成上面的脏读。

脏读在生产环境下肯定是无法接受的，那如果读加锁的话，那意味着：当更新数据的时，就没办法读取了，这会极大地降低数据库性能。

在 MySQL InnoDB 引擎层面，有新的解决方案，解决加锁后读写性能问题，叫做MVCCMulti-Version Concurrency Control多版本并发控制。

MVCC
MVCC

在 MVCC 下，就可以做到读写不阻塞，且避免了类似脏读这样的问题。那 MVCC 是怎么做的呢❓

MVCC 通过生成数据快照Snapshot，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。

回到事务隔离级别下，针对于read committed读已提交 隔离级别，它生成的就是语句级快照，而针对于repeatable read可重复读，它生成的就是事务级的快照。

MVCC2

前面提到过 read uncommitted 隔离级别下会产生脏读，而read committed读已提交 隔离级别解决了脏读。思想其实很简单：在读取的时候生成一个 “版本号”，等到其他事务 commit 了之后，才会读取最新已 commit 的 “版本号” 数据。

比如说：事务 A 读取了记录（生成版本号），事务 B 修改了记录，此时加了写锁，事务 A 再读取的时候，是依据最新的版本号来读取的（当事务 B 执行 commit 了之后，会生成一个新的版本号），如果事务 B 还没有 commit，那事务 A 读取的还是之前版本号的数据。

通过「版本」的概念，这样就解决了脏读的问题，而「版本」其实就是对应快照的数据。

read committed读已提交 解决了脏读，但也会有其他并发的问题。「不可重复读」：一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修改。

不可重复读的例子：A 查询数据库得到数据，B 去修改数据库的数据，导致 A 多次查询数据库的结果都不一样【危害：A 每次查询的结果都是受 B 的影响的】

了解 MVCC 之后，就很容易想到repeatable read可重复复读 隔离级别是怎么避免不可重复读的问题了。

repeatable read可重复复读隔离级别是「事务级别」的快照！每次读取的都是「当前事务的版本」，即使当前数据被其他事务修改了（commit），也只会读取当前事务版本的数据。

MVCC3

而repeatable read可重复复读 隔离级别会存在幻读的问题，「幻读」指的是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

在 InnoDB 引擎下的的repeatable read可重复复读隔离级别下，快照读 MVCC 影响下，已经解决了幻读的问题（因为它是读历史版本的数据）。

而如果是当前读（指的是select * from table for update），则需要配合间隙锁来解决幻读的问题。

剩下的就是serializable串行隔离级别了，它的最高的隔离级别，相当于不允许事务的并发，事务与事务之间执行是串行的，它的效率最低，但同时也是最安全的。

每种隔离级别都是基于锁和 MVCC 机制实现的👇

  • 读未提交/RU：写操作加排他锁，读操作不加锁。
  • 读已提交/RC：写操作加排他锁，读操作使用 MVCC，但每次 select 都生成读视图。
  • 可重复读/RR：写操作加排他锁，读操作依旧采用 MVCC 机制，但一次事务中只生成一个读视图。
  • 序列化/Serializable：所有写操作加临键锁（具备互斥特性），所有读操作加共享锁。

MVCC 的主要是通过 read view 和 undo log 来实现的。

mvcc原理
mvcc原理

undo log 会记录修改数据之前的信息，事务中的原子性就是通过 undo log 来实现的。所以有 undo log 可以帮我们找到「版本」的数据。

而 read view 实际上就是在查询时，InnoDB 会生成一个 read view，read view 有几个重要的字段，分别是：

  • trx_ids 尚未提交 commit 的事务版本号集合
  • up_limit_id 下一次要生成的事务 ID 值
  • low_limit_id 尚未提交版本号的事务 ID 最小值
  • creator_trx_id 当前的事务版本号

在每行数据有两列隐藏的字段，分别是DB_TRX_ID（记录着当前 ID）以及DB_ROLL_PTR 指向上一个版本数据在 undo log 里的位置指针，到这里，很容易就发现，MVCC 其实就是靠「比对版本」来实现读写不阻塞，而版本的数据存在于 undo log 中。

而针对于不同的隔离级别 read committed 和 repeatable read，无非就是 read committed 隔离级别下，每次都获取一个新的 read view，repeatable read 隔离级别则每次事务只获取一个 read view。

如果表有一定的数据量，那就应该要创建对应的索引，创建索引需要注意的点，比如说👇

  1. 是否能使用「覆盖索引」，减少「回表」所消耗的时间。意味着，我们在 select 的时候，一定要指明对应的列，而不是select *

  2. 考虑是否组建「联合索引」，如果组建「联合索引」，尽量将区分度最高的放在最左边，并且需要考虑「最左匹配原则」

  3. 对索引进行函数操作或者表达式计算会导致索引失效

  4. 利用子查询优化超多分页场景。比如limit offset, n 在 MySQL 是获取 offset + n 的记录，再返回 n 条。而利用子查询则是查出 n 条，通过 ID 检索对应的记录出来，提高查询效率。

  5. 通过 explain 命令来查看 SQL 的执行计划，看看自己写的 SQL 是否走了索引，走了什么索引。通过 show profile 来查看 SQL 对系统资源的损耗情况（不过一般还是比较少用到）

  6. 在开启事务后，在事务内尽可能只操作数据库，并有意识地减少锁的持有时间。比如在事务内需要插入和修改数据，那可以先插入后修改。因为修改是更新操作，会加行锁。如果先更新，那并发下可能会导致多个事务的请求等待行锁释放。

索引
索引

我们这边用的是Read committed读已提交，MySQL 默认用的是 Repeatable read可重复读。

选用什么隔离级别，主要看应用场景，因为隔离级别越低，事务并发性能越高。一般互联网公司都选择 Read committed 作为主要的隔离级别。

像Repeatable read可重复读隔离级别，就有可能因为「间隙锁」导致的死锁问题。

MySQL 默认的隔离级别为 Repeatable read。很大一部分原因是在最开始的时候，MySQL 的 binlog 没有 row 模式（记录具体出现变更的数据，也会包含数据所在的分区以及所位于的数据页），在 read committed 隔离级别下会存在「主从数据不一致」的问题。binlog 记录了数据库表结构和表数据「变更」，比如update/delete/insert/truncate/create。在 MySQL 中，主从同步实际上就是应用了 binlog 来实现的。有了该历史原因，所以 MySQL 就将默认的隔离级别设置为 Repeatable read。

隔离级别
隔离级别

如果走对了索引，但查询还是慢，那一般来说就是表的数据量实在是太大了。

首先，考虑能不能把「旧的数据」给 “删掉” 😢 如果「旧的数据」已经没有查询的业务了，那最简单的办法肯定是 “删掉” 部分数据咯。数据量降低了，那自然，检索速度就快了….

其实，只有极少部分业务可以删掉数据…

那么，就考虑另一种情况，能不能在查询之前，直接走一层缓存（Redis）。

而走缓存的话，又要看业务能不能忍受读取的「非真正实时」的数据（毕竟 Redis 和 MySQL 的数据一致性需要保证），如果查询条件相对复杂且多变的话（涉及各种 group by 和 sum ），那走缓存也不是一种好的办法，维护起来就不方便了…

再看看是不是有「字符串」检索的场景导致查询低效，如果是的话，可以考虑把表的数据导入至 Elasticsearch 类的搜索引擎，后续的线上查询就直接走 Elasticsearch 了。

MySQL->Elasticsearch 需要有对应的同步程序（一般就是监听 MySQL 的 binlog，解析 binlog 后导入到 Elasticsearch)。

如果还不是的话，那考虑要不要根据查询条件的维度，做相对应的聚合表，线上的请求就查询聚合表的数据，不走原表。

比如，用户下单后，有一份订单明细，而订单明细表的量级太大。但在产品侧（前台）透出的查询功能是以「天」维度来展示的，那就可以将每个用户的每天数据聚合起来，在聚合表就是一个用户一天只有一条汇总后的数据。查询走聚合后的表，那速度肯定很快的（聚合后的表数据量肯定比原始表要少很多）。

思路大致的就是「以空间换时间」，相同的数据换别的地方也存储一份，提高查询效率。

空间换时间
空间换时间

如果在 MySQL 读写都有瓶颈，那首先看下目前 MySQL 的架构是怎么样的。

如果是单库的，那是不是可以考虑升级至主从架构，实现读写分离。

简单理解就是：主库接收写请求，从库接收读请求。从库的数据由主库发送的 binlog 进而更新，实现主从数据一致（在一般场景下，主从的数据是通过异步来保证最终一致性的）。

如果在主从架构下，读写仍存在瓶颈，那就要考虑是否要分库分表了。

我这里讲的分库分表的含义是：在原来的某个库的某个表进而拆分。

比如，现在我有一张业务订单表，这张订单表在广告库中，假定这张业务订单表已经有 1 亿数据量了，现在我要分库分表，那就会将这张表的数据分至多个广告库以及多张表中。

分库分表的最明显的好处就是把请求进行均摊（本来单个库单个表有一亿的数据，那假设我分开 8 个库，那每个库 1200+W 的数据量，每个库下分 8 张表，那每张表就 150W 的数据量）。

分库分表
分库分表

一般来说是按照userId，因为按照用户的维度查询比较多，如果要按照其他的维度进行查询，那还是参照上面的的思路（以空间换时间）。

这就涉及到分布式 ID 生成的方式了，思路有很多。有借助 MySQL 自增的，有借助 Redis 自增的，有基于「雪花算法」自增的。具体使用哪种方式，那就看公司的技术栈了，一般使用 Redis 和基于「雪花算法」实现用得比较多。

至于为什么强调自增，还是跟索引是有序有关，可以看前面

分库分表的主键
分库分表的主键

更新于 2024-06-12  dc24cc8f
阅读原始文档 | 查看源代码 | 编辑此页
 Mysql, 索引, MVCC
 | 主页