本文是”深入学习MySQL：从新手到高手”系列的第二篇，将深入讲解B+树索引原理、索引类型、索引设计原则以及索引失效的常见场景。
索引概述
索引是协助MySQL高效获取数据的有序数据结构。如果没有索引，查询数据时需要全表扫描，随着数据量增大，性能会急剧下降。
-- 创建测试表
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100),
age INT,
city VARCHAR(50),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_username (username),
INDEX idx_age_city (age, city)
) ENGINE=InnoDB;
-- 插入100万条测试数据
INSERT INTO users (username, email, age, city)
SELECT
CONCAT('user_', LPAD(seq, 7, '0')),
CONCAT('user_', LPAD(seq, 7, '0'), '@example.com'),
FLOOR(RAND() * 60) + 18,
ELT(FLOOR(RAND() * 5) + 1, '北京', '上海', '广州', '深圳', '杭州')
FROM (
SELECT @rownum := @rownum + 1 AS seq
FROM information_schema.columns a,
information_schema.columns b,
(SELECT @rownum := 0) r
LIMIT 1000000
) t;B+树索引原理
为什么选择B+树？
MySQL选择B+树作为索引结构，主要基于以下缘由：
|
数据结构 |
查询复杂度 |
问题 |
|
哈希表 |
O(1) |
不支持范围查询 |
|
二叉搜索树 |
O(log n) |
可能退化为链表 |
|
平衡二叉树(AVL) |
O(log n) |
树高度大，磁盘IO次数多 |
|
B树 |
O(log n) |
非叶子节点存数据，扫描效率低 |
|
B+树 |
O(log n) |
叶子节点有序链表，范围查询高效 |
B+树结构详解
┌─────────────────┐
│ 根节点(索引页) │
│ [10] [20] [30] │
└────┬───┬───┬────┘
┌───────────┘ │ └───────────┐
▼ ▼ ▼
┌────────────┐ ┌────────────┐ ┌────────────┐
│ 索引页 │ │ 索引页 │ │ 索引页 │
│ [3][5][8] │ │[12][15][18]│ │[22][25][28]│
└──┬──┬──┬───┘ └──┬──┬──┬───┘ └──┬──┬──┬───┘
│ │ │ │ │ │ │ │ │
▼ ▼ ▼ ▼ ▼ ▼ ▼ ▼ ▼
┌──────────────────────────────────────────────┐
│ 叶子节点(数据页) │
│ [1,2,3]←→[4,5,6]←→[7,8,9]←→[10,11,12]←→... │
│ ↑ ↑ │
│ └────────── 双向链表 ─────────────────┘ │
└──────────────────────────────────────────────┘B+树的特点：
- 非叶子节点只存索引：不存储数据，可以存储更多索引项，降低树高度
- 叶子节点存储数据：所有数据都在叶子节点
- 叶子节点有序链表：支持高效的范围查询
- 树高度低：3层B+树可存储约2000万条数据
计算B+树高度：
假设：
- 主键为BIGINT（8字节）
- 指针大小6字节
- 页大小16KB
非叶子节点可存储索引项数：16384 / (8 + 6) ≈ 1170
叶子节点假设每条记录1KB，可存储：16384 / 1024 ≈ 16条
树高度计算：
- 2层：1170 × 16 ≈ 1.8万条
- 3层：1170 × 1170 × 16 ≈ 2190万条
- 4层：1170 × 1170 × 1170 × 16 ≈ 256亿条聚簇索引与二级索引
聚簇索引（Clustered Index）
聚簇索引决定了数据的物理存储顺序，InnoDB的数据文件本身就是按聚簇索引组织的。
┌─────────────────────────────────────────────────────────────┐
│ 聚簇索引 (主键索引) │
│ │
│ ┌─────────┐ │
│ │ 根节点 │ │
│ │ [50][100]│ │
│ └────┬────┘ │
│ ┌────────┴────────┐ │
│ ▼ ▼ │
│ ┌──────────┐ ┌──────────┐ │
│ │ [10][30] │ │ [70][90] │ │
│ └────┬─────┘ └────┬─────┘ │
│ │ │ │
│ ┌─────────┼─────────┐ │ │
│ ▼ ▼ ▼ ▼ │
│ ┌─────────┬─────────┬─────────┬─────────┐ │
│ │ id=1 │ id=10 │ id=30 │ id=50 │ │
│ │ 全部列 │ 全部列 │ 全部列 │ 全部列 │ ← 叶子节点 │
│ │ 数据 │ 数据 │ 数据 │ 数据 │ 存完整数据 │
│ └─────────┴─────────┴─────────┴─────────┘ │
│ ↑ ↑ │
│ └────────── 双向链表 ──────────────┘ │
└─────────────────────────────────────────────────────────────┘聚簇索引的选择规则：
-- 1. 如果定义了主键，主键就是聚簇索引
CREATE TABLE t1 (
id INT PRIMARY KEY, -- 聚簇索引
name VARCHAR(50)
);
-- 2. 如果没有主键，选择第一个非空唯一索引
CREATE TABLE t2 (
id INT NOT NULL UNIQUE, -- 聚簇索引
name VARCHAR(50)
);
-- 3. 如果都没有，InnoDB自动生成一个隐藏的row_id作为聚簇索引
CREATE TABLE t3 (
name VARCHAR(50)
);二级索引（Secondary Index）
二级索引的叶子节点存储的是索引列的值 + 主键值，而不是完整的数据行。
┌─────────────────────────────────────────────────────────────┐
│ 二级索引 (idx_username) │
│ │
│ ┌─────────┐ │
│ │ 根节点 │ │
│ │ [M][Z] │ │
│ └────┬────┘ │
│ ┌────────┴────────┐ │
│ ▼ ▼ │
│ ┌──────────┐ ┌──────────┐ │
│ │ [A][G] │ │ [P][T] │ │
│ └────┬─────┘ └────┬─────┘ │
│ │ │ │
│ ┌─────────┼─────────┐ │ │
│ ▼ ▼ ▼ ▼ │
│ ┌─────────┬─────────┬─────────┬─────────┐ │
│ │ Alice │ Bob │ Mike │ Tom │ │
│ │ id=5 │ id=3 │ id=8 │ id=1 │ ← 叶子节点 │
│ │ │ │ │ │ 存索引值+主键│
│ └─────────┴─────────┴─────────┴─────────┘ │
└─────────────────────────────────────────────────────────────┘回表查询
当通过二级索引查询时，如果需要获取索引中不包含的列，就需要回表。
-- 假设有索引 idx_username(username)
-- 需要回表：需要获取email列，但索引中没有
SELECT id, username, email FROM users WHERE username = 'Alice';
-- 执行过程：
-- 1. 通过idx_username找到username='Alice'的记录，获取主键id=5
-- 2. 通过主键id=5回表到聚簇索引，获取完整数据行
-- 3. 返回id, username, email查询过程：
二级索引 idx_username 聚簇索引（主键索引）
┌─────────────┐ ┌─────────────────┐
│ username │ │ id (主键) │
│ ─────────── │ │ ─────────────── │
│ Alice → id=5│ ───── 回表查询 ────────→ │ id=5 → 完整数据 │
└─────────────┘ └─────────────────┘覆盖索引
如果索引包含了查询所需的所有列，就不需要回表，这称为覆盖索引。
-- 创建覆盖索引
CREATE INDEX idx_username_email ON users(username, email);
-- 覆盖索引查询：不需要回表
SELECT username, email FROM users WHERE username = 'Alice';
-- 通过EXPLAIN验证
EXPLAIN SELECT username, email FROM users WHERE username = 'Alice';
-- Extra列显示：Using index（表明使用了覆盖索引）-- 覆盖索引的典型场景
-- 1. 只查询索引列
SELECT username FROM users WHERE username LIKE 'A%';
-- 2. 查询索引列 + 主键
SELECT id, username FROM users WHERE username = 'Alice';
-- 3. COUNT查询
SELECT COUNT(*) FROM users WHERE username LIKE 'A%';索引类型详解
按数据结构分类
B+树索引
-- 默认索引类型
CREATE INDEX idx_name ON users(username);
-- 等价于
CREATE INDEX idx_name ON users(username) USING BTREE;Hash索引
-- Memory引擎默认使用Hash索引
CREATE TABLE hash_test (
id INT PRIMARY KEY,
name VARCHAR(50),
INDEX idx_name (name) USING HASH
) ENGINE=MEMORY;
-- InnoDB的自适应哈希索引（AHI）由系统自动管理
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';Hash索引的限制：
- 只支持等值查询（=, IN）
- 不支持范围查询（>, <, BETWEEN）
- 不支持排序
- 不支持部分索引列匹配
全文索引
-- 创建全文索引
CREATE TABLE articles (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(200),
content TEXT,
FULLTEXT INDEX ft_content (title, content)
) ENGINE=InnoDB;
-- 使用全文索引
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 索引' IN NATURAL LANGUAGE MODE);
-- 布尔模式
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);按功能分类
主键索引
-- 创建主键索引
CREATE TABLE t1 (
id INT PRIMARY KEY, -- 方式1
name VARCHAR(50)
);
CREATE TABLE t2 (
id INT,
name VARCHAR(50),
PRIMARY KEY (id) -- 方式2
);
ALTER TABLE t3 ADD PRIMARY KEY (id); -- 方式3唯一索引
-- 创建唯一索引
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE, -- 方式1
phone VARCHAR(20),
UNIQUE INDEX idx_phone (phone) -- 方式2
);
-- 唯一索引允许NULL值，且可以有多个NULL
INSERT INTO users VALUES (1, NULL, NULL);
INSERT INTO users VALUES (2, NULL, NULL); -- 成功普通索引
-- 创建普通索引
CREATE INDEX idx_username ON users(username);
-- 或者在建表时
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
INDEX idx_username (username)
);前缀索引
对于长字符串列，可以只索引前N个字符：
-- 创建前缀索引
CREATE INDEX idx_email_prefix ON users(email(10));
-- 计算合适的前缀长度
SELECT
COUNT(DISTINCT email) / COUNT(*) AS full_selectivity,
COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS prefix_5,
COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS prefix_10,
COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS prefix_15
FROM users;
-- 选择接近full_selectivity的最小前缀长度联合索引（复合索引）
-- 创建联合索引
CREATE INDEX idx_age_city ON users(age, city);
-- 联合索引的结构
-- 先按age排序，age一样时按city排序联合索引 idx_age_city(age, city) 的B+树结构：
┌─────────────────┐
│ (25, 北京) │
│ (35, 上海) │
└────────┬────────┘
┌─────────────────┼─────────────────┐
▼ ▼ ▼
┌────────────┐ ┌────────────┐ ┌────────────┐
│(18,北京) │ │(25,广州) │ │(35,深圳) │
│(18,上海) │ │(25,深圳) │ │(40,北京) │
│(20,广州) │ │(30,北京) │ │(45,上海) │
└────────────┘ └────────────┘ └────────────┘最左前缀原则
联合索引遵循最左前缀原则：查询条件必须从索引的最左列开始，且不能跳过中间列。
-- 假设有联合索引 idx_a_b_c(a, b, c)
-- ✅ 可以使用索引
SELECT * FROM t WHERE a = 1; -- 使用a
SELECT * FROM t WHERE a = 1 AND b = 2; -- 使用a, b
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3; -- 使用a, b, c
SELECT * FROM t WHERE a = 1 AND c = 3; -- 只使用a（c无法使用）
SELECT * FROM t WHERE b = 2 AND a = 1; -- 使用a, b（优化器会调整顺序）
-- ❌ 无法使用索引
SELECT * FROM t WHERE b = 2; -- 不满足最左前缀
SELECT * FROM t WHERE c = 3; -- 不满足最左前缀
SELECT * FROM t WHERE b = 2 AND c = 3; -- 不满足最左前缀范围查询对索引的影响：
-- 假设有联合索引 idx_a_b_c(a, b, c)
SELECT * FROM t WHERE a = 1 AND b > 2 AND c = 3;
-- a使用索引，b使用索引（范围查询），c无法使用索引
SELECT * FROM t WHERE a = 1 AND b LIKE 'abc%' AND c = 3;
-- a使用索引，b使用索引（前缀匹配），c无法使用索引
-- 优化：调整索引顺序
CREATE INDEX idx_a_c_b ON t(a, c, b);
SELECT * FROM t WHERE a = 1 AND c = 3 AND b > 2;
-- a, c, b都可以使用索引索引失效场景
1. 对索引列使用函数
-- ❌ 索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;
SELECT * FROM users WHERE LEFT(username, 3) = 'abc';
-- ✅ 优化写法
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
SELECT * FROM users WHERE username LIKE 'abc%';2. 对索引列进行运算
-- ❌ 索引失效
SELECT * FROM users WHERE id + 1 = 10;
SELECT * FROM users WHERE age * 2 = 40;
-- ✅ 优化写法
SELECT * FROM users WHERE id = 9;
SELECT * FROM users WHERE age = 20;3. 隐式类型转换
-- 假设phone字段是VARCHAR类型
-- ❌ 索引失效（字符串与数字比较，phone被转换为数字）
SELECT * FROM users WHERE phone = 13800138000;
-- ✅ 正确写法
SELECT * FROM users WHERE phone = '13800138000';4. LIKE以通配符开头
-- ❌ 索引失效
SELECT * FROM users WHERE username LIKE '%abc';
SELECT * FROM users WHERE username LIKE '%abc%';
-- ✅ 可以使用索引
SELECT * FROM users WHERE username LIKE 'abc%';5. OR条件使用不当
-- ❌ 如果or两边的列不都有索引，索引失效
SELECT * FROM users WHERE username = 'Alice' OR age = 25;
-- 假设只有username有索引，age没有索引
-- ✅ 优化方案1：给age也加索引
CREATE INDEX idx_age ON users(age);
-- ✅ 优化方案2：使用UNION
SELECT * FROM users WHERE username = 'Alice'
UNION
SELECT * FROM users WHERE age = 25;6. NOT IN 和 NOT EXISTS
-- ❌ 可能导致索引失效
SELECT * FROM users WHERE id NOT IN (1, 2, 3);
-- ✅ 优化写法（如果数据量大）
SELECT * FROM users WHERE id NOT IN (1, 2, 3) AND id > 0;
-- 或者使用LEFT JOIN
SELECT u.* FROM users u
LEFT JOIN (SELECT 1 AS id UNION SELECT 2 UNION SELECT 3) t ON u.id = t.id
WHERE t.id IS NULL;7. IS NULL 和 IS NOT NULL
-- 索引列有大量NULL值时，IS NOT NULL可能不走索引
SELECT * FROM users WHERE email IS NOT NULL;
-- 优化：设计时尽量避免NULL，使用默认值
ALTER TABLE users MODIFY email VARCHAR(100) NOT NULL DEFAULT '';8. 不等于条件
-- ❌ != 和 <> 可能导致索引失效
SELECT * FROM users WHERE status != 1;
-- ✅ 如果status只有几个值，改用IN
SELECT * FROM users WHERE status IN (0, 2, 3);索引设计原则
1. 选择区分度高的列
-- 计算列的区分度（选择性）
SELECT
COUNT(DISTINCT username) / COUNT(*) AS username_selectivity,
COUNT(DISTINCT city) / COUNT(*) AS city_selectivity,
COUNT(DISTINCT age) / COUNT(*) AS age_selectivity
FROM users;
-- 区分度越接近1越好，提议 > 0.12. 联合索引列顺序
-- 原则：
-- 1. 区分度高的列放前面
-- 2. 查询频率高的列放前面
-- 3. 范围查询的列放后面
-- 示例：查询条件常常是 city = ? AND age > ?
-- 推荐：INDEX(city, age)，而不是INDEX(age, city)3. 避免冗余索引
-- 冗余索引示例
CREATE INDEX idx_a ON t(a);
CREATE INDEX idx_a_b ON t(a, b); -- idx_a是冗余的
-- 查找冗余索引
SELECT * FROM sys.schema_redundant_indexes;4. 控制索引数量
-- 查看表的索引
SHOW INDEX FROM users;
-- 提议：
-- 单表索引数量不超过5-6个
-- 单个索引列数不超过5个5. 使用覆盖索引
-- 高频查询场景，设计覆盖索引
-- 假设常常执行：SELECT id, username, email FROM users WHERE username = ?
CREATE INDEX idx_username_email ON users(username, email);
-- 这样查询就不需要回表EXPLAIN执行计划
EXPLAIN是分析SQL性能的重大工具。
EXPLAIN SELECT * FROM users WHERE username = 'Alice';关键字段解读
|
字段 |
说明 |
|
id |
查询序号 |
|
select_type |
查询类型 |
|
table |
访问的表 |
|
type |
访问类型（重大） |
|
possible_keys |
可能使用的索引 |
|
key |
实际使用的索引 |
|
key_len |
索引使用的字节数 |
|
ref |
索引比较的列或常量 |
|
rows |
预估扫描行数 |
|
filtered |
过滤比例 |
|
Extra |
额外信息（重大） |
type访问类型（从优到差）
-- 1. system: 表只有一行
EXPLAIN SELECT * FROM (SELECT 1) t;
-- 2. const: 主键或唯一索引等值查询
EXPLAIN SELECT * FROM users WHERE id = 1;
-- 3. eq_ref: 多表JOIN时，使用主键或唯一索引
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- 4. ref: 非唯一索引等值查询
EXPLAIN SELECT * FROM users WHERE username = 'Alice';
-- 5. range: 索引范围查询
EXPLAIN SELECT * FROM users WHERE age > 25;
EXPLAIN SELECT * FROM users WHERE id IN (1, 2, 3);
-- 6. index: 全索引扫描
EXPLAIN SELECT username FROM users;
-- 7. ALL: 全表扫描（需要优化）
EXPLAIN SELECT * FROM users WHERE email LIKE '%@gmail.com';Extra常见值
-- Using index: 覆盖索引，无需回表
EXPLAIN SELECT username FROM users WHERE username = 'Alice';
-- Using where: 使用WHERE过滤
EXPLAIN SELECT * FROM users WHERE age > 25;
-- Using index condition: 索引条件下推(ICP)
EXPLAIN SELECT * FROM users WHERE username LIKE 'A%' AND username LIKE '%e';
-- Using temporary: 使用临时表（需要优化）
EXPLAIN SELECT DISTINCT city FROM users;
-- Using filesort: 文件排序（需要优化）
EXPLAIN SELECT * FROM users ORDER BY email;
-- Using join buffer: JOIN时使用缓冲区
EXPLAIN SELECT * FROM users u, orders o WHERE u.id = o.user_id;key_len计算
-- key_len表明索引使用的字节数，可以判断联合索引使用了几列
-- 计算规则：
-- INT: 4字节
-- BIGINT: 8字节
-- VARCHAR(n): 3n + 2 (UTF8MB4) 或 n + 2 (Latin1)
-- CHAR(n): 3n (UTF8MB4)
-- 允许NULL: +1字节
-- 示例：idx_age_city(age INT, city VARCHAR(50))
-- age: 4 + 1 = 5 (允许NULL)
-- city: 50 * 3 + 2 + 1 = 153 (VARCHAR + 允许NULL)
-- 全部使用: 5 + 153 = 158索引优化实战
案例1：慢查询优化
-- 原始慢查询
SELECT * FROM orders
WHERE user_id = 12345
AND status = 1
AND created_at > '2024-01-01'
ORDER BY created_at DESC
LIMIT 10;
-- 分析
EXPLAIN SELECT * FROM orders ...;
-- type: ALL, 全表扫描
-- 优化：创建联合索引
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);
-- 优化后
EXPLAIN SELECT * FROM orders ...;
-- type: range, 使用索引范围查询案例2：分页查询优化
-- 原始分页查询（深分页性能差）
SELECT * FROM users ORDER BY id LIMIT 1000000, 10;
-- 优化方案1：延迟关联
SELECT u.* FROM users u
INNER JOIN (
SELECT id FROM users ORDER BY id LIMIT 1000000, 10
) t ON u.id = t.id;
-- 优化方案2：游标分页（记住上次的位置）
SELECT * FROM users WHERE id > 1000000 ORDER BY id LIMIT 10;案例3：ORDER BY优化
-- 原始查询（Using filesort）
SELECT * FROM users WHERE city = '北京' ORDER BY age;
-- 优化：创建联合索引
CREATE INDEX idx_city_age ON users(city, age);
-- 优化后：Using index（索引排序）
EXPLAIN SELECT * FROM users WHERE city = '北京' ORDER BY age;案例4：GROUP BY优化
-- 原始查询（Using temporary; Using filesort）
SELECT city, COUNT(*) FROM users GROUP BY city;
-- 优化：创建索引
CREATE INDEX idx_city ON users(city);
-- 优化后：Using index
EXPLAIN SELECT city, COUNT(*) FROM users GROUP BY city;索引监控与维护
查看索引使用情况
-- 查看索引使用统计
SELECT
object_schema,
object_name,
index_name,
count_star,
count_read,
count_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
ORDER BY count_star 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_star = 0
AND object_schema NOT IN ('mysql', 'performance_schema');索引碎片整理
-- 查看表的碎片情况
SELECT
TABLE_NAME,
DATA_LENGTH,
INDEX_LENGTH,
DATA_FREE,
(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH)) * 100 AS fragmentation_ratio
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
AND DATA_FREE > 0;
-- 整理碎片
OPTIMIZE TABLE users;
-- 或者
ALTER TABLE users ENGINE=InnoDB;在线DDL
-- MySQL 8.0支持在线添加索引
ALTER TABLE users ADD INDEX idx_email(email), ALGORITHM=INPLACE, LOCK=NONE;
-- ALGORITHM选项：
-- INPLACE: 原地修改，不复制表
-- COPY: 复制表（旧方式）
-- LOCK选项：
-- NONE: 不加锁
-- SHARED: 共享锁（允许读）
-- EXCLUSIVE: 排他锁本章小结
本文深入讲解了MySQL索引的核心知识：
- B+树原理：非叶子节点存索引，叶子节点存数据并形成有序链表
- 聚簇索引vs二级索引：聚簇索引存完整数据，二级索引存索引值+主键
- 回表与覆盖索引：覆盖索引可以避免回表，提升查询性能
- 最左前缀原则：联合索引必须从最左列开始使用
- 索引失效场景：函数、运算、类型转换、LIKE %开头等
- EXPLAIN分析：type和Extra是关键字段
- 索引设计原则：高区分度、合理顺序、避免冗余
下一篇预告：《深入学习MySQL（三）：SQL优化与执行计划分析》，将详细讲解慢查询分析、SQL优化技巧和执行计划的深度解读。
© 版权声明
文章版权归作者所有，未经允许请勿转载。
相关文章
暂无评论...
