Skip to main content

MySQL 题库

1.MySQL基础​

数据库设计三大范式​

数据库设计的三大范式是一组设计原则。

  • 第一范式（1NF）：要求原子性
    • 举例：除了主键之外，其他字段数据一样，这种就不满足第一范式
  • 第二范式（2NF）：要求非主键字段与主键之间不允许部分依赖
    • 举例：主要针对联合主键场景，非主键字段仅依赖联合主键其中一个字段，这种就不满足第二范式
  • 第三范式（3NF）：要求非主键字段与主键之间不允许传递依赖
    • 举例：假设存在书籍和发版社两张表。因为书籍和发版社是存在关联的，现在把他们放在一张表里，那么就会存在传递依赖的关系。
不满足范式一，1和3不符合原子性
| BookID | Title                  | Author                | PublicationDate | Publisher           |
|--------|------------------------|-----------------------|-----------------|---------------------|
| 1      | "The Great Gatsby"     | "F. Scott Fitzgerald" | 1925            | "Scribner"          |
| 2      | "Pride and Prejudice"  | "Jane Austen"         | 1813            | "Penguin Classics"  |
| 3      | "The Great Gatsby"     | "F. Scott Fitzgerald" | 1925            | "Scribner"          |
不满足范式二，一般存在于联合主键场景，导致部分依赖
| StudentID | CourseID | CourseName         | Teacher          |
|----------|----------|--------------------|-------------------|
| 1        | 101      | "Mathematics 101"  | "Mr. Smith"       |
| 1        | 102      | "Physics 101"      | "Ms. Johnson"     |
| 2        | 101      | "Mathematics 101"  | "Mr. Smith"       |
| 2        | 103      | "Chemistry 101"    | "Dr. Brown"       |
不满足范式三，存在传递依赖，应该拆表
| BookID | Title                  | Author                | PublicationDate | Publisher           |
|--------|------------------------|-----------------------|-----------------|---------------------|
| 1      | "The Great Gatsby"     | "F. Scott Fitzgerald" | 1925            | "Scribner"          |
| 2      | "Pride and Prejudice"  | "Jane Austen"         | 1813            | "Penguin Classics"  |
| 3      | "The Great Gatsby"     | "F. Scott Fitzgerald" | 1925            | "Scribner"          |

DML、DDL​

DML是数据库操作语言。常指对数据的管理操作，常见有增删改查操作，比如：SELECT、INSERT、UPDATE、DELETE
DDL是数据库定义语言。常指数据库结构的操作。比如：ALERT、TRUNCATE

服务层组成​

  • 连接器：负责建立用户连接，初始化权限，管理连接
  • 查询缓存
  • 分析器：词法解析
  • 优化器：基于索引所化查询
  • 执行器：调用引擎层接口操作数据

一条SQL的执行过程​

查询语句执行过程：​

  • 建立连接：MySQL 连接器负责建立用户连接，包括初始化用户权限等基础信息。
  • 查询缓存：对于已经存在缓存的查询语句，MySQL 会直接返回缓存结果。
  • 语法分析：MySQL 会对 SQL 进行解析并检查是否存在语法错误。
  • 查询优化：一条 SQL 的执行策略是多样的，比如可以走全表扫描，或者走某个索引等。 此处的查询优化就是根据表的大小、索引等信息，再结合 SQL 的查询条件，综合选择最优查询方案。
  • 执行语句：MySQL 的数据操作是基于存储引擎实现的。此处的执行是调用存储引擎接口实现。

更新语句执行过程：​


2.MySQL存储引擎​

介绍下常见的存储引擎​

MySQL 是一个开源关系型数据库管理系统，支持多种不同的存储引擎。

存储引擎是 MySQL 用于管理数据的基础组件，它们负责处理 SQL 语句，包括读取和写入数据等操作。 常见的存储引擎有：

  • InnoDB：InnoDB 是 MySQL 默认的存储引擎，它支持事务、外键和行级锁等特性。
  • MyISAM：MyISAM 是 MySQL 最古老的存储引擎之一，不支持事务和行级锁，这也是它被 InnoDB 取代的很大一部分原因之一。 但它非常适合用于处理大量的读取操作，例如数据仓库和日志文件等。
  • Memory：Memory 存储引擎将内存存储引擎，优点是速度非常快，缺点就是数据是非持久的。

介绍下InnoDB与MyISAM区别​

InnoDB和MyISAM是两个常用的存储引擎，用于MySQL数据库管理系统，它们在功能、性能和适用场景上有一些重要的区别。以下是它们之间的主要区别：

  1. 事务支持：InnoDB支持事务（ACID属性），MyISAM不支持事务

  2. 锁机制：InnoDB使用行级锁（row-level locking），MyISAM使用表级锁（table-level locking）

  3. 外键支持：

    • InnoDB支持外键完整性约束，可以定义外键关系，确保数据的完整性。
    • MyISAM不支持外键，不能定义外键关系。

  4. 崩溃恢复：

    • InnoDB支持崩溃恢复，具有事务日志和事务回滚功能，可以更好地处理数据库崩溃时的数据完整性。
    • MyISAM不具备崩溃恢复功能，容易在崩溃时丢失数据。

  5. 空间和性能：

    • InnoDB通常需要更多的磁盘空间来存储数据，因为它维护额外的事务日志和索引结构。
    • MyISAM在一些情况下可能在性能上表现更好，特别是在只读操作较多的情况下。

3.MySQL事务​

什么是事务​

事务是一组数据库操作的逻辑单元。这些操作要么全部成功，要么全部失败。因此它具有ACID属性：

  1. 原子性（Atomicity）：事务是原子的，意味着它要么完全执行，要么不执行。如果事务的任何部分失败，整个事务将被回滚，数据库将恢复到事务开始之前的状态，以确保数据的一致性。

  2. 一致性（Consistency）：事务的执行将数据库从一个一致状态转移到另一个一致状态。这意味着事务的执行不会破坏数据库的完整性约束或数据关系。

  3. 隔离性（Isolation）：每个事务应该在不受其他事务干扰的情况下执行，即使多个事务同时运行也不会相互干扰。这意味着事务之间应该是相互隔离的，以防止数据冲突。

  4. 持久性（Durability）：一旦事务成功提交，其结果应该是永久性的，并且在系统故障或重启后仍然保持。这要求数据库将事务的变更持久地保存在存储设备上，以确保数据的持久性。

事务隔离级别​

MySQL 是一个关系型数据库管理系统，它支持多个事务之间的并发执行。因此需要保障事务之间的隔离性和数据一致性。

