[기초] MySQL 성능 최적화: 실행 계획에서 type을 이해하고 개선하기

MySQL의 쿼리 성능을 최적화하기 위해서는 실행 계획을 분석해야 하는데 특히 실행 계획의 type 컬럼은 MySQL이 쿼리를 처리하는 방식 즉 데이터에 접근하는 방법을 보여줍니다.

이 정보를 활용하면 성능을 최적화할 수 있는 중요한 힌트를 얻을 수 있기 때문에 성능 최적화 첫 단계로 이번 글에서는 type 컬럼에 표시될 수 있는 12가지 값 중 5개의 대표 type을 소개하고 각 값이 성능에 미치는 영향을 분석해 보겠습니다.

1. const

const는 가장 효율적인 검색 방법으로 MySQL이 테이블에서 단일 행을 조회하는 경우입니다.

• Unique 속성을 가진 Index일 경우에만 가능합니다.

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

EXPLAIN SELECT * FROM USERS WHERE ID = '1';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

EXPLAIN SELECT * FROM USERS WHERE EMAIL = 'test@gmail.com';
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | const | email         | email | 403     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-------+

2. ref

Unique 속성이 없는 인덱스를 사용하여 특정 값에 대한 검색을 수행하는 경우 입니다.

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

CREATE INDEX index_name ON users(name);

explain select * from users where name = 'TEST_1';
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ref  | index_name    | index_name | 203     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+

3. range

Unique 속성과 상관없이 인덱스를 사용하여 범위 검색을 수행하는 경우입니다.

• BETWEEN, >, <, >=, <=와 같은 조건을 사용할 때입니다.

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100) UNIQUE
);

CREATE INDEX index_age ON users(age);

EXPLAIN SELECT * FROM users WHERE age BETWEEN 15 AND 25;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | users | NULL       | range | index_age     | index_age | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+

4. index

index는 인덱스를 전체적으로 스캔하는 방식이며 인덱스를 사용하여 데이터를 검색하지만 모든 인덱스를 스캔해야 할 때 발생합니다.

• FORCE INDEX를 사용하면 index 방식이 강제될 수 있습니다.

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100) UNIQUE
);

EXPLAIN SELECT * FROM users FORCE INDEX (email) ORDER BY email;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | index | NULL          | email | 403     | NULL |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------+

5. all

all은 인덱스가 설정되지 않은 열에 대해 데이터를 검색하는 방식입니다.

이 방식은 전체 테이블을 스캔해야 하므로 성능에 큰 영향을 미칠 수 있습니다.

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100) UNIQUE
);

EXPLAIN SELECT * FROM users where age = 30;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

결론

각각의 type은 MySQL이 데이터를 검색하는 방식을 나타냅니다. 성능 최적화를 위해서는 가장 기본적으로 const, ref, range와 같은 효율적인 접근 방식을 사용하는 것이 중요합니다. 반면, index와 ALL은 성능에 악영향을 줄 수 있으므로, 적절한 인덱스를 추가하거나 쿼리 조건을 최적화하여 이러한 type을 피하는 것이 좋습니다.