SQL查询生成与优化

请帮我写SQL查询：

表结构：
[列出相关表和字段]

需求：[描述要查询什么]

要求：
1. 写出正确的SQL
2. 解释查询逻辑
3. 如果有多种写法，比较优劣
4. 建议需要什么索引
5. 分析查询性能（EXPLAIN思路）
6. 考虑大数据量下的优化

SELECT 
  u.name,
  COUNT(o.id) as order_count,
  SUM(o.total_amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2024-01-01'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5
ORDER BY total_spent DESC
LIMIT 100;

CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);
CREATE INDEX idx_orders_total ON orders(total_amount);