sql语句优化之xxx医院/搜索列表

时间:2022-09-21 00:14:09

由于之前赶项目,敲出的sql,竟然花了1.414s,而且还是分页下的.

(注:TB_MB_HospitalConfig 的HosID,DepID,DocID等都建了索引)

SELECT
h.MemberId uuid,
h.HospitalName name,
h.AddressDetail address,
h.PhoneNum phone,
h.Level level,
h.HospitalIntro description,
d.count_t appointDoctorCount,
mi.avatar imgUrl,
CASE hc.isServiceOpen
WHEN 'N' THEN 'N'
ELSE 'Y'
END yygh
FROM
TB_MB_HosptalMember h
LEFT JOIN
(
SELECT
                HospitalID,
                count(DISTINCT doc.ID) count_t  
            FROM
                TB_MB_DoctorsInfo doc  
            LEFT JOIN
                (
                    select
                        docID,
                        paramValue as isServiceOpen  
                    from
                        TB_MB_HospitalConfig  
                    where
                        configType = 2  
                        and serviceType = 0  
                        and paramkey = 'IsServiceOpen'  
                        and status = 0
                ) hc  
                    ON doc.memberID = hc.docID  
            LEFT JOIN
                TB_MB_Scheduling sch  
                    ON doc.ID = sch.PlanDoctorID  
            WHERE
                sch.ScheduledDate > '2014-12-23'  
                AND sch.ScheduledDate <= '2014-12-30'
                AND sch.Enabled = true  
                AND sch.Status = false  
                AND sch.AvailableCount > 0  
                AND doc.Status = false  
                AND (
                    hc.isServiceOpen = 'Y'  
                    or hc.isServiceOpen IS NULL
                )  
            GROUP BY
                HospitalID
  ) d
ON h.MemberID=d.HospitalID
LEFT JOIN
TB_MB_MemberInfo mi
ON h.MemberID=mi.ID
LEFT JOIN
(
SELECT
HosID,
ParamValue as isServiceOpen
FROM
TB_MB_HospitalConfig
WHERE
ConfigType=0
AND ServiceType=0
AND Paramkey='IsServiceOpen'
AND Status=0
) hc
ON h.MemberID = hc.HosID
LEFT JOIN
(
SELECT
HosID,
ParamValueInt as sort
FROM
TB_MB_HospitalConfig
WHERE
ConfigType=0
AND ServiceType=0
AND Paramkey='Sort'
AND Status=0
) s
ON h.MemberID = s.HosID
WHERE
mi.Shield = false
ORDER BY
yygh DESC,
s.sort IS NULL,
s.sort DESC,
d.count_t DESC,
h.MemberId ASC limit 10

查看了一下mysql执行计划:

sql语句优化之xxx医院/搜索列表

红色的sql因为采用嵌套没走索引,走全表!!!

将嵌套查询(临时表d)进行优化如下:

SELECT HospitalID, COUNT(DISTINCT doc.ID) count_t 
FROM TB_MB_DoctorsInfo doc
LEFT JOIN TB_MB_HospitalConfig config
ON doc.memberID = config.docID AND config.configType = 2 AND config.serviceType = 0 AND config.paramkey = 'IsServiceOpen' AND config.STATUS = 0
LEFT JOIN TB_MB_Scheduling sch
ON doc.ID = sch.PlanDoctorID
WHERE sch.ScheduledDate > '2014-12-23'
AND sch.ScheduledDate <= '2014-12-30'
AND sch.Enabled = TRUE
AND sch.Status = FALSE
AND sch.AvailableCount > 0
AND doc.Status = FALSE
AND (
config.paramValue = 'Y'
OR config.paramValue IS NULL
)
GROUP BY HospitalID

再查询执行计划:
sql语句优化之xxx医院/搜索列表

查询时间:0.147s,缩短了差不多1s.