database.sarang.net
UserID
Passwd
Database
DBMS
ㆍMySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MySQL Q&A 31021 게시물 읽기
No. 31021
쿼리 튜닝 요청
작성자
황성균(sofang)
작성일
2017-01-23 16:50:59
조회수
2,758

안녕하세요.

아래와 같은 쿼리가 있습니다.

SELECT ipv4.ipaddress AS ipaddress, ipv4.ipaddress_label AS ipaddressLabel,

IF(ipv4.alias_name IS NULL OR ipv4.alias_name = '', ipv4.hostname, alias_name) AS hostname,

bytes, packets,

(bytes * 8 / TIMESTAMPDIFF(SECOND, '2017-01-11 12:00:00', '2017-01-12 00:00:00')) AS bps,

(packets / TIMESTAMPDIFF(SECOND, '2017-01-11 12:00:00', '2017-01-12 00:00:00')) AS pps

FROM (SELECT dst_ipv4 ipaddress,

SUM(octets) AS bytes, SUM(packets) AS packets

FROM tms_flow_packets t

WHERE check_datetime >= '2017-01-11 12:00:00'

AND check_datetime < '2017-01-12 00:00:00'

AND t.device_ip = 3232261378

AND t.protocol = 6

GROUP BY dst_ipv4

ORDER BY NULL

) grp

INNER JOIN tms_ipv4_table ipv4 ON ipv4.ipaddress = grp.ipaddress

ORDER BY bytes DESC

LIMIT 0, 5;

 

전체 시간이 12초 정도 걸리는데,

sub쿼리 수행하는데 거의다 시간이 걸립니다.

큰 범위의 데이터를 가지고 와야해서 적절한 index를 사용하기가 어려운 상황인데,

최종적으로는 5건만 출력하면 되는데, 혹시 해당 조건의 전체 데이터를 sum 하지 않고,

다른 방법으로 sum 상위 5건만 가져오는 방법이 있는지, 다른 좋은 방법이 있는지

고수님들의 조언 부탁드립니다.

 

참고로, MySql은 최신 버전을 사용하고 있습니다.

 

테이블 데이터 건수는 아래와 같습니다.

SELECT COUNT(*), COUNT(DISTINCT check_datetime) check_datetime, COUNT(DISTINCT DATE_FORMAT(check_datetime,'%Y-%m-%d')) check_date,

COUNT(DISTINCT device_ip) device_ip, COUNT(DISTINCT src_ipv4) src_ipv4,

COUNT(DISTINCT dst_ipv4) dst_ipv4, COUNT(DISTINCT src_port) src_port, COUNT(DISTINCT dst_port) dst_port, COUNT(DISTINCT protocol) protocol

FROM tms_flow_packets

 

COUNT(*) check_datetime check_date device_ip src_ipv4 dst_ipv4 src_port dst_port protocol

61726108 119458 3 6 53048 58302 63871 58563 6

 

테이블 인덱스 정보

Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type

TMS_FLOW_PACKET_IX2 1 check_datetime A 184684 BTREE

TMS_FLOW_PACKET_IX3 1 check_time A 2887 BTREE

TMS_FLOW_PACKET_IX4 1 src_ipv4 A 4702347 YES BTREE

TMS_FLOW_PACKET_IX5 1 dst_ipv4 A 4702347 YES BTREE

TMS_FLOW_PACKET_IX6 1 src_ipv6 A 824 YES BTREE

TMS_FLOW_PACKET_IX7 1 dst_ipv6 A 824 YES BTREE

TMS_FLOW_PACKET_IX8 1 src_port A 10188420 YES BTREE

TMS_FLOW_PACKET_IX9 1 dst_port A 8732931 YES BTREE

TMS_FLOW_PACKET_IX10 1 src_as A 206 YES BTREE

TMS_FLOW_PACKET_IX11 1 dst_as A 206 YES BTREE

TMS_FLOW_PACKET_IX12 1 protocol A 1031 BTREE

TMS_FLOW_PACKET_IX13 1 tcp_flags A 2062 YES BTREE

TMS_FLOW_PACKET_IX14 1 tos A 1237 YES BTREE

TMS_FLOW_PACKET_IX15 1 l7_protocol A 206 YES BTREE

TMS_FLOW_PACKET_IX1 1 check_datetime A 185807 BTREE

TMS_FLOW_PACKET_IX1 2 device_ip A 545808 BTREE

TMS_FLOW_PACKET_IX1 3 output A 1328924 BTREE

TMS_FLOW_PACKET_IX1 4 input A 2657848 BTREE

 

 

 

plan 정보

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY ALL \N \N \N \N 6345736 Using where; Using filesort

1 PRIMARY ipv4 eq_ref PRIMARY PRIMARY 4 grp.ipaddress 1 \N

2 DERIVED t ref TMS_FLOW_PACKET_IX2,TMS_FLOW_PACKET_IX5,TMS_FLOW_PACKET_IX12,TMS_FLOW_PACKET_IX1 TMS_FLOW_PACKET_IX12 1 const 6345736 Using where; Using temporary

