MySQL 索引优化技巧

MySQL 索引优化技巧

索引是提升查询性能的关键，但用不好反而拖慢速度。

1. 为什么要建索引

• 没有索引要全表扫描
• 数据量大时查询很慢
• 索引相当于书的目录
• 合适的索引能提升百倍速度

2. 索引类型

主键索引：

• 主键自动创建
• 唯一且非空

普通索引：

• 最基本的索引
• 加速查询

唯一索引：

• 值不能重复
• 比如手机号

联合索引：

• 多个字段组成
• 遵循最左前缀原则

3. 建索引原则

1）最频繁查询的字段建索引

-- 经常按name查
CREATE INDEX idx_name ON users(name);

2）区分度高的字段建索引

• 性别这种区分度低的不要建
• 手机号、身份证这种适合

3）联合索引注意顺序

-- 联合索引 (a, b, c)
-- 最左前缀：
-- a 能用
-- a,b 能用
-- a,b,c 能用
-- 单独b,c 用不了

4. 索引失效情况

1）where 里对索引字段用函数

-- 失效
SELECT * FROM users WHERE DATE(create_time) = '2026-01-01';

-- 有效
SELECT * FROM users WHERE create_time >= '2026-01-01' AND create_time < '2026-01-02';

2）where 里对索引字段做运算

-- 失效
SELECT * FROM users WHERE age + 1 = 20;

-- 有效
SELECT * FROM users WHERE age = 19;

3）like 以 % 开头

-- 失效
SELECT * FROM users WHERE name LIKE '%张';

-- 有效
SELECT * FROM users WHERE name LIKE '张%';

4）隐式类型转换

-- phone是字符串
-- 失效（数字转字符串）
SELECT * FROM users WHERE phone = 13800138000;

-- 有效
SELECT * FROM users WHERE phone = '13800138000';

5. EXPLAIN 查看执行计划

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

重点看：

• type： 访问类型，至少要 range
• key： 实际用到的索引
• rows： 扫描行数
• Extra： 额外信息

6. 索引不要建太多

• 每个索引都占空间
• 插入更新时要维护索引
• 不是越多越好
• 一张表索引建议不超过5个

7. 覆盖索引

避免回表：

-- 索引包含查询的所有字段
SELECT name, age FROM users WHERE name = '张三';

-- 索引 (name, age) 就是覆盖索引
-- 不用回表查主键

Extra 显示 Using index 就是覆盖索引。

8. 慢查询定位

-- 开启慢查询
slow_query_log = 1
long_query_time = 1  -- 超过1秒记录

-- 查看慢查询日志
-- 找到慢SQL，加索引优化

9. 常见优化场景

1）分页查询优化

-- 慢（深分页）
SELECT * FROM orders LIMIT 100000, 10;

-- 快（子查询）
SELECT * FROM orders 
WHERE id >= (SELECT id FROM orders LIMIT 100000, 1)
LIMIT 10;

2）JOIN 字段建索引

• JOIN 的两边都要建索引
• 类型要一致

3）ORDER BY 用索引

• 排序字段建索引
• 避免 filesort

10. 索引维护

查看索引：

SHOW INDEX FROM users;

删除多余索引：

DROP INDEX idx_name ON users;

分析表：

ANALYZE TABLE users;

总结

MySQL 索引优化要点：

• 区分度高的字段建索引
• 联合索引遵循最左前缀
• 避免索引失效（函数、运算、like%）
• 用 EXPLAIN 分析执行计划
• 索引不要建太多
• 覆盖索引避免回表
• 慢查询日志找问题SQL
• 深分页要优化
• JOIN 两边都建索引
• 定期维护索引

索引优化是MySQL性能优化的核心，一定要多实践。