MySQL 高级速成

202 阅读19分钟

image.png

  • 都要落实到操作系统的文件系统

连接数据库

image.png

sql语句执行流程

查询缓存

  • 曾经查过的存在内存中 KV存<sql,res>,8.0没缓存,因为修改会改缓存,要手动让缓存失效,麻烦死,遂删除。
  • 之前执行过的语句会KV的形式缓存在内存中
  • 查询之前先查找之前执行过的相同语句
  • 不推荐使用缓存:数据表修改后,会删除所有相关缓存
  • MySQL 8.0以后缓存功能已经去掉

分析器

  • 分析器的作用是知道你要“干什么”
  • 先做词法分析,识别SQL语句中的关键字
  • 再做句法分析,判断SQL语句是否符合语法

优化器

  • 优化器的作用是要知道“怎么做”
  • 优化器的主要工作是决定如何使用索引

有缺陷,可能该优化的地方没有优化

执行器

  • 执行器的主要工作是校验权限、调用存储引擎
  • 执行器首先校验此用户对目标数据有无权限
  • 执行器会以行为粒度,调用存储引擎,执行SQL
  • 在没有索引的情况下,执行器会循环查询所有行

存储引擎

  • 存储引擎的任务是将执行器的指令落实在数据文件上
  • 不同存储引擎的原理和执行方法有很大不同

InnoDB

  • 支持事务、外键
  • 支持崩溃修复能力和并发控制

什么叫索引组织表(Index Organized Table)

组织是动词

  • 索引组织表不是一种“组织表”
  • 索引组织表是由索引“组织起来的”表
  • InnoDB中,表都是根据主键顺序组织存放的

索引(Index)

  • 索引是数据库中对某一列或多个列的值进行预排序的数据结构
  • 索引可以理解为数据的“目录”
  • InnoDB中,主键是一个特殊索引字段

主键(Primary Key)

  • InnoDB存储引擎表中,每张表都有一个主键:
  • 若表中有一个非空唯一索引 (Unique NOT NULL),即为主键
    • 主键的索引
  • 若有多个非空唯一索引,选择第一个定义的索引
  • 若无,InnoDB自动创建一个6字节的指针,作为主键
    • 因为InnoDB是通过索引组织的表,必须要索引。自动创建的是看不见的。

主流索引查找算法

  • 线性查找Linear Search
  • 二分查找Binary Search
  • 二叉查找树Binary Search Tree
  • 平衡二叉树AVL Tree
    • 不让查找树变成链表,通过左旋和右旋,变平衡。
  • B树B Tree
    • 需要一个节点存多个数据,遂引入B 树
    • B树是线性数据结构和树的结合
    • B树通过多数据节点大大降低了树的高度
    • B树不需要旋转就可以保证树的平衡
  • B+树B+Tree
    • 为解决范围查找的效率和磁盘利用率的问题,遂引入B+ 树
    • B+树是由B树发展而来的一种数据结构
    • B+树的所有数据均在叶子节点,其它都是索引
    • B+树的所有数据形成了一个线性表,找到范围的最小值索引对应的数据,遍历即可。
    • 可以让部分数据加载进内存,以完成范围查找,因为它一个节点有很多数据,

B+树索引

  • InnoDB使用B+树作为索引的数据结构
  • B+树的高度一般为2-4层,查找速度非常快
  • InnoDB索引分为聚簇索引(主索引)和辅助索引

聚簇索引Clustered Index

  • 根据表的主键构造一个B+树
  • 叶子节点直接存放行数据,而不是指针(索引)
  • 索引组织表中,数据也是B+树的一部分 image.png

理论和实现的区别,在同层B+树节点之间,为双向链表,理论上没有

辅助索引 Secondary Index

  • 每张表可以有多个辅助索引
  • 叶子节点并不包含行数据
  • 叶子节点记录了行数据的主键,用来指示数据位置

lnnoDB逻辑存储结构

image.png

表空间(tablespace)

  • 表空间指的是数据表在硬盘上的存储空间
  • 默认,所有表的数据都存在共享表空间
  • 每个表的数据也可以放在独占表空间(ibd文件)

段(segment)

  • 数据段:B+树的叶子节点
  • 索引段:B+树的非叶子节点
  • InnoDB中,段由存储引擎自动管理

image.png

区(extent)

  • 区是由连续页组成的空间,大小为1MB
  • 一次从磁盘申请4~5个区
  • 一般来讲含有64个页(Page)

