MySQL

执行引擎概述 执行引擎（Executor） 负责调用存储引擎接口，执行SQL，返回结果。 SQL执行完整流程： ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ 连接器 │→│ 分析器 │→│ 优化器 │→│ 执行器 │ └──────────┘ └──────────┘ └──────────┘ └──────────┘ ↓ 调用接口 ┌──────────┐ │ 存储引擎 │ └──────────┘ 执行引擎的职责 1. 权限检查 -- 查询前检查权限 SELECT * FROM users WHERE id = 1; -- 检查流程 1. 当前用户是否有users表的SELECT权限？ 2. 如果没有 → 返回错误：Access denied -- 权限存储位置 mysql.user -- 全局权限 mysql.db -- 数据库权限 mysql.tables_priv -- 表权限 2. 调用存储引擎接口 -- 示例SQL SELECT * FROM users WHERE id = 1; -- 执行引擎操作 1. 打开表：handler = open_table("users") 2. 初始化查询：handler->index_init(id索引) 3. 定位第一行：handler->index_read(id=1) 4. 读取行数据：handler->read_row() 5. 关闭表：handler->close() 3. 结果返回 -- 返回流程 1. 存储引擎返回数据行 2. 执行引擎应用WHERE条件过滤 3. 格式化结果（MySQL协议） 4. 通过网络发送给客户端 -- 流式返回（Streaming） -- MySQL边查询边返回，不等全部数据查完 存储引擎接口（Handler API） 1. 打开表 // C代码示例（简化） int handler::ha_open(TABLE *table, const char *name, int mode); // 流程 1. 打开表文件（.ibd） 2. 加载表结构（.frm，MySQL 8.0已废弃） 3. 初始化Handler对象 2. 读取接口 // 全表扫描 int handler::rnd_init(); // 初始化全表扫描 int handler::rnd_next(uchar *buf); // 读取下一行 // 索引扫描 int handler::index_init(uint idx); // 初始化索引扫描 int handler::index_read(uchar *buf, const uchar *key, uint key_len); // 定位第一行 int handler::index_next(uchar *buf); // 读取下一行 3. 写入接口 // 插入 int handler::write_row(uchar *buf); // 更新 int handler::update_row(const uchar *old_data, const uchar *new_data); // 删除 int handler::delete_row(const uchar *buf); 执行流程详解 1. 全表扫描 -- SQL SELECT * FROM users WHERE age > 25; -- 执行流程 1. 打开表：open_table("users") 2. 初始化全表扫描：rnd_init() 3. 循环读取 WHILE (rnd_next(row) == 0) DO IF age > 25 THEN 发送到客户端 END IF END WHILE 4. 关闭表：close() -- 扫描行数：全表所有行 2. 索引扫描 -- SQL SELECT * FROM users WHERE id = 1; -- 执行流程（主键索引） 1. 打开表：open_table("users") 2. 初始化索引扫描：index_init(PRIMARY) 3. 定位第一行：index_read(id=1) 4. 读取行数据：read_row() 5. 发送到客户端 6. 关闭表：close() -- 扫描行数：1行（主键等值查询） 3. 索引范围扫描 -- SQL SELECT * FROM users WHERE id BETWEEN 1 AND 100; -- 执行流程 1. 打开表：open_table("users") 2. 初始化索引扫描：index_init(PRIMARY) 3. 定位第一行：index_read(id=1) 4. 循环读取 WHILE (index_next(row) == 0 AND id <= 100) DO 发送到客户端 END WHILE 5. 关闭表：close() -- 扫描行数：100行（range查询） JOIN执行流程 Nested Loop Join -- SQL SELECT * FROM orders o JOIN users u ON o.user_id = u.id; -- 执行流程（Nested Loop Join） 1. 打开orders表：open_table("orders") 2. 打开users表：open_table("users") 3. 初始化orders全表扫描：orders->rnd_init() 4. 循环orders表 FOR EACH order IN orders DO -- 内层：通过索引查找users表 users->index_read(id = order.user_id) users->read_row() -- 拼接结果，发送客户端 END FOR 5. 关闭表 -- 扫描次数 orders表：全表扫描（M行） users表：索引查找（M次，每次O(log N)） 总复杂度：O(M × log N) 执行器优化 1. 条件下推 -- SQL SELECT * FROM users WHERE id > 100; -- 未优化 1. 存储引擎返回所有行 2. 执行器过滤 id > 100 3. 返回结果 -- ICP优化（Index Condition Pushdown） 1. 存储引擎扫描索引时，直接过滤 id > 100 2. 返回满足条件的行 3. 减少回表次数 2. 批量读取 -- Multi-Range Read（MRR）优化 -- 场景：范围查询 + 回表 -- 未优化 1. 扫描索引：获取10个主键ID（随机顺序） 2. 逐个回表：10次随机IO -- MRR优化 1. 扫描索引：获取10个主键ID 2. 排序主键ID（变为顺序） 3. 批量回表：1次顺序IO 执行器与优化器的配合 -- SQL SELECT * FROM users WHERE name = 'Alice' AND age = 25; -- 优化器决策 1. 选择索引：idx_name_age 2. 生成执行计划： - type: ref - key: idx_name_age - rows: 1 -- 执行器执行 1. 调用存储引擎接口：index_read(idx_name_age, 'Alice', 25) 2. 存储引擎返回数据行 3. 执行器返回客户端 查看执行器操作 1. SHOW PROFILE -- 开启profiling SET profiling = 1; -- 执行SQL SELECT * FROM users WHERE id = 1; -- 查看执行耗时 SHOW PROFILES; -- 详细分析 SHOW PROFILE FOR QUERY 1; -- 输出示例 +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000050 | | checking permissions | 0.000005 | | Opening tables | 0.000020 | | init | 0.000010 | | System lock | 0.000005 | | optimizing | 0.000005 | | statistics | 0.000010 | | preparing | 0.000010 | | executing | 0.000005 | ← 执行器调用存储引擎 | Sending data | 0.000050 | ← 存储引擎返回数据 | end | 0.000005 | | query end | 0.000005 | | closing tables | 0.000005 | | freeing items | 0.000010 | | cleaning up | 0.000010 | +----------------------+----------+ 2. Performance Schema -- 查看执行引擎调用存储引擎的次数 SELECT * FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST_TEXT LIKE 'SELECT%users%' LIMIT 1\G -- 关键指标 COUNT_STAR : 10000 -- 执行次数 SUM_ROWS_EXAMINED : 100000 -- 扫描行数 SUM_ROWS_SENT : 10000 -- 返回行数 执行器性能影响 1. 扫描行数 -- 全表扫描（扫描100万行） SELECT * FROM users; -- 执行器调用100万次 rnd_next() -- 索引查询（扫描1行） SELECT * FROM users WHERE id = 1; -- 执行器调用1次 index_read() -- 优化：减少扫描行数 2. 回表次数 -- 非覆盖索引（需要回表） SELECT * FROM users WHERE name = 'Alice'; -- 1. 索引扫描：index_read(name='Alice') -- 2. 回表：read_row(主键=1) -- 覆盖索引（无需回表） SELECT id, name FROM users WHERE name = 'Alice'; -- 1. 索引扫描：index_read(name='Alice') -- 2. 直接返回（无需回表） -- 优化：使用覆盖索引 实战建议 1. 减少扫描行数 -- ❌ 不好：全表扫描 SELECT * FROM users WHERE age > 25; -- ✅ 好：使用索引 CREATE INDEX idx_age ON users(age); SELECT * FROM users WHERE age > 25; 2. 使用覆盖索引 -- ❌ 不好：需要回表 SELECT * FROM users WHERE name = 'Alice'; -- ✅ 好：覆盖索引 CREATE INDEX idx_name_age ON users(name, age); SELECT name, age FROM users WHERE name = 'Alice'; 3. 避免大结果集 -- ❌ 不好：返回100万行 SELECT * FROM users; -- ✅ 好：分页查询 SELECT * FROM users LIMIT 0, 100; 常见面试题 Q1: 执行引擎的作用是什么？ ...

