MySQL价格区间查询全解析：从基础到进阶的实战指南

在电商、金融、零售等涉及商品定价的系统中，价格区间查询是高频需求。如何高效实现”查询价格在100-500元之间的商品”这类操作，不仅影响用户体验，更直接关系到系统性能。本文将从基础语法到高级优化，系统讲解MySQL中价格区间查询的实现方法。

一、基础区间查询语法

1.1 BETWEEN运算符

最基础的区间查询方式是使用BETWEEN...AND语法：

SELECT * FROM products 
WHERE price BETWEEN 100 AND 500;

这种写法等价于：

SELECT * FROM products 
WHERE price >= 100 AND price <= 500;

优势：语法简洁直观，适合简单区间查询
注意：BETWEEN是包含边界值的闭区间查询

1.2 多区间组合查询

当需要查询多个不连续区间时，可使用OR组合：

SELECT * FROM products 
WHERE (price BETWEEN 100 AND 200) 
   OR (price BETWEEN 300 AND 400);

或使用UNION优化：

(SELECT * FROM products WHERE price BETWEEN 100 AND 200)
UNION
(SELECT * FROM products WHERE price BETWEEN 300 AND 400);

性能提示：对于大数据表，UNION ALL比UNION更高效（避免去重操作）

二、索引优化策略

2.1 单列索引优化

为价格字段创建索引是最基础的优化手段：

ALTER TABLE products ADD INDEX idx_price (price);

执行计划分析：使用EXPLAIN验证索引使用情况

EXPLAIN SELECT * FROM products WHERE price BETWEEN 100 AND 500;

理想情况下应显示type: range和key: idx_price

2.2 复合索引优化

当查询包含其他条件时，需设计复合索引：

ALTER TABLE products ADD INDEX idx_category_price (category_id, price);

索引选择原则：

• 遵循最左前缀原则
• 将高选择性列放在左侧
• 区间查询字段应放在索引右侧

2.3 索引失效场景

警惕以下导致索引失效的情况：

1. 对索引列使用函数：

   -- 错误示例：索引失效
   SELECT * FROM products WHERE ROUND(price) BETWEEN 100 AND 500;

2. 隐式类型转换：

   -- 错误示例：price为VARCHAR时索引失效
   SELECT * FROM products WHERE price BETWEEN 100 AND 500;

3. 使用NOT、!=、<>等否定操作符

三、高级查询技巧

3.1 动态价格区间查询

在存储过程中实现动态区间查询：

DELIMITER //
CREATE PROCEDURE get_products_by_price_range(
    IN min_price DECIMAL(10,2),
    IN max_price DECIMAL(10,2)
)
BEGIN
    SELECT * FROM products 
    WHERE price BETWEEN min_price AND max_price
    ORDER BY price;
END //
DELIMITER ;

3.2 分页区间查询

结合LIMIT实现分页：

SELECT * FROM products 
WHERE price BETWEEN 100 AND 500
ORDER BY price
LIMIT 20 OFFSET 0;  -- 第一页

性能优化：对于大数据表，建议使用”延迟关联”技术：

SELECT p.* FROM products p
JOIN (
    SELECT id FROM products 
    WHERE price BETWEEN 100 AND 500
    ORDER BY price
    LIMIT 20 OFFSET 0
) AS tmp USING(id);

3.3 历史价格区间分析

查询价格变动历史中的区间：

SELECT product_id, price, change_date
FROM price_history
WHERE change_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND price BETWEEN 50 AND 200
ORDER BY product_id, change_date;

四、性能调优实践

4.1 统计信息优化

确保统计信息准确：

ANALYZE TABLE products;

监控指标：

• 使用SHOW INDEX FROM products查看索引基数
• 通过SHOW STATUS LIKE 'Handler_read%'监控索引使用情况

4.2 直方图统计（MySQL 8.0+）

对于非均匀分布的价格数据，创建直方图：

ANALYZE TABLE products UPDATE HISTOGRAM ON price;

查询优化器：MySQL 8.0+会利用直方图信息优化区间查询

4.3 分区表优化

对于超大规模数据，考虑按价格范围分区：

CREATE TABLE products (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    price DECIMAL(10,2),
    PRIMARY KEY (id, price)
) PARTITION BY RANGE (price) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (500),
    PARTITION p2 VALUES LESS THAN (1000),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

查询优势：当查询区间完全落在某个分区时，可避免全表扫描

五、常见问题解决方案

5.1 浮点数比较问题

处理DECIMAL与FLOAT的精度差异：

-- 错误示例：浮点数比较不精确
SELECT * FROM products WHERE price BETWEEN 99.99 AND 100.01;
-- 正确做法：使用DECIMAL类型或范围扩大
SELECT * FROM products 
WHERE price >= 99.99 - 0.005 
  AND price <= 100.01 + 0.005;

5.2 NULL值处理

明确处理价格可能为NULL的情况：

SELECT * FROM products 
WHERE (price BETWEEN 100 AND 500 OR price IS NULL)
  AND status = 'active';

5.3 跨时区价格查询

处理不同时区的价格有效性：

SELECT p.* FROM products p
JOIN price_rules r ON p.id = r.product_id
WHERE r.effective_time <= UTC_TIMESTAMP()
  AND r.expiry_time >= UTC_TIMESTAMP()
  AND p.price BETWEEN r.min_price AND r.max_price;

六、最佳实践总结

1. 索引设计：为价格字段创建单独索引，复合查询时考虑复合索引
2. 查询写法：优先使用BETWEEN，复杂条件使用括号明确优先级
3. 数据类型：使用DECIMAL而非FLOAT存储价格
4. 性能监控：定期分析执行计划，更新统计信息
5. 扩展考虑：大数据量时评估分区表方案

通过系统应用这些技术，开发者可以构建出既准确又高效的价格区间查询系统，满足从简单商品筛选到复杂数据分析的各种业务需求。