六、索引优化实战案例

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

一、模拟数据环境

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;

优化前执行计划结果

idtypepossible_keyskeyrowsExtra
1ALLNULLNULL997200Using 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;

优化后执行计划结果

idtypepossible_keyskeyrowsExtra
1refidx_category_priceidx_category_price12000Using 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. 优化前后对比

指标优化前优化后(索引)游标分页
执行时间2800ms180ms3ms
扫描行数1,000,020100,02020
排序方式文件排序索引排序索引排序
磁盘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. 对深度分页采用游标分页方案
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值