动起来的数据库有哪些问题?

  • 产生日志数据
  • 事务之间因为锁而互相影响
  • 事务之间查询到的数据不一致

MySQL日志体系

  • MySQL为了满足主从复制、事务等，有复杂的日志体系
  • Server层产生binlog，用来进行数据复制
  • lnnoDB产生undo log、redo log，用来实现事务ACID

  • MySQL的日志体系不是主要不是用来看的，而是运行必要的

    bin log归档日志

  • Binlog是server层产生的逻辑日志

  • 用来进行数据复制和数据传送
  • Binlog完整记录了数据库每次的数据操作，可作为数据闪回手段

  • Binlog记录在专门的文件中

    undo log回滚日志

  • InnoDB自身产生的逻辑日志，用于事务回滚和展示旧版本

  • 对任何数据（(缓存)的更新，都先写undo log
  • undo log位于表空间的undo segment中

  • SQL: UPDATE name = ‘b’ → undo: UPDATE name = ‘a’

    redo log重做日志

  • lnnoDB自身产生的物理日志，记录数据页的变化

  • InnoDB”日志优先于数据” ，记录redo log视为数据已经更新
  • 内存中的数据更新后写redo log，数据被写入硬盘后删除
  • image.png

数据更新流程

  • image.png
  • redo log刷盘
    • innodb_flush_log_at_trx_commit参数控制redo log刷盘
      • 0：异步每秒刷盘
      • 1：每1个事务刷盘
      • N：每N个事务刷盘
    • 建议设置为1，保证数据安全
  • binlog刷盘
    • sync_binlog参数控制 binlog刷盘
      • 自动控制刷盘
      • 1：每1个事务刷盘
      • N：每N个事务刷盘
  • 持久化分析
    • redo log刷盘前系统崩溃
      • 数据丢失
    • redo log刷盘后系统崩溃
      • 重启时会对redo log进行重放、重写内存中数据页、重写binlog
  • 为什么redo log在binlog之前
    • redo log是系统关键节点，想到于”决断点”
    • binlog一旦写入无法撤回，因为可能已经被传送至备库

MySQL锁的种类

  • 全局锁
    • FTWRL (Flush tables with read lock)
    • 此命令使整个库处于只读状态
    • 主要用途是保证备份的一致性
    • 不要随意使用，杀伤性极大，要在备库使用
  • 表级锁
    • 数据锁
      • lock tables XXX read/write
      • 表锁是非常重的锁，在InnoDB中使用很少
    • 元数据锁（metadata lock）
      • 元数据指的是表的结构、字段、数据类型、索引等
      • 事务访问数据时，会自动给表加MDL读锁
      • 事务修改元数据时，会自动给表加MDL写锁
  • 行锁
    • S锁/X锁
      • S锁不是不让读，而是自己要读，不让别人写
      • X锁不只是不让写，而是自己要写，不让别人读写
      • 只有S锁和S锁之间可以兼容，其他均不兼容
      • image.png

事务

  • image.png

