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;性能优化建议为所有筛选条件字段建立复合索引例如(category_id, price, brand_id)使用Redis缓存分类统计结果设置5分钟过期时间对于总记录数查询大数据量表考虑使用EXPLAIN的rows近似值替代COUNT(*)前端实现无限滚动加载减少深分页查询在实际项目中我发现很多开发者在处理排序字段动态变化时喜欢用字符串拼接SQL这存在SQL注入风险。推荐使用上面示例中的CASE WHEN方案既安全又便于维护。