面试题：MySQL复杂查询场景下减少额外记录扫描的优化

SQL语句优化：
- 合理使用JOIN：
```
SELECT o.order_id, o.order_date, o.total_amount, p.product_name, oi.quantity, oi.unit_price
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN '开始时间' AND '结束时间'
  AND p.category = '特定分类'
  AND o.total_amount > '一定数值';
```
- 避免函数操作：在WHERE子句中避免对列进行函数操作，因为这会导致索引失效。例如，如果order_date是日期类型，不要使用DATE_FORMAT(o.order_date, '%Y-%m-%d') BETWEEN '开始日期' AND '结束日期'，而应直接使用o.order_date BETWEEN '开始时间' AND '结束时间'。
- 使用覆盖索引：如果查询中涉及的列都包含在索引中，数据库可以直接从索引中获取数据，而不需要回表操作，从而提高查询性能。例如，如果经常查询订单金额和订单日期，可以创建一个包含这两列的复合索引。
索引优化：
- 单列索引：
  - 在orders表的order_date列上创建索引，用于快速筛选出特定时间段的订单：CREATE INDEX idx_order_date ON orders (order_date);
  - 在orders表的total_amount列上创建索引，用于快速筛选出订单金额大于一定数值的订单：CREATE INDEX idx_total_amount ON orders (total_amount);
  - 在products表的category列上创建索引，用于快速筛选出特定分类的商品：CREATE INDEX idx_category ON products (category);
- 复合索引：
  - 如果查询中经常同时使用order_date和total_amount进行筛选，可以在orders表上创建复合索引：CREATE INDEX idx_order_date_amount ON orders (order_date, total_amount);
  - 对于order_items表和products表的连接，可以在order_items表的product_id列创建索引，在products表的product_id列创建索引，这样可以加速连接操作。例如，在order_items表：CREATE INDEX idx_product_id_oi ON order_items (product_id);，在products表：CREATE INDEX idx_product_id_p ON products (product_id); 。
- 注意索引顺序：在复合索引中，索引列的顺序很重要。一般将选择性高（基数大，即不同值多）的列放在前面，例如如果order_date的不同值比total_amount多，那么CREATE INDEX idx_order_date_amount ON orders (order_date, total_amount);这样的顺序更合理。同时要注意，复合索引遵循最左前缀原则，查询条件中要按照索引列的顺序使用才能发挥索引的最大功效。

星途面试题库

面试题：MySQL复杂查询场景下减少额外记录扫描的优化

知识考点

面试题答案