Mobile wallpaper 1Mobile wallpaper 2Mobile wallpaper 3Mobile wallpaper 4Mobile wallpaper 5Mobile wallpaper 6
4739 字
24 分钟
Mysql
2025-06-28
学习
数据库
/
Mysql
/
基础

关于数据的基本概念#

  • 数据库: 就是数据的集合, 由数据库管理系统管理
  • 数据库管理系统(DBMS): 是一种操纵、管理数据库的软件,例如这里的MySql
  • 数据库系统: 由数据库、软件、数据管理员构成
  • 数据库管理员: 就是管理数据库系统的人员

MySql中的专业术语的解释#

  • 元组：元组（tuple）是关系数据库中的基本概念，关系是一张表，表中的每行（即数据库中的每条记录）就是一个元组，每列就是一个属性。 在二维表里，元组也称为行。
  • 码：码就是能唯一标识实体的属性，对应表中的列。
  • 候选码：若关系中的某一属性或属性组的值能唯一的标识一个元组，而其任何、子集都不能再标识，则称该属性组为候选码。例如：在学生实体中，“学号”是能唯一的区分学生实体的，同时又假设“姓名”、“班级”的属性组合足以区分学生实体，那么{学号}和{姓名，班级}都是候选码。
  • 主码: 主码也叫主键。主码是从候选码中选出来的。 一个实体集中只能有一个主码，但可以有多个候选码。
  • 外码: 外码也叫外键。如果一个关系中的一个属性是另外一个关系中的主码则这个属性为外码。
  • 主属性：候选码中出现过的属性称为主属性。比如关系 工人（工号，身份证号，姓名，性别，部门）. 显然工号和身份证号都能够唯一标示这个关系，所以都是候选码。工号、身份证号这两个属性就是主属性。如果主码是一个属性组，那么属性组中的属性都是主属性。
  • 非主属性：不包含在任何一个候选码中的属性称为非主属性。比如在关系——学生（学号，姓名，年龄，性别，班级）中，主码是“学号”，那么其他的“姓名”、“年龄”、“性别”、“班级”就都可以称为非主属性。什么是 ER 图

ER图#

构成要素#

  1. 实体: 就是业务对象,比如:学生、老师、教室等。
  2. 属性: 就是实体具备的一些属性, 学生的学号、年龄、年级等。
  3. 联系: 通俗易懂,就是实体与实体之间的联系,老师教学生这种。可以存在一对多、多对多。

三范式#

  1. 1NF: 属性不可再分
    1. 也就是表里面的字段不能再往下分了(学生往下分又是什么呢哈哈),这一范式是所有关系型数据库的基本要求,必须满足。
  2. 2NF: 在1NF基础上,消除了非主属性对于码的部分函数依赖
    1. 函数依赖: 简单来说就是属性x(或者属性组)的值确定了,那么属性y的值也一定能确定,此时就说y依赖于x,x -> y。
    2. 部分函数依赖: x中的如果有真子集, 且y依赖于这一部分的真子集,就是部分函数依赖
    3. 传递函数依赖: 见名知意,就是依赖的传递,根据上方描述,就是x->y,y->z,那么x->z。
  3. 3NF: 在2NF基础上,消除了非主属性对于码的传递函数依赖

主键和外键#

  • 主键: 用于标识一个元组(就是表中的行数据集合),不能有重复,不允许为空,一个表只能有一个主键,具象化来说就是身份证.
  • 外键: 用于和其他表产生联系,外键是其他表的主键,可以重复,可以为空,可以有多个外键.

此处补充: 按照阿里规范中要求——不得使用外键和级联,一切外键概念必须在应用层解决.

说明: 以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度

drop、delete、truncate#

  • drop: drop table 表名,直接将表删除
  • delete: delete from 表名 where 条件 删除某一行或多行数据,如果不加where,则将表数据全部删除
  • truncate: truncate table 表名 只删除表中数据,再插入数据时,id(主键id)从1开始自增。

drop会将表数据和表结构都删除,而另外两个不会。另外两个truncate会清空表数据,delete加入条件后只删除满足条件的数据

属于不同数据库的语言#

