如何优化OpenMRS数据库多表最新患者数据查询SQL？

我需要从OpenMRS数据库读取唯一的最新患者数据，但当前SQL查询耗时过长，希望优化代码提升效率。

初始查询语句 #

SELECT
    pid2.identifier AS HospitalNumber,
    pid1.identifier AS UniqueID,
    person.gender AS Gender,
    MAX(encounter.encounter_datetime) AS LatestVisitDate,
    CONCAT(pn.given_name, ' ', pn.family_name) AS Patient_Name,
    CAST(psn_atr.value AS CHAR) AS Phone_No,
    pa.address1 AS Patient_Address,
    pa.city_village AS Patient_LGA,
    pa.state_province AS Patient_State,
    MAX(IF(obs.concept_id = 159599, obs.value_datetime, NULL)) AS ART_Start_Date
--    MAX(IF((obs.concept_id=165708 and encounter.form_id=27 AND obs.obs_datetime <= @endDate AND obs.voided =0),container.last_date,null)) as Pharmacy_LastPickupdate
FROM patient_identifier AS pid2
JOIN patient_identifier AS pid1 ON pid2.patient_id = pid1.patient_id
INNER JOIN encounter ON encounter.patient_id = pid2.patient_id
INNER JOIN person ON person.person_id = pid2.patient_id
INNER JOIN person_name AS pn ON pn.person_id = pid2.patient_id
LEFT JOIN person_attribute AS psn_atr ON psn_atr.person_id = pid2.patient_id
    AND psn_atr.person_attribute_type_id = (SELECT person_attribute_type_id FROM person_attribute_type WHERE name = 'Telephone Number')
LEFT JOIN person_address AS pa ON pa.person_id = pid2.patient_id
LEFT JOIN obs ON obs.person_id = pid2.patient_id
WHERE pid2.identifier_type = 5 AND pid1.identifier_type = 4
AND encounter.voided = 0 AND pid1.voided = 0
GROUP BY pid2.identifier, pid1.identifier, person.gender, person.birthdate, Patient_Name, Phone_No, Patient_Address, Patient_LGA, Patient_State;

尝试的子查询优化（存在多余行问题） #

我曾尝试用子查询优化，但结果出现多余行，不符合需求，尝试的查询语句如下：

SELECT
    pid2.identifier AS HospitalNumber,
    pid1.identifier AS UniqueID,
    person.gender AS Gender,
    encounter.encounter_datetime AS LatestVisitDate,
    CONCAT(pn.given_name, ' ', pn.family_name) AS Patient_Name,
    CAST(psn_atr.value AS CHAR) AS Phone_No,
    pa.address1 AS Patient_Address,
    pa.city_village AS Patient_LGA,
    pa.state_province AS Patient_State,
    obs_art_start_date.max_art_start_date AS ART_Start_Date
FROM patient_identifier AS pid2
JOIN patient_identifier AS pid1 ON pid2.patient_id = pid1.patient_id
INNER JOIN encounter ON encounter.patient_id = pid2.patient_id
INNER JOIN person ON person.person_id = pid2.patient_id
INNER JOIN person_name AS pn ON pn.person_id = pid2.patient_id
LEFT JOIN person_attribute AS psn_atr ON psn_atr.person_id = pid2.patient_id
    AND psn_atr.person_attribute_type_id = (SELECT pa_type.person_attribute_type_id FROM person_attribute_type AS pa_type WHERE pa_type.name = 'Telephone Number')
LEFT JOIN person_address AS pa ON pa.person_id = pid2.patient_id
LEFT JOIN (
    SELECT
        obs.person_id,
        MAX(obs.value_datetime) AS max_art_start_date
    FROM obs
    WHERE obs.concept_id = 159599
    GROUP BY obs.person_id
) AS obs_art_start_date ON obs_art_start_date.person_id = pid2.patient_id
WHERE pid2.identifier_type = 5 AND pid1.identifier_type = 4
    AND encounter.voided = 0 AND pid1.voided = 0;

优化方案与最终查询 #

核心优化点 #

• 提前聚合最新就诊记录和ART起始日期，避免主查询关联大表导致的数据膨胀
• 预获取电话属性类型ID，减少重复子查询执行
• 使用CTE拆分逻辑，提升查询可读性与执行计划优化空间
• 明确按患者ID分组，确保每行对应唯一患者

优化后的SQL #

-- 预获取每个患者的最新就诊时间
WITH latest_encounter AS (
    SELECT 
        patient_id,
        MAX(encounter_datetime) AS LatestVisitDate
    FROM encounter
    WHERE voided = 0
    GROUP BY patient_id
),
-- 预获取每个患者的ART起始日期
art_start_date AS (
    SELECT 
        person_id,
        MAX(value_datetime) AS ART_Start_Date
    FROM obs
    WHERE concept_id = 159599 AND voided = 0
    GROUP BY person_id
),
-- 预获取电话属性类型ID，避免重复查询
phone_attr_type AS (
    SELECT person_attribute_type_id 
    FROM person_attribute_type 
    WHERE name = 'Telephone Number'
)
SELECT
    pid2.identifier AS HospitalNumber,
    pid1.identifier AS UniqueID,
    p.gender AS Gender,
    le.LatestVisitDate,
    CONCAT(pn.given_name, ' ', pn.family_name) AS Patient_Name,
    CAST(psa.value AS CHAR) AS Phone_No,
    pa.address1 AS Patient_Address,
    pa.city_village AS Patient_LGA,
    pa.state_province AS Patient_State,
    asd.ART_Start_Date
FROM patient_identifier pid2
JOIN patient_identifier pid1 
    ON pid2.patient_id = pid1.patient_id
    AND pid1.identifier_type = 4
    AND pid1.voided = 0
JOIN person p 
    ON p.person_id = pid2.patient_id
JOIN person_name pn 
    ON pn.person_id = pid2.patient_id
-- 关联预查询的最新就诊记录
JOIN latest_encounter le 
    ON le.patient_id = pid2.patient_id
LEFT JOIN person_attribute psa 
    ON psa.person_id = pid2.patient_id
    AND psa.person_attribute_type_id = (SELECT person_attribute_type_id FROM phone_attr_type)
LEFT JOIN person_address pa 
    ON pa.person_id = pid2.patient_id
LEFT JOIN art_start_date asd 
    ON asd.person_id = pid2.patient_id
WHERE pid2.identifier_type = 5 
    AND pid2.voided = 0
-- 确保每个患者只返回一行
GROUP BY pid2.patient_id, pid2.identifier, pid1.identifier, p.gender, le.LatestVisitDate, Patient_Name, Phone_No, Patient_Address, Patient_LGA, Patient_State;

内容的提问来源于stack exchange，提问作者Chinedu