MySQL 提供了四个事务隔离级别，它们分别是：

  • READ UNCOMMITTED（读未提交）：最低级别的隔离级别。事务可以读取另一个事务尚未提交的数据。在此隔离级别下，会存在脏读、不可重复读和幻读等问题。
  • READ COMMITTED（读已提交）：默认的隔离级别。事务只能读取另一个已提交的事务的数据。在此隔离级别下，解决了脏读的问题，但仍然存在不可重复读、幻读的问题。
  • REPEATABLE READ（可重复读）：事务开始时读取的数据集合将保持不变，即使其他事务对这些数据进行了修改或插入操作，也不会影响该事务。这种隔离级别解决了不可重复读的问题，但是仍然存在幻读问题。
  • SERIALIZABLE（序列化）：最高级别的隔离级别，多个事务之间顺序执行。在此隔离级别下，事务之间不会存在冲突，但会牺牲性能。

什么是脏读​

当一个事务可以读取另一个事务尚未提交的数据，而另一个事务随后被回滚，那么当前事务读取到的数据就是无效的。

这类现象被称之为脏读（Dirty read），因为它读到了脏数据。

什么是不可重复读​

不可重复读是指，当一个事务，在某个时间段内，多次读取同一行数据，出现的数据不一致的场景。

不可重复读是数据库隔离级别中的一种问题，常在读未提交、读已提交隔离级别中发生。

不可重复读与幻读的区别在于，不可重复读关注的是同一行数据在一个事务内的两次读取之间是否一致， 而幻读关注的是两次查询的结果集的行数是否一致。

什么是幻读​

幻读是指，当一个事务中，在某个时间段内，多次执行同一个范围查询结果集时，出现的数据不一致的场景。

幻读是数据库隔离级别中的一种问题，常发生于范围查询场景。在可重复读隔离级别下是比较常见的。

如何避免幻读​

  1. 使用更严格的序列化事务隔离级别。但会带来较大的性能损耗。
  2. 使用行锁和间隙锁。通过在SQL语句中使用 FOR UPDATE 实现。
    • FOR UPDATE NOWAIT：如果无法获得锁，立即返回而不是等待
    • FOR UPDATE SKIP LOCKED：如果无法获得锁，跳过而不是等待
  3. 在应用层实现乐观锁，每次执行语句时去检查一遍。

MVCC​

多版本并发控制（MVCC）是一种数据库事务并发控制的机制，其实现原理主要涉及版本号的管理， 以及对数据的读取和写入时的一致性处理（基于ReadView决定事务可以看到那些数据版本）。

以下是MySQL的MVCC实现原理的主要步骤：

  1. 版本号分配： 每个事务都被分配一个唯一的事务标识（Transaction ID）。对于每个被修改的数据行，都会为其分配一个版本号，通常是事务的ID。这个版本号会标记数据行的状态，指示数据行的修改历史。

  2. Read View： 在启动事务时，会生成一个Read View，包含当前事务的事务ID。Read View决定了事务能够看到哪些数据版本。Read View的生成可能包括一些额外的信息，例如事务启动时的系统版本号。

  3. Read操作： 当一个事务执行读操作时，系统会使用Read View来判断当前事务能够看到哪些数据版本。只有那些版本号早于或等于Read View的数据版本才能被当前事务读取。这确保了事务读取的数据是一致的，不受其他并发事务修改的影响。

  4. Write操作： 当一个事务执行写操作时，会在数据行上创建一个新的版本。新版本的版本号是事务的ID。原始数据行的版本号被更新为新版本的版本号。这样，新版本的数据行对于新的读操作是可见的，而对于旧的Read View是不可见的。

  5. 回滚和垃圾回收： 当一个事务被回滚时，相关的修改可以被丢弃，不会影响其他事务。定期执行垃圾回收操作来删除不再需要的旧版本数据，以释放存储空间。


MySQL索引​

什么是索引​

索引是一种用于提高数据库查询效率的数据结构。类似于书籍的目录一样，索引可以让你不用扫描全表，而是通过索引字段，快速定位到特定的数据行。

索引的分类​

我们可以按照不同的维度对索引进行一个大致的分类：

  • 数据结构：B+树、哈希索引
  • 数据的存放位置：聚簇索引、非聚簇索引。当然，聚簇索引也可以叫主键索引，非聚簇索引我们可以叫二级索引
  • 索引字段：主键索引、普通索引、唯一索引、联合索引

索引的优缺点​

优点

  • 提高数据检索效率
  • 提高排序效率

缺点

  • 占用存储空间
  • 降低插入、更新、删除的速度

那些场景试合使用索引​

以下场景试合使用索引：

  • 查询频率高
  • 唯一性好
  • 更新频率低

相反，如果查询频率低，唯一性不好、更新频率高，那么就不适用使用索引。

索引的类型​

MySQL支持多种类型的索引，以提高查询性能和加速数据检索。以下是一些常见的MySQL索引类型：

  1. B-树索引（B-Tree Index）： B-树索引是最常见的索引类型，在大多数情况下都可以满足需求。它适用于精确匹配、范围查询、排序操作等。
  2. 哈希索引（Hash Index）： 哈希索引将索引键的值经过哈希函数计算后存储在索引中，用于加速等值查询。它对于等值查询非常高效，但不支持范围查询或排序操作。
  3. 复合索引（Composite Index）： 复合索引是包含多个列的索引。它可以用于加速多列查询、减少索引数量，但要遵循最左前缀原则。
  4. 前缀索引（Prefix Index）： 前缀索引是对列值的前缀进行索引，而不是整个值。它可以用于节省索引存储空间和加速检索。前缀索引适用于较长的字符串列。
  5. 外键索引（Foreign Key Index）： 外键索引用于连接不同表格之间的关系。它通常与外键约束一起使用，以确保数据完整性。
  6. 唯一索引（Unique Index）： 唯一索引要求索引列的值在表中是唯一的，用于强制数据的唯一性。它适用于任何数据类型。
    • 唯一索引（Unique Index）是数据库中一种用于确保某一列或多列的值在表中是唯一的索引。
  7. 主键索引（Primary Key Index）： 主键索引是唯一索引的一种，通常用于标识表中的每一行。它确保每行都具有唯一的主键值。
  8. 全文索引（Full-Text Index）： 全文索引用于对文本数据进行全文搜索。它允许模糊匹配、词干搜索和布尔搜索。全文索引适用于TEXT和VARCHAR列。

什么是主键​

在数据库中，主键（Primary Key）是一种用于唯一标识数据库表中每一行数据的字段或组合字段。主键具有以下特性：

  • 唯一性： 主键的值在表中必须是唯一的，即每一行数据都可以通过主键唯一地标识。
  • 非空性： 主键的值不能为 NULL，确保每一行都有一个非空的唯一标识。
  • 不变性： 主键的值在表中不应该发生变化，或者说一旦被指定，就应该保持不变。

使用主键有助于确保数据的完整性和一致性，并提高数据库的查询性能。

什么是外键​

