[中间件] MySQL

MySQL

基础架构

执行一条 select 语句，期间发生了什么？

下面就是 MySQL 执行一条 SQL 查询语句的流程，也从图中可以看到 MySQL 内部架构里的各个功能模块。

SQL 查询语句

可以看到， MySQL 的架构共分为两层：Server 层和存储引擎层，
Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。
存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树 ，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

  • 连接器。如果你在 Linux 操作系统里要使用 MySQL，那你第一步肯定是要先连接 MySQL 服务，然后才能执行 SQL 语句。 MySQL 的连接也跟 HTTP 一样，有短连接和长连接的概念，使用长连接的好处就是可以减少建立连接和断开连接的过程，所以一般是推荐使用长连接。
  • 查询缓存。连接器得工作完成后，客户端就可以向 MySQL 服务发送 SQL 语句了，MySQL 服务收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句。如果 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。
    MySQL 8.0 版本直接将查询缓存删掉了，也就是说 MySQL 8.0 开始，执行一条 SQL 查询语句，不会再走到查询缓存这个阶段了。
  • 解析 SQL。在正式执行 SQL 查询语句之前， MySQL 会先对 SQL 语句做解析，这个工作交由「解析器」来完成。
    解析器会做如下两件事情。
    第一件事情，词法分析。MySQL 会根据你输入的字符串识别出关键字出来，第二件事情，语法分析。根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法，如果没问题就会构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。
  • 执行 SQL。经过解析器后，接着就要进入执行 SQL 查询语句的流程了，每条SELECT 查询语句流程主要可以分为下面这三个阶段：
    prepare 阶段，也就是预处理阶段；预处理会检查 SQL 查询语句中的表或者字段是否存在，并且将 select * 中的 * 符号，扩展为表上的所有列；
    optimize 阶段，也就是优化阶段；优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。
    execute 阶段，也就是执行阶段；经历完优化器后，就确定了执行方案，接下来 MySQL 就真正开始执行语句了，这个工作是由「执行器」完成的。在执行的过程中，执行器就会和存储引擎交互了，交互是以记录为单位的。

索引下推

MySQL 5.6 推出了索引下推的查询优化策略
索引下推能够减少二级索引在查询时的回表操作，提高查询的效率，因为它将 Server 层部分负责的事情，交给存储引擎层去处理了。
举一个具体的例子，方便大家理解，这里一张用户表对 age 和 reward 字段建立了联合索引（age，reward）：
现在有下面这条查询语句：

select * from t_user  where age > 20 and reward = 100000;

联合索引当遇到范围查询 (>、<) 就会停止匹配，也就是 age 字段能用到联合索引，但是 reward 字段则无法利用到索引。
没有索引下推的时候，每查询到一条二级索引记录，都要进行回表操作，然后将记录返回给 Server，接着 Server 再判断该记录的 reward 是否等于 100000。

而使用索引下推后，判断记录的 reward 是否等于 100000 的工作交给了存储引擎层，过程如下 ：

  • Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；
  • 存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列（reward列）的条件（reward 是否等于 100000）是否成立。如果条件不成立，则直接跳过该二级索引。如果成立，则执行回表操作，将完成记录返回给 Server 层。
  • Server 层在判断其他的查询条件（本次查询没有其他条件）是否成立，如果成立则将其发送给客户端；否则跳过该记录，然后向存储引擎索要下一条记录。
  • 如此往复，直到存储引擎把表中的所有记录读完。
    可以看到，使用了索引下推后，虽然 reward 列无法使用到联合索引，但是因为它包含在联合索引（age，reward）里，所以直接在存储引擎过滤出满足 reward = 100000 的记录后，才去执行回表操作获取整个记录。相比于没有使用索引下推，节省了很多回表操作。

当你发现执行计划里的 Extr 部分显示了 “Using index condition”，说明使用了索引下推。

存储

MySQL一张数据库表的数据是保存在「 表名字.ibd 」的文件里的，这个文件也称为独占表空间文件。另外「 表名字.frm 」会保存每个表的元数据信息的，主要包含表结构定义（快速插列就用到了这个表）。
表空间由段（segment）、区（extent）、页（page）、行（row）组成，「页」是InnoDB 的数据读写的最小单位，也就是说，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。
默认每个页的大小为 16KB，也就是最多能保证 16KB 的连续存储空间。

