Lazy loaded image
八股盛宴
MySQL
字数 19033阅读时长≈ 48 分钟
2025-5-18
2026-1-30
type
Post
status
Published
date
Jan 30, 2026 12:22 PM
slug
summary
MySQL 是一种关系型数据库，主要用于数据持久化。文档涵盖了 MySQL 的基本使用，包括数据定义语言（DDL）、数据操作语言（DML）、事务控制语言（TCL）和数据控制语言（DCL），以及 MySQL 的架构、存储引擎（如 InnoDB）、索引、锁机制和事务管理。还讨论了数据库优化策略、命名规范和分库分表的实现细节，提供了对 MySQL 的全面总结和未来学习方向的建议。
tags
数据库
category
八股盛宴
icon
password
MySQL 数据库是一种关系型数据库，主要用于持久化数据。

基础命令

DDL
Data Definition Language，DDL 的主要功能是定义数据库对象。
核心指令是 CREATE、ALTER、DROP
  1. DATABASE
    1. CREATE DATABASE database_name
    2. DROP DATABASE database_name
    3. USE DATABASE database_name
  1. TABLE
    1. CREATE TABLE
      1. table (
          2. id int(10) unsigned NOT NULL COMMENT ‘Id;
          )COMMENT = ’用户表’
      2. table AS SELECT * FROM existed_table
    2. DROP TABLE table
    3. ALTER TABLE table
      1. ADD col_name col_type
      2. DROP COLUMN col_name
      3. MODIFY COLUMN col_name new_type
      4. DROP PRIMARY KEY
  1. VIEW视图是基于 SQL 语句的结果集的可视化表。 视图是虚拟表，本身并不包含数据，所以不能对其进行索引操作。 视图主要用来简化复杂的 SQL 操作以及通过只给用户开放视图来保证数据安全性。
    1. CREATE VIEW view AS SELECT id FROM table WHERE id < 10
    2. DROP VIEW view
  1. INDEX
    1. CREATE (UNIQUE) INDEX index ON table(col)
    2. ALTER TABLE table ADD INDEX index(col)
    3. ALTER TABLE table DROP INDEX index
  1. 约束
    1. NOT NULL
    2. UNIQUE
    3. PRIMARY KEY
    4. FOREIGN KEY
    5. CHECK
    6. DEFAULT
DML
Data Manipulation Language, DML 的主要功能是读写数据库。
核心指令是 INSERT、UPDATE、DELETE、SELECT
相关子查询
窗口函数
  1. SELECT
    1. 检索
      1. SELECT col_1 FROM table
      2. SELECT col_2, col_2 FROM table
      3. SELECT * FROM table
    2. DISTINCT
      1. SELECT DISTINCT col FROM table
    3. LIMIT ··· OFFSET ··· 177. 第N高的薪水 - 力扣（LeetCode）
      1. SELECT col FROM table LIMIT 5
      2. SELECT col FROM table LIMIT 5 OFFSET 5
    4. ORDER BY
      1. SELECT col from table ORDER BY col
      2. SELECT col_1, col_2, col_3 from table ORDER BY col_1, col_2
      3. SELECT col_1, col_2, col_3 from table ORDER BY 2, 3
      4. SELECT col from table ORDER BY col DESC / ASC_DESC关键字只应用到直接位于其前面的列名，如果想在多个列上进行降序排序，必须对每一列指定 DESC 关键字。
    5. WHERE 应使 WHERE 位于 ORDER BY 之前。
      1. SELECT col FROM WHERE col = 3.49
      2. SELECT col FROM WHERE col BETWEENT 3 AND 5
      3. SELECT col FROM WHERE col IS NULL
      4. AND / OR / NOT
      5. IN
          6. SELECT id FROM table1 WHERE col IN (SELECT col FROM table2 WHERE col = ‘asdas’) 作为子查询的检索语句只能查询单列。
    6. LIKE 通配符搜索只能用来匹配字符串
      1. SELECT col FROM WHERE col LIKE ‘FIsh%’
      2. SELECT col FROM WHERE col LIKE ‘FIsh_’
    7. GROUP BY
        8. SELECT id, CONUT(*) AS num FROM table GROUP BY id
    8. HAVING WHERE 和 HAIVNG 的功能类似。 唯一差别是 WHERE 过滤行，HAVING 过滤分组。 SELECT id, CONUT() AS num FROM table GROUP BY id HAVING COUNT() >= 2
    9. PARTITION BY 用于窗口函数 中，将数据划分为逻辑分区，在每个分区内进行计算（如排名、累计和等）。PARTITION BY 会保留所有原始行，并在每个分区内计算结果。
      10. JOIN 连接的本质是将不同表的记录合并起来形成一张存在于本次查询期间的新表，通过 on 或者 using 指定连接条件。
      SELECT table1.col, table2.col FROM table1 JOIN table2 ON table1.col = table2.col
      1. INNER JOIN 默认连接方式，当两个表都存在满足条件的记录才会返回行。
      1. LEFT JOIN / LEFT OUTER JOIN 返回左表所有行，即使右表没有满足条件的行
      1. RIGHT JOIN / RIGHT OUTER JOIN 返回右表所有行，即使右表没有满足条件的行
      1. FULL JOIN / FULL OUTER JOIN 只要其中一个表有满足条件的记录，就返回行。
      1. SELF JOIN
      1. CROSS JOIN
      UNION UNION 运算符将两个或更多查询结果组合起来，并生成一个结果集。 需要注意的是，所有查询的列数和列顺序必须相同。每个查询中涉及表的列的数据类型必须相同或者兼容。通常返回的列名取自第一个查询
      函数 使用特殊函数和算数符号执行计算,并使用别名引用他们。 需要注意的是从效率的角度考虑，不应该在数据库层面进行计算，所有运算应该尽可能放在软件应用层面。
      1. SELECT Concat(vend_name, ‘(’, vend_country, ‘)’) FROM table AS other_name
      1. 文本
        1. LEFT()、RIGHT()
        2. LOWER()、UPPER()
        3. LTRIM()、RTRIM()
        4. LENGTH()
      1. 时间 YYYY-MM-DD & HH:MM:SS
        1. CurDate() / CurTime() / Now()
        2. DateDiff()计算时间差
        3. DayOfWeek()
        4. Day() / Year() / Month()返回日期的对应部分
      1. 数值
        1. SIN / COS / TAN / ABS / SQRT / MOD / EXP / PI / RAND
        2. AVG() / COUNT() / MAX() / MIN() / SUM()
  1. UPDATE
    1. UPDATE table SET col = ‘new_col_value’ WHERE col = value
  1. DELETE
    1. DELETE FORM table WHERE col = value
TCL
Transaction Control Language, TCL 的主要功能是管理数据库事务。
  1. START TRANSACTION
  1. SAVEPOINT
  1. ROLLBACK TO
  1. COMMIT
DCL
Data Control Language, DCL 的主要功能是控制用户的访问权限。注意 MySQL 使用用户账号来管理访问权限，也就是是说可以同时有若干不同主机使用相同账号访问数据库服务器。
  1. SHOW GRANTS FOR user_name 查看用户权限
  1. CREATE USER user_name IDENTIFIED BY ‘password’ 创建新用户
  1. DROP USER user_name 删除用户
  1. UPDATE user SET user = ‘new_username’ WHERE user = ‘user_name’ FLUSH PRIVILEGES 修改用户名
  1. SET PASSWORD FOR user_name = ‘new_password’ 修改用户密码
  1. GRANT privilege ON privilege_level TO user 授予权限 privilege 指定若干权限（ALL、ALTER、INSERT and so on） privilege_level 确定权限应用级别（global、database、table） user 是授予权限的用户（如果用户已经存在，则修改其权限，否则，创建对应权限新用户）
    1. [INDENTIFIED BY password] 可选字句为用户设置新密码
    2. [REQUIRE tsl option] 指定用户必须通过 SSL 等安全连接连接数据库
    3. [WITH [GRANT_OPTION | resource_option]] 授予其他用户或从其他用户删除您拥有的权限。 此外，还可以使用 WITH 字句分配 MySQL 数据库服务器资源。
  1. REVOKE 撤销权限
      2. privilege ON privilege_level FROM user
    1. username@host
        2. 新创建用户没有任何权限。
        账户以 username@host 定义，信息保存在 mysql.user 中。
