MySQL - 정상 수행되던 쿼리가 IN 절에서 Index Scan 을 하지 않을 경우 Table Full Scan

Last Updated on 1월 4, 2024 by Jade(정현호)

배경

수개월 전에 회사 동료분에게 해당 내용을 공유를 들었던 적이 있었습니다.

테이블에 인덱스도 구성이 잘 되어있고, 쿼리도 인덱스를 사용할 수 있는 WHERE 조건의 쿼리인데 분명히 이전에는 Index Scan 으로 수행된 쿼리가 "갑자기 Table Full Scan 으로 수행이 된다" 였습니다. 

그래서 그 때 당시 간략하게 내용만 정리하였다가 최근에 다시 동일한 사례를 경험하여 내용을 다시 정리한 글입니다.
         

쿼리 및 파라미터

문제가 되는 쿼리는 조인이 없는 단순 1개 테이블 조회 쿼리였으며 WHERE 절에는 Index 컬럼에 IN 절에 조건이 대입되어 있는 쿼리였습니다.
             

테스트 테이블 및 데이터 생성

테스트 테이블 및 데이터는 아래와 같이 생성하였습니다.

-- 테스트 Database 생성
mysql> create database test_db; 
mysql> use test_db;


-- 테이블 프로시저 생성
mysql> DELIMITER $$
DROP PROCEDURE IF EXISTS gen_data$$

