[DB] Index 실습(대용량 데이터 처리) - MySQL

* 개요

얼마 전 대용량 시스템 공부를 진행하면서, DB에 약 700만건의 테스트 데이터를 삽입하였다.

그 후 페이징 처리를 하기 위해서 조회를 하였는데, 다음과 같은 결과가 나왔다.

select * from article where board_id = 1 order by created_at desc limit 30 offset 90;

1번 게시판의 91번 게시글에서 120번 게시글까지 가져오는 데에 17초 이상이 걸린다.

실제로 사용자가 페이지를 조회하고자 한다면 불편함을 토로할 것이다.

explain 명령어를 통해 쿼리 플랜을 확인해보았다.

explain select * from article where board_id = 1 order by created_at desc limit 30 offset 90;

쿼리 타입을 확인해보니 type = ALL로 되어있다. 이는 테이블 전체를 읽는 풀 스캔을 한다는 것이다.

Extras = Using where; Using filesort 로 되어있는데, where 절로 조건을 필터링하고, 데이터가 많아 메모리에서 정렬을 수행할 수 없어서, 파일(디스크)에서 데이터를 정렬하는 filesort를 수행하였다고 되어있다.

전체 데이터에 대해 필터링 및 정렬하기 때문에 매우 큰 비용이 든 것이다.

이 문제를 해결하기 위해 인덱스를 사용해보았다.

 

* 인덱스에 대한 이해

관계형 데이터베이스에서 인덱스는 주로 B+ tree 자료구조를 가진다. 이 상태의 인덱스의 특징은 다음과 같다.

• 데이터가 정렬된 상태로 저장된다.
• 검색, 삽입, 삭제 연산을 로그 시간에 수행 가능하다.
• 트리 구조에서 leaf node 간 연결되기 때문에 범위 검색에 효율적이다.

인덱스를 추가하면 write 시점에 B+ tree 구조의 정렬된 상태의 데이터가 생성된다.

이미 인덱스로 지정된 컬럼에 대해 정렬된 상태를 가지고 있기 때문에

• 조회 시점에 전체 데이터를 정렬하고 필터링할 필요가 없다.
• 따라서 조회 쿼리를 빠르게 수행할 수 있다.

 

* 인덱스 생성

 create index idx_board_id_article_id on article(board_id asc, article_id desc);

 

인덱스를 생성했다. 다시 쿼리 결과를 확인해보자.

select * from article where board_id = 1 order by article_id desc limit 30 offset 90;

0.02초의 시간이 걸렸고, 조회 시간이 매우매우 단축됐음을 알 수 있다.

explain select * from article where board_id = 1 order by article_id desc limit 30 offset 90;

쿼리 플랜을 확인해보니, type은 ref, 조회 key로써 인덱스가 사용됐음을 알 수 있다.