MySQL 重点

Mysql 结构

四层,连接层、服务层、存储引擎、底层文件。

连接层处理网络连接、用户授权;服务层处理 SQL 优化、解析;存储引擎直接操纵底层文件;底层文件则直接存储数据。

1、索引

底层结构

索引用的是 B+ Tree,而它的直接对手有 Hash 表、BST 二叉搜索树、AVL 平衡二叉搜索树、红黑树。

为什么采用 B+ Tree?先看看为什么不采用它的对手们。

首先是 Hash 表,Hash 冲突和链地址法老生常谈,该方法确实存在退化为单链表导致性能下降的问题,然而这却并非 Hash 表被淘汰的直接理由,最直接的理由是:不支持范围查询。因为 Hash 打乱了直接数据之间的物理顺序,范围查询最终会退化为扫盘。

BST 二叉搜索树,最大的问题是同样可能退化为单链表,性能不可靠。

那么 AVL 平衡二叉搜索树呢?解决了退化问题,却带来了需要频繁自旋的问题,如果数据库中存了大量数据,自旋的时间占用过大,所以被淘汰。

然后是红黑树,红黑树相比 AVL,自旋的效率提升了,但却不追求绝对的平衡,这就导致可能存在 tuple 被放到很高层级的位置,从而性能也有可能带来下降,但是又没有退化为单链表那么严重,但是就是这个原因导致被淘汰。

所以,B+ Tree 能被选中,很大原因是同行衬托,B+ Tree 的层级通常很低,IO 友好;B+ Tree 相比 B Tree,只有叶子节点存放 kv 对,且叶子节点互相之间有指向性,支持范围查询。

主键索引 & 非主键索引

主键索引是表建立的时候就存在的,它最符合 B+ Tree:叶子节点的 k 就是主键 id、v 就是剩余全部数据。

而非主键索引,包括其他所有概念,其在叶子节点中的 k 是非主键字段本身没有问题,然而其 v 却是主键的 id。

聚簇索引 & 非聚簇索引

B+ Tree 中 kv 都存在同一个叶子节点,找到 k 也就找到了 v,这就叫做聚簇索引,索引结构和真实数据存放在一起。非聚簇索引,那就是真实数据和索引结构分开的,比如 MyISAM,建立索引的过程中,B+ Tree 的叶子节点只存该数据在底层文件中的地址,要读写数据还要经过一轮 IO。

聚簇索引的好处,则是批量简单操作,比如范围查询,直接在 B+ Tree 中就能解决,不需要一个个跳到 IO 地址。这同时也是非聚簇索引的坏处,它需要一个一个跳到 IO 地址,范围查询的优势就没了。

当然也有坏处,索引结构本身的更新耗费更多的时间,比如索引的数据被修改了、插入数据、删除数据等,B+ Tree 结构改写很耗时间。当然,非聚簇索就虽然也要频繁改写结构,但是更新的代价会少很多,原因则是少了数据本身。

除了上述的之外,非聚簇索引还有一个坏处,就是回表。

对于非聚簇索引的 B+ Tree 来说,自己只有索引字段本身和主键id(因为非主键索引的值是主键 id),而不知道其他字段的值,假如一次查询走了非聚簇索引,但是要求返回了一个非索引字段本身,完事了就只能走一遍聚簇索引拿到对应行的那个字段。比如:

1
SELECT * FROM users WHERE name = '张三';

根据 name 建立了非聚簇索引。好了,假如我们只 select name, id,这个非聚簇索引结构本身就有,因为 k 是 name、v 是 id。

然而,这里 select *,查询所有字段,显然这个索引本身没有其他字段,那么怎么办?拿着 id 再走一遍主键索引,拿到其他字段。

这就是回表,频繁的回表会拖累性能。

覆盖索引

覆盖索引并非一种索引的名称,而是一种手段。

想要单独查询字段 A,B,C?那就创建联合索引 index(A,B,C)。

想要单独查询字段 A,C?那就创建联合索引 index(A,C)。

