mysql 百万数据查询缓慢怎么解决 已经分区,并且主键索引. 求解决

Question

mysql数据库, 引擎是innoDB,但是查询时间还是很慢, 有的已经达到30s, 找不到问题所在,求大神指点,这是查询代码,并无连表什么的, 分区是按月分的,只6月份的数据文件大小就22G,请问这个是问题出在哪里了?

 SELECT `PQData`->'$."BB1#V_RMS#Phase_AB#0#VOLTS"."avg"' AS `BB1#V_RMS#Phase_AB#0#VOLTS_avg`, `PQData`->'$."BB1#V_RMS#Phase_AB#0#VOLTS"."max"' AS `BB1#V_RMS#Phase_AB#0#VOLTS_max`, `PQData`->'$."BB1#V_RMS#Phase_AB#0#VOLTS"."min"' AS `BB1#V_RMS#Phase_AB#0#VOLTS_min` FROM `pd_1_1_pqfreeintervaldata` WHERE `DeviceID` = 9 AND `RecordTimeStampLocal` BETWEEN 1494239850 AND 1498732650

附图:

换成>= <=

SELECT `PQData`->'$."BB1#V_RMS#Phase_AB#0#VOLTS"."avg"' AS `BB1#V_RMS#Phase_AB#0#VOLTS_avg`, `PQData`->'$."BB1#V_RMS#Phase_AB#0#VOLTS"."max"' AS `BB1#V_RMS#Phase_AB#0#VOLTS_max`, `PQData`->'$."BB1#V_RMS#Phase_AB#0#VOLTS"."min"' AS `BB1#V_RMS#Phase_AB#0#VOLTS_min` FROM `pd_1_1_pqfreeintervaldata` WHERE `DeviceID` = 9 AND `RecordTimeStampLocal` >= 1494239850 AND  `RecordTimeStampLocal`<=1498732650

Answer 1

另外补充下，其实你单纯查一个大表，用索引字段=xxx 获取结果还是很快的，但是如果count(1) 结果非常大。肯定就会非常慢