쿼리 쓰고있는데 속도가 비정상적으로 느려요.. 서브쿼리..도와주세요 0 12 236

by 마이닝 [MySQL] [2019.01.11 13:15:20]


mysql 아래와 같이 쿼리 작성해서 돌리려고해요.

select @rownum:=@rownum+1 as RNUM,
(select count(*) from WL_DEVICE where SITE_ID = a.SITE_ID and DEVICE_TYPE = 4) scount,
(select count(*) from WL_DEVICE where SITE_ID = a.SITE_ID and DEVICE_TYPE = 2) lcount,
(select count(*) from WL_DEVICE where SITE_ID = a.SITE_ID and DEVICE_TYPE = 1) ccount,
(select count(distinct ieee_address) from WL_SENSOR_LEAK_DATA where SITE_ID = a.SITE_ID and receive_date = '20190105' and leak_percent between 0 and 79) gpass
from(
  select distinct e.SIDO_NAME, d.SITE_NAME, a.SITE_ID, c.VERSION from WL_DEVICE a
  left join WL_DEVICE_LOCATION b on a.SITE_ID = b.SITE_ID
  left join WL_DEVICE_ADDITONAL c on left(replace(ltrim(replace(a.IEEE_ADDRESS,'0',' ')),' ','0'),2) = c.IEEE_DIVISION
  and a.SITE_ID = c.SITE_ID
  left join WL_SITE d on a.SITE_ID = d.SITE_ID
  left join WL_SIDO e on d.SIDO_CODE = e.SIDO_ID
  where a.SITE_ID in (select SITE_ID from WL_SITE where VISIBLE = 'y' and LTE = 0)
  and a.DEVICE_TYPE = 1 and b.DEVICE_TYPE = 1
  and c.VERSION is not null
  order by e.SIDO_NAME, d.SITE_NAME desc
)a ,(SELECT @rownum :=2) as r

원래는 잘돌아갔는데.. 검색로우는 65건.. 

그러나 gpass 컬럼을 추가하려고 넣은뒤로 속도가 어마어마 하게 느려져서 검색이안될정도..

WL_SENSOR_LEAK_DATA 에 로우수가 좀많아요 

근데 아래처럼 따로 사이트아이디 를 입력하고 검색하는건 또 금방나오거든요.

select count(distinct ieee_address) from WL_SENSOR_LEAK_DATA
where site_id = '4776025021' and receive_date = '20190105' 
and leak_percent between 0 and 79


site_id 에 개별 인덱스는 걸려있고..  검색이 가능하게 될방법이없을까요..ㅜ

site_id , receive_date, leak_percent 3개 동시에 인덱스 걸어보고있는데.. 이거걸면 좀 빨라질까요..

 

 

by 마농 [2019.01.11 13:57:51]

쿼리가 전반적으로 비효율이 많아 보이고, 안해도 될 처리를 많이 하는 느낌이네요.
최종 결과에 포함되지도 않을 항목이나 테이블들을 왜 조인하는지?
Distinct 는 왜 사용하는지? 조인이 잘못된 건 아닌지?
MySQL 버전은 어떻게 되나요?
사용되는 각 테이블의 의미와 기본키 정보는 어떻게 되나요?


by 마이닝 [2019.01.11 14:10:24]

my sql 버전은 7.7.0.579 버전이에요

WL_DEVICE  = 디바이스테이블 / 기본키 : SITE_ID , IEEE_ADDRESS

WL_DEVICE_LOCATION = 디바이스 위치테이블 / 기본키 : SITE_ID, IEEE_ADDRESS, DEVICE_TYPE

WL_SITE = 디바이스가 설치된 사이트 테이블 / 기본키 : SITE_ID

WL_SIDO = 시도 코드테이블 / 기본키 : SIDO_ID

WL_SENSOR_LEAK_DATA = 누수통신누적 테이블 / 기본키 : SITE_ID, IEEE_ADDRESS, RECEIVE_TIME

이에요 최종결과에는 포함되지않지만.. where 절에 사용되다보니.. 전부 조인걸어서 쓰고있어요..

IEEE_ADDRESS 가 수집기(디바이스) id ,즉 식별번호에요 

