Java后端八股笔记6 数据库

用户88347155305
21 阅读14分钟

基础

三大范式

  • 1NF：所有的列不可再分割（三年级二班->三年级+二班）
  • 2NF：在1NF的基础上，消除部分依赖，即所有非主键必须完全依赖于主键（使用学号＋身份证号查询成绩）
  • 3NF：在2NF的基础上，消除传递依赖。即所有非主键必须直接相关于主键（学生->班级->班主任）

连表查询

  • 内连接 (INNER JOIN)：返回两个表中有匹配关系的行
  • 左外连接 (LEFT JOIN)：返回左表所有行，右表没有匹配的行置null，查看没有部门的员工（员工表，部门表）
  • 右外连接 (RIGHT JOIN)
  • 全外连接 (FULL JOIN)：返回所有行。mySQL不支持全外链接，需要左连接 UNION 右连接

1721710415166-eff24e6c-555c-436c-b1b8-7c6dbb5850d7-1768219872700-5.webp

关键字

char与varchar

  • char：固定长字符串，使用时指定固定长度，剩下的空间使用空格补足。
  • varchar：可变长字符串，使用时指定最大长度。指定为字符数：
    • 若为ASCII字符集，则1个字符占1个字节
    • 若为UTF-8字符集，则1个字符占1到4个字节

int

指定的是显示宽，存储空间均为4字节

int(4)：使用zerofill之后，存入5，显示0005；存入12345，显示12345（不截断）

IN与EXISTS

  • IN：检查左侧表达式是否存在于右侧集合。（存在返回TRUE）
  • EXISTS：判断是否能返回一行数据，只关心有没有结果，不关心结果是什么。（有结果返回TRUE）

delete

  • delete是DML语⾔，它是⼀⾏⼀⾏地删除记录，可以带WHERE⼦句只删除部分数据。因为是事务性操作，所以delete的删除是可以回滚的，并且会触发触发器。
  • truncate是DDL语⾔，它会直接删除表⾥的所有数据，保留表结构，不能带WHERE⼦-句。它相当于重建了表，所以速度⾮常快，不能回滚，也不会触发触发器。
  • drop也是DDL语⾔，它是最彻底的，会直接把整张表，包括表结构、数据、索引、约束等全部从数据库⾥删除掉，当然也不能回滚。

存储引擎

InnoDB：默认存储引擎

  • 事务支持：支持ACID事务。Myisam存储引擎是不支持事务的
  • 并发性能：采用行级锁定的机制，可以提供更好的并发性能，Myisam存储引擎只支持表级锁，锁的粒度比较大。
  • 崩溃恢复：通过 redolog 日志实现了崩溃恢复，可以在数据库发生异常情况（如断电）时，通过日志文件进行恢复，保证数据的持久性和一致性。Myisam是不支持崩溃恢复的。

MyISAM：MyISAM是MySQL的另一种常见的存储引擎，具有较低的存储空间和内存消耗，适用于大量读操作的场景。然而，MyISAM不支持事务、行级锁和外键约束，因此在并发写入和数据完整性方面有一定的限制。

Memory：Memory引擎将数据存储在内存中，适用于对性能要求较高的读操作，但是在服务器重启或崩溃时数据会丢失。它不支持事务、行级锁和外键约束。

索引

一种数据结构，存储对应的数据与数据在硬盘中所存放的位置。方便查找与排序

建立索引的列要求区分度要高。

聚簇索引

  • 聚簇索引：即数据和索引在一起，其叶子节点直接存储了完整的行数据，在innoDB中为主键索引
  • 非聚簇索引（二级索引，辅助索引）：叶子节点只存储索引与对应行的主键ID。
    • 当我们使用非聚簇索引查询时，需要先用过索引找到数据行的主键ID，在去表中找到对应的数据，这个过程叫做回表。
    • 覆盖索引：一种优化查询的方法，在二级索引中包含所有我们需要的数据列，不用再回表查询。

B+树

只有叶子节点存数据，其他节点存键值

  • 磁盘友好：数据持久化在磁盘中，磁盘I/O及其费时，而B+树层高极低，千万级数据仅3-4层，即使用磁盘I/O3-4次即可找到数据。
  • 叶子节点使用双向链表链接，方便使用范围查询，可直接在叶子节点上顺序遍历。

为什么主键ID不使用UUID

  • UUID 不是递增的，索引数据结构是 B+Tree，叶子节点数据是有序的，而如果使用 UUID 作为主键，每次插入数据时会出现新的 UUID 需要插入到索引树的中间去，这样可能会频繁地导致页分裂，使性能下降。
  • UUID 太占用内存。每个 UUID 由 36 个字符组成，占用的内存大，由于页的大小是固定的，这样一个页上能存放的关键字数量就会越少，这样最终就会导致索引树的高度越大，在索引搜索的时候，发生的磁盘 IO 次数越多，性能越差。

