mysql查询性能优化

码拜

8年 ago

SELECT
a.ELEMENT_ID,
a.ELEMENT_NAME,
a.VENDOR_CODE,
a.ELEMENT_TYP,
a.SEQUENCE,
a.STARTTIME,
a.ENDTIME,
a.TRICK_POINT,
a.PARTNUMBER,
a.BL_NUMBER
FROM cc_page_data a
WHERE a.MARKFORDELETE in (1,2)
AND a.ENDTIME > current_timestamp()
AND a.PAGE_ID = 18
AND a.TEMPLATE_FULL_ID = 18
AND a.MODEL_FULL_ID = 112
AND EXISTS
(SELECT 1
FROM cc_page_data b
WHERE b.ELEMENT_ID in( 14861,14863,14864)
AND a.PAGE_ID = b.PAGE_ID
AND a.TEMPLATE_FULL_ID = b.TEMPLATE_FULL_ID
AND a.MODEL_FULL_ID = b.MODEL_FULL_ID
AND a.SEQUENCE = b.SEQUENCE)
ORDER BY a.SEQUENCE, a.STARTTIME

解决方案

100

SELECT  a.ELEMENT_ID ,
        a.ELEMENT_NAME ,
        a.VENDOR_CODE ,
        a.ELEMENT_TYP ,
        a.SEQUENCE ,
        a.STARTTIME ,
        a.ENDTIME ,
        a.TRICK_POINT ,
        a.PARTNUMBER ,
        a.BL_NUMBER
FROM    cc_page_data a
        JOIN cc_page_data b ON a.PAGE_ID = b.PAGE_ID
                               AND a.TEMPLATE_FULL_ID = b.TEMPLATE_FULL_ID
                               AND a.MODEL_FULL_ID = b.MODEL_FULL_ID
                               AND a.SEQUENCE = b.SEQUENCE
WHERE   a.MARKFORDELETE IN ( 1, 2 )
        AND a.ENDTIME >current_timestamp()
        AND a.PAGE_ID = 18
        AND a.TEMPLATE_FULL_ID = 18
        AND a.MODEL_FULL_ID = 112
        AND b.ELEMENT_ID IN ( 14861, 14863, 14864 )
ORDER BY a.SEQUENCE ,
        a.STARTTIME