行格式（row_format），就是一条记录的存储结构。
InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和 Compressed 行格式。
Redundant 是很古老的行格式了， MySQL 5.0 版本之前用的行格式，现在基本没人用了。
由于 Redundant 不是一种紧凑的行格式，所以 MySQL 5.0 之后引入了 Compact 行记录存储方式，Compact 是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从 MySQL 5.1 版本之后，行格式默认设置成 Compact，Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，因为都是基于 Compact 改进一点东西。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式。
先看一下 Compact 格式：

Compact

可以看到，一条完整的记录分为「记录的额外信息」和「记录的真实数据」两个部分。
记录的额外信息包含 3 个部分：变长字段长度列表、NULL 值列表、记录头信息。

  1. 变长字段长度列表。在存储数据的时候，要把数据占用的大小存起来，存到「变长字段长度列表」里面，读取数据的时候才能根据这个「变长字段长度列表」去读取对应长度的数据。另外，这些变长字段的真实数据占用的字节数会按照列的顺序逆序存放，因为这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。当然，其实变长字段字节数列表不是必须的。「变长字段长度列表」只出现在数据表有变长字段的时候。
  2. NULL 值列表
    如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。
    二进制位的值为1时，代表该列的值为NULL。
    二进制位的值为0时，代表该列的值不为NULL。
    当然NULL 值列表也不是必须的。当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。
  3. 记录头信息
    记录头信息中包含的内容很多，这里说几个比较重要的：
  • delete_mask ：标识此条数据是否被删除。从这里可以知道，我们执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete_mask 标记为 1。
  • next_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。在前面我也提到了，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
  • record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录

记录真实数据部分除了我们定义的字段，还有三个隐藏字段，分别为：row_id、trx_id、roll_pointer。

  • row_id
    如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的，占用 6 个字节。
  • trx_id
    事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节。
  • roll_pointer
    这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。

缓冲池

缓冲池中页的大小默认为16KB。MySQL中读取数据首先将磁盘读到的页存放在缓冲池中，下一次读到相同的页时，首先判断是否在缓冲池中，如果在缓冲池中，直接读取该页，否则读取磁盘上的页。
修改数据首先修改在缓冲池中的页（修改后即脏页），然后再通过一种称为Checkpoint的机制以一定的频率刷新到磁盘上
LRU算法用来维护索引页和数据页, 通过最近最少使用算法将最频繁使用的页在LRU列表的前端, 此外，InnoDB做了一些优化，加入了midpoint位置（3/8），将缓冲池分为热数据区域链表和冷数据区域链表，磁盘中的数据页第一次加载到缓存页时，对应的描述数据块放到冷数据区域的链表头部，然后在配置阈值 (innodb_old_blocks_time)后，如果再次访问这个缓存页，才会将缓存页对应的描述数据块移动到热数据区域的链表头部去，以解决批量数据访问，大量热数据淘汰的问题.
另外，MySQL 针对新生代其实做了一个优化，为了防止热数据区域节点频繁移动。热数据区域前面 1/4 被访问不会移动，只有后面的 3/4被访问了才会到链表头部。

索引

数据库中，索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录。 InnoDB 在 MySQL 5.5 之后成为默认的存储引擎。
我们可以按照四个角度来分类索引。
按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类：单列索引、联合索引。
在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

  • 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
  • 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
  • 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；
    其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。

B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。
B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。
主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

  1. B+Tree vs B Tree; B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。
  2. B+Tree vs 二叉树
    对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。
    在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3~4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。
  3. B+Tree vs Hash
    Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。