在数据库中，外键（Foreign Key）是一种字段或一组字段，用于建立表与表之间的关联关系。外键定义了两个表之间的引用， 确保一个表中的数据在另一个表中存在相匹配的关联数据。外键通常与主键结合使用，建立了表与表之间的引用关系。

外键的主要特性包括：

  • 引用关系： 外键用于引用另一张表的主键，形成表与表之间的关系。
  • 数据一致性： 外键确保在进行数据插入、更新和删除操作时，关联的表之间的数据保持一致性。
  • 约束： 外键可以定义为约束，指定参照表、参照字段以及删除或更新时的操作规则。

使用外键有助于维护数据完整性，确保表之间的关系得到维护。

二叉树、B-Tree和B+Tree区别​

二叉树是每个节点最多有两个子节点的树结构。

其中，二叉查找树，是在树结构的基础上。定义了左子树节点小于父树节点小于右子树节点。

B-Tree和B+Tree都可以看作一种特殊的多叉查找树。其中B-Tree也叫平衡树、而B+Tree则是B-Tree的一种变种。

B-Tree和B+Tree在节点存储方式和连接方式上有一定的区别：

存储方式：

  • B-Tree：非叶子节点和叶子节点既存储键值，也存储实际的数据
  • B+Tree：非叶子节点存储键值，叶子节点上存储实际数据

连接方式：

  • B-Tree：各节点通过指针进行连接
  • B+Tree：各节点通过指针进行连接，此外，叶子节点是一个有序链表。因此，B+Tree在范围查询和排序方面有更强的优势

B-Tree更适合随机访问的场景，B+Tree更适合范围查询、顺序遍历、排序等场景。

聚簇索引和非聚簇索引​

聚簇索引，其叶子节点存储的是真实数据行。非聚簇索引，其叶子节点存储的是指向真实数据行的地址。

它们是更大的概念，在MySQL中，主键索引通常是聚簇索引，普通的索引通常是非聚簇索引。

为什么推荐使用自增主键作为索引​

使用自增主键作为索引是一种常见的索引所化。它使得存储更加有序，这可以减少索引碎片，提高空间利用率，从而提高查询效率。 此外，有序的数据也会提高插入效率。

为什么 MySQL 的自增主键不连续​

正常情况下自增主键是严格递增的。但是在一些场景下可能表现出不连续：

  • 事务回滚
  • 数据删除

以上场景下，自增主键计数器并不会减少

为什么联合索引需要遵循最左前缀原则​

在B+树索引中，索引有一个特性，那就是有序性。对于联合索引而言，只有第一个索引可以确保其有序性，其余的索引并不是有序的。 因此，联合索引后面的索引，是不可以直接用来快速定位查询的，只有遵循最左前缀原则才能确保查询的正确性。

联合索引最左前缀原则特点​

在范围查询（>、<）时会停止匹配，即范围查询的字段可以用到联合索引，但范围查询后的字段就可以使用联合索引。

对于方位查询 =>、=<、between、like 来说，并不会停止配置，而实在边界上会继续匹配。

如何理解块索引​

通常来说，块索引（Block Index）不是一个独立的索引类型，而是一个较低级别的概念， 用于描述数据库引擎内部索引结构的存储方式。

但在PostgreSQL中，块索引是一个高级特性。 它通常用于特定类型的应用场景，特别是那些需要处理大量块级别数据的场景，例如时间序列数据库或列式数据库。 它支持以下特性：

  1. 范围查询（Range Queries）：块索引可用于支持范围查询，例如查找某个范围内的数据块。这对于处理时间序列数据等应用非常有用。

  2. 块级别的检索：块索引直接在数据块级别存储索引信息，从而可以加速块级别的检索，而不是单个行级别的检索。

  3. 块合并和块分割：块索引对于块合并（Block Merging）和块分割（Block Splitting）等操作非常高效，这些操作通常用于数据块的管理。

索引覆盖、索引下推​

  • 索引覆盖：就是查询的值已经存在于索引中了，那么就可以直接返回，不需要再回表查询一次。
  • 索引下推：就是过滤的字段存在于索引中，那么就可以通过索引字段直接过滤，从而减少回表次数。

主键和外键的区别​

主键可以作为每一行数据的唯一性标识。 外键则是用于维护数据完整性和建立表格之间关系的一种强约束。

最左前缀原则​

无法命中索引的情况​

  1. 查询条件没有使用索引字段
  2. 使用了函数，破坏了索引的有序性
  3. 发生了隐式类型转化、隐式编码转化，类似于套上了一个转化函数
  4. 查询优化器判定不使用索引
    • 对非索引字段使用了 ORDER BY 导致优化器判定走全表扫描
    • 数据表高频增删改，数据库统计信息更新不及时，导致优化器判定异常

对于这种索引失效的场景，可以多使用 EXPLAIN 分析下。

读写分离和分库分表​

char和varchar区别​

  • CHAR 是一种固定长度的字符数据类型
  • VARCHAR 是一种可变长度的字符数据类型

int(0)和int(10)区别​

int是一种整数数据类型，用于存储整数值。而int(0)和int(10)中的括号，只是指定显示宽度，并不影响存储值或者计算。


日志​

什么是 WAL​

WAL 是 Write-Ahead Logging 的简写。是一种预写日志技术。它主要有两个作用：

  • crash safe（崩溃恢复）：它记录在数据库引擎层面发生的事务修改，当数据库发生故障时，可以通过重新 redo log 来将数据库恢复到一致的状态。
  • 将随机写转化为顺序写：

redo log​

Redo Log 是一种崩溃恢复的机制，它记录在数据库引擎层面发生的事务修改，当数据库发生故障时，可以通过重新 redo log 来将数据库恢复到一致的状态。

redo log 记录数据页的物理修改
Transaction ID: 123
LSN: 456
Page ID: employees_page_102
Modification Type: UPDATE
Modified Data: 
  - Employee ID: 102
  - New Salary: 20000

此外，redo log 将随机写转化为顺序写，具体过程如下：

  1. 用户或应用程序发起一个事务。
  2. 在事务执行过程中，相关的数据修改操作首先被写入数据库表中，修改了内存中的数据。
  3. 同时，相应的 Redo Log 记录会被生成并写入 Redo Log 文件。这些记录包含了已经对数据表进行的物理修改。
  4. 当事务顺利完成，用户或应用程序执行提交操作。

redo log数据结构​

redo log刷盘机制​

binlog​

binlog 全称 Binary Log（二进制日志），是 MySQL 用于记录数据库更改的一种日志文件。 它包含了对数据库进行修改的所有信息，例如插入、更新、删除等操作。常用于主从复制和数据恢复。

  1. 记录格式：

    • Statement：记录原生的SQL语句
    • Row：记录具体修改的数据
    • Mixed：基于Statement和Row，对于可能造成数据不一致的场景选择Row，否则选择Statement

  2. 作用：

    • 主从复制： Binlog是MySQL主从复制的核心。主数据库记录所有的变更，而从数据库通过读取binlog实现与主数据库的同步。
    • 数据恢复： Binlog可以用于恢复数据库到之前的状态，从而避免数据丢失。
    • 数据库备份： Binlog可以与数据库快照结合，用于创建一致性的备份。

