Self-JOIN SQL 쿼리 성능 향상
MariaDB 10.1.18(Linux Debian Jesse)을 사용하여 SQL 쿼리의 성능을 개선하려고 합니다.
서버에는 대량의 RAM(192GB)과 SSD 디스크가 있습니다.
실제 테이블에는 수억 개의 행이 있지만 데이터 서브셋과 간단한 레이아웃으로 성능 문제를 재현할 수 있습니다.
(간소화된) 테이블 정의는 다음과 같습니다.
CREATE TABLE `data` (
`uri` varchar(255) NOT NULL,
`category` tinyint(4) NOT NULL,
`value` varchar(255) NOT NULL,
PRIMARY KEY (`uri`,`category`),
KEY `cvu` (`category`,`value`,`uri`),
KEY `cu` (`category`,`uri`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
컨텐츠의 실제 배포를 재현하기 위해서, 다음과 같은 약 200,000 행을 삽입합니다(배시 스크립트).
#!/bin/bash
for i in `seq 1 100000`;
do
mysql mydb -e "INSERT INTO data (uri, category, value) VALUES ('uri${i}', 1, 'foo');"
done
for i in `seq 99981 200000`;
do
mysql mydb -e "INSERT INTO data (uri, category, value) VALUES ('uri${i}', 2, '$(($i % 5))');"
done
그 때문에, 다음의 내용을 삽입합니다.
- 정적 문자열("foo")을 값으로 하는 카테고리1의 100,000 행
- 카테고리 2의 100,000 행에 1~5 사이의 숫자가 값으로 지정됩니다.
- 각 데이터 세트(카테고리 1/2) 간에 20개의 행이 공통의 「우리」를 가진다.
쿼리하기 전에 항상 Analyze TABLE을 실행합니다.
실행한 쿼리의 출력은 다음과 같습니다.
MariaDB [mydb]> EXPLAIN EXTENDED
-> SELECT d2.uri, d2.value
-> FROM data as d1
-> INNER JOIN data as d2 ON d1.uri = d2.uri AND d2.category = 2
-> WHERE d1.category = 1 and d1.value = 'foo';
+------+-------------+-------+--------+----------------+---------+---------+-------------------+-------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+--------+----------------+---------+---------+-------------------+-------+----------+-------------+
| 1 | SIMPLE | d1 | ref | PRIMARY,cvu,cu | cu | 1 | const | 92964 | 100.00 | Using where |
| 1 | SIMPLE | d2 | eq_ref | PRIMARY,cvu,cu | PRIMARY | 768 | mydb.d1.uri,const | 1 | 100.00 | |
+------+-------------+-------+--------+----------------+---------+---------+-------------------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
MariaDB [mydb]> SHOW WARNINGS;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `mydb`.`d2`.`uri` AS `uri`,`mydb`.`d2`.`value` AS `value` from `mydb`.`data` `d1` join `mydb`.`data` `d2` where ((`mydb`.`d1`.`category` = 1) and (`mydb`.`d2`.`uri` = `mydb`.`d1`.`uri`) and (`mydb`.`d2`.`category` = 2) and (`mydb`.`d1`.`value` = 'foo')) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [mydb]> SELECT d2.uri, d2.value FROM data as d1 INNER JOIN data as d2 ON d1.uri = d2.uri AND d2.category = 2 WHERE d1.category = 1 and d1.value = 'foo';
+-----------+-------+
| uri | value |
+-----------+-------+
| uri100000 | 0 |
| uri99981 | 1 |
| uri99982 | 2 |
| uri99983 | 3 |
| uri99984 | 4 |
| uri99985 | 0 |
| uri99986 | 1 |
| uri99987 | 2 |
| uri99988 | 3 |
| uri99989 | 4 |
| uri99990 | 0 |
| uri99991 | 1 |
| uri99992 | 2 |
| uri99993 | 3 |
| uri99994 | 4 |
| uri99995 | 0 |
| uri99996 | 1 |
| uri99997 | 2 |
| uri99998 | 3 |
| uri99999 | 4 |
+-----------+-------+
20 rows in set (0.35 sec)
이 쿼리는 최대 350ms의 20 행을 반환합니다.
내가 보기엔 꽤 느린 것 같아요.
이러한 질의의 성능을 개선할 수 있는 방법이 있습니까?조언 좀 해주시겠어요?
다음 질문을 시도해 볼 수 있습니까?
SELECT dd.uri, max(case when dd.category=2 then dd.value end) v2
FROM data as dd
GROUP by 1
having max(case when dd.category=1 then dd.value end)='foo' and v2 is not null;
현재 당신의 테스트를 반복할 수는 없지만, 저는 테이블을 한 번만 스캔하는 것이 집약 기능의 사용을 보상할 수 있기를 바랍니다.
편집필
테스트 환경을 만들고 몇 가지 가설을 테스트했습니다.현재 최고의 퍼포먼스(100만 행)는 다음과 같습니다.
1 - uri 컬럼에 인덱스 추가
2 - 다음 질문 사용
select d2.uri, d2.value
FROM data as d2
where exists (select 1
from data d1
where d1.uri = d2.uri
AND d1.category = 1
and d1.value='foo')
and d2.category=2
and d2.uri in (select uri from data group by 1 having count(*) > 1);
아이러니한 것은 첫 번째 제안에서는 테이블에 대한 접근을 최소화하려고 노력했지만 지금은 세 가지 접근을 제안하고 있다는 것입니다.
편집일 : 10/30
자, 다른 실험을 몇 가지 해봤습니다. 결과를 요약해 보겠습니다.먼저 Aruna의 답변을 조금 더 자세히 설명하겠습니다.OP 질문에서 흥미로운 점은 데이터베이스 최적화에서 기존의 "경험의 법칙"의 예외라는 것입니다. 원하는 결과의 수가 관련된 테이블의 치수에 비해 매우 작다면 올바른 인덱스로 매우 뛰어난 성능을 얻을 수 있다는 것입니다.
"매직 인덱스"를 추가하여 20개의 행을 지정할 수 없는 이유는 무엇입니까?확실한 "공격 벡터"가 없기 때문에..제 말은, 기록에 적용할 수 있는 명확한 선택 기준은 없습니다. 목표 행의 수를 크게 줄일 수 있습니다.
생각해 보십시오. 값이 "foo"여야 한다는 사실은 방정식에서 표의 50%를 제거하는 것입니다.또한 카테고리는 전혀 선택적이지 않습니다.유일한 관심사는 20URI의 경우 카테고리1과 2의 레코드에 모두 표시됩니다.
하지만 여기서 문제가 발생합니다. 이 조건은 2개의 행을 비교하는 것입니다.제가 아는 바로는 인덱스(Oracle 함수 기반 인덱스도 포함)가 여러 행의 정보에 의존하는 조건을 줄일 수 있는 방법은 없습니다.
결론적으로, 이러한 종류의 쿼리가 필요한 경우 데이터 모델을 수정해야 합니다.예를 들어, 범주 수가 유한하고 적은 경우(예: 3=) 표는 다음과 같이 작성될 수 있습니다.
uri, value_category1, value_category2, value_category3
쿼리는 다음과 같습니다.
uri, value_category2를 선택합니다. 여기서 value_category1='foo' 및 value_category2는 null이 아닙니다.
그나저나 원래 질문으로 돌아가 봅시다.조금 더 효율적인 테스트 데이터 생성기(http://pastebin.com/DP8Uaj2t)를 만들었습니다.
이 표를 사용해 왔습니다.
use mydb;
DROP TABLE IF EXISTS data2;
CREATE TABLE data2
(
uri varchar(255) NOT NULL,
category tinyint(4) NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (uri,category),
KEY cvu (category,value,uri),
KEY ucv (uri,category,value),
KEY u (uri),
KEY cu (category,uri)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
결과는 다음과 같습니다.
+--------------------------+----------+----------+----------+
| query_descr | num_rows | num | num_test |
+--------------------------+----------+----------+----------+
| exists_plus_perimeter | 10000 | 0.0000 | 5 |
| exists_plus_perimeter | 50000 | 0.0000 | 5 |
| exists_plus_perimeter | 100000 | 0.0000 | 5 |
| exists_plus_perimeter | 500000 | 2.0000 | 5 |
| exists_plus_perimeter | 1000000 | 4.8000 | 5 |
| exists_plus_perimeter | 5000000 | 26.7500 | 8 |
| max_based | 10000 | 0.0000 | 5 |
| max_based | 50000 | 0.0000 | 5 |
| max_based | 100000 | 0.0000 | 5 |
| max_based | 500000 | 3.2000 | 5 |
| max_based | 1000000 | 7.0000 | 5 |
| max_based | 5000000 | 49.5000 | 8 |
| max_based_with_ucv | 10000 | 0.0000 | 5 |
| max_based_with_ucv | 50000 | 0.0000 | 5 |
| max_based_with_ucv | 100000 | 0.0000 | 5 |
| max_based_with_ucv | 500000 | 2.6000 | 5 |
| max_based_with_ucv | 1000000 | 7.0000 | 5 |
| max_based_with_ucv | 5000000 | 36.3750 | 8 |
| standard_join | 10000 | 0.0000 | 5 |
| standard_join | 50000 | 0.4000 | 5 |
| standard_join | 100000 | 2.4000 | 5 |
| standard_join | 500000 | 13.4000 | 5 |
| standard_join | 1000000 | 33.2000 | 5 |
| standard_join | 5000000 | 205.2500 | 8 |
| standard_join_plus_perim | 5000000 | 155.0000 | 2 |
+--------------------------+----------+----------+----------+
사용되는 쿼리는 다음과 같습니다. - query_max_based_with_ucv.sql
- query_syslog_plus_syslogs.sql
- query_max_based.sql
- query_max_based_with_ucv.sql
- query_standard_param_plus_perim.sql query_standard_sql.sql
최적의 쿼리는 첫 번째 환경 작성 후에 입력한 "query_exists_plus_perimeter"입니다.
이것은 주로 분석된 행의 수 때문이다.색인화된 테이블이 있더라도 주요 의사결정 조건 "WHERE d1.category = 1 and d1.value = 'foo'"는 대량의 행을 필터링합니다.
+------+-------------+-------+-.....-+-------+----------+-------------+
| id | select_type | table | | rows | filtered | Extra |
+------+-------------+-------+-.....-+-------+----------+-------------+
| 1 | SIMPLE | d1 | ..... | 92964 | 100.00 | Using where |
일치하는 각 행은 카테고리 2의 표를 다시 읽어야 합니다.프라이머리 키를 읽고 있기 때문에 일치하는 행을 직접 얻을 수 있습니다.
원래 테이블에서 카테고리와 값의 조합의 카디널리티를 확인합니다.고유성이 높은 경우 인덱스(카테고리, 값)를 추가하면 성능이 향상됩니다.제시된 예시와 동일한 경우 성능이 향상되지 않을 수 있습니다.
언급URL : https://stackoverflow.com/questions/40222891/improve-self-join-sql-query-performance
'programing' 카테고리의 다른 글
| Retrofit 2.0에서 인터셉터를 사용하여 헤더를 추가하는 방법 (0) | 2022.11.01 |
|---|---|
| Java에서의 유사성 문자열 비교 (0) | 2022.11.01 |
| 롬복의 슈퍼 컨스트럭터에 전화하는 방법 (0) | 2022.11.01 |
| 스트림에서 Collections.toMap()을 사용할 때 목록의 반복 순서를 유지하려면 어떻게 해야 합니까? (0) | 2022.11.01 |
| PHP에서 ++$i와 $i++의 차이점은 무엇입니까? (0) | 2022.11.01 |