索引的优缺点

一句话

索引以”读快、写慢、占空间”为代价换查询性能。 加索引前先想：这字段查得多吗？选择性够不够？

一、优点

优点	说明
加速 WHERE 查询	从 O(N) 全表扫描 → O(log N) B+ 树查找
加速 ORDER BY / GROUP BY	索引天然有序，省去 filesort
加速 JOIN	关联字段有索引时大幅提速
唯一索引保证唯一性	数据约束 + 查询双重价值
覆盖索引免回表	二级索引就能返回所有需要的列

二、缺点

缺点	说明	量化
占空间	每个索引都是一棵 B+ 树	一个 INT 索引在 100w 行表上约 30MB
写入变慢	INSERT/UPDATE/DELETE 都要维护所有相关索引	每多一个索引，写入慢 5-15%
优化器选错索引	索引太多反而干扰优化器	用 FORCE INDEX 兜底
维护成本	在线加索引要锁表/影响性能	大表用 pt-online-schema-change

三、加索引的判断标准

✅ 该加：

• WHERE / ORDER BY / GROUP BY / JOIN 高频用到
• 字段选择性高（不同值多）：cardinality / total_rows > 0.1
• 表大（万行以上），加索引 ROI 才高

❌ 不该加：

• 字段值极少（性别、是否启用）→ 索引基本无效
• 表很小（千行以下）→ 全表扫描更快
• 写入远多于读取（日志表）→ 索引拖累写入
• 字段经常更新 → 每次更新都重建索引节点

四、检查索引使用情况

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

-- 看索引选择性（cardinality 越接近行数越好）
SELECT
    table_name, index_name, cardinality
FROM information_schema.statistics
WHERE table_schema = 'mydb';

-- 找出"从没被用过"的索引（MySQL 5.7+）
SELECT * FROM sys.schema_unused_indexes;

-- 找出"冗余索引"
SELECT * FROM sys.schema_redundant_indexes;

五、常见反模式

1. 每个字段都加索引 → 写入崩溃
2. 联合索引乱序 → (a, b, c) 和 (b, a, c) 完全是不同的索引
3. 加了索引不知道有没有用 → 必须 EXPLAIN 验证
4. VARCHAR 全字段索引 → 用前缀索引：KEY idx_name (name(20))

参考

• MySQL 文档 - Optimization and Indexes: https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html
• 《高性能 MySQL》第 5 章