binlog日志格式​

binlog 主要有三种：

  1. Statement-Based Replication (SBR):

    • 工作原理： 这种格式记录的是SQL语句，即在主服务器上执行的SQL语句。当在主服务器上执行一个SQL语句时，这个语句会被记录到binlog中，而从服务器会读取这个binlog，并在从服务器上执行相同的SQL语句。
    • 优点： 相对较小的日志文件大小，因为只需记录SQL语句。
    • 缺点： 由于是基于语句的，可能会出现在主从服务器上执行相同SQL语句时导致的不一致性问题。例如，由于数据不同步或随机因素导致的不同结果。

  2. Row-Based Replication (RBR):

    • 工作原理： 这种格式记录的是对表中行的实际更改。当在主服务器上进行数据更改时，binlog会记录哪些行受到了影响，以及发生了什么变化。而从服务器会根据这些变化来修改对应的数据行。
    • 优点： 更加精确，可以确保主从服务器的数据一致性。
    • 缺点： 相对较大的日志文件大小，因为需要记录更详细的信息。

  3. Mixed Format:

    • 工作原理： 这是SBR和RBR的混合格式。MySQL会根据执行的具体操作选择使用SBR还是RBR。
    • 优点： 兼顾了SBR和RBR的优点，既可以减小日志文件大小，又可以保持数据的一致性。
    • 缺点： 需要更多的判断和逻辑来确定使用哪种格式，可能会增加一些复杂性。
statement 和 row 对比
-- SQL语句
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 2;

-- 对应的binlog记录（SBR）
### UPDATE `employees` SET `salary` = `salary` * 1.1 WHERE `department_id` = 2;

-- 对应的binlog记录（RBR）
### UPDATE `employees` SET `salary` = 22000 WHERE `employee_id` = 101;
### UPDATE `employees` SET `salary` = 19800 WHERE `employee_id` = 102;
### UPDATE `employees` SET `salary` = 24200 WHERE `employee_id` = 103;

redolog和binlog的区别是什么​

在生效机制上：redolog 是 Innodb 独有的日志，而 binlog 是 server 层的，所有的存储引擎都有使用到；

存储结构上：binlog 大小达到上限或者 flush log 会生成一个新的文件，而 redolog 有固定大小只能循环利用；

数据内容上：redolog 记录了具体的数值，对某个页做了什么修改，binlog 记录的操作内容；

在功能机制上：binlog 日志没有 crash-safe 的能力，只能用于归档，而 redo log 有 crash-safe 能力；

undo log​

Undo Log（回滚日志）是数据库引擎实现事务及多版本并发控制的核心模块，它主要用于记录事务执行过程中对数据的修改，其主要作用包括：

  1. 事务回滚： Undo Log 记录了事务执行过程中数据修改（包括插入、更新、删除等操作）的前后状态。当事务需要回滚时，系统可以根据 Undo Log 的信息将数据恢复到事务开始之前的状态。

  2. 多版本并发控制（MVCC）： MySQL使用 Undo Log 来实现多版本并发控制。当一个事务开始时，它可能会读取某些数据，而其他事务可能在此之后修改了这些数据。Undo Log 记录了事务开始时数据的版本，从而支持读取一致性的查询。

  3. 事务的隔离级别： Undo Log 也用于支持事务的隔离级别，例如可重复读隔离级别。通过保存事务开始时的数据版本，可以确保在整个事务执行期间看到一致的数据视图。

  4. 崩溃恢复： Undo Log 在数据库崩溃后，用于回滚未提交的事务，并确保数据库在崩溃后可以恢复到一致的状态。

relay log​

Relay Log 也叫中转日志。在主从复制过程中，从库接收的binlog会被写入中转日志，然后从库会读取并执行中转日志。

MySQL 主从复制还有哪些模型？​

  • 同步复制：主库提交事务后，需要等待所有从库复制成功才能返回客户结果。
  • 异步复制：主库提交事务后，不会等待binlog同步到各从库，直接返回客户结果。一旦主库宕机，从库将丢失数据。
  • 半同步复制：主库提交事务后，会等待部分从库复制成功后，即可返回客户端结果，确保部分从库拥有最新的数据。

什么时候 binlog cache 会写到 binlog 文件？​

MySQL提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上的频率：

  • sync_binlog = 0 的时候，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；
  • sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；
  • sync_binlog =N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

在MySQL中系统默认的设置是 sync_binlog = 0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦主机发生异常重启，还没持久化到磁盘的数据就会丢失。

而当 sync_binlog 设置为 1 的时候，是最安全但是性能损耗最大的设置。因为当设置为 1 的时候，即使主机发生异常重启，最多丢失一个事务的 binlog，而已经持久化到磁盘的数据就不会有影响，不过就是对写入性能影响太大。

如果能容少量事务的 binlog 日志丢失的风险，为了提高写入的性能，一般会 sync_binlog 设置为 100~1000 中的某个数值。


缓存​

在MySQL中，有两个关键的缓存机制，它们分别是Buffer Pool（缓冲池）和Change Buffer（变更缓冲区）。

buffer pool​

Buffer Pool（缓冲池）：

  • 作用： 缓存数据页中的数据，提高读性能。
  • 工作原理： 当查询需要读取表或索引的数据时，MySQL首先检查缓冲池中是否存在相应的数据页。如果存在，就直接使用内存中的数据。如果不存在，就从磁盘读取，并在缓冲池中缓存起来，以便之后的访问。
  • 优势： 缓冲池提高了数据库的读取性能，减少了对磁盘的访问，特别是对于经常被查询的数据。

InnoDB Buffer Pool 里 LRU 的实现有什么优化？​

LRU原理：

  • 数据在缓存中，则移动到头部
  • 数据不在缓存中，移动到头部，并剔除尾部

MySQL中做了部分优化：

  • 数据不在缓存中，移动到中间，而不是头部，避免误伤真正的热数据
  • 数据在缓存中，则有一套访问频率的机制

什么时候刷脏页​

  • 空闲的时候
  • redo log写满了的时候
  • buffer pool内存不足，需要淘汰脏页的时候

change buffer​

Change Buffer（变更缓冲区）：

  • 作用：记录对数据页的修改，减少随机读的性能消耗。
  • 工作原理：Change Buffer 主要用于在内存中没有找到数据页的情况下，将对表的修改缓冲下来。但如果数据页已经在缓冲池中，更新可能会直接应用于内存中的数据页，而不需要通过 Change Buffer。
  • 优势： 变更缓冲区的使用可以减少对表的直接修改，从而降低了写入时的磁盘I/O操作。这对于高写入负载的场景下特别有益。

