引言
索引是数据库性能优化的关键。理解索引的本质，才能正确使用索引。
一、没有索引的查询
1.1 全表扫描
-- 查询id=1000的用户（无索引）
SELECT * FROM users WHERE id = 1000;
执行过程：
- 从第1行开始
- 逐行检查id是否等于1000
- 找到后返回
- 如果表有100万行，最坏情况需要扫描100万行
时间复杂度：O(n)
1.2 问题
- 查询慢（全表扫描）
- 资源消耗大（CPU、磁盘IO）
- 并发性能差
二、索引的本质
2.1 从二分查找说起
# 在有序数组中查找
def binary_search(arr, target):
left, right = 0, len(arr) - 1
while left <= right:
mid = (left + right) // 2
if arr[mid] == target:
return mid
elif arr[mid] < target:
left = mid + 1
else:
right = mid - 1
return -1
时间复杂度：O(log n)
关键：数据有序 + 快速定位
2.2 索引的原理
索引是一种排序的数据结构，存储列值和行位置的映射。
索引结构（简化示例）：
+-------+-----------+
| id值 | 行位置 |
+-------+-----------+
| 1 | 0x1000 |
| 10 | 0x1050 |
| 100 | 0x1100 |
| 1000 | 0x2000 | <-- 通过索引快速定位
+-------+-----------+
查询过程：
- 在索引中查找（快速，类似二分查找）
- 获取行位置
- 根据位置读取完整数据
三、为什么索引快？
3.1 减少扫描行数
-- 无索引：扫描100万行
SELECT * FROM users WHERE id = 1000; -- 100万次比较
-- 有索引：扫描log(100万) ≈ 20次比较
SELECT * FROM users WHERE id = 1000; -- 约20次比较
3.2 减少磁盘IO
磁盘IO是最慢的操作：
- 内存访问：纳秒级
- 磁盘IO：毫秒级（慢100万倍）
索引数据结构设计：
- 一次IO读取多个节点
- 减少磁盘访问次数
3.3 数据有序
-- 索引使排序和范围查询更快
SELECT * FROM users WHERE id BETWEEN 1000 AND 2000;
-- 索引已排序，快速定位起始位置和结束位置
四、索引的代价
4.1 存储空间
-- 查看索引大小
SELECT
TABLE_NAME,
INDEX_NAME,
ROUND(STAT_VALUE * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE TABLE_NAME = 'users';
4.2 写入性能
-- INSERT时需要：
-- 1. 插入数据行
-- 2. 更新所有索引
INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');
影响：
- INSERT慢
- UPDATE慢（如果更新索引列）
- DELETE慢
4.3 索引维护
- 数据变更时需要维护索引
- 页分裂（B+树节点满时）
- 索引碎片
五、索引的适用场景
5.1 适合创建索引
-- 1. WHERE条件列
SELECT * FROM orders WHERE user_id = 123;
CREATE INDEX idx_user_id ON orders(user_id);
-- 2. ORDER BY排序列
SELECT * FROM products ORDER BY price;
CREATE INDEX idx_price ON products(price);
-- 3. JOIN连接列
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
CREATE INDEX idx_user_id ON orders(user_id);
-- 4. GROUP BY分组列
SELECT category, COUNT(*) FROM products GROUP BY category;
CREATE INDEX idx_category ON products(category);
5.2 不适合创建索引
-- 1. 小表（几百行）
-- 全表扫描更快
-- 2. 频繁更新的列
-- 维护索引代价大
-- 3. 区分度低的列（性别、状态等）
-- 索引效果差
SELECT * FROM users WHERE gender = '男'; -- 50%数据，索引无用
-- 4. 不在WHERE/ORDER BY/JOIN中使用的列
-- 浪费空间
六、索引设计原则
6.1 选择性高的列
-- 计算列的选择性
SELECT
COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;
-- 选择性 > 0.1 考虑索引
-- 选择性 < 0.01 不建议索引
6.2 索引列顺序
-- 联合索引：最左前缀原则
CREATE INDEX idx_user_date ON orders(user_id, created_at);
-- 可用：
WHERE user_id = 123;
WHERE user_id = 123 AND created_at > '2024-01-01';
-- 不可用：
WHERE created_at > '2024-01-01'; -- 没有user_id
6.3 索引数量
建议：
- 单表索引数量 < 5个
- 过多索引影响写入性能
七、实战案例
案例1：查询优化前后对比
-- 准备100万测试数据
CREATE TABLE test_users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
email VARCHAR(100),
created_at DATETIME
);
-- 插入100万数据（省略）
-- 无索引查询
SELECT * FROM test_users WHERE email = 'test@example.com';
-- 耗时：2.5秒，扫描100万行
-- 创建索引
CREATE INDEX idx_email ON test_users(email);
-- 有索引查询
SELECT * FROM test_users WHERE email = 'test@example.com';
-- 耗时：0.01秒，扫描1行
性能提升：250倍！
案例2：复合查询优化
-- 查询条件
SELECT * FROM orders
WHERE user_id = 123 AND status = 'completed' AND created_at > '2024-01-01';
-- 创建联合索引
CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);
-- 查询性能从5秒降到0.01秒
八、总结
核心要点
- 索引本质：排序的数据结构，存储列值和行位置
- 为什么快：减少扫描行数、减少磁盘IO、数据有序
- 代价：存储空间、写入性能、维护成本
- 适用场景：WHERE、ORDER BY、JOIN、GROUP BY
- 设计原则：选择性高、合理顺序、控制数量
记忆口诀
索引本质数据结构，有序存储快速查，
减少扫描减少IO，时间复杂对数化。
空间换时间策略，写入性能有代价，
选择性高才创建，WHERE JOIN ORDER BY下。
本文字数：约2,500字 难度等级：⭐⭐⭐（索引基础）