MySQL

摘要，记录一些自己认为重要需要学习的。

数据类型

整数

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。
INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。

浮点数

FLOAT 和 DOUBLE 浮点类型，DECIMAL 为高精度小数类型。

字符串

主要有 CHAR 和 VARCHAR 两种类型，一种是定长的，一种是变长的。
VARCHAR 会保留字符串末尾的空格， char 会删除

时间和日期

DATETIME 和 TIMESTAMP
DATETIME 能够保存1001年到9999年时间，精度为秒，使用8字节存储。 与时区无关
TIMESTAMP 和UNIX时间戳相同，保存从1970年1月1日午夜以来的秒数，使用4个字节，只能表示从1970-2038年。 与时区有关 。

选择优化的数据类型

  • 更小 的数据类型通常更快，因为它占用更少磁盘、内存和CPU缓存，并且处理周期少
  • 简单就好 例如：整形比字符串操作代价更小
  • 避免使用NULL 如果查询列包含NULL，对SQL来说优化更难，因为 NULL使得索引、统计和值比较变得复杂， 尽量使索引包含的字段设置成 NOT NULL

存储引擎

InnoDB

MySQL默认的 事务型引擎 ，支持真正的 在线热备份 。

MyISAM

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。 但在表有读取操作的同时，也可以往表中插入新的记录 ，这被称为并发插入(CONCURRENT INSERT)。

比较

  • 事务：Innodb是事务型，可以使用 Commit 和 Rollback
  • 并发：MyISAM 只支持 表级锁 ， InnoDB支持 行级锁(即锁一条数据) 。
  • 外键：InnoDB支持
  • 备份： InnoDB支持在线热备份

索引数据结构

InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。 InnoDB存储引擎中默认每个页的大小为 16KB ，可通过参数 innodb_page_size 将页的大小设置为4K、8K、16K

B+Tree

B Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层。
B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。

B Tree 和 B + Tree的最大区别

  • B Tree每个节点都带有key和data，而 B + Tree 非叶子节点只有key，所以相同量的数据B + Tree更加矮胖，减少了检索次数，更加高效。

操作

进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data。 插入删除操作记录会破坏平衡树的平衡性 ，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。尽量减少插入或使键有序，也减少删除

索引失效

  • 对索引使用函数
  • OR 条件左边是索引右边不是索引
  • 左模糊查询或者左右模糊查询
  • 未满足最左前匹配
  • 查询条件左边为字符串出现了隐式转换

SQL调优

  • 通过 explain 进行语法分析，分析key是否用了索引，rows扫描的行数等
  • 尽量不要select *，返回必要的列， 尽量用LIMIT限制返回的行
  • 缓存重复查询的数据，可以避免重复查询数据库，还能提高查询效率
  • 减少扫描行数，尽量使用覆盖索引
  • 对大SQL进行分解,因为一个大DML SQL会锁住很多数据，耗尽资源，阻塞很多小而重要的SQL， 分解SQL易于让缓存利用更加高效，在应用层进行连接，可以更容易对数据库进行拆分，从而更好做到高性能和可伸缩

事务

事务隔离级别

  • 读未提交(一个事务能读到另外一个事务未提交的数据)
  • 读已提交(一个事务提交的数据能被其他事务读取)
  • 可重复读(一个事务读取的数据和事务 开启时保持一致 ) InnoDB默认隔离级别
  • 串行化(对记录进行 加锁 ，如果多个事务发生 读写冲突 ， 后访问的事务必须等前一个事务执行完成 才能继续执行)

隔离级别的实现方式

  • 对于[读未提交] 直接读取最新数据即可
  • 对于[串行化] ，通过加读写锁来避免并行访问
  • 对于 [读已提交] 和 [可重复读] ，通过Read View 实现， 区别在于创建Read View的时机不同，Read View类似于数据快照，[读已提交]在[每个语句执行前]生成，而[可重复读]则是在[启动事务时]生成一个 Read View

MVCC(多版本并发控制)

InnoDB引擎在[可重复读]隔离级别下，很大程度上避免了幻读

  • 针对 快照读(普通select语句)，通过 MVCC 方式解决幻读
    · 快照读是 不加锁读，基于MVCC实现的，当隔离级别是可串行化时，会自动退化成当前读
  • 针对当前读(select.. for update) 通过next-key lock(记录锁+间隙锁)方式解决幻读
    · 当前读是指读取最新数据，读取保证其他并发事务不能修改当前记录， 会对读取数据进行加锁

Read View在MVCC中的工作