Other
  1. 存储过程 存储过程是对一系列 SQL 操作的批处理。 存储过程可以由触发器，其他存储过程以及应用程序调用。 需要注意的是，Alibaba《Java 开发手册》强制禁止使用存储过程，因为存储过程难以调试和扩展。
  1. 游标 游标是存储在 DBMS 服务器上的数据库查询，是被 SELECT 语句检索出的结果集。在存储过程中使用游标可以对一个结果集进行移动遍历。
  1. 触发器 触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，调用该对象。 在 MySQL 5.7.2 以前每个表最多可以定义六个触发器，这之后可以为同一触发事件定义多个触发器。
    1. CREATE TRIGGER trigger_name trigger_time (AFTER BEFORE) trigger_event(INSERT UPDATE DELETE)ON table_nameFOR EACH ROWBEGIN trigger_statementsEND
    2. SHOW TRIGGERS
    3. DROP TRIGGER IF EXISTS trigger_name

架构

MySQL 架构可以分为 Server 层 和 存储引擎层

Server 层

MySQL 大多数核心功能模块都在这里实现。
主要包括连接器、查询缓存（MySQL 8.x 移出）、解析器、预处理器、优化器、执行器等。
另外，所有的内置函数和所有跨存储引擎的功能都在 Server 层实现。
连接器
  1. database 和 Instance。 前者是实在的文件，包括物理操作系统文件或其他形式文件类型的集合；后者是由后台线程以及一个共享内存区组成。一般来说，database 和 Instance 对应，但是在集群情况下可能存在 database 被多个 Instance 使用的情况。 MySQL 被设计为一个单进程多线程架构的数据库，也就是说 MySQL Instance 在系统上表现为进程。
  1. MySQL 连接就是 MySQL Instance 和连接进程进行通信，本质上是进程通信。
    1. 管道通信。命名管道和共享。
    2. UNIX 域套接字。
    3. TCP/IP 连接。
        4. 建立 tcp 连接
        1. 空闲连接 MySQL 定义了空闲连接的最大空闲时长，由参数 wait_timeout 控制，默认 8h，如果超时，连接器就会断开连接。 处于空闲状态的连接被服务器主动断开后，这个客户端并不会立刻知道，等到客户端下一次发送请求的时候才会被感知。
        1. 连接限制 MySQL 服务器支持的最大连接数由 max_connections 参数控制，如果超过，系统就会拒绝接下来的连接请求。
        1. 长短连接 和 http 类似，MySQL 连接也分长短。 短连接只发送一条 sql 语句后就断开，而长连接一旦建立连接就可以不断发送 sql 语句，一般来说，为了减少建立和断开连接的过程采用长连接。 但是这会引发新的问题，大量长连接占用过大内存以至于别系统强制关闭，最终导致 MySQL 服务器异常重启。这是因为 MySQL 在执行过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才会释放。
          1. 定期断开长连接。
          2. 客户端主动重置连接。 MySQL5.7 实现了 mysql_reset_conection（）函数接口，当客户端执行一个很大的操作后，调用这个函数来重置连接，达到释放内存的目的。 这个过程不需要重连和重做权限认证，但是会恢复到连接刚刚创建时的样子。
         
        校验账号密码
        读取权限
        连接器获取用户权限，然后保存，后续用户在此连接里的任何操作都会基于连接开始时候读取到的权限进行逻辑判断。也就是说，即使管理员修改了用户权限，也不会影响到存在连接的权限，只能应用于新建的连接。
查询缓存
MySQL 会解析 sql 语句的第一个字段，如果是 select 就会先去查询缓存（Query Cache）试图获取同样命令查询的结果。 缓存命中则直接返回，未命中则继续查询。
对于更新频繁的表，查询缓存命中率很低。所以 MySQL 8.0 删除了这一组件。对于更早的版本，可以通过参数设置的方法关闭查询缓存。
解析器.
MySQL 开始解析 sql 语句语法，语法错误则报错。
大致分为词法分析和语法分析，前者提取关键字，后者根据关键字判断是否满足 MySQL 语法，正确则构建 SQL 语法树，方便后续模块获取 sql 类型、表名、字段名、where 条件等。
预处理器
检查 sql 语句中的表或者字段是否存在。
优化器
确定 sql 查询语句的执行方案。
e.g 表中存在多个索引的时候，优化器会基于查询成本来选择索引。
执行器
执行语句，主要是和存储引擎交互，交互以记录为单位。

存储引擎层

存储引擎负责数据的存储和提取，支持不同的引擎。
存储引擎是基于表的，而不是数据库，不同的存储引擎共用同一 Server 层。
如果需要更换引擎可以使用 alter 语句修改表结构。

InnoDB 存储引擎

InnoDB 体系架构分为后台线程和共享内存共同管理本地文件。

后台线程

工作的执行者。
Master Thread
主要负责将缓冲池中的数据异步刷新到磁盘（调度 Page Cleaner Thread），保证数据的一致性。
Master Thread 工作方式
Master Thread 具有最高的线程优先级，会根据数据库运行的状态在 loop、background loop、flush loop 和 suspend loop 之间切换。
  1. loop主循环，执行大多数操作。分为每 1s 的操作和每 10s 的操作。
  1. background loop若当前没有用户活动或者数据库关闭时，就会切换到这个循环。任务主要是删除无用的 undo 页，合并插入缓冲。
  1. flush loop
  1. suspend loop
IO Thread
Async IO 当 InnoDB 需要执行 I/O 操作时，它不会直接等待 I/O 操作完成，而是将请求提交给操作系统。操作系统会处理这些请求，并在 I/O 操作完成后通知数据库。 这个通知机制通常涉及回调函数（callback），也就是当 I/O 操作完成后，操作系统会调用一个预先注册的函数来告知数据库 I/O 已经完成。 IO Thread 的作用是处理这些 I/O 请求的完成通知，并执行相应的后续操作。
Purge Thread
清理旧版本数据，达到释放存储空间、维护一致性和优化性能的效果。
当一个行被删除或更新时，InnoDB 不会立即从磁盘上删除旧的数据页，而是保留旧的行版本，以便支持事务的多版本并发控制（MVCC）。 Purge Thread 的任务就是在适当的时机清理这些旧版本的数据。
Page Cleaner Thread
其作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。而其目的是为了减轻原Master Thread的工作及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能。

共享内存

Buffer Pool
InnoDB 存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。因此可将其视为基于磁盘的数据库系统。 在数据库系统重，由于 CPU 速度和磁盘速度之间的鸿沟，所以使用缓冲池技术来提高数据库的整体性能。
缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。
缓冲池概述
  1. 在数据库中读取页 首先将从磁盘中读取的页放在缓冲池中，这个过程称为 FIX。 下一次读取页时，先判断页是否在缓冲池中。若命中缓冲池，直接读取，否则，读取磁盘。
  1. 在数据库中修改页 修改缓冲池中的页，然后以一定频率刷新到磁盘上。
缓存池大小直接影响着数据库的整体性能。
因为缓冲池中存储着数据页（data page）、索引页（index page）、插入缓存（Insert page）、锁信息（Lock info）、自适应哈希索引、数据字典信息。
从 InnoDB 1.0.x版本开始， 允许有多个缓冲池实例。
每个页根据哈希值平均分配到不同缓冲池实例中。 这样做的好处是减少数据库内部的资源竞争，增加数据库的并发处理能力。 可以通过参数innodb_buffer_pool_instances来进行配置，该值默认为1。
缓冲池管理
有关数据页的管理。
  1. LRU List 管理 Buffer Pool 中的已经读取的数据页。 最频繁使用的页在列表的前端，而最少使用的页在列表的末尾。当缓冲池不能存放新读取到的页的时候，将首先释放列表末尾的页。
    1. 朴素的 LRU 算法 直接将读取的页放在列表首部。 这会导致在执行扫描操作的时候，大量新的页被刷入，而此前的热点页会被全部删除，下次需要重新建立。
    2. midpoint insertion strategy InnoDB 引擎引入 midpoint，新读取的页会被放在 midpoint 指定的位置。midpoint 默认为 37。 同时，midpoint 之后的列表称为 old 列表，之前称为 new 列表。简单理解为 new 列表中是更为活跃的热点页。
  1. Free List 管理 Buffer Pool 中的空闲的页。 当有新的页面需要加载到缓冲池的时候，首先从 Free List 中寻找，然后删除该页的同时加载到 LRU List，否则，从 LRU 中选择末尾进行替换。 当页面释放的时候，页面会被添加到 Free List 中，以供后续使用。
  1. Flush List 管理 Buffer Pool 中的脏页。 Page Cleaner Thread 会从 Flush List 中选择页面进行刷新。这些页面是从 LRU List 中挑选的被修改过的页，称为脏页。
