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秒相比 效率却是大大提高了，可以说是质的飞越，这样既可以满足带排序的查询，又可以满足时间上的快速，完美！