MySQL索引优化完全指南：从原理到实战#

🗃️ 核心知识点

理解B+树索引的底层原理

掌握索引设计的黄金法则

学会分析和优化慢查询

避免常见的索引失效场景

📚 什么是索引？#

索引是数据库中用于加速数据检索的数据结构，类似于书籍的目录。没有索引时，数据库需要全表扫描；有了索引，可以快速定位数据。

🌲 B+树索引原理#

为什么选择B+树？#

数据结构	查询复杂度	范围查询	磁盘IO
哈希表	O(1)	❌ 不支持	较少
二叉树	O(log n)	✅ 支持	较多
B+树	O(log n)	✅ 支持	最少

B+树结构示意#

1
                    [根节点: 50]
2
                   /            \
3
          [20, 35]              [70, 85]
4
         /    |    \           /    |    \
5
      [10,15] [25,30] [40,45] [60,65] [75,80] [90,95]
6
         ↓       ↓       ↓       ↓       ↓       ↓
7
       数据    数据    数据    数据    数据    数据
8

9
       叶子节点通过双向链表相连，支持范围查询

🎯 索引类型详解#

1. 主键索引 (Primary Key)#

1
-- 创建表时定义主键
2
CREATE TABLE users (
3
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
4
    username VARCHAR(50) NOT NULL,
5
    email VARCHAR(100) NOT NULL
6
);
7

8
-- InnoDB中，主键索引即聚簇索引，数据存储在叶子节点

2. 唯一索引 (Unique Index)#

1
-- 保证列值唯一
2
CREATE UNIQUE INDEX idx_email ON users(email);
3

4
-- 或在创建表时定义
5
CREATE TABLE users (
6
    id BIGINT PRIMARY KEY,
7
    email VARCHAR(100) UNIQUE
8
);

3. 普通索引 (Normal Index)#

1
-- 最基本的索引类型
2
CREATE INDEX idx_username ON users(username);

4. 联合索引 (Composite Index)#

1
-- 多列组合索引，遵循最左前缀原则
2
CREATE INDEX idx_name_age_city ON users(name, age, city);
3

4
-- ✅ 能使用索引的查询
5
SELECT * FROM users WHERE name = '张三';
6
SELECT * FROM users WHERE name = '张三' AND age = 25;
7
SELECT * FROM users WHERE name = '张三' AND age = 25 AND city = '北京';
8

9
-- ❌ 无法使用索引的查询
10
SELECT * FROM users WHERE age = 25;  -- 跳过了name
11
SELECT * FROM users WHERE city = '北京';  -- 跳过了name和age

5. 覆盖索引 (Covering Index)#

1
-- 当查询的列都在索引中时，无需回表
2
CREATE INDEX idx_name_email ON users(name, email);
3

4
-- ✅ 覆盖索引，不需要回表
5
SELECT name, email FROM users WHERE name = '张三';
6

7
-- ❌ 需要回表获取其他列
8
SELECT * FROM users WHERE name = '张三';

🔧 EXPLAIN分析查询#

1
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

EXPLAIN关键字段解读#

字段	说明	优化目标
type	访问类型	system > const > eq_ref > ref > range > index > ALL
key	实际使用的索引	应该使用预期的索引
rows	预估扫描行数	越小越好
Extra	额外信息	避免Using filesort, Using temporary

type类型详解#

1
-- const: 主键或唯一索引的等值查询
2
EXPLAIN SELECT * FROM users WHERE id = 1;
3

4
-- ref: 普通索引的等值查询
5
EXPLAIN SELECT * FROM users WHERE username = 'admin';
6

7
-- range: 范围查询
8
EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;
9

10
-- ALL: 全表扫描（需要优化！）
11
EXPLAIN SELECT * FROM users WHERE age + 1 = 25;

⚠️ 索引失效的常见场景#

1. 对索引列使用函数#

1
-- ❌ 索引失效
2
SELECT * FROM users WHERE YEAR(create_time) = 2024;
3

4
-- ✅ 优化后
5
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

2. 隐式类型转换#

1
-- 假设 phone 是 VARCHAR 类型
2
-- ❌ 索引失效（数字与字符串比较）
3
SELECT * FROM users WHERE phone = 13800138000;
4

5
-- ✅ 正确写法
6
SELECT * FROM users WHERE phone = '13800138000';

3. LIKE以通配符开头#

1
-- ❌ 索引失效
2
SELECT * FROM users WHERE name LIKE '%张';
3

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

4. OR条件未全部建立索引#

1
-- 假设只有name有索引，age没有
2
-- ❌ 索引失效
3
SELECT * FROM users WHERE name = '张三' OR age = 25;
4

5
-- ✅ 解决方案1：给age也建索引
6
-- ✅ 解决方案2：改用UNION
7
SELECT * FROM users WHERE name = '张三'
8
UNION
9
SELECT * FROM users WHERE age = 25;

5. 不等于操作#

1
-- ❌ 可能不走索引
2
SELECT * FROM users WHERE status != 'deleted';
3

4
-- ✅ 如果非删除数据占大多数，可以改写
5
SELECT * FROM users WHERE status IN ('active', 'pending', 'blocked');

💡 索引设计最佳实践#

1. 选择性高的列优先#

1
-- 选择性 = 不重复值数量 / 总行数
2
SELECT
3
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
4
    COUNT(DISTINCT user_id) / COUNT(*) AS user_id_selectivity
5
FROM orders;
6

7
-- user_id选择性更高，应该放在联合索引前面
8
CREATE INDEX idx_user_status ON orders(user_id, status);

