Published 2023. 8. 10. 21:44

MySQL rand() SubQuery 실행 계획

문제

만약 아래 쿼리를 돌리면 결과는 어떻게 나올까?

나는 랜덤으로 사용자를 1명 뽑아서 그 사용자 1명을 조회할 줄 알았다.

user table

id	name
1	name1
2	name2
3	name3

select *
from users
where id = (
    select id
    from users
    order by rand()
    limit 1
);

-- 애초에 쿼리를 아래와 같이 쓰면 되지 않나라고 생각할 수 있지만 이건 실행 계획을 이해하기 위한 예제일 뿐이고,
-- 내가 실제로 겪은 문제는 다른 테이블 간 조인이 걸려 있어서 이렇게 할 수 없는 상황이었고,
-- 이로 인해 rand() 서브 쿼리에 대한 실행 계획을 알기 위함에 이외 같은 예제를 선택했다.
select *
from users
order by rand()
limit 1;

하지만 결과는 랜덤하게 0 ~ 3개까지 나올 수 있다.

그 이유를 지금부터 실행 계획을 통해 하나씩 알아보자.

본론

실행 계획 결과(표)

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	users	null	ALL	null	null	null	null	3	100	Using where
2	UNCACHEABLE SUBQUERY	users	null	index	null	PRIMARY	4	null	3	100	Using where;
Using temporary;
Using filesort

실행 계획 컬럼

id: 단지 select 쿼리별로 부여되는 식별자이다.

