如何优化医院病历库多连接慢查询的性能？

1. 修复WHERE条件的函数操作，添加索引 #

原查询中trim(replace(st.NUMBER, '-','')) = '01099999999'、trim(sc.NAME) = 'johndoe'这类字段上的函数操作，会导致对应字段的索引完全失效，MySQL只能做全表扫描。

解决办法：

• 新增计算列存储预处理后的数据，并给计算列加索引：

  -- 处理syn_tel的手机号
  ALTER TABLE syn_tel ADD COLUMN cleaned_number VARCHAR(20) AS (trim(replace(NUMBER, '-',''))) STORED;
  CREATE INDEX idx_tel_cleaned_num ON syn_tel(cleaned_number, HOSPITAL_ID, CLIENT_ID);
  
  -- 处理syn_client的姓名
  ALTER TABLE syn_client ADD COLUMN cleaned_name VARCHAR(50) AS (trim(NAME)) STORED;
  CREATE INDEX idx_client_cleaned_name ON syn_client(cleaned_name, HOSPITAL_ID, FAMILY_ID);
  

• 修改WHERE条件为直接匹配预处理后的列：

  st.cleaned_number = '01099999999'
  AND sc.cleaned_name = 'johndoe'
  

2. 替换关联子查询为JOIN方式获取最新体重 #

原查询中获取最新体重的关联子查询，会对每一条匹配的宠物记录单独执行一次查询，数据量大时耗时极长。换成CTE+JOIN的方式批量获取：

WITH latest_vital AS (
    SELECT 
        HOSPITAL_ID, 
        PET_ID, 
        BW,
        -- 按医院+宠物分组，取最新时间的记录
        ROW_NUMBER() OVER (PARTITION BY HOSPITAL_ID, PET_ID ORDER BY DATE DESC, TIME DESC) AS rn
    FROM syn_vital
)
select 
    sc.CLIENT_ID as 'guardianId', 
    sp.PET_ID as 'patientId', 
    sp.NAME as 'petName',
    lv.BW as 'weight',
    sp.BIRTH as 'birth', 
    sp.RFID as 'regNo', 
    sp.BREED as 'vName',
    -- 保留原CASE逻辑
    (case when ss.NAME like '%fel%' or ss.NAME like '%cat%' or ss.NAME like '%pawpaw%' or ss.NAME like '%f' then '002'
    when ss.NAME like '%canine%' or ss.NAME like '%dog%' or ss.NAME like '%can%' then '001' else '007' end) as 'sCode',
    (case when LOWER(replace(sp.SEX, ' ', '')) like 'male%' then 'M'
    when LOWER(replace(sp.SEX, ' ', '')) like 'female%' or LOWER(replace(sp.SEX, ' ', '')) like 'fam%' or LOWER(replace(sp.SEX, ' ', '')) like 'woman%' then 'F'
    when LOWER(replace(sp.SEX, ' ', '')) like 'c.m%' or LOWER(replace(sp.SEX, ' ', '')) like 'castratedmale' or LOWER(replace(sp.SEX, ' ', '')) like 'neutered%' or LOWER(replace(sp.SEX, ' ', '')) like 'neutrality%man%' or LOWER(replace(sp.SEX, ' ', '')) like 'M.N%' then 'MN'
    when LOWER(replace(sp.SEX, ' ', '')) like 'woman%' or LOWER(replace(sp.SEX, ' ', '')) like 'f.s%' or LOWER(replace(sp.SEX, ' ', '')) like 'S%' or LOWER(replace(sp.SEX, ' ', '')) like 'neutrality%%' then 'FS' else 'NONE' end) as 'sex'
from syn_client sc
inner join syn_tel st on sc.HOSPITAL_ID = st.HOSPITAL_ID and sc.CLIENT_ID = st.CLIENT_ID
inner join syn_pet sp on sc.HOSPITAL_ID = sp.HOSPITAL_ID and sc.FAMILY_ID = sp.FAMILY_ID and sp.STATE = 0
inner join syn_species ss on sp.HOSPITAL_ID = ss.HOSPITAL_ID and sp.SPECIES_ID = ss.SPECIES_ID
LEFT JOIN latest_vital lv ON sp.HOSPITAL_ID = lv.HOSPITAL_ID AND sp.PET_ID = lv.PET_ID AND lv.rn = 1
WHERE
st.cleaned_number = '01099999999'
and sc.cleaned_name = 'johndoe'
and sp.HOSPITAL_ID = 'HOSPITALID999999'
order by st.TEL_DEFAULT desc

同时给syn_vital加复合索引，加速CTE的分组排序：

CREATE INDEX idx_vital_pet_date ON syn_vital(HOSPITAL_ID, PET_ID, DATE DESC, TIME DESC, BW);

3. 添加连接与查询字段的复合索引 #

根据查询的连接和字段获取需求，创建覆盖索引避免回表：

• syn_pet：CREATE INDEX idx_pet_hospital_family ON syn_pet(HOSPITAL_ID, FAMILY_ID, STATE, PET_ID, NAME, BIRTH, RFID, BREED, SEX);
• syn_species：CREATE INDEX idx_species_hospital_id ON syn_species(HOSPITAL_ID, SPECIES_ID, NAME);
• syn_tel：CREATE INDEX idx_tel_hospital_client ON syn_tel(HOSPITAL_ID, CLIENT_ID, TEL_DEFAULT);

4. 优化CASE逻辑（可选） #

原查询中对SEX和物种NAME的大量模糊匹配，每次查询都要做字符串处理，可以提前预处理：

• 给syn_pet新增sex_code列，提前把原SEX字段转换为'M'/'F'/'MN'/'FS'/'NONE'，查询时直接取该列值。
• 给syn_species新增s_code列，提前标记每个物种对应的'001'/'002'/'007'，避免每次查询都做模糊匹配。

5. 修正连接逻辑 #

原查询用LEFT JOIN syn_tel但WHERE条件过滤了st的字段，等价于INNER JOIN，直接改成INNER JOIN可以减少MySQL执行计划的判断成本。

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