[DB] 카디널리티가 낮은 인덱스 order by 속도 해결 (MySQL)

Cho-D-YoungRae·2022년 12월 6일

도입

상황

저는 음식점의 음식 사진 게시물을 공유하는 SNS 개발 프로젝트를 하고 있습니다. DB에 저장되어있는 음식점 데이터 개수는 약 89만개 입니다.

현재 음식점의 게시물 개수를 통해 음식점의 순위를 내기 위해 MySQL order by 을 사용하였는데 도저히 사용하기 어려운 정도의 속도가 나왔고 이를 해결하기 위해 적절하게 인덱스를 생성해주려 합니다.

schema

create table if not exists restaurant
(
    restaurant_id     bigint auto_increment
        primary key,
    name              varchar(100)  not null,
    phone             varchar(25)   not null,
    address_name      varchar(255)  not null,
    road_address_name varchar(255)  not null,
    address_code      varchar(15)   not null, -- 법정동 코드
    use_yn            bit           not null,
    created_at        datetime(6)   not null,
    last_modified_at  datetime(6)   not null,
    post_count        int default 0 not null, -- 게시물 개수
    favorite_count    int default 0 not null, -- 즐겨찾기 개수
    constraint FK_restaurant_address_code
        foreign key (address_code) references address_code (code)
);

create index IX_restaurant_name
    on restaurant (name);

시간 측정의 경우 MySQL 내부의 캐싱 등으로 인해 실행할 때 마다 달라질 수 있으므로 참고용으로만 기재했습니다. ~최대한 캐싱의 영향을 받지 않기 위해 제일 처음 실행시킨 쿼리의 시간을 기재했습니다.~

단일 인덱스

아래와 같은 쿼리를 통해 데이터를 조회합니다.

select
    *
from
    restaurant r
order by
    r.post_count desc
limit 892939, 1

위 schema에서 use_yn은 삭제 여부를 나타내는 칼럼이어서 where 조건에 use_yn = true 를 추가해주어야 하지만 대부분의 데이터가 삭제되지 않은 상태이고, 간단한 설명을 위해 생략하겠습니다.
이 쿼리는 모든 레코드를 스캔하는 작업은 하지만, 화면에는 레코드 1건만 출력하려고 LIMIT .. OFFSET .. 옵션을 추가한 것입니다.

인덱스 사용 전

인덱스를 사용하기 전에는 최대 3.77 초의 시간이 소요되었습니다.

mysql> select * from restaurant r order by r.post_count desc limit 892939, 1;
1 row in set (3.77 sec)
1 row in set (1.43 sec)
1 row in set (1.43 sec)
1 row in set (1.66 sec)
1 row in set (1.60 sec)
1 row in set (1.26 sec)
1 row in set (1.38 sec)
1 row in set (1.36 sec)
1 row in set (1.42 sec)
1 row in set (1.40 sec)

mysql> explain select * from restaurant r order by r.post_count desc limit 892939, 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | r     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 835329 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+

실행 계획을 확인해보니 post_count 칼럼에 인덱스가 걸려있지 않기 때문에 당연히 사용된 인덱스가 없고, Extra 도 Using filesort 로 인덱스를 사용하지 않고 정렬한 것이 나타납니다.

인덱스 설정 - 카디널리티가 낮은 문제

이제 인덱스를 설정해주도록 하겠습니다. 인덱스는 아래와 같이 설정하였습니다.

alter table restaurant add index IX_restaurant_post_count (post_count desc);

mysql> select * from restaurant r order by r.post_count desc limit 892939, 1;
1 row in set (2.22 sec)
1 row in set (1.63 sec)
1 row in set (1.86 sec)
1 row in set (1.66 sec)
1 row in set (1.26 sec)
1 row in set (1.35 sec)
1 row in set (1.16 sec)
1 row in set (1.11 sec)
1 row in set (1.20 sec)
1 row in set (1.20 sec)

인덱스를 설정해주었는데도 유의미한 성능의 향상이 발생하지 않았습니다.

현재 제 프로젝트는 서비스가 되고 있지 않은 초기 개발 프로젝트여서 게시물이 거의 없고, 그로 인해 post_count 의 카디널리티가 매우 적은 상태 입니다.

mysql> explain select * from restaurant r order by r.post_count desc limit 892939, 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | r     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 835329 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+

그로 인해 쿼리가 인덱스를 타지 못 한 모습을 보입니다.

해결

순위를 결정할 때 게시글이 아예 없는 음식점은 제외해도 되겠다고 판단되었고, 문제를 해결하기 위해 where 조건에 post_count 가 0보다 큰 것을 추가하였습니다.

mysql> select * from restaurant r where post_count > 0 order by r.post_count desc limit 892939, 1;
Empty set (0.06 sec)
Empty set (0.00 sec)
Empty set (0.00 sec)
Empty set (0.00 sec)
Empty set (0.00 sec)
Empty set (0.00 sec)
Empty set (0.00 sec)
Empty set (0.01 sec)
Empty set (0.00 sec)
Empty set (0.01 sec)

조회성능이 빨라졌습니다. 낮은 카디널리티(0보다 큰 값이 거의 없음)으로 인한 영향도 있을 것으로 예상됩니다.

mysql> explain select * from restaurant r where post_count > 0 order by r.post_count desc limit 892939, 1;
+----+-------------+-------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys            | key                      | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | r     | NULL       | range | IX_restaurant_post_count | IX_restaurant_post_count | 4       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+--------------------------------------------+