MySQL
Real MySQL [7-4] 쿼리 작성 및 최적화 - MySQL 연산자 (BETWEEN, IN)
weicome
2017. 1. 3. 20:02
본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
BETWEEN 연산자
BETWEEN 연산자는 "크거나 같다"와 "작거나 같다"는 두 개의 연산자를 하나로 합친 연산자다.
BETWEEN 연산자는 다른 비교 조건과 결합해 하나의 인덱스를 사용할 때 주의해야 할 점이 있다. 동등 비교 연산자와 BETWEEN 연산자를 이용해 부서 번호와 사원 번호로 dept_emp 테이블을 조회하는 다음 쿼리를 한번 생각해 보자.
SELECT * FROM dept_emp
WHERE dept_no='d003' AND emp_no=10001;
SELECT * FROM dept_emp
WHERE dept_no BETWEEN 'd003' AND 'd005' AND emp_no=10001;
dept_emp 테이블에는 (dept_no+emp_no) 칼럼으로 인덱스가 생성돼 있다. 첫번째 쿼리는 모두 인덱스를 이용해 범위를 줄여주는 방법으로 사용할수 있다. 하지만 두번째 쿼리에서 사용한 BETWEEN은 크다 또는 작다 연산자와같이 범위를 읽어야 하는 연산자라서 d003 보다 크거나 같고 d005보다 작거나 같은 모든 인덱스의 범위를 검색해야만 한다. 결국 BETWEEN이 사용된 두 번째 쿼리에서 emp_no=10001 조건은 비교 범위를 줄이는 역할을 하지 못한다.
BETWEEN 과 IN을 비슷한 비교 연산자로 생각하는 사람도 있는데, 사실 BETWEEN은 크다와 작다 비교를 하나로 묶어둔 것에 가깝다. 그리고 IN 연산자의 처리 방법은 동등 비교(=) 연산자와 비슷하다. 아래의 그림은 IN과 BETWEEN의 처리 과정 차이를 보여준다
IN연산자는 여러 개의 동등 비교(=)를 하나로 묶은 것과 같은 연산자라서 IN과 동등 비교 연산자는 같은 형태로 인덱스를 사용하게 된다.
BETWEEN 조건을 사용하는 쿼리는 dept_emp의 상당히 많은 레코드를 읽게 된다. 하지만 실제로 가져오는 데이터는 1건밖에 안된다. 이런 쿼리를 다음과 같은 형태로 바꾸면 emp_no=10001 조건도 작업 범위를 줄이는 용도로 인덱스를 이용할 수 있게 된다.
SELECT * FROM dept_emp
WHERE dept_no IN('d003', 'd004', 'd005') AND emp_no=10001;
BETWEEN이 선형으로 인덱스를 검색하는 것과 달리 IN은 동등 비교를 여러 번 수행하는 것과 같은 효과가 있기 때문에 dept_emp 테이블의 인덱스를 최적으로 사용할 수 있다.
이 예제처럼 여러 칼럼으로 인덱스가 만들어져 있는데, 인덱스 앞쪽에 있는 칼럼의 선택도가 떨어질 때는 IN으로 변경하는 방법으로 쿼리의 성능을 개선할 수도 있다.
SELECT * FROM dept_emp
WHERE dept_no BETWEEN 'd003' AND 'd005' AND emp_no=10001;
SELECT * FROM dept_emp
WHERE dept_no IN('d003', 'd004', 'd005') AND emp_no=10001;
다음은 BETWEEN 연산자를 사용한 첫번째 예제 쿼리의 실행계획이다.
다음은 BETWEEN 대신 IN 연산자를 사용한 두 번째 쿼리의 실행 계획이다.
둘다 인덱스 레인지 스캔을 하고 있지만 rows 칼럼에 표시된 레코드 건수는 큰 차이가 있다.
이 예제에서 dept_no는 가질 수 있는 값이 몇 개 되지 않아 손쉽게 BETWEEN을 IN 연산자로 개선할 수 있다. 그러나 IN 연산자에 사용할 상수 값을 가져오기 위해 별도의 SELECT 쿼리를 한번 더 실행해야 할 때도 있다. IN(subquery)의 형태로 쿼리를 변경하면 더 나쁜 결과를 가져올 수도 있기 때문에 IN(subquery) 형태로는 변경하지 않는 것이 좋다.
SELECT * FROM dept_emp
WHERE dept_no IN (
SELECT dept_no
FROM departments
WHERE dept_no BETWEEN 'd003' AND 'd005' )
AND emp_no=10001;
BETWEEN 연산자를 사용한 경우와 "크다", "작다" 비교를 사용한 경우의 차이를 살펴보자.
SELECT * FROM employees WHERE emp_no BETWEEN 100001 AND 4000000;
SELECT * FROM employees WHERE emp_no >=100001 AND emp_no<=4000000;
BETWEEN 비교(다음 첫 번째 쿼리)는 하나의 비교 조건으로 처리하지만, 크다와 작다의 조합으로 비교하는 경우에는 두 개의 비교 조건으로 처리한다는 것이 가장 큰 차이일 것이다. 실제로 MySQL의 옵티마이저가 최적화해서 실행하기 직전의 쿼리를 봐도 BETWEEN 연산자를 크다 작다의 연산자로 변환하지 않고 BETWEEN을 그대로 유지한다는 것을 알 수 있다.
WHERE:( 'employees'.'emp_no' between 10001 and 400000 )
WHERE:( ( 'employees'.'emp_no' >=10001 ) and ('employees'.'emp_no' <= 400000) )
이 차이는 디스크로 읽어야 하는 레코드 수가 달라질 정도의 차이를 만들어 내지는 않지만 읽어온 레코드를 CPU와 메모리 수준에서 비교하는 수준 정도의 차이가 있다고 볼 수 있다.
20여만 건의 결과를 가져오는데, 0.03초 정도 BETWEEN 이 빠르게 실행된 것을 알 수 있다. 이 차이는 디스크 작업의 차이가 아닌 CPU의 연산 차이로 발생하는 것이므로 크게 고려하지 않아도 된다.
IN 연산자
IN은 여러 개의 값에 대해 동등 비교 연산을 수행하는 연산자다. 여러 개의 값이 비교되지만 범위로 검색하지 않고 여러 번의 동등 비교로 실행되어 빠르게 처리된다. MySQL에서 IN 연산자는 사용법에 따라 상당히 비효율적으로 처리될 때도 많다.
IN 연산자의 입력이 상수가 아니라 서브 쿼리인 경우에는 상당히 느려질 수 있다. IN의 인자로 상수가 사용되면 이 상수값이 쿼리의 입력 조건으로 사용하기 때문에 기대했던 대로 적절히 인덱스를 이용해 쿼리를 실행한다. 하지만 IN의 입력으로 서브 쿼리를 사용할 때는 서브 쿼리가 먼저 실행되어 그 결과가 상수값으로 전달되지 않고, 서브 쿼리의 외부가 먼저 실행되고 IN(subquery)는 체크 조건으로 사용된다. 결과적으로 기대와는 달리 느려지는 경우가 많다.