안녕하세요.
아래와 같은 쿼리가 있습니다.
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
|