面试题：MySQL复杂查询场景下的查询优化策略

索引优化：
- 用户表（user）：
  - 在user表的id字段上确保有主键索引，这对于关联其他表时快速定位用户数据很关键。
  - 如果login_record表与user表关联是通过user_id，那么在user表的user_id字段（若不是主键）上创建索引，便于快速连接查询活跃用户。
- 订单表（order）：
  - order表的user_id字段创建索引，方便与user表关联查询特定用户的订单。
  - order_date字段创建索引，用于筛选过去一个月内的订单。
- 订单详情表（order_detail）：
  - order_id字段创建索引，用于与order表关联获取订单详情。
  - product_id字段创建索引，方便获取特定商品类别的订单详情。
  - 联合索引(order_id, product_id)，如果查询中经常同时基于订单和商品进行筛选，这个联合索引可以提高查询效率。
- 商品表（product）：
  - product_id字段创建主键索引，方便与order_detail表关联。
  - category字段创建索引，用于筛选特定类别的商品。
- 登录记录表（login_record）：
  - user_id字段创建索引，方便与user表关联。
  - login_date字段创建索引，用于筛选过去一周内的登录记录。
查询语句改写：
- 使用JOIN代替子查询：假设使用MySQL数据库，示例查询语句如下：

SELECT u.user_id, SUM(od.price * od.quantity) AS total_spend
FROM user u
-- 关联活跃用户
JOIN login_record lr ON u.user_id = lr.user_id AND lr.login_date >= CURDATE() - INTERVAL 1 WEEK
-- 关联订单
JOIN `order` o ON u.user_id = o.user_id AND o.order_date >= CURDATE() - INTERVAL 1 MONTH
-- 关联订单详情
JOIN order_detail od ON o.order_id = od.order_id
-- 关联商品
JOIN product p ON od.product_id = p.product_id AND p.category = '特定类别'
GROUP BY u.user_id
HAVING total_spend > 1000
ORDER BY total_spend DESC;

这样通过JOIN操作可以在一次扫描中获取所需数据，避免了子查询可能带来的多次扫描开销。

避免使用函数操作字段：在查询条件中尽量避免对字段使用函数，例如不要使用DATE_FORMAT(o.order_date, '%Y-%m-%d') >= CURDATE() - INTERVAL 1 MONTH，而应像上述示例直接使用o.order_date >= CURDATE() - INTERVAL 1 MONTH，因为对字段使用函数会使索引失效。
合理使用EXISTS或NOT EXISTS：如果数据量较大且查询逻辑允许，可以考虑使用EXISTS或NOT EXISTS来优化关联查询。例如，如果只想查找有特定类别商品订单的活跃用户，可以改写为：

SELECT u.user_id, SUM(od.price * od.quantity) AS total_spend
FROM user u
JOIN login_record lr ON u.user_id = lr.user_id AND lr.login_date >= CURDATE() - INTERVAL 1 WEEK
WHERE EXISTS (
    SELECT 1
    FROM `order` o
    JOIN order_detail od ON o.order_id = od.order_id
    JOIN product p ON od.product_id = p.product_id AND p.category = '特定类别'
    WHERE o.user_id = u.user_id AND o.order_date >= CURDATE() - INTERVAL 1 MONTH
)
GROUP BY u.user_id
HAVING total_spend > 1000
ORDER BY total_spend DESC;

其他优化：
- 分区表：如果数据量巨大，可以考虑对order表按order_date进行分区，例如按月分区。这样在查询过去一个月内的订单时，只需要扫描相关分区的数据，减少扫描的数据量，提高查询性能。
- 查询缓存：如果数据库支持查询缓存（如MySQL早期版本），对于不经常变化的数据，可以启用查询缓存。但要注意缓存的更新策略，避免数据不一致问题。例如，如果商品类别、订单数据等经常变化，就不太适合使用查询缓存。

星途面试题库

面试题：MySQL复杂查询场景下的查询优化策略

知识考点

面试题答案