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

Share

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

Command-Line Format --range-optimizer-max-mem-size=#
System Variable range_optimizer_max_mem_size
Scope Global, Session
Dynamic Yes
SET_VAR Hint Applies No
Type Integer
Default Value 8388608
Minimum Value 0
Maximum Value 18446744073709551615
Unit bytes

          

문제 상황 재현

재현 케이스는 간단하게 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


관련된 다른 글

 

 

 

 

 

                      

0
글에 대한 당신의 생각을 기다립니다. 댓글 의견 주세요!x