想要单独查询字段 A,C,B?那就创建联合索引 index(A,C,B)。

想要单独查询字段 id,A,C?同样创建联合索引 index(A,C) 就行,因为里面包含了 id。

想要单独查询字段 id,A?同理,创建联合索引 index(A) 就行。

联合索引 & 最左匹配原则

上面提到,创建联合索引可以有效防止回表,直接针对若干字段建立索引就行。

但是,index(A,B,C) 和 index(A,C,B),是一样的吗?不一样的,原因在于顺序不一样。

对于多字段索引,我们很容易想出分层的结构,比如 index(A,B,C),先找 A,A 里面再找 B,B 里面再找 C,这样才能找到。你找到了 A,就想直接找 C?这样不行的,中间还有一个 B 要找。

这就是最左匹配原则。

index(A,B,C),如果 where A,显然可以吃到索引,虽然只吃到一半;where A,B,C 显然也可以;where B 显然就吃不到了,因为你连 A 都还没有找。

索引失效

联合索引违背最左匹配原则

不按联合索引顺序来。

对索引字段进行算术

比如:

1
SELECT * FROM students WHERE height + 1 = 170;

对索引字段进行后置like模糊

比如:

1
SELECT * FROM students WHERE sname LIKE '%A'; 

为什么会失效?本质上还是违反最左匹配原则,假如换成 A% 就行了,以 A 开头的字符串,索引也能吃到一部分。

错误的 order by 排序

order by 不含索引字段;

order by 不根据联合索引顺序,违反最左匹配。

2、事务

一批操作,要么全执行、要么全不执行,就是事务

ACID 原则

原子性 Atom、一致性 Consistency、隔离性 Isolation、持续性 Durability。

一致性是最终目的,通过保障其他三个特性来实现。

事务隔离级别 & 三种问题

隔离级别 脏读 (Dirty Read) 不可重复读 (Non-Repeatable Read) 幻读 (Phantom Read)
READ UNCOMMITTED √ √ √
READ COMMITTED × √ √
REPEATABLE READ × × √ (标准) / ≈× (InnoDB)
SERIALIZABLE × × ×

读未提交:允许读未提交的事务临时写的数据,最低的保障级别,读写都不上锁。

读已提交:只能读取提交的事务写的数据,不允许在有事务锁定数据的时候读。但是,如果某个事务期间先后读取同一个不该被改变的数据,后面一次因为其他事务更改了该数据,结果读到前后矛盾的结果,这样的不可重复读问题会发生,且是允许的。因为读取数据的事务只能读,没有写的权力,唯一写的权力自然就交给了其他事务。相当于写上锁,但读不上锁。

可重复读:保证同一个事务期间,多次读取同一个未修改的数据,结果不变,相当于写上锁期间,读也上锁;如果没人写,那读就全体开发。也就是 InnoDB 默认隔离级别。

序列化:不管读还是写,都只能排队。

脏读

一个事务修改了数据但是后来回滚了,在此期间被另外一个事务读到了,就是脏读。

不可重复读

一个事务前后两次读取同一个理论上没有修改过数据,由于中途有另一个事务。

幻读

事务插入数据,但还没有提交期间,这些被插入的“黑户”被其他事务读到,这就是幻读。

怎么解决幻读?一是上序列化;二是上表级锁;三则是靠 Next-Key Lock,也是 InnoDB 所采用的方法。

3、MVCC

概念

InnoDB 默认事务隔离级别是 RR,即可重复读,特点是有事务在写数据期间,不允许读正在被修改的数据,这样一来当并发数量极高,读写进程相当于全部被阻塞。

所以引入 MVCC,使用各种 log 保留数据的版本。其核心思想是,当一行数据被修改时,不直接覆盖原数据,而是生成一个新版本,并把旧版本用链表保存起来。读操作去读历史快照版本,写操作去更新最新版本。

核心组件之一:隐藏字段