B+Tree 是一种多叉树，对于聚簇索引来说，叶子节点存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。选用B+树的原因在于数据放在叶子节点上，子节点上有更多的空间存储指针数据，会很大程度上减少树的深度，从而减少对树的遍历次数和对磁盘的IO。B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。双链表连接，使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。
使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效，这样就无法利用到索引快速查询的特性了。
联合索引有一些特殊情况，并不是查询过程使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询，也就是可能存在部分字段用到联合索引的 B+Tree，部分字段没有用到联合索引的 B+Tree 的情况。
这种特殊情况就发生在范围查询。联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。
另外，需要注意的是，联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。

  1. select * from t_table where a > 1 and b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？
  2. select * from t_table where a >= 1 and b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？
  3. SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？
  4. SELECT * FROM t_user WHERE name like 'j%' and age = 22，联合索引（name, age）哪一个字段用到了联合索引的 B+Tree？

其中2，3，4查询语句 a 和 b 字段都用到了联合索引进行索引查询。也可以在执行计划中的 key_len 知道这一点。原因是对于联合索引，是先按照 a 字段的值排序，然后在 a 字段的值相同的情况下，再按照 b 字段的值进行排序,存在等于的情况下后面的字段就可以用到联合索引。例如， like j% 的情况下，可以直接定位到 name=j and age=23 的情况。当这种情况发生，索引就可以帮助我们可以省略 name=j and age<23 的所有情况，因此有情况会出现同时使用 name age 联合索引！

有什么优化索引的方法？
以下几种常见优化索引的方法：
1.前缀索引优化；使用某个字段中字符串的前几个字符建立索引
2.覆盖索引优化；覆盖 SQL 中 query 的所有字段，从而避免回表。
3.主键索引最好是自增的；这样次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。从而防止页分裂，页分裂会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。
4.索引最好设置为 NOT NULL，索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化；并且，NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题
5.防止索引失效；比如，使用左或者左右模糊匹配的时候，对索引列做了计算、函数、类型转换操作等。

事务

InnoDB存储引擎中的事务完全符合ACID的特性

  • 原子性（atomicity） 数据库事务 是 不可分割 的 工作单位,要么全部完成，要么全部不完成；通过undolog的回滚来实现
  • 一致性（consistency） 在 事务开始之前 和 事务结束以后，数据库的完整性约束没有被破坏，一致性是隔离+原子+持久保证
  • 隔离性（isolation） 事务提交前对其他事务都不可见，由MVCC或者 锁机制 来实现
  • 持久性（durability） 事务一旦提交，其结果就是永久性的；保证事务系统的高可靠性（High Reliability）；通过redolog崩溃恢复

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

  1. 脏读（Dirty Read）。脏读 指的就是 在不同的事务下，当前事务 可以读到 另外事务 未提交的数据(脏数据)
    脏读发生的条件是需要事务的隔离级别为READ UNCOMMITTED。
  2. 不可重复读。不可重复读 是指 在一个事务内 多次读取 同一数据集合，结果可能会不一致
    在这个事务还没有结束时，另外一个事务 也访问 该同一数据集合，并做了一些DML操作
    因此，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务 两次读到的数据 可能是不一样的
    这样就发生了 在一个事务内 两次读到的数据 是 不一样的情况，这种情况称为不可重复读

不可重复读和脏读的区别是：脏读 是读到 未提交的数据，而 不可重复读 读到的 却是 已经提交的数据，但是其违反了 数据库事务 一致性的要求
在InnoDB存储引擎中，通过使用Next-Key Lock算法来避免不可重复读的问题。在Next-Key Lock算法下，对于索引的扫描，不仅是锁住扫描到的索引，而且还锁住这些索引覆盖的范围（gap）。因此在这个范围内的插入都是不允许的。这样就避免了另外的事务在这个范围内插入数据导致的不可重复读的问题（幻读）

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

