跳至主要內容

MySQL 索引详解

HSXISAWD2026/3/20大约 15 分钟数据库MySQL索引B+树性能优化

MySQL 索引详解

一、索引概述

1.1 什么是索引

索引是帮助 MySQL 高效获取数据的数据结构。可以类比为书籍的目录，通过目录可以快速定位到想要的内容，而不需要从头到尾翻阅整本书。

1.2 没有索引的查询

-- 假设 users 表有 1000 万行数据，没有索引
SELECT * FROM users WHERE username = 'john';

-- 执行过程：全表扫描
-- 从第 1 行开始，逐行比较 username 是否等于 'john'
-- 最坏情况需要扫描 1000 万行
-- 时间复杂度：O(n)

1.3 有索引的查询

-- 在 username 列上创建索引后
SELECT * FROM users WHERE username = 'john';

-- 执行过程：索引查找
-- 通过 B+ 树快速定位到 username = 'john' 的记录
-- 假设树高为 3，只需要 3 次磁盘 I/O
-- 时间复杂度：O(log n)

1.4 索引的优缺点

优点缺点
加快数据检索速度占用额外存储空间
加快分组和排序降低增删改的速度
加速表连接需要维护成本
通过唯一索引保证数据唯一性索引设计不当反而影响性能

二、索引数据结构

2.1 为什么选择 B+ 树

MySQL 索引为什么使用 B+ 树而不是其他数据结构？

数据结构优点缺点适用场景
数组随机访问 O(1)插入删除 O(n)静态数据
链表插入删除 O(1)查找 O(n)频繁修改
Hash等值查询 O(1)不支持范围查询、排序等值查询场景
二叉搜索树查找 O(log n)可能退化为链表内存数据
红黑树平衡、查找 O(log n)树高较大，I/O 次数多内存数据
B 树多路平衡，树高低非叶子节点也存数据文件系统
B+ 树叶子节点有序链表，范围查询高效写入时需要维护平衡数据库索引

2.2 B+ 树结构详解

B+ 树特点：

  1. 非叶子节点只存储键值和指针，不存储数据
  2. 所有数据都存储在叶子节点
  3. 叶子节点之间通过双向链表连接
  4. 所有叶子节点在同一层

数据页内部结构（16KB）：

2.3 B+ 树查找过程

查找过程：

  1. 读取根节点 [30|60]：30 < 45 < 60，走中间分支
  2. 读取节点 [40|50]：40 < 45 < 50，走中间分支
  3. 读取叶子节点，找到 45

磁盘 I/O 次数 = 树的高度 = 3 次

2.4 为什么 B+ 树矮胖

假设：

  • 每个数据页 16KB
  • 主键 BIGINT 8 字节
  • 指针 6 字节
  • 每行数据 1KB
非叶子节点可存储的键值数：
16KB / (8B + 6B) ≈ 1170 个

叶子节点可存储的记录数：
16KB / 1KB = 16 条

B+ 树存储能力：
高度为 2：1170 * 16 ≈ 1.8 万条
高度为 3：1170 * 1170 * 16 ≈ 2000 万条
高度为 4：1170 * 1170 * 1170 * 16 ≈ 200 亿条

结论：
- 高度为 3 的 B+ 树可以存储约 2000 万条数据
- 查询任何数据最多 3 次磁盘 I/O

三、InnoDB 索引类型

3.1 聚簇索引（Clustered Index）

聚簇索引的叶子节点存储的是完整的数据行。

-- InnoDB 表的聚簇索引规则：
-- 1. 如果有主键，使用主键作为聚簇索引
-- 2. 如果没有主键，使用第一个非空唯一索引
-- 3. 如果都没有，InnoDB 自动生成一个隐藏的 row_id 作为聚簇索引

CREATE TABLE users (
    id BIGINT PRIMARY KEY,      -- 聚簇索引
    username VARCHAR(50),
    email VARCHAR(100),
    age INT
);

聚簇索引结构：

3.2 二级索引（Secondary Index）