MVCC是如何做到千人千面的？

  • 行记录的版本控制，由于redo log的存在可以从最新版本推算之前的版本

    • image.png

      快照读（一致性非锁定读）

  • 不锁定数据的情况下，读取数据的特定历史版本

  • 版本由事务的具体需求确定:

    • 读已提交：根据每次SELECT时，其他事务的提交情况
    • 可持续读：根据事务开始时，其他事务的提交情况

      当前读（一致性锁定读）

  • 读取数据的当前版本，并加锁

  • 若当前版本已经被加锁且不兼容，则阻塞等待
  • X锁: UPDATE、DELETE、SELECT FOR UPDATE

  • S锁: SELECT IN SHARE MODE

  • InnoDB使用undo log实现了行记录历史查询

  • 快照读不需加行锁，属于乐观锁的一种思路
  • 当前读需加行锁，为了并发控制

  • Next-Key锁解决了可重复读下的幻读问题

    隔离问题

  • 脏读：读到了其他事务未提交的数据

  • 不可重复读：同样的查询读到的数据内容不一样
  • 幻读：同样的查询读到了更多的数据

  • image.png

    如何解决幻读问题

  • 间隙锁的功能与行锁相同，只是针对间隙加锁

  • 间隙锁不分读写，也可以认为是读锁，不允许在间隙插入
  • 可重复读加锁时，将同时锁住数据及其左右间隙

  • image.png

    Next-Key Lock的加锁逻辑

  • 加锁时以Next-Key为基本单位

  • 查找过程中扫描过的范围才加锁
  • 唯一索引等值查询，没有间隙锁，只加行锁
  • 索引等值查询最右一个扫描到的不满足条件值不加行锁
  • 索引覆盖且只加S锁时，不锁主键索引

    实战测试

    ``plsql CREATE TABLEt3(idint(11) NOT NULL,cint(11) DEFAULT NULL,dint(11) DEFAULT NULL, PRIMARY KEY (id), KEYc(c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO sakila.t3(id, c, d) VALUES (9, 0, 0); INSERT INTO sakila.t3(id, c, d) VALUES (10, 10, 10); INSERT INTO sakila.t3(id, c, d) VALUES (20, 20, 20); INSERT INTO sakila.t3(id, c, d) VALUES (30, 30, 30); INSERT INTO sakila.t3(id, c, d) VALUES (40, 40, 40); INSERT INTO sakila.t3(id, c, d) VALUES (50, 50, 50);

  2. - ![image.png](https://cdn.nlark.com/yuque/0/2021/png/8407951/1626589253614-da230961-89e3-451e-b427-2cc75204c2da.png#clientId=ubfabf68e-8599-4&from=paste&height=38&id=uff2e903d&margin=%5Bobject%20Object%5D&name=image.png&originHeight=75&originWidth=1272&originalType=binary&ratio=1&size=79105&status=done&style=none&taskId=u023c86f3-008c-466e-8d02-f36c6e933ab&width=636)
  3. <a name="hajPH"></a>
  4. ##### 等值查询间隙锁
  6. - ![image.png](https://cdn.nlark.com/yuque/0/2021/png/8407951/1626589579678-1bd28436-2e3c-475d-89d2-d12f3d4c611f.png#clientId=ubfabf68e-8599-4&from=paste&height=421&id=u3b6283ae&margin=%5Bobject%20Object%5D&name=image.png&originHeight=842&originWidth=1896&originalType=binary&ratio=1&size=577494&status=done&style=none&taskId=u3be70b97-544c-43f4-b7b2-16dec9b4d8d&width=948)
  7. ```plsql
  8. ##查看隔离级别
  9. select @@tx_isolation;
  11. ## 事务1
  12. BEGIN;
  13. SELECT * from t3 WHERE id = 11 FOR UPDATE;
  15. ## 事务2
  16. BEGIN;
  17. ## 插入不了
  18. INSERT INTO t3 VALUE(12,12,12);
  19. ## 可以查询
  20. SELECT * from t3 WHERE id = 20 FOR UPDATE;

非唯一索引等值锁
  • image.png ```plsql

    事务1

    BEGIN; SELECT id from t3 WHERE c= 10 LOCK in SHARE MODE;

事务2

BEGIN;

可以执行

SELECT * from t3 WHERE id = 20 FOR UPDATE;

阻塞

INSERT into t3 VALUE(12,12,12);

  1. <a name="ytyBL"></a>
  2. ##### 主键索引范围锁
  4. - ![image.png](https://cdn.nlark.com/yuque/0/2021/png/8407951/1626589881287-b8d04bf5-0a43-4cd3-885c-151d531b31b3.png#clientId=ubfabf68e-8599-4&from=paste&height=369&id=u52282a2f&margin=%5Bobject%20Object%5D&name=image.png&originHeight=738&originWidth=1305&originalType=binary&ratio=1&size=411175&status=done&style=none&taskId=u4a473c1b-467c-4959-85b2-91a1e7ba5e0&width=652.5)
  5. ```plsql
  6. ## 事务1
  7. BEGIN;
  8. SELECT * FROM t3 WHERE id BETWEEN 20 and 30 for UPDATE;
  10. ## 事务2
  11. BEGIN;
  12. ## 阻塞
  13. SELECT * FROM t3 WHERE id = 20 for UPDATE;
  14. ## 可以执行
  15. INSERT into t3 VALUE(12,12,12);

非唯一索引范围锁
  • image.png ```plsql

    事务1

    BEGIN; SELECT * FROM t3 WHERE c BETWEEN 20 and 30 for UPDATE;

事务2

BEGIN;

阻塞

SELECT * FROM t3 WHERE c = 20 for UPDATE;

阻塞

INSERT into t3 VALUE(12,12,12);

可以执行

INSERT into t3 VALUE(6,6,6);

  1. <a name="bcCdi"></a>
  2. ##### 非索引字段查询
  4. - ![image.png](https://cdn.nlark.com/yuque/0/2021/png/8407951/1626590423660-d21337c5-cdb8-4017-a605-82a2d98691cc.png#clientId=ubfabf68e-8599-4&from=paste&height=488&id=u521ee189&margin=%5Bobject%20Object%5D&name=image.png&originHeight=975&originWidth=1829&originalType=binary&ratio=1&size=483504&status=done&style=none&taskId=ueacd2ee3-31ea-45d7-a01d-6dfb1425a9a&width=914.5)
  5. ```plsql
  6. ## 事务1
  7. BEGIN;
  8. SELECT * FROM t3 WHERE d BETWEEN 20 and 30 for UPDATE;
  10. ## 事务2
  11. BEGIN;
  12. ## 阻塞
  13. SELECT * FROM t3 WHERE d = 20 for UPDATE;
  14. ## 阻塞
  15. INSERT into t3 VALUE(12,12,12);
  16. ## 阻塞
  17. INSERT into t3 VALUE(6,6,6);

MySQL的垃圾回收

MySQL脏页的产生

  • 更新数据时，只更新了内存中的数据页，没有更新磁盘

  • 内存中数据页与磁盘中数据页不一致，称为脏页

    什么是刷脏

  • 将内存中数据页保存至磁盘

  • 同时，删除与此页相关的redo log，推进check point

    为什么要刷脏

  • 内存中的脏页太多，内存不足

  • redo log文件写满，需要推进check point
  • 系统空闲，提前刷脏，预防上述情况
  • MySQL关闭前，保存数据
  • 前两种会产生性能问题，导致MySQL卡住

MDL锁

  • 事务访问数据时，会自动给表加MDL读锁
  • 事务修改元数据时，会自动给表加MDL写锁
  • 遇到锁不兼容时，申请MDL锁的事务形成一个队列