CREATE PROCEDURE gen_data(
-- IN v_table_name varchar(100),
IN v_rows INT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    
    create table tb_test1(
    no bigint,
    col1 varchar(100),
    col2 varchar(100),
    col3 varchar(100),
    key idx1(col1,col2)
    );

    WHILE i <= v_rows DO     
        INSERT INTO tb_test1 (no , col1, col2 , col3 )
          VALUES(concat(i), concat('col1-',i), concat('col2-',i), concat('col3-',i));
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;


-- 테이블 생성 및 데이터 입력
mysql> call gen_data(100000);

쿼리 및 플랜

문제가 된 쿼리는 아래와 같이 조인이 없는 1개 테이블의 조회이며 IN 절 조건이 있는 형태였습니다. 그리고 플랜에서도 Index 를 사용할 수 있는 것을 확인할 수 있었습니다.

mysql> explain
select *
from tb_test1
where (`col1`, `col2`)
IN (
('col1-1','col2-1'),
('col1-10','col2-10'),
('col1-11','col2-11')
);

+----+-------------+----------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_test1 | range | idx1          | idx1 | 806     | NULL |    3 |   100.00 | Using where |
+----+-------------+----------+-------+---------------+------+---------+------+------+----------+-------------+

[참고] 가로 길이에 의해서 플랜의 일부 컬럼은 편집되어 있습니다.
       

많은 수의 IN절 조건

문제가 된 쿼리는 아래 같이 아주 많은 IN 조건이 있는 SQL 문장이었습니다.

mysql> explain
select * 
from tb_test1
where (`col1`, `col2`)
IN (
('col1-1','col2-1'),
('col1-2','col2-2'),
('col1-3','col2-3'),

<...중략...>

('col1-998','col2-998'),
('col1-999','col2-999'),
('col1-1000','col2-1000'));

위와 같이 IN 조건절 내에 많은 조건이 있었고 최근의 사례에서의 쿼리는 IN 절 조건이 1,000개였습니다.
            

관련 파라미터

이 사례에서 직접 적인 원인이 된 이유는 range_optimizer_max_mem_size 파라미터와 관련이 되어 있었습니다.

range_optimizer_max_mem_size 파라미터는 "Limiting Memory Use for Range Optimization" 를 하는 파라미터로 범위 액세스 간의 메모리 사용량에 대한 제한을 하게 됩니다.

5.7.11 이하 버전에서는 기본값이 1.5M 이고, 5.7.12 이상 버전에서는 8M(8388608) 입니다.

• MySQL 8.0 server-system-variables

문제 상황 재현

재현 케이스는 간단하게 range_optimizer_max_mem_size 파라미터의 수를 조정하고 IN 절을 수를 늘리게 되면 IN 절에서 사용하는 메모리 용량이 파라미터를 초과하게 되면 Table Full Scan 으로 수행되게 됩니다.

• 파라미터 변경
mysql> set session range_optimizer_max_mem_size=nnn;

파라미터 적용 범위는 Session  과 Global 이 가능하고 Dynamic 으로 적용 가능 한 파라미터이고 0 으로 설정 시 제약이 없게 됩니다.

The limit on memory consumption for the range optimizer. A value of 0 means "no limit."

SQL 파일을 2개 생성하였으며 a.sql 파일에는 아래와 같은 유형의 쿼리이며 IN 절 조건이 1000개가 있는 쿼리입니다.

explain
select * 
from tb_test1
where (`col1`, `col2`)
IN (
('col1-1','col2-1'),
('col1-2','col2-2'),
('col1-3','col2-3'),

<...중략...>

('col1-998','col2-998'),
('col1-999','col2-999'),
('col1-1000','col2-1000'));

b.sql 은 IN 절 조건이 3개 있는 쿼리입니다.

explain
select *
from tb_test1
where (`col1`, `col2`)
IN (
('col1-1','col2-1'),
('col1-10','col2-10'),
('col1-11','col2-11')
);

2개의 파일을 이용하여 아래와 같이 파라미터를 변경하여 테스트 진행하였습니다.

-- range_optimizer_max_mem_size 기본에서는 Index Scan 으로 실행계획이 확인됨
mysql> source a.sql
+----+-------------+----------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_test1 | range | idx1          | idx1 | 806     | NULL | 1000 |   100.00 | Using where |
+----+-------------+----------+-------+---------------+------+---------+------+------+----------+-------------+


-- 파라미터 변경
mysql> set session range_optimizer_max_mem_size=5000;


-- Index Scan 에서 Table Full Scan 으로 변경됨
mysql> source a.sql
+----+-------------+----------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | tb_test1 | ALL  | idx1          | NULL | NULL    | NULL | 99671 |    50.00 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+-------+----------+-------------+


-- IN 절 조건 수가 적은 b.sql 은 여전히 Index Scan 이 가능
mysql> source b.sql
+----+-------------+----------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_test1 | range | idx1          | idx1 | 806     | NULL |    3 |   100.00 | Using where |
+----+-------------+----------+-------+---------------+------+---------+------+------+----------+-------------+


-- 0(제한 없음) 으로 변경 후 확인
mysql> set session range_optimizer_max_mem_size=0;

-- 다시 Index Scan 으로 플랜이 변경됨
mysql> source a.sql
+----+-------------+----------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_test1 | range | idx1          | idx1 | 806     | NULL | 1000 |   100.00 | Using where |
+----+-------------+----------+-------+---------------+------+---------+------+------+----------+-------------+

* 플랜의 가로 길이 때문에 일부 컬럼은 편집을 하였습니다.


위의 간단한 테스트 처럼 IN 절의 개수 와 range_optimizer_max_mem_size 파라미터에 따라서 Index 사용 유무가 달라 지는, 즉 플랜이 변경되게 됩니다. 물론 변경되는 수치는 데이터와 쿼리에 따라서 달라 지게 됩니다.

Index Scan <-> Table Full Scan 과 같이 플랜이 변경되는 사례와 그 이유는 매우 다양할 것입니다 포스팅의 내용이 절대적인 이유나 케이스 로 보기 보다는 플랜이 변경되는(인덱스를 사용하지 못하는) 여러 케이스 중 하나 정도로 문제 분석 시 한번 쯤 고려해봐도 좋을 것 같습니다.
         

Reference

Reference link
https://kimdubi.github.io/mysql/range_optimizer


관련된 다른 글

MySQL - Block Nested-Loop and Batched Key Access Joins

MySQL 에서 Block Nested-Loop 와 Batched Key Access Joins 그리고 Multi Range Read(MRR) 을 도입 및 추가한 이유로는 쉽게 Join Method 인 Nested Loop 조인의 한계점을 보완하고 개선하기 위해서 입니다

MySQL Delete 문 성능 - IN절에서 서브쿼리 사용시 - Multiple Delete / CTAS

MySQL 에서의 서브쿼리 MySQL 은 전통적으로 서브쿼리 최적화에 약한 면모를 보이고 있습니다

MySQL 쿼리 프로파일링(Query Profiling)

쿼리 프로파일링(Query Profiling)MySQL 에서 쿼리가 처리되는 동안 각 단계별 작업에 시간이 얼마나 걸렸는지 확인 할 수 있는 기능을 제공합니다.

MySQL EXPLAIN ANALYZE - 8.0.18

MySQL 8.0.18 버전은 2019년 10월 14일에 GA 가 되었으며 8.0.18 의 New Feature 로 EXPLAIN ANALYZE 기능이 추가 되었습니다.

MySQL SYS Schema 를 이용한 미사용/중복 Index 확인

Mysql 에서 제공되는 SYS Schema를 통해서 사용되지 않는 Index와 중복된 Index 를 조회하는 내용을 확인 할 수 있으며 SYS Schema 는 Mysql 5.7.7 버전부터 기본포함이며 5.6 버전 부터 별도로 설치가 가능 합니다

MySQL - UUID 활용 - PK 로 사용

안녕하세요 이번 포스팅에서는 MySQL 의 PK 컬럼에서 Auto Increment 형태 대신 UUID 를 사용하는 내용에 대해서 전반적으로 확인 해보려고 합니다. UUID 란 UUID 는 Universally Unique IDentifier 의 약어이고 범용 고유 식별자 라고 할 수 있습니다.

Jade(정현호)

Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중

Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io