不可重复读和幻读的区别
不可重复读是对于同一条数据，两次读取的数据不一样，幻读指的是两次读读了到别人新增的数据，所以不可重复读侧重的是修改，幻读侧重的是新增。
所以“不可重复读”和“幻读”都是读的过程中数据前后不一致，只是前者侧重于修改，后者侧重于增。严格来讲“幻读”可以被称为“不可重复读”的一种特殊情况，没错的。但是从数据库管理的角度来看二者是有区别的。解决“不可重复读”只要加行级锁就可以了。而解决“幻读”则需要加表级锁，或者采用其他更复杂的技术，总之代价要大许多。
delete和update可以对记录加锁，保证事务安全。而insert，由于插入行不存在，无法加锁，只能引入间隙锁解决，这也是幻读单独拿出来的原因。

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

  1. READ UNCOMMITTED-读未提交；一个事务能读到其他事务没有提交数据结果，会产生脏读，不可重复读和幻读。
  2. READ COMMITTED-读已提交；一个事务读取其他事务已经提交的数据，读取到的是最新的数据；会出现不可重复读和幻读问题
  3. REPEATABLE READ-可重复读；在同一事务中多次读取同样的数据结果是一样的，会出现幻读的问题
  4. SERIALIZABLE-串行化；强制事务排序阻塞，避免事务冲突，解决了上述所有的问题

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象，解决的方案有两种：

  • 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
  • 针对当前读（select ... for update 等语句, 每次读都是拿到最新版本的数据），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

但不能完全解决幻读现象的问题。比如下面两个发生幻读场景的例子。
第一个例子：对于快照读， MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录，那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读。
第二个例子：对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读。
所以，MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select ... for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

为什么MySQL默认使用RR隔离教别?
MySQL默认的隔离级别是RR。因为MySQL在主从复制的过程中,数据的同步是通过bin log进行的,而SQL早期只有statement这种bin log格式，里面记录的就是SQL语句的原文，如果使用读未提交和读已提交这两种隔离级别会使得语句无法复现，造成从库与主库数据不同的问题。

为什么很多大厂要改成RC?
虽然RR的隔离级别可以在一定程度上避免脏读、不可重复度和幻读等问题,但是,对于很多大型的互联网来说,会愿意将数据库的默认隔离级别调整成并发度更高的RC级别,从而,提升并发度并且降低发生死锁的概率。但需要自己处理幻读的问题，以及bin log格式不能使用statement

什么是快照读与当前读？
MySQL 里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。另外，select ... for update 这种查询语句是当前读，每次执行的时候都是读取最新的数据。

什么是MVCC?
MVCC 就是 在多个事务同时存在时，SELECT语句找寻到具体是版本链上的哪个版本，然后再找到的对应版本上 返回其中所记录的数据 的 过程.
在MySQL中，MVCC 只在 读取已提交（Read Committed）和 可重复读（Repeatable Read）两个事务级别下有效.
MVCC在并发读写数据库时，可以做到 在读操作时 不用阻塞 写操作，写操作 也不用阻塞 读操作，提高了数据库并发读写的性能。MVCC 最大的好处是读不加锁，读写不冲突。写写冲突通过排他锁（x锁）解决。

MVCC的实现原理 主要是 依赖 记录(数据)中的隐式字段、undo日志 和 Read View 来实现的

  • insert undo log，代表事务在 insert时产生的 undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
  • update undo log，事务在进行 update 或 delete 时产生的 undo log，不仅在事务回滚时需要，在快照读时也需要，所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被 purge线程统一清除
  • Read View 就是 事务 进行 快照读操作 的 时候 生成的视图；在 事务执行快照读操作的那一刻，会生成数据库系统当前的一个快照，记录并维护当前活跃事务的 ID (当每个事务开启时，都会被分配一个 ID，这个 ID 是递增的，所以最新的事务，ID 值越大). Read View 主要是用来 做 可见性判断的，里面保存了“对 本事务不可见 的 其他活跃事务”,即当某个事务执行快照读的时候，对该记录创建一个 Read View 读视图，把它(视图) 当做条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是 该行记录undo log里面的某个版本的数据

READ COMMITTED和REPEATABLE READ隔离级别 的 一个 非常大的区别 就是 它们生成Read View的时机不同
READ COMMITTED隔离级别的事务 在 每次查询 开始时 都会生成 一个独立的Read View
REPEATABLE READ隔离级别的事务 在 第一次 读取数据 时 生成一个Read View，后面就不再重复生成

锁

在 MySQL 里，根据加锁的范围，可以分为全局锁、表级锁和行锁三类。