二级索引的叶子节点存储的是主键值，而不是完整数据。

-- 在 username 上创建二级索引
CREATE INDEX idx_username ON users(username);

二级索引结构：

3.3 回表查询

通过二级索引查询时，需要先找到主键，再通过主键到聚簇索引查找完整数据。

-- 回表查询示例
SELECT * FROM users WHERE username = 'john';

执行过程：
1. 在 idx_username 索引中查找 'john'，得到 id = 2
2. 拿着 id = 2 去聚簇索引查找完整数据
3. 返回结果

总共访问了两棵 B+ 树，这就是"回表"

3.4 覆盖索引（Covering Index）

如果查询的列都在索引中，就不需要回表，这叫覆盖索引。

-- 创建复合索引
CREATE INDEX idx_username_age ON users(username, age);

-- 覆盖索引查询（不需要回表）
SELECT username, age FROM users WHERE username = 'john';

-- 执行计划中会显示 Using index
EXPLAIN SELECT username, age FROM users WHERE username = 'john';
+----+-------------+-------+------+------------------+---------+---------+
| id | select_type | table | type | key              | Extra   |         |
+----+-------------+-------+------+------------------+---------+---------+
| 1  | SIMPLE      | users | ref  | idx_username_age | Using index       |
+----+-------------+-------+------+------------------+---------+---------+

-- 需要回表的查询（查询 email 不在索引中）
SELECT username, email FROM users WHERE username = 'john';

四、复合索引与最左前缀原则

4.1 复合索引结构

CREATE INDEX idx_a_b_c ON t(a, b, c);

复合索引的排序规则：先按 a 排序，a 相同时按 b 排序，b 相同时按 c 排序。

复合索引 (a, b, c) 的 B+ 树叶子节点数据排列：

(1, 1, 1) → (1, 1, 2) → (1, 2, 1) → (1, 2, 3) → (2, 1, 1) → (2, 1, 2) → ...

观察规律：
- a 是全局有序的
- b 在 a 确定后是有序的
- c 在 a, b 都确定后是有序的

4.2 最左前缀原则

-- 索引 idx_a_b_c (a, b, c)

-- ✅ 可以使用索引
WHERE a = 1                    -- 使用 a
WHERE a = 1 AND b = 2          -- 使用 a, b
WHERE a = 1 AND b = 2 AND c = 3  -- 使用 a, b, c
WHERE a = 1 AND c = 3          -- 只使用 a（c 无法使用）
WHERE a = 1 AND b > 2          -- 使用 a, b
WHERE a = 1 AND b > 2 AND c = 3  -- 使用 a, b（c 无法使用，b 是范围查询）

-- ❌ 无法使用索引
WHERE b = 2                    -- 缺少 a，无法使用
WHERE c = 3                    -- 缺少 a, b，无法使用
WHERE b = 2 AND c = 3          -- 缺少 a，无法使用

4.3 复合索引设计原则

-- 原则1：将选择性高的列放前面
-- 选择性 = 不重复的值数量 / 总行数

SELECT
    COUNT(DISTINCT status) / COUNT(*) as status_selectivity,
    COUNT(DISTINCT user_id) / COUNT(*) as user_id_selectivity
FROM orders;

-- 如果 user_id 选择性 > status 选择性
-- 应该创建 INDEX (user_id, status) 而不是 INDEX (status, user_id)


-- 原则2：考虑排序和分组需求
-- 如果经常需要 ORDER BY create_time
CREATE INDEX idx_user_time ON orders(user_id, create_time);

SELECT * FROM orders WHERE user_id = 1 ORDER BY create_time;
-- 可以利用索引排序，避免 filesort


-- 原则3：考虑覆盖索引
-- 如果查询经常只需要 username 和 age
CREATE INDEX idx_username_age ON users(username, age);

SELECT username, age FROM users WHERE username LIKE 'john%';
-- 覆盖索引，不需要回表

五、索引类型详解

5.1 普通索引（Normal Index）

-- 最基本的索引类型，没有任何限制
CREATE INDEX idx_name ON users(name);

