MySQL 常见问题(二)

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

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

B+ 树相对于 B 树而言,它又有两种特性:

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

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

https://cdn.xiaobinqt.cn/xiaobinqt.io/20221201/b768c90fe50b48aa8d7deec33efd7e8c.png
b+树

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

比如写了一句👇

1
select * from user where id >= 10

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

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

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

1
select orderId,orderName from orderdetail where orderId = 123

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

https://cdn.xiaobinqt.cn/xiaobinqt.io/20221201/89f82e92055d4bd78318ab9baa4d9dd5.png
回表

想要避免回表,也可以使用覆盖索引(能使用就使用,因为可以避免回表操作)。

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

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

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

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

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

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

ACID,分别是原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。

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

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

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

https://cdn.xiaobinqt.cn/xiaobinqt.io/20221202/8a8fcedc257b42c8ad74a868363fdafe.png
原子性

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

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

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

https://cdn.xiaobinqt.cn/xiaobinqt.io/20221202/20d805b51694457d955fbf985e0e9adb.png
隔离性

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

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

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

https://cdn.xiaobinqt.cn/xiaobinqt.io/20221202/a58e871764ee402e933beb1c10adae7c.png
持久性

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

https://cdn.xiaobinqt.cn/xiaobinqt.io/20221202/4702334e18194c00b62d4001d1100e13.png
一致性

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 存储引擎在更新时做了哪些事情:

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

https://cdn.xiaobinqt.cn/xiaobinqt.io/20221206/0fa9678f6b214a63bfdd8506cabe8a28.png
两阶段提交

所谓两阶段提交,其实就是把 redo log 的写入拆分成了两个步骤preparecommit

根据两阶段提交,崩溃恢复时的判断规则是这样的:

如果 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 隔离级别下,读不会加任何锁,而写会加排他锁。读什么锁都不加,这就让排他锁无法排它了。

https://cdn.xiaobinqt.cn/xiaobinqt.io/20221202/06ccfe53f21143e8a3a9093f0864ac46.png
读未提交

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

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

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

https://cdn.xiaobinqt.cn/xiaobinqt.io/20221202/0e52740b759b4aa490ef6e37497de964.png
MVCC

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

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

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

https://cdn.xiaobinqt.cn/xiaobinqt.io/20221202/00282fd01288429b8b03a1407b7c5227.png

前面提到过 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),也只会读取当前事务版本的数据。

https://cdn.xiaobinqt.cn/xiaobinqt.io/20221202/c8d49279d79f4b5fb09d26aba8a809bd.png

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 来实现的。

https://cdn.xiaobinqt.cn/xiaobinqt.io/20221202/3f80906689ea4930bbf75288bd905d3c.png
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. 在开启事务后,在事务内尽可能只操作数据库,并有意识地减少锁的持有时间。比如在事务内需要插入和修改数据,那可以先插入后修改。因为修改是更新操作,会加行锁。如果先更新,那并发下可能会导致多个事务的请求等待行锁释放。

https://cdn.xiaobinqt.cn/xiaobinqt.io/20221202/7a6c78d1a5ac42e587ffd82ced372440.png
索引

我们这边用的是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。

https://cdn.xiaobinqt.cn/xiaobinqt.io/20221202/acbe1fc753c84d1e9a9ccbd047f8afc9.png
隔离级别

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

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

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

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

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

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

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

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

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

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

https://cdn.xiaobinqt.cn/xiaobinqt.io/20221202/9861ea4c29cb40a583593ce747b146d6.png
空间换时间

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

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

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

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

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

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

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

https://cdn.xiaobinqt.cn/xiaobinqt.io/20221202/6583d2b4aed24774a316bced6d17e38b.png
分库分表

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

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

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

https://cdn.xiaobinqt.cn/xiaobinqt.io/20221202/059ac16a9707417a91f9342bb8f9330c.png
分库分表的主键