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

最新推荐文章于 2024-07-05 09:30:00 发布

weixin_34422383

最新推荐文章于 2024-07-05 09:30:00 发布

阅读量996

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/weixin_34422383/article/details/79847668

版权

MYSQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一： 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