쿼리 작성 시스템 변수 및 내장 함수

초코칩·2024년 8월 12일
mysql

MySQL

목록 보기
7/8
post-thumbnail

SQL은 어떠한 데이터를 요청하기 위한 언어이기에 상당히 제한적(Domain Specified Language)이다. 그래서 쿼리가 빠르게 수행되게 하려면 데이터베이스 서버에서 쿼리가 어떻게 요청을 처리하는지 예측할 수 있어야 한다.

애플리케이션 코드를 튜닝해서 성능을 2배 개선하는 것은 쉽지 않지만, DBMS에서 몇십 배에서 몇백 배의 성능 향상이 이뤄지는 것은 상당히 흔한 일이다. 쿼리의 패터별로 어떻게 처리되는가?를 살펴보자.

쿼리 작성과 연관된 시스템 변수

SQL 모드

MySQL 서버의 sql_mode라는 시스템 설정에는 여러 개의 값이 동시에 설정될 수 있다.

  • STRICT_ALL_TABLES & STRICT_TRANS_TABLES: MySQL 서버에서 INSERT나 UPDATE 문장으로 데이터를 변경하는 경우, 자동으로 타입을 변경한다. 적절히 변환되기 어려울 경우 MySQL 서버가 INSERT나 UPDATE 문장을 계속 실행할지, 아니면 에러를 발생시킬지 결정한다. STRICT_TRANS_TABLES 옵션은 트랜잭션 지원 스토리지 엔진에서만 엄격한 모드를 적용하며, STRICT_ALL_TABLES 옵션은 트랜잭션 지원 여부와 상관없이 엄격한 모드를 적용한다.

두 모드를 적용하면 사용자가 원하지 않는 방향으로의 변환을 방지할 수 있기에 활성화할 것을 권장한다.

  • ANTI_QUOTES: MySQL에서는 문자열 값(리터럴)을 표현하기 위해 홀따옴표와 쌍따옴표를 동시에 사용할 수 있지만, 다른 DBMS에서는 그렇지 않다. sql_mode 시스템 변수에 ANSI_QUOTES를 설정하면 홈따옴표만 문자열 값 표기로 사용할 수 있고, 쌍따옴표는 칼럼명이나 테이블명과 같은 식별자를 표기하는 데만 사용할 수 있다.

  • ONLY_FULL_GROUP_BY: MySQL의 쿼리에서는 GROUP BY 절에 포함되지 않은 칼럼이더라도 집합 함수의 사용 없이 그대로 SELECT 절이나 HAVING 절에 사용할 수 있다. 이러한 부분도 SQL 표준이나 다른 DBMS와는 다른 동작 방식인데, sql_mode 시스템 변수에 ONLY_FULL_GROUP_BY를 설정해서 SQL 문법에 조금 더 엄격한 규칙을 적용한다.

MySQL 8.0에서는 sql_mode 시스템 변수의 ONLY_FULL_GROUP_BY 옵션이 기본적으로 활성화되어 있습니다. 이 옵션이 활성화되면, GROUP BY 절을 사용하는 쿼리에서 SELECT 절에는 GROUP BY 절에 명시된 칼럼과 집계 함수만 사용할 수 있습니다. MySQL 5.7 버전까지는 이 옵션이 비활성화되어 있었기 때문에, MySQL 5.7에서 8.0으로 업그레이드할 때는 ONLY_FULL_GROUP_BY 옵션을 비활성화해야 할 수도 있습니다.

  • PIPES_AS_CONCAT: MySQL에서 "||"는 OR 연산자와 같은 의미로 사용된다. 하지만 sql_mode 시스템 변수에 PIPES_AS_CONCAT 값을 설정하면 오라클과 같이 문자열 연결 연산자(CONCAT)로 사용할 수 있다.

  • PAD_CHAR_TO_FULL_LENGTH: MySQL에서는 CHAR 타입이라고 하더라도 VARCHAR와 같이 유효 문자열 뒤의 공백 문자는 제거되어 반환된다.

