Computer Science

MySQL에서 Full Scan이 발생하는 쿼리 패턴 알아보기

11/5/2024

Full Scan은 쿼리의 결과를 가져오는데에 인덱스를 사용할 수 없어 전체 테이블을 조회해야하는 쿼리 수행 방식을 의미합니다. Full Scan이 사용되는 경우 쿼리 수행 시간은 레코드의 크기와 개수에 비례하기에 데이터가 많은 상황에서는 현실적으로 사용이 불가할지도 모릅니다. 따라서 이를 해결하기 위해 필요한 컬럼에 인덱스를 생성한 뒤 쿼리 수행이 인덱스를 사용하여 이루어지게 해야합니다. 아래 내용에서는 원하는 컬럼에 인덱스를 걸었음에도 불구하고 풀 스캔이 발생하는 상황들에 대해서 다룹니다.

테스트 데이터 셋

테스트를 위해 Members 테이블을 생성했습니다. Member 테이블은 임의의 5자리 알파벳을 이름으로 가지며 0부터 100 사이의 값을 age로 갖습니다. 인덱스 사용에 대한 시간 차가 눈에 쉽게 보이게 만들기 위하여 100만개의 데이터를 생성했습니다.

➜ select count(*) from members;

+----------+

| count(*) |

+----------+

| 1000000 |

+----------+

1 row in set (0.04 sec)

LIKE 연산과 와일드카드

LIKE 연산자를 사용한다면 문자열에서 해당 패턴을 포함하고 있는 문자열을 가진 레코드를 필터링할 수 있습니다.

➜ select * from members where name like '%abc%';

157 rows in set (0.16 sec)

➜ select * from members where name like '%abc';

50 rows in set (0.17 sec)

➜ select * from members where name like 'abc%';

48 rows in set (0.17 sec)

위 쿼리는 이름 중 abc라는 패턴이 존재하거나, abc로 시작하거나 끝나는 이름에 대한 결과를 반환합니다. 결과를 얻는데에는 대략 0.17초가 걸렸습니다. 이는 해당 패턴이 존재하는 레코드를 얻기 위해 모든 레코드의 name 필드를 조사해야하여 발생한 결과입니다.

인덱스 생성

쿼리 수행 시간 단축을 위해서 name 컬럼에 인덱스를 생성해보도록 합니다. 자 과연 수행 시간이 얼마나 단축될까요?

CREATE INDEX name_idx ON members(name);

➜ select * from members where name like '%abc%';

157 rows in set (0.16 sec)

➜ select * from members where name like '%abc';

50 rows in set (0.17 sec)

➜ select * from members where name like 'abc%';

48 rows in set (0.01 sec)

와일드카드가 양옆에 붙거나, 앞에 붙은 경우에는 인덱스 생성 전과 차이가 없었으며, 와일드 카드가 뒤에 붙은 경우에는 수행시간이 유의미하게 단축되었습니다.

왜 그럴까?

위와 같은 소요시간이 발생한 이유는 '%abc%'와 '%abc'로 필터링을 하는 쿼리는 인덱스 사용이 불가능하기 때문입니다. 이는 쿼리의 실행계획을 확인함을 통해 알 수 있습니다.

➜ explain select * from members where name like '%abc%';

+----+-------------+---------+------------+------+---------------+------+---------+------+--------+-------------+

+----+-------------+---------+------------+------+---------------+------+---------+------+--------+-------------+

+----+-------------+---------+------------+------+---------------+------+---------+------+--------+-------------+

1 row in set, 1 warning (0.01 sec)

➜ explain select * from members where name like '%abc';

+----+-------------+---------+------------+------+---------------+------+---------+------+--------+-------------+

+----+-------------+---------+------------+------+---------------+------+---------+------+--------+-------------+

+----+-------------+---------+------------+------+---------------+------+---------+------+--------+-------------+

1 row in set, 1 warning (0.01 sec)

와일드카드가 문자열의 양옆에, 앞에 붙은 쿼리들은 쿼리를 수행하는데에 인덱스가 사용되지 않았습니다.

➜ explain select * from members where name like 'abc%';

+----+-------------+---------+------------+-------+---------------+----------+---------+------+-------+-----------------------+

+----+-------------+---------+------------+-------+---------------+----------+---------+------+-------+-----------------------+

+----+-------------+---------+------------+-------+---------------+----------+---------+------+-------+-----------------------+

