Mysql45讲(一)基础篇关键点整理

1. mysql架构

1.1. 逻辑架构

1.2. InnoDB引擎架构

1.3. InnoDB主从细节(右键看大图)

2. 贯穿:一条SQL语句是如何执行的?

  1. Server层:
    • 连接器(连接池):密码校验、权限校验和读取(和连接相关)、客户端断连(wait_timeout设置)、长连接存在OOM可能(mysql_reset_connection重置连接)
    • 查询缓存:KV,mysql8中移除,少用,表更新记录失效
    • 分析器:词法、语法分析、表字段不存在也是该阶段检测出来的、mysql8错误码
    • 优化器:SQL调优、多个索引决定使用哪个
    • 执行器:校对权限按优化器的执行计划执行
  2. 引擎层,多款存储引擎组件:Myisam、InnoDB、Memory、CSV、Archive归档、BlackHole等

3. 日志:一条更新语句是如何执行的?

3.1. redolog和binlog

  1. 更新涉及重要日志:
    • redolog - 物理日志(重做日志-引擎层):
      • InnoDB特有,在引擎层,记录的是物理日志(操作数据页)
      • 配置固定大小,例如4个文件,每个文件1G,则总计4G日志
      • 日志循环记录,擦除[writepos, checkpoint]直接的内容,同时推进checkpoint
    • binlog - 逻辑日志(归档日志 - 服务层)
      • 所有引擎共有,在服务层,记录的是逻辑SQL(SQL语句)
      • 最开始InnoDB是第三方插件,而mysql仅支持Myisam引擎,后来融合
      • 同时仅有binlog,无法保证Crash Safe,需要依赖redolog
      • 日志追加记录
      • 两种格式:statement记录源sql语句,row格式记录记录行内容
  2. WAL(Write-Ahead-Logging)技术,先写redolog日志,同时更新内存,再写磁盘;
  3. Crash Safe能力:基于redolog

3.2. 两阶段提交

执行update T set c=c+1 where ID=2;,会存在两阶段执行过程:prepare+commit,即先写redolog再写binlog

  1. 执行器InnoDB引擎,ID为主键(聚簇索引),引擎查索引树,从磁盘读取得到数据页(若已在内存,直接返回);
  2. 执行器数据页得到行记录,在内存中将c=c+1操作,然后调用InnoDB引擎写接口更新;
  3. InnoDB引擎将行记录更新到内存,同时将更新操作记录到redolog,此时redologprepare状态,返回执行器,告知随时可以提交事务;
  4. 执行器生成binlog写入磁盘
  5. 执行器调用引擎事务提交接口,InnoDB引擎更改redologcommit状态,整体操作完成

3.3. 数据备份

  • 备份:定期全量:N天、M小时,低峰时间段备份
  • 恢复:找故障前一天全量+从故障点后的binlog增量
  • RTODisaster Recovery灾难恢复的目标时间,资源成本的考量

4. 事务隔离

4.1. ACID特性

  • A(Atomicity)原子性:事务是原子的(不可分割),要么全部完成,要么失败,没有局部完全的情况;
  • C(Consistent)一致性:事务前后,数据库的完整性没有被破坏;
  • I(Isolation)隔离性:多事务并发情况下,由于事务交叉可能导致的数据不一致的情况;
  • D(Durability)持久性:事务提交后,数据修改就是永久的,即便系统故障也不会丢失;

4.2. 隔离级别

  • RU:read uncommited,事务可以读到其他事务未提交的内容
  • RC:read commited,事务可以读到其他事务已提交的内容
  • RR:repeatable read,事务仅能读到自己启动事务时刻的数据
  • S:serializable,串行化事务读写操作,加读、写锁

不同隔离级别,对并发性能有影响,从上到下隔离越严格,并发能力越低。

设定事务隔离级别:SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

4.3. MVCC

  • 如果是可重复读隔离级别RR下,事务T启动时候,会创建一个READ-VIEW,但具体创建会有两种差异
    • 默认情况:BEGIN/START STANSACTION,这种情况是在执行第一个DML语句时候创建的!
    • 一致性快照启动:START TRANSACTION WITH CONSISTENT SNAPSHOT, 这种情况是在执行完成上述语句,就会启动一个一致性的视图,而不用等到第一个DML才生成!
  • 多版本视图并发控制,导致了形成事务的不同隔离级别
  • 数据表的一行记录存在多个版本,每个版本都有自己的row trx_id,基于undolog回滚日志,层层回滚
  • InnoDB通过一个数组存放事务启动的瞬间相关的活跃事务ID(未提交事务的状态),组成了已提交-未提交-未开始三个事务集合的区间。对于当前启动的事务,若存在trx_id在未提交区间内,则不可见,反之在已提交区间则可见。
  • 当前读,即读取已提交事务的最新值:
    • 特别注意,更新数据都是基于当前读current read,即读取最新的数据
    • 另外select ... for updateselect ... lock in share mode加锁情况下,也是当前读
    • 不同事务,在更新同一行情况下会存在锁互斥,等到其他事务提交后,更新会基于当前读,读取取到最新的值
  • tips: 避免长事务,长事务需要保存多个版本的回滚日志,消耗磁盘资源,同时还占用锁资源

可重复读(RR)核心就是基于MVCC实现在事务启动时候形成一致性读(Consistent Read),而事务更新数据的时候,只能用当前读;如果当前的记录行锁被其他事务持有的话,就需要进入锁等待! 读提交(RC),查询仅承认在语句启动前就已提交完成的数据(不用等到第一条DML);

