Mysql知识库【总结】

MySQL是一种关系型数据库管理系统（RDBMS），其底层原理可以简单概括为以下几个方面：

- 存储引擎：MySQL支持多种存储引擎，如MyISAM、InnoDB、Memory等。每种存储引擎的实现方式不同，它们各自的特点和使用场景也不同。例如，MyISAM存储引擎适合于读多写少的场景，而InnoDB存储引擎则适合于事务处理。

数据结构：MySQL使用B-tree索引来加快数据访问速度。什么是B-tree索引

• B-tree索引是一种树形结构，每个节点可以包含多个键值对。通过B-tree索引，MySQL可以快速定位到需要的数据。

• B-Tree 对索引列的值是按顺序存储的，并且每一个叶子页到根的距离相同。B-Tree 索引可以加快数据查找的速度，因为存储引擎不需要全表扫描来获取数据，只要从索引的根节点开始搜索即可

Mysql的事务处理

• MySQL支持事务处理，这是一种可以保证数据库操作的原子性、一致性、隔离性和持久性的机制。MySQL使用ACID模型来保证事务的正确性和可靠性。

  SQL解析器和执行引擎：MySQL使用SQL解析器来解析SQL语句，并使用执行引擎执行SQL语句。MySQL的执行引擎包括查询优化器、查询执行器等组件，它们共同协作来完成SQL查询任务。

这里就要提到mysql的四大特性

原子性

• 原子性确保数据库事务是不可分割的工作单位。事务中的所有操作要么全部执行成功，要么全部不执行，不会部分执行。如果事务中的任何一部分失败，整个事务就会被回滚，好像从未开始过一样。

• 例子:
  我向女朋友转账1314元，如果转账成功了，那么我的余额就会减少1314元，我女朋友的账户余额会增加1314元。如果发生某种错误，转账未成功，那么我转账这件事就会回滚到我没发起转账的状态，我的余额不会因为女朋友没有收到而少去520.

  如何保证原子性？

  主要是利⽤undo log（回滚⽇志），回滚日志记录事务执行前数据的状态，以便在事务需要回滚时，能够撤销已执行的操作，恢复数据库到事务开始前的状态。

  例如：

  delete⼀条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据
  update⼀条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执⾏update操作
  insert⼀条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执⾏delete操作

一致性

• 一致性（Consistency）：事务前后数据的完整性必须保持一致

• 例子:
  一致性保证事务执行前后，数据库从一个一致的状态转变为另一个一致的状态。即使在事务执行期间系统出现故障，数据库也必须保持在一个有效且符合预期的状态中。所谓一致性，就是数据符合我们对它的某些限制。比如，我转账的钱 = 我女朋友收到的钱。

  如何保证一致性？

  分为两个层⾯来说。
  从数据库层⾯，数据库通过原⼦性、隔离性、持久性来保证⼀致性。也就是说ACID四⼤特性之中，C(⼀致性)是⽬的，A(原⼦性)、I(隔离性)、D(持久性)是⼿段，是为了保证⼀致性，数据库提供的⼿段。数据库必须要实现AID三⼤特性，才有可能实现⼀致性。例如，原⼦性⽆法保证，显然⼀致性也⽆法保证。

隔离性

• 隔离性（Isolation）：多个用户并发访问数据库时，一个用户的事务不能被其他用户的事务所干扰，多个并发事务之间数据要相互隔离。隔离性由隔离级别保障！（不用的隔离级别有不同程度的隔离保障）

  MySQL 默认的事务隔离级别为可重复读（repeatable-read)

  如果无法保证事务的隔离就会有一下问题

• 脏读
  一个事务可以读取到另一个事务尚未提交的数据。如果后者最终回滚了其更改，那么前者读取到的数据就是无效的，这可能导致错误的业务决策或数据不一致。读到了无效数据！

  不可重复读
  在同一事务中，如果两次读取同一数据，可能会得到不同的结果，因为其他事务在这两次读取之间已经修改并提交了该数据。这违反了用户对数据稳定性的期望。前后两次发生了数据的修改！

  幻读
  在一个事务内，多次执行同一条查询语句，每次返回的结果集可能会有差异，原因是有其他事务在这期间向表中插入了新行，导致原本不满足条件的记录出现在结果集中。这对于依赖特定数据集合进行计算或判断的事务来说是个问题。前后两次发生了数据的增加或删除！

  如何保证隔离性？

