六、索引优化实战案例-优快云博客

本文链接：https://blog.youkuaiyun.com/Bad_Things/article/details/145939571

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

一、模拟数据环境

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 仍然需要：

扫描前100000条记录
返回接下来的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;