页(page)

  • 页是lnnoDB中磁盘读写的最小逻辑单位,默认16KB
  • 一个数据页就是一个B+树的节点(B+Tree Node)
  • 页的大小充分考虑了机械硬盘和SSD的最小单元(512B和4KB),即是硬盘最小单位的倍数

通过指针组成树结构,关联每个page

数据行

  • 什么是数据行?
  • 最小单元

什么是数据行?

InnoDB中的变长列

  • 长度不固定的数据类型:
    • VARCHAR、VARBINARY、BLOB、TEXT
  • 占用空间大于768Byte的不变长类型:
    • CHAR
  • 变长编码下的CHAR

行溢出数据

  • 由于InnoDB每个数据页容量有限,导致数据字段也是有限的
  • 当数据字段过大时,一个page装不下时,InnoDB会使用行溢出机制
  • 行溢出机制会把超长字段放入单独开辟的数据页

InnoDB行记录格式Row Format

  • InnoDB行记录格式主要分为两个时代:
    • Redundant / Compact (Antelope文件格式)
    • Dynamic / Compressed (Barracuda文件格式)(主流的)
      • 如果触发行溢出机制,就让未溢出部分和溢出的部分都移到BloB页中,不像以前会拆分,将溢出行的部分换成指向blob页指针,这个行为是动态的(Dynamic),溢出才会变成指针,就减少了一个数据行的容量。
      • 对表的数据行使用zlib算法进行了压缩(Compressed )存储,典型的以时间换空间。(要压缩和解压缩),用的不多。

image.png

联合索引(最左侧用法)

  • 使用两个或以上字段生成的索引
  • 联合索引也可以加速“最左前缀”的查询
  • 联合索引可以代替最左侧字段的单独索引

通过辅助索引找行记录,再找索引。按行的最左侧字段排序,可以where 最左侧字段,快速查询,尽管有重复,可以选多个索引的最左侧字段查询。多个索引漏了中间的就会走全表扫描。

字符串的前缀索引

  • 如果字符串过长,可以考虑使用前缀索引节约空间
    • 换句话说就是,字符串后缀都一样,没有区分度,就截取前面部分当索引
  • 如果前缀区分度太小,可以考虑两种变通方法:
    • 倒序存储,如果前缀没有区分度,就让后缀当索引。
    • 新建Hash字段,如果前后都没有区分度,就浓缩成hash码当索引。

字符串like

  • (like %关键字%) (like %关键字)会使索引失效
  • (like关键字%)左模糊才可以使用索引

如何约束数据

Primary Key / Unique Key

  • 通过将数据字段设置为索引,约束数据内容
  • Primary Key:唯一,不为NULL
  • Unique Key:唯一
  • 唯一约束插入时的性能开销较大
    • 因为要检查唯一性

外键

  • 一般不使用

Default / NOT NULL

  • Default :数据默认值
  • NOT NULL:数据不为空

触发器

  • 一般较少使用

如何使用不存在的数据表(视图)?

视图View

  • 使用视图可以创建不存在的虚拟表
  • 视图的原理是预设一个SELECT语句
  • SELECT语句的查询结果作为虚拟表的数据

视图算法的选择(会影响性能所以要选择)

  • MERGE,将视图SQL合并到主查询SQL中
    • 视图的查询和原表的查询结合(MERGE)起来
  • TEMPTABLE,将视图作临时表(中间结果)来处理
  • 一般来讲,MERGE的性能优于TEMPTABLE

覆盖索引

image.png

  • 覆盖索引指的是:查询语句从执行到返回结果均使用同一个索引
  • 覆盖索引可以有效减少回表
    • 如果查询发字段不是联合索引(一个索引查不到对应字段),就会根据最左索引去查主键,再查相应字段
  • 覆盖索引通过取消回表操作,提升查询效率
  • 若数据的查询不只使用了一个索引,则不是覆盖索引
  • 可以通过优化SQL语句或优化联合索引,来使用覆盖索引
    • 少用select * ,

数据库“动起来”之后,会发生什么?

产生日志数据

  • 数据库在更新时,会产生binlog、redo log、undo log
  • binlog: server层产生的逻辑日志(更新数据时产生)
  • redo log: InnoDB产生的物理日志,保证持久化(D)
  • undo log: InnoDB产生的逻辑日志,保证隔离性(I)、原子性(A)

产生日志页耗性能

客户端之间因为锁而互相影响

  • 客户端执行SQL时,会产生各种行锁、表锁、元数据锁
  • 一个客户端产生的锁,会干扰其他客户端SQL的执行
  • 两个客户端之间可能产生死锁