join​

ON 在结果集之前生效，WHERE 在结果集之后生效。可以通过 WHERE 去查询那些值是 NULL

right join如何执行​

RIGHT JOIN 是一种连接操作，它从右边的表中选择所有的行，同时关联左边的表中匹配的行。如果左表中没有匹配的行，那么结果集中左边的列将包含 NULL 值。

下面是 RIGHT JOIN 的执行过程：

  1. 从右表中选择所有的行： 查询开始时，首先从右表中选择所有的行。

  2. 与左表进行匹配： 对于右表中的每一行，数据库引擎尝试在左表中找到匹配的行。匹配通常是基于连接条件（ON 子句中的条件）进行的。

  3. 生成结果集： 对于每一行，如果在左表中找到匹配的行，则将左右表的对应列组合成一行，并将这行添加到结果集中。如果没有找到匹配的行，那么在结果集中的左边列将包含 NULL 值。

与 LEFT JOIN 类似，如果在 RIGHT JOIN 中存在过滤条件，过滤条件会在连接操作之后应用于结果集。

下面是一个带有过滤条件的 RIGHT JOIN 示例：

假设有两个表 A 和 B：

  • 表 A：
+----+-------+
| ID | Name  |
+----+-------+
| 1  | John  |
| 2  | Jane  |
| 3  | Bob   |
+----+-------+
  • 表 B：
+----+--------+
| ID | Score  |
+----+--------+
| 1  | 90     |
| 3  | 75     |
| 4  | 88     |
+----+--------+

使用 RIGHT JOIN 带有过滤条件：

SELECT A.ID, A.Name, B.Score
FROM A
RIGHT JOIN B ON A.ID = B.ID
WHERE A.ID IS NOT NULL;

结果集：

+----+------+-------+
| ID | Name | Score |
+----+------+-------+
| 1  | John | 90    |
| 3  | Bob  | 75    |
+----+------+-------+

在这个例子中，过滤条件是 A.ID IS NOT NULL，结果集中只包含满足该条件的行。因为是 RIGHT JOIN，表 B 中的所有行都会包含在结果集中，而表 A 中没有匹配的行则会导致 Name 列包含 NULL 值。

关联算法​

1. 嵌套循环连接（Nested Loop Join）：​

嵌套循环连接是一种基本的JOIN算法，它使用嵌套循环遍历第一个表的每一行，并查找与之匹配的第二个表的行。这是一种简单但可能效率较低的算法，尤其是当其中一个表很大时。

SELECT *
FROM table1
JOIN table2 ON table1.column = table2.column;

2. 排序合并连接（Merge Join）：​

排序合并连接算法首先对两个表按照连接列进行排序，然后通过类似归并排序的方式合并两个有序的表。这种算法对于连接列有索引的情况效果较好。

SELECT *
FROM table1
JOIN table2 ON table1.column = table2.column
ORDER BY table1.column, table2.column;

3. 哈希连接（Hash Join）：​

哈希连接算法使用哈希表来存储一个表的连接列，并将另一个表的连接列与之进行匹配。这种算法适用于没有排序索引但内存足够容纳哈希表的情况。

SELECT *
FROM table1
JOIN table2 ON table1.column = table2.column;

4. 索引连接（Index Join）：​

索引连接是一种特殊情况，其中一个表的连接列上有索引，而另一个表没有索引。在这种情况下，查询优化器可能选择使用索引连接，将带有索引的表的行映射到另一个表的行。

SELECT *
FROM table1
JOIN table2 USE INDEX (index_name) ON table1.column = table2.column;

5. 自适应连接算法（Adaptive Join）：​

MySQL 8.0引入了自适应连接算法，它允许优化器在查询执行过程中动态选择JOIN算法，根据实际情况选择最优的连接策略。这有助于在不同情况下获得更好的性能。

SELECT *
FROM table1
JOIN table2 ON table1.column = table2.column;

优化场景​

mysql的explain中，有哪些关键字段​

在 MySQL 中，EXPLAIN语句用于查看查询的执行计划，其中包含了一些关键字段，提供了关于查询执行过程的重要信息。以下是一些常见的 EXPLAIN 输出中的关键字段：

  1. id:
    • 表示查询中的每个 SELECT 子句的标识符。如果查询是复合查询，可能会有多个 id 值。
  2. select_type:
    • 表示查询的类型，包括 SIMPLE（简单查询）、PRIMARY（主查询，通常由 UNION 中的第一个查询组成）、SUBQUERY（子查询）、DERIVED（派生表的结果）、UNION（UNION 中的第二个及后续查询）、UNION RESULT（UNION 的结果集）等。
  3. table:
    • 表示查询的表。如果是子查询或者派生表，可能会显示为 <subquery> 或 <derived>。
  4. type:
    • 表示连接类型，是衡量查询效率的关键指标之一。常见的取值有：
      • ALL: 全表扫描，没有使用索引。
      • index: 全索引扫描，通常是覆盖索引。
      • range: 使用索引范围扫描。
      • ref: 表示使用非唯一索引进行查找。
      • eq_ref: 表示连接使用唯一索引。
      • const, system: 表示表最多有一个匹配行，用于常量表或者 system 表。
  5. possible_keys:
    • 表示查询中可能使用的索引列表。这是一个参考信息，不一定实际使用。
  6. key:
    • 表示实际使用的索引。如果该字段为 NULL，则说明没有使用索引。
  7. key_len:
    • 表示索引中使用的字节数。较小的值通常表示更好的性能。
  8. ref:
    • 表示连接使用的索引列。如果是常量，表示对常量的引用。
  9. rows:
    • 表示估计的查询返回的行数。注意，这只是一个估计值，实际返回行数可能有偏差。
  10. filtered:
    • 表示查询中返回的行的过滤程度，即查询条件的选择性。值越接近 100%，表示越高效。
  11. Extra:
    • 包含有关查询执行的其他信息，如是否使用了临时表、文件排序、使用了覆盖索引等。

索引优化​

为什么不使用长事务​

在用户层面，连接池容易爆。
在数据库方面，尝长事务会长时间占据资源，容易导致死锁，回滚日志版本过长，影响其他事务执行效率。
在主从同步方面，长事务会导致主从同步延迟增高。


运维场景​

选错索引​

深页查询很慢​

OFFSET表示扫描跳过指定数量的行，因此OFFSET越多，数据库引擎需要遍历更多的行， 无论是全表遍历，还是索引遍历，都会造成大量的扫描耗时。

从某种程度上来讲，OFFSET导致的性能下降是不可避免的，因为它需要扫描行是需要耗时的。 这种时候我们可以从应用层做一些操作，比如我们记录下最后一条记录的ID，那么下次遍历时可以快速记录下主键ID从而实现快速定位。

