SQL优化困局：从90秒延迟到18秒响应的实战突围

-- 原始查询（看似合理实则低效） SELECT customer_id, first_name, last_name, AVG(DATEDIFF(day, order_date, GETDATE())) AS avg_days_since_order FROM orders JOIN customers ON orders.customer_id = customers.id WHERE status =Completed GROUPBY customer_id, first_name, last_name HAVING AVG(DATEDIFF(day, order_date, GETDATE())) > 301.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.

WITH order_days AS ( SELECT customer_id, DATEDIFF(day, order_date, GETDATE()) AS days_since_order FROM orders WHERE status =Completed ) SELECT c.id, c.first_name, c.last_name, AVG(o.days_since_order) AS avg_days_since_order FROM order_days o JOIN customers c ON o.customer_id = c.id GROUPBY c.id, c.first_name, c.last_name HAVING AVG(o.days_since_order) > 301.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.