안녕하세요 제목처럼 쿼리속도문제로 질문드립니다.. 원래 마리아db에서 아래와같이 쿼리를 써서 쓰고있었는데 (PARTITION BY 를 사용하여) 이떄는 문제없이 잘돌아가는 쿼리였어요 근데 최근 db종류가 mysql로 변경되면서 PARTITION BY 를 사용하지못하게됬습니다. SELECT sid, receive_time, forward_total, flow_press, meter_id, crt_dt FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY sid ORDER BY receive_time DESC) AS RN FROM kw_meter_data WHERE site_id = '4690025021' and receive_date = '20200203' )a WHERE a.RN = 1 그래서 아래와같이 쿼리를 변경해보았는데.. 결과값은 동일하게나오나 속도가 느려서 도저히 사용을하지를 못하는상황입니다. SELECT sid, receive_time, forward_total, flow_press, meter_id, crt_dt FROM kw_meter_data WHERE site_id = '4690025021' and receive_date = '20200203' AND (site_id,sid,receive_time) in (SELECT site_id,sid,max(receive_time) FROM kw_meter_data WHERE site_id = '4690025021' and receive_date = '20200203' GROUP BY site_id,sid)
SELECT sid, receive_time, forward_total, flow_press, meter_id, crt_dt FROM kw_meter_data WHERE site_id = '4690025021' and receive_date = '20200203'
까지만 검색을하면 85792 건정도가 나오구요..
최종결과값은 121건정도나옵니다.
쿼리 응답속도를 더빠르게하려면 어떻게 해야 될까요..
db에서 단일쿼리검색으로 하면 금방나오지만 실제 웹에서는 500개정도의 사이트가있고 사이트별로 모두각자 계산되어야하기에 속도가 느린것같은데..
PARTITION BY 를사용했을때는 1~2초안에 페이지가 나오고 , 위쿼리를쓰면 5분이 지나도 나오지않는 이유가 뭘까요 ... ㅜ
검색하는 테이블 정보는 아래와 같습니다. 도움부탁드립니다 ㅜ
CREATE TABLE `kw_meter_data` ( `mid` bigint(20) NOT NULL AUTO_INCREMENT, `site_id` varchar(20) NOT NULL, `sid` varchar(20) NOT NULL, `meter_id` varchar(20) NOT NULL, `receive_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `receive_date` char(8) DEFAULT NULL, `meter_type` int(11) DEFAULT '0', `flow_volume` decimal(12,5) DEFAULT '0.00000', `flow_velocity` decimal(12,5) DEFAULT '0.00000', `flow_percent` decimal(12,5) DEFAULT '0.00000', `flow_mtp` decimal(12,5) DEFAULT '0.00000', `flow_press` decimal(12,5) DEFAULT '0.00000', `forward_total` decimal(12,5) DEFAULT '0.00000', `reverse_total` decimal(12,5) DEFAULT '0.00000', `meter_stat` int(11) DEFAULT '0', `battery_stat` int(11) DEFAULT '0', `meter_err` varchar(10) DEFAULT NULL, `control_stat` int(11) DEFAULT '0', `crt_dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`mid`), KEY `kw_meter_data_index` (`site_id`,`sid`,`receive_time`), KEY `kw_meter_data_index2` (`site_id`,`meter_id`,`receive_time`), KEY `kw_meter_data_index3` (`site_id`,`receive_time`), KEY `kw_meter_data_index4` (`site_id`,`receive_date`), KEY `kw_meter_data_index5` (`receive_date`) ) ENGINE=InnoDB AUTO_INCREMENT=1220046647 DEFAULT CHARSET=utf8;
메인 쿼리 조건 receive_date 조건 때문에 _index4 를 탄 게 아닐까요?
메인 쿼리 조건 receive_date 조건을 빼면 _index 를 탈 것 같습니다.
아니면 아예 IN 서브쿼리가 아닌 조인으로 바꿔 보세요.
그리고, 쓸데 없이 인덱스가 많은 듯 하네요.
_index4 에 항목을 추가하면 좋을 듯 합니다.
(site_id, receive_date, sid, receive_time)
이걸로 커버가 가능하면 _index3 이나 _index 는 없어도 되지 않을까? 생각됩니다.
또한 receive_date 항목이 불필요하게 추가되어 있네요.
receive_time 에서 receive_date 추출이 가능한데요.
조건은 receive_date 로 주고 MAX 는 receive_time 을 가져오면 인덱스를 효율적으로 이용하지 못합니다.
조건을 아예 receive_time 에 줘야 효율적인 쿼리가 됩니다.
SELECT b.sid , b.receive_time , b.forward_total , b.flow_press , b.meter_id , b.crt_dt FROM (SELECT site_id , sid , MAX(receive_time) receive_time FROM kw_meter_data WHERE site_id = '4690025021' and receive_date = '20200203' GROUP BY site_id, sid ) a INNER JOIN kw_meter_data b ON a.site_id = b.site_id AND a.sid = b.sid AND a.receive_time = b.receive_time ;