MySql

难崩侠 #技术

MySql

事务的acid特性

  1. a atomic(原子性)
    1. 要不成功，要么失败，没有中间的状态（如果不成功就对已经进行的操作进行撤销，俗称回滚）
  2. c consistency(一致性）
    1. 事务必须保证修改后的数据修改后的数据是符合预设的规则(主键)和逻辑的，即必须保证修改后的数据修改后的数据是有效的
    2. 可能的要求
      1. 数据库层面的完整性规则：
        1. 主键、外键、唯一约束、非空约束、检查约束等。
        2. 数据类型和格式的正确性。
      2. 业务逻辑规则：
        1. 由应用程序或业务需求定义的，即使数据库层面没有强制约束，也必须被遵守的逻辑。例如，库存数量不能为负，账户余额不能小于零，订单总价必须等于商品总价等。
    3. 即事务保证修改后的数据要和这些要求一致
  3. I Isolation(隔离性)
    1. 这个特性要求事务和事务之间是互相隔离的，互不干扰的
    2. 一个事务在提交之前，其对数据所做的修改对其他事务是不可见的（或者说，可见性受隔离级别控制）。
  4. d Durability(持久性)
    1. 事务一旦提交，对数据的修改是永久的，即使断电也会读取到修改的数据

隔离级别

  1. 高并发导致的事务的隔离性问题
    1. 脏读
      1. 一个事务读取了另一个事物的提交之前的非终态数据，这个数据是无效的（脏数据）
    2. 丢失修改(也叫做ABA问题)
      1. 两个事务都未提交，同时修改未提交的数据，都提交后导致其中一个事务的修改被覆盖，导致丢失修改
    3. 不可重复读
      1. 一个事务多次读取，但是中间有其他的事务提交了，导致两次数据不一样，造成了一个数据不能重复读取两次，即两次读取但是结果改变
    4. 幻读
      1. 一个事务进行范围查询多个行，但是中间有事务插入或删除了行，同样是导致了两次读取不一样，但是这个是多个行的
    5. 总结
      1. b和c都是多次读取，但是读取到了提交的事务数据导致的
        1. 侧重点
          1. 不可重复读关注的是单行数据变了
          2. 幻读关注的是数据集发生改变
  2. 隔离级别

由并发问题引出的四个隔离级别更像是数据库对于事务的隔离性的一种保证

1. 读未提交
    1. 我保证你是可以读到未提交的数据，有点摆烂的意思，
        1. InnoDB：我不管了，爱谁谁
2. 读已提交
    1. 我保证你读到的都是提交过的数据，因为都是提交过的数据，所以没有脏数据，所以可以避免脏读
    2. 因为会加上行级锁（如果是写锁），其他事务不可修改，解决了丢失修改的问题
3. 可重复读
    1. 我保证你多次查询同一条数据，结果都是一样的，即数据可以重复进行读取
    2. 由于只保证单行的同一条数据不变，对于新插入的数据无能为力，
    3. <font style="color:#DF2A3F;">在标准 SQL 规范中，可重复读级别确实允许幻读（即对新插入的数据无能为力）。但是，MySQL 的 InnoDB 存储引擎对此做了优化，它通过 MVCC 结合间隙锁（Gap Lock）和临键锁（Next-Key Lock），在可重复读级别下也解决了幻读问题。</font>
4. 串行化
    1. 我保证所有事务都是依次执行的，该事务内的数据不会受到其他数据的任何影响
5. 总结
    1. 数据库从 `读已提交`隔离级别才开始解决事务并发导致的问题
    2. 解决问题的对应关系是
        1. `读未提交` -> `脏读`,`修改丢失`
        2. `可重复读`->`不可重复读`
        3. `串行化`->`幻读`

MVCC（多版本并发控制）

MVCC 的工作流程（读已提交和可重复读隔离级别）

MVCC 主要在 读已提交 (Read Committed) 和 可重复读 (Repeatable Read) 这两个隔离级别下发挥作用，并且它们的 Read View 创建时机有所不同：

  1. 读已提交 (Read Committed)：
    • 每次 SELECT 语句执行时，都会重新生成一个 Read View。
    • 这意味着，在一个事务中，如果多次查询同一行数据，而其他事务在这期间提交了对该行的修改，那么每次查询可能会看到不同的数据版本（因为每次都创建了新的 Read View，可能会看到其他事务提交的新版本）。这会导致不可重复读。
  2. 可重复读 (Repeatable Read)：
    • 只有事务第一次 SELECT 语句执行时，才会生成一个 Read View。
    • 该 Read View 在整个事务的生命周期内都保持不变。
    • 这意味着，在一个事务中，无论何时查询同一行数据，都会使用最初的 Read View 进行可见性判断，因此总是能看到事务开始时的数据版本，即使其他事务在这期间提交了修改，当前事务也“看不到”这些修改。这解决了不可重复读问题。
    • 对于幻读（Phantom Read），InnoDB 的可重复读隔离级别通过间隙锁（Gap Lock）和临键锁（Next-Key Lock）结合 MVCC 来解决。MVCC 解决了对已有数据的幻读，而间隙锁解决了插入操作导致的幻读。

锁

  1. 行锁
    1. 锁定一行
      1. 类型有写锁（排他锁 ，X锁）和读锁 （共享锁，S锁）
    2. 具体在Innodb里面有这几种类型
      1. 记录锁
        1. 锁定那条记录本身
        2. 解决不可重复读，因为记录不能修改了
      2. 间隙锁
        1. 锁定多条记录之间的间隙，防止插入
        2. 可以防止幻读，范围记录不能插入，不能防止不可重复读
      3. 临键锁 （InnoDB的可重复读隔离级别下的默认锁级别）
        1. 锁定范围记录本身和范围里面的间隙
        2. 同样解决幻读，可以解决幻读和不可重复度
    3. 特点
      1. 优点
        1. 并发性能高
      2. 缺点
        1. 加锁速度慢，需要额外的存储空间高
  2. 表锁
    1. 对整个表进行加锁
    2. 加锁速度快，开销小，并发度低，并发性能差
  3. 注意
    1. InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。这个在我们日常工作开发中经常会遇到，一定要多多注意！！！
      1. 原因
        1. 因为可重复读隔离级别下，为了解决不可重复读和幻读,需要进行加锁

索引

  • [ ] 回表
  • [ ] b+树
  • [x] 聚簇索引

全表扫描和全索引扫描

  1. 全表扫描
    1. 存储引擎层直接按照主键（聚簇索引）的顺序进行一行一行的查询，查到一行，交由Server层进行判断数据是否合规，可以看到全表的数据
  2. 全索引扫描
    1. 存储引擎层直接按照相关索引（二级索引）的顺序进行一行一行的查询，查到一行，交由Server层进行判断数据是否合规，可以看到全表的数据
  3. 两个方法，Server层都可以看到全表的数据，只是查找数据的路径不太一样

覆盖索引

  1. 要查的数据列直接就是或者小于索引列
  2. 如
select a,b,c from tableName where a=xxx and b=xxx and c=xxx and d=xxx

聚簇索引和二级索引

  1. 聚簇索引
    1. 可简单的理解为主键索引，因为一个叶子节点的前面是主键，然后才是剩下的内容
    2. 存储的是实际的一行表记录
    3. 叶子节点的结构为： 主键+剩余表记录
    4. 特殊点，如果没有主键，InnoDB会去找一个唯一列作为索引，如果还没有，就会自动生成一个rowId
    5. 如何查看隐藏的那个rowId（注意，只有没有显式id或无唯一性索引列，或者主键为简单数值型才有这个一个字段），
select _rowid from tableName;
  1. 二级索引
    1. 非主键索引，
    2. 存储的是主键值（主键列的值）
    3. 叶子节点的结构为： 索引列+主键列的值

回表

  1. 存储引擎先去查二级索引，然后从二级索引拿到主键的值，然后再根据主键的值去查聚簇索引，然后再查到整行记录
  2. 路径： 二级索引(索引列值+主键列值)-> 聚簇索引(主键列值+其他行记录)
  3. 使用索引覆盖可以防止回表，
  4. 索引下推可以减少回表

索引下推

在存储引擎层，将涉及索引列但是又不能准确一次性判断的索引列顺便操作一下,而不是交给server层来操作,

实际操作,将非索引列也交给存储引擎层顺便进行判断

小常识

  1. mysql规定一行数据占用最大为65535字节，包括非实际数据的长度

变长字段的长度如何确定

  1. 如果允许存储的长度超过255字节，那么就用两个，
  2. 如果不超过255字节，那么就用一个。

mysql行记录类型

  1. redundant
  2. compact
  3. dynamic
  4. compressed

行记录数据溢出

  1. 在compact格式下，mysql将溢出的数据放到溢出页中，然后在末尾用一个20字节的指针指向溢出页
  2. 而在 compress和dynamic 格式中，则将可能溢出的行数据，更换为指针，指向实际存放数据的溢出页中