truncate和drop属于DDL语言(数据定义语言Data Definition Language),操作立即生效,原数据不会放到rollback segment中,不能回滚,操作不处罚trigger. 而delete属于DML(Data Manipulation Language),会放到rollback segment中,事务提交后生效.

至于DDL和DML从名字可以看出: DDL操作表结构,DML操作表数据

执行速度的区别#

drop>truncate>delete

  • delete执行会产生binlog日志,这一操作会有时间消耗,但是可以方便数据回滚回复
  • truncate命令不会产生日志,并且会把表的自增值重置和索引恢复到初始大小等。
  • drop会把表占用的空间全部释放掉。

索引#

索引是对数据库中数据进行快速查询、检索的数据结构。很多形象的比喻就是将它比作一本书的目录.

索引底层的数据结构有很多种类型: B、B+、Hash、红黑树。Mysql中的Innodb、MyISAM引擎都有B+作为索引结构。

优缺点#

优点#

  • 大大加快检索速度,减少IO次数
  • 通过创建唯一性索引, 可以保证数据库表中每一行数据的唯一性

缺点#

  • 创建、维护索引需要耗费许多时间,如果对表有增删改,那么索引也需要动态的修改,降低了SQL执行效率
  • 索引需要物理文件存储、也会耗费一定的空间。

大多数情况下,索引查询比全表扫描要快(查询值很大的情况下,近似于全表扫描),如果数据量不大,索引也不会有很大提升

索引底层数据结构选型#

Hash表#

哈希表是键值对(key-value)集合,通过键可以快速找到值,好的哈希函数会将key均匀的映射到集合中。

了解过哈希的友友都明白它之所以快的原理: 将键(key)进行哈希函数计算(可以不止进行一个哈希函数)得到最后的哈希值,这个哈希值就是其中的index,也就找到了这个键对应的value。

hash1 = hashfunction1(key)
hash2 = hashfunction2(hash1)
        ... // 多个函数计算
index = hash % array_size //取模的意义就在于将index值固定在小于array_size,避免超出范围

通过上面可以知道,即便通过一次或多次函数计算也避免不了某些特殊的key最后的index相同(称为哈希冲突), 在这种情况下,我们的解决思路是链地址法:将哈希冲突数据保存在链表中。

这里对哈希冲突的解决办法,链地址法在JDK1.8之前在使用,后面换用了红黑树,为了提高链表过长时提高搜索效率。

但是!!!#

MySql的Innodb存储引擎不直接支持这么单纯的哈希,它使用了一种称为自适应哈希索引, 它结合了哈希索引和B+Tree,这种B+Tree可以存储多个键值对,有效减少了哈希冲突链的长度,提高索引效率。

既然哈希这么快,还会有其他的数据结构呢,每个数据结构和算法有它自己适用的领域,而对于需要顺序查询和范围查询时,哈希就心有余而力不足了 除此之外,它每次IO只能取一次,多次取值时就慢了。

二叉查找树(BST)#

二叉树都懂吧,一个根生出两个芽(节点Node),那么二叉查找树在此基础上进行进化,具备以下特点:

  • 左子树所有节点值小于根节点值
  • 右子树所有节点值大于根节点值
  • 左右子树也同样为二叉查找树

当满足上面的规则时,这个特殊的二叉树就具有了大小排序特点了(如果你想,也可以反着来,但是别人读代码就很难受了,比如用左手写字),越左越小,反之越大。 当然,我们也能想到一种特殊情况,当一组已经有大小顺序的数按序插入时,二叉树就变成了链表,失去了二叉树的身材。

如果左右子树深度相差不超过1,即二叉树平衡时,时间复杂度为O(log2(N)),这是情况最好的时候;如果二叉树极度不平衡时, 例如上方提到的退化为链表时,时间复杂度就降为了O(N)。

由此可知,二叉树的查询速度依赖于它的平衡性,对于Mysql来说,这种特性是不适合的,谁知道数据是否平衡呢? 因此在二叉树的启发下,诞生了B-Tree、B+Tree等。

自平衡二叉查找树(AVL树)#

这种数据结构解决了上面二叉查找树的缺点—保证任何节点的左右子树高度差不大于1,基于这个特性,这个数据结构也被称为:平衡二叉树 它的查找、插入和删除在平均和最坏情况下的时间复杂度都是 O(logn)。