查询优化器概述 查询优化器（Optimizer） 的作用是为SQL生成最优执行计划。 -- 原始SQL SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE u.name = 'Alice' AND o.status = 'PAID'; -- 优化器决策 1. 表连接顺序：先users还是orders？ 2. 索引选择：使用哪个索引？ 3. 连接算法：Nested Loop、Hash Join、Sort-Merge Join？ 4. 条件下推：WHERE条件何时过滤？ 优化器类型 1. 基于规则的优化器（RBO） Rule-Based Optimizer：根据预定义规则选择执行计划。 -- 规则示例 1. 有索引优于无索引 2. 主键索引优于二级索引 3. 小表驱动大表 -- 缺点： 不考虑数据分布，可能选择非最优计划 2. 基于成本的优化器（CBO） Cost-Based Optimizer（MySQL使用）：根据成本评估选择最优计划。 -- 成本因素 1. IO成本：磁盘读取次数 2. CPU成本：记录比较次数 3. 内存成本：临时表、排序开销 -- 优点： 考虑数据分布，选择更优计划 成本评估 1. 成本模型 总成本 = IO成本 + CPU成本 IO成本 = 页数 × IO_BLOCK_READ_COST CPU成本 = 行数 × ROW_EVALUATE_COST -- 默认值（可调整） IO_BLOCK_READ_COST = 1.0 ROW_EVALUATE_COST = 0.1 2. 索引扫描成本 -- 全表扫描（Table Scan） 成本 = 表页数 × 1.0 + 表行数 × 0.1 -- 索引扫描（Index Scan） 成本 = 索引页数 × 1.0 + 回表行数 × 0.1 + 回表IO × 1.0 -- 示例 表：100万行，10000页 索引：1000页，回表10万行 全表扫描成本 = 10000 × 1.0 + 1000000 × 0.1 = 110000 索引扫描成本 = 1000 × 1.0 + 100000 × 0.1 + 100000 × 1.0 = 111000 -- 优化器选择：全表扫描（成本更低） 优化策略 1. 条件化简 -- 原始条件 WHERE id > 0 AND id < 100 AND id = 50 -- 化简后 WHERE id = 50 -- 原始条件 WHERE (status = 'PAID' OR status = 'PAID') AND user_id = 1 -- 化简后 WHERE status = 'PAID' AND user_id = 1 2. 常量折叠 -- 原始 WHERE price > 100 * 2 -- 优化后 WHERE price > 200 -- 原始 WHERE YEAR(created_at) = 2025 -- 无法优化（函数作用于列，索引失效） 3. 谓词下推（Predicate Pushdown） -- 原始SQL SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE u.name = 'Alice'; -- 优化后（谓词下推） SELECT * FROM orders o JOIN (SELECT * FROM users WHERE name = 'Alice') u ON o.user_id = u.id; -- WHERE条件先过滤users表，减少JOIN的数据量 4. 外连接消除 -- 原始SQL SELECT * FROM orders o LEFT JOIN users u ON o.user_id = u.id WHERE u.id IS NOT NULL; -- 优化后（LEFT JOIN → INNER JOIN） SELECT * FROM orders o JOIN users u ON o.user_id = u.id; -- WHERE条件保证u.id不为NULL，可以消除LEFT JOIN 表连接顺序 1. 小表驱动大表 -- 示例 users表：100行 orders表：100万行 -- ✅ 好：users驱动orders FOR EACH user IN users (100次) FOR EACH order IN orders WHERE order.user_id = user.id END FOR -- 总循环次数：100次（users） × 平均100次（orders） = 10000次 -- ❌ 不好：orders驱动users FOR EACH order IN orders (100万次) FOR EACH user IN users WHERE user.id = order.user_id END FOR -- 总循环次数：100万次（效率低） 2. 连接算法 Nested Loop Join（嵌套循环）： ...

