Last Updated on 12월 28, 2025 by Jade(정현호)
안녕하세요
이번글은 MySQL에서 SQL을 실행할 때 ORDER BY 또는 GROUP BY 절에 지정된 컬럼과 LIMIT 을 같이 사용시, MySQL 옵티마이저가 예상과 다른 PK 또는 Secondary Index를 선택하는 상황에 대해서 다루고 있습니다.
이 동작은 optimizer_switch의 prefer_ordering_index 플래그의 영향을 받을 수 있으며, 일부 경우에는 이 최적화가 의도와 다르게 성능을 크게 떨어뜨릴 수 있는 상황에 대해서 알아보려고 합니다.
이글은 The Infamous ORDER BY LIMIT Query Optimizer Bug 글을 참고하여 정리 및 내용 일부를 번역 진행한 내역입니다.
Contents
개요
글을 본격적으로 시작하기 앞서서 간단하게 글에서 설명하고자 하는 기본 내용을 설명을 해보려고 합니다.
id, col1, col2 컬럼을 가진 테이블 m 가 있습니다.
id 컬럼의 기본 키(PRIMARY KEY, PK) 이며,id = 1 부터 해서 10까지 해서 10건의 데이터가 존재합니다. Secondary Index 는 col2, col1 컬럼으로 생성되어 있습니다.
다음과 같은 쿼리를 실행한다고 가정하고 실행계획을 확인해보겠습니다.
SELECT * FROM m WHERE col2 = 'c' ORDER BY id LIMIT 1;
explain SELECT * FROM m WHERE col2 = 'c' ORDER BY id LIMIT 1; +----+-------------+-------+------+---------------+---------+-------+------+----------+-----------------------------+ | id | select_type | table | type | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+---------+-------+------+----------+-----------------------------+ | 1 | SIMPLE | m | ref | idx_col2_col1 | 4 | const | 3 | 100.00 | Using index; Using filesort | +----+-------------+-------+------+---------------+---------+-------+------+----------+-----------------------------+
이 실행 계획에서 보듯, col2='c' 조건으로 보조 인덱스 idx_col2_col1 을 사용하지만, ORDER BY id 가 필요하기 때문에 보조 인덱스 순서와 맞지 않아 결과 정렬(filesort)이 발생 합니다.
순서대로 행을 읽는 것이 정렬되지 않은 보조 인덱스 조회 후 정렬하는 것보다 빠를 수 있습니다. 이 최적화를 적용하면(PK를 사용하면) 새로운 쿼리 실행 계획은 다음과 같습니다.
-- 힌트를 사용하여 PK를 사용하도록 유도 explain SELECT /*+ index(m primary) */ * FROM m WHERE col2 = 'c' ORDER BY id LIMIT 1; +----+-------------+-------+-------+---------+---------+------+------+----------+-------------+ | id | select_type | table | type | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | m | index | PRIMARY | 4 | NULL | 1 | 10.00 | Using where | +----+-------------+-------+-------+---------+---------+------+------+----------+-------------+
이 실행계획은 WHERE 조건에 맞는 보조 인덱스 대신 ORDER BY에 맞는 PK를 사용한 것으로, 순차 스캔하면서 바로 정렬된 결과를 얻을 수 있어 filesort 없이도 빠른 것처럼 보입니다. 하지만 실제로는 반대의 상황도 발생할 수 있습니다.
PK 인덱스 스캔이 오히려 보조 인덱스 + filesort보다 훨씬 느려지는 경우가 있는데, 이번 글에서는 바로 이러한 의도와 반대되는 최적화 실패 사례를 자세히 살펴보겠습니다.
Jeremy’s Test Case
먼저 테스트 케이스 내용을 알아보기 위해서 테스트 테이블 생성 및 데이터를 입력을 해보도록 하겠습니다.
jcole-1M.sql.gz 데이터 덤프 파일을 다운로드 받아서 MySQL 데이터베이스에 로드를 합니다.(해당 파일은 hackmysql.com 에서 제공되는 파일입니다)
이 테스트 케이스는 여러 많은 MySQL DBA나 전문가분들이 블로그 등에서 다뤘던 내용입니다. 그 중 한 명인 제레미 콜(Jeremy Cole)의 블로그 글 '인덱스 순서 접근을 위한 접근 경로 재고(Reconsidering access paths for index ordering)' 내용입니다.
테스트용 덤프파일에는 t 라는 테이블 하나이고, 총 100만 행이 들어 있습니다. other_id 컬럼은 1000행마다 같은 값이 한 번씩 나오는 식으로 균등하게 분포되도록 만들어져 있습니다.
예를 들어 other_id=555 인 행이 테이블 전체에 고르게 퍼져 있고, 총 1000행이 이 값에 해당하도록 세팅된 것입니다. 또한 (other_id, covered_column) 조합으로 보조(Secondary) 인덱스가 하나가 생성되어있습니다.
이 테스트를 하기 전에 통계정보가 올바르게 반영되도록 ANALYZE TABLE t; 를 먼저 수행합니다.
Jeremy’s Test Case(제레미의 테스트 케이스)는 오래된 MySQL 옵티마이저 특정을 보여주기 위한 예제입니다.
LIMIT 1 을 수행하는 다음의 쿼리의 EXPLAIN ANALYZE 결과를 확인해보도록 하겠습니다.
mysql> EXPLAIN ANALYZE SELECT non_covered_column FROM t WHERE other_id=555 ORDER BY id LIMIT 1;
| -> Limit: 1 row(s) (cost=0.25 rows=0.999) (actual time=0.213..0.213 rows=1 loops=1)
-> Filter: (t.other_id = 555) (cost=0.25 rows=0.999) (actual time=0.212..0.212 rows=1 loops=1)
-> Index scan on t using PRIMARY (cost=0.25 rows=996) (actual time=0.0497..0.188 rows=555 loops=1)
3번째 줄을 보면 먼저 Primary Key(PK) 를 사용하였음을 알 수 있고, "rows=996"은 MySQL이 일치하는 첫 번째 행을 찾기 위해 996개의 행을 스캔해야 한다고 예상했음을 의미하지만, 그 다음의 "rows=555"는 실제로 555개의 행만 스캔했음을 의미합니다. 즉, 예상보다 덜 읽고도 원하는 결과를 얻었으니, 이 최적화가 잘 된 경우입니다.
2번째 줄은 other_id=555인 행을 필터링하는 부분이고, 3번째 줄은 LIMIT 1입니다. 2번째 줄과 3번째 줄에서 "rows=1"을 볼 수 있는데, 이는 최적화가 작동하고 있음을 보여주는 결과입니다.
여기에서 MySQL이 996개 행으로 추정한 이유는 무엇일까요?
other_id=555와 일치하는 행은 1000개이므로, 대략적인 평균 간격(100만 / 1000 = 1000)으로 계산했을 때, LIMIT 1을 위해 평균 1000행 쯤 읽을 거라고 보는 겁니다. 추정이라 996 정도는 "대충 1000"으로 보면 됩니다.
다만, 여기서 중요한것은 정말 실제 실행 결과는 555행만에 매칭을 찾았고, 그 뒤는 더 안 읽습니다. 그래서 ORDER BY에 맞춰 정렬된 기본 키(PRIMARY KEY) 인덱스를 순차적으로 스캔하는 전략이 이 경우에는 상당히 유리하게 작용했습니다.
정말 PK를 사용하는 것이 빠른것이 맞을까요? 다음과 같이 일반 인덱스를 사용하는 쿼리를 강제로 실행해보겠습니다.
mysql> EXPLAIN ANALYZE
SELECT non_covered_column
FROM t FORCE INDEX (index_other_id_covered_column)
WHERE other_id=555 ORDER BY id ASC LIMIT 1;
| -> Limit: 1 row(s) (cost=350 rows=1) (actual time=3.79..3.79 rows=1 loops=1)
-> Sort: t.id, limit input to 1 row(s) per chunk (cost=350 rows=1000) (actual time=3.79..3.79 rows=1 loops=1)
-> Index lookup on t using index_other_id_covered_column (other_id=555) (cost=350 rows=1000) (actual time=0.381..3.7 rows=1000 loops=1)
처음 실행한(PK를 사용한) 쿼리에서는 0.213ms가 소요되었고, 두 번째(보조 인덱스 + filesort) 쿼리에서는 3.79ms가 소요되었습니다. 이번 경우 에는 PK 인덱스 스캔이 약 17배 빠르지만, 모든 경우가 그런 것은 아닙니다.
비균등 데이터 분포로 인한 문제 케이스 재현
문제 상황을 재현하기 위해서 우선 위에서 로드한 테이블 t 에 다음과 같이 데이터를 갱신합니다.
UPDATE t
SET id=id+1000000
WHERE other_id=555;
위의 업데이트 구문은 테이블 t 에서 other_id=555 인 1,000개 행에 대해서 id 값을 id+1000000 으로 증가시킵니다.
행 수는 여전히 1,000,000개지만, other_id=555 인 1,000개 행은 PK 인덱스의 끝부분으로 이동되게 됩니다.
이제 다음의 2개의 쿼리에 대해서 EXPLAIN ANALYZE 실행해보겠습니다.
SQL1
mysql> EXPLAIN ANALYZE
SELECT non_covered_column
FROM t
WHERE other_id=555
ORDER BY id ASC
LIMIT 1;
| -> Limit: 1 row(s) (cost=0.25 rows=0.999) (actual time=316..316 rows=1 loops=1)
-> Filter: (t.other_id = 555) (cost=0.25 rows=0.999) (actual time=316..316 rows=1 loops=1)
-> Index scan on t using PRIMARY (cost=0.25 rows=996) (actual time=0.0531..274 rows=999001 loops=1)
SQL2
mysql> EXPLAIN ANALYZE
SELECT non_covered_column
FROM t
WHERE other_id=555
ORDER BY id ASC
LIMIT 20;
| -> Limit: 20 row(s) (cost=350 rows=20) (actual time=1.22..1.22 rows=20 loops=1)
-> Sort: t.id, limit input to 20 row(s) per chunk (cost=350 rows=1000) (actual time=1.22..1.22 rows=20 loops=1)
-> Index lookup on t using index_other_id_covered_column (other_id=555) (cost=350 rows=1000) (actual time=0.149..1.13 rows=1000 loops=1)
SQL1 - LIMIT 1: 316 ms
SQL2 - LIMIT 20: 1.22 ms
실행 속도에서 약 259배 차이가 납니다.
LIMIT 1 실행한 쿼리에 대해서는 EXPLAIN ANALYZE 수행 결과에서 명확히 그 이유를 확인할 수 있습니다.
-> Index scan on t using PRIMARY (cost=0.25 rows=996) (actual time=0.0531..274 rows=999001 loops=1)
예상 행 수는 "rows=996" 이지만, 실제로 검색한 행 수는 "rows=999001"입니다.
MySQL은 첫 번째 일치하는 행이 테이블 시작 부분에서 약 1,000개 행 떨어진 곳에 있을 것이라고 예상했지만, UPDATE 문 때문에 실제로는 테이블 끝 부분(시작 부분에서 999,001 번째 행)에 있었습니다.
MySQL은 LIMIT 1에서 PRIMARY KEY 인덱스 순차 스캔 전략을 사용하지만, 그 가정(첫 번째 매칭 행이 테이블 시작부터 약 1,000행 정도 떨어져 있을 것)이 실제와 다르게 판명나서 쿼리가 느려집니다.
반대로 LIMIT 20에서는 보조 인덱스(index_other_id_covered_column) 를 사용한 수행(실행계획)을 하기 때문에 결과적으로 LIMIT 1 보다 빠른 수행 속도를 보입니다.
이제 여기서 두 가지 중요한 쟁점을 짚어볼 수 있습니다.
1. MySQL이 데이터가 균일하게 분포되어 있다고 가정하는 것
2. LIMIT 값이 MySQL이 이 쿼리 최적화를 사용하도록 유도하는 방식/이유
다만, 이 판단을 내리는 알고리즘은 모든 경우를 완벽하게 처리할 수는 없습니다. 부분적으로 데이터 분포가 항상 균일하다는 가정을 기반으로 하기 때문입니다.
따라서 위의 테스트 케이스 처럼 데이터가 한쪽 끝으로 몰려 있는 상황에서는, 이 최적화가 오히려 바람직하지 않을 수 있습니다. 이런 경우에는 prefer_ordering_index 플래그를 off로 설정하여 해당 최적화를 비활성화할 수 있습니다. 또는 Optimizer Hints 를 사용하여 특정 쿼리에 대해서만 이 최적화 끄거나 더 효율적인 인덱스를 사용하도록 유도할 수도 있습니다.
과거 MySQL 특정 버전 이전에는 이 최적화를 끌 수 없었지만, 이후 버전부터는 optimizer_switch 시스템 파라미터에서prefer_ordering_index의 플래그로 제어할 수 있습니다.
- MySQL 5.7.33 이상
- MySQL 8.0.21 이상
위의 버전부터는 세션 또는 글로벌 레벨로 비활성화할 수 있습니다.
다만, SQL 레벨(힌트)나 세션 레벨이 아닌 글로벌 레벨로 변경시에 당연히 데이터베이스의 모든 SQL에 영향을 줄수 있으며 좋아지는 SQL이 있을 수도 있지만 성능이 저하되는 SQL도 있을수 있으니 글로벌로 적용 보다는 문제가 되는 SQL 단위나 트랜잭션(세션) 단위에서의 변경 적용을 고려해보시는게 조금 더 안전하게 운영할 수 있다고 생각됩니다.
데이터 분포 관련
MySQL 쿼리 옵티마이저는 기본적으로 데이터가 균일하게 분포(uniformly distributed)되어 있다고 가정합니다.
Jeremy의 테스트 케이스에서도 UPDATE 이전에는 other_id=555와 매칭되는 행들이 테이블 시작부터 끝까지 1,000행마다 한 번씩 균일하게 분포되어 있습니다. 이 가정이 맞을 때(데이터가 실제로 균일하게 분포되어 있을 때), "prefer_ordering_index" 최적화는 성능을 향상시킬 수 있습니다.
하지만 MySQL이 이 최적화를 사용할지 말지 결정하는 비용 계산에는 여러 요소가 관여합니다.
- select limit: LIMIT n의 n
- refkey rows: 원래 선택된 인덱스가 매칭하는 행 수
- 원래 선택된 인덱스를 사용하는 읽기 비용(read cost)
- table rows: 테이블 전체 행 수
- Records per key: 인덱스 키당 행 수
- 다양한 접근 비용들
이러한 요소들을 이용해 쿼리 옵티마이저는 인덱스 스캔 비용을 계산합니다. 만약 이 인덱스 스캔 비용이 원래 선택된 인덱스를 사용하는 읽기 비용보다 낮다면, 다른 인덱스 스캔을 사용하게 됩니다.
인덱스 스캔 비용 계산은 대략(roughly) 다음과 같습니다.
x = table rows / refkey rows * select limit
index scan cost = x / records per key * access cost
여기서 중요한 것은 x 입니다. 행들이 균일하게 분포되어 있다고 가정하고, other_id=555가 1,000행(refkey rows)과 매칭된다면, LIMIT 1의 첫 번째 매칭 행은 테이블(주 키) 시작부터 x = 1,000,000 / 1,000 * 1 = 1,000행 떨어져 있을 것입니다.
마찬가지로 LIMIT 2는 2,000행으로 증가하고, LIMIT 3은 3,000행으로 선형 증가하게 됩니다.
이 x가 커질수록 PK를 이렇게 많이 스캔하는 비용이 커지기 때문에, LIMIT 1에서는 index scan(PK 스캔) 을 선택하다가, LIMIT 2 이상에서는 비용(cost)이 높아져 옵티마이저가 다시 원래 인덱스 + filesort 기반 실행 계획을 선택할 수 있습니다.
개발자나 DBA에게 중요한점은 SQL의 이러한 수행이 LIMIT n의 n 값, 참조 키(원래 선택한 인덱스)에 대해 일치하는 행의 예상 개수, 그리고 테이블의 전체 행 수에 따라 발생할 수도 있고 발생하지 않을 수도 있다는 것입니다.
즉, 위의 예시와 같은 상황이 테이블 전체 수 및 데이터가 어디에 위치해있는지 등의 분포에 따라서 상황을 맞닥뜨리다가도 하고, 그렇지 않을 수도 있다는 점입니다.
모든 것은 변하게 된다
위에서 설명한 내용과 같이 이러한 현상은 시간이 지나면서 나타났다가 사라질 수 있습니다. 왜냐하면 데이터베이스를 사용하면서 시간이 지나면서 다양한 모든 것이 변할수 있기 때문입니다.
- 테이블이 성장하거나 축소될 가능성이 큽니다
- 참조 키에 대한 매칭 행 수가 변할 수 있습니다
- LIMIT n의 n 값이 변수일 수 있습니다 (예: 페이징)
- MySQL 버전 업그레이드 등
이러한 변화들 때문에 쿼리가 작성되고 초기에 수행되는 시점에는 문제가 없을수도 있고, 정상적으로 사용하였던 쿼리가 나중에서야 문제가 발생할 수도 있어서 계속적인 Slow Query 등의 모니터링이 필요합니다.
첫 번째 포인트에 대해 조금 더 설명하자면, 보통은 "prefer_ordering_index 최적화가 적용될 때 성능이 나빠진다"고 생각할 수 있습니다.
그런데 이와 반대되는 상황도 발생할 수 있습니다. 워크로드가 이 최적화에 의존하고 있는데, 어느 순간부터 MySQL이 이 최적화를 더 이상 사용하지 않으면서 성능이 떨어지는 경우입니다.
예를 들어 어떤 테이블과 쿼리에 대해 MySQL이 이 최적화를 선택하고 있고, 보조 인덱스를 사용하는 실행 계획보다 실제로 더 빠르게 동작하고 있다고 가정해 봅시다.
시간이 지나면서 테이블에는 새로운 행이 계속 추가되지만, WHERE 조건에 매칭되는 행의 개수는 변하지 않습니다.
그러다가 어느 순간 테이블 크기가 커지면 비용 계산 결과가 달라지고, 어느 순간 MySQL이 더 이상 이 쿼리 최적화를 선택하지 않게 됩니다.
그러면 그때부터 이 쿼리는 예전보다 느려질 수 있습니다. 이러한 변화는 실제로도 재현해 볼 수 있으며 다음의 절차로 확인할 수 있습니다.
1. 기존에 생성한 t 테이블을 삭제합니다.
DROP TABLE IF EXISTS t;
2. jcole-1M.sql 다시 로드합니다.
3. other_id=555 없는 +100만 행 추가 삽입합니다.
insert 구문을 수행하기전에 다음과 같이 id 컬럼을 AUTO_INCREMENT로 변경합니다.
alter table t modify column `id` bigint NOT NULL AUTO_INCREMENT;
이제 insert 구문을 수행합니다.
INSERT INTO t (other_id, non_covered_column,covered_column)
SELECT
999999, -- 555가 아닌 아무 값
CONCAT('extra-row-', seq),
CONCAT('extra-row-', seq)
FROM (
SELECT @row := @row + 1 AS seq
FROM (SELECT 0 FROM information_schema.COLUMNS a join information_schema.COLUMNS b LIMIT 1000000) AS x,
(SELECT @row := 0) AS vars
) AS t1;
-- 입력 후 전체 카운트
select count(*) from t;
+----------+
| count(*) |
+----------+
| 2000000 |
+----------+
-- 입력 후 other_id=555 에 대해서 카운트
-- 100만건 추가로 인해 other_id=555 행 수는 변하지 않음
select count(*) from t
where other_id=555;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
4. 통계정보를 갱신합니다.
ANALYZE TABLE t;
5. LIMIT 1 vs LIMIT 20의 EXPLAIN 플랜과 쿼리 시간 확인합니다.
LIMIT 1
mysql> EXPLAIN ANALYZE
SELECT non_covered_column
FROM t
WHERE other_id=555
ORDER BY id ASC
LIMIT 1;
| -> Limit: 1 row(s) (cost=350 rows=1) (actual time=3.79..3.79 rows=1 loops=1)
-> Sort: t.id, limit input to 1 row(s) per chunk (cost=350 rows=1000) (actual time=3.79..3.79 rows=1 loops=1)
-> Index lookup on t using index_other_id_covered_column (other_id=555) (cost=350 rows=1000) (actual time=0.383..3.69 rows=1000 loops=1)
LIMIT 20
mysql> EXPLAIN ANALYZE
SELECT non_covered_column
FROM t
WHERE other_id=555
ORDER BY id ASC
LIMIT 20;
| -> Limit: 20 row(s) (cost=350 rows=20) (actual time=6.96..6.96 rows=20 loops=1)
-> Sort: t.id, limit input to 20 row(s) per chunk (cost=350 rows=1000) (actual time=6.96..6.96 rows=20 loops=1)
-> Index lookup on t using index_other_id_covered_column (other_id=555) (cost=350 rows=1000) (actual time=0.296..5.51 rows=1000 loops=1)
위의 테스트 케이스에서 중요한 점은 이전(테이블이 100만행이었을때)에 PK 스캔을 선택했던 쿼리가 더 이상 PK 스캔을 사용하지 않고 보조 인덱스 + filesort 플랜으로 바뀌었다는 것입니다.
UPDATE 이전(100만 행)에는 LIMIT 1에서 PK 스캔이 선택되어 0.213ms로 매우 빠르게 동작했습니다.
하지만 테이블이 200만 행으로 커진 지금은 PK 스캔 비용이 높아져 보조 인덱스 사용하는 실행계획으로 변경되었고 수행 시간도 3.79ms으로 기존보다 느려졌습니다.
이는 "최적화에 의존하던 쿼리가 테이블 성장으로 인해 갑자기 느려지는" 역전 현상을 정확히 보여줍니다.
다음과 같이 SQL 힌트를 통해서 기존과 동일하게 Primary Key 스캔 하도록 수행해보도록 하겠습니다.
-- Hint 사용
mysql> EXPLAIN ANALYZE
SELECT /*+ index(t primary) */ non_covered_column
FROM t
WHERE other_id=555
ORDER BY id ASC
LIMIT 1;
| -> Limit: 1 row(s) (cost=57.7 rows=0.1) (actual time=0.433..0.433 rows=1 loops=1)
-> Filter: (t.other_id = 555) (cost=57.7 rows=0.1) (actual time=0.432..0.432 rows=1 loops=1)
-> Index scan on t using PRIMARY (cost=57.7 rows=1) (actual time=0.199..0.406 rows=555 loops=1)
SQL 힌트로 강제로 PK 스캔을 적용한 결과, 실행 시간은 0.433ms로 자동 선택된 보조 인덱스 플랜(3.79ms)보다 약 9배 빠르게 동작합니다. 여기서 핵심은 100만 행일 때는 PK 스캔을 선택했던 옵티마이저가, 200만 행으로 테이블이 커진 지금은 PK 스캔을 선택하지 않았다는 점입니다.
table_rows만 2배 증가했기 때문에 x값(예상 스캔 행 수)이 2배 증가하여, 기존 100만 건일 때는 PK를 선택했지만 200만 건으로 변경되었을 때는 옵티마이저가 PK 스캔을 포기한 것으로 예상할 수 있습니다. 하지만 실제 실행해보니 PK 스캔이 Secondary 인덱스 스캔보다 빠른 결과를 보여주었습니다.
prefer_ordering_index 옵티마이저 플래그 주의점
위에서 살펴본것과 같은 상황들은 다음과 같은 이유로 시간이 지나면서 나타나거나 사라질 수 있습니다.
- 테이블이 커지거나 작아질 가능성
- 참조 키에 매칭되는 행 수가 변할 수 있음
- LIMIT n 값이 가변적일 수 있음 (페이징)
- MySQL 업그레이드 시
이러한 상황들로 인하여 prefer_ordering_index 최적화를 사용하지 않는 것을 고려할 수 도 있습니다.
하지만, prefer_ordering_index 플래스를 비활성화하는데 있어서 주의 할 점이 있습니다. 이 기능을 비활성화 하는데 있어서 특정 버전에서는 비활성화 하였을때 결함(bug)를 만날수 있습니다.
비활성화시 PK 인덱스 스캔을 하지 않고 Full Table Scan(테이블 스캔)으로 선택하는 결함(bug)가 있습니다.
MySQL 8.0.42
8.0.42 버전에서는 다음과 같이 비활성화 하였어도 동일한 실행계획으로 수행됨을 확인할 수 있습니다.
mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.42 | +-----------+ mysql> EXPLAIN SELECT non_covered_column FROM t ORDER BY id LIMIT 1; +----+-------------+-------+-------+---------+---------+------+------+----------+-------+ | id | select_type | table | type | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------+---------+---------+------+------+----------+-------+ | 1 | SIMPLE | t | index | PRIMARY | 8 | NULL | 1 | 100.00 | NULL | +----+-------------+-------+-------+---------+---------+------+------+----------+-------+ mysql> SET session optimizer_switch = "prefer_ordering_index=off"; mysql> EXPLAIN SELECT non_covered_column FROM t ORDER BY id LIMIT 1; +----+-------------+-------+-------+---------+---------+------+------+----------+-------+ | id | select_type | table | type | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------+---------+---------+------+------+----------+-------+ | 1 | SIMPLE | t | index | PRIMARY | 8 | NULL | 1 | 100.00 | NULL | +----+-------------+-------+-------+---------+---------+------+------+----------+-------+
MySQL 8.0.39
8.0.39 이하 버전에서는 다음과 같이 플래그를 비활성화 하였을때 PK 스캔을 하지 않고 Full Table Scan 을 수행하는 상황이 발생될 수 있습니다.
mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.39 | +-----------+ mysql> EXPLAIN SELECT non_covered_column FROM t ORDER BY id LIMIT 1; +----+-------------+-------+------+---------+---------+------+------+----------+-------+ | id | select_type | table |type | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+---------+---------+------+------+----------+-------+ | 1 | SIMPLE | t |index | PRIMARY | 8 | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------+---------+---------+------+------+----------+-------+ mysql> SET session optimizer_switch = "prefer_ordering_index=off"; mysql> EXPLAIN SELECT non_covered_column FROM t ORDER BY id LIMIT 1; +----+-------------+-------+------+------+---------+------+--------+----------+----------------+ | id | select_type | table | type | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | 996925 | 100.00 | Using filesort | +----+-------------+-------+------+------+---------+------+--------+----------+----------------+ <!=== 문제 실행계획
이 결함(Bug)은 8.0.39 버전 이하에서 발견되었으며, 8.0.40 버전에서 수정되었습니다.
자세한 내용은 여기 링크를 참조하세요.
Conclusion
이번 글에서 살펴본 사례는 ORDER BY ... LIMIT 쿼리 수행시 MySQL 어떠한 옵티마이저의 선택애 의해서 LIMIT 1 이 LIMIT 20 보다 느려지고, 심지어 테이블 스캔 수준의 비용을 유발할 수 있는지를 잘 보여줍니다.
특히 prefer_ordering_index 최적화는 "정렬 인덱스를 이용하면 더 빠를 것"이라는 가정 위에서 동작하는데, 데이터 분포가 비균등해지는 순간 이 가정이 깨지면서 느려지는 실행 계획을 만들어낼 수 있습니다.
중요한 점은, 이 문제는 고정된 것이 아니라 "시간에 따라 나타났다 사라졌다 할 수 있다"는 것입니다.
테이블 크기, 매칭되는 행 수, LIMIT 값, MySQL 버전 업그레이드 같은 요소가 모두 영향을 주기 때문에, 오늘은 괜찮던 플랜이 내일은 갑자기 다른 방향으로 바뀌어 성능 문제를 일으킬 수 있습니다.
심지어 어떤 워크로드는 prefer_ordering_index 에 의존해 더 빠르게 동작하다가, 테이블이 커지면서 옵티마이저가 이 최적화를 더 이상 선택하지 않게 되어 성능이 나빠지는 역전 현상도 발생할 수 있습니다.
정리하면 마지막 의견으로 이러한 경우의 쿼리는 항상 달라질수 있기 때문에 특정 인덱스를 사용하는 것이 맞다는 정답이 있다는 관점보다 상황에 따라서 적절하게 옵티마이저 힌트 등으로 성능 개선 및 보정이 필요한 상황이라고 정리 할 수 있을 것 같습니다.
Reference
Idea provider
항상 좋은 주제를 제공해주시고 같이 고민해주신 위버스(Weverse) DBA 우상철님 감사드립니다.
Reference Link
• infamous-order-by-limit-query-optimizer-bug
• bugs.mysql.com/113699
다른 연관된 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