特定用途的小型内存池
  1. redo log Buffer InnoDB 首先将 redo log 放入此处，然后按一定频率刷新到 redo log file。
  1. Change BufferChange Buffer 一个用于缓存二级索引更新操作的内存区域。当一个二级索引页在缓冲池中找不到时，InnoDB 会将更新操作暂时存储在 Change Buffer 中，等到该页被加载到缓冲池后再应用更新。这样可以减少磁盘 I/O 操作，提高性能。
  1. Adaptive Hash IndexAdaptive Hash Index InnoDB 自动创建的一种内存结构，用于加速索引查找。当 InnoDB 发现某个索引被频繁访问时，它会在内存中创建一个哈希索引。这个哈希索引使用的是额外的内存池中的内存。
  1. Additional Memory Pools for Data StructuresInnoDB 在对一些内部数据结构进行内存分配时，也会使用额外的内存池。这些数据结构包括但不限于：
      2. Page Directory：用于组织和管理缓冲池中的数据页。
      Mutexes and Semaphores：用于并发控制的数据结构。
      Hash Tables and Index Trees：用于索引管理的数据结构。
      Other Internal Data Structures：如 B-tree 节点等。
InnoDB 关键特性
  1. Insert Buffer 向表中插入一行记录时，InnoDB 会更新主键索引和所有相关的二级索引，如果二级索引不在缓冲池中，InnoDB 不会将这些更新立即写入磁盘，而是将更新操作缓存到 Insert Buffer。 当二级索引页由于其他原因被加载到缓冲池时，InnoDB 会检查 Insert Buffer 并应用所有与该索引页相关的更新操作。完成操作后从 Insert Buffer 移出。InnoDB 会根据参数定期合并 InsertBuffer 中的更新操作，前提是缓冲池中存在对应的二级索引页，否则，这些操作将继续留在 Insert Buffer 中。 Insert Buffer Bitmap 用来跟踪辅助索引页上的可用空间，并确保有足够的空间来容纳新的记录。当插入新记录时，如果检测到辅助索引页上的可用空间不足（小于 1/32 页），InnoDB 会强制进行合并操作，即从磁盘加载辅助索引页到缓冲池，并将 Insert Buffer 中的相关记录合并到辅助索引页上。 这种方法有助于减少磁盘 I/O 操作，提高数据库性能，并确保数据的一致性。
  1. Double Write
    1. 作用 Double Write 带给 InnoDB 引擎的是数据页的可靠性。 当数据库发生宕机，可能整下写入某页到表中，而这个页纸写了一部分，比如 16KB 的页只写了前 4KB，这种情况称为部分写失效，这可能导致数据丢失。 redo log 的确可以恢复数据，但是 redo log 记录的是对页的物理操作，如偏移量 800，写 aaaaa 记录。在数据页损坏的情况下是没有意义的。这就是说，在应用 redo log 前，用户仍然需要一个页的副本，当写入失效时，先通过副本还原该页，再进行重做，这就是 Double Write。 这个功能可以通过参数关闭。
    2. 实现 在对缓冲池中的脏页进行刷新时，并不直接写磁盘，而是先将脏页复制到内存中的 DoubleWrite Buffer，然后通过 DoubleWrite Buffer 分两次，每次 1MB 顺序写入共享表空间，再马上调用函数落盘。在这个过程中，DoubleWrite 页写入 DoubleWriteBuffer 时连续的，因此开销不是很大。此后将缓存中数据写入各个表空间则是离散的。
  1. Adaptive Hash Index 自适应哈希索引的主要功能是在内存中为经常访问的索引页建立一个哈希表，以加快索引查找的速度。它本质上是一个内存中的缓存机制，用于加速二级索引的查找速度。
  1. Flush Neighbor Page 当刷新一个脏页时，InnoDB 会检测该页所在区的所有页，如果是脏页，那么一并刷新。 这样做可以将多个 IO 写入操作合并成一个 IO 操作。

文件

数据库文件
  1. 参数文件
  1. socket 文件
  1. pid 文件
  1. 表结构文件
日志文件
  1. error log 记录 Mysql 的启动、运行和关闭过程。 该文件不仅记录所有的错误信息，也记录一些警告信息或正确信息。 当 MySQL 服务器不能正常启动的时候，首先查看此文件。 又是用户可以直接在 error log 中得到优化的帮助，因为有些 warning 很好的说明问题所在。
  1. binlog 记录 MySQL 数据库执行更改的所有操作。
    1. recovery 全备份文件恢复后，结合 binlog 可以达到 point-in-time 的效果。
    2. replication 通过复制和执行二进制日志使 Slave 与 Master 进行实时同步。
  1. slow query log 记录执行时间超过指定阈值的 SQL 语句，这对于性能调优非常有用。
  1. log 查询日志记录了所有对 MySQL 数据库请求的信息，无论这些请求是否得到了正确的执行。
存储引擎文件
表空间文件
  1. 表结构文件.frm
  1. 表数据文件.ibd MySQL 中每一张表的数据都存放在一个独立的.ibd 文件中。
    1. TableSpace
      2. Segment
      一般分为数据段、索引段和回滚段等。
      1. 索引段，存放 B+树的非叶子节点的区的集合。
      1. 数据段，存放 B+树的叶子节点的区的集合。
      1. 回滚段，存放回滚数据的区的集合。
      Extent
      InnoDB 存储引擎用 B+树来组织数据页，B+树每一层通过双向链表连接。使用页来分配存储空间，链表中相邻的两个页的物理位置可能离得很远，磁盘查询时就会出现大量的速度很慢的离散 I/O。 为了解决这个问题，InnoDB 引入 Extend 的概念。在表中数据量大的时候，为某个索引分配空间时不再按照页为单位分配，而是按照区为单位分配。每个区包含若干页，这就使得链表中相邻的页物理位置页相邻，可以使用速度很快的顺序 I/O。
      Page
      记录时按照行来存储，但是数据读取以页为单位，每个页默认大小为 16KB。 页的组织形式采用 B+树。
      row
      记录按照行来存储，每行记录根据不同的行格式有不同的行结构。
      1. Redundant
      1. Compact
          2. notion image
          对于行溢出采用部分溢出处理，行存储部分数据和指向存放剩余数据的溢出页的指针。
        1. 记录的额外信息
          1. 变长字段长度列表 只出现在数据表有 varchar 字段的时候。varchar 字段的真实数据占用的字节数会按照列的顺序逆序存放。
          2. NULL 值列表 如果存在允许 Null 值的列，则每个列对应 bit，bit 按照列的顺序逆序排列。 bit 为 1 则值为 Null，为 0 则值不为 Null。 Null 值列表必须用整数个字节的位表示，1 字节 8 bit，不足时则在高位补 0。
          3. 记录头信息
        2. 记录的真实数据
          1. 定义字段
          2. 隐藏字段
            1. row_id 若表未定义主键，每行会增加一个 6 bytes 的 row_id 列。
            2. trx_id 事务 id，标记生成行记录的事务。
            3. roll_pointer
            4. 记录上一个版本的指针。
      1. Compressed 、Dynamic 基于 Compact 轻微修改。 MySQL 5.7 以后默认使用 Dynamic。 对于行溢出采用完全溢出处理，行存储指针指向溢出页。
从数据页的角度看 B+树
  1. B+树的节点都是数据页
  1. 节点按照索引键大小排序，构成双向链表，便于范围查询。
  1. 非叶子节点存放目录项作为索引，叶子结点存放数据（对于根据二级索引构建的 B+树，叶子结点存放的是主键 id）