테이블 접근 순서와 실행 계획 id의 순서는 아니다.
테이블 접근 순서를 확인하려면 explain analyze를 사용하면 아래와 같이 볼 수 있다.

  -> Filter: (users.id = (select #2))  (cost=0.55 rows=3) (actual time=0.0841..0.0841 rows=0 loops=1)
      -> Table scan on users  (cost=0.55 rows=3) (actual time=0.0317..0.033 rows=3 loops=1)
      -> Select #2 (subquery in condition; uncacheable)
          -> Limit: 1 row(s)  (actual time=0.013..0.0131 rows=1 loops=3)
              -> Sort: rand(), limit input to 1 row(s) per chunk  (actual time=0.0126..0.0126 rows=1 loops=3)
                  -> Stream results  (cost=0.55 rows=3) (actual time=0.00401..0.00674 rows=3 loops=3)
                      -> Covering index scan on users using PRIMARY  (cost=0.55 rows=3) (actual time=0.00268..0.00496 rows=3 loops=3)

select_type: selcet 쿼리가 어떤 타입인지 보여준다.
- SIMPLE: UNION, 서브 쿼리를 사용하지 않는 단순한 select 쿼리. 단 하나만 존재한다.
- PRIMARY: UNION이나 서브 쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽 쿼리를 의미한다. 단 하나만 존재한다.
- DERIVED: from절에 사용된 서브 쿼리
- SUBQUERY: from절 이외에서 사용되는 서브쿼리만을 의미한다. 값을 캐싱하여 사용한다.
  - 바깥 쿼리에 영향을 받지 않으므로 캐싱하여 사용할 수 있는 것이다.
- DEPENDENT SUBQUERY: 서브쿼리가 바깥쪽 select 쿼리에서 정의된 컬럼을 사용하는 경우
  - 캐시는 되지만 SUBQUERY처럼 딱 한 번만 캐시되지는 않고, 외부 쿼리의 값 단위로 캐시가 만들어지는 방식이다.
- UNCACHEABLE SUBQUERY: 서브 쿼리가 캐싱되지 않은 경우이며, 아래 세 가지 경우에 발생한다.
  - 사용자 변수가 서브쿼리에 사용된 경우
  - NOT_DETERMINSTIC 속성의 스토어드 루틴이 서브쿼리 내에 사용된 경우
  - UUID()나 RNAD()와 같이 결과값이 호출할 때마다 달라지는 함수가 서브쿼리에 사용된 경우
type: 어떤 방식으로 테이블을 읽었는지.
- const: pk나 유니크 키로 where 조건절을 가지고, 반드시 1건을 반환하는 쿼리 처리 방식
- eq_ref: 조인에서 첫 번째 읽은 테이블의 컬럼값을 이용해 두 번째 테이블을 pk나 유니크 키로 동등 조건 검색(반드시 1건만 반환)
```
  select *
  from dept_emp de,
       employees e
  where e.emp_no=de.emp_no
  and de.dept_no='d005'
```
- ref: 조인 순서와 인덱스 종류에 관계없이 동등 조건으로 검색할 때 사용된다. 반드시 결과가 1건이라는 보장이 없을 경우
```
  select *
  from dept_emp
  where dept_no='d005';
```
- range: 인덱스를 하나의 값이 아닌 범위 검색을 의미한다. 주로 >, <, IS NULL 등과 같다. 모든 쿼리가 이 방법을 채택해도 최적의 성능이 보장된다.
- index: 인덱스 풀 스캔을 의미한다. 즉, 인덱스를 효율적으로 사용한 것이 아니다.
  - 테이블 풀 스캔 방식과 레코드 건수는 동일하다.
  - 하지만 인덱스는 보통 데이터 파일 전체보다는 크기가 작으므로 테이블 풀스캔 보다는 빠르고,
    정렬된 인덱스의 장점을 이용할 수 있어서 테이블 풀 스캔 보다는 훨씬 효율적이다. 그러나 결국 풀 스캔.
    만약 limit 10과 같이 사용한다면 효율적이다. 인덱스를 거꾸로 읽어서 10개만 가져오면 되기 때문에.
  - 세 가지 중 1번 + 2번 or 1번 + 3번 조건이 충족할 때 채택된다.
    - range나 const, ref 같은 방식 불가인 경우
    - 인덱스에 포함된 컬럼만으로 처리할 수 있는 쿼리인 경우(즉, 데이터 파일을 읽지 않아도 되는 경우) ????
    - 인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우(즉, 별도의 정렬 작업을 피할 수 있는 경우)
- ALL: 테이블 풀 스캔
possible_keys: 옵티마이저가 인덱스를 사용할법 했던 목록일 뿐이기 때문에 무시해라.
key: 실제 사용된 인덱스
rows: 옵티마이저가 쿼리를 처리하기 위해 얼마나 많은 레코드를 읽고 체크해야 하는지의 row수로써 반환되는 row수가 아니다.
filtered: 테이블에서 인덱스 조건에만 일치하는 레코드의 퍼센테이지이다.
Extra: 성능에 대한 내용이 주로 표시된다.
- Using filesort: 인덱스 정렬을 하지 못해서 레코드를 읽어 소트 버퍼에 복사하고, 다시 정렬하게 된다. 성능 저하의 원인
- Using index: 데이터 파일을 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때
  - 인덱스에 해당하는 값만 조회한다면 인덱스 페이지만 읽으면 되기 때문에 빠르다.
  - 인덱스 이외의 값을 추가로 조회한다면, 다시 데이터 파일에 접근해서 건수만큼 접근해서 조회해야 하는 단점이 있다.
- Using temporary: 중간 결과를 담아 두기 위한 임시 테이블 사용.
- Using where: MySQL 엔진(조인, 필터링, 그룹핑 …)에서 별도의 가공을 해서 필터링 작업을 처리한 경우.
  - 스토리지 엔진: 레코드 읽기 및 저장을 하며, 작업 범위 결정( emp_no BETWEEN 10000 and 2000 )을 한다.
  - MySQL 엔진: 체크 조건 결정(gender = ‘F’)과 조인 등을 처리한다.

문제 원인

위의 실행 계획을 기반으로 원인을 알 수 있다.

먼저 explain analyze와 explain을 확인 해보자.

-> Filter: (users.id = (select #2))  (cost=0.55 rows=3) (actual time=0.0841..0.0841 rows=0 loops=1)
    -> Table scan on users  (cost=0.55 rows=3) (actual time=0.0317..0.033 rows=3 loops=1)
    -> Select #2 (subquery in condition; uncacheable)
        -> Limit: 1 row(s)  (actual time=0.013..0.0131 rows=1 loops=3)
            -> Sort: rand(), limit input to 1 row(s) per chunk  (actual time=0.0126..0.0126 rows=1 loops=3)
                -> Stream results  (cost=0.55 rows=3) (actual time=0.00401..0.00674 rows=3 loops=3)
                    -> Covering index scan on users using PRIMARY  (cost=0.55 rows=3) (actual time=0.00268..0.00496 rows=3 loops=3)

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	users	null	ALL	null	null	null	null	3	100	Using where
2	UNCACHEABLE SUBQUERY	users	null	index	null	PRIMARY	4	null	3	100	Using where;
Using temporary;
Using filesort

들여쓰기가 깊을순으로 실행되는 순서이다. 실행 계획을 살펴보자.

Sort: rand(), limit input to 1 row(s) per chunk (actual time=0.0126..0.0126 rows=1 loops=3)

order by rand() 쿼리의 실행 계획이다. 이 부분을 보면 rows=1, loops=3이다.

바깥 쿼리가 type을 보면 테이블 풀 스캔을 타는 것을 볼 수 있다.

즉, 바깥 쿼리의 결과가 3row이기 때문에 loop를 3번 돌게 되고, limit1에 의해 1개의 결과를 반환하게 된다.

즉, 바깥 쿼리의 풀 스캔 결과에 따라서 loop가 결정된다.
Select #2 (subquery in condition; uncacheable)

MySQL은 UUID()나 RNAD()와 같이 결과값이 호출할 때마다 달라지는 함수가 서브쿼리에 사용된 경우에 캐싱하지 않고 매번 다시 실행하게 된다.

만약 캐싱되었다면, 항상 결과는 1개였을 것이다. 하지만 매번 새로 호출하기 때문에 확률적으로 0 ~ 3개의 rowr가 반환될 수 있는 것이다.
Table scan on users (cost=0.55 rows=3) (actual time=0.0317..0.033 rows=3 loops=1)

바깥 쿼리는 테이블 풀 스캔을 했다. type을 확인해도 ALL인걸 확인할 수 있다.

당연히 1번만 실행되며 전체 row인 3개가 반환된다.
Filter: (users.id = (select #2)) (cost=0.55 rows=3) (actual time=0.0841..0.0841 rows=0 loops=1)

여기는 id = (sub query)로 조인하는 부분이다.

항상 loop는 1번 돌고, 매칭되는 rand() 결과에 따라 rows=0 ~ 3으로 변경된다.

이 내용을 그림으로 풀면 아래와 같다.

바깥 쿼리의 결과 수 만큼 랜덤 쿼리를 loop하는 것이다.

원인 정리

MySQL에서는 rand(), uuid()와 같이 호출할 때 마다 결과가 달라지는 함수는 캐싱처리하지 않고 매번 재실행한다.

그리고 바깥 쿼리의 수 만큼 루프를 돌게되기 0 ~ 3개가 랜덤하게 결과로 반환된느 것이다.

해결 방법

바깥 쿼리에 0을 붙여서 해결 해도 되지만 실무에서 겪은 이슈에서는 그럴 수 없는 상황이어서

테이블 서브 쿼리로 변경

테이블 서브 쿼리로 할 경우는 문제가 발생하지 않는다.

  explain analyze
  select u_main.*
  from users u_main
  inner join (
              select id
              from users
              order by rand()
              limit 1
  ) u_sub on u_sub.id = u_main.id;

다른 테이블과 조인이 엮여있는 상황이라면(실제로 내가 겪은 문제였음)

스칼라 서브 쿼리(select 필드 서브 쿼리)로 해결할 수 있다.
서브 쿼리 제거

애초에 이렇게 사용하면 되긴 하지만 내가 겪은 문제는 쿼리가 조인이 좀 걸려있는 상태라서 이런 쿼리는 사용할 수 없었음.
```
  select *
  from users
  order by rand()
  limit 1;
```

핵심

rand() 함수와 같이 결과값이 호출 시 마다 달라지는 함수는 조인 서브 쿼리로 사용할 경우

바깥 쿼리의 결과 수 만큼 재실행하며, 캐싱 처리하지 않는다.

그래서 limit을 이와 같이 걸어도 원하는 결과를 얻을 수 없다.

이걸 알고만 있다면, 해결 방안은 상황에 맞게 잘 만들어 낼 수 있다.

잡지식

백틱(Backtick)

table 이렇게 테이블이나 컬럼을 명시하기 위한 문자를 칭하는 용어