请教怎么优化查询效率

Lcindep110 2017-09-16 07:06:55

怎么优化这张表呢？现在查询好慢

...全文

224 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

zjcxc 2017-09-21

打赏
举报

估计是自定义函数的问题，你可以试试去掉函数，仅做查询看看效率

qq_30635781 2017-09-21

打赏
举报

先算运算，再查

ACMAIN_CHM 2017-09-21

打赏
举报

create index xxx on jh_device_upload_5387(terminal,newFinishHour)

Lcindep110 2017-09-21

打赏
举报

EXPLAIN select terminal, avg(fun_get_value(substring(filedir,113,16))) as noise, avg(fun_get_value(substring(filedir,129,16))) as pm25, avg(fun_get_value(substring(filedir,145,16))) as pm10, avg(fun_get_value(substring(filedir,161,16))) as temperature, avg(fun_get_value(substring(filedir,177,16))) as humidity, avg(fun_get_value(substring(filedir,193,16))) as windspeed, avg(fun_get_value(substring(filedir,225,16))) as tsp,newFinishHour as d from jh_device_upload_5387 FORCE INDEX(terminal_newFinishMinute) where terminal=5387 and newFinishMinute>=201708210000 and newFinishMinute<=201709211459 group by terminal,d 1 SIMPLE jh_device_upload_5387 range terminal_newFinishMinute terminal_newFinishMinute 13 22956 Using where; Using temporary; Using filesort

Lcindep110 2017-09-21

打赏
举报

show index from jh_device_upload_5387 jh_device_upload_5387 0 PRIMARY 1 id A 45901 BTREE jh_device_upload_5387 0 PRIMARY 2 terminal A 45901 BTREE jh_device_upload_5387 0 PRIMARY 3 InfoType A 45901 BTREE jh_device_upload_5387 1 newFinishMonth 1 newFinishMonth A 2 YES BTREE jh_device_upload_5387 1 newFinishDate 1 newFinishDate A 108 YES BTREE jh_device_upload_5387 1 newFinishHour 1 newFinishHour A 1207 YES BTREE jh_device_upload_5387 1 newFinishMinute 1 newFinishMinute A 45901 YES BTREE jh_device_upload_5387 1 terminal_newFinishMinute 1 terminal A 2 BTREE jh_device_upload_5387 1 terminal_newFinishMinute 2 newFinishMinute A 45901 YES BTREE

Lcindep110 2017-09-21