跳到主要内容

MySQL 索引原理与优化

索引是数据库性能优化的核心手段。本章将深入讲解 MySQL 索引的底层原理、InnoDB 存储引擎的索引实现、索引设计策略以及优化技巧。理解索引的本质，才能写出高效的 SQL 语句。

索引的本质​

什么是索引？​

索引是数据库中用于加速数据检索的数据结构。打个比方：如果表是一本书，索引就是书的目录。没有目录时，你要找某个内容只能从第一页翻到最后一页；有了目录，你可以快速定位到目标页码。

为什么需要索引？​

没有索引时会发生什么？

假设 users 表有 100 万行数据，执行以下查询：

SELECT * FROM users WHERE email = '[email protected]';

MySQL 必须从第一行开始，逐行扫描整张表，比较每行的 email 值。这被称为全表扫描（Full Table Scan），时间复杂度是 O(N)，当数据量很大时性能极差。

有了索引后呢？

如果在 email 列上建立了索引，MySQL 可以利用 B+Tree 的有序性，通过二分查找快速定位目标数据。时间复杂度从 O(N) 降到 O(log N)，性能提升几个数量级。

索引的代价​

索引不是免费的午餐，它带来性能提升的同时也有代价：

代价类型具体影响
存储空间每个索引都需要占用磁盘空间
写入性能INSERT、UPDATE、DELETE 需要同步维护索引
内存占用索引页会被加载到 Buffer Pool 中
维护成本需要定期分析和优化索引

因此，索引不是越多越好，需要根据实际查询需求精心设计。

B+Tree 索引结构​

MySQL 的 InnoDB 存储引擎使用 B+Tree 作为索引的底层数据结构。理解 B+Tree 是掌握 MySQL 索引的关键。

为什么选择 B+Tree？​

常见的查找数据结构有数组、链表、二叉搜索树、哈希表等，为什么数据库选择了 B+Tree？

二叉搜索树的问题：

当数据有序插入时，二叉搜索树会退化为链表，查找效率从 O(log N) 退化为 O(N)。即使使用 AVL 树或红黑树保持平衡，树的高度仍然较大，导致磁盘 I/O 次数多。

B+Tree 的优势：

  • 矮胖树：每个节点可以有多个子节点，树的高度很低（通常 3-4 层就能存储千万级数据）
  • 磁盘友好：每个节点的大小设计为磁盘页大小（默认 16KB），一次 I/O 可以读取一个完整节点
  • 范围查询高效：叶子节点通过双向链表连接，范围查询只需要找到起始点，然后遍历链表即可

B+Tree 结构详解​

B+Tree 是一种多路平衡查找树，具有以下特点：

                        ┌─────────────────────────────┐
                        │      根节点（内部节点）       │
                        │   [20 | 40 | 60 | 80]       │
                        └───────┬───────┬───────┬──────┘
               ┌────────────────┼───────┼───────┼────────────────┐
               ▼                ▼       ▼       ▼                ▼
        ┌──────────┐     ┌──────────┐  ...  ┌──────────┐     ┌──────────┐
        │ 中间节点  │     │ 中间节点  │       │ 中间节点  │     │ 中间节点  │
        │[5|10|15] │     │[25|30|35]│       │[45|50|55]│     │[85|90|95]│
        └────┬─────┘     └────┬─────┘       └────┬─────┘     └────┬─────┘
             │                │                  │                │
             ▼                ▼                  ▼                ▼
        ┌─────────┐     ┌─────────┐        ┌─────────┐     ┌─────────┐
        │ 叶子节点 │◄───►│ 叶子节点 │◄─────►│ 叶子节点 │◄───►│ 叶子节点 │
        │[1-4]    │     │[5-9]    │        │[20-24]  │     │[25-29]  │
        └─────────┘     └─────────┘        └─────────┘     └─────────┘
             │                │                  │                │
             ▼                ▼                  ▼                ▼
           数据            数据               数据             数据

内部节点（非叶子节点）：

  • 只存储键值和指向子节点的指针
  • 不存储实际数据
  • 起到索引作用，指导查找方向

叶子节点：

  • 存储键值和实际数据（或数据指针）
  • 所有叶子节点在同一层，保证查询效率稳定
  • 叶子节点通过双向链表连接，支持高效的范围查询

