MySQL EXPLAIN 명령어로 ORDER BY 쿼리 성능 분석하기

개요

MySQL에서 ORDER BY 절이 포함된 쿼리의 실행 계획을 분석하려면 EXPLAIN 명령어를 활용할 수 있습니다. EXPLAIN은 쿼리가 인덱스를 사용하는지, 아니면 추가적인 정렬 작업(filesort)이 발생하는지 등을 보여주어 쿼리 최적화에 큰 도움이 됩니다.

이 글에서는 실제 예제 테이블을 만들고, 다양한 ORDER BY 조건에 따라 EXPLAIN 결과가 어떻게 달라지는지 단계별로 살펴보겠습니다.

1. 테이블 생성

먼저 예제로 사용할 테이블을 생성합니다. Id 컬럼은 자동 증가(AUTO_INCREMENT) 기본 키로 설정합니다.

mysql> create table DemoTable606 (Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstName varchar(100));
Query OK, 0 rows affected (0.56 sec)

2. 데이터 삽입

INSERT 명령어를 사용해 몇 개의 레코드를 추가합니다.

mysql> insert into DemoTable606(FirstName) values('John');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable606(FirstName) values('Robert');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable606(FirstName) values('Chris');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable606(FirstName) values('David');
Query OK, 1 row affected (0.13 sec)

3. 저장된 레코드 확인

SELECT 문으로 테이블의 모든 레코드를 조회해 보겠습니다.

mysql> select *from DemoTable606;

실행 결과는 다음과 같습니다.

+----+-----------+
| Id | FirstName |
+----+-----------+
|  1 | John      |
|  2 | Robert    |
|  3 | Chris     |
|  4 | David     |
+----+-----------+
4 rows in set (0.00 sec)

4. EXPLAIN 명령어로 ORDER BY 분석

4-1. 기본 키(Id)만으로 정렬하는 경우

기본 키인 Id 컬럼으로 정렬하면 MySQL은 PRIMARY 인덱스를 그대로 활용합니다. Extra 열이 NULL로 표시되는 것을 확인할 수 있으며, 이는 별도의 정렬 작업 없이 인덱스 순서대로 결과를 읽는다는 의미입니다.

mysql> explain select * from DemoTable606 order by Id;
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| 1  | SIMPLE      | DemoTable606 | NULL       | index | NULL          | PRIMARY | 4       | NULL | 4    | 100.00   | NULL  |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

4-2. Id와 FirstName으로 함께 정렬하는 경우

FirstName에는 인덱스가 없으므로, 두 컬럼을 함께 정렬하면 MySQL이 전체 테이블 스캔(type: ALL)을 수행하고 Using filesort가 나타납니다. filesort는 메모리나 디스크에서 추가 정렬 작업이 이루어진다는 뜻으로, 대량의 데이터에서는 성능 저하의 원인이 됩니다.

mysql> explain select * from DemoTable606 order by Id, FirstName;
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1  | SIMPLE      | DemoTable606 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4    | 100.00   | Using filesort |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

4-3. RAND() 함수를 함께 사용하는 경우

RAND()처럼 매번 값이 달라지는 함수를 정렬 조건에 넣으면 인덱스를 전혀 활용할 수 없습니다. 이 경우 임시 테이블(Using temporary)까지 생성되면서 Using temporary; Using filesort가 동시에 표시됩니다. 가장 비용이 큰 정렬 방식이므로 대용량 테이블에서는 주의해야 합니다.

mysql> explain select * from DemoTable606 order by Id, RAND();
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1  | SIMPLE      | DemoTable606 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4    | 100.00   | Using temporary; Using filesort |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

정리

EXPLAIN 결과에서 Extra 열을 확인하면 ORDER BY 쿼리의 효율성을 빠르게 판단할 수 있습니다.

Extra: NULL — 인덱스 순서대로 읽어 별도 정렬이 필요 없는 가장 이상적인 경우입니다.
Using filesort — 인덱스를 활용하지 못하고 추가 정렬 작업이 발생하는 경우입니다.
Using temporary; Using filesort — 임시 테이블 생성과 정렬이 모두 일어나는 가장 비효율적인 경우입니다.

따라서 ORDER BY에 사용하는 컬럼에 적절한 인덱스를 설계하고, RAND() 같은 비결정적 함수의 사용을 최소화하면 쿼리 성능을 크게 향상시킬 수 있습니다.