SQL索引需建表前预判、线上安全添加并避开失效陷阱。主键自动建聚簇索引；外键和高频查询字段应手动建索引；组合索引遵循最左前缀原则；MySQL用ALGORITHM=INPLACE，PostgreSQL用CONCURRENTLY；避免对索引列使用函数或运算。
SQL索引不是“建了就快”，关键在选对字段、用对类型、避开常见坑。下面按真实开发节奏，一步步带你从建表前预判到上线后优化，覆盖大多数业务场景。
一、建表时就该想好的索引设计
别等慢查询报警才加索引——那已经晚了。建表阶段就要结合主键、唯一性、高频查询条件做预判：
-
主键自动建聚簇索引：比如
CREATE TABLE user (id BIGINT PRIMARY KEY, name VARCHAR(50))，id列天然带高效索引，不用额外加 -
外键列建议手动加索引：如订单表
user_id是外键，且常用于JOIN或WHERE user_id = ?，就该立刻建：CREATE INDEX idx_order_user_id ON orders(user_id); -
组合索引注意最左前缀原则：如果经常查
WHERE status = 'paid' AND created_at > '2024-01-01'，建INDEX idx_status_created ON orders(status, created_at)；但反过来查created_at单独条件就用不上这个索引
二、给已有表安全加索引（生产环境必看）
线上加索引可能锁表、拖慢写入，尤其大表。不同数据库策略不同：
-
MySQL 5.6+：默认支持
ALGORITHM=INPLACE，不锁表（但仍有短暂元数据锁），推荐显式写法：ALTER TABLE logs ADD INDEX idx_log_type_time (log_type, create_time) ALGORITHM=INPLACE, LOCK=NONE; -
PostgreSQL：直接用
CONCURRENTLY，完全不阻塞读写：CREATE INDEX CONCURRENTLY idx_user_email ON users(email);（注意：不能在事务块中执行） -
小提醒：加索引前先
EXPLAIN确认原查询是否真走全表扫描；加完再EXPLAIN ANALYZE验证效果
三、哪些情况索引会“失效”？避坑清单
建了索引却没用上？多半掉进这些坑里：
-
对索引列做函数/运算：如
WHERE YEAR(create_time) = 2024→ 改成WHERE create_time >= '2024-01-01' AND create_time -
隐式类型转换：
user_id是BIGINT，但写成WHERE user_id = '123'→ 字符串强制转数字，索引失效 -
LIKE 通配符开头：
WHERE name LIKE '%张%'无法用索引；可考虑全文索引或倒排表替代 -
OR 条件混用无索引列：
WHERE a = 1 OR b = 2，若只有a有索引，整个条件可能放弃索引 → 拆成UNION或补全索引
四、进阶技巧：让索引更省空间、更精准
不只是“能用”，还要“用得好”：
-
前缀索引节省空间：对长文本字段（如邮箱、URL），不必索引全字段：
CREATE INDEX idx_user_email_prefix ON users(email(32));（前32字符足够区分大部分邮箱） -
覆盖索引减少回表：把查询用到的字段都放进索引，避免查索引后再去主表捞数据。
例如常用SELECT id, name, email FROM users WHERE status = 'active'，可建：CREATE INDEX idx_status_cover ON users(status, id, name, email); -
部分索引（PostgreSQL / MySQL 8.0+）只索引满足条件的数据：
CREATE INDEX idx_active_orders ON orders(user_id) WHERE status = 'paid';（大幅缩小索引体积，提升写入速度）
基本上就这些。索引不是越多越好，而是刚好够用、刚好命中、刚好不拖累写入。每次加索引前，问自己三个问题：这个查询真的慢吗？慢在哪一步？加了这个索引，其他写操作会变卡吗？答案清楚了，再动手。