InnoDB 页结构​

InnoDB 将数据按页（Page）组织，默认页大小为 16KB。每个 B+Tree 节点就是一个页：

-- 查看页大小
SHOW VARIABLES LIKE 'innodb_page_size';
-- 输出: 16384 (16KB)

一个页可以存储多少条记录？

假设主键是 BIGINT（8 字节），指针是 6 字节，则一个内部节点页大约可以存储：

16384 字节 / (8 + 6) 字节 ≈ 1170 个键值

如果是 3 层的 B+Tree：

  • 第 1 层（根节点）：1 个页，约 1170 个键值
  • 第 2 层（中间节点）：1170 个页，约 1170 × 1170 ≈ 137 万个键值
  • 第 3 层（叶子节点）：约 137 万个页，假设每页存储 100 条记录，则可存储约 1.37 亿条记录

这就是为什么 3 层 B+Tree 就能存储上亿条数据，查询只需要 3 次 I/O。

页填充因子​

InnoDB 在插入记录时，会预留一定的空闲空间，以减少页分裂：

  • 顺序插入：页填充约 15/16（约 94%）
  • 随机插入：页填充约 1/2 到 15/16
  • 可通过 innodb_fill_factor 调整（默认 100，表示创建索引时尽量填满）

当页填充率低于 MERGE_THRESHOLD（默认 50%）时，InnoDB 会尝试合并页以释放空间。

聚簇索引与二级索引​

InnoDB 的索引分为聚簇索引和二级索引，理解它们的区别对于性能优化至关重要。

聚簇索引（Clustered Index）​

聚簇索引是 InnoDB 表的"主索引"，它决定了数据的物理存储顺序。

聚簇索引的特点：

  1. 数据与索引合一：叶子节点直接存储完整的行数据
  2. 每张表只有一个聚簇索引
  3. 数据按主键顺序存储：这也是为什么建议使用自增主键

聚簇索引的生成规则：

场景聚簇索引
定义了 PRIMARY KEY使用主键作为聚簇索引
没有主键，但有 NOT NULL 的 UNIQUE 键使用第一个 UNIQUE 键
都没有InnoDB 自动生成 6 字节的隐藏列 DB_ROW_ID

聚簇索引结构：

                    聚簇索引（PRIMARY KEY）
                           │
              ┌────────────┼────────────┐
              ▼            ▼            ▼
         ┌────────┐   ┌────────┐   ┌────────┐
         │ ID=1   │   │ ID=2   │   │ ID=3   │
         │ name=A │   │ name=B │   │ name=C │
         │ age=20 │   │ age=25 │   │ age=30 │
         │ ...    │   │ ...    │   │ ...    │
         └────────┘   └────────┘   └────────┘
           完整行数据    完整行数据    完整行数据

聚簇索引的优势：

  • 通过主键查询非常快（直接定位到数据页）
  • 范围查询效率高（数据物理连续存储）
  • 覆盖索引扫描可以直接返回数据

聚簇索引的劣势：

  • 二级索引需要"回表"查询
  • 主键过长会导致二级索引占用更多空间
  • 插入新行可能导致页分裂（如果主键不是递增的）

二级索引（Secondary Index）​

二级索引是除聚簇索引之外的所有索引，包括普通索引、唯一索引、复合索引等。

二级索引的结构：

二级索引的叶子节点存储的是：索引列的值 + 主键值，而不是数据的物理地址。

                    二级索引（INDEX name_age ON name, age）
                               │
                  ┌────────────┼────────────┐
                  ▼            ▼            ▼
             ┌────────┐   ┌────────┐   ┌────────┐
             │ name=A │   │ name=B │   │ name=C │
             │ age=20 │   │ age=25 │   │ age=30 │
             │ PK=1   │   │ PK=2   │   │ PK=3   │
             └────────┘   └────────┘   └────────┘
               ↓              ↓              ↓
            回表查询        回表查询        回表查询

为什么二级索引存储主键值？

如果二级索引存储物理地址，当行数据移动（如页分裂、更新导致行变长）时，需要更新所有指向该行的二级索引，代价巨大。存储主键值，虽然需要回表，但避免了这个问题。

回表查询：

通过二级索引查找数据的过程：

  1. 在二级索引 B+Tree 中查找，得到主键值
  2. 用主键值在聚簇索引 B+Tree 中查找，得到完整行数据

