IN 연산자 사용 시 FULL SCAN ?!
Oracle 등 다른 DBMS에서는 10ms 내로 소요되는 UPDATE문이
MySQL, MariaDB에서만 10,000~20,000ms 가량 소요되는 것이 확인되었다. (약 2만개의 레코드를 가진 테스트DB)
우선, 10.3 버전의 MariaDB에서 실행계획을 조회해보았다.
IN 연산자로 PK 혹은 인덱스 컬럼 조회 시 Unique 나 Range Index Scan을 하지 않고, Index Full Scan을 하는 것으로 나왔다.
원인과 해결 방법을 알아보자!
실행계획 조회
1. MySQL, MariaDB
-- 실행계획 조회
EXPLAIN
UPDATE sample_table
SET UPD_DT = current_timestamp()
WHERE sample_id IN (SELECT sample_id
FROM other_table
WHERE other_id = 1000);
-- 실행계획 조회 (JSON 형식)
EXPLAIN FORMAT = JSON
UPDATE sample_table
SET UPD_DT = current_timestamp()
WHERE sample_id IN (SELECT sample_id
FROM other_table
WHERE other_id = 1000);실행계획 조회 결과
id|select_type |table |type |possible_keys |key |key_len|ref |rows |Extra |
--+------------------+------------+--------------+-----------------------------+-------+-------+----+-------+-----------+
1|PRIMARY |SAMPLE_TABLE|index | |PRIMARY|92 | |1759872|Using where|
2|DEPENDENT SUBQUERY|OTHER_TABLE |index_subquery|PRIMARY,PK_OTHER_TABLE,IDX_01|PRIMARY|92 |func| 1|Using where|아래 두 컬럼을 중점적으로 보자!
-
type컬럼 : 테이블 접근 방식-
system : 테이블에 데이터가 없거나 한 개만 있는 경우
-
const : UNIQUE INDEX SCAN (조회되는 데이터가 단 1건일 때)
-
eq_ref : JOIN을 수행할 때 driven table에 고유인덱스나 기본키로 단 1건의 데이터를 조회하는 방식
-
ref : 동등 조건으로 조회 (반환되는 레코드가 반드시 1건이라는 보장이 없음)
-
range : 테이블 내의 연속된 데이터 범위를 조회
-
index : INDEX FULL SCAN (인덱스는 보통 테이블보다 크기가 작으므로 테이블 풀 스캔 방식보다는 빠를 가능성이 있음)
-
index_subquery : IN (subquery) 형태의 쿼리를 위한 접근 방식으로, 인덱스를 이용해 중복값을 제거
-
unique_subquery : IN (subquery) 형태의 서브 쿼리에서 중복되지 않은 유니크한 값만 반환 (별도의 중복 제거 작업이 필요하지 않음)
-
ALL : TABLE FULL SCAN (전체 테이블 중 10~20% 이상 분량의 데이터를 조회할 때는 ALL 유형이 오히려 성능상 유리할 수 있음)
-
-
rows컬럼 : 쿼리 수행에서 예상하는 검색해야 할 행수
실행계획 조회 결과 (JSON 형식)
access_type, rows에 주목
{
"query_block": {
"select_id": 1,
"table": {
"update": 1,
"table_name": "SAMPLE_TABLE",
"access_type": "index",
"key": "PRIMARY",
"key_length": "",
"used_key_parts": ["SAMPLE_ID"],
"rows": 1759872,
"attached_condition": "<in_optimizer>(SAMPLE_TABLE.SAMPLE_ID,<exists>(subquery#2)) and SAMPLE_TABLE.PRCS_CD = 'S'"
},
"subqueries": [
{
"query_block": {
"select_id": 2,
"table": {
"table_name": "OTHER_TABLE",
"access_type": "index_subquery",
"possible_keys": ["PRIMARY", "PK_OTHER_TABLE", "IDX_01"],
"key": "PRIMARY",
"key_length": "92",
"used_key_parts": ["SAMPLE_ID"],
"ref": ["func"],
"rows": 1,
"filtered": 100
}
}
}
]
}
}
2. 비슷한 쿼리와 비교
서브쿼리 대신 값을 넣은 UPDATE문
- range : 테이블 내의 연속된 데이터 범위를 조회
EXPLAIN
UPDATE sample_table
SET UPD_DT = current_timestamp()
WHERE sample_id IN ('a', 'b', 'c');
-- result
id|select_type|table |type |possible_keys |key |key_len|ref|rows|Extra |
--+-----------+-------------+-----+------------------------+-------+-------+---+----+-----------+
1|SIMPLE |SAMPLE_TABLE |range|PRIMARY,PK_SAMPLE_TABLE |PRIMARY|92 | | 2|Using where|IN절 안에 값을 명시했을 경우, 인덱스를 정상적으로 사용했음을 확인할 수 있다.
'인덱스를 정상적으로 사용한다'의 의미는
리프 블록에서 스캔 시작점을 찾아 스캔하다가 중간에 멈추는 것을 의미한다. 즉, 리프 블록 일부만 스캔하는 Index Range Scan을 의미한다.
where절 조건이 같은 SELECT문
- eq_ref : 조인 수행 시, 인덱스로 단 1건의 데이터를 조회
EXPLAIN
SELECT sample_id, upd_dt
FROM sample_table
WHERE sample_id IN (SELECT sample_id
FROM other_table
WHERE other_id = 1000);
-- result
id|select_type |table |type |possible_keys |key |key_len|ref |rows|Extra |
--+------------+-------------+------+-------------------------------------+-------+--------------------------+----+-----------+
1|PRIMARY |<subquery2> |ALL |distinct_key | | | | 2| |
1|PRIMARY |SAMPLE_TABLE |eq_ref|PRIMARY,PK_SAMPLE |PRIMARY|92 |SCH01.OTHER_TABLE.OTHER_ID| 1| |
2|MATERIALIZED|OTHER_TABLE |ref |PRIMARY,PK_OTHER_TABLE,IDX_01|IDX_01 |4 |const | 2|Using index|같은 조건임에도 UPDATE문에서는 index, SELECT문에서는 eq_ref 로 테이블에 접근했다!
SELECT문에서는 하나의 데이터로 검색할 것을 예상했으나, UPDATE문에서는 그러지 못했을뿐만 아니라 인덱스 스캔 시작점조차 찾지 못했다.
해당 원인을 찾아보았으나, 찾지 못했다...
아마 옵티마이저의 문제같으나 근본적인 해결 방법을 찾기는 어려울 것 같아 다른 방법으로 해결하였다. (아래 해결 방법 확인!)
3. Oracle과 비교
-- 실행계획 저장
EXPLAIN PLAN FOR
UPDATE sample_table
SET upd_dt = sysdate
WHERE sample_id IN (SELECT sample_id
FROM other_table
WHERE other_id = 1000)
AND prcs_cd = 'S';
-- 실행계획 조회
SELECT * FROM TABLE(dbms_xplan.display);실행계획 조회 결과
Id 6번을 보면 PK를 이용하여 INDEX UNIQUE SCAN을 한다.
(수직적 탐색만으로 데이터를 찾는 스캔 방식으로서,
Unique 인덱스를 '=' 조건으로 탐색하는 경우에 작동한다.)
PLAN_TABLE_OUTPUT |
-----------------------------------------------------------------------------------------------------+
Plan hash value: 3021195589 |
-----------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-----------------------------------------------------------------------------------------------------|
| 0 | UPDATE STATEMENT | | 950 | 68400 | 963 (1)| 00:00:12 ||
| 1 | UPDATE | SAMPLE_TABLE | | | | ||
| 2 | NESTED LOOPS | | | | | ||
| 3 | NESTED LOOPS | | 950 | 68400 | 963 (1)| 00:00:12 ||
| 4 | SORT UNIQUE | | 950 | 25650 | 12 (0)| 00:00:01 ||
|* 5 | INDEX RANGE SCAN | IDX_01 | 950 | 25650 | 12 (0)| 00:00:01 ||
|* 6 | INDEX UNIQUE SCAN | PK_SAMPLE_TABLE | 1 | | 1 (0)| 00:00:01 ||
|* 7 | TABLE ACCESS BY INDEX ROWID| SAMPLE_TABLE | 1 | 45 | 2 (0)| 00:00:01 ||
-----------------------------------------------------------------------------------------------------|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
5 - access("OTHER_ID"=1000) |
6 - access("SAMPLE_ID"="SAMPLE_ID") |
7 - filter("PRCS_CD"='S') |
Index Full Scan 원인
샘플 쿼리문은 특수한 경우로 판단되어 일반적인 경우도 추가로 알아보았다.
일반적으로 Index Full Scan을 했다는 것은 인덱스를 정상적으로 사용하지 못했다고 해석할 수 있다. 즉, 인덱스 스캔 시작점을 찾지 못해 Index Range Scan을 하지 못했다는 의미이다.
그 이유는 다음과 같다.
1. IN에 포함되는 데이터가 너무 많은 경우
2. IN에 포함되는 데이터가 흩어져 있는 경우
3. 가공값으로 검색
IN 조건절을 사용하지 않고 Index Full Scan하는 경우
- Like로 중간에 포함된 값을 검색
- 여러 컬럼에 대한 OR조건
해결 방법
1. range_optimizer_max_mem_size 조절
range_optimizer_max_mem_size는 range optimizer로 이용할 수 있는 메모리 크기를 조절하는 시스템 변수이다.
IN에 포함된 데이터의 비율이 매우 높다면 range 함수 대신 다른 방법으로 데이터를 탐색한다.
range 함수를 이용할 수 있도록 range_optimizer_max_mem_size를 늘리거나 0으로 설정해 볼 수 있다.
- MySQL default 값
- ver 5.7.12 이상 : 8M
- ver 5.7.11 이하 : 1.5M
참고로, Mysql 5.7버전 이상부터는 0으로 설정 시 unlimit을 의미한다.
아래 방법으로 해당 변수를 임의로 설정할 수 있다.
# range_optimizer_max_mem_size 확인
SHOW VARIABLES LIKE 'range_optimizer%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| range_optimizer_max_mem_size | 8388608 |
+------------------------------+---------+
# 글로벌 적용 & 메모리 제한 없음 (no limit)
SET GLOBAL range_optimizer_max_mem_size = 0;
# 글로벌 적용 & 5000000 bytes 로 메모리 제한
SET range_optimizer_max_mem_size = 5000000;-
MySQL 5.7 에서 추가된 range_optimizer_max_mem_size
https://blog.naver.com/parkjy76/222450202444 -
MariaDB 10.3 과 MySQL 5.7의 시스템변수 차이
https://mariadb.com/kb/en/system-variable-differences-between-mariadb-10-3-and-mysql-5-7/ -
MariaDB does not limit memory used for range optimization
https://jira.mariadb.org/browse/MDEV-9764?page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel&showAll=true
2. 조건 범위 줄이기
range_optimizer_max_mem_size를 충분한 크기로 조절해도 (혹은 0으로 설정해도) 인덱스를 타지 않을 수 있다.
이는 조건에 일치하는 레코드가 너무 많거나, 데이터가 인덱스 전 범위에 걸쳐 흩어져 있어 MySQL 옵티마이저가 range scan을 포기하고 full scan을 하는 것일 수 있다.
IN에 포함되는 데이터 자체를 줄이거나, 조건 범위를 잘라서 여러번 조회함으로써 해결해볼 수 있을 것이다.
3. 쿼리 튜닝 ✨
샘플 쿼리의 경우 가능한 튜닝 방법은 아래와 같다.
1) 다른 연산자 사용 (ex. '=')
2) 인덱스를 타도록 IN구문 앞에 다른 인덱스 조건을 넣기
(실제로는 위 2번 방법을 사용하여 해결하였다!)
그 외 테이블과 데이터 등에 따라 튜닝 방법은 다양하므로, 상황에 맞게 쿼리를 튜닝해보자
DBeaver 쿼리 수행이력(소요시간) 조회 방법
상단 시계 아이콘 - 트랜잭션 로그 또는 쿼리 관리자
cf) 통계테이블 갱신
실행계획 조회 기능은 통계정보 테이블을 기반으로 동작하므로, 통계정보가 부족하거나 불일치할 경우 분석의 정확성이 다소 떨어질 수 있다. 따라서, 아래와 같이 통계테이블을 갱신해보자.
MySQL, MariaDB
ANALYZE TABLE sample_tableTable |Op |Msg_type|Msg_text|
-------------------+-------+--------+--------+
SCHEMA.SAMPLE_TABLE|analyze|status |OK |통계테이블 갱신 + 실행 계획 조회 동시 실행
MySQL 8.0.18버전부터 가능
EXPLAIN ANALYZE 분석하고자하는 쿼리;
*참고사이트