无法命中索引​

  • 索引字段使用了函数：因为函数会破坏索引的有序性，从而导致全表扫描。
  • 隐式类型转化：比如查询字段为字符串，但实际查询条件使用的数字，这会导致在实际查询过程中的执行类型转化。
  • 隐式编码转化：当两张表使用了不同类型的编码时，实际查询过程中会执行编码类型转化。

单次查询很慢​

  • 无索引：走全表扫描
  • 有索引：
    • 没有正确命中索引
    • 索引体积过大
  • 存在脏页，数据库需要先刷脏页

性能定期抖动​

count(*)会扫描全表​

删除表后空间不变​

以 InnoDB 为例，数据库引擎在删除数据并不会立即释放磁盘空间，而是将删除的数据标记为已删除。以便后续可以复用。

如果需要释放这些删除数据，需要执行 OPTIMIZE TABLE 重建表。其原理就是新建一张临时表实现替换。

死锁​

在并发系统中，出现循环的资源依赖就会导致死锁。在数据库中，就是多个事务相互等待对方释放资源而导致死锁。

如何避免死锁​

  1. 设置事务超时
    • SET SESSION innodb_lock_wait_timeout = 10;：这个设置是会话级别的，只对当前会话有效
    • SET GLOBAL innodb_lock_wait_timeout = 10;：这个设置是全局的
  2. 使用死锁检测机制，他会引入一定性能消耗，当发生死锁时，会主动回滚其中一个事务。

主备延迟过大​

造成主备延迟过大的可能原因：

  1. 备库服务器配置比主库低
  2. 备库读压力过大
  3. 存在大事务，导致延迟增大

解决方案：

  1. 升级备库服务器配置
  2. 一般备库是单线程回放binlog，当主库业务压力较大时，备库延迟不可避免会上升。这时备库也已启用并行复制。
  • 按库并行
  • 按表并行
  • 按行并行

页分裂和页合并​

InnoDB 是 MySQL 数据库引擎中的一种存储引擎，它使用 B+ 树（B-tree）数据结构来组织存储数据。在 InnoDB 中，页是最小的存储单元，每个页默认大小为 16KB。 当插入、更新或删除数据时，InnoDB 可能会进行页的分裂和合并来维护 B+ 树的平衡。

页分裂（Page Split）​

当一个页中的数据已经满了，而需要插入新的数据时，InnoDB 会执行页分裂操作。页分裂的基本过程如下：

  1. 选择分裂点： 选择一个合适的位置，将该位置之后的数据移到一个新的页中。

  2. 创建新页： 创建一个新的页，并将分裂点之后的数据复制到新的页中。

  3. 更新父节点： 更新父节点，将新页的相关信息插入到父节点中。

页分裂确保了 B+ 树的平衡，使得树的高度不会过深，提高了查询效率。然而，页分裂也可能导致频繁的磁盘 I/O 操作，因为需要移动和复制数据。

页合并（Page Merge）​

与页分裂相反，页合并是在删除数据后为了维持 B+ 树的平衡而执行的操作。当一个页中的数据量变得较小，且相邻的兄弟页也没有足够的空间容纳数据时，InnoDB 可能会执行页合并操作。页合并的基本过程如下：

  1. 选择合并点： 选择一个合适的位置，将该位置之后的数据合并到相邻的兄弟页中。

  2. 合并数据： 将数据从当前页移动到相邻的兄弟页中。

  3. 更新父节点： 更新父节点，删除当前页的相关信息。

页合并有助于减少存储空间的浪费，但也可能导致频繁的磁盘 I/O 操作。因此，页分裂和页合并都是在平衡查询性能和存储空间利用率之间进行权衡的过程。

几十亿数据量级的表的插入更新删除会这么慢​

在一张几十亿的大表中，进行curd操作，速度一般不会很快。

更新一张表的速度差距主要取决于多个因素，包括表的大小、索引的结构、系统资源、以及更新操作的具体内容。下面是一些可能导致速度差距的原因：

  1. B+ 树的高度： 当表的数据量很大时，B+ 树的高度可能会增加。在查询时，如果需要遍历更深的层次，查询速度可能会减慢。这与页分裂和合并有关，因为这些操作可能会导致树的重新平衡。
  2. 页分裂和合并： 当更新表时，特别是涉及到索引的更新时，可能触发页分裂和合并。这些操作可能导致额外的磁盘 I/O 操作，影响性能。在一个包含数十亿行的表中，这些操作可能会更加显著。
  3. 索引结构： 表的索引结构对更新操作的性能有重要影响。如果表有多个索引，更新操作可能需要更新多个索引，这可能会导致额外的开销。
  4. 事务和锁： 在更新大表时，事务和锁的管理可能对性能产生显著影响。如果有很多并发的更新操作，锁的争用可能会导致性能下降。
  5. 系统资源： 更新操作的性能还取决于系统的硬件和配置。如果系统资源有限，例如内存、磁盘 I/O 带宽等，更新操作的性能可能会受到限制。

数据页的访问模式​

数据页的访问模式对数据库性能有重要的影响，尤其在大表的情况下。以下是一些可能导致数据页访问性能快慢的因素：

  1. 顺序访问 vs 随机访问
  • 顺序访问（Sequential Access）： 当数据按照某个顺序（例如主键顺序）存储时，执行顺序访问可能更为高效。这是因为相邻的数据通常在相邻的数据页中，减少了磁盘 I/O 操作的次数，提高了性能。
  • 随机访问（Random Access）： 如果更新、插入或删除操作导致数据页的随机分布，即使逻辑上连续，也可能需要跨越多个数据页进行读取和写入。这样的随机访问可能增加了磁盘 I/O 操作，影响性能。
  1. 数据页的大小
  • 小页 vs 大页： 较小的数据页可能导致更多的页分裂和合并操作，增加了额外的开销。一方面，小页可能更容易在内存中缓存，但在处理大量数据时可能导致频繁的磁盘 I/O 操作。较大的数据页可以减少 I/O 操作次数，但可能会导致缓存利用率降低。
  1. 数据表的填充因子
  • 填充因子： 数据表的填充因子指的是数据页中实际存储的数据占总页空间的比例。如果填充因子较低，数据页中有很多空闲空间，可能导致更多的磁盘 I/O 操作。如果填充因子过高，可能导致页的分裂和合并频繁，影响性能。
  1. 索引的使用
  • 索引的选择和使用： 合适的索引能够改善数据页的访问性能。但是，如果使用过多或不必要的索引，可能会增加更新操作的开销。此外，非覆盖索引可能导致需要额外的数据页访问。
  1. 查询计划和统计信息
  • 查询计划和统计信息的准确性： 数据库系统使用统计信息生成查询计划。如果统计信息不准确，生成的查询计划可能不是最优的。在大表中，维护准确的统计信息可能变得更加昂贵。