这就是"回表"——需要两次 B+Tree 查找。

-- 假设有索引 INDEX idx_name ON users(name)
SELECT * FROM users WHERE name = '张三';

-- 执行过程：
-- 1. 在 idx_name 索引中查找 name='张三'，得到主键 id=100
-- 2. 用 id=100 在聚簇索引中查找，得到完整行数据
-- 这就是回表查询

聚簇索引 vs 二级索引对比​

特性聚簇索引二级索引
叶子节点存储完整行数据索引列值 + 主键值
每表数量一个可以多个
查询方式直接返回数据可能需要回表
插入性能受主键顺序影响影响较小
空间占用等于表大小额外空间

主键设计建议​

根据聚簇索引的特性，主键设计应遵循以下原则：

1. 使用自增主键

-- 推荐：自增主键
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- 不推荐：UUID 作为主键
CREATE TABLE users (
    id CHAR(36) PRIMARY KEY,  -- UUID 随机无序
    name VARCHAR(50)
);

为什么 UUID 作为主键不好？

  • 插入顺序随机，导致频繁的页分裂
  • 页分裂会产生大量碎片，降低存储效率
  • 索引维护成本高

2. 主键尽量短

-- 推荐：整数类型
id INT AUTO_INCREMENT PRIMARY KEY

-- 不推荐：长字符串
id VARCHAR(100) PRIMARY KEY

主键越长，二级索引占用的空间越大，因为每个二级索引都存储主键值。

3. 主键不要更新

-- 不推荐：更新主键
UPDATE users SET id = 1000 WHERE id = 1;

更新主键会导致：

  • 聚簇索引中数据的物理移动
  • 所有二级索引的更新
  • 可能触发页分裂

索引类型​

按数据结构分类​

B+Tree 索引​

MySQL 最常用的索引类型，适用于大多数场景：

-- 创建 B+Tree 索引（默认类型）
CREATE INDEX idx_name ON users(name);

-- 显式指定 B+Tree
CREATE INDEX idx_name ON users(name) USING BTREE;

适用场景：

  • 全值匹配：WHERE name = '张三'
  • 范围查询：WHERE age > 20
  • 前缀匹配：WHERE name LIKE '张%'
  • 排序：ORDER BY name
  • 分组：GROUP BY name

Hash 索引​

基于哈希表实现，只支持 Memory 存储引擎：

-- Memory 表默认使用 Hash 索引
CREATE TABLE sessions (
    session_id VARCHAR(32) PRIMARY KEY,
    data TEXT,
    INDEX USING HASH (session_id)
) ENGINE = Memory;

特点：

  • 只支持等值查询（=, IN）
  • 不支持范围查询、排序
  • 查询效率 O(1)，但不稳定（哈希冲突）

全文索引（FULLTEXT）​

用于全文搜索，支持中文分词：

-- 创建全文索引（需要 ngram 解析器支持中文）
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_content (content) WITH PARSER ngram
);

-- 全文搜索
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE);

空间索引（SPATIAL）​

用于地理空间数据（GIS）：

CREATE TABLE locations (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    position POINT SRID 4326,
    SPATIAL INDEX idx_position (position)
);

-- 空间查询：查找距离某点 10km 内的位置
SELECT name, ST_Distance_Sphere(
    position, 
    ST_GeomFromText('POINT(116.4074 39.9042)', 4326)
) AS distance
FROM locations
WHERE ST_Distance_Sphere(
    position, 
    ST_GeomFromText('POINT(116.4074 39.9042)', 4326)
) < 10000;

按功能分类​

主键索引（PRIMARY KEY）​

特殊的唯一索引，不允许 NULL，每张表只能有一个：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

-- 或在表创建后添加
ALTER TABLE users ADD PRIMARY KEY (id);

唯一索引（UNIQUE）​

索引列的值必须唯一，允许 NULL：

-- 创建唯一索引
CREATE UNIQUE INDEX uk_email ON users(email);

-- 或在建表时定义
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    UNIQUE INDEX uk_phone (phone)
);

唯一索引 vs 主键：

特性主键唯一索引
NULL 值不允许允许
每表数量一个多个
是否为聚簇索引是（InnoDB）否

普通索引（INDEX）​