事务造成查询到的数据与磁盘上不一致

  • 客户端可能暂时看不到已经更新的数据
  • 事务可能产生隐式锁,造成性能问题

MySQL日志体系

  • MySQL为了满足主从复制、事务等,有复杂的日志体系
  • Server层产生binlog,用来进行数据复制
  • InnoDB产生undo log.redo log,用来实现事务ACID
  • MySQL的日志体系不是主要不是用来看的,而是运行必要的

binlog归档日志

  • Binlog是server层(不是引擎)产生的逻辑日志
  • 用来进行数据复制和数据传送,主从复制
  • Binlog完整记录了数据库每次的数据操作,可作为数据闪回手段
  • Binlog记录在专门的文件中

undo log 回滚日志

是回滚时用到的日志,不是记录回滚情况的日志

  • InnoDB自身产生的逻辑曰志,用于事务回滚和展示旧版本
  • 对任何数据(缓存)的更新,都先写undo log
  • undo log位于表空间的undo segment中
  • SQL: UPDATE name = 'b’→undo: UPDATE name = 'a'

redo log重做日志

  • InnoDB自身产生的物理日志记录数据页的变化
  • InnoDB “日志优先于数据”,记录redo log视为数据已经更新
  • 内存中的数据更新后写redo log,数据被写入硬盘后删除
  • binlog储存在4个1GB文件(可配置)中,并且循环写入

循环写入

image.png

  • write pos是当前日志写入点
  • check point是擦除点,数据被更新到硬盘时擦除
  • 当write pos追上check point时,事务无法提交,需要等待check point推进(影响性能)
  • 只要redo log不丢,数据就不会丢失

数据更新流程

image.png

redo log刷盘

更新的时候redo log 是在内存中的,需要刷入硬盘,就是上面的那个环,为了防止断电log数据丢失,需要控制刷入硬盘的频率。

  • innodb_flush_log_at_trx_commit参数控制redo log刷盘
    • 0:异步每秒刷盘
    • 1:每1个事务刷盘
    • N:每N个事务刷盘
  • 建议设置为1,保证数据安全

bin log刷盘

  • sync_binlog参数控制 bin log刷盘
    • 0:自动控制刷盘
    • 1:每1个事务刷盘
    • N:每N个事务刷盘
  • 建议设置为1,保证数据安全

数据安全优先于性能

持久化分析

  • redo log刷盘前系统崩溃:
    • 数据丢失
  • redo log刷盘后系统崩溃:
    • 重启时会对redo log进行重放、重写内存中数据页、重写binlog

为什么redo log在binlog之前

  • redo log是系统关键节点,想到于“决断点”。
    • 只要redo log 成功了就更新成功了。
  • binlog一旦写入无法撤回,因为可能已经被传送至备库
    • 导致主从数据不一致。

MySQL锁的种类

  • 按照粒度分,MySQL锁可以分为全局锁、表级锁、行锁
  • 全局锁会给整个数据库实例加锁,整个库无法修改
  • 表级锁分为表锁(数据锁)和元数据锁
  • 行锁会锁住数据行,分为共享锁和独占锁

全局锁

  • FTWRL ( Flush tables with read lock)
  • 此命令使整个库处于只读状态
  • 主要用途是保证备份的一致性
  • 不要随意使用,杀伤性极大,要在备库使用
    • 不然所有业务都暂停了

表锁(数据锁)

  • 命令: lock tables XXX read/write
  • 表锁是非常重的锁,在lnnoDB中使用很少

元数据锁(matadata lock)

  • 元数据指的是表的结构、字段、数据类型、索引等
  • 事务访问数据时,会自动给表加MDL读锁
  • 事务修改元数据时,会自动给表加MDL写锁

行锁

  • 行锁也有两种类型,有很多种叫法:下面的名称都是一个意思
    • 读锁/写锁
    • 共享锁/排他锁
    • 共享锁/独占锁
    • S锁/X锁
  • S锁不是不让读,而是自己要读,不让别人写
  • X锁不只是不让写,而是自己要写,不让别人读写
  • 只有S锁和S锁之间可以兼容,其他均不兼容

加了X锁,要在事务提交后才解锁,所以长事务会影响性能

事务:InnoDB的杀手锏

事务的特性

  • 原子性(Atomicity)
  • 一致性(Consistency)
  • 隔离性( lsolation)
  • 持久性(Durability)

原子性(Atomicity)

  • 事务中的操作要么全部成功,要么全部失败
  • MySQL的两阶段提交保证了事务的原子性
    • prepare 和 commit两个阶段
  • undo log用来回滚事务的更改

