企业官网建设流程全解析

1. 电商后台商品排序实战：ORDER BY的深度应用

在电商平台的商品管理后台，排序功能是提升用户体验的核心要素。想象一下，当用户搜索"智能手机"时，默认按销量降序排列，而点击"价格排序"按钮后立即切换为按价格升序排列——这背后就是ORDER BY在发挥作用。

多字段排序的典型场景： 假设我们需要为运营人员提供一个商品列表，要求优先显示库存紧张（goods_number < 10）的商品，再按销量降序排列，最后按价格升序排列。对应的SQL应该是：

SELECT goods_id, goods_name, goods_number, sales, price FROM products WHERE category_id = 5 -- 手机分类 ORDER BY CASE WHEN goods_number < 10 THEN 0 ELSE 1 END, -- 库存紧张优先 sales DESC, -- 销量降序 price ASC; -- 价格升序

这个案例中，我们使用了CASE WHEN实现条件排序，这是很多新手容易忽略的高级技巧。通过这种组合排序，运营人员可以快速识别需要补货的热销商品。

排序性能优化要点：

• 为所有参与排序的字段建立复合索引（如本例中的(sales, price)）
• 避免对TEXT/BLOB类型字段直接排序
• 大数据量排序时考虑增加tmp_table_size参数值

提示：在MySQL 8.0+版本中，可以创建函数索引来优化带条件的排序查询，如创建索引：(CASE WHEN goods_number < 10 THEN 0 ELSE 1 END, sales, price)

2. 商品分类统计：GROUP BY的实战技巧

商品分类统计是电商后台最常见的分析需求。假设我们需要统计每个二级分类下的商品数量、平均价格和最高销量，传统的写法是：

SELECT category_id, COUNT(*) AS product_count, AVG(price) AS avg_price, MAX(sales) AS max_sales FROM products GROUP BY category_id;

但这样只能显示分类ID，运营人员还需要二次查询才能知道分类名称。更专业的做法是使用JOIN和多级分组：

SELECT c1.cat_name AS '一级分类', c2.cat_name AS '二级分类', COUNT(p.goods_id) AS '商品数量', CONCAT(FORMAT(AVG(p.price),2), '元') AS '平均价格', MAX(p.sales) AS '最高销量' FROM products p JOIN categories c2 ON p.category_id = c2.cat_id JOIN categories c1 ON c2.parent_id = c1.cat_id GROUP BY c1.cat_id, c2.cat_id WITH ROLLUP; -- 增加汇总行

WITH ROLLUP的妙用： 这个关键字会在结果集中添加汇总行，比如在所有分类统计完成后，最后会加一行所有分类的汇总数据。这在制作报表时特别有用，不需要应用层再做额外计算。

分组过滤的陷阱： 很多新手会混淆WHERE和HAVING的使用时机。记住这个原则：WHERE在分组前过滤原始数据，HAVING在分组后过滤聚合结果。例如要找出平均价格超过3000元的分类：

SELECT category_id, AVG(price) AS avg_price FROM products WHERE is_on_sale = 1 -- 只统计上架商品 GROUP BY category_id HAVING avg_price > 3000;

3. 分页查询优化：LIMIT的进阶玩法

电商后台的商品列表几乎都会采用分页展示。最基础的分页写法大家都很熟悉：

-- 第一页 SELECT * FROM products ORDER BY create_time DESC LIMIT 0, 20; -- 第二页 SELECT * FROM products ORDER BY create_time DESC LIMIT 20, 20;

但当数据量达到百万级时，这种写法在翻到后面几页时会越来越慢，因为MySQL需要先扫描并丢弃前面的所有记录。以下是几种优化方案：

方案一：记住上一页的最大ID

-- 假设上一页最后一条记录的ID是1020 SELECT * FROM products WHERE id > 1020 ORDER BY id LIMIT 20;

方案二：使用覆盖索引+延迟关联

SELECT p.* FROM products p JOIN ( SELECT id FROM products ORDER BY create_time DESC LIMIT 100000, 20 ) AS tmp ON p.id = tmp.id;

特殊场景：获取每个分类的前N个商品这是典型的"分组Top N"问题，在MySQL中有多种实现方式：

-- 方法1：使用派生表（MySQL 5.7+） SELECT p.* FROM products p WHERE ( SELECT COUNT(*) FROM products WHERE category_id = p.category_id AND sales >= p.sales ) <= 3 -- 每个分类取前3名 ORDER BY p.category_id, p.sales DESC; -- 方法2：使用窗口函数（MySQL 8.0+） SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rn FROM products ) AS ranked WHERE rn <= 3;

4. 综合实战：商品筛选系统开发

现在我们来设计一个完整的商品筛选接口，支持以下功能：

• 按分类、价格区间、品牌筛选
• 支持多种排序方式
• 分页加载
• 显示分类统计信息

SQL示例：

-- 1. 先获取分类统计信息（用于左侧筛选栏） SELECT c.cat_id, c.cat_name, COUNT(p.goods_id) AS product_count, MIN(p.price) AS min_price, MAX(p.price) AS max_price FROM categories c LEFT JOIN products p ON c.cat_id = p.category_id AND p.is_on_sale = 1 WHERE c.parent_id = 1 -- 手机大类 GROUP BY c.cat_id; -- 2. 获取商品列表（带分页） SELECT p.*, b.brand_name FROM products p JOIN brands b ON p.brand_id = b.brand_id WHERE p.category_id IN (5,6,7) -- 二级分类 AND p.price BETWEEN 2000 AND 5000 AND p.brand_id IN (1,3,5) ORDER BY CASE WHEN :sort = 'price_asc' THEN p.price END ASC, CASE WHEN :sort = 'price_desc' THEN p.price END DESC, CASE WHEN :sort = 'sales' THEN p.sales END DESC LIMIT :offset, :page_size; -- 3. 获取当前筛选条件下的总记录数（用于分页计算） SELECT COUNT(*) AS total FROM products WHERE category_id IN (5,6,7) AND price BETWEEN 2000 AND 5000;

性能优化建议：

1. 为所有筛选条件字段建立复合索引，例如：(category_id, price, brand_id)
2. 使用Redis缓存分类统计结果，设置5分钟过期时间
3. 对于总记录数查询，大数据量表考虑使用EXPLAIN的rows近似值替代COUNT(*)
4. 前端实现无限滚动加载，减少深分页查询

在实际项目中，我发现很多开发者在处理排序字段动态变化时，喜欢用字符串拼接SQL，这存在SQL注入风险。推荐使用上面示例中的CASE WHEN方案，既安全又便于维护。