行级锁的类型主要有三类：

  • Record Lock，记录锁，也就是仅仅把一条记录锁上；记录锁，我们是要考虑 X 型与 S 型关系
  • Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；间隙锁之间是兼容的
  • Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
    间隙锁目的是为了防止其他事务插入数据，因此间隙锁与间隙锁之间是相互兼容的。

MySQL 是怎么加行级锁的？
普通的 select 语句是不会对记录加锁的（除了串行化隔离级别），因为它属于快照读，是通过 MVCC（多版本并发控制）实现的。select ... for update;独占锁 或者select ... lock in share mode;共享锁 查询会加锁的语句称为锁定读。
除了上面这两条锁定读语句会加行级锁之外，update 和 delete 操作都会加行级锁，且锁的类型都是独占锁(X型锁)。
加锁的对象是索引，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。但是，next-key lock 在一些场景下会退化成记录锁或间隙锁。
那到底是什么场景呢？总结一句，在能使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成记录锁或间隙锁。
在线上在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁（但并不是表锁），相当于把整个表锁住了

死锁
死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：设置事务等待锁的超时时间; 开启主动死锁检测; 从业务的角度，对订单做幂等性校验的目的是为了保证不会出现重复的订单，那我们可以直接将 order_no 字段设置为唯一索引列，利用它的唯一性来保证订单表不会出现重复的订单，不过有一点不好的地方就是在我们插入一个已经存在的订单记录时就会抛出异常。

MySQL只操作同一条记录，也会发生死锁吗？
会，因为数据库的锁锁的是索引，并不是记录。当我们在事务中，更新一条记录的时候，如果用到普通索引作为条件，那么会先获取普通索引的锁，然后再尝试获取主键索引的锁。
那么这个时候，如果刚好有一个线程，已经拿到了这条记录的主键索引的锁后，同时尝试在该事务中去拿该记录的普通索引的锁。这时候就会发生死锁。

日志

undo log

undo log 回滚日志，用于事务回滚及实现MVCC功能，保证事务的原子性。undo log是逻辑日志
一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id：通过 ReadView + undo log 可以实现 MVCC（多版本并发控制）。
undo log 会写入 Buffer Pool 中的 Undo 页面。undo log 和数据页的刷盘策略是一样的，都需要通过 redo log 保证持久化。

redo log

redo log 重做日志，用于崩溃恢复，记录的是数据页的物理变化，保证事务的持久性。redo log是物理日志，是循环写的，空间固定会用完；
InnoDB 的更新操作采用的是 Write Ahead Log (预先日志持久化) 策略，即先写日志，再写入磁盘。
当一条记录更新时，redo流程大致如下
第一步：InnoDB 会先把记录从硬盘读入内存(缓冲池)
第二部：修改数据的内存拷贝，即脏页
第三步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值
第四步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式( 这样redo log 写入磁盘的开销更小)
第五步：定期将内存中修改的数据刷新到磁盘中(注意，不是从redo log file刷入磁盘，而是从内存刷入磁盘，redo log file只在崩溃恢复数据时才用)，如果数据库崩溃，则依据redo log buffer、redo log file进行重做，恢复数据,这才是redo log file的价值所在

如何保证事务的持久性的呢？
当事务commit提交时，innodb引擎先将 redo log buffer 写入到 redo log file 进行持久化，待事务的commit操作完成时才算完成。也就是在持久化一个数据页之前，先将内存中相应的日志页持久化。日志页持久化需要通过fsync操作；,保证立即由os cache文件系统缓存写入redo log file

bin log

bin log 二进制日志，用于数据备份，主从复制，灾难恢复, 适用于所有存储引擎，记录了所有数据库表结构变更和表数据修改。是逻辑日志，binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

具体更新一条记录 UPDATE t_user SET name = 'xx' WHERE id = 1; 的流程如下:

  1. 执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录：
    • 如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
    • 如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。
  2. 执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：
    • 如果一样的话就不进行后续更新流程；
    • 如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；
  3. 开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log。
  4. InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术，MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo 日志，然后在合适的时间再将修改的行数据写到磁盘上。
  5. 至此，一条记录更新完了。
  6. 在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。
  7. 事务提交，剩下的就是「两阶段提交」

