풀스캔 쿼리 패턴 및 튜닝

1. 들어가며

 

풀스캔 쿼리라는 건 백엔드 개발자에게 정말 무서운 말인 것 같습니다. 실제 테이블 레코드 건수가 적다면 큰 부하가 아니겠지만 실무에서 그런 경우는 흔치 않습니다. 특히 서비스의 메인이 되는 테이블에 풀스캔 쿼리가 발생하는 일 만큼은 피해야 합니다. 오늘은 풀스캔 쿼리가 발생하는 주된 패턴을 살펴보고 풀스캔 쿼리를 피하는 방법에 대해 살펴보도록 하겠습니다.

 

2. 풀스캔 쿼리 패턴

 

1) 컬럼이 가공되는 경우

컬럼이 연산에 포함되는 경우 인덱스를 사용할 수 없게 됩니다. 인덱스는 컬럼 원본 값으로 만들어져 있기 때문입니다.

SELECT * FROM tb1 WHERE count + 10 < 2000 // 연산

SELECT * FROM tb1 WHERE MOD(id,2)=0 // 함수

SELECT * FROM tb1 WHERE str_column=12345 // 형변환

 

• 연산의 경우: id에 1만 곱해서 사실 동일한 값일텐데도 인덱스 사용 못하고 full scan을 하지 못합니다.
• 함수인 경우: 사실 실무에서 제법 나타나는 사례인데, 특히 DATE와 같은 것과 함께 사용할 때 그렇습니다. 함수를 제거하고 범위 조회처럼 바꿔서 사용해야 합니다.
• 형변환: 문자열과 숫자는 다릅니다. 재밌는 점은 숫자인 column에 대해 문자열을 비교할 경우 인덱스를 탈 수 있는데 이는 mysql 내부 형변환 우선순위 때문입니다. 문자를 숫자로 변경하는 것에 더 우선순위가 있습니다.

2) 인덱싱이 되지 않은 컬럼을 조건절에 OR 연산과 함께 사용

OR 절은 여러 개의 조건 중 하나라도 참이면 전체 조건을 참으로 판단합니다. 이 말은 OR 절이 사용된 쿼리는 데이터베이스가 조건의 모든 가능성을 검사하고 그 결과를 결합해야 한다는 것을 의미하며, 데이터베이스가 최적의 OR 조건을 뽑기 힘들어 인덱스를 사용할 수 없습니다. 

 

3) 복합 인덱스의 컬럼들 

• 인덱스는 인덱스를 구성하는 컬럼 순서대로 정렬된 인덱스 데이터가 만들어지게 됩니다.
• 그래서 인덱스의 선행 컬럼이 조건으로 주어지지 않으면 그 인덱스를 사용할 수 없습니다.
• 후행 컬럼만 where절에 있으면 인덱스가 먹질 않습니다.

4) LIKE 연산에서 시작 문자열로 와일드 카드를 사용

 

5) REGEXP 연산 사용

 

6) 테이블 풀스캔이 인덱스 사용보다 더 효율적인 경우

예를 들어 위와 같은 테이블이 있다고 했을 때

mysql> EXPLAIN SELECT * FROM users WHERE group_name IN ('A','B');
+----+-------+------+------+----------+------+
| id | table | type | key  | key_len | rows |
+----+-------+------+------+----------+------+
| 1  | users | ALL  | NULL | NULL    |298867|
+----+-------+------+------+----------+------+

mysql> EXPLAIN SELECT * FROM users WHERE group_name IN ('C','D');
+----+-------+-------+---------------+----------+------+
| id | table | type  | key          | key_len | rows |
+----+-------+-------+---------------+----------+------+
| 1  | users | range | ix_groupname | 43      | 30   |
+----+-------+-------+---------------+----------+------+

 

위와 같은 실행계획으로 실행됩니다. 이는 A, B의 경우 거의 모든 데이터이니깐 그냥 바로 테이블 페이지를 읽는 것이 효율적이기 때문이고 C, D의 경우 인덱스를 타는 것이 더 빠르기 때문입니다.

 

7) NOT EQUAL 조건과 IS NOT NULL 조건

mysql> EXPLAIN SELECT * FROM users WHERE group_name NOT IN ('A','B');
+----+-------+------+-----------------+---------+-------+
| id | table | type | key            | key_len | rows  |
+----+-------+------+-----------------+---------+-------+
| 1  | users | range| ix_groupname   | 43      | 32    |
+----+-------+------+-----------------+---------+-------+

 

mysql> EXPLAIN SELECT * FROM users WHERE group_name NOT IN ('C','D');
+----+-------+------+-----+---------+--------+
| id | table | type | key | key_len | rows   |
+----+-------+------+-----+---------+--------+
| 1  | users | ALL  | NULL| NULL    | 298867 |
+----+-------+------+-----+---------+--------+

 

mysql> EXPLAIN SELECT * FROM users WHERE dormant_at IS NOT NULL;
+----+-------+------+----------------+---------+-------+
| id | table | type | key           | key_len | rows  |
+----+-------+------+----------------+---------+-------+
| 1  | users | range| ix_dormantat  | 6       | 30    |
+----+-------+------+----------------+---------+-------+

• 많은 사람들이 not equal 조건과 is not null 조건을 쓰면 index를 못 쓴다고 알고 있습니다.
• 하지만 위 사례에선 첫번째는 인덱스 사용, 두 번째는 풀스캔, 세번째도 인덱스 사용하고 있습니다.
• 이처럼 경우에 따라 index를 사용합니다. 이는 데이터 분포도에 따라 다릅니다.
• 위 세개의 차이: C, D의 경우 NOT IN인 경우가 훨씬 많아 선제적으로 full scan을 합니다.
• A, B의 경우 NOT IN 조건이 아닌 경우가 많아서 index를 사용합니다.