Distinct  는.. WL_SENSOR_LEAK_DATA 테이블 조회의 결과가 receive_time 이 각각 달라서 같은 ieee_address 가 4건씩

나오게되서.. 중복된걸 없애주려고 사용했어요....;

 

WL_SENSOR_LEAK_DATA  에 인덱스 새로걸려고 시도했지만 로우수가 너무많아서 응답없음 뜨네요..;;ㅜㅜ

 

 


by 마농 [2019.01.11 14:33:39]

조인 조건이 많이 누락된 느낌이네요.
site_id 와 ieee_address 로 조인을 걸어야 하는데
site_id 만 가지고 조인을 하니 중복이 발생하고 중복제거를 위해 불필요하게 사용되고 있구요.
최종 결과에 카운트 외에는 아무런 정보가 없는데? 이거 맞나요? 쿼리가 이상해요.
최종 원하는 결과가 뭔지 설명 가능한가요?


by 마이닝 [2019.01.11 14:37:30]
select @rownum:=@rownum+1 as RNUM, a.*,
(select count(*) from WL_DEVICE where SITE_ID = a.SITE_ID and DEVICE_TYPE = 4) scount,
(select count(*) from WL_DEVICE where SITE_ID = a.SITE_ID and DEVICE_TYPE = 2) lcount,
(select count(*) from WL_DEVICE where SITE_ID = a.SITE_ID and DEVICE_TYPE = 1) ccount,
(select count(distinct ieee_address) from WL_SENSOR_LEAK_DATA where site_id = a.SITE_ID and receive_time like '2019-01-05%' and leak_percent between 0 and 79) pcount,
(select count(distinct ieee_address) from WL_SENSOR_LEAK_DATA where site_id = a.SITE_ID and receive_time like '2019-01-05%' and leak_percent between 80 and 100) rmiss,
(select (select count(*) from WL_DEVICE where SITE_ID = a.SITE_ID and DEVICE_TYPE = 4) - count(distinct ieee_address) from WL_SENSOR_LEAK_DATA where site_id = a.SITE_ID and receive_time like '2019-01-05%') dmiss
from(
  select distinct e.SIDO_NAME, d.SITE_NAME, a.SITE_ID, c.VERSION 
  from WL_DEVICE a
  left join WL_DEVICE_LOCATION b on a.SITE_ID = b.SITE_ID
  left join WL_DEVICE_ADDITONAL c on left(replace(ltrim(replace(a.IEEE_ADDRESS,'0',' ')),' ','0'),2) = c.IEEE_DIVISION and a.SITE_ID = c.SITE_ID
  left join WL_SITE d on a.SITE_ID = d.SITE_ID
  left join WL_SIDO e on d.SIDO_CODE = e.SIDO_ID
  where a.SITE_ID in (select SITE_ID from WL_SITE where VISIBLE = 'y' and LTE = 0)
  and a.DEVICE_TYPE = 1 and b.DEVICE_TYPE = 1
  and c.VERSION is not null
  order by e.SIDO_NAME, d.SITE_NAME desc
)a ,(SELECT @rownum :=2) as r
limit 5
 
아 위에 올린거중에 a.* 가 빠졌었네요.. ㅜ

 

아래와같은 테이블형태로 65로우까지 나오게되는 결과에요.. 지금 limit 걸어서 돌리니까 돌아가긴하는데... 엄청엄청오래걸려서 나오네요 .. 5건뿐인데두 ...;;

RNUM SIDO_NAME SITE_NAME SITE_ID VERSION scount lcount ccount pcount rmiss dmiss
3 강원도 동해시(계약140401_) 4217010100 구버전 548 93 4 133 9 406
4 경기도 하남시(계약170629_준공170920_AS만료190919) 4145010200 ver7이상 684 176 4 533 72 79
5 경상남도 함양군(계약170427_준공170627_AS만료190626) 4887025027 ver5~5.5 800 250 8 969 52 0
6 경상남도 통영시(계약140409_) 4822010600 ver5~5.5 595 80 3 547 34 14
7 경상남도 고성군(계약160623_준공161021_AS만료181020) 4882025027 ver5~5.5 1900 393 15 2150 80 0