最基本的索引类型，无特殊约束：

CREATE INDEX idx_status ON users(status);

复合索引​

多列组合的索引，也叫组合索引或多列索引：

-- 创建复合索引
CREATE INDEX idx_name_age ON users(last_name, first_name, age);

复合索引的设计至关重要，详见后续"最左前缀原则"。

复合索引与最左前缀原则​

复合索引是多个列组合而成的索引，理解其工作原理是设计高效索引的关键。

最左前缀原则​

复合索引按照定义的列顺序构建，MySQL 会从索引的最左列开始匹配。

假设有索引 idx_abc (a, b, c)：

-- ✅ 可以使用索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3        -- 部分使用（只用 a）

-- ❌ 无法使用索引
WHERE b = 2                  -- 跳过了 a
WHERE c = 3                  -- 跳过了 a 和 b
WHERE b = 2 AND c = 3        -- 跳过了 a

为什么是这样？

索引 B+Tree 首先按第一列排序，第一列相同时按第二列排序，以此类推。就像电话簿：先按姓氏排序，同姓的再按名字排序。如果你只知道名字，无法利用电话簿的排序快速查找。

范围查询的影响​

当索引列中出现范围查询（>, <, >=, <=, BETWEEN, LIKE 前缀）时，该列之后的索引列无法使用：

-- 假设索引 idx_abc (a, b, c)

-- ✅ 三列都能使用
WHERE a = 1 AND b = 2 AND c = 3

-- ⚠️ 只有 a 和 b 能使用（a 是等值，b 是范围，c 无法使用）
WHERE a = 1 AND b > 2 AND c = 3

-- ⚠️ 只有 a 能使用（a 是范围，后面的都无法使用）
WHERE a > 1 AND b = 2 AND c = 3

复合索引设计策略​

1. 等值查询列放前面，范围查询列放后面

-- 查询：WHERE status = 'active' AND created_at > '2024-01-01'
-- 推荐
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 不推荐
CREATE INDEX idx_created_status ON orders(created_at, status);

2. 高选择性列放前面

选择性 = 不同值的数量 / 总行数，选择性越高，索引效果越好。

-- 查看列的选择性
SELECT 
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT user_id) / COUNT(*) AS user_selectivity
FROM orders;

-- 假设 user_id 选择性为 0.9，status 选择性为 0.1
-- 推荐：高选择性列放前面
CREATE INDEX idx_user_status ON orders(user_id, status);

3. 考虑排序和分组

如果经常按某列排序或分组，可以将其加入索引：

-- 查询：WHERE user_id = ? ORDER BY created_at DESC
CREATE INDEX idx_user_created ON orders(user_id, created_at);

这样索引本身有序，可以避免 filesort。

索引下推（Index Condition Pushdown）​

MySQL 5.6 引入的优化特性，可以在索引遍历过程中执行 WHERE 条件过滤，减少回表次数。

-- 假设有索引 idx_name_age (name, age)
SELECT * FROM users WHERE name LIKE '张%' AND age = 25;

没有索引下推时：

  1. 在索引中找到所有 name LIKE '张%' 的记录
  2. 回表查询完整行数据
  3. 过滤 age = 25 的记录

有索引下推时：

  1. 在索引遍历时直接过滤 age = 25
  2. 只对满足条件的记录回表

索引下推减少了回表次数，提高查询效率。可以在 EXPLAIN 的 Extra 字段看到 Using index condition。

覆盖索引​

当查询的所有列都包含在索引中时，MySQL 可以直接从索引返回数据，无需回表，这就是覆盖索引。

覆盖索引的优势​

-- 假设有索引 idx_name_email (name, email)

-- ✅ 覆盖索引查询
SELECT name, email FROM users WHERE name = '张三';
-- EXPLAIN Extra: Using index

-- ❌ 需要回表（查询了 id 列）
SELECT id, name, email FROM users WHERE name = '张三';
-- EXPLAIN Extra: NULL

覆盖索引的好处：

  • 避免回表，减少 I/O 操作
  • 索引通常比数据小，更容易被缓存
  • 查询效率显著提升

如何判断是否使用了覆盖索引？​

使用 EXPLAIN 查看 Extra 字段：

EXPLAIN SELECT name, email FROM users WHERE name = '张三';

