MySQL

• 优点：
  • 大大加快数据的检索速度（减少检索的数据量），减少磁盘 I/O 次数。
  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 缺点：
  • 创建索引和维护索引要耗费时间，降低数据的维护速度。
  • 索引需要占用物理空间，降低数据存储量。

常见的索引数据结构包括： Hash 表、二叉查找树（BST）、平衡二叉查找树（AVL）、红黑树、B 树 和 B+ 树等。

目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B 树和 B+ 树都是多路平衡查找树，即每个节点有多个子节点。B 树和 B+ 树的区别主要在于：

• B 树的所以节点存储键值，B+树的非叶子节点只存储键，只有叶子节点存储键值。
• B 树的叶子节点相互独立，B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索是范围内的每个节点做二分查找，可能无需遍历到叶子节点，B+树的检索是范围内的每个节点都要遍历到叶子节点。
• B 树的范围查询，首先找到要查找的下限，然后进行中序遍历，直到找到上限；B+树的范围查询，只需对叶子节点进行遍历。

综上，B+ 树的优点在于，具有更少的 IO 次数，更稳定的查询效率，更适合范围查询。

• 聚簇索引（聚集索引）： 索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。准确来说，是将数据存储在叶子节点中。
• 非聚簇索引（非聚集索引）： 索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。在叶子节点中存储叶子节点本身的索引值和主键值（MyISAM 中存放是数据的地址值），有了主键值再进行一次回表操作，从聚簇索引中根据主键值定位数据位置。

• 覆盖索引： 覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。
• 组合索引（复合索引） 使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

在 InnoDB 存储引擎中，非主键索引的叶子节点包含的是主键的值。这意味着，当使用非主键索引进行查询时， 数据库会先找到对应的主键值，然后再通过主键索引来定位和检索完整的行数据。这个过程被称为“回表”。

最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。

如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，这样可以提高查询效率。

最左匹配原则会一直向右匹配，直到遇到范围查询（如 >、<）为止。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配。

• 不为 NULL 的字段： 索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。 如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
• 被频繁查询的字段： 我们创建索引的字段应该是查询操作非常频繁的字段。
• 被作为条件查询的字段： 被作为 WHERE 条件查询的字段，应该被考虑建立索引。
• 频繁需要排序的字段： 索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
• 被经常频繁用于连接的字段： 经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键， 只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

事务是一组操作的集合，要么全部执行成功，要么全部回滚。事务的 ACID 特性包括：

• 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
• 一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
• 隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
• 持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

• 脏读：读取到未提交的数据。
• 不可重复度：多次读取数据，但在读取过程中，另一个事务修改了数据，导致多次读取的数据不一致。
• 幻读：多次读取数据，但在读取过程中，另一个事务插入或删除了数据，导致多次读取的查询数据总量不一致。
• 丢失修改：两个事务同时读取同一数据，其中一个事务修改了数据，另一个事务也修改了数据，导致其中一个事务的修改被覆盖。

不可重复读是读取了其他事务更改的数据，针对update操作。使用行级锁，锁定该行，事务 A 多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。

幻读是读取了其他事务新增的数据，针对insert与delete操作。使用表级锁，锁定整张表，事务 A 多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。

SQL 标准定义了四种事务隔离级别，不同的隔离级别对并发事务的影响不同，包括：

• 读未提交（Read Uncommitted，RU）：允许脏读，一个事务可以读取到另一个事务未提交的数据。
• 读已提交（Read Committed，RC）：只能读取到已提交的数据，避免脏读，但可能出现不可重复读。
• 可重复读（Repeatable Read，RR）：保证同一事务中多次读取的数据是一致的，除非数据是被本身事务自己修改的。
• 串行化（Serializable）：最高隔离级别，保证事务串行执行，避免脏读、不可重复读和幻读。

• 升级事务隔离级别：将隔离级别升级到 Serializable，保证事务串行执行，避免幻读。
• 使用锁机制：Next-Key Lock，保证不会出现幻读。

MySQL InnoDB 存储引擎默认的隔离级别是可重复读（Repeatable Read）。采用以下方式解决幻读问题的发生：

