面试题：MySQL高级难度：复杂查询优化与简单查询对比

Question

假设存在一个医院的数据库，有病人表（patients）包含病人ID（patient_id）、病人姓名（patient_name）、年龄（age）等字段，诊断表（diagnoses）包含诊断ID（diagnosis_id）、病人ID（patient_id）、诊断结果（diagnosis_result）、诊断时间（diagnosis_time）。现在要找出每个年龄段（以10岁为一个区间，如0 - 9岁，10 - 19岁等）最常见的三种诊断结果及其出现次数，且只统计近一年的诊断数据。请分析使用简单查询和复杂查询的可行性及性能差异，并给出一个优化后的复杂查询SQL语句。

Answer 1

简单查询可行性及性能分析

1. 可行性：简单查询可以通过多次查询来实现。例如，先根据年龄区间筛选病人，再针对每个年龄区间的病人ID去诊断表中获取诊断结果并统计次数，最后对每个区间的结果取前3名。
2. 性能：性能较差。因为需要对不同年龄区间进行多次查询，每次查询都要扫描诊断表，增加了数据库I/O和处理时间，尤其在数据量较大时效率极低。

复杂查询可行性及性能分析

1. 可行性：复杂查询可以通过使用窗口函数和子查询等技术，在一个查询中完成所有计算。
2. 性能：性能较好。一次扫描数据，利用窗口函数计算每个诊断结果在各年龄区间的出现次数，再通过子查询筛选出每个区间最常见的前3种诊断结果，减少了重复扫描数据的开销。

优化后的复杂查询SQL语句

WITH age_diagnosis_count AS (
    SELECT 
        p.age,
        d.diagnosis_result,
        COUNT(*) AS diagnosis_count,
        ROW_NUMBER() OVER (PARTITION BY p.age ORDER BY COUNT(*) DESC) AS rn
    FROM 
        patients p
    JOIN 
        diagnoses d ON p.patient_id = d.patient_id
    WHERE 
        d.diagnosis_time >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
    GROUP BY 
        p.age, d.diagnosis_result
)
SELECT 
    age,
    diagnosis_result,
    diagnosis_count
FROM 
    age_diagnosis_count
WHERE 
    rn <= 3;

此SQL语句首先使用CTE（Common Table Expression）计算每个年龄区间内每个诊断结果的出现次数，并通过ROW_NUMBER()窗口函数为每个年龄区间内的诊断结果按出现次数降序排名。然后在外部查询中筛选出每个年龄区间排名前三的诊断结果。