-- Extra: Using index  ← 表示使用了覆盖索引

设计覆盖索引​

-- 原始查询
SELECT user_id, SUM(amount) 
FROM orders 
WHERE status = 'completed' 
GROUP BY user_id;

-- 设计覆盖索引
CREATE INDEX idx_status_user_amount ON orders(status, user_id, amount);

这个索引可以实现：

  1. 快速过滤 status = 'completed'
  2. 按 user_id 分组（索引有序）
  3. 直接从索引读取 amount 求和
  4. 完全避免回表

索引失效场景​

即使创建了索引，某些情况下 MySQL 也无法使用索引。了解这些场景可以避免写出低效的 SQL。

1. 在索引列上使用函数或计算​

-- ❌ 索引失效：在索引列上使用函数
WHERE YEAR(created_at) = 2024
WHERE DATE(created_at) = '2024-01-01'
WHERE LOWER(name) = 'zhangsan'

-- ✅ 索引有效：改写条件
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
WHERE created_at >= '2024-01-01 00:00:00' 
  AND created_at <= '2024-01-01 23:59:59'
WHERE name = 'ZhangSan' OR name = 'zhangsan'

函数索引（MySQL 8.0+）：

如果确实需要在函数结果上建立索引，可以使用函数索引：

-- 创建函数索引
CREATE INDEX idx_year ON orders((YEAR(created_at)));

-- 或在建表时
CREATE TABLE orders (
    id INT PRIMARY KEY,
    created_at DATETIME,
    INDEX idx_year ((YEAR(created_at)))
);

2. 隐式类型转换​

-- ❌ 索引失效：phone 是 VARCHAR，传入数字触发隐式转换
WHERE phone = 13800138000

-- ✅ 索引有效：使用字符串
WHERE phone = '13800138000'

为什么会失效？

MySQL 会将字符串列转换为数字进行比较，相当于执行了 CAST(phone AS SIGNED)，这是一个函数操作。

3. LIKE 以通配符开头​

-- ❌ 索引失效：以 % 开头
WHERE name LIKE '%张'
WHERE name LIKE '%张%'

-- ✅ 索引有效：前缀匹配
WHERE name LIKE '张%'

前缀匹配为什么有效？

索引是按字典序排列的，前缀匹配可以利用 B+Tree 的有序性。而以 % 开头意味着要从任意位置开始匹配，无法利用索引顺序。

4. 使用 NOT、!=、<> 操作​

-- ⚠️ 可能索引失效
WHERE status != 'deleted'
WHERE status NOT IN ('deleted', 'cancelled')

-- ✅ 改写为 IN 或范围
WHERE status IN ('active', 'pending')
WHERE status = 'active' OR status = 'pending'

5. 使用 OR 连接非索引列​

-- 假设 name 有索引，age 没有索引
-- ❌ 索引失效
WHERE name = '张三' OR age = 25

-- ✅ 使用 UNION
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 25;

6. 索引列参与表达式计算​

-- ❌ 索引失效
WHERE age + 1 = 26
WHERE age * 2 &gt; 50

-- ✅ 索引有效
WHERE age = 25
WHERE age &gt; 25

7. 联合索引不满足最左前缀​

-- 假设索引 idx_abc (a, b, c)
-- ❌ 索引失效
WHERE b = 2 AND c = 3
WHERE c = 3

8. 全表扫描更快时​

当 MySQL 优化器判断全表扫描比使用索引更快时，会放弃使用索引：

  • 表数据量很小
  • 查询需要返回大部分数据（如超过 20%）
  • 索引选择性太低（如性别列只有两个值）
-- 强制使用索引（谨慎使用）
SELECT * FROM users FORCE INDEX (idx_name) WHERE name = '张三';

-- 忽略索引
SELECT * FROM users IGNORE INDEX (idx_name) WHERE name = '张三';

EXPLAIN 执行计划分析​

EXPLAIN 是分析 SQL 查询性能的关键工具，可以帮助理解 MySQL 如何使用索引。

基本使用​

EXPLAIN SELECT * FROM users WHERE name = '张三';

-- MySQL 8.0+ 可以使用更详细的格式
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';

输出字段详解​

+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+

type：访问类型​

type 表示 MySQL 如何查找数据，从好到差依次为：

