mysql的隔离级别及版本控制

概念

• 脏读

  发生在当一个事务正在访问数据并对数据进行了修改，而这种修改还没有提交到数据库中时，另一个事务也访问这个数据并使用了这个数据。这种情况下，读取的数据是未提交的，因此被称为脏读。

• 不可重复读

  指在一个事务内，多次读取同一数据时，由于另一个事务的修改并提交，导致第一次和第二次读取到的数据可能不一样。这种情况发生在同一个事务内，但读取的是同一数据项，因此称为不可重复读。

• 幻读

  幻读指的是，在同一事务下，某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

• 当前读

  update\delete\insert\select for update

• 快照读

  select

• 回表

  当执行查询语句的时候，查询的字段在命中的索引中没有全部的字段的时候，则需要进行二次查询聚簇索引拿到所有字段

• 索引覆盖

  当执行查询语句的时候，查询的字段在命中的索引中有全部的字段的时候，则不需要进行二次查询聚簇索引拿到所有字段

• 索引下推

  • MySQL在5.6及以上版本中引入的一个优化器特性，用于在存储引擎层面减少回表次数，进而提高查询性能.
  • 当你的查询中使用了复合索引，但在过滤条件中并不是使用索引的第一个字段，或者不是一个范围查询时，MySQL优化器可以利用索引下推来进一步减少回表次数。简单来说，就是让存储引擎在索引中过滤掉尽可能多的行，而不是仅仅停留在索引叶子节点上.

  CREATE TABLE users (
      id INT NOT NULL,
      name VARCHAR(20),
      age INT,
      INDEX idx_name_age (name, age)
  );
  SELECT * FROM users WHERE name LIKE 'Alice%' AND age > 30;
  

  • 在没有使用索引下推的情况下，MySQL会先扫描索引idx_name_age，找到所有匹配name LIKE 'Alice%'的行后，再回表检查这些行的age是否大于30。
  • 如果启用了索引下推，MySQL会在索引树中直接过滤掉age不大于30的行，减少回表的次数。
  • 在实际的查询中，你可以通过EXPLAIN FORMAT=TREE查看查询计划来确认是否使用了索引下推

• bufferpool 数组

• 热数据区
• • head 指针
• • tail 指针
• 冷数据区
• • head 指针
• • tail 指针 冷热数据通过lru算法进行淘汰

mysql server

• 1、连接器
• 2、缓存
• 3、解析器
• 4、优化器
• 5、执行期

sql 执行的顺序

• 1. FROM子句：确定查询的来源表，可以是实际的表或者是另一个子查询。
• 2. ON：连接条件（如果有），应用于FROM子句中的表。
• 3. JOIN子句：如果有的话，可以是LEFT JOIN、RIGHT JOIN、INNER JOIN或CROSS JOIN，它们分别指定连接类型。
• 4. WHERE子句：过滤条件，根据指定条件过滤记录。
• 5. GROUP BY子句：分组条件，根据指定的列进行分组。
• 6. HAVING子句：分组后的条件过滤。
• 7. SELECT子句：确定查询的列。
• 8. DISTINCT子句：去除重复数据。
• 9. ORDER BY子句：结果集的排序顺序。
• 10. LIMIT子句：限制结果集的数量。

隔离级别

• read uncommited

  A 事务可以读取到 B事务未提交的数据 产生脏读、不可重复读、幻读

• read commited

  A 事务可以读取到 B事务已经提交的数据 产生不可重复读、幻读

• repeatable read

  A 事务多次读取的数据是一致的，不受B事务提不提交的影响 幻读 在快照读和当前读的情况下

• serialize

  事务串性化

索引分类

索引类型

• 普通索引 key/index
• 唯一索引 unique
• 主键索引 parmary key
• 组合索引 key/index 多字段组合
• 全文索引 fulltext

锁分类

锁粒度

• 行锁 开销大、加锁慢、会发生死锁、并发度最高
• 表锁 开销小、加锁快、不会发生死锁、并发度低
• 间隙锁(页锁) 开销一般、加锁一般、会发生死锁、并发度一般

类型

• 读锁（共享锁 S锁）
• 写锁（排他锁 X锁）
• 意向锁（I锁）

  • 是一种表级别的锁，为了解决多粒度锁而设定的。\
  • IS锁 意向共享锁，在一个事务中要加S锁前，需要先获得IS锁 \
  • IX锁 意向排他锁，在一个事务中要加X锁前，需要先获得IX锁
    ⚠️: 意向锁简单来说就是在添加行锁的时候，给表添加一个标识表明该表已经存在共享锁或者排他锁，其他事务加锁时可以像判断这个标识。

性能

• 乐观锁
• 悲观锁
• 意向锁

解决RR隔离级别的幻读

间隙锁（gap lock）

• 在两个值之间的空隙加锁，为了解决RR级别下的幻读问题。
• 间隙（next key logck）：如数据库表中数据 为 1、2、3、8、10 那么间隙就为（3 - 8），（8 - 10），（10 - +∞）

临键锁：

• 行级别的锁机制，是行锁与间隙锁的组合，即介于索引记录到索引区间的一种排他锁，
• 临键锁只与非唯一索引有关，与唯一索引无关。

注意：

• 间隙与临键的区别：间隙锁的间隙是引擎自动划分出来的，而临键是where条件上的范围查询
• 只有在当前读 （select for update）的情况下才会加这些锁

索引的数据结构

• B-Tree索引 范围查询
• Hash索引 不适用于范围查询
• Full-text索引 全文检索

数据行隐藏字段

• db_trx_id 创建或者修改记录的最后一次事务id
• db_roll_ptr 回滚指针，上一个数据库历史版本
• db_row_id 隐藏主键

read view

read view叫做读视图，在执行查询的时候就产生了产生（所以这里才有可见性算法的第三条对比），此视图并不保存实际的数据，而是事物相关的信息，用来做可见性的判断

• trx_list 生成read view的时候活跃的事物列表
• up_limit_id 生成read view的时候活跃事物列表中的最小事物id
• low_limit_id 生成read view的时候系统尚未分配的下一个事物 的id（最大的事物的id）
• db_trx_id 创建或者修改记录的最后一次事务id

mvcc 可见性算法

• 1、如果db_trx_id 小于up_limit_id，说明事物一定是在生成readview的时候已经提交，可见的
• 2、db_trx_id 是否大于low_limit_id，如果大于说明，数据是在生成readview之后产生的事物，一定是不可见的，如果小于进行第三步
• 3、判断db_trx_id是否在活跃的事物列表中，如果在，说明在生成readview的时候事物在未提交，在当前快照读是不可见的，如果不存在，说明在生成readview的时候事物已经提交了，是可见的