MySQL价格区间查询：技巧、优化与实战指南

在电商、金融或数据分析场景中，价格区间查询是MySQL数据库的高频操作。无论是筛选“100-500元商品”还是分析“月收入5000-10000元用户”，正确的查询方式直接影响性能与结果准确性。本文将从基础语法、性能优化、索引策略及实战案例四个维度，系统解析MySQL价格区间查询的实现方法。

一、基础语法：BETWEEN与比较运算符

1. BETWEEN的简洁性

BETWEEN是区间查询的直观选择，其语法如下：

SELECT * FROM products 
WHERE price BETWEEN 100 AND 500;

此查询等价于price >= 100 AND price <= 500，但代码更简洁。需注意：

• 边界包含性：BETWEEN包含两端值（100和500均包含）。
• 数据类型匹配：若price为DECIMAL(10,2)，需确保比较值类型一致，避免隐式转换导致的精度问题。

2. 比较运算符的灵活性

当需要排除边界或组合复杂条件时，比较运算符更灵活：

-- 查询价格大于100且小于500的商品（不包含边界）
SELECT * FROM products 
WHERE price > 100 AND price < 500;
-- 组合其他条件（如库存>0）
SELECT * FROM products 
WHERE price BETWEEN 100 AND 500 
  AND stock > 0;

二、性能优化：索引与查询重写

1. 索引的核心作用

价格字段未加索引时，区间查询需全表扫描，时间复杂度为O(n)。通过索引可降至O(log n)：

-- 创建单列索引
ALTER TABLE products ADD INDEX idx_price (price);
-- 复合索引（若常同时查询价格和类别）
ALTER TABLE products ADD INDEX idx_category_price (category_id, price);

优化建议：

• 索引选择性：高选择性字段（如价格）单独建索引效果更好。
• 避免索引失效：对索引列使用函数（如ROUND(price,0)）会导致索引失效。

2. 查询重写策略

场景1：宽区间查询（如1-10000）

若数据分布均匀，直接使用BETWEEN即可。但若数据倾斜（如90%商品价格<200），可拆分查询：

-- 先查低价区间（利用索引）
SELECT * FROM products WHERE price BETWEEN 1 AND 200;
-- 再查高价区间（若需）
SELECT * FROM products WHERE price BETWEEN 201 AND 10000;

场景2：动态区间参数

当区间边界由变量传入时，需防止SQL注入：

// PHP示例：使用预处理语句
$min = 100;
$max = 500;
$stmt = $pdo->prepare("SELECT * FROM products WHERE price BETWEEN ? AND ?");
$stmt->execute([$min, $max]);

三、高级技巧：覆盖索引与分页优化

1. 覆盖索引加速查询

若只需价格和商品ID，可创建仅包含这些字段的索引：

ALTER TABLE products ADD INDEX idx_price_cover (price, id);
-- 查询时仅访问索引（无需回表）
SELECT id FROM products 
WHERE price BETWEEN 100 AND 500;

性能对比：覆盖索引查询速度比普通索引快30%-50%（取决于表大小）。

2. 分页查询优化

大表区间查询常需分页，传统LIMIT offset, size在offset大时性能差。改用“延迟关联”优化：

-- 传统方式（offset=10000时慢）
SELECT * FROM products 
WHERE price BETWEEN 100 AND 500 
ORDER BY price 
LIMIT 10000, 20;
-- 优化方式：先通过索引定位ID，再关联查询
SELECT p.* FROM products p
JOIN (
    SELECT id FROM products 
    WHERE price BETWEEN 100 AND 500 
    ORDER BY price 
    LIMIT 10000, 20
) AS tmp ON p.id = tmp.id;

四、实战案例：电商价格筛选系统

案例背景

某电商需实现“价格区间+类别+品牌”的多条件筛选，数据量1000万条。

解决方案

1. 表结构优化：

   CREATE TABLE products (
       id INT AUTO_INCREMENT PRIMARY KEY,
       category_id INT NOT NULL,
       brand_id INT NOT NULL,
       price DECIMAL(10,2) NOT NULL,
       -- 其他字段...
       INDEX idx_category_brand_price (category_id, brand_id, price)
   );

2. 查询语句：

   SELECT id, name, price 
   FROM products 
   WHERE category_id = 5 
     AND brand_id IN (10, 20) 
     AND price BETWEEN 200 AND 800 
   ORDER BY price 
   LIMIT 0, 20;

3. 性能监控：

   • 使用EXPLAIN分析执行计划，确认索引使用情况。
   • 监控慢查询日志，定位未优化语句。

五、常见误区与解决方案

误区1：索引滥用

问题：为所有查询字段建索引，导致写入性能下降。
解决：遵循“最左前缀原则”，仅对高频查询条件建复合索引。

误区2：忽略数据分布

问题：对均匀分布数据优化的查询，在倾斜数据中失效。
解决：使用ANALYZE TABLE更新统计信息，或手动拆分查询。

误区3：浮点数比较

问题：DECIMAL与FLOAT类型混用导致精度错误。
解决：统一使用DECIMAL(10,2)存储价格，避免浮点数比较。

六、总结与建议

1. 基础选择：优先使用BETWEEN，需排除边界时用比较运算符。
2. 索引策略：单列索引适用于简单查询，复合索引优化多条件筛选。
3. 性能监控：定期用EXPLAIN和慢查询日志分析瓶颈。
4. 扩展场景：对于超大数据集，考虑分库分表或使用专用分析引擎。

通过合理设计索引、优化查询语句及监控性能，MySQL价格区间查询可轻松支撑百万级数据的高效筛选。实际开发中，建议结合业务特点进行压力测试，持续调优查询方案。