MySQL索引：数据库的超级目录
MySQL索引：数据库的「超级目录」
想象你有一本1000页的百科全书，要快速找到某个知识点（如“光合作用”）：
- ❌ 无索引：逐页翻找 → 全表扫描（慢！）
- ✅ 有索引：直接查目录 → 精准定位（快！）
索引的本质：预先对数据排序+存储位置信息，加速检索的特殊数据结构。
一、索引类型及原理
1. 数据结构
| 索引类型 | 数据结构 | 适用场景 | 特点 |
|---|---|---|---|
| B+树索引 | 多叉平衡树 | 默认索引（InnoDB） | 范围查询快，适合磁盘存储 |
| 哈希索引 | 哈希表 | 精确匹配（Memory引擎） | 等值查询极快，不支持范围查询 |
| 全文索引 | 倒排索引 | 文本搜索（MATCH AGAINST） | 解决LIKE '%word%'低效问题 |
📌 B+树为什么快？
- 叶子节点形成链表 → 范围查询高效（如
WHERE id > 100）- 非叶子节点只存索引 → 单节点存储更多key
- 所有数据在叶子节点 → 查询路径长度一致
2. 逻辑分类
| 索引类型 | 描述 | 示例 |
|---|---|---|
| 主键索引 | 唯一标识，不允许NULL | PRIMARY KEY (id) |
| 唯一索引 | 保证列值唯一，允许NULL | UNIQUE KEY (email) |
| 普通索引 | 加速查询，允许重复值 | INDEX (name) |
| 联合索引 | 多列组合索引 | INDEX (city, age) |
二、索引生效与失效场景
✅ 生效场景
-- 1. 全值匹配
SELECT * FROM users WHERE name = 'Alice'; -- 2. 最左前缀原则（联合索引）
INDEX (a, b, c) -- 生效: WHERE a=? / WHERE a=? AND b=? / WHERE a=? AND b=? AND c=? -- 3. 范围查询（部分生效）
SELECT * FROM orders WHERE amount > 100 AND status = 1; -- (amount)索引生效 -- 4. 覆盖索引（直接从索引拿数据）
SELECT id FROM products WHERE price > 50; -- 索引包含(id,price)
❌ 失效场景
-- 1. 违反最左前缀
INDEX (a, b, c)
WHERE b = 2; -- ❌ 索引失效 -- 2. 对索引列运算
WHERE YEAR(create_time) = 2023; -- ❌ 改用: create_time BETWEEN '2023-01-01' AND '2023-12-31' -- 3. 隐式类型转换
WHERE phone = 13800138000; -- ❌ phone是varchar类型 -- 4. LIKE左模糊
WHERE name LIKE '%Lee'; -- ❌ 全表扫描 -- 5. OR条件未全覆盖
WHERE age = 18 OR name = 'Bob'; -- 若name无索引 → 全表扫描
三、索引优化策略
1. EXPLAIN诊断工具
EXPLAIN SELECT * FROM employees WHERE department_id = 3;
关键指标：
- type：
system > const > ref > range > index > ALL（性能从优到差） - key：实际使用的索引
- rows：扫描行数（越小越好）
2. 设计原则
| 策略 | 说明 |
|---|---|
| 只为高频查询建索引 | 避免维护成本（增删改变慢） |
| 短字段优先 | 整型索引比字符串快，考虑用city_code代替city_name |
| 避免冗余索引 | INDEX(a,b) 和 INDEX(a) 同时存在 → 后者冗余 |
| 前缀索引 | 长文本可截取前N字符：ALTER TABLE t ADD INDEX (text_col(10)) |
3. 慢查询优化示例
问题SQL：
SELECT * FROM logs WHERE user_id = 1001 AND DATE(create_time) = '2023-10-01';
优化步骤：
- 避免对
create_time计算 → 改用范围查询 - 建立联合索引
(user_id, create_time)
-- 优化后
SELECT * FROM logs
WHERE user_id = 1001 AND create_time >= '2023-10-01 00:00:00' AND create_time < '2023-10-02 00:00:00';
四、索引的代价
- 空间代价：索引占用额外存储（特别是B+树的非叶子节点）
- 时间代价：
INSERT：需更新索引 → 性能下降约10%UPDATE：若修改索引列 → 触发索引重组DELETE：标记删除 → 产生索引碎片
💡 黄金法则：
不要为小表建索引（全表扫描更快）
中大型表重点优化WHERE和JOIN列
五、高级技巧
1. 索引下推（ICP）
MySQL 5.6+
INDEX (age, city)
SELECT * FROM users WHERE age > 20 AND city = 'Beijing';
- 旧版本：先按
age>20回表查数据 → 再过滤city - 开启ICP：在索引层直接过滤
city→ 减少回表次数
2. 覆盖索引优化
-- 原始查询
SELECT id, name FROM products WHERE category = 'Electronics'; -- 优化方案：
ALTER TABLE products ADD INDEX (category, id, name); -- 覆盖索引
数据直接从索引返回 → 避免回表查主键
总结：索引使用指南
-
建索引前问3个问题：
- 数据量是否足够大？
- 查询频率是否高？
- 该字段过滤性是否好？（如性别字段不适合单独建索引）
-
优先考虑：
WHERE条件列、JOIN关联列、ORDER BY排序列
-
定期维护：
ANALYZE TABLE users; -- 更新索引统计信息 OPTIMIZE TABLE orders; -- 重建表+索引（解决碎片问题）
🚨 最后警告：
索引不是越多越好！
- 表数据量<1万 → 通常不需要索引
- 每增加一个索引 →
INSERT速度降低约10%