SQL 进阶与索引优化

一、多表查询

商品列表要带分类名，就得 JOIN 两张表：

SELECT p.id, p.name, p.price, c.name AS category_name
FROM product p
LEFT JOIN category c ON p.category_id = c.id
WHERE p.deleted = 0 AND p.status = 1
ORDER BY p.id DESC
LIMIT 0, 10;

JOIN 类型：

类型	语义	使用场景
INNER JOIN	只取两边匹配的数据	商品必有分类
LEFT JOIN	左表全保留，右表无则 NULL	订单明细查商品（商品可能已删）
RIGHT JOIN	反之，很少用	—

第 6 部分把这条 SQL 写进了 ProductMapper.xml。

二、聚合查询（统计用）

-- 每个分类下的商品数
SELECT category_id, COUNT(*) AS cnt
FROM product
WHERE deleted = 0
GROUP BY category_id
ORDER BY cnt DESC;

三、子查询

-- 价格高于平均价的商品
SELECT name, price FROM product
WHERE price > (SELECT AVG(price) FROM product WHERE deleted = 0 AND status = 1);

四、索引：查询提速的关键

索引原理（一句话版）

索引 = 排好序的数据结构（B+Tree）。没有索引时 MySQL 全表扫描，有索引时像查字典一样跳过大量行。

-- 商品名模糊搜索很常见的两个字段
CREATE INDEX idx_product_name ON product (name);
CREATE INDEX idx_product_status ON product (status);

-- 联查：订单按用户查询
CREATE INDEX idx_order_user ON `order` (user_id);

-- 组合索引（两个条件一起查，走一个索引）
CREATE INDEX idx_order_user_status ON `order` (user_id, status);

索引使用原则（面试必问）

索引失效的经典情况

SELECT * FROM product WHERE name LIKE '%手机%';   -- 左模糊，索引失效
SELECT * FROM product WHERE YEAR(created_at) = 2026;  -- 函数包裹，索引失效
SELECT * FROM product WHERE name = '手机' OR price > 100;  -- 复杂 OR 可能失效
SELECT * FROM product WHERE status = 1 AND name = '手机';  -- 组合索引没走最左前缀

原则	说明
最左前缀	组合索引 (user_id, status) 查询必须从 user_id 开始
少用 SELECT *	只取所需列，减少回表
避免函数/运算包字段	WHERE name LIKE '手机%' 可以，%手机 不行
区分度高的列建索引	性别(0/1)区分度低，收益小；username 区分度高

五、慢查询排查（EXPLAIN）

一条查询慢，用 EXPLAIN 看它是怎么执行的：

EXPLAIN SELECT * FROM order o
JOIN order_item oi ON oi.order_id = o.id
WHERE o.user_id = 1;

重点看两个字段：

• type：ALL = 全表扫描（危险！）；range/ref/eq_ref/const = 用上了索引（好）。
• key：实际所用的索引；NULL = 没走索引。
• rows：预估扫描行数，越小越好。

-- 如果 type=ALL，立即建索引
CREATE INDEX idx_order_user ON `order` (user_id);

六、分页优化（深分页问题）

LIMIT 100000, 10 会扫描 10 万行再丢弃。大偏移量用游标分页 / 延迟关联：

-- 慢：LIMIT 100000, 10 要扫 10 万行
SELECT * FROM product ORDER BY id LIMIT 100000, 10;

-- 快：先只查 id，再取数据（覆盖索引减少回表）
SELECT p.* FROM product p
JOIN (SELECT id FROM product ORDER BY id LIMIT 100000, 10) t
ON p.id = t.id;

商城早期数据量不大，先用普通 LIMIT 即可。知道深分页是隐患，为进阶加分。

七、本章验收

1. 商品列表联查分类名的 SQL 能查出 category_name。
2. 统计每个分类商品数的聚合 SQL 结果正确。
3. 商城高频筛选字段都建了索引。
4. 能用 EXPLAIN 看 query 是否 type=ALL。

进阶方向（了解）

读写分离、分库分表（ShardingSphere）、Redis 缓存热点数据、ES 做全文搜索。这些都是"数据量大了怎么办"的解法，简历/面试加分项，本项目先用到 MySQL 基础优化。