MySQL基础

发表于 分类于 阅读次数： 本文字数： 4.6k
总结MySQL底层基础知识

SQL名词

  • DQL（Data Query Language）：数据查询语言，指Select语句，通常会归类到DML中
  • DDL（Data Definition Language）：数据定义语言，通常指Create、Drop、Alter等对表操作的语句
  • DML（Data Manipulation Language）：数据操作语言，通常指Insert、Delete、Update等对数据操作的语句
  • DCL（Data Control Language）：数据控制语言，通常指Grant、Revoke等对数据库权限控制的语句

数据库三范式

  1. 原子性：数据不可再分
  2. 唯一性：所有数据都要和数据表中的键有完全依赖关系
  3. 冗余性：非键属性之间是完全无关的

数据完整性

  1. 实体完整性：每个实体都是独一无二的

    • 主键（primary key）
    • 唯一约束
    • 唯一索引（unique）

  2. 引用完整性（参照完整性）：关系中不允许引用不存在的实体

    • 外键（foreign key）

  3. 域完整性：数据是有效的

    • 数据类型及长度
    • 非空约束（not null）
    • 默认值约束（default）
    • 检查约束（check）
      • 说明：在MySQL数据库中，检查约束并不起作用。

数据一致性

实现原理：事务

数据库相关的命令

普通命令

  • SHOW ENGINES; - 查看数据库支持的数据引擎
  • begin/start transaction - 开启事务
  • commit/rollback - 提交/回滚
  • explain sql语句 - 查看执行计划
  • savepoint 保存点名称 - 在事务中添加保存点
  • rollback [work] to [savepoint] 保存点名称 - 事务中回滚到保存点
  • release savepoint 保存点名称 - 删除保存点

变量命令

注意：变量的查看可以通过show variables like '%变量名%'，也可以通过select @@变量名，非特殊情况不做说明

  • show global variables; - 查看所有全局变量
  • show variables; - 查看局部变量
  • show variables like '%storage_engine%'; - 查看数据库的默认引擎，此处storage_engine不是完整的变量名
  • show variables like '%datadir%'; - 查看当前数据保存位置
  • show variables like "innodb_page_size"; - 查看innodb一个数据页的大小
  • set session autocommit = on/off - 设定事务是否自动开启
  • select @@tx_isolation; - 查看数据库的事务隔离级别（8.0.3之前版本）
  • select @@transaction_isolation; - 查看数据库的事务隔离级别（8.0.3之后版本）
  • set session transaction isolation level 事务隔离名称 - 设置事务隔离级别

MySQL数据引擎

数据结构可视化查看

特性 InnoDB MRG_MYISAM MEMORY MyISAM
存储限制 有 没有 有 有
事务、集群索引、外键 支持
锁机制 行锁 表锁 表锁 表锁
B树索引、索引缓存 支持 支持 支持 支持
哈希索引 支持
全文检索 支持（5.6+） 支持
数据缓存 支持 支持
数据可压缩 支持
内存使用 高 低 中 低
存储空间使用 高 低 低
批量插入性能 低 高 高 高

MySQL从5.5开始默认使用InnoDB，InnoDB也是唯一一个支持外键、事务、行锁的数据引擎。

InnoDB 简介

支持事务安全表（ACID）

  • A（Atomicity）：原子性；事务，要么全成功，要么全失败
  • C（Consistency）：一致性；事务前后数据的完整性必须保持一致。
  • I（Isolation）：隔离性；多个事务之间相互隔离
  • D（Durability）：持久性；持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的

事务特性的实现

  • 隔离性：读写锁 + MVCC

事务隔离级别：

  • 无事务隔离级别：会出现第一类丢失更新、脏读、不可重复读、第二类丢失更新和幻读
  • Read uncommitter（未提交读）：会出现脏读、不可重复读、第二类丢失更新、幻读，解决了第一类丢失更新
  • Read Committer（提交读）：会出现不可重复读、第二类丢失更新、幻读，解决了脏读和第一类丢失更新
  • Repeatable Read（可重复读）：会出现幻读，解决了脏读、不可重复读和第二类丢失更新，MySQL默认的级别，InnoDB引擎下该级别也解决了幻读
  • Serializable（串行化）：最安全，速度最慢，所有问题都解决了

备注：

  • 第一类丢失更新：撤销一个事务的时候，把其他事务已提交的更新覆盖了
  • 脏读：一个事务修改了数据未提交，另一个事务读到了修改后的数据
  • 不可重复读：第一个事务读取了数据后，第二个事务修改了该数据，第一个事务再次读该数据就会读到和第一次不同是数据
  • 第二类丢失更新：一个事务提交更新的时候，将其他事务已提交的事务覆盖了，是不可重复读的特例
  • 幻读：事务一操作了数据库的多条记录，事务二向数据库中插入一条满足事务一操作的数据。事务一中就会发现这条满足条件的数据。像是出现幻觉一样