2. 频繁查询的列建索引#

1
-- 分析慢查询日志，找出高频查询条件
2
-- 为WHERE、JOIN、ORDER BY、GROUP BY涉及的列建索引

3. 控制索引数量#

1
-- ❌ 索引过多会影响写入性能
2
CREATE INDEX idx_a ON table(a);
3
CREATE INDEX idx_b ON table(b);
4
CREATE INDEX idx_c ON table(c);
5
CREATE INDEX idx_ab ON table(a, b);
6
CREATE INDEX idx_bc ON table(b, c);
7

8
-- ✅ 合理设计联合索引，一个索引覆盖多个查询场景
9
CREATE INDEX idx_abc ON table(a, b, c);

4. 长字符串使用前缀索引#

1
-- 对于长字符串，使用前缀索引节省空间
2
CREATE INDEX idx_email_prefix ON users(email(10));
3

4
-- 选择合适的前缀长度
5
SELECT
6
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS len5,
7
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS len10,
8
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS len15
9
FROM users;

📊 实战案例：电商订单表优化#

原始表结构#

1
CREATE TABLE orders (
2
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
3
    order_no VARCHAR(32) NOT NULL,
4
    user_id BIGINT NOT NULL,
5
    product_id BIGINT NOT NULL,
6
    amount DECIMAL(10,2) NOT NULL,
7
    status TINYINT NOT NULL DEFAULT 0,
8
    create_time DATETIME NOT NULL,
9
    update_time DATETIME NOT NULL
10
);

常见查询场景分析#

1
-- 场景1：根据订单号查询（高频，需要快速定位）
2
SELECT * FROM orders WHERE order_no = 'ORD202412010001';
3
-- 建议：唯一索引
4
CREATE UNIQUE INDEX idx_order_no ON orders(order_no);
5

6
-- 场景2：查询用户的订单列表（高频）
7
SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time DESC;
8
-- 建议：联合索引
9
CREATE INDEX idx_user_time ON orders(user_id, create_time DESC);
10

11
-- 场景3：后台查询某状态的订单（中频）
12
SELECT * FROM orders WHERE status = 1 AND create_time > '2024-12-01';
13
-- 建议：联合索引
14
CREATE INDEX idx_status_time ON orders(status, create_time);
15

16
-- 场景4：统计某商品的销售额
17
SELECT SUM(amount) FROM orders WHERE product_id = 500 AND status = 2;
18
-- 建议：联合索引 + 覆盖索引
19
CREATE INDEX idx_product_status_amount ON orders(product_id, status, amount);

最终索引设计#

1
-- 精简后的索引方案
2
CREATE UNIQUE INDEX idx_order_no ON orders(order_no);
3
CREATE INDEX idx_user_time ON orders(user_id, create_time DESC);
4
CREATE INDEX idx_status_time ON orders(status, create_time);
5
CREATE INDEX idx_product_status ON orders(product_id, status);

🔍 慢查询优化流程#

1
1. 开启慢查询日志
2
   ↓
3
2. 分析慢查询日志，找出问题SQL
4
   ↓
5
3. 使用EXPLAIN分析执行计划
6
   ↓
7
4. 检查是否缺少索引或索引失效
8
   ↓
9
5. 优化SQL或添加/调整索引
10
   ↓
11
6. 测试验证优化效果

开启慢查询日志#

1
-- 查看当前配置
2
SHOW VARIABLES LIKE 'slow_query%';
3
SHOW VARIABLES LIKE 'long_query_time';
4

5
-- 开启慢查询日志
6
SET GLOBAL slow_query_log = 'ON';
7
SET GLOBAL long_query_time = 1;  -- 超过1秒的查询记录

🚀 总结#

索引优化口诀#

最左前缀要牢记，联合索引按顺序
覆盖索引减回表，查询效率大提升
函数运算要避免，索引列保持原样
类型转换是大坑，字段类型要匹配
选择性高放前面，区分度大更有效

性能优化checklist#

✅ 为高频查询条件建立索引
✅ 使用EXPLAIN验证索引使用情况
✅ 避免索引失效的写法
✅ 定期分析和优化慢查询
✅ 控制单表索引数量（建议不超过5-6个）

如果这篇文章对你有帮助，欢迎点赞分享！ 💪

音乐

Pasule Blog!

音乐

MySQL索引优化完全指南：从原理到实战

MySQL索引优化完全指南：从原理到实战#

📚 什么是索引？#

🌲 B+树索引原理#

为什么选择B+树？#

B+树结构示意#

🎯 索引类型详解#

1. 主键索引 (Primary Key)#

2. 唯一索引 (Unique Index)#

3. 普通索引 (Normal Index)#

4. 联合索引 (Composite Index)#

5. 覆盖索引 (Covering Index)#

🔧 EXPLAIN分析查询#

EXPLAIN关键字段解读#

type类型详解#

⚠️ 索引失效的常见场景#

1. 对索引列使用函数#

2. 隐式类型转换#

3. LIKE以通配符开头#

4. OR条件未全部建立索引#

5. 不等于操作#

💡 索引设计最佳实践#

1. 选择性高的列优先#

2. 频繁查询的列建索引#

3. 控制索引数量#

4. 长字符串使用前缀索引#

📊 实战案例：电商订单表优化#

原始表结构#

常见查询场景分析#

最终索引设计#

🔍 慢查询优化流程#

开启慢查询日志#

🚀 总结#

索引优化口诀#

性能优化checklist#

文章分享

评论区

音乐

文章目录