InnoDB 给所有数据的行,添加了三个外部不可见的隐藏字段:

  • trx_id(事务 ID):记录最后一次修改(INSERT 或 UPDATE)该行记录的事务 ID。每次一个事务往数据库写数据,事务 ID 就会自增,这说明 trx_id 可以用于判断事务的先后顺序。

  • roll_ptr(回滚指针):指向这条记录对应的 Undo Log(回滚日志)。通过这个指针,可以顺藤摸瓜找到这行数据修改前的历史版本。

  • row_id(隐藏主键):如果表没有定义主键,InnoDB 会自动生成一个隐藏主键。

对于 MVCC 来说,有用的是 trx_id 和 roll_ptr。

核心组件之二:undo_log

每一行,如果被修改,原先的数据不会消失,而是保存到 undo_log 内,而当前最新版本的行,其 roll_ptr 就指向 undo_log 内上一个版本的地址。

undo_log 主要有两大作用,一是供快照读,二是容易回滚。

当然,undo_log 不会无限地增长,MVCC 机制会动态地清理古老的 undo_log,保证空间。

核心组件之三:read view

read_view 可以控制一个事务可以看到 undo_log 中哪些历史版本。

read_view 并非一个独立的组件,而是当 select 时动态生成一条,其内部包含以下内容:

  • m_ids:生成 ReadView 时,当前系统中活跃(未提交)的读写事务 ID 集合。

  • min_trx_id:m_ids 中最小的事务 ID。

  • max_trx_id:系统中下一个要生成的事务 ID(最大事务 ID + 1)。

  • creator_trx_id:创建这个 ReadView 的事务自身的事务 ID。

那如何控制事务的可见性?具体规则:

1
2
3
4
5
6
7
8
9
10
11
1. trx_id == creator_trx_id:如果行记录的修改者就是当前事务自己,说明这个版本是自己改的,**可见**。

2. trx_id < min_trx_id:说明该行记录在当前事务创建 ReadView 之前就已经提交了,**可见**。

3. trx_id >= max_trx_id:说明该行记录是在当前事务创建 ReadView 之后才启动的事务修改的,**不可见**。

4. min_trx_id <= trx_id < max_trx_id:分情况讨论:

如果 trx_id 在 m_ids 列表中(说明生成 ReadView 时,这个事务还没提交),**不可见**。

如果 trx_id 不在 m_ids 列表中(说明生成 ReadView 时,这个事务已经提交了),**可见**。

快照读 & 当前读

在 InnoDB 下,一般的 select 默认都是快照读,也就是遵循 MVCC,避开争议内容,读稳定数据。这种读法允许多个事务同时进行,防止写数据时阻塞。

与此对立的就是当前读,只有在 select 后做特殊标记时才会触发,这种读法不走 MVCC,而是排他地直接读真实数据

MVCC 在 RR 与 RC

InnoDB 默认的事务隔离级别是可重复读 RR,RR 自然是支持 MVCC 的,但是 InnoDB 也对读提交 RC 进行了 MVCC 支持,尽管二者在实现上不同。

在 RC 隔离级别下的 每次select 查询前都生成一个Read View (m_ids 列表);而在 RR 隔离级别下只在事务开始后第一次select 数据前生成一个Read View(m_ids 列表)

我们 select 读快照是为了避开争议内容,读非争议内容,在 RR 级别下,同一个事务能保证先后读取数据不变(除非主动修改),所以全程只需要一个 read_view 确保能读到非争议内容就行了。

而 RC 明确不保障可重复读,即便有 MVCC 加持也不例外,但是可以明确的是每次都读最新提交的数据。

RR + MVCC + Next-key-Lock 防止幻读

幻读就是读到了本应删除但还没来得及删除的行,而 MVCC 的读快照天然解决了这个问题,由于有着 read_view 的加持,任何事务都读不到还未提交的数据,不管是修改、删除还是插入,一视同仁。

当然,光靠 MVCC 解决不了当前读的幻读问题,这个时候

锁

行级锁

MyISAM 只能实现表级锁,而 InnoDB 支持行级锁,