一致性(Consistency)

  • 事务必须使数据库从一个一致性状态变换到另外一个一致性状态
    • 总金额在流转(资金转移)的过程中,不能多也不能少。
  • 锁和两阶段提交保证了一致性

隔离性(Isolation)

  • 事务不能被其他事务的操作数据所干扰
  • 多个并发事务之间要相互隔离
    • 两个事务在执行时互不知晓,只有在事务提交后才有可能相互影响。但是可能性不大,因为更新数据时InnoDB都有自动加锁
  • 锁和undo log实现了MySQL事务的隔离性

持久性(Durability)

  • 一个事务一旦被提交,它对数据库中数据的改变就是永久性的
  • redo log实现了MySQL事务的持久性

image.png

隔离级别

  • 读未提交(READ UNCOMMITTED)
  • 读提交(READ COMMITTED)
  • 可重复读(REPEATABLE READ)
  • 串行化(SERIALIZABLE)

读未提交(READ UNCOMMITTED)

各事务间没有提交的内容各事务间可以互相访问。等于没有隔离

  • 读、写都不加锁,不隔离
  • 每次查询都查询到数据的最新版本
  • 性能最好,但是等于没有事务,很少采用

读提交(READ COMMITTED)

  • 一般读取时,读取此时已经提交的数据
  • 写数据时,加X锁,提交时释放锁
    • 加锁没提交,其它事务不能访问。
  • Oracle数据库的默认隔离级别

可重复读(REPEATABLE READ)

  • 一般读取时,读取本事务开始时的数据状态
    • 其它事务即使提交了,当前事务读的也是之前的数据,当前事务也提交,才能读到其它事务修改并提交的内容。
    • 读历史版本。
  • 写数据时,加X锁,提交时释放
  • MySQL数据库的默认隔离级别

一条简单的sql语句是隐式的事务

串行化(SERIALIZABLE)

  • 读加S锁、写加X锁,提交时释放
  • 对于一条数据,同时只能有一个事务进行写操作
  • 事务隔离性最高,性能太差,很少采用

MVCC(多版本并发控制)

行记录的版本控制

  • 由于undo log的存在,可以从最新版本推算之前版本

快照读(一致性非锁定读)

  • 不锁定数据的情况下,读取数据的特定历史版本
  • 版本由事务的具体需求确定:
    • 读已提交:根据每次SELECT时,看其他事务的提交情况
    • 可持续读:根据事务开始时,其他事务的提交情况

当前读(一致性锁定读)

  • 读取数据的当前版本,并加锁
  • 若当前版本已经被加锁且不兼容,则阻塞等待
  • X锁(更新前也是要先读的,读的是当前版本):UPDATE、DELETE、SELECT FOR UPDATE
  • S锁:SELECT IN SHARE MODE

隔离问题

  • 脏读:读到了其他事务未提交的数据
  • 不可重复读:同样的查询读到的数据内容不一样
    • 读到的内容不同。
  • 幻读:同样的查询读到了更多的数据
    • 读到的数据量不同,或多或少。 image.png

如何解决幻读问题?

  • MySQL在可重复读级别时,通过Next-Key锁解决了幻读问题
  • Next-Key锁是行锁+间隙锁
  • 间隙锁的功能与行锁相同,只是针对间隙加锁
  • 间隙锁不分读写,也可以认为是读锁,不允许在间隙插入
  • 可重复读加锁时,将同时锁住数据及其左右间隙

image.png

不要忘记B+树的叶节点是链表,将链表的间隙锁住,这样数据量就不会变了。

Next-Key Lock的加锁逻辑

  • 加锁时以Next-Key为基本单位
    • 就是分段加锁,锁的是某个范围,未加锁的部分是可以读写的。
  • 查找过程中,扫描过的范围才加锁
  • 唯一索引等值查询没有间隙锁,只加行锁
  • 索引等值查询最右一个扫描到的不满足条件值不加行锁
  • 索引覆盖且只加S锁时,不锁主键索引

image.png

在可重复读的隔离级别下,查询非索引字段查询,并更新数据会锁全表,
当前读时,不要查询没有索引的项目。

MySQL会刷脏,导致STW

MySQL没有垃圾回收。

  • 但是会发现数据库卡几秒,磁盘IO很高
  • 此时MySQL正在“刷脏页

MySQL脏页的产生

  • 更新数据时,只更新了内存中的数据页,没有更新磁盘
    • 毕竟持久化跟redo log有关,只要redo log 存了,就相当于存到硬盘了,
    • 但是毕竟没有正在的存,只是有记录而已,而且redo log 也会满,就是write point 和check point 相遇,导致更新不了数据。
  • 内存中数据页与磁盘中数据页不一致,称为脏页

