本篇记录javaguide中的mysql重要知识点，详细信息可参阅MySQL索引详解 | JavaGuide(Java面试+学习指南)

01索引

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构。对于数据量大的数据库，建立索引可以大幅度提升查询速率。

索引类型

按数据结构看有：BTree 索引、B+Tree 索引、哈希索引、哈希索引、全文索引，其中要记的就B和B+树结构。

按存储方式看有：聚簇索引、非聚簇索引，差别在于一个指向数据一个指向物理地址。

按照应用维度看：主键索引、联合索引、全文索引、普通索引、唯一索引、覆盖索引。其中需要记的有主键索引、覆盖索引、联合索引。

MySQL 8.x 中实现的索引新特性：隐藏索引（主键不能设置为隐藏）、降序索引、函数索引（在索引中使用函数或者表达式的值）

其中主键列使用的就是主键索引，二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

B树和B+树的区别

B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。

B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

覆盖索引和联合索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

使用表中的多个字段创建索引，就是联合索引，也叫组合索引或复合索引。

最左前缀匹配原则

最左前缀匹配原则指的是，在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询（如 **>、<**）才会停止匹配。对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

索引下推：一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

使用索引的一些考虑

创建索引时应该注意，建议使用一下字段作为索引：不为 NULL 的字段 、被频繁查询的字段、被作为条件查询的字段、 频繁需要排序的字段、被经常频繁用于连接的字段

同时避免一下字段作为索引：被频繁更新的字段、限制每张表上的索引数量、联合索引而不是单列索引、避免冗余索引、使用前缀索引代替普通索引

索引失效

简言之，不满足最左前缀匹配时会失效，一下都是失效的具体情况。

使用 SELECT * 进行查询;
创建了组合索引，但查询条件未遵守最左匹配原则;
在索引列上进行计算、函数、类型转换等操作;
以 % 开头的 LIKE 查询比如 like '%abc';
查询条件中使用 or，且 or 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;

02日志

MySQL 日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中，比较重要的还要属二进制日志 binlog（归档日志）和事务日志 redo log（重做日志）和 undo log（回滚日志）。其中ACID的持久性依赖redo log（重做日志），原子性依赖undo log（回滚日志）。binlog来同步数据，保证数据一致性。

redo log 重做日志

mysql通过redo log可以恢复数据，保证数据的持久性与完整性。查询数据表时会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。

更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

redolog的刷盘时机有三种：

0 ：设置为 0 的时候，表示每次事务提交时不进行刷盘操作
1 ：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值）
2 ：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache

日志文件组：硬盘上存储的 redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。它采用的是环形数组形式，从头开始写，写到末尾又回到头循环写。

使用redolog的原因：数据库的数据页大小是16KB，刷盘比较耗时，可能就修改了数据页里的几 Byte 数据，就要刷盘，这不合理。而且数据页刷盘是随机写，因为一个数据页对应的位置可能在硬盘文件的随机位置，所以性能是很差。如果是写 redo log，一行记录可能就占几十 Byte，只包含表空间号、数据页号、磁盘文件偏移量、更新值，再加上是顺序写，所以刷盘速度很快。所以用 redo log 形式记录修改内容，性能会远远超过刷数据页的方式，这也让数据库的并发能力更强。

binlog

而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。

不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。数据库用binlog来同步数据，保证数据一致性

binlog记录有三种格式：statement、row、mixed。statement记录的内容是SQL语句原文；row记录的内容不再是简单的SQL语句了，还包含操作的具体数据，同时还需要解析工具解析出来；mixed记录的时前两者的混合。

写入时机：事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。

每个线程都会分配一块内存作为binlog cache。write的指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快，fsync才是将数据持久化到磁盘的操作。

它有三种选择，一种是0：表示每次提交事务都只write，由系统自行判断什么时候执行fsync；

一种是1：表示每次提交事务都会执行fsync，就如同 redo log 日志刷盘流程 一样。

还有一种是大于1：表示每次提交事务都write，但累积N个事务后才fsync。

两阶段提交（指的是redo log）

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。

为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。原理很简单，将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。

03MVCC

一致性非锁定读和锁定读（快照读和当前读）

共享锁S和排他锁X:

共享锁【S锁】:又称读锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

排他锁【X锁】:又称写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

快照读：在 Repeatable Read 和 Read Committed 两个隔离级别下，如果是执行普通的 select 语句（不包括 select ... lock in share mode ,select ... for update）则会使用 一致性非锁定读（MVCC）。并且在 Repeatable Read 下 MVCC 实现了可重复读和防止部分幻读。

当前读：

select ... lock in share mode：对记录加 S 锁，其它事务也可以加S锁，如果加 x 锁则会被阻塞
select ... for update、insert、update、delete：对记录加 X 锁，且其它事务不能加任何锁

MVCC 的实现（详细的解释在另一篇题为MVCC的笔记中）

MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改。

中间件学习--数据库索引、日志和mvcc的补充