面试题：MySQL WHERE子句中范围值与空值检查的性能优化及逻辑处理

Question

现有`products`表，包含`product_name`（产品名称）、`price`（价格）、`stock_status`（库存状态，可能为空）字段。假设表数据量非常大，需要查询价格在50到100之间，库存状态不为空且库存状态符合特定业务规则（例如库存状态为‘in_stock’或‘low_stock’）的产品信息，如何编写SQL语句能在保证结果正确的同时尽量优化查询性能？请阐述优化思路并给出SQL示例。

Answer 1

优化思路

1. 索引优化：对price和stock_status字段建立复合索引，这样在查询时可以快速定位到符合条件的记录。因为表数据量非常大，索引能大大减少数据扫描范围。
2. 避免函数操作：在WHERE子句中尽量避免对字段进行函数操作，以免索引失效。

SQL示例

假设使用MySQL数据库，示例如下：

-- 先创建复合索引
CREATE INDEX idx_price_stock_status ON products (price, stock_status);

-- 查询语句
SELECT product_name, price, stock_status
FROM products
WHERE price BETWEEN 50 AND 100
  AND stock_status IS NOT NULL
  AND stock_status IN ('in_stock', 'low_stock');

在其他数据库（如Oracle）中，索引创建和查询语句基本类似，但语法细节上可能略有不同。例如Oracle创建索引语法为：

-- 创建复合索引
CREATE INDEX idx_price_stock_status ON products (price, stock_status);

-- 查询语句
SELECT product_name, price, stock_status
FROM products
WHERE price BETWEEN 50 AND 100
  AND stock_status IS NOT NULL
  AND stock_status IN ('in_stock', 'low_stock');