所以为字段创建索引的过程其实就是在创建并维护一颗 B+树。
数据页结构
  1. File Header记录页的信息
  1. Page Header 记录页的状态信息
  1. Infimun Records / Supremum Records每个数据页有两个虚拟的行记录用来限定记录的边界。
  1. User Records实际存储行记录的内容，以单向链表存在。
  1. Free Space
  1. Page Directory存放记录的相对位置。值得一提的是，B+树索引本身并不能找到具体的一条记录，能找到的是该记录所在的页。MySQL 把数据页载入内存，然后通过 Page Directory 二叉查找。
  1. File Tailer校验页的完整性
redo log file
记录 redo log。

其他存储引擎。

  1. NDB 存储引擎
    1. 集群存储引擎
    2. 采用 share nothing 的集群架构
    3. 数据全部放在内存中，可以将非索引数据放在磁盘上
  1. Memory 存储引擎
    1. 数据存放在内存，数据库重启或发生崩溃，表中数据小时。
    2. 默认使用哈希索引，而不是 B+树索引。
    3. MySQL 使用 Memory 存储引擎作为临时表来存放查询的中间结果集。 诸如排序操作或分组操作的临时数据、子查询或联合查询的中间结果。.
  1. Archive存储引擎
    1. 只支持INSERT和SELECT操作
    2. 使用zlib算法将数据行（row）进行压缩后存储，压缩比一般可达1∶10
    3. 使用行锁来实现高并发的插入操作，但是其本身并不是事务安全的存储引擎，其设计目标主要是提供高速的插入和压缩功能。
  1. MyISAM 存储引擎
    1. 表锁设计
    2. 不支持事务
    3. 支持全文索引

索引

索引是数据的目录。
我理解的索引即是 MySQL 为页创建并维护一个数据结构来实现快速高效的查询。

索引分类

按照数据结构分类
从二分查找树到 B+树
  1. 二分查找树
  1. AVL 树
  1. B 树
  1. B+树
  1. B+Tree Index 最大的优点在于查询效率很高，即使数据量很大的情况下，查询数据的磁盘 I/O 依然维持在 3~4 次。
  1. Hash Index 类似于 key-value，对于选定的列，MySQL 会去计算 Hash 值作为 key，value 则为对应行的位置 InnoDB 引擎支持的 Hash 索引是自适应的，由系统根据表的使用情况自动建立哈希索引，不能认为干预。
  1. Full-Text Index
按照物理存储分类
  1. Clustered Index 主键索引，也叫做聚簇索引。
  1. Secondary Index 辅助索引，也叫做二级索引。
按照字段特性分类
  1. 主键索引 一张表只能有一个主键索引。
  1. 唯一索引 索引列的值必须唯一，一张表可以有多个唯一索引。
  1. 普通索引
  1. 前缀索引 对字符类型字段的前几个字符建立的索引，而不是在整个字段类型上建立的索引。使用前缀索引的目的是减少索引占用的存储空间，提升查询效率。
  1. 联合索引 将多个索引合成一个索引，该索引就叫做联合索引。 联合索引的非叶子节点用定义索引的所有列作为 B+Tree 的 key 值
    1. 最左匹配原则 查询数据时按从左到右顺序匹配索引和比较查询。 如果不遵循此原则，联合索引将失效。 联合索引表现出全局有序和局部有序，前者指所有数据行按照最左侧列值进行全局排序，后者指在前缀级别相同的情况下，下一级表现出一定的顺序。
    2. 范围查询
    3. 回表、索引下推

索引创建

适用索引
  1. 字段有唯一性限制
  1. 经常用于 where 查询条件的字段
  1. 经常用于 group by 和 order by 的字段
不必索引
  1. WHERE 条件，GROUP BY，ORDER BY 里用不到的字段
  1. 字段中存在大量重复数据
  1. 表数据太少的时候，不需要创建索引
  1. 经常更新的字段不用创建索引

索引优化

  1. 前缀索引 使用前缀索引是为了减小索引字段的大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。 在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减少索引项的大小。
  1. 覆盖索引 使用覆盖索引即为需要查询的所有字段建立联合索引，这样在查询的时候可以直接从二级索引中查询得到需要的信息，而不需要通过回表去主键索引查询完整信息。
  1. 主键索引自增 使用自增主键，每次插入数据都是追加操作，效率很高。
  1. 索引设置 not null 索引存在 null 会导致优化器在做索引选择的时候更复杂。 null 会占用行的物理空间。
  1. 防止索引失效
    1. 左或者左右模糊匹配
    2. 查询条件对索引列做了计算、函数和类型转换操作
    3. 不遵循最左匹配原则
    4. where 字句中，or 前面的条件是索引列，后面的条件不是索引列。
EXPLAIN 分析
当在 select 语句前加上 EXPLAIN 命令，MySQL 将不会立刻执行查询并返回结果而是会返回一个关于查询如何被解析器优化和执行的详细报告。 这份报告可以帮助理解查询的的执行流程和可能的性能瓶颈。
  1. type 字段 描述扫描方式，扫描效率从低到高依次为
    1. All，全表扫描
    2. index，全索引扫描
    3. range，索引范围扫描
    4. ref，非唯一索引扫描
    5. eq_reF，唯一索引扫描
    6. const，唯一索引扫描
  1. Using index MySQL 扫描索引本身完成排序。 ORDER BY 满足两种情况会使用 Using index
    1. order by 使用索引最左前列
    2. 使用 where 字句与 order by 字句条件列组合满足索引最左前列
  1. Using filesort MySQL 不能使用使用索引完成排序，使用内存或磁盘来辅助排序。
    1. 数据量不大(参考值<256K)，MySQL 会将查询到的数据放入内存中名为 sort_buffer 的区域使用快排处理。
    2. 数据量比较大(参考值>=256K)，超越了 sort_buffer 。MySQL 会采用归并排序的思想，把要排序的数据划分为若干份在内存中排序后放入若干临时文件，最终对若干有序的文件合并排序。 这里涉及磁盘 I/O 和回表操作，会导致性能的下降。 可以通过一下手段优化：
      1. 增大缓存 配置参数 sort_buffer_size。
      2. 减小数据 MySQL 使用 rowid 来优化，但需要附加一次回表操作，而且若是优化过后的数据仍然太大依旧会使用 filesort。
      3. 覆盖索引 建立联合索引来使用索引覆盖。这是最有效的解决方式。
  1. Using rowid MySQL 只将必要的数据和主键 id 放入 sort_buffer 来减小数据量以尽量避免 filesort。 MySQL 会根据检索到的单行数据长度来判断是否使用 rowid 进行优化，单行长度大于 max_length_for_sort_data（默认 1024 字节）就会执行优化。
  1. Using temporary MySQL 使用位于内存表中的临时表来存储中间结果集，通常与 JOIN 操作有关。临时表通常由 MySQL 自动创建和管理。

锁和事务

锁机制

锁是数据库系统区别于文件管理系统的一个关键特性。
锁机制用于管理对共享资源的并发反问，数据库系统使用锁来提供数据库的完整性和一致性。
数据库中的锁分为两种 Lock 和 Latch。
Lock 的对象是事务，锁定的是数据库中的对象（Page、Row），并且一般仅在事务 commit 或 rollback 后进行释放。
Latch 的对象是线程，保护的是内存数据结构，持续时间和临界资源有关，主要有 mutex 和 rwlock。
接下来讨论的有关锁的概念都是指前者 Lock。
锁的分类
全局锁
加上全局锁整个数据库都会是只读状态。
表级锁
  1. 表锁 表级别的共享锁就是读锁，表级别的独占锁就是写锁。
  1. 意向锁 在对表中某些记录加共享锁或独占锁之前，需要先在表级别上加一个意向共享锁或意向排它锁。意向锁的目的是为了快速判断表里是否有记录被加锁。
  1. 元数据锁 元数据锁的目的是保护数据库的表结构，确保在执行 DDL 或 DML 操作时不会发生冲突或数据损坏。 DDL 操作加 MDL 写锁，DML 加 MDL 读锁。 MDL 锁在事务提交后才会释放，并且申请 MDL 锁的操作会形成一个写锁获取优先级高于读锁获取优先级的队列。 假设一个长事务获取了 MDL 读锁，同时又有一个事务试图获取 MDL 写锁被阻塞，就会阻塞后续该表的所有 DML 操作。
  1. 自增长锁 自增长锁是为了正确的分配自增长值。 在 InnoDB 的存储内存结构中，对每个含有自增长值的表都有一个 auto-increment counter 。 当对含有自增长计数器的表进行插入操作时，插入操作会锁定这个计数器并依据这个自增长的计数器+1 赋予自增长列。 有关锁算法参数影响：
    1. 传统锁模式：0（MySQL 5.7 以前） 获取表级锁，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的 SQL 语句后立即释放，可以提高并发性能。
    2. 连续锁模式：1（MySQL 5.7 开始）
      1. Simple Inserts 不使用表级锁，而是使用一个轻量级锁来生成自增值，而不是一直加锁直到插入完成。 但是如果其他事务持有自增长锁，页需要等待其他事物完成才能使用轻量级锁。
      2. Bulk Inserts 类似于传统模式。
    3. 交错锁模式：2（MySQL 8.0 开始） 不使用表级锁，并且多条语句可以同时执行。 这是最快和最具扩展性的模式，但是对于基于语句的 replication 和依赖 SQL 的 recovery 是不安全的。
