目录

  • 基础架构
  • 日志
  • 事务
  • 类型
  • 索引
  • 优化

SQL 执行过程

基础架构

SQL 执行过程
MySQL 主要分为 Server 层和存储引擎层：

  • Server 层：所有跨存储引擎的功能都在这一层实现，如存储过程、触发器、视图，函数等，还有一个通用的 binlog 日志模块。

    查询缓存在 MySQL 8.0 后被移除，因为缓存失效在实际业务场景中可能会非常频繁，如果对一个表更新，这个表上的所有的查询缓存都会被清空。
  • 存储引擎：主要负责数据的存储和读取，采用可替换的插件式架构，支持 InnoDB、MyISAM 等多个存储引擎，其中 InnoDB 自带 redo log 日志模块。

查询语句

select * from tb_student where A.age='18' and A.name='张三';
  • 先检查该语句是否有权限，如果没有权限直接返回错误信息；如果有，以这条 SQL 语句为 key 在内存中查询是否有缓存（MySQL 8.0 以前），无缓存则执行下一步。
  • 通过分析器进行词法分析，提取 SQL 语句的关键元素，如这个语句是 select，查询的表名、列，以及查询条件等。然后判断是否有语法错误。

  • 接下来优化器会根据自己的优化算法选择其所认为执行效率最高的方案（有时不一定最好），例如上面的 SQL 可以有两种执行方案：

    • a. 先查询表中姓名为“张三”的学生，再判断年龄是否是 18 岁。
    • b. 先找出学生中年龄为 18 岁的，再查询姓名为“张三”的学生。

  • 确认了执行计划后，进行权限校验，如果没有权限就会返回错误信息，否则调用数据库引擎接口，返回引擎的执行结果。

    Server 层每从存储引擎读到一条记录就会发送给客户端，之所以客户端是直接显示所有记录的，是因为客户端是等查询语句完成后才会显示。

更新语句（两阶段提交）

update tb_student A set A.age='19' where A.name='张三';

在 InnoDB 引擎下，这个语句的执行流程如下：

  • 先通过 where 条件查询到张三这条数据，如果该行数据不在 InnoDB Buffer Pool（内存）中，则从磁盘加载对应的数据页。
  • InnoDB 修改内存中的该行数据，同时生成 undo log（更新前的值，用于回滚）。
  • WAL（Write-Ahead Logging）机制：InnoDB 写入 redo log（保证宕机后可恢复数据），进入 prepare 状态，并通知执行器准备提交事务。
  • 执行器记录 binlog（逻辑日志，用于主从复制、数据恢复），记录该语句。
  • 执行器调用 InnoDB 引擎提交 redo log，将其改为 commit 状态，更新完成。

 
两阶段提交
上述过程中，redo log 的写入拆成了两个步骤 prepare 和 commit。
两阶段提交

  • 写入 binlog 时发生异常时：MySQL 根据 redo log 日志恢复数据时，发现 redo log 还处于 prepare 阶段，且没有对应 binlog 日志，就会回滚该事务。
  • redo log 在 commit 阶段发生异常时：虽然 redo log 处于 prepare 状态，但是能通过事务 id 找到对应的 binlog 日志，所以 MySQL 认为是完整的，就会提交事务。

日志

binlog

MySQL 中的逻辑日志，用于记录语句的原始逻辑。数据备份、主从复制需要依靠 binlog 来同步数据。它有三种格式：

  • statement：SQL 语句原文，但 update_time=now() 会获取当前系统时间。
    binlog statement format
  • row：update_time=now() 变成了具体的时间。
    binlog row format
  • mixed：前两者的混合，因为 row 更占用空间，恢复与同步时会更消耗 IO 资源。

 
写入机制
binlog 写入机制

  • 一个事务的 binlog 不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为 binlog cache。
  • 单个线程 binlog cache 的大小可以由参数 binlog_cache_size 控制，如果存储内容超过了这个参数，就要暂存到磁盘。

  • write 和 fsync 的时机，可以由参数 sync_binlog 控制。

    在出现 IO 瓶颈的场景里，将 sync_binlog 设置成较大的值可以提升性能。
    0：每次提交事务都只 write，由系统自行判断什么时候执行 fsync 。
    1：每次提交事务都会执行 fsync ，防止宕机时 cache 中 binlog 丢失。
    N(N>1)：每次提交事务都 write，但累积 N 个事务后才 fsync。