1 row in set (0.00 sec)

한 편, 와일드카드가 문자열의 뒤에 붙은 쿼리는 요청을 처리하는데에 name_idx를 사용했음을 알 수 있습니다.

인덱스는 정렬된 트리로 존재하며, 쿼리를 수행하기 위해 조사해야하는 범위를 일부 서브트리 구간으로 제한함으로써 성능의 향상을 이뤄냅니다. 세번째 쿼리는 'abc'로 시작하는 name을 가진 레코드를 찾는 쿼리이기에, abc로 시작하는 레코드들이 모여있는 서브트리만 조사하면 나머지는 스캔할 필요가 없습니다. 한 편 abc를 포함하는, 혹은 abc로 끝나는 name을 가진 레코드를 찾기 위해서는 사전 순으로 정렬된 트리는 사용될 수 없습니다. 이 때는 모든 레코드를 확인하여 해당 패턴이 존재하는지 검사해야합니다.

따라서 varchar와 같은 문자열 타입에서 와일드카드를 패턴의 앞에 사용하는 것은 풀스캔이 발생하는 대표적인 패턴이고, 잘 모른채로 사용한다면 실수하기 쉽습니다. 이러한 문제를 극복하고 문자열에서 패턴을 검사하기 위해서는 문자열 검색 용으로 만들어진 전문 검색 인덱스Full-Text Index 나, 별도의 검색엔진을 사용하는 것이 성능을 높이는 대안책이 될 수 있습니다.

인덱싱 되지 않은 컬럼을 조건절에 or 연산과 사용

➜ select * from members where name like 'abc%' or age > 99;

10015 rows in set (0.17 sec)

위와 같이 인덱스가 걸려있지 않은 age 컬럼에 대하여 OR 조건을 가진 쿼리를 수행하는 것도 Full Scan이 발생하게 됩니다. 아무리 name에 대해 인덱스가 걸려있더라도, age 조건을 만족하는 결과를 얻기 위해서 전체 테이블을 조회해야하기 때문입니다.

➜ explain select * from members where name like 'abc%' or age > 99;

+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+

+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+

+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+

물론 AND 조건은 인덱스를 활용한 결과로 age를 필터링해도 무방하기 때문에 조사 범위를 줄일 수 있습니다.

➜ select * from members where name like 'abc%' and age = 1000;

Empty set (0.01 sec)

➜ explain select * from members where name like 'abc%' and age > 99;

+----+-------------+---------+------------+-------+---------------+----------+---------+------+-------+----------+--------------------------------------+

+----+-------------+---------+------------+-------+---------------+----------+---------+------+-------+----------+--------------------------------------+

+----+-------------+---------+------------+-------+---------------+----------+---------+------+-------+----------+--------------------------------------+

컬럼이 가공되는 경우

컬럼이 가공되어 처리되어야 하는 쿼리 역시 인덱스를 사용할 수 없습니다.

위 예제에서 사용했던 Member 테이블의 age를 조건으로 하는 쿼리를 작성하고 수행시간을 확인해보겠습니다.

➜ select * from members where age = 99;

10042 rows in set (0.17 sec)

나이가 99인 회원을 조회하는 쿼리는 0.17초가 걸렸습니다. 현재의 결과는 별도의 인덱스가 없으므로 모든 레코드를 조회하여 나이가 99인 결과만 가져오게 되었습니다. 이러한 경우 age에 인덱스가 있다면, 조사의 범위를 줄여 효율적으로 쿼리 결과를 가져올 수 있을 것입니다.

➜ create index age_idx on members(age);

➜ select * from members where age = 99;

10042 rows in set (0.05 sec)

한 편, 쿼리에 인덱스 컬럼에 대한 연산과정이 필요하다면 인덱스는 사용될 수 없습니다.

➜ select * from members where age * 2 = 198;

10042 rows in set (0.15 sec)

위 쿼리는 age에 2를 곱한 값이 198인 (즉 age가 99인) 데이터들을 가져오는 쿼리입니다. 이 연산을 수행하기 위해서 MySQL은 원본 컬럼의 데이터가 정렬되어 저장되어있는 인덱스를 사용하지 못합니다. age는 매 레코드마다 새롭게 계산되어 198과 비교되어야 해당 레코드를 가져올지 말 지를 결정할 수 있습니다.

➜ explain select * from members where age * 2 = 198;

+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+

+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+

+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+