AVL树采用了节点旋转保证平衡(红黑树也有此类操作,约定不同,旋转条件也就不同了)。

具体学习可以参考二叉平衡树

红黑树#

基本形状和上面二叉树差不多,但是约定的特点不一样:

  • 每个节点非黑即红
  • 根节点总是为黑
  • 每个叶子节点都是黑色空节点
  • 如果节点为红,则它的子节点必须为黑;反过来就不一定
  • 最重要: 从任意节点到它的叶子节点或者空子节点的每条路径,必须包含相同数目的黑色节点

具体学习可以专门搜索学习。

B-Tree、B+Tree#

B-Tree也称为多路平衡查找树,B+是B的变体。这两种的都是平衡树,B就由此而来—balance嘛

这两种数据结构是目前大多数据库和文件系统采用最多的数据结构作为索引结构。

区别:

  1. B所有的节点都可以存储key,也可以存储value;B+只有叶子节点存放key、value,其他节点只能存放key。
  2. B的叶子节点都是独立的;B+的叶子节点有一个指针指向相邻的叶子节点。
  3. B的检索过程类似于对范围内的每个节点的key进行二分查找,存在还没有到叶子节点就结束检索的情况;而B+的检索过程就很稳定,都是根到叶。
  4. B进行范围检索,先找到下限,再中序遍历,直到上限;而B+的检索就是对链表遍历即可。

由此,可以知道B+的IO次数更少,且检索更稳定。

Mysql中的Innodb和MyISAM都采用了B+,只是两者的实现方式不同。

索引总结#

数据结构划分#

  • B-Tree索引: Mysql默认的索引类型,只有叶子节点存储value,其他节点只有指针和key。
  • 哈希索引: 键值对形式,一次定位。
  • 全文索引: 对文本进行分词, 只有Char,Varchar,Text可以创建全文索引。一般不用,效率低,都分词了,那肯定使用es咯

底层存储划分#

  • 聚簇索引: 索引结构和数据堆一起的索引,Innodb的主键索引就属于这个
  • 非聚簇索引: 这个就反着来,索引结构和数据分开的索引,二级索引就属于这个,MyISAM中主键、非主键都是这个

应用维度划分#

  • 主键索引: 加快查询+列值唯一+表中只有一个
  • 普通索引: 加快查询
  • 唯一索引: 加快查询+列值唯一
  • 前缀索引: 对文本的前几个字符创建的索引,建立的数据更小。
  • 全文索引: 同上。
  • 覆盖索引: 一个索引包含所有需要查询的字段值。

…待补充…

Mysql日志—binlog、redo log、undo log#

日志包括:错误日志、查询日志、慢查询日志、事务日志、二进制日志,重要的日志就是标题提到的: 二进制日志(binlog)、事务日志(redo)、回滚日志(undo)

redo log#

它是Innodb独有的,让mysql拥有崩溃恢复的能力,保证了数据的持久性和完整性

Mysql中操作数据都是也”页”为单位,查询某条数据的时候,会从硬盘把一页数据加载出来,这个叫数据页, 数据页放到Buffer pool中

后续的查找先从buffer pool中找,没有找到再从硬盘里取,这样就减少了IO操作.

更新同样的道理,buffer pool有就直接更新,没有再加载。

我们对数据页进行了什么操作会被记录到重做日志缓存里(redo log buffer),然后再写入redo log,这样也是减少IO操作的设计。

理想情况下,一提交就会进行刷盘操作,实际上,刷盘根据策略进行

每条 redo 记录由“表空间号+数据页号+偏移量+修改数据长度+具体修改的数据”组成

刷盘时机 InnoDb刷新redo log重做日志时机有以下几种情况:

  1. 事务提交: 当事务提交的时候,log buffer里的redo log会被刷新到磁盘中
  2. log buffer空间不足: log buffer中缓存的redo log占满了log buffer总容量的一半时,会刷新到磁盘中。
  3. 事务日志缓存区满: InnoDb使用一个事务日志缓存区来暂时存储事务的重做日志条目。缓存区满的时候, 会刷新到磁盘中。
  4. CheckPoint: InnoDb会定期执行检查点操作(我理解为定期检查),会把内存中缓存的修改刷新到磁盘中,并对重做日志一同刷新,确保数据一致性。
  5. 后台刷新线程: InnoDb启动了一个后台线程,负责周期性(1秒)地将修改未提交地数据页刷新到磁盘,并对重做日志一并刷新。
  6. 正常关闭服务器: Mysql关闭时,redo log刷新到磁盘。

