MySQL索引优化实战技巧

前言

数据库性能问题80%都是由索引不当引起的。一个优秀的索引可以让查询速度提升数百倍，而一个错误的索引可能让数据库直接瘫痪。本文将从索引原理出发，结合实战案例，深入探讨MySQL索引的设计和优化技巧。

一、索引基础

1.1 什么是索引

索引是一种数据结构，用于快速定位数据。就像书的目录一样，可以快速找到需要的内容，而不用翻遍整本书。

1
2
3
4
5
6
7
8
-- 没有索引：全表扫描
SELECT * FROM users WHERE email = 'user@example.com';
-- 扫描100万行数据，耗时5秒

-- 有索引：索引查找
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = 'user@example.com';
-- 通过索引直接定位，耗时0.01秒

1.2 B+树索引结构

MySQL的InnoDB引擎使用B+树作为索引结构：

B+树特点：

  • 所有数据都在叶子节点
  • 叶子节点之间有指针连接
  • 非叶子节点只存储键值
  • 树的高度通常为3-4层
1
2
3
4
5
       [10, 20, 30]          ← 根节点
      /    |    |    \
 [1-9] [11-19] [21-29] [31-40] ← 中间节点
   ↓      ↓       ↓       ↓
数据页  数据页  数据页  数据页   ← 叶子节点（存储实际数据）

查找过程：

  1. 从根节点开始
  2. 比较键值，选择分支
  3. 最多3-4次IO就能定位到数据

1.3 索引类型

主键索引（PRIMARY KEY）：

1
2
3
4
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50)
);

唯一索引（UNIQUE）：

1
CREATE UNIQUE INDEX idx_email ON users(email);

普通索引（INDEX）：

1
CREATE INDEX idx_name ON users(name);

联合索引（组合索引）：

1
CREATE INDEX idx_name_age ON users(name, age);

全文索引（FULLTEXT）：

1
CREATE FULLTEXT INDEX idx_content ON articles(content);

二、索引设计原则

2.1 最左前缀原则

联合索引遵循最左前缀匹配：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建联合索引
CREATE INDEX idx_abc ON table(a, b, c);

-- ✅ 会使用索引
SELECT * FROM table WHERE a = 1;
SELECT * FROM table WHERE a = 1 AND b = 2;
SELECT * FROM table WHERE a = 1 AND b = 2 AND c = 3;

-- ❌ 不会使用索引
SELECT * FROM table WHERE b = 2;
SELECT * FROM table WHERE c = 3;
SELECT * FROM table WHERE b = 2 AND c = 3;

-- ⚠️ 部分使用索引
SELECT * FROM table WHERE a = 1 AND c = 3;  -- 只用到a

设计建议：

  • 将选择性高的列放在前面
  • 常用的查询条件放在前面
  • 考虑查询频率

2.2 索引列不能参与计算

1
2
3
4
5
6
7
8
-- ❌ 索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;
SELECT * FROM users WHERE age + 1 = 26;

-- ✅ 索引有效
SELECT * FROM users WHERE created_at >= '2024-01-01' 
  AND created_at < '2025-01-01';
SELECT * FROM users WHERE age = 25;

2.3 覆盖索引

覆盖索引包含查询所需的所有列，无需回表：

1
2
3
4
5
6
7
8
-- 创建覆盖索引
CREATE INDEX idx_name_age_email ON users(name, age, email);

-- ✅ 覆盖索引，无需回表
SELECT name, age, email FROM users WHERE name = '张三';

-- ❌ 非覆盖索引，需要回表
SELECT name, age, email, address FROM users WHERE name = '张三';

2.4 索引选择性

选择性 = 不重复的值 / 总行数，越接近1越好：

1
2
3
4
5
6
7
8
9
-- 检查选择性
SELECT 
  COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;

-- 示例
-- email选择性：0.99（几乎每个值都不同）✅
-- gender选择性：0.5（只有男女两个值）❌
-- status选择性：0.1（只有几个状态）❌

建议：

  • 选择性低于0.1的列不适合单独建索引
  • 可以考虑联合索引

三、索引失效场景

3.1 使用不等于操作符

1
2
3
4
5
6
-- ❌ 可能不走索引
SELECT * FROM users WHERE status != 1;
SELECT * FROM users WHERE status <> 1;

-- ✅ 优化方案
SELECT * FROM users WHERE status IN (0, 2, 3, 4);

3.2 使用OR连接

1
2
3
4
5
6
7
-- ❌ 可能不走索引
SELECT * FROM users WHERE name = '张三' OR age = 25;

