인덱스를 사용하기 위한 기본 규칙

[hyeonic]

주제

인덱스를 사용하기 위한 기본 규칙

선정 이유

인덱스는 조회 성능 개선에 많은 도움을 준다. 이러한 인덱스를 적절히 활용하기 위한 규칙에 대해 알아본다.

해당 텍스트

WHERE 절과 ORDER BY 또는 GROUP BY가 인덱스를 사용하기 위해서는 기본적으로 인덱스된 칼럼의 값 자체를 변환하지 않고 그대로 사용해야 한다. 인덱스는 보통 칼럼의 값을 변환 없이 B-Tree에 정렬해서 저장한다.

아래와 같은 예시는 WHERE 절에서 salary 칼럼을 가공한 뒤 상수값과 비교하면 인덱스를 적절히 활용하지 못하게 된다.

SELECT * FROM salaries WHERE salary * 10 > 150000;

위 쿼리는 다음과 같이 간단히 수정하면 유도할 수 있지만 MySQL 옵티마이저에서는 인덱스를 최적으로 이용할 수 있게 표현식을 변환하지 못한다.

SELECT * FROM salaries WHERE salary > 150000 / 10;

만약 복잡한 연산을 수행하거나 MD5()와 같은 함수를 사용할 경우 미리 계산된 값을 저장하도록 MySQL의 가상 칼럼을 추가하고 그 칼럼에 인덱스를 생성하거나 함수 기반의 인덱스를 사용한다.

정리하면 인덱스의 칼럼을 변형해서 비교하는 경우에는 인덱스를 이용할 수 없게 된다는 것을 유의해야 한다.

추가로 WHERE 절에서 사용하는 비교 조건에는 연산자 양쪽의 데이터 타입이 일치해야 한다.

CREATE TABLE tb_test (
    age VARCHAR(10),
    INDEX ix_age(age)
);

INSERT INTO tb_test VALUES ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7');

이제 SELECT 쿼리의 실행 계획을 확인한다.

EXPLAIN SELECT * FROM tb_test WHERE age = 2;

+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tb_test | NULL       | index | ix_age        | ix_age | 33      | NULL |    7 |    14.29 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+

age 칼럼은 인덱싱 되어 있기 때문에 실행 계획의 type 칼럼에 ref나 range가 표시되길 기대하지만 index인 인덱스 풀 스캔을 진행하고 있다. 이러한 이유는 age 칼럼의 데이터 타입과 비교되는 값의 타입이 다르기 때문이다. 결국 문자열 타입인 age가 숫자 타입으로 변환되며 인덱스 레인지 스캔이 불가능해진다. 이것은 아래와 같이 개선이 가능하다.

EXPLAIN SELECT * FROM tb_test WHERE age = '2';

+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tb_test | NULL       | ref  | ix_age        | ix_age | 33      | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+-------------+

관련 페이지

55 ~ 57p