redo log

InnoDB 特有的物理日志，记录“在某个数据页上做了什么修改”。MySQL 实例宕机重启时，InnoDB 会使用 redo log 恢复数据，来保证数据的持久性与完整性。
redo log
MySQL 中查询一条记录，会从硬盘把一页的数据加载出来，将数据页放入 Buffer Pool 中。后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，从而减少硬盘 IO 开销。更新数据的时候，就直接在 Buffer Pool 里更新。然后会把“在某个数据页上做了什么修改”记录到 redo log buffer 里，接着刷盘到 redo log 文件里。

为什么不把修改后的数据页直接刷盘？

  • 数据页大小是 16KB ，刷盘比较耗时，每次可能就修改了数据页里的几 Byte 数据，没有必要把完整的数据页刷盘；如果是写 redo log，一行记录可能就占几十 Byte，只包含表空间号、数据页号、磁盘文件偏移量、更新值。
  • 数据页刷盘是随机写，因为一个数据页可能在硬盘的随机位置，性能很差；redo log 是顺序写，刷盘速度很快。

 
InnoDB 将 redo log 刷到磁盘上有几种情况：

  • 事务提交时：通过innodb_flush_log_at_trx_commit参数控制。

    0：每次事务提交时不进行刷盘操作。这种方式性能最高，但是也最不安全，因为如果 MySQL 挂了或宕机了，可能会丢失最近 1 秒内的事务。
    1：每次事务提交时都将进行刷盘操作。这种方式性能最低，但是也最安全，因为只要事务提交成功，redo log 记录就一定在磁盘里，不会有任何数据丢失。
    2：每次事务提交时都只把 log buffer 里的 redo log 内容写入 page cache（文件系统缓存）。这种方式的性能和安全性都介于前两者中间。
  • buffer 空间不足时：redo log 占 redo log buffer 总容量的约一半时。
  • 事务日志缓冲区满：InnoDB 使用一个事务日志缓冲区（transaction log buffer）来暂时存储事务的重做日志条目。当缓冲区满时，会触发日志的刷新，将日志写入磁盘。
  • Checkpoint：InnoDB 定期会执行检查点操作，将内存中的脏数据（已修改但尚未写入磁盘）刷新到磁盘，并且会将相应的 redo log 一同刷新，以确保数据的一致性。
  • 后台刷新线程：InnoDB 启动了一个后台线程，负责周期性（每隔 1 秒）地将脏页刷新到磁盘，并将相关的 redo log 一同刷新。
  • 正常关闭服务器：MySQL 关闭的时候，redo log 都会刷入到磁盘里去。

undo log

undo log 属于逻辑日志，记录的是 SQL 语句，比如事务执行一条 DELETE 语句，那 undo log 就会记录一条相对应的 INSERT 语句。同时，undo log 的信息也会被记录到 redo log 中，因为 undo log 也要实现持久性保护。当执行事务过程中出现错误或者需要执行回滚操作的话，MySQL 可以利用 undo log 将数据恢复到事务开始之前的状态。

undo log 采用 segment（段）的方式来记录，便于管理多个并发事务的回滚需求。事务开始时，需要为其分配一个 rollback segment（回滚段），每个 rollback segment 有 1024 个 undo log segment（undo 日志段），每个 undo 操作在记录的时候占用一个 undo log segment。

rollback segment header（通常在回滚段的第一个页）负责管理 rollback segment ，其中包含一个 history list。undo-log 本身是会被清理的，INSERT 操作在事务提交后就可以清除掉了（因为其他事务不可能依赖它的历史版本），UPDATE / DELETE 操作在事务提交后不会立即删除，而是加入 history list，这个列表使得后台线程 purge 能够找到并清理已经提交的事务的 undo log 记录。

事务