4.4. 症状

  • 脏读:读到其他事务还没有提交的内容,读到的数据是脏的,可能没有被提交! – RU级别存在脏读问题
  • 不可重复读:读的的下一次数据,可能与上次一数据一致!– RURC级别都存在不可重复读问题
  • 幻读:两次相同条件,读到不一样的的内容(其他事务插入或更新了相同范围内数据),自己像幻觉一样! – RURCRR都可能发生

5. 索引

5.1. 索引类型/数据结构

  • Hash:
    • 基于数组,利用hash函数,将key转化成数组位置,值存放在数据中该位置
    • 索引冲突:拉链法、再哈希
    • 适应于等值查询,无法查询区间范围
  • 有序数组:适应于静态存储引擎,因为动态插入会移动大量有序数组的元素
  • 二叉树:查询性能很高,O(logN)时间复杂度,但需要保持树的平衡;另外,二叉树索引结构,对机械磁盘读取数据页不友好,因为存在多次磁盘的IO操作,故InnoDB采用B+树结构,降低了磁盘访问频率!

5.2. B+索引树模型

  • 主键索引:表基于索引树存放数据(聚簇索引)+(普通索引),新建一个索引就相当于新建一棵B+
  • 普通索引:若要查找的数据都在普通索引中包含,则无需回表,否需要回表
  • 索引维护:
    • 页分裂:在插入过程中,可能引发插入到数据页,同时导致该数据页后续元素都要移动到一个新的数据页中的情况
    • 页合并:数据库执行,通过重建页排序,将节点下的数据页空洞修复
    • 数据页空洞,可能需要重建索引
  • 索引长度问题:主键索引通常使用自增ID设定,NOT NULL PRIMARY KEY AUTO_INCREMENT,采用整型则只需要4个字节,长整型只需要8个字节,相比其他字段做主键,占用空间较小!

5.3. 覆盖索引

若要查找的数据都在普通索引中包含,则无需回表,否需要回表。

由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。

5.4. 最左前缀规则

最左前缀规则是由B+树这种索引结构决定的,可以利用索引的“最左前缀”,来定位记录;

由于最左前缀规则存在,联合索引中字段的顺序尤为重要了,另外,联合索引还需要结合存储空间考虑;

5.5. 索引下推

满足索引的条件下,针对联合索引(name, age),以下SQL查询:

select * from tuser where name like '张%' and age=10 and ismale=1;

检索名字为的,但不满足age=10的都会被过滤掉,比如(张三, 11),减少了回表次数。

原因是倘若符合张%的有100个,但age=10的仅有10个,则原来需要回表100次,现在通过索引下推只需要回表10次。

6. 锁

  • 锁的范围:库、表、行
  • DML(数据管理,增删改查),DDL(数据定义,表结构修改定义)

6.1. 库级锁

  • 应用:全库逻辑备份,不加全局锁的话,得到的不是一个一致性的视图(先备份余额表,再备份课程表;恢复的话则余额表有钱,课程表有课)!
  • FTWRL(Flush tables with read lock): 在不支持事务的数据库引擎,比如Myisam上用到;
  • 备份命令:mysqldump --single-transaction,基于MVCC支持,数据可以正常更新(备份由于事务启动,看到的是一个一致性视图),在支持事务的数据库引擎上,比如InnoDB上用到!

6.2. 表级锁

  • 表锁:LOCK TABLES ... READ/WRITE
    • 读锁和写锁互斥
    • 在无行锁情况下,并发支持靠的是表锁
  • 表元数据锁:MDL(meta data lock)mysql5.5引入,当对表结构做变更或者是DML时候,都会加MDL锁:
    • 读锁间不互斥,多个线程支持并发DML
    • 读锁、写锁互斥,多个线程同时更改表结构,需要等待到锁资源才可以执行
    • MDL在DML增删改查过程中是自动加DML读锁,同时在DDL修改数据表结构会加DML写锁,因此即使是高频小表,也可能被阻塞很久:读锁-读锁-写锁(block)-读锁(block)...
    • 如何安全DDL: 低峰操作、申请DML锁,发现申请不到,先释放锁,后续再操作

6.3. 行级锁

  • 行锁:行记录锁,两个线程同时更新一行,会导致后更新的DML被阻塞,直至innodb_lock_wait_timeout超时
    • mysql行锁是由各个引擎自己实现的,有了行锁支持,提升了数据库的并发能力
  • 两阶段锁协议:行锁在需要的时候才加上,需要待事务提交结束才会释放,因此需要缩短锁持有时间,将事务中多个行操作,考虑将影响并发的锁往后操作
  • 锁配置:
    • 锁等待,直至超时:innodb_lock_wait_timeout=50
    • 死锁检测,耗CPU:innodb_deadlock_detect=on,1000个线程并发操作行,存在1M的检测量,期间消耗大量CPU
  • 死锁应对:
    • 在应用层排队或者在进入引擎之前排队
    • 将操作单行,分散到操作多行,降低锁冲突
    • 先记录内存锁操作,做数据排队聚合,再落盘
  • 针对行锁其他注意:更新条件若没有加索引,会导致全表锁定
死锁相关参数:
// ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
| innodb_deadlock_detect    | ON  |
| innodb_lock_wait_timeout  | 50  |

7. 小结

快速对mysql的逻辑结构、日志、事务和事务隔离级别、锁类型、索引和索引结构等进行简要概述,对mysql的一些基本知识点进行了回顾和学习!