MySQL价格区间查询：精准筛选与高效优化策略

摘要

在电商、金融等业务场景中，价格区间查询是高频需求。本文系统梳理MySQL中实现价格区间查询的多种方法，从基础BETWEEN语法到索引优化策略，结合实际案例分析性能瓶颈，并提供分页处理、动态参数绑定等实用技巧，帮助开发者构建高效稳定的价格筛选功能。

一、价格区间查询基础语法

1.1 BETWEEN运算符

BETWEEN是MySQL中最直观的价格区间查询方式，其语法结构为：

SELECT * FROM products 
WHERE price BETWEEN 100 AND 500;

该查询等价于：

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

性能特点：BETWEEN在语义上更清晰，但执行计划与>=…<=组合完全相同，选择依据应基于代码可读性。

1.2 边界值处理要点

• 包含边界：BETWEEN包含上下限值，与数学区间[100,500]一致
• 浮点数处理：对decimal(10,2)类型字段，建议使用整数单位（分）存储避免浮点误差
• NULL值处理：BETWEEN不匹配NULL值，需单独处理：

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

二、索引优化策略

2.1 单列索引设计

为price字段创建索引是基础优化：

ALTER TABLE products ADD INDEX idx_price (price);

执行计划验证：

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

应确认type列为range，key列为idx_price。

2.2 复合索引应用

当查询包含排序时，复合索引效果更佳：

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

对于查询：

SELECT * FROM products 
WHERE price BETWEEN 100 AND 500 
ORDER BY category_id;

复合索引可避免filesort操作。

2.3 索引选择性分析

使用索引统计工具评估：

SELECT 
  COUNT(DISTINCT price) AS distinct_prices,
  COUNT(*) AS total_rows,
  COUNT(DISTINCT price)/COUNT(*) AS selectivity
FROM products;

选择性（selectivity）>30%的字段适合建索引。

三、高级查询技巧

3.1 动态参数绑定

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

DELIMITER //
CREATE PROCEDURE get_products_by_price(
  IN min_price DECIMAL(10,2),
  IN max_price DECIMAL(10,2)
)
BEGIN
  SET @sql = CONCAT('SELECT * FROM products WHERE price BETWEEN ', min_price, ' AND ', max_price);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

3.2 分页处理方案

结合LIMIT实现高效分页：

-- 第一页
SELECT * FROM products 
WHERE price BETWEEN 100 AND 500 
ORDER BY price 
LIMIT 20 OFFSET 0;
-- 后续页（使用游标分页）
SELECT * FROM products 
WHERE price BETWEEN 100 AND 500 
  AND (price > 200 OR (price = 200 AND id > 100)) 
ORDER BY price 
LIMIT 20;

3.3 多表关联查询

在订单系统中查询特定价格区间的商品：

SELECT p.* FROM products p
JOIN order_items oi ON p.id = oi.product_id
WHERE oi.unit_price BETWEEN 50 AND 200
GROUP BY p.id
HAVING COUNT(DISTINCT oi.order_id) > 3;

四、性能优化实践

4.1 查询重写优化

将OR条件转换为UNION ALL：

-- 原查询（效率低）
SELECT * FROM products 
WHERE price < 50 OR price > 500;
-- 优化后
SELECT * FROM products WHERE price < 50
UNION ALL
SELECT * FROM products WHERE price > 500 AND price NOT BETWEEN 0 AND 50;

4.2 直方图统计优化

MySQL 8.0+支持直方图统计：

-- 创建直方图
ANALYZE TABLE products UPDATE HISTOGRAM ON price WITH 10 BUCKETS;
-- 查看直方图信息
SELECT * FROM sys.schema_histogram_stats 
WHERE object_schema = 'your_db' AND object_name = 'products' AND column_name = 'price';

4.3 查询缓存策略

对频繁查询的固定区间：

-- 启用查询缓存（MySQL 8.0已移除，此处为示例）
SET GLOBAL query_cache_size = 1048576;
SET SESSION query_cache_type = ON;
-- 使用SQL_CACHE提示
SELECT SQL_CACHE * FROM products 
WHERE price BETWEEN 100 AND 500;

五、常见问题解决方案

5.1 区间查询返回空集

排查步骤：

1. 确认字段类型与查询值类型一致
2. 检查数据是否存在：

   SELECT MIN(price), MAX(price) FROM products;

3. 验证索引使用情况：

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

5.2 大区间查询性能下降

优化方案：

• 对大数据表使用分区表：

  CREATE TABLE products (
  id INT,
  price DECIMAL(10,2),
  ...
  ) PARTITION BY RANGE (price) (
  PARTITION p0 VALUES LESS THAN (100),
  PARTITION p1 VALUES LESS THAN (500),
  PARTITION p2 VALUES LESS THAN MAXVALUE
  );

• 考虑使用NoSQL方案处理超大规模数据

5.3 动态价格区间统计

使用窗口函数实现区间统计：

SELECT 
  FLOOR(price/100)*100 AS price_range_start,
  FLOOR(price/100)*100 + 99.99 AS price_range_end,
  COUNT(*) AS product_count,
  AVG(price) AS avg_price
FROM products
GROUP BY FLOOR(price/100)
ORDER BY price_range_start;

六、最佳实践总结

1. 索引优先：为price字段创建适当索引
2. 参数校验：前端输入需验证价格区间合理性（如min≤max）
3. 分页策略：大数据量时采用游标分页
4. 定期维护：执行ANALYZE TABLE更新统计信息
5. 监控告警：设置慢查询日志监控（long_query_time=1s）

通过系统应用上述方法，可显著提升MySQL价格区间查询的性能和稳定性。实际开发中，建议结合具体业务场景进行压力测试，持续优化查询方案。