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

更新于

2024-06-13

许可协议

评论