• 快照读：在可重复读隔离级别下，通过 MVCC 机制，读取到的是快照数据，不会受到其他事务的影响。
• 当前读：使用 Next-Key Lock 进行加锁来保证不出现幻读，Next-Key Lock 是行记录锁（Record Lock）和间隙锁（Gap Lock）的结合，行锁只能锁住已经存在的行，为了避免插入新行，需要依赖间隙锁。

锁的粒度

• 行锁
  • 记录锁（Record Lock）：属于单个行记录上的锁。
  • 间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。
  • 临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。
• 表锁
• 页锁：锁定数据页

锁的类型

• 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
• 排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。
• 意向锁（表锁）
  • 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
  • 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

死锁是指两个或两个以上的事务之间互相等待对方具有的锁，同时持有对方需要的锁。

• 避免死锁
  • 控制资源获取顺序：事务按照一定顺序获取一系列锁，获取不到释放当前占有的锁。
  • 控制锁的粒度或少用锁：通过将隔离等级从 RR 更改为 RC，避免间隙锁和 next-key 锁带来的死锁情况。
  • 减少持有资源的时间：事务具有长时间锁的需求，可以将 SQL 语句集中在事务的最前面或最后面。
• 解决死锁
  • 目前的数据库都一般都具备自动干预死锁的能力。比如 mysql 中可以通过同等待图机制检测到死锁后选择回滚一个开销较小的事务来解决死锁; 还可以通过设置一个事务的超时时间来避免死锁；

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

• 隐藏字段：

  • DB_TRX_ID：表示该行记录的创建或修改事务的 ID。
  • DB_ROLL_PTR：表示该行记录的回滚指针，指向 undo log 中的历史版本。
  • DB_ROW_ID：表示该行记录的唯一 ID。

• Read View：在某一时刻给事务系统的 trx_sys 打 snapshot，把当时 trx_sys 状态（包括活跃读写事务数组）记下来，之后的所有读操作根据其事务 ID（即 trx_id）与 snapshot 中的 trx_sys 的状态作比较，以此判断 read view 对于事务的可见性。

快照读：读取的是记录的可见版本（可能是历史版本），不加锁。 MySQL 中 InnoDB 存储引擎的快照读(Snapshot Read)是一种读取数据的方式，它可以在事务开始时创建一个数据快照，这个快照是一致性的，即读取在事务开始时或特定时间点之前提交的数据。 其实就是简单的 select 操作。如：

• select * from table where ?;

当前读：读取的是记录的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发修改这条记录。 其实就是特殊的读操作，比如插入、更新、删除属于当前读，需要加锁的也属于当前读。如：

• 共享锁：select * from table where ? lock in share mode;
• 排他锁：select * from table where ? for update;
• 插入：insert into table values(?);
• 更新：update table set ? where ?;
• 删除：delete from table where ?;

事务总能够读取到自己写入(update /insert /delete)的行记录。而其他事务的提交，则分情况。 RC 模式，快照读总是能读到最新的行数据快照，当然，必须是已提交事务写入的。 RR 模式，某个事务首次 read 记录的时间为 T1，之后的操作不会读取到 T1 时间之后已提交事务写入的记录，以保证连续相同的 read 读到相同的结果集。 简单点说：

• RR 下，事务在第一个 Read 操作时，会建立 Read View，并贯穿整个事务的过程，保证了可重复读的效果。
• RC 下，事务在每次 Read 操作时，都会建立 Read View，以保证获取到的都是数据库中最新的被 Commit 的值。

• 作者：Brand
• 出处：https://www.cnblogs.com/wzh2010/

为了保证逻辑日志和物理日志的一致性；如果没有二阶段提价的话；如果插入操作先提交了 redo log 此时 undo log 还没有进行写盘此时断电了；再次重启数据库后 bin log 就无法感知导致从数据库比主数据库少一条数据； 如果先写入 bin log 再写入 redo log, 如果 bin log 完成写入后崩溃那么 redo log 就无法感知未记录的事务, 就会导致从数据中有这个事务但是主数据中没有。

• 作者：不吃勤菜_Java 版
• 链接：https://www.nowcoder.com/discuss/727194657410531328?sourceSSR=search
• 来源：牛客网

Explain 是 MySQL 提供的用于分析查询语句的工具，可以帮助开发人员分析查询语句的性能，找出可能存在的问题。 重要的关注指标包括：select_type、type、possible_keys、key、rows 等。