-- ✅ 优化方案：改用UNION
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 25;

3.3 模糊查询以%开头

1
2
3
4
5
6
-- ❌ 不走索引
SELECT * FROM users WHERE name LIKE '%张三%';
SELECT * FROM users WHERE name LIKE '%张三';

-- ✅ 走索引
SELECT * FROM users WHERE name LIKE '张三%';

3.4 隐式类型转换

1
2
3
4
5
6
7
-- 假设phone字段是VARCHAR类型

-- ❌ 发生隐式转换，索引失效
SELECT * FROM users WHERE phone = 13800138000;

-- ✅ 正确使用
SELECT * FROM users WHERE phone = '13800138000';

3.5 使用函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- ❌ 索引失效
SELECT * FROM users WHERE LOWER(name) = 'zhangsan';
SELECT * FROM users WHERE DATE(created_at) = '2024-01-01';

-- ✅ 优化方案
-- 方案1：去掉函数
SELECT * FROM users WHERE name = 'zhangsan';

-- 方案2：创建函数索引（MySQL 8.0+）
CREATE INDEX idx_lower_name ON users((LOWER(name)));

-- 方案3：使用范围查询
SELECT * FROM users 
WHERE created_at >= '2024-01-01 00:00:00' 
  AND created_at < '2024-01-02 00:00:00';

四、执行计划分析

4.1 EXPLAIN详解

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

关键字段解释：

1
2
3
| id | select_type | table | type  | possible_keys | key      | key_len | ref   | rows | Extra       |
|----|-------------|-------|-------|---------------|----------|---------|-------|------|-------------|
| 1  | SIMPLE      | users | ref   | idx_name      | idx_name | 202     | const | 1    | Using index |

type字段（访问类型，从好到坏）：

  • system：表只有一行
  • const：主键或唯一索引查询
  • eq_ref：连接查询，使用主键或唯一索引
  • ref：非唯一索引查询
  • range：范围查询
  • index：全索引扫描
  • ALL：全表扫描 ❌

Extra字段：

  • Using index：覆盖索引，好！✅
  • Using where：使用WHERE过滤
  • Using filesort：需要排序，慢！⚠️
  • Using temporary：使用临时表，很慢！❌

4.2 实战案例分析

案例1：全表扫描

1
2
3
4
5
6
7
8
9
10
11
12
13
EXPLAIN SELECT * FROM orders WHERE amount > 100;

-- 结果
type: ALL
rows: 1000000  ← 扫描100万行
Extra: Using where

-- 优化：添加索引
CREATE INDEX idx_amount ON orders(amount);

-- 再次查询
type: range
rows: 50000  ← 只扫描5万行

案例2：索引失效

1
2
3
4
5
6
7
8
9
10
11
12
13
EXPLAIN SELECT * FROM users WHERE YEAR(birthday) = 1990;

-- 结果
type: ALL  ← 全表扫描
Extra: Using where

-- 优化
EXPLAIN SELECT * FROM users 
WHERE birthday >= '1990-01-01' AND birthday < '1991-01-01';

-- 结果
type: range  ← 使用索引
key: idx_birthday

五、实战优化案例

5.1 慢查询优化

原始查询：

1
2
3
4
5
6
7
8
9
SELECT u.name, o.order_no, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 1
  AND o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

-- 执行时间：5.2秒

问题分析：

1
2
3
4
5
6
7
8
9
10
EXPLAIN SELECT ...

-- users表
type: ALL
rows: 100000

-- orders表
type: ALL
rows: 1000000
Extra: Using where; Using filesort

优化步骤：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 1. 为orders.user_id添加索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 2. 创建联合索引
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 3. 优化后再次查询
EXPLAIN SELECT ...

-- orders表
type: ref
key: idx_status_created
rows: 1000  ← 大幅减少
Extra: Using index condition

-- 执行时间：0.05秒 ✅

5.2 分页查询优化

问题查询：

1
2
3
4
5
6
-- 深度分页，非常慢
SELECT * FROM orders 
ORDER BY id 
LIMIT 1000000, 20;

-- 执行时间：10秒

优化方案1：使用子查询

1
2
3
4
5
6
7
SELECT * FROM orders
WHERE id >= (
  SELECT id FROM orders ORDER BY id LIMIT 1000000, 1
)
LIMIT 20;

-- 执行时间：0.5秒

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

1
2
3
4
5
6
7
8
9
-- 第一次查询
SELECT * FROM orders WHERE id > 0 ORDER BY id LIMIT 20;
-- 返回最后一条的id为100