事务隔离级别

  • READ-UNCOMMITTED（读取未提交） ：最低的隔离级别，允许读取尚未提交的数据变更。（脏读、幻读、不可重复读）
  • READ-COMMITTED（读取已提交） ：允许读取并发事务已经提交的数据。（幻读、不可重复读）
  • REPEATABLE-READ（可重复读） ：InnoDB 默认，对同一字段的多次读取结果都是一致的，除非被事务本身所修改。（幻读）
  • SERIALIZABLE（可串行化） 最高的隔离级别，完全服从 ACID 的隔离级别。所有事务逐个执行，事务之间不可能产生干扰。

ACID

。。。

多版本并发控制（MVCC）

MVCC（Multi-Version Concurrency Control）通过创建数据的多个版本和使用快照读取来实现并发控制，用于在多个并发事务同时读写数据库时保持数据的一致性和隔离性。读操作使用旧版本数据的快照，写操作创建新版本，并确保原始版本仍然可用。

1、读操作（ SELECT ）：

  • 如果要读取的数据行有多个版本，事务会选择不晚于其开始时间的最新版本，确保只读取在它开始之前已经存在的数据。
  • 事务读取的是快照数据，因此其他并发事务对数据行的修改不会有影响。

 
2、写操作（ INSERT、UPDATE、DELETE ）：

  • 事务会为要修改的数据行创建一个新版本，并将修改后的数据写入新版本。
  • 新版本的数据会带有当前事务的版本号，以便其他事务能够正确读取相应版本的数据。
  • 原始版本仍然存在，供其他事务使用快照读取，保证不受当前事务的写操作影响。
  • 为了防止数据库无限增长，MVCC 会定期版本回收，删除不再需要的旧版本数据。

 
3、事务提交和回滚：

  • 当一个事务提交时，它所做的修改将成为数据库的最新版本，并且对其他事务可见。
  • 当一个事务回滚时，它所做的修改将被撤销，对其他事务不可见。

 
一致性非锁定读：例如上述 InnoDB 多版本控制的方式（快照读）。

锁定读（当前读）：读取数据的最新版本，会对读取到的记录加锁。

# 加共享锁（S锁）
select ... lock in share mode
# 加排它锁（X锁）
select ... for update/insert/delete

InnoDB 中的实现

MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。

隐藏字段：InnoDB 为每行数据添加了三个隐藏字段：

  • DB_TRX_ID：最后一次插入或更新该行的事务 id。
  • DB_ROLL_PTR：回滚指针，指向该行的 undo log ；如果该行未被更新，则为空。
  • DB_ROW_ID：如果该表无主键且没有唯一非空索引，会使用该 id 来生成聚簇索引。

 
Read View：主要是用作可见性判断。

class ReadView {
private:
    trx_id_t m_low_limit_id;   // 大于等于该 ID 的事务均不可见
    trx_id_t m_up_limit_id;    // 小于该 ID 的事务均可见
    trx_id_t m_creator_trx_id; // 创建该 Read View 的事务 ID
    trx_id_t m_low_limit_no;   // 事务 Number，小于该 Number 的 Undo Logs 可被 Purge
    ids_t m_ids;               // 创建 Read View 时的活跃事务列表，不可见
    bool m_closed;             // 标记 Read View 是否关闭
}

undo log：当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现非锁定读。

 
RC 下不可重复读的根本原因：RC 和 RR 生成 Read View 的时机不同。

  • RC：事务中每次 SELECT 前都生成一个 Read View。
  • RR：只在事务开始后第一次 SELECT 前生成一个 Read View。

 
 
RR 下幻读的根本原因：UPDATE 或 for update 触发的当前读操作，绕过了快照读。（事务A先快照读，事务B插入一条数据并提交事务，事务A再当前读，触发幻读）