MySQL启动流程 启动命令： mysqld --defaults-file=/etc/my.cnf 完整流程（8个阶段）： 1. 读取配置文件 2. 初始化文件系统 3. 初始化InnoDB存储引擎 4. 加载系统表 5. 执行崩溃恢复（如需要） 6. 启动后台线程 7. 监听客户端连接 8. 完成启动 1. 读取配置文件 查找顺序 # MySQL配置文件查找顺序（Linux） 1. /etc/my.cnf 2. /etc/mysql/my.cnf 3. /usr/etc/my.cnf 4. ~/.my.cnf 5. --defaults-file指定的文件 # 查看实际读取的配置 mysqld --verbose --help | grep -A 1 "Default options" 关键配置 [mysqld] # 数据目录 datadir = /var/lib/mysql # 端口 port = 3306 # 字符集 character-set-server = utf8mb4 # Buffer Pool大小 innodb_buffer_pool_size = 8G # 日志文件 log-error = /var/log/mysql/error.log 2. 初始化文件系统 # 检查数据目录 1. 检查datadir是否存在 2. 检查datadir权限（mysql用户可读写） 3. 创建临时目录（tmpdir） # 检查必要文件 1. ibdata1（系统表空间） 2. ib_logfile0/ib_logfile1（redo log） 3. mysql/（系统数据库目录） # 如果文件不存在 → 初始化数据库：mysqld --initialize 3. 初始化InnoDB存储引擎 步骤： 1. 分配Buffer Pool内存 ├─ 默认128MB，生产环境建议50-80%内存 └─ innodb_buffer_pool_size = 8G 2. 打开表空间文件 ├─ ibdata1（系统表空间） └─ *.ibd（独立表空间） 3. 打开redo log文件 ├─ ib_logfile0 └─ ib_logfile1 4. 打开undo表空间（MySQL 8.0+） ├─ undo_001 └─ undo_002 5. 初始化Change Buffer、Adaptive Hash Index 4. 加载系统表 -- 加载系统数据库 mysql/ ├─ user.frm/ibd -- 用户表 ├─ db.frm/ibd -- 数据库权限 ├─ tables_priv.frm/ibd -- 表权限 └─ ... -- 加载存储引擎信息 information_schema/ performance_schema/ sys/ 5. 崩溃恢复（Crash Recovery） 检查是否需要恢复 判断条件： IF MySQL未正常关闭（如kill -9、断电） THEN 执行崩溃恢复 ELSE 跳过恢复 END IF -- 判断依据：redo log的checkpoint LSN 恢复流程 1. 读取redo log ├─ 从checkpoint开始读取redo log └─ 找到所有未刷盘的数据页修改 2. 重做（Redo） ├─ 对于已提交的事务 └─ 应用redo log，恢复数据 3. 回滚（Undo） ├─ 对于未提交的事务 └─ 使用undo log回滚 4. 清理 ├─ 清理临时表 └─ 清理未完成的DDL操作 -- 恢复时间：取决于redo log大小和修改量 -- 通常：几秒到几分钟 示例： ...