-- 第二次查询
SELECT * FROM orders WHERE id > 100 ORDER BY id LIMIT 20;
-- 返回最后一条的id为120

-- 执行时间：0.01秒 ✅

5.3 COUNT优化

慢查询：

1
2
SELECT COUNT(*) FROM orders WHERE status = 1;
-- 执行时间：3秒

优化方案：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 1. 添加索引
CREATE INDEX idx_status ON orders(status);

-- 2. 如果status只有少数几个值，考虑维护计数表
CREATE TABLE order_counts (
  status INT PRIMARY KEY,
  count INT
);

-- 更新订单时，同时更新计数
BEGIN;
INSERT INTO orders (...) VALUES (...);
UPDATE order_counts SET count = count + 1 WHERE status = 1;
COMMIT;

-- 查询时直接读取
SELECT count FROM order_counts WHERE status = 1;
-- 执行时间：0.001秒 ✅

六、索引维护

6.1 查看索引使用情况

1
2
3
4
5
6
7
8
9
10
11
12
-- 查看表的索引
SHOW INDEX FROM users;

-- 查看索引统计信息
SELECT 
  table_name,
  index_name,
  cardinality,
  index_type
FROM information_schema.statistics
WHERE table_schema = 'your_database'
  AND table_name = 'users';

6.2 检测未使用的索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- MySQL 5.7+
SELECT 
  t.table_schema,
  t.table_name,
  s.index_name,
  s.cardinality
FROM information_schema.tables t
JOIN information_schema.statistics s 
  ON t.table_schema = s.table_schema
  AND t.table_name = s.table_name
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage p
  ON s.table_schema = p.object_schema
  AND s.table_name = p.object_name
  AND s.index_name = p.index_name
WHERE t.table_schema = 'your_database'
  AND p.index_name IS NULL
  AND s.index_name != 'PRIMARY';

6.3 重建索引

索引碎片会影响性能，定期重建：

1
2
3
4
5
6
7
8
-- 重建单个索引
ALTER TABLE users DROP INDEX idx_name, ADD INDEX idx_name(name);

-- 优化表（重建所有索引）
OPTIMIZE TABLE users;

-- 或使用
ALTER TABLE users ENGINE=InnoDB;

七、高级技巧

7.1 前缀索引

对于长字符串字段，可以只索引前几个字符：

1
2
3
4
5
6
7
8
9
-- 创建前缀索引
CREATE INDEX idx_email_prefix 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
FROM users;

7.2 索引合并

MySQL可以合并多个索引：

1
2
3
4
5
6
7
8
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_amount ON orders(amount);

-- MySQL会合并两个索引
SELECT * FROM orders WHERE status = 1 AND amount > 100;

-- 但联合索引更好
CREATE INDEX idx_status_amount ON orders(status, amount);

7.3 延迟关联

优化大offset的分页：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 原始查询（慢）
SELECT * FROM articles 
WHERE category = 'tech'
ORDER BY created_at DESC
LIMIT 100000, 20;

-- 优化：先获取ID，再关联
SELECT a.* FROM articles a
JOIN (
  SELECT id FROM articles
  WHERE category = 'tech'
  ORDER BY created_at DESC
  LIMIT 100000, 20
) t ON a.id = t.id;

八、监控与调优

8.1 开启慢查询日志

1
2
3
4
5
6
7
8
9
10
11
-- 查看配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过1秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 分析慢查询日志
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

8.2 性能监控

1
2
3
4
5
6
7
8
-- 查看当前执行的查询
SHOW PROCESSLIST;

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS;

-- 查看表状态
SHOW TABLE STATUS LIKE 'users';

九、总结

索引设计黄金法则

  1. 不是越多越好：每个索引都有维护成本
  2. 选择性要高：区分度低的列不适合索引
  3. 联合索引遵循最左前缀：列的顺序很重要
  4. 覆盖索引是王道：减少回表操作
  5. 定期维护：清理无用索引，重建碎片索引

优化思路

  1. 使用EXPLAIN分析执行计划
  2. 关注type、rows、Extra字段
  3. 优先优化type为ALL的查询
  4. 避免Using filesort和Using temporary
  5. 监控慢查询日志

最佳实践

  • 为WHERE、ORDER BY、JOIN的列添加索引
  • 小表不需要索引
  • 写多读少的表少建索引
  • 定期分析表统计信息
  • 在业务低峰期维护索引

掌握这些技巧，你的数据库查询性能将得到质的飞跃！

参考资料