Read View中的四个字段可以把事务分为 已经提交的事务、 当前事务id 、创建Read View时，当前活跃且未提交事务的id范围以及 还未开始的事务
聚簇索引中包含两个隐藏字段 对该行更改的事务trx_id 和 指向旧版本的指针，旧版本的记录写入到 undo 日志中

一个事务对记录进行访问时，会将 trx_id在Read View中进行比较

  • 如果 trx_id小于最小活跃事务id ,则表示该版本是在创建此Read View之前已经提交了的版本，因此可见
  • 如果 trx_id大于等于还未开始事务的id ,则表示该记录版本是 在创建该Read View后启动事务更改的，所以该版本不可见
  • 如果 trx_id在上述情况之间，则需判断是否在活跃且未提交事务id范围中
    · 如果trx_id在已启动且未提交事务范围中，则表明生成该版本的事务还在活跃，所以该版本不可见
    · 如果trx_id不在已启动且未提交事务范围中，则表明生成该版本的事务已经提交，所以该版本可见
  • 虽然MVCC只是很大程度避免了幻读， 但还是存在幻读 ，比如 事务A更新了一条事务B插入的记录，那么事务A前后两次查询的记录就不一样了

锁

MySQL加锁可以分为 全局锁 、 表锁 、 行锁 三类

表锁

  • 元数据锁
    · 对一张表进行CRUD时，会自动加 MDL 读锁
    · 对一张表进行结构变化时，会自动加 MDL 写锁

MDL锁是为了保证当前用户进行操作时，防止其他线程对这个表结构进行更改。写锁优先级高于读锁

  • 意向锁

  • AUTO-INC锁
    表主键为自增时，在新增数据时，会持有该锁，在新增完成后就会释放。

Innodb提供了一种轻量锁，给自增字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

行级锁种类

  • 记录锁(锁住一条记录)
    · 当事务当前读查询获取到该记录的记录锁， 其他事务对该记录的更新和删除会发生阻塞，如果其他事务进行插入不会阻塞，因为会报错主键重复

  • 间隙锁(锁住一个范围，左开有开区间)
    · 只存在于可重复读级别，目的是为了解决可重复读的幻读
    · 两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥，因为间隙锁目的是为了防止插入幻影记录

  • next-key lock锁(记录锁和间隙锁的组合， 左开右闭区间)

针对当前读加锁

MySQL如何加锁

加锁的对象是 索引，加锁的基本单位是next-key lock，左开右闭区间

在某些情况下，如果使用记录锁或间隙锁就足以防止幻读现象的发生，那么next-key lock就会退化成记录锁或间隙锁。

  • 当我们用 主键索引进行等值查询 ，如果记录存在，则退化成 [记录锁] ， 如果记录不存在，则退化成 [间隙锁]

  • 当我们用主键索引进行范围查询时，会对每一个扫描到的索引加next-lock锁
    · 针对大于等于，因为存在等值，所以对扫描到的记录退化成记录锁
    · 针对小于等于或者小于， 取决于条件值的记录是否存在
    · 如果记录在表中不存在 ， 扫描到终止范围查询的记录时，该记录的索引的next-key锁会退化成间隙锁，其他扫描到的记录加next-key锁
    · 当查询 记录在表中存在 ，如果是 小于条件 ， 扫描到终止范围查询的记录时，该记录的索引的next-key锁会退化成间隙锁 ，其他为next-lock锁

  • 当我们用非主键索引进行等值查询时 ， 因为存在两个索引，一个是主键索引，一个是二级索引，所以在加锁时，同时会对这两个索引加锁，但对主键索引加锁时，只有满足查询条件的记录才会对它们的主键索引加锁
    · 当 查询的记录存在 时，会进行二级索引扫描，直到扫描到第一个不符合条件的记录就停止，然后对扫描到的二级索引添加 next-lock锁 ，而对于第一个不符合条件的索引退化成间隙锁，同时在符合条件的记录的主键索引添加记录锁
    · 当 查询记录不存在 时，扫描到第一个不符合条件的二级索引记录，该二级索引的next-key锁会退化成间隙锁。因为不满足查询条件，所以不会对主键索引加锁

  • 当我们用非主键索引进行范围查询时，索引的next-lock 锁不会退化！！！

SQL执行流程

  1. 连接器
    · 首先经过连接器进行连接，先经过 TCP 三次握手建立连接后， 验证账号密码 ,通过则建立长连接

  2. 查询缓存(8.0将这一部分删掉)
    · 如果有这一步则进行查询看是否执行过这一条语句，缓存以key-value形式存储，key为查询语句

  3. 解析器-解析SQL
    · 进行 词法分析获取关键字 ， 语法分析 判断SQL满足规则，建立SQL语法树。

  4. 执行SQL
    · 预处理器 检查字段是否存在，将*扩展为所有列
    · 优化器 负责将语句的执行方案确定下来，基于内部的选择来决定走哪个索引等
    · 执行器 与存储引擎进行交互

