请教怎么优化查询效率

Question

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

Answer 1

以文本方式贴出(不要贴图!)show create table ...explain select ....
及
show index from 以供分析

Answer 2

你这个sql中计算的太多了，sql最好就是准确查询，条件多没关系，但是运算会严重拖延查询效率，可以尝试将运算的部分放到代码中执行

Answer 3

show index from jh_device_upload_5387jh_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

Answer 4

EXPLAINselect 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

Answer 5

create index xxx on jh_device_upload_5387(terminal,newFinishHour)