InnoDB 在 RR 级别下，如果执行的是当前读，为尽可能避免其它事务插入造成幻读。会对读取的记录使用临键锁。（例）

  • 间隙锁（Gap Lock）：锁定两个索引记录之间的间隙。
  • 临键锁（Next-key Lock）：间隙锁+记录锁，锁定一个索引记录及其前后的间隙。
  • 间隙锁只会阻塞 INSERT ，记录锁会阻塞任意的锁。
  • 查询普通列：表级别的间隙锁 + 表级别的记录锁。

  • 查询主键/唯一索引列

    • 范围查询：左开右闭，例如有 id 分别为 5, 10, 15, 20 的四条数据，查询条件为 11<id<16，则间隙锁锁住 (10,15] 和 (15,20] ，记录锁锁住 15, 20 。
    • 等值查询：若查询条件为 id=17，则间隙锁锁住 (10,15]，记录锁失效；若查询条件为 id=15，则间隙锁失效，记录锁锁住 15 。
  • 查询普通索引列：等值查询时由于字段可重复，间隙锁始终锁住相邻区间，其余同上。

 
插入意向锁：当事务插入一条记录时，会通过插入意向锁标记插入意图，其它事务插入相同唯一键时，会触发行锁，导致其他事务被阻塞，等待上一个事务提交或回滚后继续执行。这种方式可以优化插入操作的并发性能，防止死锁。

类型

MySQL 字段类型主要分为：

  • 数值类型：整型（ TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT ）、浮点型（ FLOAT、DOUBLE ）、定点型（ DECIMAL ）
  • 字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB 等。
  • 时间类型：YEAR、TIME、DATE、DATETIME、TIMESTAMP 等。

布尔类型

MySQL 没有布尔类型，而是用 TINYINT(1) 存储 0 或 1 来表示布尔值。

时间类型

数据类型存储方式受时区
影响		存储空间时间范围
INT / BIGINT全数字如 1578707612否4 Bytes / 8 Bytes1970-01-01 00:00:01 之后
DATETIMEYYYY-MM-DD HH:MM:SS否5 Bytes（<5.6.4）
5~8 Bytes		1000-01-01 00:00:00[.000000] ～
9999-12-31 23:59:59[.999999]
TIMESTAMPYYYY-MM-DD HH:MM:SS是4 Bytes（<5.6.4）
4~7 Bytes		1970-01-01 00:00:01[.000000] ～
2038-01-19 03:14:07[.999999]
  • MySQL 5.6.4 后，DATETIME 和 TIMESTAMP 的存储空间会根据毫秒精度而变化。

索引

数据结构

B+ 树索引、哈希索引、全文索引。

为什么使用 B+ 树而不是 B 树？

  • B+ 树非叶子节点只存指针，单个磁盘页可以存储更多的索引。
  • B+ 树查询效率更稳定，都是从根节点到叶子节点的过程。
  • B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+ 树的叶子节点是一个双向链表，只需要对链表进行遍历。

索引类型

聚簇索引：数据与索引一起存放，有且只有一个。若存在主键，主键索引就是聚簇索引；若不存在，则使用第一个唯一索引；若都没有，InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。
非聚簇索引（二级索引）：叶子节点存储的数据是主键的值，通过二级索引可以定位主键的位置（回表查询，即要查两个 B+ 树）。唯一索引、普通索引、前缀索引、全文索引等都属于二级索引。

覆盖索引：查询中使用了索引，且需要返回的列在索引中都能找到。

# 前两个为覆盖索引，第三个 gender 需要回表查询，为非覆盖索引
select * from user where id = 1;
select id, name from user where name = 'xxx'
select id, name, gender from user where name = 'xxx'

# 覆盖索引解决超大分页问题
select * from user limit 1000000, 10;
↓↓↓
select *
from user u
    (select id from user order by id limit 1000000, 10) a
where u.id = a.id;

索引创建原则

  • 数据量大，且查询比较频繁的表中的字段。
  • 经常进行 where、order by、group by 操作的字段。
  • 选择区分度高的列效率更高，尽量建立唯一索引。
  • 对于字符串类型的字段，根据字段特点，建立前缀索引。
  • 尽量使用联合索引，通过覆盖索引减少回表查询。
  • 控制索引数量，减小增删改时维护索引的代价。

索引下推