部分写问题（Partial Page Write） 问题 页大小16KB > 磁盘块大小4KB，写入过程中系统崩溃，导致页损坏。 写入16KB页（4个磁盘块）： ┌──────┬──────┬──────┬──────┐ │ 4KB │ 4KB │ 4KB │ 4KB │ └──────┴──────┴──────┴──────┘ ✅ ✅ ❌ ❌ 已写入 已写入 未写入 未写入 ↑ 系统崩溃 结果：页损坏（部分写） redo log无法恢复： redo log记录的是页的逻辑修改（如"将id=1的name改为Alice"） 如果页本身损坏，无法应用redo log Double Write Buffer解决方案 原理 先写双写缓冲区（连续空间），再写数据文件（离散空间）。 写入流程： 1. 脏页 → Double Write Buffer（共享表空间，连续2MB） 2. Double Write Buffer → 磁盘（刷盘，原子操作） 3. 脏页 → 数据文件（离散写入） 崩溃恢复： IF 数据文件页损坏 THEN 从Double Write Buffer恢复页 ELSE 使用redo log恢复 END IF Double Write Buffer结构 1. 内存结构 Buffer Pool ├─ 脏页1（16KB） ├─ 脏页2（16KB） └─ ... Double Write Buffer（内存，2MB） ├─ 页1（16KB） ├─ 页2（16KB） ├─ ... └─ 页128（16KB） 2. 磁盘结构 共享表空间（ibdata1） ┌─────────────────────────────────┐ │ 数据字典 │ ├─────────────────────────────────┤ │ Double Write Buffer（2MB，连续） │ ← 双写缓冲区 │ ├─ 区1（1MB = 64页） │ │ └─ 区2（1MB = 64页） │ ├─────────────────────────────────┤ │ Undo Log │ └─────────────────────────────────┘ 写入流程 1. 正常写入 1. 脏页刷新触发 ├─ Buffer Pool中的脏页批量刷新（如128个页） 2. 写入Double Write Buffer（内存） ├─ 128个页复制到Double Write Buffer 3. 写入共享表空间（磁盘，顺序写） ├─ fsync刷盘（原子操作） ├─ 2MB连续空间，1次IO 4. 写入数据文件（磁盘，随机写） ├─ 128个页分别写入各自的表空间文件 ├─ 随机IO，多次寻址 5. 清空Double Write Buffer（内存） 关键： ...