行级锁
  1. Record Lock 单行记录上的锁。 Record Lock 总是会去锁住索引记录，倘若没有索引，就会使用隐式的主键来进行锁定。
  1. Gap Lock 锁定一个范围，但不包含记录本身
  1. Next-Key Lock 锁定一个范围，并且锁定记录本身
  1. 插入意向锁 事务在插入一条新记录的时候，需要判断插入位置是否被其他事务加了 Gap Lock 或者 Next-key Lock，如果有的话，插入操作就会产生阻塞，直到事务提交导致锁释放。 在此期间会生成一个插入意向锁，表明事务处于等待状态。 插入意向锁之间不会产生冲突，多个插入操作同时插入一个 gap 的时候无需等待，
锁的运用
select 语句不会对记录加锁。
  1. 一致性非锁定读 InnoDB 存储引擎通过 MVCC 读取当前执行时间数据库中行的数据。 如果读取的行正在执行修改操作，这时不会去等待行锁的释放，而是去读取行的一个快照数据。 非锁定读极大地提高了数据库的并发性，在事务隔离级别 Read Committed 和 Repeatable Read 下，InnoDB 默认使用一致性非锁定读，但是对快照数据的定义不完全相同。 Read Committed 总是读取被锁定行的最新一份快照数据。 Repeatable Read 总是读取事务开始时的行数据版本。
  1. 一致性锁定读 显式的对数据库进行读取进行加锁以保证数据逻辑一致性。 事务提交则锁释放，使用时需要手动提交事务。
    1. select ··· for update 对读取的行记录加一个写锁。
    2. select ··· lock in share mode 对读取的行记录加一个读锁。
update 和 delete 操作都会加行级独占锁。
加锁的对象是索引条目而不是表中的物理行。
锁的细节
阻塞
由于不同锁的兼容关系，某些时刻事务中的锁需要等待另一个事务中的锁释放它所占有的资源，这就是阻塞。
  1. 等待时间 MySQL 支持动态调整参数来设定阻塞最大等待时间。
  1. 超时处理 MySQL 通过调整参数来设定是否在等待超时时对事务 rollback，默认不 rollback。
Dead Lock
指两个或两个以上的事务在执行过程中因为争夺锁资源造成的一种互相等待的现象。
  1. timeout 被动的死锁处理方式。 该方式设置一个时间阈值，当两个事务互相等待时，等待时间超过设置阈值，其中一个事务 rollback 以便另一个事务能继续进行。但是无法选择回滚代价最小的事务。
  1. wait for graph 主动的死锁检测方式。 该方式要求数据库保存锁的的信息链表和事务等待链表，通过上述链表构造图，图中若存在回路则代表存在死锁。 在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则含有死锁，通常来说 InnoDB 选择代价较小的事务 rollback。
Lock Escalation
锁升级是指将当前锁的粒度降低。 InnoDB 不存在锁升级。因为其不是根据每个记录来产生行锁的，相反，其根据每个事务访问的每个页对锁进行管理，采用 BitMap 的方式。因此不管是一个事务锁住页中一个记录还是多个记录，其开销通常一致。 需要注意的是在 InnoDB 中存在的所谓的”退化“现象（从 Next-Key Lock）并不是锁升级，而是一种优化行为，即在使用 Record Lock 和 Gap Lock 就能避免幻读的场景下不使用 Next-Key

事务

数据库事务要求 ACID 特性。
原子性、隔离性和持久性是实现一致性的手段。
对于实际开发 Spring 会处理好底层细节，包括与数据库的交互，以及在必要时回滚或提交事务，使得开发者可以专注于业务逻辑的开发。
Atomicity
事务的原子性主要通过undo log 和 redo log 保证。
事务是数据库中的最小单位，当事务中的部分操作失败，需要撤销那些已经成功的操作来确保数据回到事务执行前的状态。
  1. undo log 记录事务更新前的行数据以实现撤销回退。
    1. rollback segment ： undo log segment ：undo page InnoDB 对 undo log 的管理采用段的方式。 默认存在于共享表空间的每个回滚段记录 1024 个 undo log segment，而在每个 segment 中进行 undo page 的申请。
      1. insert undo log & update undo log 存在于 undo page 中的 undo log 可以分为两类：
        1. insert undo log Insert 操作产生的 undo log。 事务可以根据 Insert undo log 撤销操作，而且因为插入操作只对事务本身可见，所以该 undo log 在事务提交后直接删除。
        2. update undo log Update 和 Delete 操作产生的 undo log。 事务可以根据此信息撤销对应操作，而且此数据实际上就是 MVCC 赖以实现的所谓行历史版本，因此不能在事务提交后删除。 提交时放入 undo log 链表（确保 undo log 可以被安全删除的数据结构）等待 purge 线程清理。
      2. rollback segment & transaction
        1. 事务只会使用一个回滚段，一个回滚段在同一时间可能服务于多个事务。
        2. 事务和回滚段的交互。
          1. 事务开始时，DBMS 负责分配回滚段并动态调节。
          2. 事务进行中，发生数据库数据修改（delete、update、insert）就复制原始数据到回滚段。 事务去 rollback segment 申请 undo page 写入 undo log。但是为一个事务分配一个页会造成空间浪费，所以就要考虑页的重用。 具体来讲就是当事务提交后，并不会立刻删除 undo page，而是判断 undo page 的使用空间是否小于 3/4，小于则代表可重用，于是页不会被回收，其他事务的 undo log 也就可以记录在此页。
          3. 事务提交时，InnoDB 做两件事
            1. 将 undo log 放入 history list 中，以供 purge 操作。 history list 按照事务提交的顺序组织 redo log。history 中不存储实际数据，而是存储指向对应 undo page 的引用。 Purge 时先从 history list 中寻找 undo log ，然后再充 undo page 中寻找 undo log。 这样设计避免大量的随机读取操作，使得 Purge 数据可以快速访问 undo log。
            2. 判断 undo page 重用性，可重用则分配给下一个事务。
          相关参数
          1. 可以通过参数设置 rollback segment 文件所在路径。
          1. 可以通过参数设置 rollback segment 个数，默认 128。
          1. 可以通过参数设置 rollback segment 文件数量，保证分配平均。
    2. information in rollback segment 可以判断存在于回滚段中存在三种类型的数据。
      1. uncommitted undo information 未提交的回滚数据。
      2. committed undo information 已经提交但未过期的回滚数据。这就是 mvcc 维护的行的历史版本。
      3. expired undo information 已经提交也过期的回滚数据。回滚段满会优先覆盖这部分数据。 这部分数据也是 purge 线程需要删除回收的数据。
  1. redo log
事务在 undo log segment 分配 undo page 写入 undo log 的过程属于物理修改，同样需要写入重做日志。
Isolation
并发产生问题
通过锁机制可以实现事务的隔离性要求，使得事务可以并发的工作，但是会带来三种问题。
  1. 脏读，指在不同事务下，当前事务可以读取到另外事务未提交的数据。
  1. 幻读 & 不可重复读 指在同一事务下，连续执行两次 SQL 语句得到不同的结果。 范围查询到新的行为幻读，等值查询到新的值为不可重复读。
  1. 丢失更新 指一个事务的更新操作会被另一个事务的更新操作覆盖，从而导致数据不一致。
