一、价格查询的数据库设计基础

在MySQL中实现价格查询功能，首要任务是设计合理的表结构。以电商场景为例，商品价格通常存储在products表中，其核心字段包括：

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL COMMENT '商品价格，保留两位小数',
    category_id INT NOT NULL,
    stock INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

关键设计要点：

1. 数据类型选择：使用DECIMAL(10,2)而非FLOAT，避免浮点数精度问题导致的价格计算错误。例如，0.1+0.2在FLOAT类型下可能得到0.30000000000000004，而DECIMAL能精确存储0.30。
2. 索引策略：为高频查询字段建立索引。若按分类查询价格，应在category_id上创建索引：

   CREATE INDEX idx_category ON products(category_id);

3. 历史价格追踪：对于需要价格走势分析的场景，建议建立价格历史表：

   CREATE TABLE price_history (
       id INT AUTO_INCREMENT PRIMARY KEY,
       product_id INT NOT NULL,
       price DECIMAL(10,2) NOT NULL,
       effective_date DATE NOT NULL,
       FOREIGN KEY (product_id) REFERENCES products(id)
   );

二、基础价格查询实现

1. 单条件精确查询

最基础的价格查询场景，例如查询ID为100的商品价格：

SELECT price FROM products WHERE id = 100;

优化建议：

• 使用EXPLAIN分析查询执行计划，确认是否使用了主键索引
• 对于高频查询，可考虑在应用层缓存结果（如Redis）

2. 多条件组合查询

实际业务中常需组合多个条件查询价格，例如查询某分类下价格在100-500元的商品：

SELECT id, name, price 
FROM products 
WHERE category_id = 5 
  AND price BETWEEN 100 AND 500
ORDER BY price ASC;

性能考量：

• 复合索引设计：若该查询频繁，可建立(category_id, price)复合索引
• 分页处理：大数据量时使用LIMIT offset, size实现分页

3. 动态价格查询

对于需要用户输入价格区间的场景，建议使用预处理语句防止SQL注入：

// Java示例
String sql = "SELECT * FROM products WHERE price BETWEEN ? AND ?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setBigDecimal(1, minPrice);
stmt.setBigDecimal(2, maxPrice);
ResultSet rs = stmt.executeQuery();

三、高级查询技术

1. 索引优化策略

价格排序优化：当查询需要按价格排序时，索引选择至关重要。对比两种索引方案：

• 方案A：单列索引(price)
• 方案B：复合索引(category_id, price)

测试表明，在WHERE category_id=5 ORDER BY price场景下，方案B的查询速度比方案A快3-5倍，因为避免了排序操作。

索引维护建议：

• 定期使用ANALYZE TABLE products更新统计信息
• 监控慢查询日志，识别未使用索引的查询

2. 事务处理中的价格查询

在涉及价格修改的交易场景中，必须使用事务保证数据一致性：

START TRANSACTION;
-- 查询当前价格
SELECT price INTO @current_price FROM products WHERE id = 100 FOR UPDATE;
-- 验证库存并计算新价格
SET @new_price = @current_price * 0.9; -- 9折优惠
-- 更新价格
UPDATE products SET price = @new_price WHERE id = 100;
COMMIT;

关键点：

• 使用FOR UPDATE锁定记录，防止并发修改
• 事务应尽可能短小，减少锁持有时间

3. 存储过程实现复杂查询

对于需要频繁执行的复杂价格计算逻辑，建议封装为存储过程：

DELIMITER //
CREATE PROCEDURE get_discounted_prices(
    IN cat_id INT,
    IN min_p DECIMAL(10,2),
    IN max_p DECIMAL(10,2),
    IN discount DECIMAL(3,2)
)
BEGIN
    SELECT id, name, price, (price * (1 - discount)) AS discounted_price
    FROM products
    WHERE category_id = cat_id
      AND price BETWEEN min_p AND max_p
    ORDER BY price DESC;
END //
DELIMITER ;
-- 调用示例
CALL get_discounted_prices(5, 100, 500, 0.15);

优势：

• 减少网络传输，提高性能
• 业务逻辑集中管理，便于维护

四、实际应用中的最佳实践

1. 价格缓存策略

对于热点商品价格，建议实施多级缓存：

1. MySQL查询缓存（8.0已移除，但可替代方案丰富）
2. Redis缓存：设置10分钟过期时间

   SET productprice 299.99 EX 600

3. 本地缓存：应用服务器内存缓存

2. 价格变更监控

建立价格变更触发器，记录价格变动历史：

DELIMITER //
CREATE TRIGGER price_change_log
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.price != OLD.price THEN
        INSERT INTO price_history(product_id, price, effective_date)
        VALUES (NEW.id, NEW.price, CURDATE());
    END IF;
END //
DELIMITER ;

3. 查询性能监控

定期执行以下监控脚本，识别性能瓶颈：

-- 识别未使用索引的查询
SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'your_db'
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
-- 检查索引使用情况
SELECT * FROM sys.schema_unused_indexes;

五、常见问题解决方案

1. 价格精度问题

问题表现：价格计算出现微小误差（如0.30存储为0.299999…）

解决方案：

• 统一使用DECIMAL类型
• 计算时使用ROUND()函数：

  SELECT ROUND(price * 1.1, 2) AS increased_price FROM products;

2. 并发修改冲突

问题表现：高并发场景下出现价格更新丢失

解决方案：

• 使用SELECT ... FOR UPDATE锁定记录
• 考虑乐观锁机制，添加版本号字段：

  ALTER TABLE products ADD COLUMN version INT DEFAULT 0;
  UPDATE products SET price = 199.99, version = version + 1 
  WHERE id = 100 AND version = 5;

3. 大数据量查询缓慢

问题表现：百万级数据量时查询超时

解决方案：

• 实施分区表策略，按价格范围分区
• 使用读写分离架构，将查询负载分散到从库
• 考虑引入Elasticsearch等搜索引擎处理复杂查询

六、总结与展望

MySQL价格查询系统的优化是一个持续过程，需要从表设计、索引策略、查询写法到架构设计多层面考虑。实际开发中，建议：

1. 建立完善的监控体系，持续跟踪查询性能
2. 定期进行索引优化和统计信息更新
3. 根据业务发展及时调整数据库架构

随着MySQL 8.0的普及，窗口函数、通用表表达式等新特性为复杂价格分析提供了更强支持。开发者应保持对新技术的学习，持续提升价格查询系统的性能和可靠性。