Adaptive Hash Index概述 Adaptive Hash Index（AHI，自适应哈希索引） 是InnoDB的自动优化机制，在Buffer Pool上层构建哈希索引，加速等值查询。 查询流程对比： 传统B+树查询： SELECT * FROM users WHERE id = 100; → B+树查找（3-4次页访问） AHI查询： SELECT * FROM users WHERE id = 100; → Hash查找（1次内存访问） AHI的工作原理 1. 自动构建 -- InnoDB监控查询模式 SELECT * FROM users WHERE id = 1; -- 第1次 SELECT * FROM users WHERE id = 2; -- 第2次 SELECT * FROM users WHERE id = 3; -- 第3次 ... SELECT * FROM users WHERE id = 100; -- 第N次 -- 当检测到等值查询频繁访问同一索引前缀 -- InnoDB自动构建哈希索引 AHI[100] → Buffer Pool页地址 2. 哈希索引结构 B+树索引（磁盘） 10 / \ 5 15 / \ / \ 1 7 12 17 → 数据页 AHI（内存） Hash表： id=1 → 页地址0x1000 id=5 → 页地址0x1001 id=7 → 页地址0x1002 id=10 → 页地址0x1003 ... 3. 查询流程 -- 查询 SELECT * FROM users WHERE id = 100; -- 流程 1. 检查AHI是否有id=100的条目 2. ✅ 有：直接通过Hash查找，获取页地址（O(1)） 3. ❌ 没有：走B+树查找（O(log N)） AHI的优势 1. 加速等值查询 -- 等值查询（适合AHI） SELECT * FROM users WHERE id = 100; -- ✅ AHI加速 SELECT * FROM orders WHERE order_id = 1001; -- ✅ AHI加速 -- B+树查询：3-4次页访问（假设树高3） -- AHI查询：1次Hash查找（快3-4倍） 2. 减少CPU开销 -- B+树查找需要： 1. 二分查找（多次比较） 2. 页间跳转（多次指针访问） 3. 最终定位数据行 -- AHI查找需要： 1. Hash计算（一次） 2. 直接获取页地址（一次） AHI的限制 1. 只适用于等值查询 -- ✅ 适用 SELECT * FROM users WHERE id = 100; SELECT * FROM users WHERE id IN (1, 2, 3); -- ❌ 不适用 SELECT * FROM users WHERE id > 100; -- 范围查询 SELECT * FROM users WHERE id BETWEEN 1 AND 100; -- 范围查询 SELECT * FROM users WHERE name LIKE 'A%'; -- 模糊查询 2. 只针对热点数据 -- AHI只缓存频繁访问的索引前缀 -- 例如：id=100访问了1000次 → 自动加入AHI -- 而：id=999999只访问1次 → 不会加入AHI 3. 有维护开销 -- 更新/删除数据时，需要维护AHI UPDATE users SET name = 'Alice' WHERE id = 100; -- 流程： 1. 更新B+树索引 2. 更新Buffer Pool 3. 更新AHI（如果id=100在AHI中） AHI配置 1. 查看状态 -- 查看AHI开关 SHOW VARIABLES LIKE 'innodb_adaptive_hash_index'; -- ON（默认开启） -- 查看AHI分区数（MySQL 5.7+） SHOW VARIABLES LIKE 'innodb_adaptive_hash_index_parts'; -- 8（默认，减少锁竞争） 2. 开关配置 -- 关闭AHI（不推荐） SET GLOBAL innodb_adaptive_hash_index = OFF; -- 开启AHI（默认） SET GLOBAL innodb_adaptive_hash_index = ON; 注意：动态修改需要重建AHI，会短暂影响性能。 ...

