MySQL을 DB로 사용하여 개발할 때 내가 작성한 쿼리가 실제 어떻게 수행이 되고 성능은 어느 정도인지 확인이 필요할 때가 있습니다. 성능이 느리다면 쿼리를 개선하거나 구조를 다시 짜는 리팩터링을 통해 성능을 끌어올릴 수도 있구요. 제가 프로젝트를 진행하면서 만든 쿼리의 실행 계획을 분석하고 성능 개선을 한 사례를 말씀드리겠습니다.
간단한 사례
제가 진행한 프로젝트에는 캠핑용품 유형 데이터가 존재합니다.
캠핑용품 유형이란?
캠핑용품에는 텐트, 코펠, 버너, 테이블, 의자와 같이 여러 가지 유형이 존재하는 데 그 유형을 나타내는 데이터입니다. 그래서 실제 테이블은 id와 name 칼럼으로 구성되어 있고 id를 통해 유형 데이터의 유일함을 표현할 수 있도록 Primary Key 제약 조건을 걸고 사용 중이었습니다.
하지만 프로젝트를 진행하면서 캠핑용품 유형 데이터의 수정 기능이 요구되었고 id가 아닌 name만으로 검색할 수 있는 기능이 필요했습니다.
유형 테이블을 처음에 만들 때는 name 기반의 검색 조건은 필요하지 않았고 앞으로도 필요하지 않을 것이라 생각했습니다. 그래서 name에 대한 인덱스는 필요 없다 생각하여 만들지 않았는데 결과적으로 위 쿼리의 실행 계획을 살펴봤을 때 풀 테이블 스캔이 발생하는 것을 확인했습니다.
실행 쿼리에 Explain을 붙여 실행 계획을 살펴보면 type이라는 항목이 ALL로 지정되어 있는 것을 볼 수 있습니다.
여기서 ALL은 풀 테이블 스캔을 뜻하며 테이블의 모든 레코드를 디스크에서 읽는 작업이 일어난다는 뜻입니다. 레코드가 늘어나면 날수록 디스크 I/O가 많이 발생하고 결국 성능 저하 또한 발생할 수 있습니다.
그래서 저는 유형 데이터의 특성을 분석하여 유형 테이블의 name은 중복될 수 없고 유일해야 한다고 판단했고 Unique key를 통해 name조건으로 검색 시 유니크 인덱스를 사용해 풀 테이블 스캔을 회피할 수 있도록 변경하였습니다.
const는 테이블의 레코드 수에 상관없이 쿼리가 Primary Key 또는 Unique Key 칼럼을 WHERE 조건으로 가지고 있고 반드시 한 건임을 보장할 수 있는 경우에 나타납니다. 즉, 테이블의 레코드를 전부 검색하여 name이 tent인지 비교하지 않고 WHERE 조건에 주어진 값('tent')을 통해 찾으려는 레코드가 어디에 있는지 즉시 확인이 가능한 경우를 말합니다.
그리고 Extra 항목이 변경된 것도 볼 수 있는데요. Using index는 커버링 인덱스를 의미하며 인덱스만으로 해당 쿼리 결과를 도출할 수 있을 때 나타납니다.
커버링 인덱스를 사용하지 못하면 어떻게 되나요?
MySQL에서 쿼리의 결과를 도출하기 위해 인덱스의 리프 노드에 있는 레코드 주소를 참조하여 디스크에 저장된 레코드로 접근합니다. 즉, 디스크 I/O가 발생하는데 레코드의 개수가 적으면 큰 영향이 없지만 많은 경우라면 커버링 인덱스를 사용하냐 안 하냐에서 큰 성능 차이가 존재합니다.
비교적 간단한 요구사항과 쿼리에도 불구하고 실행 계획 분석을 통해 매우 비효율적인 동작을 확인하고 개선할 수 있었습니다.
그럼 복잡한 경우는 어떨까요?
복잡한 사례
캠핑용품을 빌려주는 사람이 캠핑용품을 어느 기간만큼 얼마의 비용으로 어디서 빌려줄 수 있는 정보가 담긴 rental 테이블, 구체적으로 어떤 캠핑용품인지의 정보가 담긴 gear라는 테이블이 있습니다.
대여 정보를 사용자의 위치를 기반으로 조회하는 기능이 필요했고 location 칼럼에 저장되는 point 타입의 위치 정보를 ST_DisTance_Sphere 함수를 사용해 자기 위치의 n미터 이내 rental 데이터를 조회하는 쿼리가 필요했습니다. 추가적으로 캠핑용품 유형 ID가 있으면 특정 유형 ID의 gear를 가진 rental 데이터만 조회하는 조인 쿼리 또한 필요했습니다.
캠핑용품 유형 ID가 없고 100m 이내의 레코드를 검색하는 쿼리
SELECT * FROM RENTAL WHERE 100 > ST_DisTance_Sphere(location, POINT(x좌표, y좌표));
캠핑용품 유형 ID가 14이고 100m 이내의 레코드를 검색하는 쿼리
SELECT * FROM RENTAL R, GEAR G WHERE 100 > ST_DisTance_Sphere(location, POINT(x좌표, y좌표)) AND G.TYPE=14 AND R.ID = G.RENTAL_ID;
첫 번째의 경우 location 칼럼을 조건으로 사용하는데 location에는 어떠한 인덱스도 존재하지 않아 풀 테이블 스캔이 일어나는 것을 EXPLAIN을 통해 확인할 수 있었습니다.
그래서 아래의 조치로 location 칼럼에 공간 인덱스를 부여하고 인덱스를 사용할 수 있도록 변경하였습니다.
- Spatial Index(R-Tree) 생성
- location 칼럼 속성 추가(NOT NULL, SPID 0)
- ST_Within 함수를 사용해 Point 타입이 아닌 Polygon 타입으로 검색
SELECT * FROM RENTAL WHERE ST_Within(location, ST_GeomFromText('POLYGON((x좌표 - 50m y좌표 - 50m, x좌표 + 50m y좌표 - 50m , x좌표 + 50m y좌표 + 50m, x좌표 - 50m y좌표 + 50m))'));
이렇게 type이 ALL이 아닌 range로 인덱스 레인지 스캔을 하는 것을 확인할 수 있습니다.
두 번째 경우는 gear의 rental_id와 rental의 id가 조인되는 쿼리입니다. 이 경우에는 위의 공간 인덱스는 거치지만 rental_id는 인덱스가 존재하지 않아 gear 테이블을 조인할 때 풀 테이블 스캔이 발생합니다.
여기서 rental_id에 normal 인덱스를 생성해서 다시 실행 계획을 분석하면
type이 ref로 바뀌어 인덱스를 잘 사용하는 것을 확인할 수 있습니다.
이렇게 자신이 작성한 쿼리가 MySQL에서 인덱스를 사용해 효율적으로 처리되는지 EXPLAIN을 통해 확인이 가능합니다. 풀 테이블 스캔을 최대한 피하는 쪽으로 개선하시면 비교적 좋은 성능을 보여줄 수 있습니다.
단, 테이블의 전체 레코드 25% 이상을 조회하는 경우에는 인덱스 전체를 스캔하는 것보다 풀 테이블 스캔이 더 효율적일 수 있습니다. 인덱스 스캔은 랜덤 I/O, 풀 테이블 스캔은 순차 I/O라는 차이점이 있기 때문입니다. 각각의 특징을 잘 이해하고 자신의 상황에 알맞게 개선해 나가는 것이 가장 중요합니다.
'Database > MySQL' 카테고리의 다른 글
| 나는 왜 MySQL Replication을 적용했을까? (0) | 2021.04.25 |
|---|