什么是刷脏

  • 内存中数据页保存至磁盘
  • 同时,删除与此页相关的redo log推进check point

什么时候要刷脏

  • 内存中的脏页太多,内存不足
  • redo log文件写满,需要推进check point
    • 日志优先
  • 系统空闲,提前刷脏,预防上述情况
  • MySQL关闭前,保存数据
  • 前两种会产生性能问题,导致MySQL卡住

如何避免被迫刷脏,影响性能

都用参数设置。其实脏页也有缓存的功能。

  • 正确告知InnoDB,服务器的硬盘性能
    • 配置项:innodb_io_capacity
    • 用来告知服务器的硬盘性能。
    • 以期控制刷脏的频率和速度。
  • 配置合理的脏页比例上限
    • 配置项:innodb_max_dirty_pages_pct
    • 当脏页比例接近此值,会加速刷脏页
    • 建议保持默认值75%
  • 控制“顺便刷脏”策略
    • 配置项:innodb_flush_neighbors
    • 传统(机械硬盘)的磁盘连续写性能最好,尽量刷连续(或相邻)的页
      • 防止磁头摆幅太太,影响性能。
    • SSD建议设为0 (8.0已经默认0)
      • 没有磁头了,无所谓了。

长事务的危害

就是事务的begin到commit耗时太长,不一定是sql太复杂会时间长。

主要危害:锁无法释放

  • 行级锁长时间无法释放,导致其他事务等待
    • 当前读会对数据行加锁,事务提交前无法释放
    • 其他事务更新相同数据时会等待锁,造成更新性能差
  • 容易产生死锁
    • 长事务的锁长时间不释放,容易与其他事务产生死锁
    • 死锁指的是两个事务都依赖对方的锁释放
  • MDL锁(元数据)hold住大量事务,造成MySQL奔溃

解决方法

  • 调整innodb_lock_wait_timeout参数
  • 默认为50,即等待50秒还未获取锁,当前语句报错
    • 进行异常处理,比如rollback
  • 如果等待时间过长,可以适当缩短此参数
  • 主动死锁检测: innodb_deadlock_detect
    • 默认开启的。
    • 会导致死锁的后一个事务,让那个事务报错。
    • 如果事务太多不建议使用此功能,因为死锁检测也耗时,可以用等待超时的功能。
  • 发现死锁时回滚代价较小的事务

MDL锁

  • 事务访问数据时,会自动给表加MDL读锁
  • 事务修改元数据时,会自动给表加MDL写锁
  • 遇到锁不兼容时,申请MDL锁的事务形成一个队列
    • 前一个事务因为读获取了读锁(共享的),后一个事务因为写想获取写锁,获取不到,阻塞。
    • 再后一个事务即使想获取读锁也是获取不到的,各个事务就都要排队了。
    • 解决办法就是将写事务放最后

如何查看影响性能的锁(8.0)

select即可

  • 查看锁:performance_schema库data_locks表
  • 查看锁等待:performance_schema库data_lock_waits表
  • 查看MDL锁: performance_schema库metadata_locks表

什么是“三高”

  • 高并发:同时处理的事务数高
  • 高性能:事务/SQL的执行速度高
  • 高可用:系统可用时间高
    • 有备份,防止宕机后业务不可用。

为什么不直接讲“三高”

  • “三高”只是目的,并不是手段,手段有:
    • 复制
    • 扩展
    • 切换

复制

  • 目的:数据冗余
  • 手段:binlog传送
  • 收获:并发量提升、可用性提升

占硬件资源 image.png binlog记录信息较全,而redo log 是循环读写的,并没有记录全库的信息


  • 根据复制同步的类型,复制可以分为:
    • 异步复制(Asynchronous Replication) image.png
    • 半同步复制(Semisynchronous Replication) image.png
    • 组复制(Group Replication) image.png

扩展

  • 目的:扩展数据库容量
  • 手段:数据分片分库、分表
  • 收获:性能、并发量的提升

因为分库分表较难保持数据一致,所以会影响可用性。

切换

  • 目的:提高可用性
  • 手段:主从身份切换
  • 收获:并发量的提升

丢失切换时期数据

ROW格式的binlog

  • 不记录SQL语句原文
    • 安全,不会泄漏数据
  • 记录数据行的变化
  • 不是物理日志,还是逻辑日志
    • 记的是行变化,不是数据的具体页
  • 占空间较大