怎么優化這張表呢？現在查詢好慢
uj5u.com熱心網友回復：
uj5u.com熱心網友回復：
以文本方式貼出(不要貼圖!)show create table ...
explain select ....
及
show index from
以供分析
uj5u.com熱心網友回復：
你這個sql中計算的太多了，sql最好就是準確查詢，條件多沒關系，但是運算會嚴重拖延查詢效率，可以嘗試將運算的部分放到代碼中執行uj5u.com熱心網友回復：
好的
uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
先算運算，再查uj5u.com熱心網友回復：
create index xxx on jh_device_upload_5387(terminal,newFinishHour)uj5u.com熱心網友回復：
估計是自定義函式的問題，你可以試試去掉函式，僅做查詢看看效率轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/110881.html
標籤：MySQL