第一类丢失更新 脏读 第二类丢失更新 不可重复读 幻读
Read uncommitted ✅ ❌ ❌ ❌ ❌
Read Committed ✅ ✅ ❌ ❌ ❌
Repeatable Read ✅ ✅ ✅ ✅ ❌
Serializable ✅ ✅ ✅ ✅ ✅

索引

MySQL中，所有数据类型的列都可以被索引，常用的存储引擎InnoDB和MyISAM能支持每个表创建16个索引。InnoDB和MyISAM使用的索引其底层算法是B-tree（B树），B-tree是一种自平衡的树，类似于平衡二叉排序树，能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的操作都在对数时间内完成。

B-tree：是一颗多路平衡查找树；

  • 每个节点最多有m-1个关键字。
  • 根节点最少可以只有1个关键字。
  • 非根节点至少有m/2个关键字。
  • 每个节点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。
  • 所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同。
  • 每个节点都存有索引和数据，也就是对应的key和value。

B+tree：

  • 根节点至少一个元素。
  • 非根节点元素范围：m/2 <= k <= m-1。
  • B+树有两种类型的节点：内部结点（也称索引结点）和叶子结点。内部节点就是非叶子节点，内部节点不存储数据，只存储索引，数据都存储在叶子节点。
  • 内部结点中的key都按照从小到大的顺序排列，对于内部结点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它。叶子结点中的记录也按照key的大小排列。
  • 每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序链接。
  • 父节点存有右孩子的第一个元素的索引。

参考链接：面试官问你B树和B+树，就把这篇文章丢给他

MVCC

MVCC:Multi Version Concurrency Control（多版本并发控制）

MVCC的定义源码

MVCC只在 Read Committed 和 Repeatable Read两个隔离级别下工作。Read Uncommitted总是读取最新的记录行，Serializable 会对所有读取的记录行都加锁。

MCVV 的实现：隐藏字段、Read View、Undo log

隐藏字段

  1. DB_TRX_ID(6字节)：表示最近一次对本记录行作修改（insert|update）的事务ID。至于delete操作，InnoDB认为是一个update操作，不过会更新一个另外的删除位，将行表示为deleted。并非真正删除。
  2. DB_ROLL_PTR(7字节)：回滚指针，指向当前记录行的undo log信息
  3. DB_ROW_ID(6字节)：随着新行插入而单调递增的行ID。理解：当表没有主键或唯一非空索引时，innodb就会使用这个行ID自动产生聚簇索引。如果表有主键或唯一非空索引，聚簇索引就不会包含这个行ID了。这个DB_ROW_ID跟MVCC关系不大。

官方文档：15.3 InnoDB Multi-Versioning

Read View（读视图）结构

和快照、snapshot是类似的概念。主要是用来做可见性判断的, 里面保存了“对本事务不可见的其他活跃事务”。

Read View的定义源码，其中包括主要的几个变量

  1. low_limit_id：目前出现过的最大的事务ID+1，即下一个将被分配的事务ID。
  2. up_limit_id：活跃事务列表trx_ids中最小的事务ID，如果trx_ids为空，则up_limit_id 为 low_limit_id。
  3. trx_ids：Read View创建时其他未提交的活跃事务ID列表。意思就是创建Read View时，将当前未提交事务ID记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的。Read View中trx_ids的活跃事务，不包括当前事务自己和已提交的事务（正在内存中）
  4. creator_trx_id：当前创建事务的ID，是一个递增的编号。（这个编号并不是DB_ROW_ID）

Undo log

Undo log中存储的是老版本数据，当一个事务需要读取记录行时，如果当前记录行不可见，可以顺着undo log链找到满足其可见性条件的记录行版本。

大多数对数据的变更操作包括 insert/update/delete，在InnoDB里，undo log分为如下两类：

  1. insert undo log : 事务对insert新记录时产生的undo log, 只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。
  2. update undo log : 事务对记录进行delete和update操作时产生的undo log，不仅在事务回滚时需要，快照读也需要，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被purge线程删除。

Purge线程：为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下旧记录的deleted_bit，并不真正将旧记录删除。
为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。purge线程自己也维护了一个read view，如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

参考文档：MySQL中MVCC的正确打开方式（源码佐证）

锁

普通select
1.读未提交，读提交，可重复读 不加锁，快照读
2.串行化，普通select会升级为select … in share mode

加锁select
select … for update
select … in share mode
1.唯一索引，唯一条件 记录锁(Record Locks)
2.其他的查询条件和索引条件 间隙锁(Gap Locks)与临键锁(Next-Key Locks)

update与delete
1.唯一索引，唯一条件 记录锁(Record Locks)
2.其他， 排他临键锁(Next-Key Locks)

insert
排它锁、同时，会在插入区间加插入意向锁(insert intention lock)。