日志

undo log(回滚日志) 实现事务中的原子性，用于事务 回滚 和 MVCC

  • 在 插入 一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录 删掉 就好了；
  • 在 删除 一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录 插入 到表中就好了；
  • 在 更新 一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列 更新为旧值 就好了。

redo log(重做日志) 实现事务中的持久化，用于掉电等故障恢复

Buffer Pool会把存储的数据划分为若干个页，默认 16KB ，叫做 缓存页

Buffer Pool 是为了提高读写效率，但它是基于内存，内存不可靠。

为了防止断电等导致数据丢失，当记录一条数据时， Innodb 会先更新内存(同时标记为 脏页 )，会在合适的时机，由后台线程将缓存在 Buffer Pool 中的数据刷到磁盘上，这就是 WAL 技术

redo log 和 undo log的区别

  • redo log 记录了此次事务 完成后 的数据状态，记录的是更新 之后 的新值
  • undo log 记录此次事务 开始时 的数据状态，记录的是 之前 的旧值
    事务提交之前发生崩溃，通过undo log 进行回滚数据，事务提交后发生崩溃，通过 redo log 进行恢复
    redo log 是追加方式，所以是[顺序写]入磁盘；而写入数据是[随机写]。这就好比一个记事本按顺序一直往下写和写一个字要找对应的页。
    redo log 实现了 事务的持久性，让 MySQL 有了崩溃恢复的能力， 将写操作从[随机写]转变为[顺序写]，提升写入磁盘性能。

产生的 redo log 也 不是直接写入磁盘 ，因为这样产生大量 I/O 效率低下

redo log 有自己的缓存- redo log buffer
写入时机：

  • MySQL 正常关机
  • 当 redo log buffer 写入量超过 1/2 时，将 redo log buffer 写入磁盘
  • Innodb 后台线程每隔一秒，持久化一次
  • 每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘

redo log 实际是由两个 log 文件(一个大小默认为 1GB )组成，两个文件循环写， 当redo log写的 Buffer Pool 脏数据超过redo log大小，即 redo log 写满后 ，就会阻塞 MySQL ，此时 会停下来将 Buffer Pool 中的脏数据刷新到磁盘中，然后标记 redo log 哪些可以删除，接着对旧的 redo log 进行擦除，腾出空间，然后 MySQL 恢复正常

bin log(归档日志) 主要用于数据备份和主从复制

MySQL 执行完一条更新操作后，还会生成一条 binlog，等事务提交后，会将该事务产生的 binlog统一写入 binlog 文件。

binlog 主要记录所有数据库表结构变更和表数据修改的日志， 不会记录查询类的操作

redo log 和 binlog区别

  1. 适用范围不同
    • binlog是 MySQL 的 Server 层实现的，任何存储引擎都可以使用
    • redo log 是 Innodb 存储引擎实现的日志
  2. 文件格式不一样
    • binlog 有三种格式类型：STATEMENT、 ROW、MIXED
      • STATEMENT：记录的是SQL语句 ，相当于逻辑日志；但有 动态函数问题 ，比如用了 uuid 或 now 函数，在主库上执行的结果并不是在从库上执行的结果，这种随时在变的函数会导致数据复制不一致。
      • ROW：记录的是数据变更 ，每行数据变化的结果都会记录，使 binlog 文件过大
      • MIXED: STATEMENT + ROW 模式，会根据不同情况使用
    • redo log是物理日志，记录某个数据页的修改，比如对XX表空间中的YY数据页ZZ偏移量的地方AAA更新
  3. 写入方式
    • binlog 是追加写，写满之后新建
    • redo log 是循环写，日志空间固定
  4. 用途不一样
    • binlog 是数据备份、主从复制
    • redo log 掉电等故障恢复

主从复制模型

  • 同步复制: MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回结果。
  • 异步复制(默认): MySQL 主库提交事务的线程不等待从库复制成功，直接返回。
  • 半同步复制：MySQL 5.7 版本之后新增的方式，事务不用等待所有从库复制成功响应， 只用返回一部分复制成功响应即可。半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

当一个事务提交后，其 产生的 binlog 先写入 binlog cache ，每个线程都有一个。

binlog 刷盘是根据参数 sync_binlog 来控制的

  • sync_binlog=0: 每次提交事务只 write，不fsync，后续由系统决定
  • sync_binlog=1: 每次提交事务都 write 和 fsync
  • sync_binlog=N: N 次 write 后，执行 fsync