我们要注意设置正确的刷盘策略innodb_flush_log_at_trx_commit。 根据 MySQL 配置的刷盘策略的不同，MySQL 宕机之后可能会存在轻微的数据丢失问题。 innodb_flush_log_at_trx_commit 的值有 3 种，也就是共有 3 种刷盘策略:

0：设置为 0 的时候，表示每次事务提交时不进行刷盘操作。这种方式性能最高，但是也最不安全，因为如果 MySQL 挂了或宕机了，可能会丢失最近 1 秒内的事务。

1：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作。这种方式性能最低，但是也最安全，因为只要事务提交成功，redo log 记录就一定在磁盘里，不会有任何数据丢失。

2：设置为 2 的时候，表示每次事务提交时都只把 log buffer 里的 redo log 内容写入 page cache（文件系统缓存）。page cache 是专门用来缓存文件的，这里被缓存的文件就是 redo log 文件。这种方式的性能和安全性都介于前两者中间。

InnoDb后台开启一个线程,每隔1秒,redo log buffer写入文件系统缓存page cache,调用fsync刷新到磁盘

日志文件组#

硬盘上存储的redo log不只有一个, 而是以日志文件组出现,每个redo log大小一样

它采用环形数组形式,从头写到尾,到尾再从头开始写。

这个日志文件组还有两个属性: write pos和checkpoint:

  • write pos: 是当前记录的位置,一边写一边后移。
  • checkpoint: 是当前要擦出的位置,同样是往后走。

bin log#

  • redo log 是物理日志,记录的内容是:对某个数据页有什么修改,是InnoDb存储引擎。
  • bin log 是逻辑日志, 记录的内容是:语句的原始逻辑。

不管什么存储引擎,只要发生表的更新,都会有bin log产生。

他的作用: Mysql的数据备份、主从、主主都离不开它,需要依靠它完成数据同步,保障数据一致性。

bin log 记录所有涉及到更新数据的逻辑操作,按顺序写

记录格式#

bin log它有以下记录格式, 通过binlog_format指定:

  • statement: 记录内容是sql语句原文,但是同步数据的时候如果遇到语句中存在update_time = now()获取系统当前时间,这样就导致了和原库数据不一致的问题
  • row: 记录内容不再简单的sql语句的原文,包含了操作的具体数据,该内容看不到详细信息,需要通过mysqlbinlog工具解析。这样存在一个问题:很显然,会占用很大空间,消耗IO资源,降低性能
  • mixed: 是前面两种的混种,mysql会判断该语句是否会引起数据不一致的问题,根据这个选择前面两种某一种。

写入机制#

…待补充…

两阶段提交#

…待补充…

undo log#

每个事务对数据修改时,都会记录到undo log,当执行事务出错需要回滚的时候,mysql利用undo log将数据恢复到事务开始的状态。

它和bin log一样属于逻辑日志,记录的sql语句,但是是和原sql语句反着来的,比如进行delete操作,记录的是insert操作。 同时,undo log信息也会被记录到redo log中,因为undo log会被删除清理的,在事务提交(insert)后,就被清理了;update/delete操作字在事务提交后不会立即清理,会加入history list,由后台的purge进行清理。

总结#

  • redo log: 保证数据的持久性
  • undo log: 保证事务的原子性
  • bin log: 保证数据一致性
Mysql
https://onlystem.xyz/posts/mysql/
作者
Only Stem
发布于
2025-06-28
许可协议
CC BY-NC-SA 4.0

部分信息可能已经过时

Java基础
Simple Guides for Mizuki
蜀ICP备2025177764号 | 公安备案图标 川公网安备51011402000992号
© 2026 Only Stem. All Rights Reserved. / RSS / Atom / Sitemap
Powered by Astro & Mizuki  Version 7.6.5
封面
Sample Song
Sample Artist
封面
Sample Song
Sample Artist
0:00 / 0:00