MySQL价格区间查询：高效筛选与优化实践指南

在电商、金融、数据分析等场景中，价格区间查询是高频需求。如何通过MySQL高效实现价格筛选，同时兼顾查询性能与数据准确性，是开发者必须掌握的核心技能。本文将从基础语法到性能优化，系统讲解MySQL价格区间查询的实现方法。

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

1.1 BETWEEN运算符

BETWEEN是MySQL中最常用的区间查询语法，其基本形式为：

SELECT * FROM products 
WHERE price BETWEEN 100 AND 500;

该查询等价于：

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

特点：

• 语法简洁，可读性强
• 包含边界值（100和500均包含）
• 适用于闭区间查询

适用场景：

• 明确知道上下界的标准查询
• 需要包含边界值的业务逻辑

1.2 比较运算符组合

对于开区间或半开区间查询，可使用比较运算符组合：

-- 左开右闭区间 (100, 500]
SELECT * FROM products 
WHERE price > 100 AND price <= 500;
-- 开区间 (100, 500)
SELECT * FROM products 
WHERE price > 100 AND price < 500;

优势：

• 精确控制边界包含关系
• 灵活处理各种区间类型

二、索引优化策略

2.1 单列索引优化

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

ALTER TABLE products ADD INDEX idx_price (price);

优化原理：

• 索引使区间查询变为有序数据扫描
• 减少全表扫描的数据量

性能对比：
| 查询方式 | 扫描行数 | 执行时间 |
|————————|—————|—————|
| 无索引查询 | 全表 | 0.45s |
| 有索引查询 | 区间内 | 0.02s |

2.2 复合索引设计

当查询条件包含多个字段时，需设计复合索引：

-- 创建(category, price)复合索引
ALTER TABLE products ADD INDEX idx_cat_price (category, price);
-- 高效查询方式
SELECT * FROM products 
WHERE category = 'electronics' 
AND price BETWEEN 100 AND 500;

索引选择原则：

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

2.3 索引失效场景

以下情况会导致索引失效：

-- 对索引列使用函数
SELECT * FROM products 
WHERE ROUND(price) BETWEEN 100 AND 500;
-- 隐式类型转换
SELECT * FROM products 
WHERE price BETWEEN '100' AND '500';  -- 字符串与数字比较
-- OR条件导致索引合并
SELECT * FROM products 
WHERE price < 100 OR price > 500;

优化建议：

• 避免在索引列上使用函数
• 确保比较值类型与字段类型一致
• 复杂条件考虑拆分为多个查询

三、高级查询技术

3.1 分页查询优化

大数据量下，分页查询需结合索引：

-- 传统分页（性能差）
SELECT * FROM products 
WHERE price BETWEEN 100 AND 500 
LIMIT 10000, 20;
-- 优化方案（使用索引覆盖）
SELECT * 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;

优化效果：

• 传统方式需扫描10020行
• 优化后仅扫描区间内10020行

3.2 动态价格区间查询

业务中常需根据参数动态构建查询：

-- 存储过程实现
DELIMITER //
CREATE PROCEDURE query_by_price_range(
    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 ;
-- 调用示例
CALL query_by_price_range(100, 500);

安全建议：

• 使用参数化查询防止SQL注入
• 对输入参数进行有效性验证

3.3 多表关联查询

涉及多表的价格区间查询需注意连接顺序：

-- 优化前（性能差）
SELECT p.* FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.type = 'electronics'
AND p.price BETWEEN 100 AND 500;
-- 优化后（先过滤再连接）
SELECT p.* FROM 
(SELECT * FROM products WHERE price BETWEEN 100 AND 500) p
JOIN categories c ON p.category_id = c.id
WHERE c.type = 'electronics';

优化原理：

• 减少中间结果集大小
• 尽早应用高选择性条件

四、性能监控与调优

4.1 EXPLAIN分析

使用EXPLAIN查看查询执行计划：

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

关键指标解读：

• type列：应达到range级别
• key列：应显示使用的索引
• rows列：预估扫描行数应合理

4.2 慢查询日志

配置慢查询日志定位性能问题：

# my.cnf配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

分析工具：

• mysqldumpslow：汇总慢查询
• pt-query-digest：专业分析工具

4.3 查询重写建议

常见需要重写的查询模式：

-- 不推荐（多次查询）
SELECT COUNT(*) FROM products WHERE price < 100;
SELECT COUNT(*) FROM products WHERE price BETWEEN 100 AND 500;
SELECT COUNT(*) FROM products WHERE price > 500;
-- 推荐（单次查询）
SELECT 
    SUM(price < 100) AS low_count,
    SUM(price BETWEEN 100 AND 500) AS mid_count,
    SUM(price > 500) AS high_count
FROM products;

五、实际应用案例

5.1 电商价格筛选实现

某电商平台需求：

• 按分类筛选商品
• 支持多价格区间并选
• 分页显示结果

解决方案：

-- 创建复合索引
ALTER TABLE products ADD INDEX idx_cat_price_stock (category_id, price, stock);
-- 动态SQL实现
DELIMITER //
CREATE PROCEDURE filter_products(
    IN cat_ids VARCHAR(255),
    IN price_ranges TEXT,
    IN page INT,
    IN per_page INT
)
BEGIN
    -- 解析价格区间（示例简化）
    SET @min_price = SUBSTRING_INDEX(SUBSTRING_INDEX(price_ranges, ',', 1), '-', 1);
    SET @max_price = SUBSTRING_INDEX(SUBSTRING_INDEX(price_ranges, ',', 1), '-', -1);
    -- 构建动态SQL
    SET @sql = CONCAT('
        SELECT p.* FROM products p
        WHERE p.category_id IN (', cat_ids, ')
        AND p.price BETWEEN ', @min_price, ' AND ', @max_price, '
        AND p.stock > 0
        ORDER BY p.price
        LIMIT ', (page-1)*per_page, ', ', per_page);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

5.2 金融风控系统实现

某金融系统需求：

• 查询交易金额在特定区间的记录
• 需要关联用户信息
• 实时性要求高

优化方案：

-- 创建覆盖索引
ALTER TABLE transactions ADD INDEX idx_user_amount_time (
    user_id, 
    amount, 
    transaction_time
);
-- 高效查询
SELECT t.*, u.name, u.risk_level
FROM transactions t
JOIN users u ON t.user_id = u.id
WHERE t.amount BETWEEN 10000 AND 50000
AND t.transaction_time > '2023-01-01'
ORDER BY t.transaction_time DESC
LIMIT 100;

六、最佳实践总结

1. 索引设计原则：

   • 为价格字段创建单列索引
   • 复合查询时创建复合索引
   • 遵循最左前缀原则

2. 查询编写规范：

   • 优先使用BETWEEN进行闭区间查询
   • 明确边界包含关系时使用比较运算符
   • 避免在索引列上使用函数

3. 性能优化技巧：

   • 大数据量分页使用子查询优化
   • 动态SQL使用参数化查询
   • 定期分析慢查询日志

4. 监控与维护：

   • 使用EXPLAIN分析执行计划
   • 配置合理的慢查询阈值
   • 定期更新统计信息（ANALYZE TABLE）

通过系统掌握这些技术和优化方法，开发者能够高效实现MySQL价格区间查询，满足各种业务场景的需求。实际开发中，应根据具体数据特点、查询模式和性能要求，选择最适合的实现方案。