-- 或者在建表时创建
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50),
    INDEX idx_name (name)
);

5.2 唯一索引（Unique Index）

-- 索引列的值必须唯一，允许 NULL
CREATE UNIQUE INDEX uk_email ON users(email);

-- 或者
ALTER TABLE users ADD UNIQUE INDEX uk_email(email);

-- 唯一约束会自动创建唯一索引
ALTER TABLE users ADD CONSTRAINT uk_phone UNIQUE (phone);

5.3 主键索引（Primary Key）

-- 特殊的唯一索引，不允许 NULL
-- InnoDB 的主键索引就是聚簇索引
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,  -- 主键索引
    name VARCHAR(50)
);

-- 复合主键
CREATE TABLE order_items (
    order_id BIGINT,
    product_id BIGINT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

5.4 前缀索引

对于长字符串，可以只索引前面一部分字符。

-- 创建前缀索引（只索引前 10 个字符）
CREATE INDEX idx_email ON users(email(10));

-- 如何确定前缀长度？
-- 计算不同前缀长度的选择性
SELECT
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) as sel_5,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) as sel_10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) as sel_15,
    COUNT(DISTINCT email) / COUNT(*) as sel_full
FROM users;

-- 选择一个选择性接近完整列的前缀长度

前缀索引的特点：

  • 优点：减少索引空间，提高索引效率
  • 缺点：无法用于 ORDER BY 和 GROUP BY，无法使用覆盖索引

5.5 全文索引（Full-Text Index）

-- 创建全文索引
CREATE FULLTEXT INDEX ft_content ON articles(title, content);

-- 或者
ALTER TABLE articles ADD FULLTEXT INDEX ft_content(title, content);

-- 使用全文索引查询
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 性能优化');

-- 布尔模式
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -PostgreSQL' IN BOOLEAN MODE);

-- 全文索引配置
SHOW VARIABLES LIKE 'ft_min_word_len';     -- 最小词长度（MyISAM）
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';  -- 最小词长度（InnoDB）

5.6 空间索引（Spatial Index）

-- 用于地理空间数据
CREATE TABLE locations (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    position POINT NOT NULL SRID 4326,
    SPATIAL INDEX sp_position (position)
);

-- 插入数据
INSERT INTO locations (id, name, position)
VALUES (1, 'Beijing', ST_GeomFromText('POINT(116.4 39.9)', 4326));

-- 空间查询
SELECT name FROM locations
WHERE ST_Distance_Sphere(position, ST_GeomFromText('POINT(116.4 39.9)', 4326)) < 10000;

六、索引失效场景

6.1 函数操作导致索引失效

-- ❌ 错误：对索引列使用函数
SELECT * FROM users WHERE YEAR(create_time) = 2024;
SELECT * FROM users WHERE LEFT(name, 3) = 'abc';

-- ✅ 正确：改写查询
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';
SELECT * FROM users WHERE name LIKE 'abc%';

6.2 隐式类型转换

-- phone 字段是 VARCHAR 类型
-- ❌ 错误：传入数字，发生隐式转换，索引失效
SELECT * FROM users WHERE phone = 13800138000;

-- ✅ 正确：传入字符串
SELECT * FROM users WHERE phone = '13800138000';

-- 规则：字符串和数字比较时，将字符串转为数字
-- 等价于：SELECT * FROM users WHERE CAST(phone AS SIGNED) = 13800138000

6.3 OR 条件

-- 如果 OR 的条件中有一个列没有索引，整个查询不使用索引
-- 假设 name 有索引，age 没有索引

-- ❌ 索引失效
SELECT * FROM users WHERE name = 'john' OR age = 25;

-- ✅ 使用 UNION 优化
SELECT * FROM users WHERE name = 'john'
UNION
SELECT * FROM users WHERE age = 25;

-- ✅ 或者给 age 也创建索引
CREATE INDEX idx_age ON users(age);
SELECT * FROM users WHERE name = 'john' OR age = 25;  -- index_merge