联合索引

存储结构也是B+树，每个索引节点存放的不只是单个字段。而是多个字段。同时遵循最左匹配原则，最左侧索引整体有序，其他索引局部有序。

最左匹配原则：在使⽤联合索引进⾏查询时，查询条件必须从索引的最左边的列开始，并且不能跳过中间的列，否则索引可能不会被完全使⽤，甚⾄完全失效。

把区分度高的字段放在左边

索引下推

（ICP）⼀个查询优化；下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

在没有索引下推之前，⽐如我们有⼀个(age, name) 的联合索引

  • 执⾏WHERE age = 18AND name LIKE '%三' 这样的查询。MySQL会先在索引树⾥找到所有age=18的记录，然后拿着这些记录的主键ID，⼀条条地回表去查完整数据，最后在Server层根据name LIKE'%三'这个条件进⾏过滤。
  • 有了索引下推之后，过程就不⼀样了。MySQL在索引内部就会判断name LIKE'%三'是否符合的条件，只有符合条件的记录，才会去回表。

优点：它在索引层⾯就过滤掉了⼤量不满⾜条件的数据，从⽽⼤⼤减少了回表的次数，提升了查询性能

索引失效

  • 使用左模糊匹配的时候，也就是 like %xx
  • 在查询条件中对索引列使用函数，表达式
  • 联合索引不正确使用需要遵循最左匹配原则

事务

ACID

  • 原子性：一系列操作，要不全部完成，要不全部不完成
  • 一致性：事务操作前与操作后，数据满足完整性约束，数据库保持一致性。
  • 隔离性：并发事务之间应该保持隔离，不能互相影响
  • 持久性：事务处理后，对数据的修改是永远的。

并发问题

  • 脏读：读到了另一个事务修改但未提交的数据。
  • 不可重复读：同一个事务多次读同一个数据，前后数据不一样
  • 幻读：同一个事务，两次相同的范围查询返回了不同数量的行，主要由于其他事务插入新行导致。

隔离级别

名称描述实现说明
读未提交（read uncommitted）一个事务还没提交，他的变更会被其他事务看到无最低级别
读提交（read committed）一个事务提交之后，他的变更才能被其他事务看到每个语句执行之前生成一个Read View可避免脏读
可重复读（repeatable read）一个事务执行过程中，读到的数据保持一致事务启动之前生成一个Read ViewinnoDB默认级别，可避免不可重复读，但不能防止新行的插入
串行化（serializable）强制事务串⾏执⾏，完全避免了所有并对记录上行级锁最高级别，性能最低。

innoDB解决幻读：

  • 快照读（普通 SELECT，读取事务开始时的数据版本）：通过 MVCC + Read View 避免幻读；
  • 当前读（SELECT … FOR UPDATE，读取当前最新的数据版本）：通过 间隙锁（Gap Lock） + 记录锁（Record Lock） = Next-Key Lock，锁定区间，阻止插入。

MVCC

通过版本链来控制：多版本并发控制

核心思想：在读数据时不加锁，而是通过记录数据在某个时间点的快照，来为不同事务提供不同数据版本。

原理

对于使用 InnoDB 存储引擎的数据库表，它的聚簇索引记录中都包含下面两个隐藏列：

image-20260111222926561-1768219881848-7.png

  • trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
  • roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向上一个旧版本记录，形成版本链。

当⼀个事务要修改某⾏数据时，InnoDB把原始数据拷⻉⼀份到undo log⾥，然后修改原始数据，并记录下当前操作的事务ID。当另⼀个事务来查询这⾏数据时，InnoDB会根据这个事务的ID和版本链中的事务ID进⾏⽐较，找到⼀个对当前事务可⻅的、合适的版本返回。这样就实现了在不加锁的情况下，让不同的事务看到不同版本的数据。

Read View

一个数据快照

readview结构.drawio.png 一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

  • 如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
  • 如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
  • 如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：
    • 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
    • 如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

锁

  • 全局锁：整个数据库就处于只读状态，这时其他线程执行增删改或者表结构修改都会阻塞。全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。
  • 表级锁：通过lock tables 语句可以对表加表锁。
  • 行级锁：InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。
    • 记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的，满足读写互斥，写写互斥
      • S 锁：共享锁，读锁，之间是兼容的，可以多个事务同时持有
      • X 锁：排他锁，写锁，和任何锁都不兼容，⼀旦⼀个事务拿了写锁，其他事务就必须等待
    • 间隙锁：锁的是⼀个“间隙”，也就是两个索引记录之间的⼀段范围，这个范围是开区间，不包括记录本身。
    • Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

日志

image-20260111231500763.png

redo log

保证持久性，记录了数据也做了哪些修改（“哪个数据⻚的哪个位置被修改成了什么值”）。如果MySQL宕机，可根据redo log内容进行恢复。

