MySQL 索引优化

什么是索引？

索引是一种数据结构，用于快速查找数据，类似于书的目录。

索引的优缺点

优点：

• 大大加快查询速度
• 加速表与表之间的连接
• 减少分组和排序的时间

缺点：

• 占用存储空间
• 降低写操作（INSERT、UPDATE、DELETE）的速度
• 需要维护成本

索引类型

1. 普通索引（INDEX）

最基本的索引，没有任何限制。

-- 创建索引
CREATE INDEX idx_username ON users(username);

-- 创建表时指定
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    INDEX idx_username (username)
);

-- 添加索引
ALTER TABLE users ADD INDEX idx_username (username);

2. 唯一索引（UNIQUE）

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

CREATE UNIQUE INDEX idx_email ON users(email);

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

3. 主键索引（PRIMARY KEY）

特殊的唯一索引，不允许NULL，一个表只能有一个主键。

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

-- 或
ALTER TABLE users ADD PRIMARY KEY (id);

4. 复合索引（组合索引）

多个列组合的索引。

CREATE INDEX idx_name_age ON users(username, age);

最左前缀原则：

• 可以匹配：(username), (username, age)
• 不能匹配：(age)

-- 能使用索引
SELECT * FROM users WHERE username = 'user1';
SELECT * FROM users WHERE username = 'user1' AND age = 20;

-- 不能使用索引
SELECT * FROM users WHERE age = 20;

5. 全文索引（FULLTEXT）

用于全文搜索，仅InnoDB和MyISAM支持。

CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 全文搜索
SELECT * FROM articles WHERE MATCH(content) AGAINST('MySQL');

索引数据结构

B+Tree索引（默认）

InnoDB和MyISAM使用B+Tree作为索引结构。

特点：

• 所有数据存储在叶子节点
• 叶子节点通过指针连接，便于范围查询
• 非叶子节点只存储键值，节省空间

         [10, 20]
       /    |    \
   [5,7]  [12,15] [22,25]
    / \    / \     / \
   数据  数据  数据  数据

Hash索引

Memory存储引擎使用。

特点：

• 查找速度快 O(1)
• 只支持等值查询，不支持范围查询
• 不支持排序

CREATE TABLE temp (
    id INT,
    name VARCHAR(50),
    INDEX USING HASH (name)
) ENGINE=MEMORY;

创建索引的原则

1. 适合创建索引的情况

✅ WHERE条件字段

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

✅ ORDER BY排序字段

SELECT * FROM users ORDER BY created_at DESC;
-- 在created_at上创建索引

✅ JOIN连接字段

SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- 在user_id上创建索引

✅ GROUP BY分组字段

SELECT age, COUNT(*) FROM users GROUP BY age;
-- 在age上创建索引

2. 不适合创建索引的情况

❌ 数据量小的表 ❌ 频繁更新的字段 ❌ 区分度低的字段（如性别） ❌ WHERE条件中用不到的字段

索引失效的情况

1. 使用函数或计算

-- 索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;
SELECT * FROM users WHERE age + 1 = 21;

-- 正确写法
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
SELECT * FROM users WHERE age = 20;

2. 类型转换

-- username是VARCHAR类型，但传入数字，索引失效
SELECT * FROM users WHERE username = 123;

-- 正确写法
SELECT * FROM users WHERE username = '123';

3. 使用 NOT、!=、<>

-- 索引失效
SELECT * FROM users WHERE age != 20;
SELECT * FROM users WHERE age <> 20;

-- 可能使用索引
SELECT * FROM users WHERE age > 20 OR age < 20;

4. LIKE 以 % 开头

-- 索引失效
SELECT * FROM users WHERE username LIKE '%user';

-- 可以使用索引
SELECT * FROM users WHERE username LIKE 'user%';

5. OR 连接的条件

-- 如果OR前后有一个字段没有索引，索引失效
SELECT * FROM users WHERE username = 'user1' OR age = 20;

-- 解决：给age也加上索引，或改用UNION
SELECT * FROM users WHERE username = 'user1'
UNION
SELECT * FROM users WHERE age = 20;

6. 违反最左前缀原则

-- 复合索引 (username, age)

-- 索引失效
SELECT * FROM users WHERE age = 20;

-- 可以使用索引
SELECT * FROM users WHERE username = 'user1';
SELECT * FROM users WHERE username = 'user1' AND age = 20;

索引优化实战

1. 使用EXPLAIN分析查询

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

重要字段：

• type：访问类型（ALL < index < range < ref < eq_ref < const）
• possible_keys：可能使用的索引
• key：实际使用的索引
• rows：扫描的行数
• Extra：额外信息

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | users | ref  | idx_username  | idx  | 203     | const|    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

2. 覆盖索引

查询的列都在索引中，不需要回表查询。

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

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

-- 需要回表
SELECT * FROM users WHERE username = 'user1';

3. 索引下推（Index Condition Pushdown）

MySQL 5.6+ 支持，将WHERE条件下推到存储引擎层。

-- 复合索引 (username, age)
SELECT * FROM users WHERE username LIKE 'user%' AND age = 20;

-- 索引下推：age = 20 在索引层就过滤掉了

4. 前缀索引

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

-- 只索引前10个字符
CREATE INDEX idx_email ON users(email(10));

-- 查看选择性
SELECT 
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) as selectivity
FROM users;

5. 索引合并

MySQL会合并多个索引的结果。

-- username和age都有索引
SELECT * FROM users WHERE username = 'user1' OR age = 20;

-- MySQL可能使用index_merge

慢查询优化

1. 开启慢查询日志

-- 查看是否开启
SHOW VARIABLES LIKE 'slow_query_log';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询时间阈值（秒）
SET GLOBAL long_query_time = 2;

2. 分析慢查询

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

3. 优化步骤

1. 使用EXPLAIN分析查询
2. 检查是否使用了索引
3. 优化查询语句
4. 添加合适的索引
5. 验证优化效果

索引维护

查看索引

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

-- 查看索引使用情况
SHOW INDEX FROM users \G

删除索引

-- 删除索引
DROP INDEX idx_username ON users;

-- 或
ALTER TABLE users DROP INDEX idx_username;

重建索引

-- 重建索引（优化碎片）
ALTER TABLE users ENGINE=InnoDB;

-- 或
OPTIMIZE TABLE users;

最佳实践

1. 选择性高的字段建索引：区分度高的字段
2. 使用短索引：索引字段尽量短，节省空间
3. 利用最左前缀：合理设计复合索引
4. 避免过度索引：索引不是越多越好
5. 定期维护索引：重建碎片化的索引
6. 监控索引使用情况：删除无用索引

💡 提示

这是一个demo文档，欢迎补充更多索引优化相关内容。

相关链接

• MySQL简介
• 基础查询