为什么大表页分裂/合并成本高​

  1. 单次页分裂导致数据移动 在 InnoDB 中，B+ 树的每个节点（包括根节点和非叶子节点）都包含多个键值对。当一个节点需要分裂时，例如，由于插入新的键值对导致节点溢出，发生的分裂操作会影响到父节点和兄弟节点。 假设一个节点包含键值对 A, B, C，现在需要插入键值对 D，导致节点溢出。为了保持 B+ 树的有序性，系统可能会将 C 移动到一个新的节点，创建一个新的节点用于存储 D，并将新节点的键值对连接到父节点。 这个过程中可能涉及到数据页的拷贝、分配新的数据页等操作，导致了数据的移动。这样的操作可能在整个 B+ 树结构中产生级联效应，影响性能。
  2. 重做日志的产生 在 InnoDB 中，每个事务的修改都会被写入重做日志（redo log）中，以确保事务的原子性和持久性。当进行页分裂和合并操作时，可能会涉及到多个数据页的修改，这些修改会被记录到重做日志中。 即使是单次修改一条数据，如果这个修改引发了页的分裂，那么整个分裂的过程中所做的修改都需要被记录到重做日志。这是因为数据库引擎需要能够回滚或重做事务，确保在系统崩溃或断电的情况下，数据的一致性和持久性。 重做日志中的记录可能包括数据页的物理位置、修改的偏移量以及修改的内容等信息。因此，即使是修改一条数据，若该操作引发了页的修改，重做日志的产生量也可能相对较大。 在实际应用中，为了减少重做日志的产生，可以考虑调整事务的隔离级别、批量提交事务、避免不必要的索引等。这些措施可以在一定程度上减少不必要的重做日志量。

块索引​

BRIN即Block Range Indexes，顾名思义，就是对数据块区段所做的索引。其实它的设计思路很简单，就是通过扫描整个表，记录下每个固定区段（例如第1到128号数据块）所含数据被索引字段的最大值和最小值， 依次存入索引空间。当处理某个查询，需要找到符合查询条件的记录时，可以使用BRIN索引，跳过与查询条件不符合的区段，加速查找。

块索引的主要思想是将相邻的一批数据行组织成块，因此它不关注全局的有序性。它更适用于：

  • 数值范围查询
  • 频繁的插入和删除操作

对于经常大批量尾部插入的表，B+树为什么会发生尾部更新的互斥​

其中主要的问题之一是页分裂和索引的维护成本。

  • 页分裂导致性能下降： 大批量的尾部插入可能导致数据页的频繁分裂。当一个节点的数据页已满，进行尾部插入时可能触发页分裂操作，这会导致磁盘 I/O 操作，增加了写入成本。
  • 索引维护成本增加： B+ 树索引的维护通常涉及到对节点的分裂、合并等操作，这些操作可能导致大量的磁盘 I/O 和 CPU 计算开销。在大批量插入时，索引的维护成本可能显著增加，影响整体性能。

PostgreSQL​

时序数据库​

时序数据库是专门设计用于处理时间序列数据的数据库系统，因此在处理时间数据方面通常更高效。常用于存储和查询按时间顺序产生的数据，如传感器数据、日志数据、金融数据等。

以下是一些时序数据库相对于通用数据库（如 MySQL）的优势和其原理：

  • 列式存储： 时序数据库通常使用列式存储，将同一列的数据存储在一起，这样可以提高压缩率和读取效率。这在时间序列数据中通常更为适用，因为往往只需要查询特定的列。
    • 相比于行式存储，列式存储有一些优势，特别适合处理大量的分析查询、聚合操作和数据压缩。
    • 压缩效果更好： 列式存储通常能够更好地利用数据的局部性，实现更高的压缩率。由于同一列的数据通常是相似的，压缩算法更容易找到重复模式，从而减小存储空间。
    • 只读性能提高： 列式存储对于只读查询、聚合和分析操作的性能较高。这是因为只需要读取所需列的数据，而不需要读取整行，从而减少了 I/O 操作。
    • 跳过不必要的列： 在列式存储中，查询可以直接跳过不必要的列，减少了不必要的数据传输和处理。
  • 精简的数据模型： 时序数据库对于时间序列数据的模型相对简化，去掉了一些通用数据库中的不必要的特性，提高了写入和查询的效率。
  • 数据分区： 时序数据库通常使用数据分区的方式，将数据按照时间进行划分，以便更快速地定位和查询特定时间范围的数据。
  • 数据压缩： 时序数据库采用多种压缩算法，减小数据存储/索引空间，同时提高读取效率。
  • 时间索引：时序数据库会针对时间数据建立专门的索引结构，例如使用 B+树 / 跳表等，以支持高效的时间范围查询。

Elasticsearch​

TIDB​

TiDB（Ti Distributed Database）是一个开源的分布式数据库系统，它具有水平扩展、高可用性和分布式事务支持等特性。TiDB 的架构设计允许它在大规模的数据集上实现高性能和高可靠性。

TiDB 的架构主要包括三个核心组件：

  1. TiDB Server： TiDB Server 负责 SQL 的解析和执行，它是一个兼容 MySQL 协议的分布式数据库服务器。TiDB Server将 SQL 查询分解成计算任务，并将这些任务分发到底层的 TiKV 存储引擎进行处理。

  2. TiKV： TiKV 是一个分布式事务键值存储引擎，被设计为支持大规模的分布式事务。它负责存储数据并执行事务，具有分布式、水平可扩展、自动故障转移等特性。TiKV 使用 Raft 协议来确保数据的一致性和可用性。

  3. PD（Placement Driver）： PD 是 TiDB 中的元数据管理组件，负责存储集群的元数据信息，包括数据分布、节点状态、数据调度等。PD 通过自动化的方式管理集群的拓扑结构，支持自动的数据分片和负载均衡。

TiDB 的性能优势主要体现在以下几个方面：

  • 水平扩展： TiDB 支持水平扩展，可以方便地添加更多的节点来提升系统的整体性能。这使得 TiDB 能够适应不断增长的数据规模和查询负载。

  • 分布式事务： TiDB 支持分布式事务，能够在整个集群中保证 ACID 特性。这为应用提供了强一致性的事务支持，同时 TiDB 的架构设计也保证了分布式事务的性能。

  • 自动化的负载均衡和故障转移： PD 负责自动管理集群的拓扑结构，包括节点的加入和退出、数据的分片和调度等。这使得 TiDB 具有很好的负载均衡和故障转移能力，提高了系统的稳定性和可靠性。

  • 优化的执行计划： TiDB 使用 TiKV 作为存储引擎，并通过优化的执行计划来减少不必要的数据读取和计算。这有助于提高查询性能，特别是在复杂查询和大规模数据集的情况下。

