sql优化

 一次侧 d_firstminutehandledata 

SELECT
    count(1)
FROM
    d_firstminutehandledata
WHERE
    stationBranchId = 532
AND collectionTime BETWEEN '2022-01-13'
AND '2023-03-01'

 九万多条数据 

SELECT
    collectionTime,
    stationBranchId,
    accHeat,
    accFlow,
    waterMeterAccFlow,
    Data_FMeterSFlow_A,
    totalElectricMeterElectricity
FROM
    d_firstminutehandledata
WHERE
    stationBranchId = 532
AND collectionTime BETWEEN '2022-01-13'
AND '2023-03-01'
LIMIT 10000

 

 查询一万条数据21秒

SELECT
    collectionTime,
    stationBranchId,
    accHeat,
    accFlow,
    waterMeterAccFlow,
    Data_FMeterSFlow_A,
    totalElectricMeterElectricity
FROM
    d_firstminutehandledata
WHERE
    stationBranchId = 532
AND collectionTime BETWEEN '2022-01-13'
AND '2023-03-01'
ORDER BY
    collectionTime DESC
LIMIT 10000

 

加上排序，同样是查询一万条数据，但是查询时间却变成了285秒

SELECT
    collectionTime,
    stationBranchId
FROM
    d_firstminutehandledata
WHERE
    stationBranchId = 532
AND collectionTime BETWEEN '2022-01-13'
AND '2023-03-01'
ORDER BY
    collectionTime DESC
LIMIT 10000

仅仅只查询联合主键（联合主键会创建索引），查询条件是索引字段，查询字段也是索引字段是非常快的 ，即使加了排序也是非常快，不到1秒

那么我想到可以利用这个非常快的查询，来查询其他需要的字段

SELECT
    b.*, a.accHeat,
    a.accFlow,
    a.waterMeterAccFlow,
    a.Data_FMeterSFlow_A,
    a.totalElectricMeterElectricity
FROM
    d_firstminutehandledata a
RIGHT JOIN (
    SELECT
        collectionTime,
        stationBranchId
    FROM
        d_firstminutehandledata
    WHERE
        stationBranchId = 532
    AND collectionTime BETWEEN '2022-01-13'
    AND '2023-03-01'
    ORDER BY
        collectionTime DESC
    LIMIT 10000
) b ON a.stationBranchId = b.stationBranchId
AND a.collectionTime = b.collectionTime

 用子查询，然后right join，这样主查询中就不需要排序什么的操作，仅仅是索引 in 子查询，而子查询又非常快，结果是32秒，跟第一个无排序使用了21秒的查询相比虽然慢了10秒，但是 和 第二个加了排序的查询 285秒相比 效率却是大大提高了，可以说是质的飞越，这样既可以满足带排序的查询，又可以满足时间上的快速，完美！