MySQL实战50题：从排序到分页的进阶技巧

1. 排序查询的实战技巧排序是数据库查询中最基础也最常用的操作之一。在实际项目中我经常遇到需要按照特定规则展示数据的场景。比如电商网站的商品列表需要按价格排序新闻网站的文章需要按发布时间排序。MySQL的ORDER BY子句就是专门用来处理这类需求的利器。先来看个最简单的例子。假设我们有个商品表product包含id、productName、dir_id和salePrice字段。要按零售价从高到低排序SQL可以这样写SELECT id, productName, dir_id, salePrice FROM product ORDER BY salePrice DESC这里有几个关键点需要注意DESC表示降序ASC表示升序默认可以省略排序字段可以是表中的任何列包括计算字段多字段排序时优先级从左到右递减我在实际项目中遇到过这样一个案例需要先按商品分类排序同分类下再按价格从高到低排。对应的SQL是这样的SELECT id, productName, dir_id, salePrice FROM product ORDER BY dir_id ASC, salePrice DESC这个查询会先按dir_id升序排列对于dir_id相同的记录再按salePrice降序排列。这种多字段排序在报表类查询中特别常见。2. 分页查询的性能优化分页查询是Web应用中最常见的场景之一。记得我刚入行时经常看到这样的分页SQLSELECT * FROM product LIMIT 0, 10 -- 第一页 SELECT * FROM product LIMIT 10, 10 -- 第二页这种写法在数据量小的时候没问题但当表数据达到百万级时性能就会急剧下降。因为LIMIT的偏移量越大MySQL需要扫描的数据就越多。后来我学到了一个优化技巧使用覆盖索引延迟关联。比如要优化上面的查询可以这样改写SELECT * FROM product WHERE id (SELECT id FROM product ORDER BY id LIMIT 10000, 1) LIMIT 10这个查询的原理是先用子查询快速定位到偏移量对应的id然后再基于这个id做范围查询。实测在百万级数据表上性能能提升10倍以上。另一个常见的分页陷阱是使用ORDER BY配合LIMIT时如果排序字段有重复值可能会导致分页结果出现重复或遗漏。解决方案是确保排序字段组合能唯一确定记录顺序通常可以加上主键字段SELECT * FROM product ORDER BY salePrice DESC, id ASC LIMIT 0, 103. 聚合函数的高级用法MySQL提供了丰富的聚合函数如COUNT、SUM、AVG、MAX、MIN等。这些函数看似简单但实际使用中有不少技巧。比如要统计每个分类下的商品数量和平均价格新手可能会写两个查询-- 查询商品数量 SELECT dir_id, COUNT(*) FROM product GROUP BY dir_id -- 查询平均价格 SELECT dir_id, AVG(salePrice) FROM product GROUP BY dir_id其实MySQL支持在一个查询中使用多个聚合函数SELECT dir_id, COUNT(*) as product_count, AVG(salePrice) as avg_price, MAX(salePrice) as max_price, MIN(salePrice) as min_price FROM product GROUP BY dir_idGROUP BY还支持HAVING子句来过滤分组结果。比如要找出平均价格超过500的商品分类SELECT dir_id, AVG(salePrice) as avg_price FROM product GROUP BY dir_id HAVING avg_price 500这里有个容易混淆的点WHERE是在分组前过滤记录HAVING是在分组后过滤结果集。我在项目中就见过因为混淆两者导致的bug。4. 多表关联查询实战实际业务中数据往往分散在多个表中。比如商品信息在product表分类信息在productdir表库存信息在productstock表。要查询商品名称、分类名称和库存量就需要多表关联。最基本的关联方式是使用WHERE子句SELECT p.productName, d.dirName, s.storeNum FROM product p, productdir d, productstock s WHERE p.dir_id d.id AND p.id s.product_id不过更推荐使用显式的JOIN语法可读性更好SELECT p.productName, d.dirName, s.storeNum FROM product p JOIN productdir d ON p.dir_id d.id JOIN productstock s ON p.id s.product_idJOIN有几种变体INNER JOIN只返回匹配的记录LEFT JOIN返回左表所有记录右表不匹配则为NULLRIGHT JOIN返回右表所有记录左表不匹配则为NULLFULL JOIN返回所有记录MySQL不支持我在处理报表时经常需要计算各种统计指标。比如要计算每个商品的利润零售价-成本价乘以库存量并按利润从高到低排序SELECT p.productName, p.salePrice, d.dirName, (p.salePrice - p.costPrice) * s.storeNum as profit FROM product p JOIN productdir d ON p.dir_id d.id JOIN productstock s ON p.id s.product_id ORDER BY profit DESC5. 条件查询的进阶技巧WHERE子句是过滤数据的主要手段。除了基本的、、等比较运算符MySQL还支持一些高级用法。LIKE操作符用于模糊匹配支持两个通配符%匹配任意数量字符_匹配单个字符比如查询名称包含M的商品SELECT * FROM product WHERE productName LIKE %M%IN操作符可以简化多个OR条件。比如查询价格为800、1600或3000的商品SELECT * FROM product WHERE salePrice IN (800, 1600, 3000)BETWEEN可以查询某个范围内的值SELECT * FROM product WHERE salePrice BETWEEN 1000 AND 2000NULL值的处理需要特别注意。不能直接用 NULL判断而要使用IS NULLSELECT * FROM product WHERE discount IS NULL我在项目中遇到过这样的需求查询库存量低于警戒线的商品。这需要组合多个条件SELECT p.productName, s.storeNum, s.warningNum FROM product p JOIN productstock s ON p.id s.product_id WHERE s.storeNum s.warningNum AND p.status 1 -- 只查询上架商品6. 子查询的妙用子查询是指嵌套在其他查询中的SELECT语句。合理使用子查询可以解决很多复杂问题。比如要查询价格高于平均价的商品SELECT * FROM product WHERE salePrice (SELECT AVG(salePrice) FROM product)子查询也可以用在FROM子句中作为临时表SELECT d.dirName, COUNT(p.id) as product_count FROM productdir d LEFT JOIN (SELECT * FROM product WHERE salePrice 1000) p ON d.id p.dir_id GROUP BY d.idEXISTS子查询用于检查子查询是否返回结果SELECT * FROM product p WHERE EXISTS ( SELECT 1 FROM productstock s WHERE s.product_id p.id AND s.storeNum 10 )这个查询会找出库存量小于10的商品。我在处理库存预警功能时就用了这种写法。7. 性能优化实战经验经过多年的MySQL使用我总结了一些性能优化的实战经验索引优化为经常用于查询条件和排序的字段创建索引。比如ALTER TABLE product ADD INDEX idx_salePrice (salePrice)**避免SELECT ***只查询需要的字段减少数据传输量。使用EXPLAIN分析在复杂查询前加上EXPLAIN查看执行计划EXPLAIN SELECT * FROM product WHERE salePrice 1000批量操作使用INSERT INTO ... VALUES (...),(...)代替多次单条插入。合理使用事务将多个操作放在一个事务中减少IO开销。定期优化表特别是对频繁更新的表OPTIMIZE TABLE product配置调优根据服务器配置调整MySQL参数如buffer_pool_size、innodb_log_file_size等。记得有一次我们有个分页查询特别慢EXPLAIN发现虽然salePrice有索引但因为用了OR条件导致索引失效。后来改写成UNION ALL解决了问题-- 优化前慢 SELECT * FROM product WHERE salePrice 1000 OR costPrice 500 LIMIT 0, 10 -- 优化后 SELECT * FROM product WHERE salePrice 1000 UNION ALL SELECT * FROM product WHERE costPrice 500 LIMIT 0, 10