MySQL BETWEEN과 부등호 성능 비교해 보기 (+ 날짜 타입과 숫자 타입 비교)

MySQL BETWEEN과 부등호 성능 비교해 보기 (+ datetime, int 성능 비교)

 

SQL 쿼리에서는 WHERE 조건절 안에서 'BETWEEN' 또는 '부등호(<, <=, >, >=)'를 사용하여 조건의 범위를 지정할 수 있는데요.

최근 조건절에서 범위 지정 시 'BETWEEN'을 사용하는 것과 '부등호'를 사용하는 것의 성능 차이가 있다는 내용을 보게 되어 실제 데이터 조회를 통해 확인해 보게 되었습니다.

(성능 차이가 발생하는 것을 직접 확인해보고 싶었기 때문에 원인에 대한 내용보다는 테스트와 결과에 초점을 둔 포스팅이라는 점 참고 부탁드리겠습니다.)

 

각 테스트에서는 약 30만 건의 동일한 데이터를 사용하였으며, 아래의 몇 가지 경우를 가지고 결과를 비교해 보았습니다.

mysql 버전은 8.3.0 버전을 사용하였습니다.

 

1. datetime 타입에 대해 BETWEEN과 부등호 성능을 비교 (인덱스 없는 상태)

2. int(unix timestamp) 타입에 대해 BETWEEN과 부등호 성능을 비교 (인덱스 없는 상태)

3. 1번 환경에 인덱스를 걸어 성능 비교

4. 2번 환경에 인덱스를 걸어 성능 비교

 

---

1. datetime 타입에 대해 BETWEEN과 부등호 성능을 비교 (인덱스 없는 상태)

CREATE TABLE `employees_1` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

(1번 예시에서 사용한 테이블)

 

count(*) from employees_1

해당 예시에서는 위와 같은 'employees_1' 테이블을 사용하였으며, 해당 테이블에 존재하는 테이터는 약 30만 건입니다.

 

 

have_query_cache 확인

SHOW VARIABLES LIKE 'have_query_cache' 명령을 통해 쿼리 캐시가 설정되어 있지 않은 것을 확인하였습니다.

 

 

explain employees_1

각 쿼리는 단순히 WHERE 조건절에서 BETWEEN과 부등호를 사용하는 차이이기 때문에 실행 계획(EXPLAIN)에서는 차이점이 없는 것을 확인하였습니다.

 

 

-- datetime 타입에 대해 BETWEEN을 사용하는 경우 
SELECT SQL_NO_CACHE * 
FROM employees_1 
WHERE employees_1.start_date BETWEEN '2019-11-01 00:00:00' AND '2023-08-01 00:00:00';

-- datetime 타입에 대해 부등호를 사용하는 경우
SELECT SQL_NO_CACHE * 
FROM employees_1 
WHERE employees_1.start_date >= '2019-11-01 00:00:00' 
AND employees_1.start_date <= '2023-08-01 00:00:00';

 

 

 

결과

각각의 쿼리를 5번씩 실행하였으며, 쿼리 프로파일링(Query Profiling) 기능을 통해 각 쿼리가 실행된 시간(Duration)을 확인한 결과는 다음과 같습니다.

 

* BETWEEN 평균 Duration = 0.34304

* 부등호 평균 Duration = 0.26812

 

---

2. int(unix timestamp) 타입에 대해 BETWEEN과 부등호 성능을 비교 (인덱스 없는 상태)

CREATE TABLE `employees_2` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `start_date` int DEFAULT NULL,
  `end_date` int DEFAULT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

(2번 예시에서 사용한 테이블)

 

count(*) from employees_2

'employees_2' 테이블에 존재하는 데이터 역시 약 30만 건으로 1번 예시에서 사용한 'employees_1' 테이블의 데이터와 동일한 데이터를 사용하였습니다.

 

다만 start_date, end_date 컬럼의 타입만 'datetime' -> 'int'로 변경하였으며, INSERT SELECT 과정에서 unix_timestamp 함수를 통해 값을 변환하여 삽입하였습니다.

 

 

