Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
3.5k views
in Technique[技术] by (71.8m points)

mysql查询效率慢

SELECT

( SELECT eoor.NAME FROM EOM_O_OPER eoor WHERE eoor.oper_id = walu.LOGIN_OPER_ID ) AS OPER_NAME,
( SELECT eoo.org_name FROM EOM_O_ORG eoo WHERE eoo.org_no = walu.ORG_NO ) AS ORG_NO,
( SELECT wsc.NAME FROM WF_SYS_CODE wsc WHERE wsc.CODE_TYPE = 'LOG_UPLOAD_TYPE' AND wsc.VALUE = walu.LOG_TYPE ) AS LOG_TYPE,
IMEI,
UPLOAD_TIME,
LOG_SIZE,
FILE_NO 

FROM

    WF_APP_LOG_UPLOAD walu 
    
    上面的子查询 只是为了把对应的编码转成对应的名称
          人员账号 转成 人员名称
          地区编号转成 地区名称
          类型编码 转成 类型名称
          这就是三个子查询所做的事情
          
          换成关联表的形式去查询更加慢。如下:
          
           SELECT 

walu.IMEI,

walu.UPLOAD_TIME,
walu.LOG_SIZE,
walu.FILE_NO

from WF_APP_LOG_UPLOAD walu
LEFT JOIN EOM_O_OPER eoor
on eoor.oper_id = walu.LOGIN_OPER_ID
LEFT JOIN EOM_O_ORG eoo
on eoo.org_no = walu.ORG_NO
LEFT JOIN WF_SYS_CODE wsc
on wsc.CODE_TYPE = 'LOG_UPLOAD_TYPE' AND wsc.VALUE = walu.LOG_TYPE


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

没有筛选条件吗,需要全表数据?检查关联建是否有索引


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...