五、数据库索引详解：作用、原理与使用指南

最新推荐文章于 2025-10-27 09:17:16 发布

原创最新推荐文章于 2025-10-27 09:17:16 发布 · 1.4k 阅读

16 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #oracle

数据库专栏收录该内容

8 篇文章

订阅专栏

数据库索引详解：作用、原理与使用指南

一、索引的作用（为什么需要索引？）

1. 生活中的类比

想象你要在一本 500 页的书中快速找到「数据库索引」相关内容：

没有目录：需要逐页翻找 → 全表扫描
有目录：直接定位到第 320 页 → 索引查询

2. 数据库中的核心作用

加速数据检索：减少磁盘 I/O 次数
优化排序和分组：避免临时表排序
强制唯一性：通过唯一索引保证数据唯一

3. 性能对比示例

数据量	无索引查询耗时	有索引查询耗时
10万条	200ms	5ms
1000万条	20秒	10ms

二、索引的工作原理

1. B-Tree 索引结构（最常用）

       [ 根节点 ]
     /     |     \
[分支节点][分支节点][分支节点]
 /  |  \          /  |  \
 [叶子节点]... [叶子节点]...

2. 查找过程演示（查找 id=25）

根节点判断 25 在 20-30 区间
进入第二个分支节点
找到叶子节点中的 id=25 记录
通过指针获取完整数据

3. 索引类型对比

索引类型	特点	适用场景
B-Tree	支持范围查询、排序	大多数场景（默认选择）
Hash	精确匹配快，不支持范围	内存表、等值查询
全文	文本关键词搜索	文章内容检索

三、索引的创建与使用

1. 创建索引的 SQL 语法

-- 基本语法
CREATE [UNIQUE] INDEX 索引名称 
ON 表名 (字段1, 字段2...);

-- 创建普通索引
CREATE INDEX idx_user_name ON users(name);

-- 创建联合索引
CREATE INDEX idx_user_age_city ON users(age, city);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_user_email ON users(email);

2. 最佳使用场景

使用场景	示例 SQL	推荐索引
WHERE 条件字段	`SELECT * FROM users WHERE age=25`	`INDEX(age)`
ORDER BY 排序字段	`SELECT * FROM products ORDER BY price`	`INDEX(price)`
JOIN 关联字段	`SELECT * FROM orders JOIN users ON orders.user_id=users.id`	`INDEX(user_id)`
覆盖索引（查询字段全在索引中）	`SELECT name FROM users WHERE age>20`	`INDEX(age, name)`

四、索引使用注意事项

1. 创建原则

高频查询字段优先
高区分度字段优先（如身份证号比性别更适合）
联合索引字段顺序：常用字段在前，区分度高的在前

2. 最左前缀原则

对于联合索引 (A,B,C)：

✅ 有效：WHERE A=1 AND B=2
✅ 有效：WHERE A=1 ORDER BY B
❌ 无效：WHERE B=2 AND C=3

3. 避免索引失效的常见情况

错误写法	正确写法	原因
`WHERE YEAR(create_time)=2023`	`WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'`	对索引列使用函数
`WHERE name LIKE '%张'`	`WHERE name LIKE '张%'`	前导通配符
`WHERE age+10 > 30`	`WHERE age > 20`	对索引列进行运算

五、索引的优缺点分析

优点

提升查询速度（量级提升）
加速表之间的连接
减少排序和分组时间

缺点

增加存储空间：索引通常占数据量的 10-30%
降低写操作速度：每次 INSERT/UPDATE/DELETE 需要维护索引
维护成本：需要定期优化重建索引

六、实战案例：电商系统索引优化

原始表结构

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    category VARCHAR(50),
    price DECIMAL(10,2),
    stock INT,
    created_at DATETIME
);

常见查询场景与索引方案

场景 1：按分类和价格筛选商品

SELECT * FROM products 
WHERE category='电子产品' 
  AND price BETWEEN 1000 AND 5000
ORDER BY created_at DESC;

索引方案：

CREATE INDEX idx_category_price ON products(category, price);

场景 2：按名称搜索商品

SELECT * FROM products 
WHERE name LIKE '苹果%';

索引方案：

CREATE INDEX idx_product_name ON products(name);

场景 3：统计库存紧张的热销商品

SELECT category, COUNT(*) 
FROM products 
WHERE stock < 50 
GROUP BY category;

索引方案：

CREATE INDEX idx_stock_category ON products(stock, category);

七、索引管理命令

1. 查看索引

SHOW INDEX FROM products;

2. 删除索引

DROP INDEX idx_name ON products;

3. 索引维护

-- 重建索引（InnoDB）
ALTER TABLE products ENGINE=InnoDB;

-- 分析索引使用情况
ANALYZE TABLE products;

最佳实践总结

不是越多越好：通常单表索引不超过 5 个
定期检查无用索引：通过慢查询日志分析
优先考虑联合索引：减少索引数量
监控索引效果：使用 EXPLAIN 验证