事务隔离级别
  1. 读未提交 Read Uncommitted 事务未提交时，他做的变更能被其他事务看到。发生脏读。
  1. 读已提交 Read Committed 事务提交之后，他做的变更才能被其他事务看到。发生幻读。
  1. 可重复读 Repeatable Read 事务执行过程中看到的数据始终保持一致。 MySQL InnoDB 默认隔离级别。很大程度上避免幻读现象发生。 对于快照读，mvcc 解决幻读和不可重复读，对于当前读，RR 默认对读操作使用间隙锁，彻底解决幻读。
  1. 串行化 Serializable 避免幻读，但是性能损失不能接受。
事务隔离实现
  1. Read View InnoDB 用来确定数据行版本可见性的数据结构。 RC 和 RR 都是通过 Read View 来实现的，他们的区别在于创建 Read View 的时机不同。 RC 在每个语句执行前都会重新生成，这使得事务可以看到新的已提交的历史版本。 RR 是在启动事务时生成并在整个事务期间使用，这使得事务事务看到的数据始终一致。
    1. creator_trx_id 创建 Read View 的事务的事务 id
    2. m_ids 创建 Read View 时当前数据库中“活跃且未提交”的事务 id 列表。
    3. min_trx_id 创建 Read View 时当前数据库中活跃且未提交的事务中最小事务的事务 id
    4. max_trx_id 创建 Read View 时当前数据库中应该给下一个事务的 id 值。
  1. 隐藏列
    1. trx_id 标志最后一次修改该行数据的事务。
    2. roll_pointer 追踪行数据的上一个版本。 指向 undo log 中的一条记录，构成历史版本链表。
  1. MVCC
    1. 通过回滚日志和回滚指针构建历史版本链
    2. 通过 ReadView 确定历史版本可见性。
    3. MVCC 保证读取的是第一个对当前事务可见的版本。
Durability
主要实现崩溃恢复的功能。
Write Ahead Logging
MySQL 的写操作是先写日志，再写磁盘。 写磁盘是随机写，写日志是顺序写，顺序写性能更高。 还可以根据 redo log 来重做数据。
Redo Log
  1. redo log form 重做日志记录了在某个数据页做了什么修改。 “对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新” redo_log_type + space + page_no + redo_log_body
  1. redo log buffer MySQL 通过先写缓存再写磁盘的方式写入日志文件。 缓存写磁盘可以通过参数控制写入策略。
    1. 0 事务提交时写入redo log buffer，由后台线程（Master Thread）间断的持久链接化到磁盘。 减少磁盘 I/O，性能更高，但是可能丢失 1s 的数据。
    2. 1 事务提交时直接写入磁盘。 数据安全性高，性能较弱。
    3. 2 事务提交时写入 Page Cache，再由操作系统持久化到磁盘。 折中方案，性能和数据安全性介于二者之间。对于数据安全性而言，只要操作系统不宕机就不会丢失数据。
  1. redo log file
    1. redo log block
    2. redo log group redo log group 包含若干 redo log file，以循环写的方式写入。
      1. write pos 标记最新写入日志文件的重做日志的位置。
      2. check point 标记重做日志缓冲区中已经写入磁盘的日志记录的位置。
        3. 一旦二者重合则代表 MySQL 不能执行新的更新操作即阻塞，MySQL 触发 Checkpoint 操作将缓存池中的脏页刷新到磁盘推进 check point 指针。
Checkpoint 机制
Checkpoint 机制是 WAL 和 redo log 的优化技术。 一可以优化数据库重做的流程，二可以平衡磁盘 I/O 和内存。
  1. check point 指针，标记已经写入磁盘的重做记录位置。
    1. Log Sequence Number 日志序列号，用来标记版本。 Page、redo log 和 Checkpoint 都有 LSN。
      1. 创建日志 LSN1
      2. 日志刷盘 LSN2 标记页修改已经写入日志
      3. 数据刷盘 LSN3 标记当前最旧的脏页数据
      4. 写作CKP LSN4 已经写入 redo log 中作为check point 指针
    2. 数据库重做时，InnoDB 扫描 redo log，从 check point 指针的位置开始 apply redo log 到 Buffer Pool。 对于 Buffer Pool 中 LSN 小于 CKP 的数据页 apply redo log直到它们的 LSN 等于 CKP，而其他的页则不用操作。
  1. CheckPoint 操作，将缓存中的脏页写入磁盘。
    1. Sharp CheckPoint 发生在数据库关闭的时候，会将所有脏页写入磁盘。 这是默认的工作方式。
    2. Fuzzy CheckPoint 发生在数据库运行的时候，刷新一部分脏页。
      1. Master Thread Checkpoint 定期刷新。
      2. Flush_LRU_List Checkpoint Buffer Pool 中 LRU 空闲列表不足触发。
      3. Asyns / Sync Flush Checkpoint redo log file 不足触发。
      4. Dirty Page too much Checkpoint 缓存池中脏页达到一定比例触发。
Double Write
在写入磁盘的过程中保留页的副本，以避免宕机带来页的丢失。
Consistency
一致性是目标
事务分类
Flat Transactions
扁平事务（Flat Transaction）是事务类型中最简单的一种，但在实际生产环境中，这可能是使用最为频繁的事务。 在扁平事务中，所有操作都处于同一层次，其由BEGIN WORK开始，由COMMIT WORK 或ROLLBACK WORK结束，其间的操作是原子的，要么都执行，要么都回滚。因此扁平事务是应用程序成为原子操作的基本组成模块。 扁平事务的主要限制是不能提交或者回滚事务的某一部分，或分几个步骤提交。带有保存点的扁平事务（Flat Transactions with Savepoint） 除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态。这是因为某些事务可能在执行过程中出现的错误并不会导致所有的操作都无效，放弃整个事务不合乎要求，开销也太大。 保存点（Savepoint）用来通知系统应该记住事务当前的状态，以便当之后发生错误时，事务能回到保存点当时的状态。 对于扁平的事务来说，其隐式地设置了一个保存点。然而在整个事务中，只有这一个保存点，因此，回滚只能回滚到事务开始时的状态。 保存点用SAVE WORK函数来建立，通知系统记录当前的处理状态。 当出现问题时，保存点能用作内部的重启动点，根据应用逻辑，决定是回到最近一个保存点还是其他更早的保存点。
Chained Transactions
链事务。 链事务的思想是在提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式地传给下一个要开始的事务。 注意，提交事务操作和开始下一个事务操作将合并为一个原子操作。这意味着下一个事务将看到上一个事务的结果，就好像在一个事务中进行的一样。 链事务与带有保存点的扁平事务不同的是，带有保存点的扁平事务能回滚到任意正确的保存点。而链事务中的回滚仅限于当前事务，即只能恢复到最近一个的保存点。对于锁的处理，两者也不相同。链事务在执行COMMIT后即释放了当前事务所持有的锁，而带有保存点的扁平事务不影响迄今为止所持有的锁。
Nested Transactions
嵌套事务。
Distributed Transactions
分布式事务。 InnoDB 引擎提供了对 XA 事务的支持，并通过 XA 事务来支持分布式事务实现。 XA 事务允许不同数据库之间的分布式事务，InnoDB 使用分布式事务时隔离级别必须为 SERIALIZABLE。
分布式事务指多个独立事务资源参与到全局事务中。事务资源通常是关系型数据库，全局事务要求参与其中的是要么全部提交要么全部回滚，这对于单机事务原有的 ACID 有了更高的要求。
  1. 外部 XA 分布事务
    1. Resource Manager
    2. Transaction Manager
    3. Application Program
  1. 内部 XA 分布事务
 

日志

redo log

存储引擎生成。 redo log 是物理日志，记录数据页做了什么修改，用于故障恢复。

undo log

存储引擎生成。 undo log 存在于表空间文件中，记录行的历史版本，用于实现多版本并发控制。

bin log

Server 生成。 bin log 是逻辑日志，记录数据库表结构变更和表数据修改，用于备份恢复和主从复制。

