拾荒的小海螺

1、简述

在日常数据库开发中，索引是性能优化的核心手段之一。虽然索引可以大幅提升查询效率，但错误使用也可能导致查询变慢、写入变慢甚至误导执行计划。

本文将带你系统了解 MySQL 创建索引的注意事项，帮助你在实际项目中构建更高效、合理的数据库索引体系。

2、为什么需要关注创建索引的细节？

索引是把双刃剑：

✅ 优点：

🔹 提升查询速度
🔹 降低 I/O 访问成本
🔹 支持排序、分组、连接优化

❌ 缺点：

🔹 占用磁盘空间
🔹 降低写入速度（INSERT/UPDATE/DELETE）
🔹 索引错误可能导致优化器选择非最优执行路径

因此，创建索引时需要格外谨慎。

3、创建索引的注意事项

3.1 ✅ 避免给低选择性字段创建索引

低选择性字段（如性别、状态、布尔值）建索引意义不大，会导致 MySQL 扫描大量记录。

-- ❌ 不推荐：status 只有0或1
CREATE INDEX idx_status ON orders(status);

应优先考虑唯一性高、区分度大的字段（如用户ID、手机号）。

3.2 ✅ 遵循最左前缀原则（多列索引）

如果使用联合索引（如 CREATE INDEX idx_a_b_c ON table(a, b, c)），查询时应从左往右包含索引字段。

-- ✅ 命中联合索引
SELECT * FROM table WHERE a = ? AND b = ?;

-- ❌ 无法使用索引
SELECT * FROM table WHERE b = ? AND c = ?;

3.3 ✅ 避免在 WHERE 子句中对索引字段做函数或计算操作

这会导致索引失效，转为全表扫描。

-- ❌ 索引失效
SELECT * FROM orders WHERE DATE(create_time) = '2024-06-01';

-- ✅ 推荐写法
SELECT * FROM orders 
WHERE create_time >= '2024-06-01' AND create_time < '2024-06-02';

3.4 ✅ 覆盖索引优于回表查询

如果查询字段全部包含在索引中，可避免回表（从数据页再取数据），效率更高。

-- 假设有联合索引 (user_id, name, age)
-- ✅ 覆盖索引，无需访问表数据
SELECT user_id, name FROM users WHERE user_id = 123;

3.5 ✅ 控制索引数量，避免冗余或重复

过多索引会导致维护开销变大，特别是写操作变慢。

-- ❌ 重复索引，无意义
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_name_age ON users(name, age);  -- 已包含 name

3.6 ✅ 对排序字段添加索引可提升 ORDER BY 性能

但要注意：ORDER BY 多字段时字段顺序必须和索引一致。

-- 假设有索引 (user_id, create_time)
-- ✅ 可利用索引排序
SELECT * FROM orders WHERE user_id = 1 ORDER BY create_time DESC;

-- ❌ 不匹配顺序，无法用索引排序
SELECT * FROM orders ORDER BY create_time, user_id;

3.7 ✅ 不要轻易在小表上建索引

对于行数很少（如几百行以内）的小表，索引带来的提升可能不明显，反而增加管理成本。

3.8 ✅ DML频繁字段慎重建索引

频繁更新的字段建索引会带来写入性能下降，甚至锁等待增加。

4、实战样例

示例表结构

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT,
  status TINYINT,
  total_price DECIMAL(10,2),
  create_time DATETIME
);

4.1 创建合理索引

-- 复合索引，适合查询最新订单
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);

4.2 正确查询用法（命中索引）

SELECT user_id, status, total_price 
FROM orders 
WHERE user_id = 1001 AND status = 1 
ORDER BY create_time DESC
LIMIT 10;

4.3 查看执行计划

EXPLAIN SELECT ...;

检查是否使用到 idx_user_status_time 索引。

5、总结

编号	注意事项	建议
1	避免对低选择性字段建索引	优先唯一性高的字段
2	联合索引遵循最左前缀原则	字段顺序要慎重
3	避免对索引字段使用函数/表达式	保持字段“裸用”
4	用覆盖索引替代回表	SELECT 字段包含索引字段
5	控制索引数量，避免重复	定期检查冗余索引
6	排序字段加索引	注意排序顺序与索引一致
7	小表不建索引	小表扫描更快
8	写频繁字段慎建索引	更新成本高

🙌 扩展工具推荐

✅ SHOW INDEX FROM table：查看表已有索引
✅ EXPLAIN SELECT ...：分析是否命中索引
✅ pt-duplicate-key-checker：Percona 工具查冗余索引
✅ slow_query_log + pt-query-digest：定位未命中索引的慢查询

MySQL：创建索引需要关注哪些细节？