如何保证持久性：

  • Write-ahead logging（WAL）：在事务提交之前，先将事务所做的修改操作记录到redo log中，然后再将数据写入磁盘。这样即使在数据写入磁盘之前发生了宕机，系统可以通过redo log中的记录来恢复数据。
  • Redo log的循环写入：redo log采用循环写入的方式：

image-20260111230825041.png

redo log file是⼀个环形结构。在这个环形结构上有两个⾮常关键的指针：

  • write pos：表示当前⽇志写到了哪个位置。
  • check point：表示已经刷盘的⽇志的后⼀个位置。
  • write pos到check point之间的部分⽤来记录新⽇志，也就是留给新记录的空间。
  • check point到write pos之间是待刷盘的记录，如果不刷盘会被新记录覆盖。

redo log的写⼊过程

  • 当有数据修改时，⽇志记录会先被写⼊内存中(redo log buffer)。
  • 当事务提交时，将⽇志真正地持久化到磁盘上的redo log file⾥。

刷盘策略：

  • 设置为 1（默认值）：最安全。每次事务提交，都会从内存同步刷到磁盘。
  • 设置为 0：性能最好，但最不安全。事务提交时只留在内存⾥，由后台线程每秒刷盘。
  • 设置为 2：事务提交时写⼊到操作系统的⽂件缓存，由操作系统写入磁盘。

undo log

用于事务回滚，记录数据被修改之前的旧版本

执⾏⼀条把A字段的值从1改成2，那么redo log记录的是“改成2”这个事实，⽽undo log记录的就是“改之前它是1”这个历史信息。

主要作用：

  • 实现事务回滚：利⽤undo log中记录的旧值，将数据恢复到事务开始之前的状态。
  • 实现MVCC（多版本并发控制）：通过undo log，构建出这⾏数据在那个事务开始前的“历史版本”。

bin log

二进制日志，是Server层的日志，无论哪种引擎都会产生。

主要作用：

  • 主从复制：主库（Master）产⽣bin log，从库（Slave）获取并重放这些bin log，从⽽实现主从数据同步。
  • 数据恢复：我们可以利⽤全量备份加上某个时间点之后的bin log，来实现基于时间点的精确数据恢复（Point-in-Time Recovery）。

格式：

  • Statement格式：记录原始的SQL语句。
  • Row格式：记录每⼀⾏数据被修改前后的具体内容。精确，⽇志量会变得很⼤
  • Mixed格式：默认使⽤的格式。MySQL会⾃动判断，对于绝⼤多数安全的、确定性的SQL，它会使⽤Statement格式来记录；⽽对于那些可能导致主从不⼀致的“危险”SQL（UUID），它会⾃动切换成Row格式来记录。

两阶段提交

保证数据⼀致性，⽤来协调InnoDB的redo log和Server层的bin log这两个独⽴⽇志的写⼊。

把事务的提交分成了两个阶段：

  1. 准备阶段（Prepare）：当**InnoDB**写完redo log后，它不直接提交事务，⽽是将redo log的状态设置为“prepare”。
  2. 提交阶段（Commit）：之后，由执⾏器去写bin log。当bin log成功写⼊磁盘后，执⾏器再通知InnoDB，将redo log的状态从“prepare”改为“commit”，完成整个事务的提交。

刷盘机制

主要由 sync_binlog这个参数来控制。

  • 为0时，表示MySQL不控制bin log的刷新，⽽是由系统⾃⾏判断何时写⼊磁盘。
  • 为N时，每N个事务，才会将bin log写⼊磁盘。
  • 为1时，则表示每次commit，都将bin log 写⼊磁盘。

主从同步

实现原理

基于主库的bin log来实现：

  1. 主库记录变更：主库上所有对数据的修改操作，都会被记录到⾃⼰的bin log⽂件中。
  2. 从库拉取⽇志：从库上有⼀个专⻔的I/O线程连接到主库，请求主库的bin log。主库把bin log的内容推送给从库。从库写⼊到⾃⼰的中继⽇志（Relay Log）中。
  3. 从库重放⽇志：从库上还有⼀个SQL线程，它会去读取Relay Log⾥的内容，然后把那些在主库上执⾏过的SQL操作，在⾃⼰本地原封不动地再执⾏⼀遍，这样就保证了数据和主库⼀致。

主从同步延迟

原因：

  • 从库压力大：性能差或复制时间过长。
  • 网路延迟：bin log传输不及时

解决方案：

  • 并行复制：开启参数，让从库的线程并行的执行事务
  • 调整同步模式：从异步改成半同步复制

分库分表

image-20260112171706483.png

  • 垂直分库：按照业务逻辑拆分
  • 水平分库：将不同字段拆分到不同表中
avatar
文章
阅读
粉丝