by 우리집아찌 [2019.01.11 14:58:30]

컬럼 추가만 해서 느려지신거면 REORG 한번 해보세요.


by 마이닝 [2019.01.11 15:35:09]

아.. 컬럼추가한게 아니구요 ..제가 본문에 헷갈리게 작성했네요 ... 원래있던 컬럼이었는데 검색조건에 넣었더니 느려진거에요. WL_SENSOR_LEAK_DATA 의 데이터로우수가 너무많아서 찾는데 검색이 엄청오래걸리는거같아요..ㅜ 얼마나있는지 볼려고 count 했더니 결과가 안나오네요....;; 

REORG 는 ..  데이터베이스 설정에대해서... 공부가 많이부족합니다......;;


by 마농 [2019.01.11 15:27:10]

wl_device_additonal 의 기본키는?
ieee_address 의 값은 어떤 형태로 들어가나요?


by 마이닝 [2019.01.11 15:33:35]

wl_device_additonal 테이블의 기본키  : SITE_ID, IEEE_DIVISION  

ieee_address  의 값은 0000000000228018 이런식으로 들어가있어요


by 마농 [2019.01.11 15:48:27]

결과에 sido, site 에 대해 여러개의 version 이 붙게 되는 건가요?
아니면 sido, site 가 유일하게 하나만 나와야 하는 건가요?
version 은 site 와 관계가 없는 정보인 듯 합니다.
version 은 site 가 아닌 device 와 관계된 정보인 듯 합니다.
이게 왜 site 와 연결되어 결과로 보여져야 하는지 의문이네요?


by 마이닝 [2019.01.11 16:04:04]

음... sido, site 는 유일하게 하나만 붙게되요

사이트안에 디바이스들이 있는데 예를들어 0000000000228018 의 ieee_address   의값이있다면 앞에 0을없애고 22를 키로해서 wl_device_additonal 에있는 버전정보와 매칭하게되요

현재 한사이트에는 한버전의 디바이스들만 들어가있어요 그래서 해당사이트에 있는 디바이스들이 어떤버전을 쓰고있는지를 표시하기위해 두개를 보여주게 구성했습니다


by 마농 [2019.01.11 16:14:24]
SELECT b.sido_id
     , b.sido_name
     , a.site_id
     , a.site_name
     , c.version
     , d.scount
     , d.lcount
     , d.ccount
     , e.pcount
     , e.rmiss 
     , d.scount - e.cnt AS dmiss
  FROM wl_site a
 INNER JOIN wl_sido b
    ON a.sido_code = b.sido_id
 INNER JOIN
       (SELECT site_id
             , LEFT(REPLACE(LTRIM(REPLACE(ieee_address,'0',' ')),' ','0'),2) ieee_division
             , COUNT(CASE WHEN device_type = 4 THEN 1 END) scount
             , COUNT(CASE WHEN device_type = 2 THEN 1 END) lcount
             , COUNT(CASE WHEN device_type = 1 THEN 1 END) ccount
          FROM wl_device
         GROUP BY site_id
        ) d
    ON a.site_id = d.site_id
 INNER JOIN
       (SELECT site_id
             , COUNT(DISTINCT ieee_address) cnt
             , COUNT(DISTINCT CASE WHEN leak_percent BETWEEN  0 AND  79 THEN ieee_address END) pcount
             , COUNT(DISTINCT CASE WHEN leak_percent BETWEEN 80 AND 100 THEN ieee_address END) rmiss
          FROM wl_sensor_leak_data
         WHERE receive_time LIKE '2019-01-05%'
         GROUP BY site_id
        ) e
    ON a.site_id = e.site_id
 INNER JOIN wl_device_additonal c
    ON d.site_id       = c.site_id
   AND d.ieee_division = c.ieee_division
 WHERE a.visible = 'Y'
   AND a.lte     =  0
   AND c.version IS NOT NULL
;

 


by 마이닝 [2019.01.11 17:05:09]

감사합니다... 서브쿼리날리는 횟수가 훨씬 줄어든것같네요.

올려주신 쿼리이용해서 이쁘게 사용해볼게요. 감사합니다 ;;;

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입