面试题：MySQL NOT操作符在性能优化及复杂业务规则下的数据排除策略

Question

在一个电商数据库中，有`products`表（`product_id`、`product_name`、`category`、`price`、`stock`），`orders`表（`order_id`、`customer_id`、`order_date`），`order_items`表（`order_item_id`、`order_id`、`product_id`、`quantity`）。业务规则要求查询出从未被订购过且库存小于10并且价格大于50的产品信息，写出高效的SQL查询语句，并解释在大数据量下如何优化此查询以提升性能。

Answer 1

SQL查询语句

SELECT p.product_id, p.product_name, p.category, p.price, p.stock
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL
  AND p.stock < 10
  AND p.price > 50;

大数据量下的性能优化

1. 索引优化：
   • 在products表的product_id、stock和price列上创建索引。例如：

CREATE INDEX idx_products_product_id ON products(product_id);
CREATE INDEX idx_products_stock ON products(stock);
CREATE INDEX idx_products_price ON products(price);

- 在`order_items`表的`product_id`列上创建索引：

CREATE INDEX idx_order_items_product_id ON order_items(product_id);

2. 查询执行计划分析：使用数据库的查询分析工具（如MySQL的EXPLAIN关键字）来分析查询的执行计划，确保数据库使用了合适的索引进行查询。例如：

EXPLAIN SELECT p.product_id, p.product_name, p.category, p.price, p.stock
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL
  AND p.stock < 10
  AND p.price > 50;

3. 分区表：如果数据量极大，可以考虑对products表按category等维度进行分区，这样在查询时可以缩小扫描范围，提高查询效率。例如，在MySQL中可以使用RANGE分区：

CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(255),
    category VARCHAR(255),
    price DECIMAL(10,2),
    stock INT
)
PARTITION BY RANGE (stock) (
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20),
    PARTITION p2 VALUES LESS THAN (MAXVALUE)
);

4. 避免全表扫描：确保查询条件能够利用索引，避免使用会导致全表扫描的函数或操作，例如在WHERE子句中对列使用函数操作。
5. 适当使用临时表：如果中间结果集较大，可以考虑使用临时表存储中间结果，减少复杂查询中的数据重复扫描。例如：

CREATE TEMPORARY TABLE temp_products AS
SELECT p.product_id, p.product_name, p.category, p.price, p.stock
FROM products p
WHERE p.stock < 10
  AND p.price > 50;

SELECT tp.product_id, tp.product_name, tp.category, tp.price, tp.stock
FROM temp_products tp
LEFT JOIN order_items oi ON tp.product_id = oi.product_id
WHERE oi.order_item_id IS NULL;