六、索引优化实战案例

索引优化实战案例：电商商品查询优化

一、模拟数据环境

1. 创建测试表

-- 创建商品表（无索引）
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    category VARCHAR(50),
    price DECIMAL(10,2),
    stock INT,
    created_at DATETIME
);

-- 插入100万条测试数据
-- 使用存储过程随机生成数据（此处简化展示）
INSERT INTO products (name, category, price, stock, created_at)
VALUES 
  ('iPhone 15', '手机', 7999.00, 100, NOW()),
  ('小米电视', '家电', 2999.00, 50, NOW()),
  -- ... 插入100万条类似数据

二、优化前情况分析

1. 典型查询场景

-- 查询手机类商品，按价格排序，分页查看
SELECT id, name, price 
FROM products 
WHERE category = '手机'
ORDER BY price DESC
LIMIT 100000, 20;  -- 查看第10001-10020条

2. 执行计划分析（EXPLAIN）

EXPLAIN SELECT id, name, price 
FROM products 
WHERE category = '手机'
ORDER BY price DESC
LIMIT 100000, 20;

优化前执行计划结果：

id	type	possible_keys	key	rows	Extra
1	ALL	NULL	NULL	997200	Using where; Using filesort

关键问题：

• type=ALL：全表扫描
• Using filesort：文件排序（内存/磁盘临时表）
• rows=997200：扫描了几乎所有数据

3. 性能表现

• 执行时间：2.8秒
• 扫描行数：1,000,020行（满足条件的10万条 + 前10万条丢弃）

---

三、索引优化方案

1. 创建联合索引

-- 对 WHERE条件字段（category）和 ORDER BY字段（price）创建联合索引
CREATE INDEX idx_category_price ON products(category, price);

2. 优化后执行计划

EXPLAIN SELECT id, name, price 
FROM products 
WHERE category = '手机'
ORDER BY price DESC
LIMIT 100000, 20;

优化后执行计划结果：

id	type	possible_keys	key	rows	Extra
1	ref	idx_category_price	idx_category_price	12000	Using index

优化效果：

• type=ref：索引范围扫描
• Using index：覆盖索引（直接从索引取数据，无需回表）
• rows=12000：实际扫描行数仅为符合条件的12万条

3. 性能表现

• 执行时间：0.18秒（提升15倍）
• 扫描行数：100,020行（直接通过索引定位）

---

四、深度优化：游标分页

1. 传统分页的问题

即使使用索引，LIMIT 100000, 20 仍然需要：

1. 扫描前100000条记录
2. 返回接下来的20条

2. 游标分页优化方案

-- 首次查询
SELECT id, name, price 
FROM products 
WHERE category = '手机'
ORDER BY price DESC
LIMIT 20;

-- 后续查询（记录上次最后一条的price和id）
SELECT id, name, price 
FROM products 
WHERE category = '手机'
  AND (price < 5999.00 OR (price = 5999.00 AND id > 123456))
ORDER BY price DESC
LIMIT 20;

3. 执行计划对比

分页方式	执行时间	扫描行数
传统分页	0.18秒	100,020
游标分页	0.003秒	20

---

五、优化总结

1. 优化前后对比

指标	优化前	优化后（索引）	游标分页
执行时间	2800ms	180ms	3ms
扫描行数	1,000,020	100,020	20
排序方式	文件排序	索引排序	索引排序
磁盘I/O	高	中	低

2. 索引选择依据

1. WHERE条件字段：category 作为查询条件
2. ORDER BY字段：price 需要排序
3. 覆盖索引：查询字段（id, name, price）都在索引中

3. 注意事项

• 索引维护成本：该索引会增加约 150MB 存储空间（100万条数据）
• 写操作影响：INSERT/UPDATE/DELETE 时需要维护索引
• 数据分布：如果category区分度低（如90%都是手机），索引效果会下降

---

通过这个案例可以直观看到，合理使用索引可以将查询性能提升数十倍。建议在实际业务中：

1. 使用 EXPLAIN 分析慢查询
2. 优先为高频查询创建覆盖索引
3. 对深度分页采用游标分页方案