MySQL索引篇

• 回表：如果用二级索引查询商品，会先检二级索引中的 B+Tree 的索引值，找到对应的叶子节点，然后获取主键值，然后再通过主键索引中的 B+Tree 树查询到对应的叶子节点，然后获取整行数据。这个过程叫「回表」，也就是说要查两个 B+Tree 才能查到数据。

• 覆盖索引：不过，当查询的数据是能在二级索引的 B+Tree 的叶子节点里查询到，这时就不用再回主键索引查，这种在二级索引的 B+Tree 就能查询到结果的过程就叫作「覆盖索引」，也就是只需要查一个 B+Tree 就能找到数据。覆盖索引优化：将所查的数据建立一个联合索引。

• 为什么 MySQL InnoDB 选择 B+ Tree 作为索引的数据结构？

  1. B+ Tree vs B Tree。B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+ Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。

     另外，B+ Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找（范围查找） ，而 B Tree无法做到这一点。

     B+ 树的插入、删除，由于有冗余节点，可能存在节点的分裂、变形，而且 B+ 树会自动平衡，不需要更多复杂的算法，类似红黑树的旋转操作等。因此，B+ 树的插入和删除效率更高。B+ Tree更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

     （存在大量范围检索的场景，适合使用 B+树，比如MySQL数据库InnoDB；而对于大量的单个索引查询的场景，可以考虑 B 树，比如 NoSQL 的MongoDB）

  2. B+ Tree vs 二叉树。即使数据达到千万级别时，B+ Tree 的高度依然维持在 3 ~ 4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

  3. B+ Tree vs Hash。Hash 表不适合做范围查询，它更适合做等值的查询（复杂度O(1)）， 因此B+ Tree 索引要比 Hash 表索引有着更广泛的适用场景。

• 前缀索引：是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

• 联合索引：最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。

• 索引下推优化（index condition pushdown)， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

• 常见的索引失效场景：

  1. 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；

  2. 在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；

  3. 联合索引要能正确使用需要遵循最左匹配原则，否则就会导致索引失效。

  4. 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

• 哪种 count 性能最好？

  count(*) = count(1) > count(主键字段)> count(字段)

  count(*) 其实等于 count(0)，count(*) 执行过程跟 count(1) 执行过程基本一样的。

  count(1)、 count(*)、 count(主键字段)在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描。

  不要使用 count(字段) 来统计记录个数，因为它的效率是最差的，会采用全表扫描的方式来统计。

• 如何优化 count(*)？

1. 近似值

   如果你的业务对于统计个数不需要很精确，比如搜索引擎在搜索关键词的时候，给出的搜索结果条数是一个大概值。可以使用 show table status 或者 explain 命令来表进行估算。

2. 额外表保存计数值

   如果是想精确的获取表的记录总数，我们可以将这个计数值保存到单独的一张计数表中。

MySQL事务篇

事务是在 MySQL 引擎层实现的

• 事务的4个特征（重点隔离性）

  1） 原子性（Atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节，而且事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。

  2） 一致性（Consistency）：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。

  3） 隔离性（Isolation） ：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。

  4） 持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

• InnoDB 引擎通过什么技术来保证事务的这四个特性的呢？

  1） 持久性是通过 redo log（重做日志）来保证的；

  2）原子性是通过 undo log（回滚日志） 来保证的；

  3）隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；（通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制））

  4）一致性则是通过持久性+原子性+隔离性来保证；

• 并行事务会引发什么问题？

  在同时处理多个事务的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题。

  1）脏读

  如果一个事务**「读到」了另一个「未提交事务修改过的数据」**，就意味着发生了「脏读」现象。如果事务 A 发生了回滚，那么事务 B 刚才得到的数据就是过期的数据，这种现象就被称为脏读。

  2）不可重复读

  在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了「不可重复读」现象。

  3）幻读

  在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就感觉发生了幻觉一样，就意味着发生了「幻读」现象。

  严重程度：脏读 > 不可重复读 > 幻读

• 事务的隔离级别有哪些？

  SQL 标准提出了四种隔离级别来规避这些现象，隔离级别越高，性能效率就越低，这四个隔离级别如下：

  1）读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；

  2）读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；

  3）可重复读（repeatable read） ，指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；

  「可重复读」隔离级别下在事务期间读到的记录都是事务启动前的记录。

  4）串行化（serializable），会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

  按隔离水平高低排序如下： 串行化 > 可重复读 > 读已提交 > 读未提交

 在「读未提交」隔离级别下，可能发生脏读、不可重复读和幻读现象；
 在「读提交」隔离级别下，可能发生不可重复读和幻读现象，但是不可能发生脏读现象；
 在「可重复读」隔离级别下，可能发生幻读现象（但是可以很大程度上避免幻读现象的发生），但是不可能脏读和不可重复读现象；
 在「串行化」隔离级别下，脏读、不可重复读和幻读现象都不可能会发生。

• 隔离级别如何实现

  对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View。

  而**「可重复读」隔离级别是「启动事务时」**生成一个 Read View，然后整个事务期间都在用这个 Read View。

• 避免幻读的方案：

  针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读。

  针对当前读（select ... for update 等语句），比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。通过next-key lock（记录锁+间隙锁） 解决幻读。