mysql - 重点
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 | 1. trx_id == creator_trx_id:如果行记录的修改者就是当前事务自己,说明这个版本是自己改的,**可见**。 |
快照读 & 当前读
在 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 支持行级锁,
