索引优化

引言 本文通过5个真实案例，综合运用索引优化知识，展示完整的优化过程。 案例1：订单列表查询优化 1.1 问题 -- 慢SQL SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20; -- 执行时间：5秒 1.2 分析 EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20; 结果： type: ref key: idx_user_id rows: 50000 Extra: Using filesort ← 文件排序 问题： 扫描5万行 需要额外排序（filesort） 1.3 优化 -- 创建联合索引 CREATE INDEX idx_user_created ON orders(user_id, created_at); -- 验证 EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20; 结果： type: ref key: idx_user_created rows: 20 Extra: Using index condition ← 无filesort 1.4 效果 执行时间：5秒 → 0.01秒 性能提升：500倍 扫描行数：50000 → 20 案例2：分页查询深分页优化 2.1 问题 -- 慢SQL（翻到第5000页） SELECT * FROM products ORDER BY id LIMIT 100000, 20; -- 执行时间：8秒 2.2 分析 EXPLAIN SELECT * FROM products ORDER BY id LIMIT 100000, 20; 问题： ...

一、何时创建索引 1.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 DESC; CREATE INDEX idx_price ON products(price); -- 3. GROUP BY分组列 SELECT category, COUNT(*) FROM products GROUP BY category; CREATE INDEX idx_category ON products(category); -- 4. JOIN连接列 SELECT * FROM orders o JOIN users u ON o.user_id = u.id; CREATE INDEX idx_user_id ON orders(user_id); -- 5. DISTINCT去重列 SELECT DISTINCT category FROM products; CREATE INDEX idx_category ON products(category); 1.2 不适合创建索引的场景 -- 1. 小表（< 1000行） -- 全表扫描更快 -- 2. 频繁更新的列 -- 维护索引代价高 -- 3. 区分度低的列（选择性 < 0.01） SELECT * FROM users WHERE gender = '男'; -- 50%数据 -- 不如全表扫描 -- 4. 不在WHERE/ORDER BY/JOIN中使用的列 -- 纯粹浪费空间 二、选择索引列的原则 2.1 选择性高的列 -- 计算列的选择性 SELECT COUNT(DISTINCT column) / COUNT(*) AS selectivity FROM table_name; -- 选择性 > 0.1：适合索引 -- 选择性 < 0.01：不适合索引 示例： ...