MySQL 5.6 后提供了索引下推（Index Condition Pushdown，ICP），允许存储引擎在索引遍历过程中，执行部分 WHERE 字句的判断条件，直接过滤掉不满足条件的记录，从而减少回表次数，提高查询效率。

例如表中有联合索引 （a, b） ，查询 select * from tb where a>xxx and b=yyy：

  • 没有索引下推时，存储引擎根据二级索引定位到 a>xxx 的一条记录后，获取主键值，进行回表操作，将完整记录返回给 Server 层，Server 层再判断是否 b=yyy ，如果是，则发送给客户端，然后继续读取记录。
  • 有索引下推后，存储引擎定位到 a=xxx 后，先不执行回表操作，而是判断 b=yyy 是否成立，如果成立则进行回表查询。

索引失效

  • 最左前缀原则
  • 隐式转换
  • like %xx：特殊情况，表中只有两个字段且建立联合索引 （a, b） ，查询 select * from xxx where name like "%xx" 会显示覆盖索引，但这种方式本质还是遍历了整颗索引树，只是没有回表。
  • WHERE 子句中的 OR：如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。如果都设置为索引，则会分别对两个索引进行扫描并合并。
  • 对索引列进行表达式计算
  • 对索引列使用函数：MySQL 8.0 后，新增函数索引，可以针对计算后的值建立一个索引。

count()

性能排序：`count(*) = count(1) > count(主键字段) > count(字段)。

  • count(*) / count(1)：统计表中有多少行。
  • count(字段)：函数指定的参数不为 NULL 的记录有多少个。

 
原因：count(主键字段) 在遍历时会读取记录中 id 的值，而 count(*) / count(1) 不会。此外，如果表中有二级索引，它们都会遍历这个二级索引，因为相较于聚簇索引空间更小，I/O 成本更低。

大表统计

  • 建立二级索引，提高一定速度。
  • 通过 show table status 或 explain 命令估算近似值。

  • 维护一个额外表保存计数值，每次插入时 +1。

    MyISAM 引擎的数据表维护了一个额外字段 row_count ，通过表级锁保证一致性；而 InnoDB 由于 MVCC 的原因，无法确定应该返回多少行。

优化

定位慢查询

MySQL 自带慢日志
/etc/my/cnf 中配置开启后，会记录在 /var/lib/mysql/localhost-slow.log 中。

# 开启慢日志查询
slow_query_log=1
# SQL 语句执行时间超过 2s，则记录慢查询日志，默认 10s。
long_query_time=2

分析慢查询

通过 EXPLAIN 命令获取执行语句的信息。

  • possible_key：可能使用到的索引。
  • key：实际命中的索引。
  • key_len：索引占用的大小。

  • Extra：额外的执行计划信息，常见的有

    Extra说明
    Using filesort需要额外的排序，没有用到索引。
    Using temporary创建临时表来存储查询结果，如 ORDER BY、GROUP BY
    Using index使用了覆盖索引，不用回表，查询效率高
    Using index condition使用了索引条件下推
    Using whereWHERE 条件在存储引擎层执行，不是索引覆盖
    Using join buffer (Block Nested Loop)连表查询时，若被驱动表未使用索引，MySQL 先缓存驱动表数据，再遍历匹配

  • type：SQL 连接类型，性能由好到差为

    Type说明
    NULL没有使用到表。
    system系统表，表只有一行数据，通常用于查询系统表
    const使用主键索引或唯一索引查询，最多只有一行匹配的记录
    eq_ref使用主键索引或唯一索引进行连接操作
    ref使用非唯一索引查找数据，可能由多个符合条件的行
    range对索引列进行范围查询
    index查询遍历了整棵索引树
    ALL全表扫描，不走索引

参考资料：JavaGuide

文章标题：【八股】MySQL

文章作者：nek0peko

文章链接：https://nek0peko.com/index.php/archives/270/

商业转载请联系站长获得授权，非商业转载请注明本文出处及文章链接，未经站长允许不得对文章文字内容进行修改演绎。
本文采用创作共用保留署名-非商业-禁止演绎4.0国际许可证

2025-02-07 八股文·none