Change Buffer概述 Change Buffer 是InnoDB的写优化机制，用于缓存非唯一二级索引的修改操作。 传统方式（每次修改立即更新索引）： INSERT → 更新数据页 → 更新索引页（磁盘IO） Change Buffer方式（延迟更新索引）： INSERT → 更新数据页 → 缓存到Change Buffer → 后台合并（merge） 为什么需要Change Buffer？ 问题：二级索引随机IO CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100), INDEX idx_email (email) -- 非唯一二级索引 ); -- 插入数据 INSERT INTO users VALUES (1, 'Alice', 'alice@a.com'); INSERT INTO users VALUES (100, 'Bob', 'bob@b.com'); INSERT INTO users VALUES (50, 'Charlie', 'charlie@c.com'); 问题： 主键索引：顺序插入（1 → 100 → 50，但按主键排序） email索引：随机插入（alice → bob → charlie，按email排序） 随机IO：email索引页分散在磁盘各处，需要多次磁盘寻址 Change Buffer解决方案： 缓存email索引的修改到内存 批量合并（merge），减少随机IO Change Buffer的工作原理 1. 插入流程 INSERT INTO users VALUES (101, 'David', 'david@d.com'); -- 流程 1. 更新主键索引（聚簇索引） - 直接写入Buffer Pool（缓存命中）或磁盘 2. 更新email索引（二级索引） - 检查索引页是否在Buffer Pool - ✅ 在：直接更新 - ❌ 不在：缓存到Change Buffer（不读磁盘） 3. 后台合并（merge） - 当索引页加载到Buffer Pool时 - 或系统空闲时 - 或Change Buffer满时 2. 合并（Merge）流程 触发条件： 1. 查询需要访问该索引页 2. 后台线程定期合并 3. Change Buffer空间不足 4. MySQL正常关闭 合并流程： 1. 加载索引页到Buffer Pool 2. 应用Change Buffer中的修改 3. 写回磁盘（或标记为脏页） 4. 清空Change Buffer对应条目 Change Buffer的限制 只适用于非唯一二级索引 -- ✅ 适用：非唯一索引 CREATE INDEX idx_name ON users(name); -- 可以使用Change Buffer -- ❌ 不适用：唯一索引 CREATE UNIQUE INDEX uk_email ON users(email); -- 无法使用Change Buffer -- 原因：需要读取索引页检查唯一性，无法延迟合并 -- ❌ 不适用：主键索引 -- 原因：主键是聚簇索引，数据直接存储在索引中 为什么唯一索引不能用？ -- 插入数据 INSERT INTO users VALUES (102, 'Eve', 'eve@e.com'); -- 唯一索引检查流程 1. 检查email='eve@e.com'是否已存在 2. 必须读取email索引页（磁盘IO） 3. 如果不存在，才能插入 -- 无法使用Change Buffer 因为必须立即读取索引页，无法延迟合并 Change Buffer配置 1. 查看配置 -- 查看Change Buffer大小 SHOW VARIABLES LIKE 'innodb_change_buffer_max_size'; -- 25（默认，占Buffer Pool的25%） -- 查看Change Buffer模式 SHOW VARIABLES LIKE 'innodb_change_buffering'; -- all（默认） 2. 模式配置 模式 缓存操作 适用场景 all INSERT、DELETE、UPDATE（默认） 通用 none 禁用 唯一索引多的表 inserts 只缓存INSERT 只有插入场景 deletes 只缓存DELETE 批量删除场景 changes 缓存INSERT、UPDATE 插入更新场景 purges 缓存DELETE、PURGE 清理场景 -- 配置示例 # my.cnf [mysqld] innodb_change_buffering = all innodb_change_buffer_max_size = 25 -- 25% 性能对比 测试场景 -- 测试表（500万行，非唯一索引） CREATE TABLE test_change_buffer ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100), phone VARCHAR(20), INDEX idx_email (email), INDEX idx_phone (phone) ); -- 批量插入100万行 INSERT INTO test_change_buffer (name, email, phone) SELECT CONCAT('User', seq), CONCAT('user', seq, '@example.com'), CONCAT('1380000', seq) FROM seq_1_to_1000000; 结果： ...