两阶段提交

事务提交后，redo log 和 bin log 都要持久化到磁盘，但是两者逻辑独立，可能出现半成功状态，这就会出现日志之间逻辑不一致。 MySQL 使用两阶段提交来解决。 两阶段提交其实就是保证多个逻辑操作要么全成功，要么全失败。
  1. Prepare 将 xid 写入 redo log。 设置 redo log 状态为 prepare。 redo log 持久化。
  1. Commit 将 xid 写入 bin log bin log 持久化 设置 redo log 状态为 commit 标记完全提交的 redo log 持久化
这样的提交方式下，系统存在三个崩溃点
1. redo log 持久化
这里崩溃的话，事务视作未提交，不影响一致性。 2. bin log 持久化
这里崩溃的话，数据库重启后会比较 redo log 的 xid 和 bin log 的 xid 发现 bin log 失败，并且根据 redo log 中的信息回滚事务。 3. redo log 持久化
这里崩溃的话，数据库重启后会根据 redo log 中标记的 prepare 信息补全 commit 记录。
这样就可以保证日志文件的数据一致性，但会带来性能的损失，这主要是因为两阶段提交的磁盘 I/O 次数很高以及对两阶段加锁锁粒度过大。
可以考虑使用 group commit 来优化磁盘 I/O。当有多个事务提交的时候，会将多个 bin log 操作合并成一个从而减少磁盘 I/O。同时不再锁住事务提交全过程而是对每个阶段引入的队列进行保护，减小锁的粒度。
所以 Prepare 阶段未有变化，而 Commit 阶段可以拆分成三个过程，在此过程中每个阶段维护一个队列，第一个事务会成为 Leader，后续事务为 Follower：
1. Flush（redo log group commit）
Leader 获取队列事务组，并将同组事务 redo log 写入磁盘，bin log 写入文件系统缓存。 2. Sync（bin log group commit）
等待一段时间以组合更多事务的 bin log ，再将 bin log 从文件系统写入磁盘。
事务数量到达设置的最大值会立刻开始写入磁盘。 3. Commit
设置 redo log 状态为 commit。

备份和恢复

数据库优化

数据库命名规范

  1. 所有数据库对象名称必须使用小写字母并用下划线分割
  1. 所有数据库对象名称禁止使用 MySQL 保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）
  1. 数据库对象的命名要能做到见名识意，并且最好不要超过 32 个字符
  1. 临时库表必须以 tmp_ 为前缀并以日期为后缀，备份表必须以 bak_ 为前缀并以日期 (时间戳) 为后缀
  1. 所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）

基本设计规范

  1. 数据库和表的字符集统一使用 UTF8
  1. 所有表和字段添加注释
  1. 控制单表数据量大小
  1. 谨慎使用 MySQL 分区表
  1. 经常使用的列放到一个表中
  1. 禁止在表中预留字段
  1. 禁止在表中存储文件

字段设计规范

  1. 优先选择符合存储需要的最小数据类型 存储字节越小，占用空间越少，性能越好。
    1. 某些字符串转换成数字类型存储
    2. 非负型数据，优先使用无符号整型存储
    3. 小数值类型有限使用 TINYINT
  1. 避免使用 TEXT 和 BLOB 类型
  1. 避免使用 ENUM 类型 修改 ENUM 值需要使用 ALTER 语句； ENUM 类型的 ORDER BY 操作效率低，需要额外操作； ENUM 数据类型存在一些限制比如建议不要使用数值作为 ENUM 的枚举值。
  1. 尽可能把所有列定义为 NOT NULL
  1. 不要用字符串存储日期
    1. DATETIME
    2. TIMESTAMP
    3. 数值型时间戳
  1. 同财务相关的金额数据必须使用 decimal 类型
  1. 单表不要过多字段

SQL 开发规范

  1. 尽量不在数据库做运算，复杂运算转移到业务应用完成
  1. 使用 select <字段列表>查询
  1. 禁止使用不含字段列表的 insert 语句
  1. 避免数据类型的隐式转换
  1. 避免使用子查询

数据库架构优化

读写分离
主要应对数据库读并发。将对数据库的读写操作分散到不同的数据库节点。
部署数据库
一般情况下，选择一主多从。
一台主数据库负责写操作，其他从数据库负责读操作。
  1. 主从同步 Master 和 Slave 之间会进行复制以保证数据一致性。主要依靠 bin log 来实现。 Master 将数据变化写入 binlog，Slave 连接 Master 并创建线程向主库请求 binlog，Master 会创建 binlog dump 线程发送 binlog，Slave 接受并将其写入 relay log，然后执行 relay log。
  1. 同步延迟 replication 会存在延迟，
    1. 避免延迟 强制将读请求路由到 Master。可以将那些必须获取最新数据的读请求交给 Master 处理。
    2. 延迟读取 延迟之后再进行读取。对于数据敏感的场景，可以在完成写请求后，避免立即进行读请求操作。比如支付成功后跳转支付成功页面，当点击返回后才去查询。
  1. 请求分发
    1. 代理 在应用和数据之间增加代理层。应用程序的所有数据交给代理层处理，代理层负责分离读写请求，并将他们路由到对应数据库。 1. MySQL Router、 2. Atlas 3. MaxScale 4. MyCa
    2. 组件方式 引入三方组件来帮助我们读写请求。 1. sharding-jdbc
 
分库分表
主要解决数据库存储压力。
分库就是将数据库的数据分散到不同数据库中，分为垂直分库和水平分库。垂直分库指把单一数据库按照业务进行划分，水平分库指把同一个表按一定规则拆分到不同的数据库上。
分表就是对表单的数据进行拆分，分为水平拆分和垂直拆分。垂直分表是对数据表列的拆分，把一张列比较多的表拆分为多张表；水平分表是对数据表行的拆分，把一张行较多的表拆分为多张表。
应用条件
  1. 单表数据千万级别以上导致数据库读写速度比较缓慢。 分库分表的成本太高，如非必要不采用。数据千万级并不能作为唯一参考，还是要综合表的结构具体情况具体分析。而且千万级也是一个不严谨的概念，更多细节。
  1. 数据库中的数据占用空间过大导致备份时间越来越长。
  1. 应用的并发量太大（应优先考虑其他优化方法，而非分库分表）
实现细节
分片算法
主要解决数据被水平分片之后的存储位置选择。
  1. 哈希分片、一致性哈希分片 哈希分片求指定分片键的哈希，然后根据哈希值确定数据应被放置在哪个表中。哈希分片比较适合随机读写的场景，不太适合经常需要范围查询的场景。哈希分片可以使每个表的数据分布相对均匀，但对动态伸缩（例如新增一个表或者库）不友好。 一致性哈希分片将哈希空间组织成一个环形结构，将分片键和节点（数据库或表）都映射到这个环上，然后根据顺时针的规则确定数据或请求应该分配到哪个节点上，解决了传统哈希对动态伸缩不友好的问题
  1. 范围分片 范围分片按照特定的范围区间（比如时间区间、ID 区间）来分配数据。范围分片适合需要经常进行范围查找且数据分布均匀的场景，不太适合随机读写的场景（数据未被分散，容易出现热点数据的问题）。
  1. 映射表分片 映射表分片使用一个单独的表（称为映射表）来存储分片键和分片位置的对应关系。映射表分片策略可以支持任何类型的分片算法，如哈希分片、范围分片等。映射表分片策略是可以灵活地调整分片规则，不需要修改应用程序代码或重新分布数据。不过，这种方式需要维护额外的表，还增加了查询的开销和复杂度。