explain employees_2

SELECT unix_timestamp('2019-11-01 00:00:00');    -- '1572534000'
SELECT unix_timestamp('2023-08-01 00:00:00');    -- '1690815600'

 

WHERE 조건절에 사용한 기간은 동일하게 적용하였으며, unix_timestamp 함수를 통해 변환한 값입니다.

1번 예시 때와 마찬가지로 실행 계획(EXPLAIN)에서의 차이점은 없는 것을 확인하였습니다.

 

 

-- int 타입에 대해 BETWEEN을 사용하는 경우
SELECT SQL_NO_CACHE * 
FROM employees_2 
WHERE employees_2.start_date BETWEEN 1572534000 AND 1690815600;

-- int 타입에 대해 부등호를 사용하는 경우
SELECT SQL_NO_CACHE * 
FROM employees_2 
WHERE employees_2.start_date >= 1572534000
AND employees_2.start_date <= 1690815600;

 

 

 

결과

첫 번째 예시와 동일하게 각각의 쿼리를 5번씩 실행하였으며, 쿼리 프로파일링 기능을 통해 각 쿼리가 실행된 시간(Duration)을 확인한 결과는 다음과 같습니다.

 

* BETWEEN 평균 Duration = 0.23089

* 부등호 평균 Duration = 0.24306

 

---

3 + 4. 1번 환경에 인덱스를 걸어 성능 비교, 2번 환경에 인덱스를 걸어 성능 비교

explain employees_1 add index

 

explain employees_2 add index

 

start_date 컬럼의 타입이 datetime, int 각각일 때에 대해 INDEX를 적용시켜 보았으나, 인덱스를 타지 않는 것을 확인했는데요.

추측상으로는 옵티마이저가 판단하기에 인덱스를 태우는 것보다 테이블 풀스캔이 더 빠르다고 판단되어 이러한 결과가 나온 것 같습니다.

 

---

BETWEEN과 부등호 성능에 대한 결론

* 해당 내용은 개인적인 의견이 포함되어 있으며, 성능의 경우 테이블 구조 및 실행 환경(버전) 등에 따라서도 달라질 수 있다는 점 참고 부탁드립니다.

 

	BETWEEN	부등호(<, <=, >, >=)
datetime	0.34304	0.26812
int	0.23089	0.24306

 

우선 'datetime'에 대해 미세한 차이라고 생각될 수는 있겠지만 '부등호'를 사용했을 때의 결과가 'BETWEEN'을 사용했을 때 보다 빠르다는 것을 확인할 수 있었는데요.

조회 대상 테이블의 데이터가 훨씬 많아지고, 또 조금의 성능 차이도 중요한 서비스의 경우에는 BETWEEN 대신 부등호의 사용을 충분히 고려해 볼 수 있을 것 같습니다.

 

그리고 날짜 데이터를 'int(unix timestamp)' 타입으로 저장하는 경우, BETWEEN 또는 부등호를 사용하였을 때 전반적인 속도가 datetime 보다 조금 빠르다는 것이 확인되는데요.

하지만 이 경우에는 시스템 전반적으로 날짜 형식의 데이터 타입을 숫자로 바꾸고, 다시 숫자 형식의 데이터 타입을 날짜 형식으로 바꾸는 등의 번거로운 과정이 생긴다면 전체적인 속도가 더 느려질 수 있다는 점도 고려해보아야 할 것 같습니다.

 

(해당 내용 중 잘못된 부분이나 궁금한 부분에 대해서는 댓글 남겨주시면 확인하여 답변드리겠습니다. 감사합니다.)

 

 

 

< 함께 보면 좋은 자료 >

2022.11.12 - [Programming/MySQL] - MySQL EXPLAIN 실행 계획을 통한 쿼리 최적화

2024.05.01 - [Programming/Web] - (MyBatis) , CDATA 사용 이유 정리