类型说明性能
system单行系统表最优
const主键或唯一索引常量查询极优
eq_ref主键或唯一索引关联查询优秀
ref非唯一索引等值查询良好
fulltext全文索引良好
ref_or_null类似 ref，额外搜索 NULL良好
index_merge索引合并一般
range索引范围扫描一般
index全索引扫描较差
ALL全表扫描最差

重点关注：避免出现 ALL，尽量达到 ref 或更好。

-- const：主键常量查询
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type: const

-- ref：非唯一索引等值查询
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- type: ref

-- range：范围查询
EXPLAIN SELECT * FROM users WHERE age &gt; 25;
-- type: range

-- ALL：全表扫描（需要优化）
EXPLAIN SELECT * FROM users WHERE age + 1 = 26;
-- type: ALL

key 和 possible_keys​

  • possible_keys：MySQL 可能使用的索引列表
  • key：实际使用的索引
  • key_len：使用的索引长度（字节）
-- 假设有索引 idx_name_age (name, age)
EXPLAIN SELECT * FROM users WHERE name = '张三' AND age = 25;

-- possible_keys: idx_name_age
-- key: idx_name_age
-- key_len: 204  (假设 name VARCHAR(50) utf8mb4，age INT)

key_len 计算方法：

  • VARCHAR(n)：n × 4 + 2（utf8mb4 编码，最大 4 字节/字符，+2 是变长字段长度标记）
  • INT：4 字节
  • NULL 值需要额外 1 字节

rows：预估扫描行数​

MySQL 预估需要扫描的行数，这是一个估计值，基于索引统计信息。值越小越好。

-- 更新索引统计信息
ANALYZE TABLE users;

Extra：额外信息​

Extra 字段包含重要的执行细节：

值含义建议
Using index使用覆盖索引好
Using index condition使用索引下推好
Using where服务器层过滤一般
Using filesort额外排序操作需优化
Using temporary使用临时表需优化
Using join buffer使用连接缓冲可能需优化
Impossible WHEREWHERE 条件永远为假检查逻辑
-- Using filesort：需要优化
EXPLAIN SELECT * FROM users ORDER BY age;
-- Extra: Using filesort

-- 优化：为排序列创建索引
CREATE INDEX idx_age ON users(age);

-- Using temporary：需要优化
EXPLAIN SELECT DISTINCT status FROM users;
-- Extra: Using temporary

-- Using index：覆盖索引，好
EXPLAIN SELECT name FROM users WHERE name = '张三';
-- Extra: Using index

EXPLAIN ANALYZE（MySQL 8.0.18+）​

MySQL 8.0.18 引入了 EXPLAIN ANALYZE，它会实际执行查询并显示每个步骤的耗时：

EXPLAIN ANALYZE SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.name = '张三';

-- 输出示例：
-&gt; Nested loop inner join  (cost=1.15 rows=2) (actual time=0.032..0.042 rows=3 loops=1)
    -&gt; Index lookup on u using idx_name (name='张三')  (cost=0.35 rows=1) (actual time=0.019..0.020 rows=1 loops=1)
    -&gt; Index lookup on o using idx_user_id (user_id=u.id)  (cost=0.78 rows=2) (actual time=0.010..0.019 rows=3 loops=1)

这提供了实际的执行时间和行数，比普通 EXPLAIN 更直观。

索引创建与管理​

创建索引​

-- 方式一：CREATE INDEX
CREATE INDEX idx_name ON users(name);
CREATE UNIQUE INDEX uk_email ON users(email);
CREATE INDEX idx_name_age ON users(last_name, first_name, age);

-- 方式二：ALTER TABLE
ALTER TABLE users ADD INDEX idx_age (age);
ALTER TABLE users ADD UNIQUE INDEX uk_phone (phone);
ALTER TABLE users ADD PRIMARY KEY (id);

-- 方式三：建表时创建
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    age INT,
    INDEX idx_name (name),
    UNIQUE INDEX uk_email (email),
    INDEX idx_name_age (name, age)
);

前缀索引​

对长字符串列，可以只索引前面的字符，节省空间：

-- 创建前缀索引
CREATE INDEX idx_email_prefix ON users(email(10));

如何选择前缀长度？

