索引失效问题,不知道原因

一: select 
ID,b.STAND_ID,b.STATION_ID,GROUP_ID,a.OP_TIME,a.BATCH_NUMBER
from  data_collect a inner join
(SELECT STATION_ID,STAND_ID, max(BATCH_NUMBER) as BATCH_NUMBER
    FROM
        data_collect
    WHERE
    OP_TIME <= '2018-03-23 09:01:00'
GROUP BY  STAND_ID
)  b 
on a.BATCH_NUMBER=b.BATCH_NUMBER and  a.STAND_ID=b.STAND_ID

order by b.STATION_ID,b.STAND_ID

索引为:STAND_ID, OP_TIME, BATCH_NUMBER  

不走索引

二:走索引

SELECT

ID,
b.STAND_ID,
a.STATION_ID,
a.GROUP_ID,
a.OP_TIME,
a.PT1,
a.PT2,
a.TE1,
a.TE2,
a.PT3,
a.PT4,
a.TE3,
a.TE4,
CVI1,
QI,
FT1,
FT1Q,
FT2,
FT3,
FC1V1,
LT1,
QQI,
JQI,
FT3Q,
FT2Q,
FT3Q_PRICE,
JQI_PRICE,
QQI_PRICE,
a.BATCH_NUMBER
FROM
data_collect a
INNER JOIN (
SELECT
STAND_ID,
 max(BATCH_NUMBER) AS BATCH_NUMBER
FROM
data_collect
WHERE
1 = 1
AND OP_TIME <= '2018-03-23 09:01:00'
GROUP BY STAND_ID
) b ON a.BATCH_NUMBER = b.BATCH_NUMBER AND a.STAND_ID = b.STAND_ID
ORDER BY a.STATION_ID,a.STAND_ID
展开阅读全文

没有更多推荐了,返回首页