6.4 LIKE 以通配符开头

-- ❌ 索引失效
SELECT * FROM users WHERE name LIKE '%john';
SELECT * FROM users WHERE name LIKE '%john%';

-- ✅ 可以使用索引
SELECT * FROM users WHERE name LIKE 'john%';

6.5 NOT、!=、<> 操作

-- 通常不使用索引（优化器判断全表扫描更快）
SELECT * FROM users WHERE status != 1;
SELECT * FROM users WHERE status NOT IN (1, 2);
SELECT * FROM users WHERE status <> 1;

-- 特殊情况：如果选择性足够高，可能使用索引
-- 例如 status 只有 0 和 1 两个值，查询 status != 0 等价于 status = 1

6.6 IS NULL / IS NOT NULL

-- 是否使用索引取决于数据分布
-- 如果大部分数据是 NULL，IS NOT NULL 可能使用索引
-- 如果大部分数据非 NULL，IS NULL 可能使用索引

-- 建议：尽量避免使用 NULL，用默认值替代
ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) NOT NULL DEFAULT '';

6.7 索引失效总结表

场景示例解决方案
函数操作WHERE YEAR(date) = 2024改写为范围查询
隐式转换WHERE phone = 123使用正确的类型
OR 无索引列WHERE a = 1 OR b = 2UNION 或添加索引
LIKE 前导%WHERE name LIKE '%abc'使用全文索引
范围查询后的列WHERE a > 1 AND b = 2调整索引顺序
NOT / !=WHERE status != 1考虑改写查询

七、执行计划分析

7.1 EXPLAIN 基本用法

EXPLAIN SELECT * FROM users WHERE username = 'john';

+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
| 1  | SIMPLE      | users | ref  | idx_username  | idx_username | 202  | const | 1    | NULL  |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+

7.2 关键字段解析

type 字段（访问类型）

从好到差排序：

type说明示例
system表只有一行系统表
const主键或唯一索引等值查询WHERE id = 1
eq_ref主键或唯一索引关联查询JOIN ON a.id = b.id
ref非唯一索引等值查询WHERE name = 'john'
range索引范围扫描WHERE id > 100
index全索引扫描覆盖索引查询
ALL全表扫描无索引条件
-- const 示例
EXPLAIN SELECT * FROM users WHERE id = 1;

-- ref 示例
EXPLAIN SELECT * FROM users WHERE username = 'john';

-- range 示例
EXPLAIN SELECT * FROM users WHERE id BETWEEN 1 AND 100;

-- ALL 示例（应避免）
EXPLAIN SELECT * FROM users WHERE age > 18;  -- age 无索引

key_len 计算

-- key_len 表示索引使用的字节数

字符串类型：
- CHAR(n)：n * 字符集长度
- VARCHAR(n)：n * 字符集长度 + 2（存储长度）
- 如果允许 NULL：+ 1

数值类型：
- TINYINT：1
- SMALLINT：2
- INT：4
- BIGINT：8

日期类型：
- DATE：3
- DATETIME：8
- TIMESTAMP：4

示例计算：
- VARCHAR(50) NOT NULL，utf8mb4：50 * 4 + 2 = 202
- VARCHAR(50) NULL，utf8mb4：50 * 4 + 2 + 1 = 203
- INT NOT NULL：4
- INT NULL：4 + 1 = 5

Extra 字段

Extra说明优化建议
Using index覆盖索引最佳状态
Using where使用 WHERE 过滤正常
Using temporary使用临时表需要优化
Using filesort文件排序需要优化
Using index condition索引条件下推（ICP）较好
Using join buffer连接缓冲考虑添加索引

7.3 EXPLAIN FORMAT=JSON

EXPLAIN FORMAT=JSON
SELECT * FROM users WHERE username = 'john'\G

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.00"          -- 查询成本
    },
    "table": {
      "table_name": "users",
      "access_type": "ref",
      "possible_keys": ["idx_username"],
      "key": "idx_username",
      "key_length": "202",
      "rows_examined_per_scan": 1,   -- 扫描行数
      "rows_produced_per_join": 1,
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.10",
        "prefix_cost": "1.00"
      }
    }
  }
}

