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

由于之前赶项目，敲出的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因为采用嵌套没走索引,走全表！！！

将嵌套查询（临时表ｄ）进行优化如下:

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

再查询执行计划:

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