@TOC
一.存储引擎
1.1 MySQL体系结构
1). 连接层
最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。
2). 服务层
完成大多数的核心服务功能，如SQL接口，缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。
3).存储引擎层
负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的。
4). 存储层
数据存储层， 主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。
插件式的存储引擎架构，
1.2 存储引擎介绍
存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。
1). 建表时指定存储引擎
CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;
2). 查询当前数据库支持的存储引擎
-
XA → 是否支持 XA 分布式事务（两阶段提交，用于跨数据库事务）。
-
Savepoints → 是否支持保存点（事务内设置回滚点，可部分回滚）。
-- 1. 开启事务 START TRANSACTION; -- 2. 插入第一条数据 INSERT INTO users (id, name) VALUES (1, 'Alice'); -- 3. 设置保存点 sp1 SAVEPOINT sp1; -- 4. 插入第二条数据 INSERT INTO users (id, name) VALUES (2, 'Bob'); -- 5. 设置保存点 sp2 SAVEPOINT sp2; -- 6. 插入第三条数据（假设这里出错了） INSERT INTO users (id, name) VALUES (3, 'Charlie'); -- 7. 发现第三条有问题，回滚到 sp2（只撤销 Charlie） ROLLBACK TO SAVEPOINT sp2; -- 此时 Bob 还在，Charlie 被撤销了 -- 8. 再次插入第三条数据（修正后） INSERT INTO users (id, name) VALUES (3, 'Charlie'); -- 9. 提交事务 COMMIT;
| 特性 | 普通事务（单机） | XA分布式事务（跨实例） |
|---|---|---|
| 操作范围 | 同一个MySQL实例内的任意张表 | 多个独立的MySQL实例，或MySQL + 其他资源（如MQ） |
| 锁机制 | InnoDB的行锁/间隙锁/表锁（本地生效） | 两阶段提交（2PC），通过全局协调实现 |
| 锁定的表数量 | 无限制（可以是一张，也可以是几千张） | 无限制（但每个分支实例内部仍用本地锁） |
| 协调方式 | 单一InnoDB引擎自我管理 | 需要外部事务管理器（TM）介入 |
show engines;
1.3 存储引擎特点
1.3.1 InnoDB
1).介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的MySQL 存储引擎。
2).特点
DML操作遵循ACID模型，支持事务；行级锁，提高并发访问性能；支持外键FOREIGN KEY约束，保证数据的完整性和正确性；
3). 文件
innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的 、sdi-新版的）、数据和索引。
目录：
每一个ibd文件就对应一张表
4). 逻辑存储结构
-
表空间（Tablespace） 是逻辑结构的最高层，对应物理上的
.ibd文件。每个表独立拥有一个表空间（默认配置下）。 -
段（Segment） 是表空间内部的逻辑分组，常见类型有数据段（存放 B+ 树的叶子节点，即实际行数据）、索引段（存放 B+ 树的非叶子节点，即索引键）和回滚段（存放 Undo 日志）。段的管理完全由 InnoDB 引擎自动完成，无需人工干预。
-
区（Extent） 是段的组成单元，固定大小为 1MB。每个区包含 64 个连续页（64 × 16KB = 1MB）。InnoDB 默认每次分配 1 个区，在批量写入等场景下，为了减少磁盘碎片，最多会一次性分配 4 个连续区。
-
页（Page） 是 InnoDB 磁盘 I/O 的最小操作单位，默认大小为 16KB。常见的页类型包括数据页、索引页、Undo 页、系统页等。
-
行（Row） 是数据存储的最小逻辑单元，数据按行存放在页中。除了用户定义的字段外，每行记录还包含隐藏字段：事务 ID（
DB_TRX_ID，6 字节）和回滚指针（DB_ROLL_PTR，7 字节）；如果表未显式定义主键，还会额外生成一个行 ID（DB_ROW_ID，6 字节）。┌─────────────────────────────────────────────────────────────┐ │ 表空间 (Tablespace) │ │ 对应物理文件: xxx.ibd │ │ │ │ ┌───────────────────────────────────────────────────────┐ │ │ │ 段 (Segment) │ │ │ │ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ │ │ │ │ 数据段 │ │ 索引段 │ │ 回滚段 │ ... │ │ │ │ └──────────┘ └──────────┘ └──────────┘ │ │ │ │ │ │ │ │ ┌───────────────────────────────────────────────┐ │ │ │ │ │ 区 (Extent) │ │ │ │ │ │ 大小: 1 MB │ │ │ │ │ │ │ │ │ │ │ │ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐ │ │ │ │ │ │ │ 页 0 │ │ 页 1 │ │ 页 2 │ ... │ 页 63│ │ │ │ │ │ │ └──────┘ └──────┘ └──────┘ └──────┘ │ │ │ │ │ │ 每个页大小: 16 KB (默认) │ │ │ │ │ │ 共 64 个连续页，构成 1 MB │ │ │ │ │ └───────────────────────────────────────────────┘ │ │ │ │ │ │ │ │ ┌───────────────────────────────────────────────┐ │ │ │ │ │ 页 (Page) │ │ │ │ │ │ ┌─────────────────────────────────────────┐ │ │ │ │ │ │ │ 页头 (38B) │ 行数据区 │ 页尾 (8B) │ │ │ │ │ │ │ └─────────────────────────────────────────┘ │ │ │ │ │ │ │ │ │ │ │ │ ┌─────────────────────────────────────────┐ │ │ │ │ │ │ │ 行 (Row) 1 │ 行 (Row) 2 │ ... │ │ │ │ │ │ │ └─────────────────────────────────────────┘ │ │ │ │ │ └───────────────────────────────────────────────┘ │ │ │ └───────────────────────────────────────────────────────┘ │ └─────────────────────────────────────────────────────────────┘
1.3.2 MyISAM
1). 介绍
MyISAM是MySQL早期的默认存储引擎。
2). 特点
不支持事务，不支持外键
支持表锁，不支持行锁
访问速度快
3). 文件
xxx.sdi：存储表结构信息
xxx.MYD: 存储数据
xxx.MYI: 存储索引
1.3.3 Memory
1). 介绍
Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为
临时表或缓存使用。
2). 特点
内存存放
hash索引（默认）
3).文件
xxx.sdi：存储表结构信息
1.4 存储引擎选择
| 特性 | InnoDB（默认） | MyISAM | Memory | ARCHIVE |
|---|---|---|---|---|
| 事务 (ACID) | ✅ 支持 | ❌ 不支持 | ❌ 不支持 | ❌ 不支持 |
| 行级锁 | ✅ 支持（高并发） | ❌ 表锁（并发差） | ❌ 表锁 | ❌ 表锁 |
| 外键约束 | ✅ 支持 | ❌ 不支持 | ❌ 不支持 | ❌ 不支持 |
| 崩溃恢复 | ✅ 强（Redo Log） | ⚠️ 弱（需修复） | ❌ 无（重启即丢） | ⚠️ 弱 |
| MVCC（多版本并发） | ✅ 支持 | ❌ 不支持 | ❌ 不支持 | ❌ 不支持 |
| 全文索引 | ✅ 支持（5.6+） | ✅ 支持（传统强项） | ❌ 不支持 | ❌ 不支持 |
| 数据压缩 | ✅ 支持（表压缩） | ✅ 支持（压缩表） | ❌ 不支持 | ✅极致压缩（ZIP） |
| 适用场景 | 几乎所有 OLTP 场景 | 只读/报表/日志分析 | 临时表/缓存/会话 | 大量历史日志/审计 |
二 索引
2.1 索引概述
2.1.1 介绍
索引（index）是帮助MySQL高效获取数据的数据结构(有序)。
MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：
| 特性 | B+Tree | Hash | R-tree（空间） | Full-text（全文） |
|---|---|---|---|---|
| 底层数据结构 | 平衡多叉树 | 哈希表 | R树（多维度平衡树） | 倒排索引（单词 → 文档列表） |
| 支持精确查询 (=) | ✅ 支持 | ✅最快（O(1)） | ❌ 不支持 | ❌ 不支持（只有全文匹配） |
| 支持范围查询 (> <) | ✅ 支持 | ❌ 不支持 | ✅ 支持（空间范围） | ❌ 不支持 |
| 支持排序 (ORDER BY) | ✅ 支持 | ❌ 不支持 | ❌ 不支持 | ❌ 不支持 |
| 支持模糊匹配 (LIKE) | ⚠️ 仅前缀匹配（如 'abc%'） |
❌ 不支持 | ❌ 不支持 | ✅支持（全文搜索） |
| 适用引擎 | InnoDB / MyISAM / Memory | Memory / InnoDB（自适应） | InnoDB / MyISAM | InnoDB / MyISAM |
| 典型场景 | 所有通用查询 | 键值对缓存（如会话ID） | 地理围栏 / 位置服务 | 文章、评论、产品描述搜索 |
| 能否手动创建 | ✅ 可以 | ✅ 可以（Memory引擎） | ✅ 可以 | ✅ 可以 |
2.2.2 B-Tree。
B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。
以一颗最大度数（max-degree）为4(4阶)的b-tree为例，那这个B树每个节点最多存储3个key，4
个指针。
https://www.cs.usfca.edu/~galles/visualization/BTree.html
5阶的B树，每一个节点最多存储4个key，对应5个指针。
一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
在B树中，非叶子节点和叶子节点都会存放数据。
看下面这个简单的B树节点，里面存了 3个Key 和 4（P）个指针：
┌─────────────────────────────────────────────────────────┐
│ P0 │ K1=10 │ P1 │ K2=20 │ P2 │ K3=30 │ P3 │
└─────────────────────────────────────────────────────────┘
2.2.3 B+Tree
所有的数据都会出现在叶子节点（没有分支的节点）。
叶子节点形成一个单向链表。
非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。
MySQL优化后变成了双向链表
2.3 索引分类
2.3.1 索引分类
MySQL索引
│
┌───────────────────────┼───────────────────────┐
│ │ │
按数据结构分类 按物理存储分类 按字段特性分类
│ │ │
┌───┴───┐ ┌─────┴─────┐ ┌─────┴─────┐
│ │ │ │ │ │
B+Tree Hash 聚簇索引 二级索引 主键索引 唯一索引 普通索引 全文索引
(主流) (Memory) (InnoDB) (辅助索引) (PRIMARY) (UNIQUE) (INDEX) (FULLTEXT)
│ │
R-tree Full-text ┌─────────────────┐
(空间) (全文) │ 按业务应用分类 │
│ 单列索引 | 组合索引 │
└─────────────────┘
聚集索引：
必须有,而且只有一个（
如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。）
聚集索引的叶子节点下挂的是这一行的数据 。
二级索引：
索引结构的叶子节点关联的是对应的主键可以存在多个。
叶子节点下挂的是该字段值对应的主键值。（即查询可能要进行回表）。
-- 假设表结构
CREATE TABLE user (
id INT PRIMARY KEY, -- 聚集索引
name VARCHAR(50),
age INT,
INDEX idx_name (name) -- 二级索引
);
-- 查询
SELECT * FROM user WHERE name = '张三';
┌─────────────────────────────────────────────────────────────────────────┐
│ 查询流程 │
│ │
│ ① 走二级索引 idx_name │
│ ┌─────────────────────────────┐ │
│ │ idx_name (二级索引) │ │
│ │ ┌──────────┬────────────┐ │ │
│ │ │ name │ 主键 id │ │ ← 找到 name='张三'，得到 id=5 │
│ │ ├──────────┼────────────┤ │ │
│ │ │ 张三 │ 5 │ │ │
│ │ └──────────┴────────────┘ │ │
│ └─────────────────────────────┘ │
│ │ │
│ ▼ ② 回表（拿着 id=5 去聚集索引查完整行） │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ 聚集索引 (主键索引) │ │
│ │ ┌──────────┬────────────────────────────────────────────────┐ │ │
│ │ │ 主键 id │ 完整行数据 (name, age, 及其他所有列) │ │ │
│ │ ├──────────┼────────────────────────────────────────────────┤ │ │
│ │ │ 5 │ '张三', 25, ... │ │ │
│ │ └──────────┴────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ ③ 返回完整行数据给客户端 │
└─────────────────────────────────────────────────────────────────────────┘
| 对比维度 | 主键索引 (PRIMARY KEY) | 唯一索引 (UNIQUE) | 普通索引 (INDEX/KEY) | 全文索引 (FULLTEXT) |
|---|---|---|---|---|
| 核心定义 | 唯一标识表中每一行记录的索引 | 确保某列（或列组合）的值在表中全局唯一 | 最基本的索引类型，仅用于加速查询，无任何约束 | 基于倒排索引，用于对大文本字段进行关键词搜索 |
| 唯一性约束 | ✅ 必须唯一 | ✅ 必须唯一 | ❌ 允许重复 | ❌ 允许重复 |
| 非空约束 | ✅ 必须非空（NOT NULL） | ❌ 允许 NULL（但只能有一个 NULL 值） | ❌ 允许 NULL，且可有多个 NULL | ❌ 允许 NULL（但全文索引通常作用于非空文本列） |
| 每表数量 | 最多 1 个（每表必须有且仅有 1 个） | 可以有多个（可对多列分别建立多个唯一索引） | 可以有多个 | 可以有多个（可对多个文本列分别建立） |
| 默认排序 | 按主键值升序物理存储 | 按索引列值升序存储 | 按索引列值升序存储 | 按相关性评分排序（查询时动态计算） |
| 底层结构 | B+Tree（叶子节点存储完整行数据，即聚簇索引） | B+Tree（叶子节点存储主键值，即二级索引） | B+Tree（叶子节点存储主键值，即二级索引） | 倒排索引（词 → 文档ID列表） |
| 是否必须存在 | ✅ 是（InnoDB 必须有聚集索引；无主键时会自动生成隐藏 rowid） | ❌ 否（可选） | ❌ 否（可选） | ❌ 否（可选） |
| 适用场景 | - 每张表的行唯一标识 - 频繁用于 JOIN 的关联字段 - 作为其他索引的回表依据 |
- 业务唯一标识（如身份证号、手机号、邮箱） - 防止重复数据插入 - 加速等值查询 |
- 加速 WHERE、JOIN、ORDER BY 的查询 - 覆盖索引的组合列 - 绝大多数查询加速需求 |
- 文章/博客/评论的内容搜索 - 产品描述的关键词匹配 - 日志/文档的全文检索 |
| 查询优化 | 等值查询（O(log n)）、范围查询、排序 | 等值查询（O(log n)）、范围查询、排序 | 等值查询（O(log n)）、范围查询、排序 | 自然语言搜索、布尔搜索，支持关键词匹配，但性能远不如 Elasticsearch |
| 是否支持组合 | ❌ 不支持（主键必须是单列或组合，但组合后整体视为一个主键） | ✅ 支持（组合唯一索引，列组合值唯一） | ✅ 支持（组合普通索引，遵循最左前缀原则） | ✅ 支持（可对多个列建立组合全文索引） |
| DDL 语法示例 | CREATE TABLE t (id INT PRIMARY KEY);或 ALTER TABLE t ADD PRIMARY KEY (id); |
CREATE TABLE t (email VARCHAR(50) UNIQUE);或 ALTER TABLE t ADD UNIQUE idx_email (email); |
CREATE TABLE t (name VARCHAR(50), INDEX idx_name (name));或 ALTER TABLE t ADD INDEX idx_name (name); |
CREATE TABLE t (content TEXT, FULLTEXT idx_ft (content));或 ALTER TABLE t ADD FULLTEXT idx_ft (content); |
| 查询语法示例 | SELECT * FROM t WHERE id = 1; |
SELECT * FROM t WHERE email = 'a@b.com'; |
SELECT * FROM t WHERE name = '张三'; |
SELECT * FROM t WHERE MATCH(content) AGAINST('关键词'); |
| 主要限制 | 1. 每表只能有一个 2. 列值必须非空且唯一 3. 组合主键最多 16 列（MySQL 限制） |
1. 允许一个 NULL 值（在 MySQL 中，NULL != NULL，所以多个 NULL 不违反唯一性） 2. 组合唯一索引中，某列为 NULL 时，该行不参与唯一约束校验 |
1. 不保证唯一性，可能返回多条记录 2. 过长列需指定前缀长度（如 INDEX idx_name (name(10))） |
1.仅支持 CHAR、VARCHAR、TEXT 类型 2. 存在 50% 阈值（自然语言模式，结果集 > 50% 会被忽略） 3. 不支持中文分词（需配合 ngram 插件） 4. 性能有限，不适合大规模全文检索（建议用 ES） |
| 是否支持覆盖索引 | ✅ 支持（本身就是数据，无需回表） | ✅ 支持（如果查询列都在该唯一索引中，则免回表） | ✅ 支持（如果查询列都在该普通索引中，则免回表） | ❌ 不支持（全文索引只返回文档ID，还需回表取数据） |
| 存储空间消耗 | 较大（叶子节点存完整行数据） | 较小（只存索引列值 + 主键值） | 较小（只存索引列值 + 主键值） | 较大（倒排索引需存储词项及其文档ID列表，占用空间可观） |
| 写入性能影响 | 插入/更新时需维护 B+Tree 顺序，有一定开销 | 插入/更新时需校验唯一性，额外开销 | 插入/更新时需维护 B+Tree，开销相对较小 | 插入/更新时需同步更新倒排索引，开销最大 |
B树深度问题
一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空
间，主键即使为bigint，占用字节数为8。
高度为2：
索引页（非叶子节点页） 只存主键+指针6字节（固定）
n * 8 + (n + 1) * 6 = 16*1024 , 算出n约为 1170
1171* 16 = 18736
也就是说，如果树的高度为2，则可以存储 18000 多条记录。
高度为3：
1171 * 1171 * 16 = 21939856
也就是说，如果树的高度为3，则可以存储 2200w 左右的记录。
【根目录页】 ← 第1层（非叶子）
存的是：主键 + 指针
（比如：100→指向中间页A，200→指向中间页B）
/ \
/ \
【中间目录页A】 【中间目录页B】 ← 第2层（非叶子）
存的是：主键 + 指针 存的是：主键 + 指针
（比如：50→数据页1） （比如：150→数据页3）
/ \ / \
/ \ / \
【数据页1】 【数据页2】 【数据页3】 【数据页4】 ← 第3层（叶子）
存完整行数据 存完整行数据 存完整行数据 存完整行数据
16行 16行 16行 16行
2.4 索引语法
1). 创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;
2). 查看索引
SHOW INDEX FROM table_name ;
3). 删除索引
DROP INDEX index_name ON table_name ;
2.5 SQL性能分析
# 查看MySQL 服务器的全局运行状态统计信息。
SHOW GLOBAL STATUS;
| 分类 | 核心指标 | 用途 |
|---|---|---|
| 连接池 | Threads_connected |
当前连接数，接近上限时需扩容 |
Max_used_connections |
历史最高连接数，评估连接池配置 | |
| SQL 概况 | Com_select/insert/update/delete |
统计读写比例 |
Slow_queries |
慢查询总数，衡量SQL整体健康度 | |
Questions |
总查询数，用于计算 QPS | |
| 索引与扫描 | Select_scan |
全表扫描次数，越大说明索引越差 |
Select_full_join |
无索引JOIN次数，必须趋近于0 | |
| 临时表与排序 | Created_tmp_disk_tables |
磁盘临时表次数，过大需调优 |
Sort_merge_passes |
排序合并文件次数，越小越好 | |
| 内存命中率 | Innodb_buffer_pool_reads |
从磁盘读的次数，越小越好 |
Innodb_buffer_pool_read_requests |
从内存读的次数，越大越好 | |
| (二者结合计算命中率) | 应 > 95%，否则内存不足 | |
| 行操作量 | Innodb_rows_read/inserted/updated/deleted |
统计行级读写压力 |
| 锁等待 | Innodb_row_lock_current_waits |
当前行锁等待数，必须为0 |
Innodb_row_lock_waits |
行锁等待累计次数，越少越好 | |
Table_locks_waited |
表锁等待次数，越少越好 | |
| 日志与IO | Innodb_log_waits |
日志等待次数，必须为0 |
Innodb_data_reads/writes |
磁盘读写次数，看IO压力 | |
| 其它 | Uptime |
运行时长 |
Open_tables |
当前打开表数，评估缓存配置 |
2.5.1 SQL执行频率
MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信
息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次。
查询增删改查次数
-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';
2.5.2 慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有
SQL语句的日志。
MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。
show variables like 'slow_query_log%';
不开启的原因
- 磁盘I/O开销：每一条符合条件的SQL都需要被判断、格式化并写入磁盘上的日志文件，这是一个额外的、持续的I/O操作。在高并发的生产环境下，这会与正常的数据读写争抢磁盘资源，拖慢整体性能。
- CPU与锁竞争：写入日志（尤其是文本文件）需要CPU参与，并且在内核层面可能存在并发写入的竞争，进一步增加开销。
2.5.3 performance_schema
1.什么是 performance_schema？
官方定义：
performance_schema是一个用于监控 MySQL 服务器运行时性能的存储引擎（PERFORMANCE_SCHEMA），它以表的形式提供内部执行数据，不影响正常业务事务。
核心特点：
- 内置默认启用（MySQL 8.0 默认开启，5.7 通常也默认开启）
- 数据位于内存中（重启后重置），不会写入磁盘，无持久化开销
- 采样开销极低（通常 < 5%），适合长期开启在生产环境
- 提供 数十张表，涵盖：语句、阶段、事务、等待、锁、内存、文件IO、连接、复制等所有维度
2.与 SHOW PROFILES 的本质区别
| 对比维度 | SHOW PROFILES |
performance_schema |
|---|---|---|
| 数据来源 | 临时记录在会话变量中 | 持久化在内存表中，结构化存储 |
| 历史范围 | 仅当前会话，有限条数（默认15） | 全局所有线程，历史记录可配置大小 |
| 细粒度 | 仅“阶段耗时” + 少量CPU/IO | 语句、阶段、等待事件、锁、内存、事务全链路 |
| 是否影响性能 | 轻微影响 | 极低（可忽略） |
| MySQL 8.0 支持 | 已弃用，不推荐 | 官方推荐替代方案 |
| 可查询性 | 只能看原始输出 | 可以用 SQL 任意过滤、聚合、关联分析 |
一句话总结：
SHOW PROFILES是“手电筒”，performance_schema是“CT 扫描仪”。
三、核心表分类（常用）
我将常用表按功能分层，方便你理解：
1. 语句级别（最常用）
| 表名 | 作用 |
|---|---|
events_statements_current |
当前正在执行的语句 |
events_statements_history |
当前线程最近执行的语句（默认10条） |
events_statements_history_long |
全局所有线程的历史语句（条数可调，如1000条） |
字段示例：SQL_TEXT, TIMER_WAIT（耗时，皮秒）, ROWS_EXAMINED, ROWS_SENT, CREATED_TMP_TABLES, NO_INDEX_USED, LOCK_TIME
2. 阶段级别（替代 SHOW PROFILE）
| 表名 | 作用 |
|---|---|
events_stages_current |
当前执行的阶段 |
events_stages_history_long |
历史阶段记录 |
阶段值：如 stage/sql/optimizing、stage/sql/executing、stage/sql/Sending data
3. 等待事件（锁、IO、互斥等）
| 表名 | 作用 |
|---|---|
events_waits_current |
当前等待事件 |
events_waits_history_long |
历史等待事件 |
典型等待：
wait/io/table/sql/handler—— 表 IO 等待wait/lock/metadata/sql/mdl—— 元数据锁wait/innodb/row_lock—— InnoDB 行锁
4. 内存与连接
memory_summary_global_by_event_name：各模块内存使用threads：所有线程状态session_connect_attrs：连接属性（如程序名、客户端IP）
4.实战：如何用它替代 SHOW PROFILE 定位慢SQL
前置检查（8.0 默认已开启）
-- 查看是否启用
SELECT * FROM performance_schema.setup_consumers
WHERE NAME LIKE 'events_statements%history_long';
如果未开启，执行：
UPDATE performance_schema.setup_consumers
SET ENABLED='YES'
WHERE NAME='events_statement_history_long';
-- 同时开启阶段记录（用于分析各阶段耗时）
UPDATE performance_schema.setup_consumers
SET ENABLED='YES'
WHERE NAME='events_stages_history_long';
步骤1：找出最耗时的 5 条 SQL
SELECT
THREAD_ID,
EVENT_ID,
TRUNCATE(TIMER_WAIT/1000000000, 3) AS duration_ms,
SQL_TEXT,
ROWS_EXAMINED,
ROWS_SENT,
NO_INDEX_USED,
CREATED_TMP_TABLES
FROM performance_schema.events_statements_history_long
WHERE SQL_TEXT NOT LIKE '%performance_schema%'
AND SQL_TEXT NOT LIKE '%information_schema%'
ORDER BY TIMER_WAIT DESC
LIMIT 5;
步骤2：查看某个 SQL 的各个阶段耗时（等价于 SHOW PROFILE）
-- 拿到上一步的 EVENT_ID（假设为 12345）和 THREAD_ID（假设为 678）
SELECT
EVENT_NAME AS stage_name,
TRUNCATE(TIMER_WAIT/1000000000, 3) AS stage_ms
FROM performance_schema.events_stages_history_long
WHERE THREAD_ID = 678
AND PARENT_EVENT_ID = 12345
ORDER BY TIMER_WAIT DESC;
输出示例：
stage_name | stage_ms
---------------------------------|----------
stage/sql/Sending data | 245.120
stage/sql/optimizing | 1.234
stage/sql/preparing | 0.856
stage/sql/statistics | 0.432
步骤3：查看该 SQL 的锁等待情况
SELECT
EVENT_NAME AS wait_type,
TRUNCATE(TIMER_WAIT/1000000000, 3) AS wait_ms,
SOURCE,
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME
FROM performance_schema.events_waits_history_long
WHERE THREAD_ID = 678
AND PARENT_EVENT_ID = 12345
ORDER BY TIMER_WAIT DESC;
步骤4：关联查看是否全表扫描或临时表
SELECT
SQL_TEXT,
NO_INDEX_USED, -- 1 表示未使用索引
NO_GOOD_INDEX_USED, -- 1 表示索引效率极差
CREATED_TMP_TABLES, -- 是否创建了临时表
CREATED_TMP_DISK_TABLES -- 是否创建了磁盘临时表（性能大坑）
FROM events_statements_history_long
WHERE EVENT_ID = 12345;
5.高级用法（组合诊断）
案例：定位是“锁等待”还是“数据量大”
-- 找出那些执行时间长，且大量时间消耗在“等待”上的 SQL
SELECT
s.SQL_TEXT,
TRUNCATE(s.TIMER_WAIT/1000000000, 3) AS total_ms,
TRUNCATE(SUM(w.TIMER_WAIT)/1000000000, 3) AS wait_total_ms,
ROUND(SUM(w.TIMER_WAIT)/s.TIMER_WAIT * 100, 2) AS wait_pct
FROM events_statements_history_long s
JOIN events_waits_history_long w ON w.THREAD_ID = s.THREAD_ID
AND w.PARENT_EVENT_ID = s.EVENT_ID
WHERE s.SQL_TEXT NOT LIKE '%performance_schema%'
GROUP BY s.EVENT_ID
HAVING wait_pct > 50 -- 等待时间占比超过50%，说明是锁或IO瓶颈
ORDER BY total_ms DESC;
6.关键配置参数（可调）
| 参数 | 作用 | 推荐值 |
|---|---|---|
performance_schema_consumer_events_statements_history_long_size |
全局历史记录条数 | 1000 ~ 10000（生产建议 5000） |
performance_schema_consumer_events_stages_history_long_size |
阶段历史记录条数 | 1000 ~ 5000 |
performance_schema_max_thread_instances |
最大监控线程数 | 默认足够，大并发库可调大 |
修改方式（动态）：
SET GLOBAL performance_schema_consumer_events_statements_history_long_size = 5000;
7.最佳实践建议
- 生产环境长期开启，性能影响极小，便于随时回溯问题。
- 不要直接用
performance_schema做实时告警，它适合事后分析，告警请用sys库（它封装了performance_schema）。 - 配合
sys库使用更简便，例如：-- sys 库提供更友好的视图 SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 5; - 定期清理或截断历史（重启即清空），无需手动维护。
| 场景 | 使用方案 |
|---|---|
| 临时调试单个SQL，MySQL 5.5/5.6 老环境 | SHOW PROFILES（快速） |
| 生产环境长期监控，MySQL 5.7+/8.0 | performance_schema（强烈推荐） |
| 需要直观的报表、趋势、摘要 | sys 库（基于 performance_schema 封装） |
| 需要分析锁、事务、内存、IO 等综合问题 | 只能用 performance_schema |
典型问题信号
| 输出内容 | 问题 | 解决方向 |
|---|---|---|
type = ALL |
全表扫描 | 建立合适的索引 |
Extra = Using filesort |
需要额外排序（非索引排序） | 在 ORDER BY 字段上建索引 |
Extra = Using temporary |
创建了临时表（常见于 GROUP BY、DISTINCT） |
优化分组/去重逻辑，或建索引 |
rows 巨大 |
索引区分度低或走错索引 | 调整索引或强制使用索引 |
key = NULL |
没用到任何索引 | 检查查询条件是否索引失效（如函数、隐式类型转换） |
2.5.4 explain
1.简单使用
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行
过程中表如何连接和连接的顺序。
explain SELECT * from qd_head head inner join qd_list list on head.ID=list.HEAD_ID;
2.关键参数
| 字段 | 含义 | 关注点 |
|---|---|---|
| type | 访问类型，从好到差依次为：system > const > eq_ref > ref > range > index > ALL |
如果是 ALL（全表扫描），必须优化 |
| possible_keys | 优化器考虑可能使用的索引 | 如果为 NULL，说明没有可用索引 |
| key | 实际选择的索引 | 如果与 possible_keys 不一致，需要分析原因 |
| rows | 预估扫描的行数 | 数字越大越危险，是优化的核心指标 |
| Extra | 额外信息 | 出现 Using filesort 或 Using temporary 说明有严重的性能隐患，需重点优化 |
进阶用法
1.EXPLAIN FORMAT = JSON
将传统的表格形式执行计划，输出为结构化 JSON 文档
基础语法
EXPLAIN FORMAT = JSON
SELECT
{
"query_block": {
"select_id": 1, // 查询块ID
"cost_info": {
"query_cost": "125.87" // 整个查询的总估算成本（重要！）
},
"nested_loop": [ // 嵌套循环连接
{
"table": {
"table_name": "b",
"access_type": "range", // 访问类型
"possible_keys": ["idx_year"],
"key": "idx_year",
"key_length": "4",
"rows_examined_per_scan": 3450, // 预估扫描行数
"rows_produced_per_join": 3450,
"filtered": "100.00",
"cost_info": {
"read_cost": "98.34",
"eval_cost": "6.90",
"prefix_cost": "105.24", // 当前操作累计成本
"data_read_per_join": "2M" // 数据读取量预估
},
"used_columns": ["id","title","cat_id","publish_year"]
}
},
{
"table": {
"table_name": "c",
"access_type": "eq_ref", // 对第二张表是 eq_ref（基于主键关联）
"key": "PRIMARY",
"rows_examined_per_scan": 1,
"cost_info": {
"prefix_cost": "125.87" // 最终总成本
}
}
}
]
}
}
| 普通 EXPLAIN | JSON 格式补充的信息 |
|---|---|
只显示 rows（扫描行数） |
额外显示 read_cost（IO成本）、eval_cost（CPU成本） |
| 无法直观看到成本占比 | 通过 prefix_cost 看出哪个表连接消耗最大 |
| 多表连接只有执行顺序 | 通过 nested_loop 清晰展示嵌套逻辑和每步成本递增 |
| 不显示数据量 | 提供 data_read_per_join（预估读取数据量） |
2.EXPLAIN ANALYZE
真正执行 SQL，并在执行过程中埋点计时，输出每个操作的：
-
实际执行时间（毫秒级）
-
实际返回行数
-
实际循环次数（对嵌套循环操作尤为重要）
基础语法
EXPLAIN ANALYZE SELECT
2.6 索引使用
2.6.1 最左前缀法则
前提：联合索引
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始（与书写顺序无关），
并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效，用explain可根据索引使用长度判断)。
假设索引为 (a, b, c)
| SQL 条件 | 索引使用情况 | 说明 |
|---|---|---|
WHERE a=1 AND c=3 |
仅用到 a | 跳过了 b，b 和 c 的索引都失效。虽然 c 在条件里，但因为 b 断了，c 无法被用于缩小范围（只能走回表过滤）。 |
WHERE a=1 AND b>2 AND c=3 |
用到 a 和 b | 范围查询（>）会导致 b 之后的 c 失效。这是非常常见的误区，以为 c 也在索引中。 |
WHERE a=1 AND b IN (2,3) AND c=4 |
用到 a、b、c | 注意：IN 在某些情况下被视为等值查询，不破坏最左前缀，所以三个字段都可能被用到。 |
2.6.2 范围查询
联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。
字段A、B、C、联合索引 where A= and B> and C=
则A、B走了索引C没有走索引 。
注 当范围查询使用>= 或 <= 时，A、B、C都走联合索引了。
2.6.3 索引失效情况
注意联合索引的最左匹配
1）不要在索引列上进行运算操作（注意运算并只有加减，还有截取等）， 索引将失效。
2）字符串类型字段使用时，不加引号，索引将失效。
3）如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
4）用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会
被用到。
5）如果MySQL评估使用索引比全表更慢，则不使用索引。（如索引重复占一大半）
6）is null 与 is not null 操作是否走索引 其实就是根据数据null数量才考虑是否走索引
2.6.4 多个索引情况
注：explain只是方便观看索引使用情况。
1). use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进
行评估）。
explain select * from tb_user use index(idx_user_pro) where profession = '软件工
程';
2). ignore index ： 忽略指定的索引。
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工
程';
3）force index ： 强制使用索引。
explain select * from tb_user force index(idx_user_pro) where profession = '软件工
程';
2.6.5 覆盖索引
尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢？ 覆盖索引是指 查询使用了索引，并
且需要返回的列，在该索引中已经全部能够找到 。
即 select返回字段必须是索引有的字段，否则要回表
| Extra | 含义 |
|---|---|
| Using where; UsingIndex | 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据 |
| Using indexcondition | 查找使用了索引，但是需要回表查询数据 |
2.6.6 前缀索引
当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让
索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建
立索引，这样可以大大节约索引空间，从而提高索引效率。
1). 语法
--n为截取字符串的长度
create index idx_xxxx on table_name(column(n)) ;
2). 前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，
索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
create index idx_email_5 on tb_user(email(5));
-- 查出截取前几个准确率最高
select count(distinct substring(email,1,5)) / count(*) from tb_user ;
2.7 索引设计原则
1). 针对于数据量较大（百万），且查询比较频繁的表建立索引。
2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索
引。
3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，
避免回表，提高查询效率。
6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增
删改的效率。
create unique index idx_user_phone_name on tb_user(phone,name); 1
7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含
NULL值时，它可以更好地确定哪个索引最有效地用于查询。
三. SQL优化
3.1 插入数据
3.1.1 insert
1）优化1
一次插入多条
Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
2）优化2
手动控制事务然后再多次插入
3）优化3
主键顺序插入，性能要高于乱序插入。
3.1.2 大批量插入数据
如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使
用MySQL数据库提供的load指令进行插入。
可以执行如下指令，将数据脚本文件中的数据加载到表结构中：
-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
--创建表结构
create table ......
-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields
terminated by ',' lines terminated by '\n' ;
-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- load加载数据
load data local infile '/root/load_user_100w_sort.sql' into table tb_user
fields terminated by ',' lines terminated by '\n' ;
3.2 主键优化
在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。
那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不够，将会存储
到下一个页中，页与页之间会通过指针连接。
页分裂
数据存储不了时进行。
A. 主键顺序插入效果
①. 从磁盘中申请页， 主键顺序插入
②. 第一个页没有满，继续往第一页插入
③. 当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接
④. 当第二页写满了，再往第三页写入
B. 主键乱序插入效果
①. 加入1#,2#页都已经写满了。
但是数据按照顺序是第一页中的数据
②. 此时第一页会取中间值把右边的数据放在新的页
③.再把数据插入到指定的页上面
④那么此时，这三个页之间的数据顺序是有问题的。 1#的下一个页，应该是3#， 3#的下一个页是2#。 所以，此时，需要重新设置链表指针。
页合并
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间
变得允许被其他记录声明使用。
页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前
或后）看看是否可以将两个页合并以优化空间使用。
注： MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。
索引设计原则
满足业务需求的情况下，尽量降低主键的长度。
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
业务操作时，避免对主键的修改。
3.3 order by优化
MySQL的排序，有两种方式：
Using filesort:
通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sortbuffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using index :
通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。
对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序
操作时，尽量要优化为 Using index。
Backward index scan，这个代表反向扫描索引，因为在MySQL中我们创建的索引，默认索引的叶子节点是从小到大排序的，而此时我们查询排序时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan。 在MySQL8版本中，支持降序索引，我们也可以创建降序索引。
因为创建索引时，如果未指定顺序，默认都是按照升序排序的，而查询时，一个升序，一个降序，此时
就会出现Using filesort。
创建联合索引(age 升序排序，phone 倒序排序)
create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);
注:要满足最左匹配 此时有 条件，此时顺序是必要的。
order by优化原则:
A. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
B. 尽量使用覆盖索引。
C. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
D. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小
sort_buffer_size(默认256k)。
3.4 group by优化
在分组操作中，需要通过以下两点进行优化，以提升性能：
A. 在分组操作时，可以通过索引来提高效率。
B. 分组操作时，索引的使用也是满足最左前缀法则的
3.5 limit优化
在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。
优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查
询形式进行优化
3.6 count优化
3.6.1 概述
MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count() 的时候会直接返回这个
数，效率很高； 但是如果是带条件的count，MyISAM也慢。
InnoDB 引擎就麻烦了，它执行 count() 的时候，需要把数据一行一行地从引擎里面读出
来，然后累积计数。
可以采用redis，但是带条件的SQL又比较麻烦，而且每次insert 和delete的时候都会修改redis
3.6.2 count用法
count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是
NULL，累计值就加 1，否则不加，最后返回累计值。
用法：count（*）、count（主键）、count（字段）、count（数字）
| count用法 | 含义 |
|---|---|
| count(主键) | InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null) |
| count(字段) | 没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。 |
| count(数字) | InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。 |
| count(*) | InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。 |
按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count()，所以尽量使用 count()。
3.7 update优化
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。