Buffer Pool概述 Buffer Pool（缓冲池） 是InnoDB最重要的内存结构，用于缓存数据页和索引页。 ┌──────────────────────────────────────┐ │ MySQL内存结构 │ ├──────────────────────────────────────┤ │ Buffer Pool（最大，默认128MB） │ 缓存数据页 ├──────────────────────────────────────┤ │ Change Buffer │ 缓存写操作 ├──────────────────────────────────────┤ │ Adaptive Hash Index │ 自适应哈希索引 ├──────────────────────────────────────┤ │ Log Buffer │ 缓存redo log └──────────────────────────────────────┘ Buffer Pool的作用 1. 减少磁盘IO -- 无Buffer Pool（每次查询都访问磁盘） SELECT * FROM users WHERE id = 1; -- 磁盘IO：10ms -- 有Buffer Pool（缓存命中，直接读内存） SELECT * FROM users WHERE id = 1; -- 内存读取：0.01ms（快1000倍） 2. 提升并发性能 -- 多个事务并发读取同一数据页 事务A: SELECT * FROM users WHERE id = 1; -- 加载到Buffer Pool 事务B: SELECT * FROM users WHERE id = 1; -- 直接从Buffer Pool读取（无磁盘IO） 事务C: SELECT * FROM users WHERE id = 1; -- 直接从Buffer Pool读取 Buffer Pool的结构 1. 缓冲页（Buffer Page） 缓冲页是Buffer Pool的基本单位，与磁盘页一一对应。 ...