이 글에 대한 댓글이 총 6건 있습니다.

인덱스가 컬럼별로 다 따로 따로 네요.
컬럼들을 결합하여 결합인덱스를 만드셔야 합니다.
 - 기본형 : (device_ip, protocol, check_datetime)
 - 확장형 : (device_ip, protocol, check_datetime, dst_ipv4, octets, packets)


서브 쿼리 내 불필요한 ORDER BY 절은 빼세요.
  - 아니면 ORDER BY bytes DESC 를 서버쿼리 안으로 넣으면서
  - LIMIT 구문도 함께 안쪽으로 넣으세요.

마농(manon94)님이 2017-01-23 17:58:17에 작성한 댓글입니다.

네. 답변 감사드립니다.

추가 질문이 있습니다.

1. 이미 기존에 많은 인덱스로 인해, insert 할때 부하가 있어서 추가적인 인덱스를 만들기가 어려운 상황 입니다.

2. subquery의 order by null 절은 정렬을 하지 않음으로써 시간을 줄이기 위해 일부러 넣은 것인데, 제가 잘못 알고 있는 건가요?

3.order by 와 limit 구문을 안쪽에 넣으면 밖에 있는 tms_flow_packets 테이블과 join이 되지 않은 상태에서 limit 를 해버리는 문제가 있어서, 안으로 넣지 못했어요.

이런 상태에서 더 좋은 방법이 있을까요?

 

황성균(sofang)님이 2017-01-23 21:00:00에 작성한 댓글입니다.

limit을 안쪽으로 넣으시는게 불가하시면 subquery에 있는 order by는 join을 하기 때문에 불필요합니다.

김주왕(kimjuking)님이 2017-01-24 08:42:26에 작성한 댓글입니다.

1. 기존 인덱스들이 불필요하게 많이 생성된 듯 합니다.
  - 불필요한 인덱스들을 지우고 꼭 필요한 인덱스를 만드세요.
2. 정렬을 하지 않으려면
  - 정렬구문을 빼야죠
  - 정렬구문을 억지로 넣을 필요가 없죠.
3. 조인 으로 인해 결과 건수가 달라지나요?
  - 그게 아니라면? LIMIT 를 안으로 넣는게 맞습니다.

마농(manon94)님이 2017-01-24 09:26:30에 작성한 댓글입니다.

네. 답변 감사드립니다.

ORDER BY 절에 대해서 계속 궁금해서 질문드립니다.

GROUP BY를 하면서 정렬이 기본적으로 발생한다고 알고 있습니다. 이 부분을 ORDER BY NULL 로써, 정렬을 하지않도록 하려고 한건데, 제가 잘못 알고 있나요?

ORDER BY NULL 절을 넣었을때와 뺐을때 PLAN 정보를 보면,

뺐을때,

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY ALL \N \N \N \N 2 Using where; Using filesort

1 PRIMARY ipv4 eq_ref PRIMARY PRIMARY 4 grp.ipaddress 1 \N

2 DERIVED t ref TMS_FLOW_PACKET_IX1,TMS_FLOW_PACKET_IX2,TMS_FLOW_PACKET_IX5,TMS_FLOW_PACKET_IX12 TMS_FLOW_PACKET_IX1 4 const 1 Using where; Using temporary; Using filesort

넣었을때,

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY ALL \N \N \N \N 2 Using where; Using filesort

1 PRIMARY ipv4 eq_ref PRIMARY PRIMARY 4 grp.ipaddress 1 \N

2 DERIVED t ref TMS_FLOW_PACKET_IX1,TMS_FLOW_PACKET_IX2,TMS_FLOW_PACKET_IX5,TMS_FLOW_PACKET_IX12 TMS_FLOW_PACKET_IX1 4 const 1 Using where; Using temporary

 

Using filesort 부분이 차이가 납니다. 제가 잘못 이해하고 있는 부분 있으시면 가이드 주시기 바랍니다. 감사합니다..

황성균(sofang)님이 2017-01-24 12:07:13에 작성한 댓글입니다.

아...
ORDER BY NULL 에 그런 기능이 있었군요.
그룹바이 소팅 방지 기능.

마농(manon94)님이 2017-01-24 13:40:23에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
31025저장 프로시저 관련 문의입니다.
김태호
2017-02-17
1964
31023root가 아닌계정이 모든정보를 볼수 있는방법이 있을까요 ?
이상준
2017-01-24
2688
31022mysql 쿼리 확인 요청드립니다.
홍준희
2017-01-24
2696
31021쿼리 튜닝 요청 [6]
황성균
2017-01-23
2758
31020나이대 구하기 [2]
궁그미
2017-01-20
2314
31018대용량 테이블에서 count 하기 [2]
오도훈
2017-01-06
2608
31017FULLTEXT 검색 관련 문의 드립니다.
임재송
2017-01-06
1944
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2017 DSN, All rights reserved.
작업시간: 0.068초, 이곳 서비스는
	PostgreSQL v9.6.3으로 자료를 관리합니다