面试题：MySQL复杂条件下AND与OR优先级及优化

Question

有一个`orders`表，字段包括`order_id`、`customer_id`、`order_date`、`total_amount`、`status`。需求是查询在2023年1月1日之后下单，并且订单状态为'completed'或者订单总金额大于1000的订单信息，同时要求查询效率尽可能高。请写出SQL语句，并分析在这种复杂条件下AND与OR操作符的优先级如何影响查询计划，以及你是如何优化查询的。

Answer 1

SQL语句

SELECT order_id, customer_id, order_date, total_amount, status
FROM orders
WHERE order_date > '2023-01-01'
  AND (status = 'completed' OR total_amount > 1000);

AND与OR操作符优先级对查询计划的影响

• 在SQL中，AND操作符的优先级高于OR操作符。这意味着数据库会先处理AND连接的条件，再处理OR连接的条件。如果不使用括号明确分组，查询逻辑可能与预期不符。例如，如果写成WHERE order_date > '2023-01-01' AND status = 'completed' OR total_amount > 1000，数据库会先计算order_date > '2023-01-01' AND status = 'completed'，然后再与total_amount > 1000进行OR运算，这可能导致查询结果不符合需求。

查询优化

1. 索引优化：
   • 对order_date字段创建索引，可以加速对order_date > '2023-01-01'条件的过滤。例如，使用CREATE INDEX idx_order_date ON orders (order_date);
   • 对status和total_amount字段也可以考虑创建复合索引，如CREATE INDEX idx_status_amount ON orders (status, total_amount);，这样可以帮助快速筛选出满足status = 'completed' OR total_amount > 1000条件的数据。
2. 查询执行计划分析：
   • 使用数据库提供的查询执行计划分析工具（如MySQL的EXPLAIN关键字），查看查询的执行计划，确认索引是否被正确使用。例如，EXPLAIN SELECT order_id, customer_id, order_date, total_amount, status FROM orders WHERE order_date > '2023-01-01' AND (status = 'completed' OR total_amount > 1000);，根据执行计划中的信息，如key字段是否显示了创建的索引，来判断索引是否生效，并进一步优化查询。