[Real MySQL 8.0] 11-3 MySQL 연산자와 내장 함수 (3)

11-3-2 MySQL 연산자

BETWEEN 연산자

BETWEEN 연산자는 크거나 같다와 작거나 같다라는 두 개의 연산자를 하나로 합친 연산자입니다.

다른 비교 조건과 결합해 하나의 인덱스를 사용할 때 주의해야할 점이 있습니다.

 

# 쿼리 1
SELECT *
FROM dept_emp
WHERE dept_no 
  BETWEEN 'd003' AND 'd005'
  AND emp_no=10001 # unique key 설정을 바로 하면 오류날 수 있으니 api 호출하는 곳에 물어봐야함.
;

# 쿼리 2
SELECT *
FROM dept_emp
WHERE dept_no 
  IN ('d003', 'd004', 'd005')
  AND emp_no=10001
;

 

쿼리1(사진 왼쪽, BETWEEN 연산)은 dept_emp 테이블의 (dept_no, emp_no) 인덱스의 많은 레코드를 읽은 후 WHERE 조건절에 있는 emp_no에 해당하는 1건의 레코드를 반환합니다.

 

쿼리2(사진 오른쪽, IN 연산)는 IN 조건은 동등 비교를 여러 번 수행하는 것과 같은 효과가 있기 때문에 (dept_no, emp_no) 인덱스를 최적으로 사용할 수 있습니다.

 

BETWEEN을 사용하는 쿼리와 IN을 사용하는 쿼리 둘 다 인덱스 레인지 스캔을 하고 있지만 실행 계획의 rows 칼럼에 표시된 레코드 건수는 큰 차이가 있음을 알 수 있습니다.

 

mysql> explain select * from users where id in (1, 1000, 1000000) and name = 'name1000';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |    3 |    10.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from users where id between 1 and 1000000 and name = 'name1000';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL | 499154 |    10.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

 

IN 연산자

IN은 여러 개의 값에 대해 동등 비교 연산을 수행하는 연산자입니다.

여러 개의 값이 비교되지만 범위로 검색하는 것이 아니라 여러 번의 동등 비교로 실행하기 때문에 일반적으로 빠르게 처리합니다.

 

• 상수를 사용한 경우 - IN (?, ?, ?)
• 서브 쿼리를 사용한 경우 - IN (SELECT ... FROM ... )

 

IN 연산자에 상수가 사용된 경우는 동등 비교와 동일하게 작동하기 때문에 쿼리를 빠르게 처리합니다.

NOT IN의 실행 계획은 인덱스 풀 스캔으로 표시되는데, 동등이 아닌 부정형 비교여서 인덱스를 이용하여 처리 범위를 줄이는 조건으로 사용할 수 없기 때문입니다.

 

실행 계획에 인덱스 레인지 스캔이 표시되는 경우가 있습니다.

이는 InnoDB 테이블에서 프라이머리 키가 클러스터링 키이기 때문일 뿐 실제 IN과 같이 효율적으로 실행된다는 것을 의미하지는 않습니다.