MySQL基础篇（二）事务隔离 & 索引

begin/start transaction 并不是事务的起点，执行到它们之后第一个操作 InnoDB 表的语句，事务才真正启动；或者 start transaction with consistent snapshot。

一致性视图：用作可见性判断，保存了“对本事务不可见的其他活跃事务”。

第一次执行快照读（普通的读操作）时创建。
执行 start transaction with consistent snapshot 时创建。

undo log（回滚日志）

① 保存了同一条记录不同版本，每条记录在更新时，都会记录一条回滚操作（InnoDB 实现秒级创建快照）。

② 当事务需要读取记录行并发现当前版本不可见，则可以顺着回滚链找到最新的可见版本。

③ 删除时机：

insert undo log：只用于回滚，事务提交后可立即丢弃。
update undo log：update delete 的 undo log；不仅回滚需要，快照读也需要；快照都不涉及该日志记录时，undo log 才会删除 。

MVCC

多版本控制（Multiversion Concurrency Control）

最早的数据库只有 '读读' 可并发；加入 MVCC 之后，除 '写写' 以外都可以并发；大幅度提高 InnoDB 的并发度。
InnoDB 通过 undo log 保存一条数据的多个版本，能够找回数据的历史版本供用户读。
InnoDB 实现 MVCC 时，使用了一致性视图：consistent read view；用于支持 RR RC 的隔离级别实现。

MVCC 实现逻辑

① InnoDB 里每个事务都有一个唯一的 transaction id；事务开始时申请，严格递增。

② 每次事务更新数据，都会保存新的数据版本，并将自己的 id 赋予新数据版本的 row

trx_id；新数据版本有方法得到前一次旧数据版本。

③ InnoDB 为每个事务构造一个数组：用于保存在事务启动瞬间，其他处于活跃状态（未提交）的事务 ID。

④ row trx_id 分组情况：数组里事务 id 最小值记为低水位，系统里已经创建过的最大事务 id + 1 记为高水位。

落在绿色部分：在启动前已提交的事务或是当前事务自己生成的数据版本；可见。
落在红色部分：由未来启动的事务生成的版本；不可见。
落在黄色部分：[不在数组中，表示已提交，可见][在数组中，表示未提交，不可见]。

⑤ 视图数组（可见事务 id）和高水位组成的一致性视图（read view），读取数据时，使用该视图判断数据版本是否可见。

一致性视图举例总结

V1 V2 V3 V4 物理上并不是真实存在的，U1 U2 U3 为 undo log

假设 V1 V2 已提交事务生成，V3 未提交事务生成；则 id = 19 的当前事务能读到的版本为？

当前事务视图数组：[17, 19]
V4 row trx_id = 25，处于红色区，不可见，往前找；
V3 row trx_id = 17，处于黄色区，未提交不可见，往前找；
V2 row trx_id = 15 已提交，处于绿色区，可见，取该版本。

最终取版本 V2，k = 10。

总结：

未提交，不可见。
在视图创建前提交，可见。
在视图创建后提交，不可见。

更新逻辑&RR RC 区别

更新逻辑：

update 先读后写，使用当前读（current read）；读取最新的版本。
我的理解是：类似于 Java 中，加锁读到的都是最新值。

RR RC 区别：

RR：1 3
RC：2 3

为什么不建议长事务？

长事务意味者系统中会存在很老的视图，在它提交前，它可能用到的回滚记录都必须保留，导致占用大量存储空间。

事务启动方式？

显示启动：begin / start transaction
提交：commit
回滚：rollback
自动提交：set autocommit = 1；否则主动提交/回滚/断开连接
提交事务并自动启动下一个事务：commit work and chain；省去再次 begin 的花销

避免长事务？

应用端：

确认是否使用了 set autocommit=1；随便跑一个业务，通过 MySQL 的 general_log 的日志来确认。
确认是否有不必要的只读事务。
根据业务本身估值，通过设置 SET MAX_EXECUTION_TIME 命令，控制每个语句最长执行时间，避免单个语句意外执行太长时间。（为什么会有意外？）

数据库端：

监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill。
Percona 的 pt-kill 这个工具不错，推荐使用。
在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题。
如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）；如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

索引

索引常见模型

哈希表

键-值：哈希函数将 key 换算成一个确定的位置，将 value 放入其中；哈希结果相同时，连成一个链表。
无序存储：添加新元素时，更快，只需追加即可；不适合区间搜索（整个遍历）。
适合等值查询：NoSQL引擎。

有序数组

适合等值查询
适合区间搜索
不适合更新数据：只适用于静态存储引擎；一些不再修改的数据

为什么使用 B+ 树？

二叉搜索树

极端情况会退化成链表（顺序插入）

二叉平衡搜索树（AVL）

能保证左右子树高度差 <= 1
但是整体高度会随着数据量增大而增大（不可控）

红黑树

加入了反转、平衡
但是整体高度会随着数据量增大而增大（不可控）

B/B-树

所有节点都包含数据
MySQL 节点最大 16KB，一个索引节点最大（包括数据） 1KB
一个节点只能放 16 个元素，数据量极大时，树的高度也很大（不可控）

B+树

非叶子节点都不包含数据，只用作索引；降低索引节点对空间的占用
若用 bigInt（8 B）做索引，下一节点地址（6 B）；一个节点能放 16 KB / (8 + 6) B = 1170 个元素；三层 B+ 树能放 1170 * 1170 * 16 = 219002400（两千多万）

高度小且可控，减少 IO 次数。
数据存储在叶子节点，查询效率稳定。
数据都是顺序存储，并且叶子节点指向下一个叶子节点；区间查找效率高。

InnoDB 索引模型

重要概念

索引组织表：表都是根据主键顺序以索引方式存储的。
B+ 树：每个索引对应一棵 B+ 树。
主键索引/聚簇索引（clustered index）：叶子节点存整行数据。
非主键索引/二级索引（secondary index）：叶子节点存主键的值；先在二级索引树搜索到对应主键的值，再到主键索引树搜索一遍（回表），搜索两次。

索引维护

页分裂：某数据页已满，插入一个处于中间的新值，此时必须申请一个新页，并将部分数据挪动到新页；降低性能，降低空间利用率。
页合并：删除数据后，空间利用率很低，则合并数据页。

自增主键的优势？

符合递增插出场景；插入新纪录，只有追加，无挪动，无页分裂。
占用空间较小；主键长度越小，非主键索引叶节点就越小，非主键索引整体占用空间就越小。

什么场景使用业务字段做主键？

只有一个索引（只有这一个字段用于查询）
索引为唯一索引

典型的 KV 场景，无需考虑其他索引叶节点大小，符合"尽量使用主键查询"原则。

覆盖索引

索引中已经包含了需要查询的字段（如：select 主键 from 表 where 索引=?）；不需要回表，显著提升性能。

最左前缀原则

特性：不只是索引的全部定义，只要满足最左前缀就可以利用索引来加速检查；可以是联合索引的最左 N 个字段，也可以是字符串索引最左 M 个字符。
联合索引字段顺序：①可以少维护一个索引，则优先考虑该组合；②a, b 既有联合索引又有基于各自的查询，若长度 b > a 则使用顺序为（b, a），节省空间。