什么是两阶段提交？
两阶段提交用于保证 redo log 和 binlog 的逻辑一致性。两阶段提交把单个事务的提交拆分成了 2 个阶段，将 redo log 的写入拆成了两个步骤：prepare 和 commit具体如下：
在 prepare 阶段，事务会先写 redo log 并将其标记为 prepare 状态，然后写 binlog；在 commit 阶段，事务会将 redo log 标记为 commit 状态，并将 binlog 落盘。这样，无论数据库在哪个时刻发生宕机，都可以根据 redo log 和 binlog 的状态来判断事务是否提交，并保证数据的一致性。
总的来说，就是在 prepare 阶段写 redo log 和 binlog，在 commit 阶段修改 redo log 的状态并落盘 binlog。这样可以避免数据库发生异常重启或者主从切换时出现数据不一致的情况。

特性

ALTER TABLE A ADD XXX, ALGORITHM=INSTANT;

MySQL 8.0提供了快速新增列的功能。通过ALGORITHM=INSTANT可以快速新增列，在执行DDL操作时仅修改表的元数据，在DML操作时读取记录时根据当前记录数据+当前表元数据来"生成"完整数据。

执行SQL比较慢，排查思路？
可能有以下四个原因：
1.没有索引或者索引失效。通过explain查看是否命中索引，可以尝试优化索引或者SQL语句。比如是否遵循最左匹配原则，是否进行函数计算
2.单表数据量过多，导致查询瓶颈。可以对表进行水平切分或者垂直切分。
3.网络原因或者机器负载过高。可以进行读写分离，一主多从分布式部署。
4.热点数据导致单点负载不均衡。除了对数据本身的调整，可以增加缓存，缓解数据库压力。

分布式

在面对高并发、大规模数据和高可用性需求时，MySQL的单节点架构可能无法满足要求。为了实现高可用性和扩展性，可以采用MySQL的分布式架构。
MySQL的分布式架构通过将数据和负载分散到多个节点上，以提高系统的吞吐量、容错能力和可扩展性。下面将介绍几种常见的实现高可用性和扩展性的方法。

  1. 主从复制（Master-Slave Replication）： 主从复制是MySQL中最常见也是最简单的一种分布式架构模式。主从复制基于异步复制的方式，通过将写操作在主节点上执行，然后将写操作的日志传递给一个或多个从节点进行重放。从节点只用于读操作，可以提供更好的读性能。主从复制可以实现高可用性和读写分离。
    主从复制的优势在于简单易用，可以快速扩展读性能，但写性能仍然受限于主节点的性能。
  2. 分片（Sharding）： 分片是一种将数据水平划分到多个节点的方式，每个节点负责存储其中一部分数据。分片可以根据数据的某个特定字段（如用户ID或日期）进行划分，以确保相关数据存储在同一节点上。应用程序需要通过分片键来路由查询和写操作到正确的节点。分片可以提供良好的可扩展性，通过增加节点可以实现水平扩展。但是，分片会引入一定的复杂度，包括数据迁移、事务管理、跨分片查询等问题。
  3. 数据库集群（Database Clustering）： 数据库集群是将多个节点组合成一个逻辑上的数据库集合，通过共享数据和负载来提供高可用性和扩展性。集群通常采用主备模式或多主模式，以确保数据的冗余和高可用性。
    数据库集群可以提供更好的可用性和扩展性，但也会增加复杂性和维护成本。此外，集群节点之间的数据同步和一致性是需要仔细考虑的问题。
  4. 数据库中间件（Database Middleware）： 数据库中间件是一种位于应用程序和数据库之间的软件层，用于处理数据库访问和负载均衡。中间件接收应用程序的请求，并根据负载情况将请求转发给适当的数据库节点。
    数据库中间件可以提供透明的扩展性和高可用性，对应用程序无感知。然而，中间件的引入可能会带来额外的延迟和单点故障问题

主从复制是怎么实现？
主服务器提供写，从服务器提供读
MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。
这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成。。

参考：
图解MySQL
MySQL四十五讲

posted @ 2024-10-12 09:17  Jamest  阅读(20)  评论(0)  编辑  收藏  举报