MySQL 的性能调优（第一部分）

关注

原创 2026-04-13 20:47:21 329 阅读 AI 写同款· GEO 优化 ›

MySQL 性能调优——SQL 优化随着业务的不断增长，数据库中的数据也会越来越多，数据库的压力会越来越大，我们会发现，在业务繁忙的时候，数据库的性能会直线下降，这时为了保证良好的性能，需要想办法分担数据库的压力。分担数据库的读负载可以使用主从复制的方式，增加只读从数据库，通过读写分离的方式把数据库的读负载分担到不同的从数据库中，这时在一段时间内已经可以解决问题了。随着业务的发展，会发现，单一的主数据库已经无法承担写负载了，那么这时就需要对单一的主数据库进行拆分了。阅读详情

一、什么是索引？

在关系型数据库中，索引是一种特殊的数据结构，它将数据按照特定规则进行预排序和组织，能够帮助数据库系统快速定位到目标数据记录，从而显著提升数据库表中数据的查找和访问速度。

生活中的类比

📚 书籍目录：快速找到特定章节
📁 文件标签：快速定位文件位置
🏢 房间编号：快速找到具体房间
🏷️ 商品标签：快速分类检索商品

核心思想

以空间换时间：通过额外的存储空间来存储索引数据，从而换取查询性能的指数级提升。

二、索引的分类体系

MySQL 中的索引是在存储引擎层实现的，不同存储引擎支持不同的索引类型。

1. 按数据结构分类

B+Tree索引（最重要，最常用）
Hash索引（Memory引擎支持）
Full-text索引（全文搜索）

2. 按物理存储分类

聚集索引（Clustered Index）：数据行与索引一起存储
非聚集索引（Non-clustered Index）：索引与数据行分开存储

3. 按字段特性分类

索引类型	关键字	特点	是否允许空值
主键索引	PRIMARY KEY	唯一标识，一张表只有一个	不允许
唯一索引	UNIQUE	确保列值唯一	允许一个NULL
普通索引	INDEX	最基本的索引类型	允许
全文索引	FULLTEXT	用于全文搜索	允许

4. 按字段个数分类

单列索引：只包含一个字段
联合索引（复合索引/组合索引）：包含多个字段

三、索引数据结构演进与对比

🌳 数据结构演进图谱

二叉树 → 红黑树 → B树 → B+树

1. 二叉树

graph TD
    A[50] --> B[30]
    A --> C[70]
    B --> D[20]
    B --> E[40]
    C --> F[60]
    C --> G[80]

特点：

每个节点最多两个子节点
左子节点 < 父节点 < 右子节点
数据随机时效率尚可，但有序插入时退化为链表

问题场景：当数据有序插入时（1,2,3,4,5...）

退化为链表，查询时间复杂度 O(n)

2. 红黑树（平衡二叉树）

优化点：

通过自旋保持平衡
避免极端情况下的链表化
树高度相对稳定

局限性：

每个节点仍然只有两个子节点
数据量大时，树的高度仍然很高
每层深度增加 = 磁盘I/O次数增加

3. B树（多路平衡查找树）

graph TD
    A[10, 20, 30] --> B[5, 8]
    A --> C[15, 18]
    A --> D[25, 28]
    A --> E[35, 40]

突破性改进：

一个节点可以有 M 个子节点（M>2）
每个节点存储多个键值
显著降低树的高度

3阶B树示例：

[20, 40]
        /    |    \
   [10,15] [25,30] [50,60]

4. B+树（MySQL的选择）

graph TD
    subgraph "非叶子节点（只存键）"
        A[P1: 10, P2: 20]
    end
    
    A --> B[P3: 5, P4: 8]
    A --> C[P5: 15, P6: 18]
    A --> D[P7: 25, P8: 28]
    
    subgraph "叶子节点（存数据+指针）"
        B1[5 → 数据A]
        B2[8 → 数据B]
        C1[15 → 数据C]
        C2[18 → 数据D]
        D1[25 → 数据E]
        D2[28 → 数据F]
    end
    
    B --> B1
    B --> B2
    C --> C1
    C --> C2
    D --> D1
    D --> D2
    
    B1 --> B2
    B2 --> C1
    C1 --> C2
    C2 --> D1
    D1 --> D2

四、为什么MySQL选择B+树？

B树 vs B+树核心区别

特性	B树	B+树
非叶子节点存储	键值 + 数据	仅键值（指针）
叶子节点存储	键值 + 数据	键值 + 数据
叶子节点连接	无连接	双向链表连接
数据位置	所有节点	仅叶子节点
查找稳定性	不稳定	稳定（都要到叶子节点）
空间利用率	较低	更高

B+树的三大优势

✅ 1. 更矮的树，更少的磁盘I/O

假设：
- 每个节点大小为16KB
- 每个指针8字节
- 主键为bigint（8字节）

B+树非叶子节点容量 = 16KB / (8+8) ≈ 1024个键值
三层B+树可存储 = 1024² ≈ 100万条记录

✅ 2. 高效的范围查询

-- 查找年龄20-30的用户
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

由于叶子节点是双向链表连接，只需要找到起始点，然后顺序遍历即可。

✅ 3. 排序和分组优化

-- 按年龄排序
SELECT * FROM users ORDER BY age;

-- 按城市分组
SELECT city, COUNT(*) FROM users GROUP BY city;

索引本身有序，天然支持排序和分组操作。

五、面试精华：深度问题解析

❓ 高频面试题1：为什么B+树比B树更适合数据库索引？

参考答案：

更高的扇出，更少的I/O：B+树非叶子节点只存键值，不存数据，所以每个节点能存储更多键值，树更矮
范围查询优势：叶子节点双向链表，范围查询效率极高
查询稳定：每次查询都要到叶子节点，时间复杂度稳定在O(log n)
更适合磁盘存储：充分利用局部性原理，减少磁盘随机I/O

❓ 高频面试题2：如果没有主键索引，还会创建B+树吗？

深度解析：

-- 示例：创建无主键表
CREATE TABLE no_pk_table (
    id INT,
    name VARCHAR(50),
    age INT
) ENGINE=InnoDB;

答案是：会的！

InnoDB的实现机制：

隐藏主键：如果表没有显式定义主键，InnoDB会自动创建一个6字节的隐藏主键DB_ROW_ID
强制索引：InnoDB要求每个表必须有聚集索引
回退选择：
- 优先使用主键
- 没有主键则使用第一个非空的唯一索引
- 都没有则使用隐藏的DB_ROW_ID创建聚集索引

-- 验证隐藏主键
SHOW CREATE TABLE no_pk_table;
-- 或
SELECT * FROM information_schema.INNODB_SYS_TABLES 
WHERE NAME LIKE '%no_pk_table%';

六、实战优化建议

1. 索引设计原则

-- ✅ 好的设计
CREATE INDEX idx_name_age ON users(name, age);

-- ❌ 避免过度索引
-- 每个索引都需要维护，增删改时会降低性能

2. 联合索引最左匹配原则

-- 索引: (a, b, c)
WHERE a = 1 AND b = 2 AND c = 3  -- ✅ 全用
WHERE a = 1 AND b = 2           -- ✅ 用a,b
WHERE a = 1                    -- ✅ 用a
WHERE b = 2 AND c = 3          -- ❌ 无法用索引
WHERE b = 2                    -- ❌ 无法用索引