Semi Join
조인은 아니지만 조인과 같이 사용하는 준조인
1. Table Pullout
서브쿼리를 조인으로 바꾼다.
다른 최적화도 사용할 수 있다.
전체 semijoin 플래그 외에는 비활성화 시키는 variable이 없다.
2. FirstMatch
In 서브쿼리를 exists로 바꿔서 실행시켜서 각 로우에 대해 handler_read_key 1회씩만 실행한다.
아우터쿼리에서 적절한 인덱스를 사용할 수 있을 때 사용한다.
# before
SELECT *
FROM employees e
WHERE e.emp_no IN (
SELECT emp_no
FROM salaries
)
# after
SELECT *
FROM employees e
WHERE exists(
SELECT *
FROM salaries s
WHERE s.emp_no = e.emp_no
)위 쿼리에서 before 쿼리가 after처럼 실행된다.
서브쿼리에 필요한 부분만 읽기 때문에 서브쿼리(salaries)의 행이 많을수록 유리하다.
3. Materialization
임시테이블로 구체화한 후 드라이빙 또는 드리븐 테이블로 사용한다.
드리븐 테이블로 사용할 때는 자동으로 생성되는 인덱스인 distinct_key가 사용된다.
서브쿼리는 상관서브쿼리가 아니어야 한다. (독립적으로 실행할 수 있어야 한다.)
서브쿼리에 Group by가 있어도 가능하다.
SELECT *
FROM employees e
WHERE e.emp_no IN (
SELECT emp_no
FROM salaries
)서브쿼리가 먼저 실행되기 때문에 서브쿼리 비용이 높을 경우 (salaries 행이 많을수록) 불리하다.
4. LooseScan
서브쿼리를 루스스캔하여 읽고 드라이빙 테이블로 사용한다.
SELECT /*+
SEMIJOIN(@subq1 LOOSESCAN)
*/ *
FROM departments d
WHERE d.dept_no IN (
SELECT /*+ QB_NAME(subq1) */ DISTINCT de.dept_no
FROM dept_emp de
);# explain
[
{
"id": 1,
"select_type": "SIMPLE",
"table": "de",
"partitions": null,
"type": "index",
"possible_keys": "PRIMARY",
"key": "PRIMARY",
"key_len": "20",
"ref": null,
"rows": 331143,
"filtered": 0,
"Extra": "Using index; LooseScan"
},
{
"id": 1,
"select_type": "SIMPLE",
"table": "d",
"partitions": null,
"type": "eq_ref",
"possible_keys": "PRIMARY",
"key": "PRIMARY",
"key_len": "16",
"ref": "employees.de.dept_no",
"rows": 1,
"filtered": 100,
"Extra": null
}
]루스 스캔 할 수 있는 서브쿼리에서 가능하다.
이상한 점은 departments가 9건, dept_emp 약 30만건인데 약 100m가 걸리고 인덱스 레인지스캔(handler_read_next)도 30만건으로 표시된다.
explain에는 loosescan 표시됬지만 실제로는 mateiralizaion으로 실행되는건지 확인 필요하다
SELECT d.*
FROM departments d
INNER JOIN (
SELECT DISTINCT de.dept_no
FROM dept_emp de
) de ON d.dept_no = de.dept_no
;# explain
[
{
"id": 1,
"select_type": "PRIMARY",
"table": "<derived2>",
"partitions": null,
"type": "ALL",
"possible_keys": null,
"key": null,
"key_len": null,
"ref": null,
"rows": 9,
"filtered": 100,
"Extra": null
},
{
"id": 1,
"select_type": "PRIMARY",
"table": "d",
"partitions": null,
"type": "eq_ref",
"possible_keys": "PRIMARY",
"key": "PRIMARY",
"key_len": "16",
"ref": "de.dept_no",
"rows": 1,
"filtered": 100,
"Extra": null
},
{
"id": 2,
"select_type": "DERIVED",
"table": "de",
"partitions": null,
"type": "range",
"possible_keys": "PRIMARY,ix_fromdate,ix_empno_fromdate",
"key": "PRIMARY",
"key_len": "16",
"ref": null,
"rows": 9,
"filtered": 100,
"Extra": "Using index for group-by"
}
]세미조인의 루스스캔 말고 일반 루스스캔으로 서브쿼리 처리 후 조인하니까 10ms 소요된다.
인덱스 스캔(Handler_read_key)도 20건으로 정상적으로 표시된다.
5. Duplicate Weedout
서브쿼리를 조인으로 바꿔 실행한 후 중복을 제거한다.
extra에 start temporary, end temporary가 표시된다.
조인으로 바꾸기 때문에 상관서브쿼리에서도 가능하다.
Skip Scan
다중 컬럼 인덱스에서 첫 번째 컬럼이 WHERE 조건에 없더라도 사용할 수 있게 한다.
일반적인 상황과 다르게 첫 번째 컬럼의 카디널리티가 낮을 수록 좋다.
쿼리가 커버링 인덱스인 상황에서만 사용할 수 있다.
첫 번째 컬럼도 IN 조건으로 모두 적용하면 커버링 인덱스 외에도 사용할 수 있다.
# index (gender, birth_date)
SELECT gender
, birth_date
FROM employees e
WHERE birth_date > '1965-02-01'WHERE에 gender가 없지만 인덱스를 사용할 수 있다.
Using index for skip scan가 표시된다.
SELECT *
FROM employees e
WHERE gender IN ('M', 'F')
AND birth_date > '1965-02-01'WHERE문에 gender가 가질 수 있는 모든 값을 IN 으로 줘서 실행하면 스킵스캔 사용하지 않아도 되고
커버링 인덱스가 아니어도 가능하다
Loose Scan
주로 Group by 쿼리에서 MIN 이나 MAX 값을 구할 때 필요한 값만 읽는다.
SELECT dept_no
, min(emp_no)
FROM dept_emp
WHERE dept_no BETWEEN 'd002' AND 'd004'
GROUP BY dept_noExtra에 Using index for group-by가 표시된다.
인덱스 머지
한 쿼리에서 두 개의 인덱스를 같이 사용하는 방식
교집합
AND 조건에 각각 다른 인덱스를 사용할 경우 두 인덱스 모두 사용 후 교집합으로 결과를 구한다.
optimizer_switch의 index_merge_intersection으로 활성화/비활성화 할 수 있다.
Using intersect로 표시된다.
합집합
OR 조건으로 각각 다른 인덱스를 사용하며 같은 컬럼(PK)로 정렬 되어 있을 경우
우선순위 큐 알고리즘 사용하여 중복 없는 합집합을 구한다.
optimizer_switch의 index_merge_union으로 활성화/비활성화 할 수 있다.
Using union으로 표시된다.
정렬 후 합집합
OR 조건으로 각각 다른 인덱스를 사용하며 정렬 컬럼이 다른 경우
같은 컬럼(PK)로 정렬 후 합집합과 동일한 방식으로 집계한다.
optimizer_switch의 index_merge_sort_union으로 활성화/비활성화 할 수 있다.
Using sort-unoin으로 표시된다.
