发表更新architecture19 分钟读完 (大约2898个字)0次访问

MySQL锁、索引、日志知识小结

1. myisam和innodb区别

  • myisam是MySQL 5.1以前的默认引擎，支持全文检索、压缩、空间函数等，但是不支持事务和行级锁，所以一般用于有大量查询少量插入的场景来使用，而且myisam不支持外键，并且索引和数据是分开存储的。
  • innodb是基于聚簇索引建立的，和myisam相反它支持事务、外键，并且通过MVCC来支持高并发，索引和数据存储在一起。

2. MySQL的索引

  • B+树和Hash索引

  • B+树是左小右大的顺序存储结构，节点只包含id索引列，而叶子节点包含索引列和数据，这种数据和索引在一起存储的索引方式叫做聚簇索引，一张表只能有一个聚簇索引。假设没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有的话则会隐式定义一个主键作为聚簇索引

    image-20210929154403899

  • 非聚簇索引(二级索引)保存的是主键id值，这一点和myisam保存的是数据地址是不同的

    image-20210929154445643

  • 区别
    image-20210929154500925

3. 覆盖索引和回表

  • 覆盖索引指的是在一次查询中，如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为覆盖索引，而不再需要回表查询
    • explain sql语句看Extra的结果是否是“Using index”
    • 尽量不要用select *
      • explain select * from user where age=1 可能回表
      • explain select id,age from user where age=1 覆盖索引

4. 锁

  • 目的：解决并发引起的幻读、脏读
  • 操作维度
    • 共享锁（读锁）
      • 常用于确认依存的数据是否存在
      • 事务 + lock in share mode，注意避免两个事务使用共享锁+写入，造成死锁
      • 属于行锁，全表扫描时会变成表锁
    • 排他锁（写锁）常用于并发场景的库存操作
      • 事务 + FOR UPDATE
      • 属于行锁，全表扫描时会变成表锁
  • 粒度维度
    • 表锁
      • 锁定整张表并且阻塞其他用户对该表的所有读写操作，比如alter修改表结构的时候会锁表
      • myisam只支持表锁
      • 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突概率高，并发度最低
    • 行锁
      • 开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
  • 逻辑维度
    • 乐观锁（并没有真正上锁）
      • 效率高，容易发生业务失败，适用于多读的应用类型
      • 更新时判断有没有其他人更新过
        • 通过版本号实现
        • 通过时间戳实现
    • 悲观锁
      • 效率低，不容易发生更新失败
      • 上排他锁
        • 开始事务
        • 查询数据与上锁 for update
        • 更新数据
        • 提交事务

5. 事务的ACID

  • 原子性：事务要么全部成功要么失败
  • 一致性：事务前后的数据完整性一致
  • 隔离性：事务的修改在提交前，其他事务是看不到的，不受干涉
    • read uncommit 读未提交（脏读）：读到了其他事务还没有提交的数据
    • read commit 读已提交（不可重复度），解决了脏读，只读取已提交事务的数据，但同一个读取事务中两次读取结果可能不一致
    • repeatable read 可重复复读 （MySQL默认），事务开始读取数据时，不再允许修改操作 ，但是有可能产生幻读
      • 幻读：一个事务在前后两次查询同一范围的时候，后一次查询看到了前一次查询没有看到的行
        • 原因：行锁不能锁新增的行
        • 影响：事务锁无法锁住新增的数据
        • 解决：间隙锁
    • serializable 串行，不常用，使用锁，会导致超时和锁竞争
  • 永久性：事务一旦提交就永久保存在数据库中

6. ACID实现原理

  • 原子性：undo log，记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql
  • 一致性：代码层面+回滚+恢复
  • 隔离性：锁和MVCC0
  • 永久性：redo log，redo log+内存，结合刷盘策略记录一次数据修改，宕机时可从redo log中恢复

