在后端系统中，数据库往往是性能瓶颈 —— 当数据量增长到百万、千万级，一条缺少索引的 SQL 可能耗时几秒甚至几分钟，拖垮整个应用。索引作为数据库的 “目录”，能让查询从 “全表扫描” 变为 “精准定位”，但不合理的索引设计（如冗余索引、失效索引）反而会降低性能。索引优化，是每个后端开发者必须掌握的核心技能。
索引的核心价值与工作原理
索引的核心价值是 “加速查询，减速写入”：
-
加速查询：通过 B + 树等数据结构，将无序数据变为有序，查询时无需扫描全表
-
减速写入：新增、更新、删除数据时，需同步维护索引结构，增加 IO 开销
B + 树索引原理：
- 叶子节点存储数据地址（聚簇索引）或主键（非聚簇索引）
- 非叶子节点只存索引值和指针，用于快速定位
- 适合范围查询（如
BETWEEN、> <）和等值查询（如=）
索引设计的黄金法则
1. 最左前缀匹配原则：联合索引的 “潜规则”
联合索引（如(a, b, c)）遵循 “最左前缀匹配”，即查询条件包含最左列（a）时才能用到索引，以下情况会命中索引：
-
WHERE a = 1 -
WHERE a = 1 AND b = 2 -
WHERE a = 1 AND b = 2 AND c = 3
以下情况不会命中或只能部分命中：
-
WHERE b = 2（缺少最左列 a） -
WHERE a = 1 AND c = 3（中间列 b 缺失，只能用到 a 的索引） -
WHERE b = 2 AND a = 1（条件顺序不影响，数据库会优化顺序，但仍需包含最左列）
设计建议：
- 将过滤性强的列放在联合索引左侧（如性别列过滤性差，不适合放左侧）
- 避免重复索引（如已有
(a, b)，无需再建(a)）
2. 避免索引失效：这些情况会让索引 “罢工”
-
函数或运算操作：
WHERE SUBSTR(phone, 1, 3) = '138'（对索引列做函数处理） -
隐式类型转换：
WHERE phone = 13800138000（phone 是字符串，与数字比较会转换） -
模糊查询前缀 % ：
WHERE name LIKE '%三'（前缀模糊匹配，索引失效） -
OR 条件中有非索引列：
WHERE a = 1 OR b = 2（b 无索引，导致整个查询全表扫描） -
NOT IN / NOT EXISTS：通常会导致全表扫描（小表除外）
反例：
-- 索引：(phone, create_time)
SELECT * FROM user
WHERE SUBSTR(phone, 1, 3) = '138' -- 函数操作，索引失效
AND create_time > '2023-01-01';
正例：
-- 优化：避免函数操作，调整查询方式
SELECT * FROM user
WHERE phone LIKE '138%' -- 后缀模糊，索引有效
AND create_time > '2023-01-01';
3. 索引不是越多越好：控制索引数量
- 单表索引数量建议不超过 5-6 个，过多会导致写入性能下降
- 频繁更新的列不适合建索引（如订单状态，更新时需频繁维护索引）
- 小表（数据量 < 1 万）无需建索引（全表扫描更快）
索引优化实战技巧
1. 慢查询分析：找到需要优化的 SQL
通过开启 MySQL 慢查询日志，定位执行时间长的 SQL：
# my.cnf 配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1 # 执行时间超过1秒的视为慢查询
log_queries_not_using_indexes = 1 # 记录未使用索引的查询
使用EXPLAIN分析 SQL 执行计划：
EXPLAIN SELECT * FROM order WHERE user_id = 123 AND status = 'PAID';
关注type字段（const> eq_ref> ref> range> index> ALL，ALL表示全表扫描）和key字段（是否用到索引）。
2. 常用场景的索引设计
-
单表查询：根据
WHERE、ORDER BY、GROUP BY字段设计索引- 例：
SELECT * FROM product WHERE category_id = 5 ORDER BY price DESC→ 索引(category_id, price DESC)
- 例：
-
联表查询：在关联字段（如
product_id）上建索引- 例：
SELECT * FROM order JOIN order_item ON order.id = order_item.order_id→ 给order_item.order_id建索引
- 例：
-
分页查询：避免
LIMIT 100000, 10（扫描前 100010 行），改用WHERE id > 100000 LIMIT 10（依赖主键有序性）
3. 索引维护：定期 “体检”
- 定期删除冗余索引（通过
sys.schema_unused_indexes查看未使用的索引） - 重建碎片化索引（
OPTIMIZE TABLE table_name，适用于 InnoDB） - 大表加索引需谨慎（避免锁表，可使用
ALTER TABLE ... ADD INDEX ... LOCK=NONE在线加索引）
避坑指南
-
不要用
SELECT *：只查询需要的字段，避免回表查询（非聚簇索引需回表查数据） -
避免索引列存 NULL 值：NULL 会影响索引效率，建议设置默认值（如空字符串）
-
联合索引顺序不是越左越好：需结合业务查询频率，如
(a, b)和(b, a)哪个更常用
数据库索引优化的本质是 “平衡查询与写入性能”—— 没有放之四海而皆准的完美索引，只有适合业务场景的合理设计。后端开发者需要结合数据量、查询频率、更新频率，不断迭代优化索引策略，这是一个 “持续改进” 而非 “一劳永逸” 的过程。