-- 分析不同前缀长度的选择性
SELECT 
    COUNT(DISTINCT email) AS total,
    COUNT(DISTINCT LEFT(email, 5)) AS prefix_5,
    COUNT(DISTINCT LEFT(email, 10)) AS prefix_10,
    COUNT(DISTINCT LEFT(email, 15)) AS prefix_15,
    COUNT(DISTINCT LEFT(email, 20)) AS prefix_20
FROM users;

-- 选择选择性接近完整列的最短前缀

前缀索引的限制：

  • 不能用于 ORDER BY 和 GROUP BY
  • 不能作为覆盖索引

隐藏索引（MySQL 8.0+）​

隐藏索引不会被优化器使用，适合测试删除索引的影响：

-- 创建隐藏索引
CREATE INDEX idx_test ON users(test_column) INVISIBLE;

-- 切换可见性
ALTER TABLE users ALTER INDEX idx_test INVISIBLE;  -- 隐藏
ALTER TABLE users ALTER INDEX idx_test VISIBLE;    -- 显示

-- 查看索引是否隐藏
SHOW INDEX FROM users;
-- Visible 字段: YES/NO

使用场景：

  • 准备删除索引前，先隐藏测试影响
  • 调试查询性能，临时禁用某个索引

降序索引（MySQL 8.0+）​

MySQL 8.0 支持真正的降序索引：

-- 创建降序索引
CREATE INDEX idx_created_desc ON orders(created_at DESC);

-- 混合排序索引
CREATE INDEX idx_status_asc_created_desc ON orders(status ASC, created_at DESC);

-- 可以优化这类查询
SELECT * FROM orders 
WHERE status = 'active' 
ORDER BY created_at DESC;

函数索引（MySQL 8.0+）​

基于函数或表达式创建索引：

-- 基于函数创建索引
CREATE INDEX idx_year ON orders((YEAR(created_at)));

-- 基于表达式创建索引
CREATE INDEX idx_full_name ON users((CONCAT(first_name, ' ', last_name)));

-- JSON 路径索引
CREATE INDEX idx_data_name ON users((CAST(data-&gt;&gt;'$.name' AS CHAR(50))));

查看和删除索引​

-- 查看表的索引
SHOW INDEX FROM users;

-- 从 information_schema 查看
SELECT 
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    NON_UNIQUE,
    CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'users'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

-- 删除索引
DROP INDEX idx_name ON users;
ALTER TABLE users DROP INDEX idx_name;
ALTER TABLE users DROP PRIMARY KEY;  -- 删除主键

索引维护​

-- 分析表，更新索引统计信息
ANALYZE TABLE users;

-- 优化表，重建表和索引
OPTIMIZE TABLE users;

-- 检查表
CHECK TABLE users;

-- 强制重建表（修复索引碎片）
ALTER TABLE users ENGINE = InnoDB;

索引优化策略​

1. 为高频查询创建索引​

根据实际查询模式创建索引，而不是盲目创建：

-- 分析慢查询
SELECT * FROM mysql.slow_log 
ORDER BY query_time DESC 
LIMIT 10;

-- 为慢查询中的条件列创建索引
CREATE INDEX idx_status_user ON orders(status, user_id);

2. 选择合适的索引类型​

场景推荐索引类型
主键查询主键索引
唯一约束唯一索引
等值查询普通索引
多条件查询复合索引
范围查询B+Tree 索引
文本搜索全文索引
地理数据空间索引

3. 避免冗余索引​

冗余索引浪费空间并增加维护成本：

-- 冗余索引示例
CREATE INDEX idx_a ON users(a);
CREATE INDEX idx_a_b ON users(a, b);  -- idx_a 是冗余的

-- 删除冗余索引
DROP INDEX idx_a ON users;

4. 删除未使用的索引​

-- 查看索引使用情况（MySQL 5.7+）
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_READ,
    COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'mydb'
  AND INDEX_NAME IS NOT NULL
ORDER BY COUNT_READ DESC;

-- 查找未使用的索引
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
  AND COUNT_READ = 0
  AND OBJECT_SCHEMA = 'mydb';

5. 控制索引数量​

索引不是越多越好，一般建议：

  • 单表索引数量不超过 5 个
  • 单个索引的列数不超过 5 列
  • 根据实际查询需求创建

6. 定期分析表​

-- 定期执行，更新统计信息
ANALYZE TABLE users;