7.4 EXPLAIN ANALYZE（MySQL 8.0.18+）

EXPLAIN ANALYZE
SELECT * FROM users WHERE username = 'john';

-> Index lookup on users using idx_username (username='john')
   (cost=0.35 rows=1)
   (actual time=0.025..0.027 rows=1 loops=1)

-- actual time：实际执行时间
-- rows：实际返回行数
-- loops：循环次数

八、索引设计最佳实践

8.1 主键设计

-- 推荐：使用自增 BIGINT 作为主键
CREATE TABLE users (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    ...
);

-- 不推荐：使用 UUID 作为主键
-- 原因：
-- 1. UUID 是随机的，导致页分裂，插入性能差
-- 2. UUID 占用 36 字节，浪费空间
-- 3. 所有二级索引都存储主键，进一步浪费空间

-- 如果必须使用 UUID，考虑有序 UUID
-- MySQL 8.0 提供 UUID_TO_BIN(uuid, 1) 将 UUID 转为有序二进制

8.2 索引数量控制

-- 建议：单表索引不超过 5-6 个

-- 原因：
-- 1. 每个索引都需要存储空间
-- 2. 每次写操作都需要维护所有索引
-- 3. 过多索引会影响优化器选择

-- 定期审查未使用的索引
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_star = 0
ORDER BY object_schema, object_name;

8.3 查询覆盖设计

-- 针对高频查询设计覆盖索引

-- 假设有高频查询：
SELECT user_id, order_amount, status
FROM orders
WHERE user_id = ? AND status = ?
ORDER BY created_at DESC
LIMIT 10;

-- 设计覆盖索引
CREATE INDEX idx_user_status_time_amount
ON orders(user_id, status, created_at, order_amount);

-- 这个索引可以：
-- 1. 通过 user_id 和 status 快速定位
-- 2. 利用 created_at 排序，避免 filesort
-- 3. order_amount 在索引中，避免回表

8.4 联合索引顺序

-- 原则：
-- 1. 等值查询列放前面
-- 2. 排序列紧随其后
-- 3. 选择性高的列优先

-- 示例查询
SELECT * FROM orders
WHERE user_id = 1
AND status IN ('pending', 'paid')
ORDER BY created_at DESC;

-- 最佳索引设计
CREATE INDEX idx_user_status_time ON orders(user_id, status, created_at);

-- 不好的设计
CREATE INDEX idx_status_user_time ON orders(status, user_id, created_at);
-- 虽然 status 可能选择性更高，但 user_id 是等值查询，应该放前面

8.5 避免冗余索引

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

-- 查找冗余索引
SELECT
    table_name,
    redundant_index_name,
    redundant_index_columns,
    dominant_index_name,
    dominant_index_columns
FROM sys.schema_redundant_indexes;

九、索引监控与维护

9.1 索引使用统计

-- 查看索引使用情况
SELECT
    object_schema,
    object_name,
    index_name,
    count_read,
    count_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
ORDER BY count_read DESC;

-- 查看索引大小
SELECT
    database_name,
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
AND database_name = 'your_database';

9.2 索引碎片处理

-- 查看表和索引的碎片情况
SELECT
    table_name,
    data_length,
    index_length,
    data_free,
    ROUND(data_free / (data_length + index_length) * 100, 2) AS frag_ratio
FROM information_schema.tables
WHERE table_schema = 'your_database'
AND data_free > 0
ORDER BY frag_ratio DESC;

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

-- 或者
ALTER TABLE users ENGINE=InnoDB;

9.3 索引建议工具

-- 使用 EXPLAIN 分析
EXPLAIN SELECT * FROM users WHERE age > 18;

-- MySQL 8.0 可以查看优化器建议
-- performance_schema 中的 events_statements_summary_by_digest
SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'your_database'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
Default footer
Copyright © 2026 HSXISAWD