数据库基础

1 锁

1.1 InnoDB的锁

• 行级锁(InnoDB存储引擎实现了两种标准的)

  • 共享锁 允许事务读一行数据
  • 排他锁 允许事务删除或更新一行数据

• 意向锁(表锁)

  • 意向共享锁（IS Lock），事务想要获得一张表中某几行的共享锁
  • 意向排他锁（IX Lock），事务想要获得一张表中某几行的排他锁

1.2 锁的类型

• 一致性非锁定读

  • 是指InnoDB存储引擎通过行多版本控制（multi versioning）的方式来读取当前执行时间数据库中行的数据
  • 如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放，MVCC 实现。

• 一致性锁定读

  • 事务的隔离级别为REPEATABLE READ模式下，InnoDB存储引擎的SELECT操作使用一致性非锁定读。但是在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性
  • 两种一致性的锁定读操作
    • SELECT…FOR UPDATE
      • 加一个X锁，其他事务不能对已锁定的行加上任何锁
    • SELECT…LOCK IN SHARE MODE
      • 对读取的行记录加一个S锁，其他事务可以向被锁定的行加S锁，但是如果加X锁，则会被阻塞
    • 必须在一个事务中，务必加上BEGIN，START TRANSACTION或者SET AUTOCOMMIT=0

• 自增长与锁

  • 每个含有自增长值的表都有一个自增长计数器
  • 当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，执行如下的语句来得到计数器的值：
    • SELECT MAX(auto_inc_col)FROM t FOR UPDATE;

• 外键和锁

  • 对于一个外键列，如果没有显式地对这个列加索引，InnoDB存储引擎自动对其加一个索引，因为这样可以避免表锁

1.3 锁的算法

• Record Lock：单个行记录上的锁
• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
• Next-Key Lock∶Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

2 理论基础知识

• 第一范式 属性不可再分
• 第二范式 满足第一范式，非主属性不存在对主属性的部分依赖（如联合主键，某个属性只依赖其中一部分）
• 第三范式 满足第二范式，属性不存在对非主属性的传递依赖。

3 事务的实现

• 原子性、一致性、持久性通过数据库的redo log和undo log来完成
• redo log称为重做日志，用来保证事务的原子性和持久性
  • redo通常是物理日志，记录的是页的物理修改操作
• undo log用来保证事务的一致性
  • undo是逻辑日志，根据每行记录进行记录。

3.1 redo

• 持久化

  • 当事务提交（COMMIT）时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的COMMIT操作完成才算完成

• 重做日志来实现事务的持久性，两部分组成。

  • 重做日志缓冲(redo log buffer)
  • 重做日志文件(redo log file)

• innodb_flush_log_at_trx_commit 控制重做日志刷新到磁盘的策略 默认值为1

  • 0 事务提交时不进行写入重做日志操作，这个操作仅在master thread中完成，而在master thread中每1秒会进行一次重做日志文件的fsync操作
  • 1 事务提交时必须调用一次fsync操作
  • 2 事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行fsync操作
    • mysql 宕机，操作系统不宕机，不会事务丢失
    • 操作系统宕机，事务会丢失。

3.2 undo

• 作用 回滚操作与mvcc

• undo log 格式

  • insert undo log
    • 记录 insert 操作时产生的undo log
      • 因为insert 操作的记录只对当前事务可见，该undo log 在事务提交后可以直接删除，不需要进行purge操作。
    • 11 TRX_UNDO_INSERT_REC
  • update undo log
    • 记录 delete 和 update操作产生的undo log
      • 需要提供MVCC机制，事务提交不能进行删除，提交放入到undo log链表，等待purge线程进行最后的删除。
    • 分类
      • 12 TRX_UNDO_UPD_EXIST_REC 更新non-delete-mark的记录 更新非主键属性
      • 13 TRX_UNDO_UPD_DEL_REC 将delete的记录标记为not delete
      • 当插入一条 与标记为删除记录相同的主键时。
      • 14 TRX_UNDO_DEL_MARK_REC 将记录标记为delete 更新主键/非主键属性

3.3 mvcc

• 表的隐藏列

  • TRX_ID 记录操作该数据 事务的是事务id
  • roll_point 指向上一个版本数据在undo log 位置指针
  • DB_ROW_ID 行标识 ，如果表没有主键，InnoDB 会自动生成一个隐藏主键。
  • 每条记录头信息（record header）有一个专门的bit(deleted_flag) 表示当前记录是否已经删除。

• insert delete update

  • insert 产生一条新纪录。
    • trx_id 是当前插入记录的事务id。
  • delete
    • 标记删除
    • trx_id 是当前删除记录的事务id。
  • update
    • update 主键
      • 添加一条新纪录
      • 新纪录 roll_point 指向 旧纪录
        • 旧纪录 roll_point，TRX_ID不动

3.3.1 ReadView

• 每一个sql语句执行前都会得到一个 readView。

• 关键属性

  • trx_ids 当前系统活跃(未提交)事务版本号集合
  • low_limit_id 创建当前read view 时 当前系统最大事务版本号+1。
  • up_limit_id 创建当前read view 时 系统正处于活跃事务最小版本号
  • creator_trx_id 创建当前read view的事务版本号

• 匹配条件

  • 数据事务ID < ReadView.up_limit_id
    • 显示 开启事务之前存在的数据
  • 数据事务ID >= ReadView.low_limit_id
    • 不显示 开启事务之后新加的数据
  • up_limit_id <= 数据事务ID < low_limit_id 则与活跃事务集合trx_ids里匹配（数据可能是 当前事务开始的时候还没有提交的）
    • 数据事务ID不存在于trx_ids 集合
      • 显示 ReadView 产生的时候已经commit
    • 数据事务ID存在于trx_ids 集合
      • 数据的事务ID等于creator_trx_id
        • 是
          • 显示 当前事务自己生成的
        • 否
          • 不显示 ReadView产生的数据还没提交，又不是自己的。
  • 当前数据事务ID 不满足 ReadView条件，从undo log 获取历史版本。
    • 历史版本再来和 ReadView条件匹配，直到找到一条满足条件的历史数据，或者找不到返回空。

3.4 当前读和快照读

• InnoDB 默认可重复读 ，通过 "行排他锁+MVCC" 一起实现的。

当前读

• select ... lock in share mode
• select ... for update
• insert
• update
• delete

快照读

• 简单的select操作(当然不包括 select ... lock in share mode, select ... for update)

3.4 purge

• 用于最终完成delete和update操作。因为要支持MVcc，若该行记录已不被任何其他事务引用，那么就可以进行真正的delete操作。

4 慢查询

• 如何定位到这些糟糕的查询语句呢

4.1 参数

# 查看是否开启慢查询以及路径
show variables like '%slow_query_log%';


# 开启慢查询 ，MySQL重启失效 配置长期有效，请在my.cnf中进行配置
set global slow_query_log = 1;

# 慢查询的阈值时间单位s
set global long_query_time = 10;

# 查看慢查询的阈值时间 默认大于10记录
show variables like 'long_query_time';

# 查看慢查询阈值 临时设置的
show global variables like 'long_query_time';

• mysqldumpslow 工具分析