行格式概述 行格式（Row Format） 定义了一行数据在磁盘上的存储方式。 -- 查看表的行格式 SHOW TABLE STATUS LIKE 'users'\G -- Row_format: Dynamic -- 创建表时指定行格式 CREATE TABLE test ( id INT PRIMARY KEY, name VARCHAR(100) ) ROW_FORMAT=COMPACT; -- 修改行格式 ALTER TABLE test ROW_FORMAT=DYNAMIC; 四种行格式 行格式 MySQL版本 特点 适用场景 Redundant 5.0前默认 旧格式，占用空间大 兼容性 Compact 5.0-5.6默认 紧凑格式，节省20%空间 通用（已过时） Dynamic 5.7+默认 动态格式，处理行溢出 推荐（默认） Compressed 5.5+ 压缩格式，节省50%+空间 只读表、归档数据 1. Compact行格式（紧凑格式） 结构 ┌──────────────────────────────────────────────────┐ │ 变长字段长度列表（逆序） │ VARCHAR、TEXT字段的长度 ├──────────────────────────────────────────────────┤ │ NULL值列表（位图，逆序） │ 标记哪些字段为NULL ├──────────────────────────────────────────────────┤ │ 记录头信息（5字节） │ deleted_flag、min_rec_flag等 ├──────────────────────────────────────────────────┤ │ 隐藏列 │ DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID ├──────────────────────────────────────────────────┤ │ 列1数据 │ 实际数据 ├──────────────────────────────────────────────────┤ │ 列2数据 │ ├──────────────────────────────────────────────────┤ │ ... │ └──────────────────────────────────────────────────┘ 示例 CREATE TABLE user_compact ( id INT PRIMARY KEY, name VARCHAR(20), age INT, email VARCHAR(50) ) ROW_FORMAT=COMPACT; INSERT INTO user_compact VALUES (1, 'Alice', 25, 'alice@example.com'); 存储布局： ...

InnoDB存储层次 表空间（Tablespace） └─ 段（Segment） └─ 区（Extent，1MB = 64页） └─ 页（Page，16KB，InnoDB的基本IO单位） └─ 行（Row） 1. 表空间（Tablespace） 定义 表空间是InnoDB存储数据的逻辑单位，包含多个段。 分类 类型 文件名 内容 配置参数 系统表空间 ibdata1 数据字典、undo log、双写缓冲 innodb_data_file_path 独立表空间 table_name.ibd 表数据+索引 innodb_file_per_table 通用表空间 tablespace.ibd 多个表共享 CREATE TABLESPACE 临时表空间 ibtmp1 临时表、排序缓冲 innodb_temp_data_file_path Undo表空间 undo_001、undo_002 undo log（MySQL 8.0+） innodb_undo_tablespaces 系统表空间 vs 独立表空间 -- 查看配置 SHOW VARIABLES LIKE 'innodb_file_per_table'; -- ON：每个表独立.ibd文件（推荐） -- OFF：所有表共享ibdata1（不推荐） -- 查看表空间文件 # ls -lh /var/lib/mysql/test/ -rw-r----- 1 mysql mysql 16M users.ibd -- 独立表空间 优点： ...

存储引擎概述 存储引擎（Storage Engine） 是MySQL的核心组件，负责数据的存储和读取。 -- 查看支持的存储引擎 SHOW ENGINES; -- 输出示例 +--------------------+---------+ | Engine | Support | +--------------------+---------+ | InnoDB | DEFAULT | -- 默认引擎 | MyISAM | YES | | MEMORY | YES | | CSV | YES | | ARCHIVE | YES | +--------------------+---------+ InnoDB vs MyISAM对比 特性 InnoDB MyISAM 事务支持 ✅ 支持ACID ❌ 不支持 锁粒度 ✅ 行锁 ❌ 表锁 外键 ✅ 支持 ❌ 不支持 崩溃恢复 ✅ 自动恢复（redo log） ❌ 需要手动修复 MVCC ✅ 支持 ❌ 不支持 全文索引 ✅ 支持（5.6+） ✅ 支持 存储结构 聚簇索引 非聚簇索引 COUNT(*) 慢（需要扫描） 快（存储行数） 空间占用 较大 较小 适用场景 OLTP（事务处理） OLAP（统计分析） InnoDB存储引擎 核心特性 支持事务：ACID保障 行级锁：高并发性能 MVCC：读写不冲突 外键约束：数据完整性 文件结构 # InnoDB文件（MySQL 5.7+） /var/lib/mysql/ ├─ ibdata1 # 系统表空间（共享） ├─ ib_logfile0 # redo log文件 ├─ ib_logfile1 ├─ test/ # 数据库目录 │ ├─ users.ibd # 独立表空间（数据+索引） │ └─ users.frm # 表结构定义（MySQL 8.0已废弃） 聚簇索引 主键索引直接存储数据： ...