자가진단 목록과 게시글 목록을 조회시 mysql의 order by limit을 사용한 페이징 처리를 했습니다. 그런데 현재 코드에서 사용되는 ORDER BY... LIMIT offset,count 절은 테이블 풀 스캔을 하기 때문에 offset과 count의 숫자가 커질수록 테이블을 스캔하는 범위가 늘어나고 쿼리 실행이 오래걸리는 문제가 발생합니다.
변경 전 코드
SELECT *
FROM diagnosis
ORDER BY id ASC LIMIT #{pageStart},#{perPageNum}
SELECT *
FROM diagnosis
ORDER BY id ASC LIMIT 5,5위 코드는 자가진단 데이터를 조회하는 쿼리문입니다. LIMIT 5,5를 예를 들면 상위 6번째부터 5개의 레코드를 가져오게 됩니다. 실제로는 먼저 diagnosis 테이블을 처음부터 읽은면서 10건의 레코드를 읽은 후, 앞의 5건은 버리고 마지막 5건만 반환하는 형태입니다. 즉 사용자에게는 5건만 표시되지만 MYSQL 서버는 10건의 레코드를 읽습니다.
SELECT *
FROM diagnosis
ORDER BY id ASC LIMIT 2000000,5만약 위 쿼리가 실행된다면 diagnosis 테이블을 처음부터 2000005건의 레코드를 읽은 후 2000000건은 버리고 마지막 5건만 사용자에게 반환될 것입니다.
이러한 문제를 해결하기 위해 인덱스를 사용하여 문제를 해결하도록 했습니다.
인덱스 알고리즘 선택
인덱스에는 알고리즘 별로 B- 트리 인덱스와 해시 인덱스 등이 존재합니다.
B-Tree 인덱스는 (칼럼의 값, 레코드 주소 or 자식 노드 주소) 형태의 여러 페이지를 트리 형식으로 저장합니다. 이 때 칼럼의 값을 변형 시키지 않고 원형 값 그대로 사용하며 정렬된 상태로 저장합니다. 또한 삽입, 삭제 시에 트리의 높이를 작게 유지하도록 조절해주는 Self-Balancing Search Tree의 특징을 갖습니다. 이러한 Balancing 의 특징을 통해 한쪽으로 높이가 무한히 커지는 편향 트리를 막을 수 있게 됩니다.
해시 인덱스는 칼럼 값을 해싱해서 인덱스에 저장합니다. 컬럼 값을 변형해서 저장하기 때문에 원래의 컬럼값이 정렬되어있는 것이 보장되어야 하는 prefix 전방일치나 범위 검색과 같은 경우에는 문제가 발생합니다.
물론 해시 인덱스는 검색, 삭제, 삽입 시 시간복잡도가 평균 o(1)로 빠르겠지만 이번에 사용한 ORDER BY와 같은 쿼리문은 인덱스의 정렬 특징을 사용하기 때문에 B-Tree를 사용합니다. 또한 B-Tree 도 검색, 삭제, 삽입 시 시간복잡도가 높이 만큼의 O(log n) = o(h) 이 걸리기 때문에 다소 빠른 편이라 할 수 있습니다.
인덱스 사용 대상
현재 페이징을 사용하는 기능은 게시글 목록 조회 기능과 자가진단 목록 조회 기능입니다. 이 중에서 자가진단에만 인덱스를 적용하기로 결정했습니다.
게시글의 경우 insert, update, delete가 많기 때문에 인덱스를 사용 시 비용이 많이 듭니다.
B-Tree의 경우,
인덱스 삽입 시 항상 정렬된 상태로 삽입되어야 하기 때문에 느릴 수 있습니다. 정렬이 보장되는 적당한 위치에 삽입이 됩니다. 또한 만약 첫번째 레코드 주소와 컬럼값 앞에 삽입된다면 뒤에 있는 레코드주소와 컬럼값 쌍들이 하나씩 이동되어야합니다.
인덱스 삭제 시에는 해당 인덱스 키 공간에 삭제마크를 작업합니다. 이는 결국 사용되지 않고 계속 방치되며 인덱스 크기가 커질 수 있습니다.
인덱스 변경 시에는 먼저 인덱스 키 공간을 삭제마킹한 후, 다시 새로운 값을 생성하는 형태로 처리합니다. 즉, 단순히 변경이 아니라 삭제와 생성의 과정을 거치기 때문에 이또한 인덱스 크기가 커질 수 있습니다.
따라서 인덱스의 변경이나 삽입이 많이 일어나는 게시글은 데이터 저장과 변경에 많은 희생이 따르기 때문에 사용하지 않습니다. 반면 자가진단 데이터는 인덱스의 변경이나 삽입이 많지 않고 읽기를 많이 하기 때문에 자가진단 데이터만을 사용합니다.
인덱스 컬럼값 선택하기
인덱스를 설정할 컬럼값을 선택할 때에는 Cardinality를 기준으로 선택했습니다. 그 이유는 Cardinality 기수성이 높다는 것은 그만큼 인덱스 키 값 가운데 중복이 없고 유니크한 값이 많다는 것을 의미합니다. 그만큼 인덱스의 검색 대상이 줄어들기 때문에 빠르게 처리 가능합니다.
SELECT *
FROM test
WHERE country = 'KOREA' AND city = 'SEOUL'위 쿼리문에서 country에 인덱스가 걸려있으며, 테이블에 데이터가 1만건 있다고 가정해봅니다.
A케이스는 country 컬럼의 유니크한 값의 개수가 만약 10개일 경우 ,
B케이스는 country 컬럼의 유니크한 값의 개수가 1000개일 경우입니다.
A 케이스의 경우 유니크한 값이 10개이므로 test 테이블에는 10개의 country가 저장되있는 것입니다. 하나의 korea라는 키 값으로 검색했을 때는 대략 10000 / 10 = 1000의 Cardinality 를 가지면 1000건의 인덱스 키값을 읽어야 합니다.
반면 B 케이스의 경우는 10000 / 1000 = 10의 Cardinality를 가지며 10건의 인덱스 키값을 검색합니다.
현재 diagnosis 테이블에는 id, name, number_of_choice(질문답선택개수) 정보가 존재합니다. 이 중 id와 name은 유니크한 값이기 때문에 index를 설정할 수 있을 듯 합니다.
그 중 id를 인덱스로 사용했습니다. 왜냐하면 id는 PK이기 때문에 InnoDB에서는 기본적으로 프라이머리 키에 대해 인덱스를 제공해주며, 현재 사용하는 쿼리 또한 id를 사용합니다. 따라서 이미 인덱스로 존재하는 id를 인덱스로 사용하기로 했습니다.
변경 후 코드
SELECT id, name, number_of_choice
FROM diagnosis
WHERE id > #{pageStart}
ORDER BY id LIMIT #{perPageNum}
문제를 해결하기 위해 WHERE 조건절로 읽어야할 위치를 인덱스를 통해 찾고, 그 위치에서 특정 페이지별 데이터 개수만큼 데이터를 읽습니다.
SELECT id, name, number_of_choice
FROM diagnosis
WHERE id > 2000000
ORDER BY id LIMIT 5예를 들어 위 쿼리라고 본다면 인덱스로 id가 2000005인 부분부터 5개의 자가진단 데이터를 읽는 것입니다.
-> 3번 Limit: 5 row(s) (cost=1.46 rows=5) (actual time=0.025..0.030 rows=5 loops=1)
-> 2번 Filter: (diagnosis.id > 5) (cost=1.46 rows=6) (actual time=0.024..0.028 rows=5 loops=1)
-> 1번 Index range scan on diagnosis using PRIMARY (cost=1.46 rows=6) (actual time=0.023..0.026 rows=5 loops=1)실제 실행 계획을 확인해보니 인덱스 레인지 스캔을 타는 것을 확인했습니다. 테이블의 전체 레코드를 읽는 것을 테이블 풀 스캔이라고 하며 반면 인덱스 레인지 스캔은 인덱스 검색 범위가 결정되어있기 때문에 전체 레코드를 읽지 않아도 됩니다.
하지만 인덱스에 있는 id만 필요한 것이 아닌 name, number_of_choice 데이터가 필요하기 때문에 결국에는 실제 데이터 파일의 레코드를 읽어와야 합니다. 이때 인덱스 레인지 스캔 범위에 있는 인덱스 키값 한건 한건 단위로 디스크 랜덤 I/O가 일어납니다. 디스크 I/O가 만약 많이 발생한다면(레코드를 많이 읽어야한다면) 그만큼 시스템 콜이 많이 발생하고 부하가 발생합니다. 하지만 현재 프로젝트에서는 5개의 레코드만을 읽기 때문에 5개의 I/O가 발생하며 이는 많은 양이 아니기 때문에 문제가 될 것 같지는 않습니다.
실제 시간을 비교해봐도 쿼리를 수정한 후가 실행 시간이 더 빠른 것을 확인했습니다.(캡쳐를 모르고 삭제함..) 인덱스를 타도록하여 자가진단 목록 조회 쿼리 실행 속도를 줄일 수 있게 되었습니다.
참고
Real MYSQL 8.0