-- 或使用事件定时执行
CREATE EVENT analyze_tables
ON SCHEDULE EVERY 1 WEEK
DO
    CALL analyze_all_tables();

实战案例​

案例 1：电商订单查询优化​

问题 SQL：

SELECT * FROM orders 
WHERE user_id = 1001 
  AND status = 'completed'
ORDER BY created_at DESC 
LIMIT 10;

执行计划分析：

EXPLAIN SELECT * FROM orders 
WHERE user_id = 1001 AND status = 'completed'
ORDER BY created_at DESC LIMIT 10;

-- type: ALL, Extra: Using where; Using filesort
-- 全表扫描 + 文件排序，性能很差

优化方案：

-- 创建复合索引
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at DESC);

-- 执行计划
-- type: ref, Extra: Using index condition
-- 使用索引，避免全表扫描和文件排序

索引设计解释：

  • user_id 放第一位：等值查询，快速过滤
  • status 放第二位：等值查询，进一步过滤
  • created_at DESC 放第三位：支持降序排序，避免 filesort

案例 2：覆盖索引优化​

问题 SQL：

-- 统计每个用户的订单数量
SELECT user_id, COUNT(*) 
FROM orders 
WHERE status = 'completed'
GROUP BY user_id;

优化前：

-- 假设只有 idx_status (status)
EXPLAIN SELECT user_id, COUNT(*) 
FROM orders WHERE status = 'completed' GROUP BY user_id;

-- type: ref, Extra: Using where; Using temporary; Using filesort
-- 使用临时表和文件排序

优化后：

-- 创建覆盖索引
CREATE INDEX idx_status_user ON orders(status, user_id);

EXPLAIN SELECT user_id, COUNT(*) 
FROM orders WHERE status = 'completed' GROUP BY user_id;

-- type: ref, Extra: Using index
-- 使用覆盖索引，无需回表

案例 3：大表分页优化​

问题 SQL：

-- 深分页查询，越往后越慢
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

问题分析：

MySQL 需要扫描前 1000010 行，然后丢弃前 1000000 行，效率极低。

优化方案 1：延迟关联

-- 先查 ID，再关联查完整数据
SELECT o.* 
FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 10
) t ON o.id = t.id;

优化方案 2：记录上次位置

-- 记住上次查询的最大 ID
SELECT * FROM orders WHERE id &gt; 1000000 ORDER BY id LIMIT 10;

案例 4：索引合并优化​

问题 SQL：

SELECT * FROM orders 
WHERE user_id = 1001 OR product_id = 2001;

执行计划：

EXPLAIN SELECT * FROM orders 
WHERE user_id = 1001 OR product_id = 2001;

-- type: index_merge
-- possible_keys: idx_user_id, idx_product_id
-- key: idx_user_id, idx_product_id
-- Extra: Using union(idx_user_id, idx_product_id); Using where

问题：索引合并不是最高效的方式，需要合并两个索引的结果。

优化方案：

-- 改写为 UNION
SELECT * FROM orders WHERE user_id = 1001
UNION
SELECT * FROM orders WHERE product_id = 2001;

-- 或者根据业务需求，创建复合索引

小结​

MySQL 索引是性能优化的核心技术，本章我们学习了：

核心概念：

  • 索引是加速数据检索的数据结构，本质是空间换时间
  • InnoDB 使用 B+Tree 作为索引结构，适合磁盘存储

索引类型：

  • 聚簇索引：数据与索引合一，每表一个，通常是主键
  • 二级索引：存储索引列 + 主键，查询可能需要回表
  • 复合索引：多列组合，遵循最左前缀原则

优化要点：

  • 为高频查询创建合适的索引
  • 复合索引设计：等值列在前，范围列在后
  • 利用覆盖索引减少回表
  • 避免索引失效场景

诊断工具：

  • EXPLAIN 分析执行计划
  • EXPLAIN ANALYZE 查看实际执行时间
  • performance_schema 监控索引使用

练习​

  1. 分析一张表的查询模式，设计合理的索引方案
  2. 使用 EXPLAIN 分析慢查询，找出索引失效原因
  3. 设计一个覆盖索引优化聚合查询
  4. 对比有索引和无索引的查询性能差异
  5. 实践索引下推和覆盖索引的使用

参考资料​