综合来说，TiDB 的性能优势来自于其分布式、水平可扩展、自动化管理的架构设计，以及对分布式事务和执行计划的优化。这使得 TiDB 能够在大规模和高负载的场景下提供高性能、高可用性的分布式数据库服务。


名词解释​

脏页​

脏页（Dirty Page）是指缓冲池中的页（Page）被修改过但尚未写回到磁盘的状态。 缓冲池是用于暂时存储数据库中的数据页的区域，以提高数据读取和写入的性能。 该页被称为“脏页”，因为它在内存中的内容与磁盘上的对应数据不同。

主备延迟​


开放话题​

列举一些你的数据库优化经验​

优化数据库，需要先知道数据库瓶颈在哪里。其实很多问题都可以通过升级数据库配置解决。

比如硬件方面可以升级SSD、升级服务器配置，软件方面可以调大整缓冲区、连接池等。在大多数普通场景中，这往往是最简单有效的方式。

在一些特殊场景，比如高并发、高查询、高写入等场景，这些特殊场景往往都需要从架构层面去解决根本问题。

数据库层的优化肯定是很重要的，但可能不是核心。

数据库优化的点其实很多：

第一个优化点就是SQL本身。我遇到过直接用SQL写业务的项目，洋洋洒洒仅100行的SQL，各种子查询，各种关联查询。正常情况下我肯是不会去碰它的，但让速度太慢，严重影响业务了。 我当时的做法就是拉通业务方，重新梳理清楚了需求，把业务逻辑放在代码里，尽量精简SQL。

第二个优化点就是索引。这种一般是按业务场景，去选择更合适的索引。
对于一些范围查询、排序场景，可以考虑使用B+树索引。
对于一些纯等值查询的场景，可以考虑使用哈希索引。
对于一些高频的多列查询，可以考虑使用复合索引，通过索引覆盖减少回表次数从而提高查询性能，同时也可以减少索引数量和体积。
对于一些长字符串的场景，可以适当考虑前缀索引。

第三个优化其实就是我们常说的读写分离、分库分表。
以分表来说，我遇到过一个url运营场景。运营数据具有延迟性。一天的数据量都是千万级。每天按时间建一张表，这张表是没有索引的。因为索引影响写入性能。 然后凌晨跑一个定时任务，把昨天的表建一个索引。

还有一个场景，也是我上一位领导留下来的。对MD5的优化。字符串长度是32，大小就是32字节。 但是，你把他转化16进制，它大小就只有16字节。而且完美解决了大小问题。

数据库架构：

由服务层和引擎层组成。其中，服务层又包含：连接器、查询缓存、分析器、优化器、执行器，而引擎层则支持InnoDB、MyISAM、Memory。

对于一条SQL的执行过程：

  1. 客户端与数据库层建立连接。
  2. 数据库连接器负责建立连接，并在此过程中验证用户合法性、权限等。
  3. 如果是查询SQL，会优先去查询缓存中查看是否已经存在缓存，如果存在则会直接返回。（mysql8+版本中已经废弃查询缓存，弊大于利）
  4. 数据库分析器负责将SQL解析成语法树。在此过程中会也会验证SQL的合法性。
  5. 数据库优化器，会根据SQL的查询条件，表索引情况等，综合选择查询方式。（不是有索引就一定会用索引的，比如某索引区分度过低，那么优化器很可能会判定全表扫描）
  6. 数据库执行器会调用引擎接口，去获取/更新数据
  7. 引擎会先从buffer pool中查询是否存在，不存在则按指定条件去读取数据页，并刷到buffer pool中
  8. 引擎层将要更新的数据库刷入buffer pool，并将其标记为脏页。然后将更新数据库写入redo log中，此时将redo log标记为prepare阶段。实现了WAL机制
  9. 引擎层生成undo log并记入缓冲区
  10. 服务层提交事务，并记录binlog

事务隔离级别：

  • 读未提交：存在脏读、不可重复读、幻读
  • 读已提交：存在不可重复读、幻读
  • 可重复读：存在幻读
  • 串行化

MVCC也叫多版本并发视图，是可重复读事务隔离级别的核心机制之一。在创建事务的时候，会创建ReadView视图。该视图维护了当前事务：所有存活的事务、存活事务的最小事务ID、当前事务ID、下一个事务ID。 然后创建undo log，undo log也是行数据，事务ID和undo log指针，因此undo log本质是一个版本链。 而ReadView则是控制在此版本链中那些是可以被当前事务看到的，那些是不应该看到的。

锁类型：

  • 全局锁
  • 表锁：共享表锁、独占表锁、元数据锁、意向锁
  • 行锁：记录锁、间隙锁、next-key

日志：

  • undo log：回滚日志

  • redo log：重做日志

  • binlog：归档日志

    • statement：记录原始sql语句
    • row：记录具体行的修改内容
    • mined：由数据库判断那些sql可以使用statement，那些应该使用row

  • 主从复制机制

    • 同步复制：全部从节点同步成功后次啊响应客户端请求
    • 异步复制：不管从节点同步情况，直接响应客户端请求
    • 半同步复制：只要由部分从节点同步成功，就视为同步成功

索引类型

  • 按数据结构划分：B+树、哈希
  • 按行数据的存储位置：聚簇索引、非聚簇索引。其中聚簇索引也叫主键索引、非聚簇索引也叫二级索引
  • 按字段进行划分：主键索引、普通索引、联合索引、唯一索引等

索引优化：

  • 索引覆盖：减少回表次数
  • 索引下推：减少回表次数
  • 前缀索引：减少索引体积
  • key是md5，长度为32各字节。使用的是哈希索引。使用前缀索引，索引体积可以减少一半。但是区分度令人窒息。md5转成16进制，一共128位，只占16字节，还顺便解决了大小写问题

索引失效：一般是两种：破坏了有序性、被优化器判断不使用

  • 对索引加了计算函数
  • 隐式类型转化，这种也可以理解位在外面套了一个编码转化的函数
  • 索引区分度不高，被判定不使用使用
  • 最左前缀原则

join：

  • nexted loop join：嵌套循环，性能最差。
  • indexed join：选择较小的表，遍历每一行，去查询另一张表的索引
  • hash join：在内容中构建哈希表，通常选择较小的表，然后对于较大的表，遍历每一行去哈希表中匹配，看是否满足条件。
  • merged join：两张表均有索引，且有序，因此性能会更好

数据库引擎：

  • innodb：支持事务、行锁、支持外键
  • mysiam：不支持事务、支持表锁

数据页合并、分裂： 页可以为空或者填满，此外也还有一个默认的合并阈值（50%），当删除或者更新发生的时候，当前页和下一页若满足在一页内分配，那么就会触发页合并。合并过程会申请表锁。 同理，当插入发生的时候，如果页无法存放，则会造成页分裂。 虽然它们在物理存储上是乱序的，但因为是双向链表，所以在逻辑上是有序的。