• 这里就延伸到了我们mysql另一个面试常问的问题

  第一种:提供事务的隔离级别

• 读未提交（Read Uncommitted）：事务可以读取其他未提交事务修改的数据，可能导致脏读。

• 读已提交（ReadCommitted）：事务只能读取其他已提交事务的结果，避免了脏读，但可能出现不可重复读。

• 可重复读（Repeatable Read）：在一个事务内多次读取同一条数据结果是一样的，避免了不可重复读，但可能遇到幻读问题。

• 串行化（Serializable）：最严格的隔离级别，通过串行执行事务或者使用更高级的锁机制来避免所有并发问题，包括脏读、不可重复读和幻读，但性能开销最大。

  

  第二种: 并发控制

• 锁机制：包括行锁、表锁、页锁等。通过在事务访问数据前加锁，阻止其他事务修改或读取，直到当前事务结束释放锁。锁的类型和粒度会影响并发性能。

• 多版本并发控制（MVCC, Multiversion ConcurrencyControl）：允许多个事务读取同一份数据的不同版本，而不会相互阻塞。每个事务看到的是基于事务开始时刻数据的一个快照，从而避免了读写冲突，广泛应用于如PostgreSQL、MySQL的InnoDB引擎中。

• 时间戳排序：为事务分配时间戳，事务按照时间戳顺序执行，以避免冲突。

• 乐观锁与悲观锁：乐观锁假设并发冲突较少，仅在提交时检查数据是否被修改；悲观锁则假定会发生并发冲突，提前加锁防止修改。

• 持久性（Durability）：一个事务一旦提交，他对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

• 持久性主要通过redolog（重做日志）来实现。每当数据库中的数据发生变化时，数据库系统首先将这些变化记录到事务日志中。事务日志通常是顺序写入磁盘上的非易失性存储介质（如HDD或SSD），以确保即使在系统崩溃或电源故障的情况下，数据的修改操作仍能被追踪并恢复。在事务提交前，其相关日志会被标记为“已准备好提交”。如果发生故障，系统重启后可以通过重做（redo）日志将未完全写入数据库的数据操作完成，从而恢复到一致状态。

• 我们在公司操作数据库 数据表的时候一定要谨慎操作 一定要做数据备份!!!

  在MySQL中，为了解决CPU和磁盘速度不⼀致问题，MySQL是将磁盘上的数据加载到内存，对内存进⾏操作，然后再回写磁盘。假设宕机了，在内存中修改的数据全部丢失了，持久性就⽆法保证。

  怎么解决？
  使用预写日志！在实际修改数据库之前，先将修改记录写入日志中。这样即使在修改数据和更新内存与磁盘之间发生故障，也能根据日志恢复到一致状态。

看了这么多有人会说什么是mysql的MVCC呢！

什么是MVCC

• 多版本并发控制（MVCC），是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。
  这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。

  MVCC的底层数据结构

• 版本链

  每个数据项都有多个版本:
  数据项（如表中的一行）的每个修改都会创建一个新的版本。每个版本都包含一个时间戳或事务ID，以标识该版本的有效期。
  版本链是一种链表结构，其中每个版本都指向其前一个版本。这样可以跟踪每个数据项的历史版本。
  版本号或时间戳

• 事务ID或时间戳:

  每个事务在开始时会获取一个事务ID或时间戳，用于标记事务的开始时间。
  数据的每个版本都带有一个创建时间戳和一个有效期（通常是删除时间戳或事务ID）。这使得事务可以根据自己的视图看到在它开始之前提交的数据。
  快照视图

• 一致的快照:

  在事务开始时，数据库系统创建一个快照，记录所有有效版本的数据。事务只会看到在快照时间之前已提交的版本，从而确保事务的读操作在其生命周期内一致。
  可见性规则

• 读取可见性:

  在事务中，读取操作根据事务的开始时间戳或ID，访问所有在该时间戳之前提交的版本。
  写操作创建新的版本，并标记其有效期。

怎么实现数据库的隔离级别呢?

• 读未提交（Read Uncommitted）

  特性:
  最低的隔离级别，允许一个事务读取另一个未提交事务的数据。
  可能导致脏读（Dirty Read）。

  实现机制:
  没有锁: 数据库系统不对读取的数据加锁。
  实现简单: 只需在读取数据时没有锁定机制，因此可能读取到其他事务未提交的数据。

