use-the-index-luke 저자 Markus Winand
---------------------------------------------
Markus Winand는 SQL에 대한 통찰력을 제공하고 다양한 시스템이 SQL을 지원하는 방법을 modern-sql.com 에서 보여줍니다. 이전에 그는 use-the-index-luke.com 을 만들었는데, 지금도 활발하게 유지되고 있습니다. Markus는 winand.at 를 통해 강사, 연사 및 컨설턴트로 고용될 수 있습니다.
---------------------------------------------
You can upload a Korean translation of use-the-index-luke.com on your blog
Thank you from the bottom of my heart to author Makus Winand for allowing me.
These are translations that I use for studying by using a papago (google translate)
The translations may not be correct or there may be a typo.
I'd appreciate it if you could point it out in the comments.
---------------------------------------------
---------------------------------------------
use-the-index-luke.com 의 한글번역본을 블로그에 업로드 해도 된다고
허락해주신 Makus Winand 저자님께 진심으로 감사합니다.
이 번역본들은 제가 공부용도로 번역기(papago, google transrate)를 돌려서
번역한 내용들이라 맞지 않거나, 오타가 있을수 있습니다.
댓글로 지적해주시면 감사하겠습니다.
---------------------------------------------
https://use-the-index-luke.com/sql/join/hash-join-partial-objects
ㄴ 해시 조인
해시 조인 알고리즘은 중첩된 루프 조인의 약점인 내부 쿼리 실행 시 많은 B-Tree 순회를 목표로 합니다.
대신 조인의 한 쪽에서 후보 레코드를 조인의 다른 쪽에서 각 행에 대해 매우 빠르게 검색할 수 있는 해시 테이블로 로드합니다.
해시 조인을 조정하려면 Nested Loop 조인과 완전히 다른 인덱싱 접근 방식이 필요합니다.
또한 대부분의 ORM 도구에서 해결해야 할 과제인 열을 적게 선택하여 해시 조인 성능을 향상시킬 수도 있습니다.
해시 조인에 대한 인덱싱 전략은 조인 열을 인덱싱할 필요가 없기 때문에 매우 다릅니다. predicate가 해시 조인 성능을 향상시키는 독립형 인덱스만 있습니다.
----------------------------------------------------------
Tip
해시 조인 성능을 향상시키기 위해 Predicate를 독립적으로 인덱싱 합니다.
----------------------------------------------------------
다음 예를 생각해 보십시오. 지난 6개월 동안의 모든 매출과 해당 직원 세부 정보를 선택합니다.
----------------------------------------------------------
SELECT *
FROM sales s
JOIN employees e ON (s.subsidiary_id = e.subsidiary_id
AND s.employee_id = e.employee_id )
WHERE s.sale_date > trunc(sysdate) - INTERVAL '6' MONTH
----------------------------------------------------------
SALE_DATE 필터는 독립적인 유일한 where 절입니다. 즉, 하나의 테이블만 참조하고 Join predicate에 속하지 않습니다.
----------------------------------------------------------
--------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49244 | 59M| 12049|
|* 1 | HASH JOIN | | 49244 | 59M| 12049|
| 2 | TABLE ACCESS FULL| EMPLOYEES | 10000 | 9M| 478|
|* 3 | TABLE ACCESS FULL| SALES | 49244 | 10M| 10521|
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("S"."SUBSIDIARY_ID"="E"."SUBSIDIARY_ID"
AND "S"."EMPLOYEE_ID" ="E"."EMPLOYEE_ID")
3 - filter("S"."SALE_DATE">TRUNC(SYSDATE@!)
-INTERVAL'+00-06' YEAR(2) TO MONTH)
----------------------------------------------------------
첫 번째 실행 단계는 전체 테이블 검색으로 모든 직원을 해시 테이블(계획 ID 2)에 로드하는 것입니다. 해시 테이블은 Join Predicate를 키로 사용합니다. 다음 단계에서 데이터베이스는 SALE 테이블에 대해 전체 테이블 스캔을 다시 수행하고 SALE_DATE(계획 Id 3)의 조건을 충족하지 않는 모든 판매를 삭제합니다.
나머지 SALES 레코드의 경우 데이터베이스는 해시 테이블에 액세스하여 해당 직원 상세 내역을 로드합니다.
해시 테이블의 유일한 목적은 임시 메모리 구조로 작동하여 EMPLOYEES 테이블에 여러 번 액세스하지 않도록 하는 것입니다. 해시 테이블은 처음에는 단일 레코드를 효율적으로 가여오기 위해 인덱스가 필요하지않도록 한 번에 로드됩니다. Predicate 정보는 EMPLOYEES 테이블 (계획 ID 2)에 필터가 하나도 적용되지 않았음을 확인합니다. 쿼리에 이 테이블에 독립적인 Predicate가 없습니다.
----------------------------------------------------------
Important
Join Predicate를 인덱싱해도 해시 조인 성능이 향상되지 않습니다.
----------------------------------------------------------
그렇다고 해서 해시 조인을 인덱싱할 수 없는 것은 아닙니다. 독립 Predicate는 index화 할 수 있습니다. 두 가지 테이블 액세스 작업 중 하나에 적용되는 조건입니다. 위의 예에서 SALE_DATE의 필터입니다.
----------------------------------------------------------
CREATE INDEX sales_date ON sales (sale_date)
----------------------------------------------------------
다음 실행 계획에서는 이 인덱스를 사용합니다.
그럼에도 불구하고 쿼리에는 EMPLOYEES 테이블에 대한 전체 테이블 검색이 사용됩니다. 이는 쿼리에 EMPLOYEES에 대한 독립적인 위치 Predicate가 없기 때문입니다.
----------------------------------------------------------
--------------------------------------------------------------
| Id | Operation | Name | Bytes| Cost|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 59M| 3252|
|* 1 | HASH JOIN | | 59M| 3252|
| 2 | TABLE ACCESS FULL | EMPLOYEES | 9M| 478|
| 3 | TABLE ACCESS BY INDEX ROWID| SALES | 10M| 1724|
|* 4 | INDEX RANGE SCAN | SALES_DATE| | |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("S"."SUBSIDIARY_ID"="E"."SUBSIDIARY_ID"
AND "S"."EMPLOYEE_ID" ="E"."EMPLOYEE_ID" )
4 - access("S"."SALE_DATE" > TRUNC(SYSDATE@!)
-INTERVAL'+00-06' YEAR(2) TO MONTH)
----------------------------------------------------------
해시 조인 인덱싱은 중첩된 루프 조인과 반대로 대칭입니다. 즉, 조인 수서는 인덱싱에 영향을 주지 않습니다.
SALES_DATE 인덱스는 조인 순서가 반대인 경우 해시 테이블을 로느하는 데 사용할 수 있습니다.
----------------------------------------------------------
Note
해시 조인 인덱싱은 조인 순서와 무관합니다.
----------------------------------------------------------
해시 조인 성능을 최적화하는 다른 방법은 해시 테이블 크기를 최소화하는 것입니다.
이 방법은 전체 해시 테이블이 메모리에 맞는 경우에만 최적의 해시 조인이 가능하기 때문에 작동합니다. 따라서 최적화 도구는 자동으로 해시 테이블에 대한 조인의 작은 쪽을 사용합니다. Oracle 실행 계획은 "바이트" 열에 예상 메모리 요구 사항을 표시합니다. 위의 실행 계획에서 EMPLOYEES 테이블은 9MB가 필요하므로 더 작은 테이블입니다.
SQL 쿼리를 변경하여 해시 테이블 크기를 줄일 수도 있습니다. 예를 들어 데이터베이스가 해시 테이블에 더 적은 수의 후보 레코드를 로드하도록 추가 조건을 추가하는 방법도 있습니다. 위의 예를 계속하면 DEPARTMENT 속성에 필터를 추가하여 영업 직원만 고려됩니다. 데이터베이스는 해시 테이블에 매출을 가질 수 없는 직원을 저장할 필요가 없기 때문에 DEPARTMENT 속성에 인덱스가 없더라고 해시 조인 성능이 향상됩니다. 그렇게 할 때는 해당 부서에서 근무하지 않는 직원의 판매 기록이 없는지 확인해야 합니다. 제약 조건을 사용하여 가정을 보호합니다.
해시 테이블 크기를 최소화할 때 관련 요인은 행 수가 아니라 메모리 공간입니다.
실제로 필요한 속성만 선택하여 더 적은 수의 열을 선택하여 해시 테이블 크기를 줄일 수도 있습니다.
----------------------------------------------------------
SELECT s.sale_date, s.eur_value
, e.last_name, e.first_name
FROM sales s
JOIN employees e ON (s.subsidiary_id = e.subsidiary_id
AND s.employee_id = e.employee_id )
WHERE s.sale_date > trunc(sysdate) - INTERVAL '6' MONTH
----------------------------------------------------------
잘못된 열을 삭제하면 오류 메시지가 빠르게 표시되기 때문에 버그가 거의 발생하지 않습니다. 그럼에도 불구하고 해시 테이블 크기를 9MB에서 234KB로 크게 줄일 수 있습니다. 이 경우 97%가 절감됩니다.
----------------------------------------------------------
--------------------------------------------------------------
| Id | Operation | Name | Bytes| Cost|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2067K| 2202|
|* 1 | HASH JOIN | | 2067K| 2202|
| 2 | TABLE ACCESS FULL | EMPLOYEES | 234K| 478|
| 3 | TABLE ACCESS BY INDEX ROWID| SALES | 913K| 1724|
|* 4 | INDEX RANGE SCAN | SALES_DATE| | 133|
--------------------------------------------------------------
----------------------------------------------------------
----------------------------------------------------------
Tip
해시 조인 성능을 향상시켜려면 열을 더 적게 선택합니다.
----------------------------------------------------------
언뜻 보기에는 SQL문에서 몇 개의 열을 제거하는 것이 간단해 보이지만
ORM(Object-Relational Mapping) 도구를 사용하는 경우에는 매우 어려운 문제입니다. 소위 부분 객체에 대한 지원은 매우 희박합니다. 다음 예에서는 몇 가 가능성을 보여 줍니다.
----------------------------------------------------------
JPA defines the FetchType.LAZY in the @Basic annotation. It can be applied on property level:
@Column(name="junk")
@Basic(fetch=FetchType.LAZY)
private String junk;
JPA providers are free to ignore it:
The LAZY strategy is a hint to the persistence provider runtime that data should be fetched lazily when it is first accessed. The implementation is permitted to eagerly fetch data for which the LAZY strategy hint has been specified.
— EJB 3.0 JPA, paragraph 9.1.18
Hibernate 3.6 implements lazy property fetching via compile time bytecode instrumentation. The instrumentation adds extra code to the compiled classes that does not fetch the LAZY properties until accessed. The approach is fully transparent to the application but it opens the door to a new dimension of N+1 problems: one select for each record and property. This is particularly dangerous because JPA does not offer runtime control to fetch eagerly if needed.
Hibernate’s native query language HQL solves the problem with the FETCH ALL PROPERTIES clause (see FewerColumnsInstrumentedHibernate.java):
select s from Sales s FETCH ALL PROPERTIES
inner join fetch s.employee e FETCH ALL PROPERTIES
where s.saleDate >:dt
The FETCH ALL PROPERTIES clause forces Hibernate to eagerly fetch the entity—even when using instrumented code and the LAZY annotation.
Another option for loading only selected columns is to use data transport objects (DTOs) instead of entities. This method works the same way in HQL and JPQL, that is you initialize an object in the query (FewerColumnsJPA.java sample):
select new SalesHeadDTO(s.saleDate , s.eurValue
,e.firstName, e.lastName)
from Sales s
join s.employee e
where s.saleDate > :dt
The query selects the requested data only and returns a SalesHeadDTO object—a simple Java object (POJO), not an entity.
Solving a real world performance problem does often involve a lot of existing code. Migrating that code to new classes is probably unreasonable. But byte-code instrumentation causes N+1 problems, which is likely worse than the original performance issue. The FewerColumnsJPA.java example uses a common interface for the entity and the DTO to solve the problem. The interface defines the getter methods only so that a read-only consumer can easily be changed to accept the the DTO as input. That is often sufficient because large hash joins are usually triggered by reporting procedures that do not update anything.
If you are building a new report, you could consider fetching the data via DTOs or a simple Map, like demonstrated in the FewerColumnsHibernate.java sample.
----------------------------------------------------------
----------------------------------------------------------
경고
MySQL은 2019년 8.0.18 버전 이후로 해시 조인을 도입하였습니다.
----------------------------------------------------------
----------------------------------------------------------
Factbox
* 해시 조인에는 조인 Predicate의 인덱스가 필요하지 않습니다. 대신 해시 테이블을 사용합니다.
* 해시 조인은 인덱스가 독립적인 Predicate를 지원하는 경우에만 인덱스를 사용합니다.
* 해시 테이블 크기를 줄여 성능을 향상시킵니다. 수평(row 수 감소) 또는 수직(열 수 감소)
* 해시 조인은 조인 Predicate에 범위 조건이 있는 조인(세타 조인)을 수행할 수 없습니다.
----------------------------------------------------------
----------------------------------------------------------
----------------------------------------------------------
'use-the-index-luke' 카테고리의 다른 글
| 5장 Clustering Data (1) | 2023.10.06 |
|---|---|
| 4.3 Sort-Merge Join (1) | 2023.10.02 |
| 4.1 Nested Loops (0) | 2023.09.25 |
| 4장 The Join Operation (0) | 2023.09.21 |
| 3.3 Response Time and Throughput (0) | 2023.09.18 |