7. 三大日志

  • 日志类型

    • 逻辑日志： 可以简单理解为记录的就是sql语句
    • 物理日志： mysql 数据最终是保存在数据页中的，物理日志记录的就是数据页变更

  • binlog

    • binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。 binlog 是 mysql的逻辑日志，并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志

    • binlog 是通过追加的方式进行写入的，可以通过 max_binlog_size 参数设置每个 binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志

    • binlog使用场景

      • 主从复制 ：在 Master 端开启 binlog ，然后将 binlog 发送到各个 Slave 端， Slave 端重放 binlog 从而达到主从数据一致
      • 数据恢复 ：通过使用 mysql binlog工具来恢复数据。

    • binlog刷盘时机

      对于 InnoDB 存储引擎而言，只有在事务提交时才会记录 binlog ，此时记录还在内存中，那么 binlog是什么时候刷到磁盘中的呢？ mysql 通过 sync_binlog 参数控制 biglog 的刷盘时机

      • 0：不去强制要求，由系统自行判断何时写入磁盘；
      • 1：每次 commit 的时候都要将 binlog 写入磁盘；
      • N：每N个事务，才会将 binlog 写入磁盘。

    • binlog日志格式

      • STATMENT： 基于 SQL 语句的复制(statement-based replication, SBR )，每一条会修改数据的sql语句会记录到 binlog 中 。（ MySQL5.7.7之前默认）
        • 优点： 不需要记录每一行的变化，减少了binlog日志量，节约了 IO , 从而提高了性能
        • 缺点： 在某些情况下会导致主从数据不一致，比如执行sysdate() 、 slepp() 等
      • ROW： 基于行的复制( row-based replication, RBR )，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了 。（ MySQL 5.7.7之后默认）
        • 优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题
        • 缺点： 会产生大量的日志，尤其是 alter table 的时候会让日志暴涨
      • MIXED： 基于 STATMENT 和 ROW 两种模式的混合复制(mixed-based replication, MBR )，一般的复制使用 STATEMENT 模式保存 binlog ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog

  • redo log

    • 持久性

      • 在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中
        • 因为 Innodb 是以 页 为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了
        • 一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差
      • redo log
        • 只记录事务对数据页做了哪些修改

    • redo log

      • 内存中的日志缓冲( redo log buffer )
      • 磁盘上的日志文件( redo log file )
      • mysql 每执行一条 DML 语句，先将记录写入 redo log buffer ，后续某个时间点再一次性将多个操作记录写到 redo log file 。这种 先写日志，再写磁盘 的技术就是 MySQL里经常说到的WAL(Write-Ahead Logging) 技术

    • 在计算机操作系统中，用户空间(user space)下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间( kernel space )缓冲区( OS Buffer )。因此， redo log buffer 写入 redo log file 实际上是先写入 OS Buffer ，然后再通过系统调用 fsync() 将其刷到 redo log file

      image-20210929162855388

    • innodb_flush_log_at_trx_commit 支持三种写入时机

      image-20210929162944681

  • undo log

    • undo log 主要记录了数据的逻辑变化，比如一条 INSERT语句，对应一条 DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的undo log ，这样在发生错误时，就能回滚到事务之前的数据状态

8. 幻读与MVCC

  • MVCC 多版本并发控制

    • 每行数实际上隐藏了两列，创建时间版本号，过期(删除)时间版本号，每开始一个新的事务，版本号都会自动递增

      image-20210929163107032

    • MVCC的原理是查找创建版本小于或等于当前事务版本，删除版本为空或者大于当前事务版本

    • select * from user where id<=3 and create_version<=3 and (delete_version>3 or delete_version is null);

    • 在一次事务中，防止了数据的删除和修改导致的脏读，但不能防止新增

      • 小明开启事务current_version=6查询名字为’王五’的记录，发现不存在

      • 小红开启事务current_version=7插入一条数据，结果是这样

        image-20210929163147425

      • 小明执行插入名字’王五’的记录，发现唯一索引冲突，无法插入，这就是幻读

9. 间隙锁

  • 间隙锁是封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围
  • 产生条件
    • Repeatable Read事务隔离级别
    • 使用普通索引锁定
    • 使用多列唯一索引
    • 使用唯一索引锁定多行记录（对于使用唯一索引来搜索并给某一行记录加锁的语句，不会产生间隙锁）
  • 唯一索引
    • 对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁，如：WHERE id = 5 FOR UPDATE;
    • 对于查找某一范围内的查询语句，会产生间隙锁，如：WHERE id BETWEEN 5 AND 7 FOR UPDATE;
  • 普通索引
    • 在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样；
    • 在普通索引跟唯一索引中，数据间隙的分析，数据行是优先根据普通索引排序，再根据唯一索引排序。

10. 主从同步

  • 流程

    image-20210929163407319

  • 全同步复制

    • 强制同步binlog到从库
    • 所有从库执行完成后返回客户端
    • 性能差

  • 半同步复制

    • 从库执行后返回ack给主库
    • 主库收到至少一个ack后完成

MySQL锁、索引、日志知识小结

https://wurang.net/mysql/

作者

Wu Rang

发布于

2021-08-14

更新于

2021-12-06

许可协议

#

评论

×