• 读已提交（Read Committed）

  特性:
  数据只可读已提交事务的数据，避免了脏读。
  可能会导致不可重复读（Non-repeatable Read），即同一事务中读取的数据可能因其他事务的提交而变化。

  实现机制:
  读操作: 读取数据时加共享锁，确保只能读取已提交的数据。
  写操作: 写操作在数据上加排他锁，确保写操作的原子性和隔离性。

• 可重复读（Repeatable Read）

  特性:
  确保同一事务中对数据的多次读取结果一致，避免了不可重复读。
  可能导致幻读（Phantom Read），即在同一事务中执行的查询可能在其他事务插入数据后返回不同的结果。

  实现机制:
  加锁机制: 对读取的数据加共享锁，确保在事务结束前数据不能被其他事务修改。
  多版本并发控制（MVCC）: 通过维护数据的多个版本来实现可重复读，避免了对数据的锁定，从而减少了锁争用。

• 串行化（Serializable）

  特性:
  最高的隔离级别，确保事务之间完全隔离，避免脏读、不可重复读和幻读。
  事务的执行效果等同于所有事务按顺序执行。

  实现机制:
  加锁机制: 对涉及的数据加排他锁，确保在事务期间数据不被其他事务修改。
  MVCC: 维护数据的多个版本，确保每个事务看到的是一致的数据快照。
  乐观并发控制: 事务在提交前检查是否存在其他事务的冲突，确保事务提交的结果是有效的。

• 具体实现机制

  加锁机制:

  共享锁和排他锁: 控制对数据的读取和写入操作。共享锁允许多个事务读取数据但不修改，排他锁则禁止其他事务读取或修改数据。
  多版本并发控制（MVCC）:

  版本管理: 每次对数据的修改都创建一个新的版本。事务在开始时读取一个一致的快照，确保读取到的数据在事务生命周期内保持一致。
  乐观并发控制:

  冲突检测: 在事务提交时检查是否有冲突，如果检测到冲突，则回滚事务并要求重试。
  行级锁和表级锁:

  行级锁: 锁定特定行的数据，允许更高的并发性。
  表级锁: 锁定整个表，较少使用但可能更简单。

  总结
  数据库的隔离级别通过各种机制来确保事务的并发性和数据的一致性。不同的隔离级别在保护数据一致性和提高并发性之间进行权衡，具体实现方式包括加锁机制、MVCC和乐观并发控制等。选择适当的隔离级别取决于应用程序的需求以及对数据一致性和性能的要求。

中间我们还提到过一些日志

那么就让我们来了解一下Innodb的事务与实现方式吧

操作事务的日志一共有两个

• redo 日志

• undo 日志

  存放形式

• redo：在页修改的时候，先写到 redo log buffer 里面， 然后写到 redo log
  的文件系统缓存里面(fwrite)，然后再同步到磁盘文件（fsync）。

• undo：在 MySQL5.5 之前，undo 只能存放在 ibdata 文件里面， 5.6 之后，可以通过设置
  innodb_undo_tablespaces 参数把 undo log 存放在 ibdata 之外。

  事务是如何通过日志来实现的

• 因为事务在修改页时，要先记 undo ，在记 undo 之前要记 undo 的 redo， 然后修改数据页，再记数据页修改的 redo。
  redo（里面包括 undo 的修改）一定要比数据页先持久化到磁盘。

• 当事务需要回滚时，因为有 undo，可以把数据页回滚到前镜像的状态。 崩溃恢复时，如果 redo log 中事务没有对应的 commit
  记录，那么需要用 undo 把该事务的修改回滚到事务开始之前。如果有 commit 记录，就用 redo 前滚到该事务完成时并提交掉。

InnoDB 和 MyISAM 的区别

SQL语句的执行顺序

MySQL 查询执行的顺序是：
(1)     SELECT
(2)     DISTINCT <select_list>
(3)     FROM <left_table>
(4)     <join_type> JOIN <right_table>
(5)     ON <join_condition>
(6)     WHERE <where_condition>
(7)     GROUP BY <group_by_list>
(8)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

具体的，可以看看 《SQL 查询之执行顺序解析》 文章。

https://zouzls.github.io/2017/03/23/SQL%E6%9F%A5%E8%AF%A2%E4%B9%8B%E6%89%A7%E8%A1%8C%E9%A1%BA%E5%BA%8F%E8%A7%A3%E6%9E%90/