分片键选择
一般来说，分片键应该具备以下特点： 具有共性，即能够覆盖绝大多数的查询场景，尽量减少单次查询所涉及的分片数量，降低数据库压力； 具有离散性，即能够将数据均匀地分散到各个分片上，避免数据倾斜和热点问题； 具有稳定性，即分片键的值不会发生变化，避免数据迁移和一致性问题； 具有扩展性，即能够支持分片的动态增加和减少，避免数据重新分片的开销。
引入产生改变
  1. join 操作 同一个数据库中的表分布在了不同的数据库中，导致无法使用 join 操作。这样就导致我们需要手动进行数据的封装，比如你在一个数据库中查询到一个数据之后，再根据这个数据去另外一个数据库中找对应的数据。不过，很多大厂的资深 DBA 都是建议尽量不要使用 join 操作。因为 join 的效率低，并且会对分库分表造成影响。对于需要用到 join 操作的地方，可以采用多次查询业务层进行数据组装的方法。不过，这种方法需要考虑业务上多次查询的事务性的容忍度。
  1. 事务问题 同一个数据库中的表分布在了不同的数据库中，如果单个操作涉及到多个数据库，那么数据库自带的事务就无法满足我们的要求了。这个时候，我们就需要引入分布式事务了。关于分布式事务常见解决方案总结，网站上也有对应的总结：https://javaguide.cn/distributed-system/distributed-transaction.html 。
  1. 分布式 ID 分库之后， 数据遍布在不同服务器上的数据库，数据库的自增主键已经没办法满足生成的主键唯一了。分布式 ID 介绍&实现方案总结。
    1. 基本要求 全局唯一、高性能、高可用、方便易用 安全、有序递增
    2. 解决方案
      1. 数据库
        1. 数据库主键自增
        2. 数据库号段模式
        3. Redis
        4. MongoDB
      2. 具体算法
        1. UUID（Universally Unique Identifier） UUID 包含 32 个 16 进制数字（8-4-4-4-12）。UUID 可以保证唯一性，因为其生成规则包括 MAC 地址、时间戳、名字空间（Namespace）、随机或伪随机数、时序等元素，计算机基于这些规则生成的 UUID 是肯定不会重复的。可以做到全局唯一、性能出色、简单易用，但是存储空间消耗比较大。不同 UUID 版本对应的生成规则不同。
          1. Version1： 根据时间和节点 ID（通常是 MAC 地址）生成
          2. Version2： 根据标识符（通常是组或用户 ID）、时间和节点 ID 生成
          3. Version 3、5：通过散列（hashing）名字空间（namespace）标识符和名称生成
          4. Version 4：UUID 使用随机性或伪随机性生成
            5. UUID.randomUUID() jdk 提供了生成 UUID（v4） 的方法。
        2. Snowflake 由 64 bit 的二进制数字组成，这 64bit 的二进制被分成了几部分，每一部分存储的数据都有特定的含义。
            • sign(1bit):符号位（标识正负），始终为 0，代表生成的 ID 为正数。
            • timestamp (41 bits):一共 41 位，用来表示时间戳，单位是毫秒，可以支撑 2 ^41 毫秒（约 69 年）
            • datacenter id + worker id (10 bits):一般来说，前 5 位表示机房 ID，后 5 位表示机器 ID（实际项目中可以根据实际情况调整）。这样就可以区分不同集群/机房的节点。
            • sequence (12 bits):一共 12 位，用来表示序列号。 序列号为自增值，代表单台机器每毫秒能够产生的最大 ID 数(2^12 = 4096),也就是说单台机器每毫秒最多可以生成 4096 个 唯一 ID。实际项目中我们一般会对 Snowflake 进行改造，最常见的就是在算法生成的 ID 中加入业务类型信息。
      3. 开源框架 1. UidGenerator（百度） 2. Leaf（美团）《Leaf——美团点评分布式 ID 生成系统》 3. Tinyid（滴滴）
      4. 跨库聚合查询问题 分库分表会导致常规聚合查询操作，如 group by，order by 等变得异常复杂。这是因为这些操作需要在多个分片上进行数据汇总和排序，而不是在单个数据库上进行。 为了实现这些操作，需要编写复杂的业务代码，或者使用中间件来协调分片间的通信和数据传输。 这样会增加开发和维护的成本，以及影响查询的性能和可扩展性。
冷热分离
数据冷热分离是指根据数据的访问频率和业务重要性，将数据分为冷数据和热数据，冷数据一般存储在存储在低成本、低性能的介质中，热数据高性能存储介质中。 优点是热数据的查询性能得到优化（用户的绝大部分操作体验会更好）、节约成本（可以冷热数据的不同存储需求，选择对应的数据库类型和硬件配置，比如将热数据放在 SSD 上，将冷数据放在 HDD 上）缺点是系统复杂性和风险增加（需要分离冷热数据，数据错误的风险增加）、统计效率低（统计的时候可能需要用到冷库的数据）
定义冷数据和热数据
热数据是指经常被访问和修改且需要快速访问的数据，冷数据是指不经常访问，对当前项目价值较低，但需要长期保存的数据。
  1. 时间维度区分
      2. ：按照数据的创建时间、更新时间、过期时间等，将一定时间段内的数据视为热数据，超过该时间段的数据视为冷数据。例如，订单系统可以将 1 年前的订单数据作为冷数据，1 年内的订单数据作为热数据。这种方法适用于数据的访问频率和时间有较强的相关性的场景。
  1. 访问频率区分
      2. ：将高频访问的数据视为热数据，低频访问的数据视为冷数据。例如，内容系统可以将浏览量非常低的文章作为冷数据，浏览量较高的文章作为热数据。这种方法需要记录数据的访问频率，成本较高，适合访问频率和数据本身有较强的相关性的场景。
冷数据迁移与存储
  1. 迁移
    1. 业务层代码实现 当有对数据进行写操作时，触发冷热分离的逻辑，判断数据是冷数据还是热数据，冷数据就入冷库，热数据就入热库。这种方案会影响性能且冷热数据的判断逻辑不太好确定，还需要修改业务层代码，因此一般不会使用。
    2. 任务调度
        3. 可以利用 xxl-job 或者其他分布式任务调度平台定时去扫描数据库，找出满足冷数据条件的数据，然后批量地将其复制到冷库中，并从热库中删除。这种方法修改的代码非常少，非常适合按照时间区分冷热数据的场景。
    3. 监听数据库的变更日志 binlog
        4. 将满足冷数据条件的数据从 binlog 中提取出来，然后复制到冷库中，并从热库中删除。这种方法可以不用修改代码，但不适合按照时间维度区分冷热数据的场景。
  1. 存储 冷数据的存储要求主要是容量大，成本低，可靠性高，访问速度可以适当牺牲。
      2. 冷数据存储方案：
      • 中小厂 直接使用 MySQL/PostgreSQL 即可（不改变数据库选型和项目当前使用的数据库保持一致），比如新增一张表来存储某个业务的冷数据或者使用单独的冷库来存放冷数据（涉及跨库查询，增加了系统复杂性和维护难度）
      • 大厂 Hbase（常用）、RocksDB、Doris、Cassandra
      如果公司成本预算足的话，也可以直接上 TiDB 这种分布式关系型数据库，直接一步到位。TiDB 6.0 正式支持数据冷热存储分离，可以降低 SSD 使用成本。使用 TiDB 6.0 的数据放置功能，可以在同一个集群实现海量数据的冷热存储，将新的热数据存入 SSD，历史冷数据存入 HDD。
深度分页
  • 传统分页通过 pageNum 和 pageSize 计算偏移量 OFFSET，直接跳过前 N 条数据，但是需要扫描，导致深度分页性能急剧下降，如果查询列是二级索引，连带的回表操作也会影响性能。
    • 使用子查询减少回表 & 使用 INNER JOIN 减少回表 通过二级索引树查询到满足条件的主键 ID，再走主键索引查询
  • 游标分页通过上一页的最后一个记录的唯一标识定位下一页的起始位置，无需扫描前 N 条数据，始终扫描固定行数。 性能稳定，无论分页深度如何查询时间固定；适应动态数据，容忍数据插入删除，仅影响当前页结果，适合无限滚动加载，如社交媒体，聊天记录。 但是无法跳页，需要维护游标状态。

写在最后

2024.9.23 初版
总结了我迄今为止了解到的所有有关 MySQL 的知识。
包括 MySQL 基本使用、MySQL 基本架构（InnoDB 引擎相关）及其相关实现
缺少的部分仍然存在，这也指明了有关于 MySQL 的下一个学习方向，即：
MySQL 优化策略 备份和恢复 & 索引优化
MySQL 备份和恢复 备份和恢复
MySQL 分布式部署 1.5事务分类
这需要对基础知识的消化和实践才能达到，当然也有书籍《高性能 MySQL》。
 
上一篇
Redis
下一篇
JUC
目录