[MySQL] 쿼리 개선 기록 - 날짜 범위, 중복 제거

날짜로 필터링을 하거나 데이터 중복을 제거할 때 인덱스에서 데이터를 찾는지 또는 추가적인 작업을 하는지는 쿼리 속도에 영향을 준다.

이 간단한 작업으로도 큰 효과를 가져올 수 있었다.

 

당시 회사내 쿼리 개선 된 결과)

분류	쿼리 속도	행 수
기존 쿼리	약 26401ms	약1800만건
개선 된 쿼리	약 190ms	약6만8천건

 

가정

예를 들어 이런 쿼리가 있다고 가정해 보자.(날짜에 파티션은 제외하고 가정)

SELECT student, count(DISTINCT class_uid) as total
FROM school.class1
WHERE date_format(plan_dt, '%Y%m') = '202307'
GROUP BY student;

1. class1에는 plan_dt에 인덱스가 생성되어 있다.

2. class_uid는 데이터가 고유하며 NULL 값이 없다.

3. plant_dt는 date 컬럼이다.

 

분석

1. Distinct 함수는 중복된 데이터를 제거해 주는 역할을 한다. 다만 작동방식을 보면 이렇다.

일반적으로 내부 임시 테이블을 생성하고, 결과 집합의 모든 데이터를 이 임시 테이블에 삽입한 후 중복을 제거한다.
이때, 데이터가 많을수록 임시 테이블의 크기가 커지고, 디스크 I/O가 증가하며, 결과적으로 쿼리 성능이 저하될 수 있다.

 

2. DATE_FORMAT 함수 사용 시 날짜를 변환할 수 있다. 다만 작동방식을 보면 이렇다.

인덱스는 데이터베이스 테이블의 특정 컬럼들에 대한 값과 그 값들이 저장된 위치의 매핑을 저장한다. 이를 통해 DBMS는 특정 값이나 값의 범위를 빠르게 찾는데, 컬럼에 함수를 적용하면 함수의 결괏값에 대한 인덱스를 갖고 있지 않기 때문에, 테이블의 모든 행을 순회하며 함수를 각 행에 적용해야 한다.
이 과정을 풀 테이블 스캔(full table scan)이라고 하며, 대규모 데이터가 있는 테이블에서는 이로 인해 성능 저하가 발생한다.

 

결론

SELECT student, count(class_uid) as total
FROM school.class1
WHERE plan_dt >= '2023-07-01' AND plan_dt < '2023-08-01'
GROUP BY student;

1. 인덱스를 사용할 수 있게 함수로 변환하지 않고 범위 검색을 한다.(between도 가능)

2. 데이터가 고유하며 NULL값이 없다면 추가적인 작업(임시 테이블 생성 및 중복 제거)은 제외한다.

COUNT(*) vs COUNT(class_uid)의 차이점

COUNT(*): 테이블의 행 수를 세는 데 사용되고, NULL 값을 포함한 모든 행을 세며, 특정 컬럼의 값과 무관하게 전체 행 수를 반환한다.
COUNT(class_uid): 특정 컬럼에서 NULL이 아닌 값의 개수를 세는 데 사용되므로, COUNT(*)와 다른 값을 반환할 수 있다.