MySQL이 불필요한 공백 문자를 제거하는 방식이 더 편리하다. 하지만 CHAR 타입의 칼럼값을 가져올 때 뒤쪽의 공백이 제거되지 않고 반환돼야 한다면 sql_mode 시스템 설정에 PAD_CHAR_TO_FULL_LENGTH를 추가하면 된다.

  • N0_BACKSLASH_ESCAPES: MySQL에서도 일반적인 프로그래밍 언어에서처럼 역슬래시 문자를 이스케이프 문자로 사용할 수 있다. sql_mode 시스템 설정에 NO_BACKSLASH_ESCAPES를 추가하면 역슬래시를 문자의 이스케이프 용도로 사용하지 못한다. 이 설정을 활성화하면 역슬래시 문자도 다른 문자와 동일하게 취급한다.

  • IGNORE_SPACE: MySQL에서 스토어드 프로시저나 함수의 이름 뒤에 공백이 있으면 "스토어드 프로시저나 함수가 없습니다"라는 에러가 출력될 수도 있다. MySQL에서는 스토어드 프로시저나 함수명과 괄호 사이에 있는 공백까지도 스토어드 프로시저나 함수의 이름으로 간주한다. 이 동작 방식이 기본 모드이므로 몇 번이고 함수가 있는지 확인하기도 한다. sql_mode 시스템 변수에 **IGNORE_SPACE를 추가하면 프로시저나 함수명과 괄호 사이의 공백은 무시**한다. IGNORE_SPACE 옵션은 MySQL 서버의 내장 함수에만 적용되며, IGNORE_SPACE 옵션이 활성화되면 MySQL 서버의 내장 함수는 모두 예약어로 간주되어 테이블이나 칼럼의 이름으로 사용될 수 없다. 물론 역따옴표(`. backtick)를 이용하면 예약어를 테이블이나 칼럼의 이름으로 사용할 수 있다.

  • REAL_AS_FLOAT: MySQL 서버에서는 부동 소수점 타입은 FLOAT과 DOUBLE 타입이 지원되는데, REAL 타입은 DOUBLE 타입의 동의어로 사용된다. 하지만 REAL_AS_FLOAT 모드가 활성화되면 MySQL 서버는 REAL이라는 타입이 FLOAT 타입의 동의어로 바뀐다.

  • NO_ZERO_IN_DATE & NO_ZERO_DATE: 두 옵션이 활성화되면 MySQL 서버는 DATE 또는 DATETIME 타입의 칼럼에 실제로 존재하지 않는 날짜를 저장하는 것이 불가능해진다.

  • ANSI: 이 값은 앞에서 설명한 여러 가지 옵션을 조합해서 MySQL 서버가 최대한 SQL 표준에 맞게 동작하게 만들 어준다. ANSI 모드는 REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY 모드의 조합으로 구성된 모드다.

  • TRADITIONAL: STRICT_TRANS_TABLES나 STRICT_ALL_TABLES와 비슷하지만 조금 더 엄격한 방법으로 SQL의 작동을 제어한다. TRADITIONAL 모드는 STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_ IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION 모드의 조합으로 구성된 모드다. sql_mode의 TRADITIONAL 모드가 활성화되면 TRADITIONAL 모드가 아닐 때 경고로 처리되던 상황이 모두 에러로 바뀌고 SQL 문장은 실패한다.

영문 대소문자 구분

MySQL 서버는 설치된 운영체제에 따라 테이블명의 대소문자를 구분한다. 이는 MySQL의 DB나 테이블이 디스크의 디렉터리나 파일로 매핑되기 때문이다. 즉, 윈도우에 설치된 MySQL에서는 대소문자를 구분하지 않지만 유닉스 계열의 운영체제에서는 대소문자를 구분한다.

DB나 테이블명의 대소문자 구분은 가끔 윈도우에서 운영되던 MySQL 데이터를 리눅스로 가져오거나 그 반대의 경우 문제가 되기도 한다. MySQL 서버가 운영체제와 관계없이 대소문자 구분의 영향을 받지 않게 하려면 MySQL 서버의 설정 파일에 lower_case_table_names 시스템 변수를 설정하면 된다. 이 변수를 1로 설정하면 모두 소문자로만 저장되고, MySQL 서버가 대소문자를 구분하지 않게 해준다. 이 설정의 기본값은 0으로, DB나 테이블명에 대해 대소문자를 구분한다. 또한 윈도우와 macOS에서는 2를 설정할 수도 있는데, 이 경우에는 저장은 대소문자를 구분해서 하지만 MySQL의 쿼리에서는 대소문자를 구분하지 않게 해준다.

이러한 설정 자체를 떠나서 가능하면 초기 DB나 테이블을 생성할 때 대문자 또는 소문자만으로 통일해서 사용하는 편이 좋다.

MySQL 예약어

생성하는 데이터베이스나 테이블, 칼럼의 이름을 예약어와 같은 키워드로 생성하면 해당 칼럼이나 테이블을 SQL에서 사용하기 위해 항상 역따옴표(')나 쌍따옴표로 감싸야 한다. 이는 프로그램을 개발할 때뿐만 아니라 관리 작업을 할 때도 상당히 성가신 일이 될 것이다.

MySQL에서 이미 등록된 예약어의 개수는 적지 않으며, 예약어별로 문제가 되지 않는 키워드들도 있다. 이러한 예약어를 모두 구분해서 기억하기란 쉽지 않은 일이다. 매뉴얼을 통해 예약어인지 아닌지를 찾아보는 것도 방법이지만 가장 좋은 방법은 직접 MySQL에서 테이블을 생성해 보는 것이다. 이때 주의해야 할 사항은 역따옴표(')로 테이블명이나 칼럼명을 둘러싸지 않고 테이블을 생성해야 한다는 것이다. 역따옴표로 둘러싸고 테이블을 생성하는 경우 예약어를 사용했다고 하더라도 에러나 경고를 보여주지 않고, 그대로 테이블을 생성해 버리기 때문이다. 테이블을 생성할 때는 항상 역따옴표로 테이블 이나 칼럼의 이름을 둘러싸지 않은 상태로 생성하길 권장한다. 그래야만 예약어인지 아닌지를 MySQL 서버가 에러로 알려주기 때문이다. 테이블 생성이 실패하는 경우라면 해당 예약어는 역따옴표로 감싸지 않고는 사용할 수 없다는 것을 의미한다.

매뉴얼의 SQL 문법 표기를 읽는 방법

정확하고 더 상세한 문법을 확인하려면 MySQL 매뉴얼의 SQL 문법을 참조하는 것이 좋다.

위 표기법에서 대문자로 표현된 단어는 모두 키워드를 의미한다. 키워드는 대소문자를 특별히 구분하지 않고 사용할 수 있다. 그림의 표기법에서 이탤릭체로 표현한 단어는 사용자가 선택해서 작성하는 토큰을 의미하는데, 대부분 테이블명이나 칼럼명 또는 표현식을 사용한다. 이 항목이 SQL 키워드나 식별자(테이블명이나 칼럼명 등)가 아니라면 MySQL 매뉴얼에서는 그 항목에 대해 하단에 나열된 "value"나 "value_list"와 같이 다시 상세한 문법을 설명해 준다. 단말 노드도 중요 사항이나 주의사항이 있으면 매뉴얼의 하단에서 별도 설명이 추가되기 때문에 쉽게 참조할 수 있다.

  • 대괄호("[]"): 해당 키워드나 표현식 자체가 선택 사항임을 의미
  • 파이프("|"): 앞과 뒤의 키워드나 표현식 중에서 단 하나만 선택해서 사용 가능
  • 중괄호("()"): 괄호 내의 아이템 중에서 반드시 하나를 사용해야 하는 경우
  • "..." 표기: 앞에 명시된 키워드나 표현식의 조합이 반복될 수 있음을 의미

MySQL 연산자와 내장 함수

MySQL에서만 사용되는 연산자나 표기법이 있다. 여기에는 ANSI 표준 형태가 아닌 연산자가 많이 있는데, 이러한 부분은 MySQL을 처음 사용하는 사용자를 혼란스럽게 만들기도 한다. 가능하면 SQL의 가독성을 높이기 위해 ANSI 표준 형태의 연산자를 사용하길 권장한다. 일반적으로 각 DBMS의 내장 함수는 거의 같은 기능을 제공하지만 이름이 호환되는 것은 거의 없다.

리터럴 표기법 문자열

문자열

SQL 표준에서 문자열은 항상 흩따옴표(')를 사용해서 표시한다. 하지만 MySQL에서는 다음과 같이 쌍따옴표를 사용해 문자열을 표기할 수도 있다. 

SELECT * FROM departments WHERE dept_no='d001'; 
SELECT * FROM departments WHERE dept_no="d001";

또한 SQL 표준에서는 문자열 값에 흩따옴표가 포함돼 있을 때 홈따옴표를 두 번 연속해서 입력하면 된다. 하지만 MySQL에서는 쌍따옴표와 홈따옴표를 혼합해서 이러한 문제를 피해 가기도 한다. 마찬가지로 문자열 값이 쌍따옴표를 가지고 있을 때는 쌍따옴표를 두 번 연속해서 사용할 수 있다. 다음 예제 모두 MySQL에서 아무 문제없이 사용할 수 있는 문자열 표기 방법이다. 첫 번째와 두 번째 쿼리의 문자열 표기법은 SQL 표준이지만, 세 번째와 네 번째 표기법은 MySQL에서만 지원되는 방식이다. 

SELECT * FROM departments WHERE dept_no= 'd''001'; 
SELECT * FROM depar tments WHERE dept_no= 'd"001'; 
SELECT * FROM departments WHERE dept_no="d'001"; 
SELECT * FROM departments WHERE dept_no="d""001";

SQL에서 사용되는 식별자(테이블명이나 칼럼명 등)가 키워드와 충돌할 때 오라클이나 PostgreSQL에서는 쌍따옴표나 대괄호로 감싸서 충돌을 피한다. MySQL에서는 역따옴표(')로 감싸서 사용하면 예약어와의 충돌을 피할 수 있다.

CREATE TABLE tab_test ('table' VARCHAR(20) NOT NULL, ...); 
SELECT 'column' FROM tab_test;

MySQL 서버의 sql_mode 시스템 변숫값에 ANSI_QUOTES를 설정하면 쌍따옴표는 문자열 리터럴 표기에 사용할 수 없다. 그리고 테이블명이나 칼럼명의 충돌을 피하려면 역따옴표(')가 아니라 쌍따옴표를 사용해야 한다.

SQL 표준 표기법만 사용할 수 있게 강제하려면 sql_mode 시스템 변수값에 "ANSI"를 설정하면 된다. 하지만 이 설정은 대부분 쿼리의 작동 방식에 영향을 미치므로 프로젝트 초기에 적용하는 것이 좋다. 운용 중인 애플리케이션에서 sql_mode 설정을 변경하는 것은 상당히 위험하다.

숫자

숫자 값을 상수로 SQL에 사용할 때는 다른 DBMS와 마찬가지로 따옴표(' 또는 ") 없이 숫자 값을 입력하면 된다. 또한 문자열 형태로 따옴표를 사용하더라도 비교 대상이 숫자 값이거나 숫자 타입의 칼럼이면 MySQL 서버가 문자열 값을 숫자 값으로 자동 변환한다.

주의점 - 문자열 자동 변환

서로 다른 타입으로 WHERE 조건 비교가 수행되는 다음 퀴리를 잠깐 살펴보자. 

SELECT * FROM tab_test WHERE number_column='10001'; 
SELECT * FROM tab_test WHERE string_column=10001;

위 쿼리와 같이 두 비교 대상이 문자열과 숫자 타입으로 다를 때는 자동으로 타입의 변환이 발생한다. MySQL은 숫자 타입과 문자열 타입 간의 비교에서 숫자 타입을 우선시하므로 문자열 값을 숫자 값으로 변환한 후 비교를 수행한다. 첫 번째 쿼리는 주어진 상숫값을 숫자로 변환하는데, 이때는 상숫값 하나만 변환하므로 성능과 관련된 문제가 발생하지 않는다.

두 번째 쿼리는 주어진 상숫값이 숫자 값인데, 비교되는 칼럼은 문자열 칼럼이다. 이때 MySQL은 문자열 칼럼을 숫자로 변환해서 비교한다. 즉, string_column 칼럼의 모든 문자열 값을 숫자로 변환해서 비교를 수행해야하므로 string_column에 인덱스가 있더라도 이를 이용하지 못한다. string_column에 알파벳과 같은 문자가 포함된 경우에는 숫자 값으로 변환할 수 없으므로 쿼리 자체가 실패할 수도 있다. 원칙적으로 이러한 문제점을 제거하려면 숫자 값은 숫자 타입의 칼럼에만 저장해야 한다.

날짜

다른 DBMS에서 날짜 타입을 비교하거나 INSERT하려면 문자열을 DATE 타입으로 변환하는 코드가 필요하다. 하지만 MySQL에서는 정해진 형태의 날짜 포맷으로 표기하면 MySQL 서버가 자동으로 DATE나 DATETIME 값으로 변환하기 때문에 복잡하게 STR_TO_DATE() 같은 함수를 사용하지 않아도 된다.
날짜 타입의 칼럼과 문자열 값을 비교하는 경우 MySQL 서버는 문자열 값을 DATE 타입으로 변환해서 비교한다. SQL에서 문자열을 DATE 타입으로 강제 변환해서 비교하는 경우와 차이가 없다. 첫 번째 경우와 같이 비교한다고 해서 칼럼의 값을 문자열로 변환해서 비교하지 않기 때문에 해당 인덱스를 이용하는데 문제가 되지 않는다.

불리언

BOOL이나 BOOLEAN이라는 타입이 있지만 사실 이것은 TINYINT 타입에 대한 동의어일 뿐이다. 테이블의 칼럼을 BOOL로 생성한 뒤에 조회해보면 칼럼의 타입이 BOOL이 아니라 TINYINT라는 점을 알 수 있다. 불리언 타입을 꼭 사용하고 싶다면 ENUM 타입으로 관리하자.

MySQL 연산자

동등(Equal) 비교(=, <=>)

동등 비교는 다른 DBMS에서와 마찬가지로 "=" 기호를 사용해 비교를 수행하면 된다. 하지만 MySQL에서는 동등 비교를 위해 "<=>" 연산자도 제공한다. "<=>" 연산자는 "=" 연산자와 같으며, 부가적으로 NULL 값에 대한 비교까지 수행한다. MySQL에서는 이 연산자를 NULL-Safe 비교 연산자라고 한다.

부정(Not-Equal) 비교 (<>, !=)

"같지 않다" 비교를 위한 연산자는 "<>"를 일반적으로 많이 사용한다. 이와 함께 C/C++의 연산자인 "!="도 Not-Equal 연산자로 사용할 수 있다. 어느 쪽을 사용하든 특별히 문제가 되지는 않겠지만 하나의 SQL 문장에서 <>"와 "!="가 혼용되면 가독성이 떨어지므로 통일해서 사용하는 방법을 권장한다.

NOT(!) 연산자

TRUE 또는 FALSE 연산의 결과를 반대로(부정) 만드는 연산자로 "NOT"을 사용한다. 하지만 C/C++에서처럼 "!" 연산자를 같은 목적으로 사용할 수 있다. 사실 NOT이나 "!"는 불리언 값뿐만 아니라 숫자나 문자열 표현식에서도 사용할 수 있지만 부정의 결괏값을 정확히 예측할 수 없는 경우에는 사용을 자제하는 것이 좋다.

AND(&&)와 OR(||) 연산자

일반적으로 DBMS에서는 불리언 표현식의 결과를 결합하기 위해 AND나 OR를 사용한다. MySQL에서는 AND와 OR뿐만 아니라 "&&"와 "||"의 사용도 허용한다. "&&"는 AND 연산자와 같으며, "||"는 OR 연산자와 같다. 오라클에서는 "||"를 불리언 표현식의 결합 연산자가 아니라 문자열을 결합하는 연산자로 사용한다. 오라클에서 운영되던 애플리케이션을 MySQL로 이관한다거나 문자열 결합 연산에 "||"를 사용하고 싶을 수도 있다. 이때는sql_mode 시스템 변숫값에 PIPES_AS_CONCAT을 설정하면 된다. 물론 이 설정이 활성화되면 불리언 표현식을 결합할 때 "&&" 연산자는 사용할 수 있지만 "||" 연산자는 사용할 수 없다.

SQL의 가독성을 높이기 위해 다른 용도로 사용될 수 있는 "&&" 연산자와 "||" 연산자는 사용을 자제하는 것이 좋다.

나누기(/, DIV)와 나머지(%, MOD) 연산자

나누기 연산자는 일반적으로 알고 있는 "/" 연산자를 사용한다. 나눈 몫의 정수 부분만 가져오려면 DIV 연산자를 사용하고, 나눈 결과 몫이 아닌 나머지를 가져오는 연산자로는 "%" 또는 MOD 연산자(함수)를 사용한다.

REGEXP

연산자 문자열 값이 어떤 패턴을 만족하는지 확인하는 연산자이며, RLIKE는 REGEXP와 똑같은 비교를 수행하는 연산자다.

RLIKE는 가끔 문자열 값의 오른쪽 일치용 LIKE 연산자(Right LIKE)로 혼동할 때가 있는데, MySQL의 RLIKE는 정규 표현식(Regular expression)을 비교하는 연산자라는 점을 기억하자.

REGEXP 연산자를 사용하려면 다음 예제와 같이 REGEXP 연산자의 좌측에 비교 대상 문자열 값 또는 문자열 칼럼을, 우측에 검증하고자 하는 정규 표현식을 사용하면 된다.

LIKE 연산자

REGEXP 연산자보다는 훨씬 단순한 문자열 패턴 비교 연산자이지만 DBMS에서는 LIKE 연산자를 더 많이 사용한다. REGEXP 연산자는 인덱스를 전혀 사용하지 못한다는 단점이 있지만 LIKE 연산자는 인덱스를 이용해 처리할 수도 있다. LIKE 연산자는 정규 표현식을 검사하는 것이 아니라 어떤 상수 문자열이 있는지 없는지 정도를 판단하는 연산자다.

와일드 카드

LIKE에서 사용할 수 있는 와일드카드 문자는 "%"와 "_"가 전부다. REGEXP는 비교 대상 문자열의 일부에 대해서만 일치해도 TRUE를 반환하는 반면, LIKE는 항상 비교 대상 문자열의 처음부터 끝까지 일치하는 경우에만 TRUE를 반환한다.

  • %: 0 또는 1개 이상의 모든 문자에 일치(문자의 내용과 관계없이)
  • _: 정확히 1개의 문자에 일치(문자의 내용과 관계없이)

와일드카드 문자인 "%"와 "_" 문자 자체를 비교한다면 ESCAPE 절을 LIKE 조건 뒤에 추가해 이스케이프 문자(Escape sequence)를 설정할 수 있다. LIKE 연산자는 와일드카드 문자인 (%, _)가 검색어의 뒤쪽에 있다면 인덱스 레인지 스캔으로 사용할 수 있지만 와일드카드가 검색어의 앞쪽에 있다면 인덱스 레인지 스캔을 사용할 수 없으므로 주의해서 사용해야 한다.

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) 칼럼으로 구성된 프라이머리 키가 존재한다. 그래서 첫 번째 쿼리는 dept_no와 emp_no 조건 모두 인덱스를 이용해 범위를 줄여주는 방법으로 사용할 수 있다. 하지만 두 번째 쿼리에서 사용한 BETWEEN은 크다(>) 또는 작다(<) 연산자와 같이 범위를 읽어야 하는 연산자라서 dept_no가 'd003'보다 크거나 같고 'd005'보다 작거나 같은 모든 인덱스의 범위를 검색해야만 한다. 결국 BETWEEN이 사용된 두 번째 퀴리에서 emp_no=10001 조건은 비교 범위를 줄이는 역할을 하지 못한다.

BETWEEN과 IN

BETWEEN과 IN을 동일한 비교 연산자로 생각하는 사람도 있는데, 사실 BETWEEN은 크다와 작다 비교를 하나로 묶어 둔 것에 가깝다. 그리고 IN 연산자의 처리 방법은 동등 비교(=) 연산자와 비슷하다. 그림은 IN과 BETWEEN 처리 과정의 차이를 보여주는데, IN 연산자는 여러 개의 동등 비교(=)를 하나로 묶은 것과 같은 연산자라서 IN과 동등 비교 연산자는 같은 형태로 인덱스를 사용한다.

BETWEEN 조건을 사용하는 위의 쿼리는 dept_emp 테이블의 (dept_no, emp_no) 인덱스의 상당히 많은 레코드(전체 데이터의 1/3)를 읽는다. 하지만 실제로 가져오는 데이터는 1건밖에 안된다. 결국 이 쿼리는 10만 건을 읽어서 1건 반환하는 것이다. 그런데 이 쿼리를 다음과 같은 형태로 바꾸면 emp_no=10001 조건도 작업 범위를 줄이는 용도로 인덱스를 이용할 수 있게 된다.

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

BETWEEN이 선형으로 인덱스를 검색해야 하는 것과는 달리 IN은 동등(Equal) 비교를 여러 번 수행하는 것과 같은 효과가 있기 때문에 dept_emp 테이블의 인덱스(dept_no, emp_no)를 최적으로 사용할 수 있는 것이다.

IN을 활용한 성능 개선

여러 칼럼으로 인덱스가 만들어져 있는데, 인덱스 앞쪽에 있는 칼럼의 선택도가 떨어질 때는 IN으로 변경하는 방법으로 쿼리의 성능을 개선할 수도 있다. 실제 두 쿼리의 차이는 실행 계획을 통해서도 알 수 있다.

BETWEEN을 사용한 퀴리와 IN을 사용한 퀴리 둘 다 인덱스 레인지 스캔을 하고 있지만 실행 계획의 rows 칼럼에 표시된 레코드 건수는 매우 큰 차이가 있다. 예전 버전의 MySQL 서버에서는 BETWEEN 연산자를 IN 연산자로 변경하기 위해서는 우선 dept_no 칼럼의 값이 'd003'와 'd005' 사이의 모든 부서 코드 값을 가져와 "dept_no IN ('d003', 'd004' , 'd005')" 조건을 만들어야 했다. 하지만 MySQL 8.0 버전부터는 다음과 같이 "IN (subquery)" 형태로 작성하면 옵티마이저가 세미 조인 최적화를 이용해 더 빠른 퀴리로 변환해서 실행한다.

세미 조인

세미 조인이란 테이블 A와 B가 있고, A의 일부 레코드가 B와 일치하는 경우에 해당하는 A의 레코드만을 반환하고자 할 때 세미 조인을 사용할 수 있다. 이 경우, B 테이블의 정보는 반환되지 않고 A 테이블의 레코드만 반환된다.

SELECT A.*
FROM A
WHERE EXISTS (
    SELECT 1
    FROM B
    WHERE A.key = B.key
);

IN 연산자

IN은 여러 개의 값에 대해 동등 비교 연산을 수행하는 연산자다. 여러 개의 값이 비교되지만 범위로 검색하는 것이 아니라 여러 번의 동등 비교로 실행하기 때문에 일반적으로 빠르게 처리된다. IN 연산자는 다음과 같이 두 형태를 구분해서 생각해볼 필요가 있다.

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

Ref

  • RealMySQL 1권
profile
초코칩
초코칩처럼 달콤한 코드를 짜자

0개의 댓글