쿼리 작성과 최적화 (1/2)

쿼리 작성과 최적화

어플리케이션 코드를 튜닝해서 성능을 2배 개선하는 것은 쉽지 않지만 DBMS에서 몇십 배에서 몇백 배의 성능 향상이 이뤄지는 것은 상당히 흔한 일이다. 그만큼 SQL 처리에서 "어떻게(HOW)"를 이해하고, 쿼리를 작성하는 것이 중요하다.

 

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

대소문자 구분, 문자열 표기 방법 등과 같은 SQL 작성 규칙은 MySQL 서버의 시스템 설정에 따라 달라진다. MySQL 서버의 시스템 설정이 쿼리에 어떤 영향을 주는지 살펴보자.

 

SQL 모드

MySQL 서버의 sql_mode라는 시스템 설정에는 여러 개의 값이 동시에 설정될 수 있다. 그중에서 대표적으로 SQL 작성과 결과에 영향을 미치는 값은 어떤 것들이 있는지 살펴보고, sql_mode를 설정할 때는 구분자(,)를 이용해 아래 키워드들을 동시에 설정할 수 있다.

• STRICT_ALL_TABLES & STRICT_TRANS_TABLES: MySQL 서버에서 INSERT나 UPDATE 문장으로 데이터를 변경하는 경우 컬럼의 타입과 저장되는 값의 타입이 다를 때 자동으로 타입 변경을 수행한다. 이때 타입이 적절히 변환되기 어렵거나 컬럼에 저장될 값이 없거나 값의 길이가 컬럼의 최대 길이보다 큰 경우 STRICT_TRANS_T ABLES 옵션은 InnoDB 같은 트랜잭션을 지원하는 스토리지 엔진에만 엄격한 모드를, STRICT_ALL_TABLES 옵션은 모든 스토리지 엔진에 대해 엄격한 모드를 적용한다.
• ANSI_QUOTES: MySQL에서는 문자열 값(리터럴)을 표현하기 위해 홑따옴표와 쌍따옴표를 동시에 사용할 수 있는데 ANSI_QUOTES를 설정하면 홑따옴표만 문자열 값 표기로 사용할 수 있고, 쌍따옴표는 컬럼명이나 테이블명과 같은 식별자를 표기하는 데만 사용할 수 있다.
• ONLY_FULL_GROUP_BY: MySQL의 쿼리에서는 GROUP BY 절에 포함되지 않은 컬럼이더라도 집합 함수의 사용없이 그대로 SELECT 절이나 HAVING 절에 사용할 수 있다. ONLY_FULL_GROUP_BY를 활성화하면 GROUP BY 절이 사용된 문장의 SELECT 절에는 GROUP BY 절에 명시된 컬럼과 집계 함수(COUNT 또는 SUM과 같은 그룹 함수)만 사용할 수 있다.
• PIPE_AS_CONCAT: MySQL에서 "||"는 OR 연산자와 같은 의미로 사용된다. 하지만 sql_mode 시스템 변수에 PIPE_AS_CONCAT 값을 설정하면 오라클과 같이 문자열 연결 연산자(CONCAT)로 사용할 수 있다.
• PAD_CHAR_TO_FULL_LENGTH: MySQL에서는 CHAR 타입이라고 하더라도 VARCHAR와 같이 유효 문자열 뒤의 공백 문자는 제거되어 반환된다. 하지만 CHAR 타입의 컬럼값을 가져올 때 뒤쪽의 공백이 제거되지 않고 반환되어야 한다면 sql_mode 시스템 설정에 PAD_CHAR_TO_FULL_LENGTH를 추가하면 된다.
• NO_BACKSLASH_ESCAPES: MySQL에서도 일반적인 프로그래밍 언어에서처럼 역슬래시 문자을 이스케이프 문자로 사용할 수 있다. NO_BACKSLASH_ESCAPES를 활성화하면 역슬래시 문자도 다른 문자와 동일하게 취급하여 이스케이프 용도로 사용하지 못한다.
• IGNORE_SPACE: MySQL에서 스토어드 프로시저나 함수의 이름 뒤에 공백이 있으면 "스토어드 프로시저나 함수가 없습니다"라는 에러가 출력될 수도 있다. MySQL에서는 스토어드 프로서저나 함수명과 괄호 사이에 있는 공백까지도 스토어드 프로시저나 함수의 이름으로 간주한다. IGNORE_SPACE를 추가하면 프로시저나 함수명과 괄호 사이의 공백은 무시된다.
• REAL_AS_FLOAT: MySQL 서버에서는 부동 소수점 타입은 FLOAT과 DOUBLE 타입이 지원되는데, REAL 타입은 DOUBLE 타입의 동의어로 사용된다. 하지만 REAL_AS_FLOAT 모드가 활성화되면 MySQL 서버는 REAL이라는 타입이 FLOAT 타입의 동의어로 바뀐다.
• NO_ZERO_IN_DATE & NO_ZERO_DATE: 두 옵션이 활성화되면 MySQL 서버는 DATE 또는 DATETIME 타입의 컬럼에 "2020-00-00" 또는 "0000-00-00"과 같은 잘못된 날짜를 저장하는 것이 불가능해진다. 이처럼 실제 존재하지 않는 날짜를 저장하지 못하게 하려면 sql_mode에 NO_ZERO_DATE와 NO_ZERO_IN_DATE 모드를 활성화한다.
• ANSI: "REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY" 모드의 조합으로 MYSQL 서버가 최대한 SQL 표준에 맞게 동작하게 만들어준다.
• TRANSITIONAL: "STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION" 모드의 조합으로 구성된 모드로 TRANDITIONAL 모드가 아닐 때 경고로 처리되던 상황이 모두 에러로 바뀌고 SQL 문장은 실패한다.

 

영문 대소문자 구분

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

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

 

MySQL 예약어

생성하는 데이터베이스나 테이블, 컬럼의 이름을 예약어와 같은 키워드로 생성하려면 항상 역따옴표(`)나 쌍따옴표로 감싸야 한다. 이는 개발 또는 관리에 있어 모두 성가신 일이 될 수 있기 때문에 예약어를 사용한 테이블이나 컬럼은 생성하지 않는 것이 좋다.

 

MySQL 연산자

일반 DBMS에서 사용되는 기본적인 연산자는 MySQL에서도 거의 비슷하게 사용되지만 MySQL에서만 사용되는 연산자나 표기법이 있다.

 

문자열

MySQL에서는 홑따옴표와 쌍따옴표를 사용해 문자열을 표기할 수 있다. 문자열 값에 홑따옴표가 포함되어 있는 경우에는 홑따옴표를 두 번 연속해서 사용할 수도 있지만 MySQL에서는 쌍따옴표와 홑따옴표를 혼합하여 사용해도 아무런 문제가 없는 문자열 표기 방법이다.

 

숫자

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

숫자 값을 상수로 SQL에 사용할 때는 따옴표 없이 숫자 값을 입력하면 되는데 위 쿼리처럼 두 비교 대상이 문자열과 숫자 타입으로 다를 때는 자동으로 타입의 변환이 발생한다. 첫 번째 쿼리는 주어진 상숫값을 숫자로 변환하는데, 상숫값 하나만 변환하므로 성능과 관련된 문제가 발생하지 않는다. 두 번째 쿼리는 주어진 상숫값이 숫자 값인데, 비교되는 컬럼은 문자열 컬럼이기 때문에 string_column 컬럼의 모든 문자열 값을 숫자로 변환해서 비교를 수행해야 하므로 string_column에 인덱스가 있더라도 이를 이용하지 못한다. string_column에 알파벳과 같은 문자가 포함된 경우에는 숫자 값으로 변환할 수 없으므로 쿼리 자체가 실패할 수도 있다.

 

날짜

SELECT * FROM dept_emp WHERE from_date = '2011-04-29';
SELECT * FROM dept_emp WHERE from_date = STR_TO_DATE('2011-04-29', '%Y-%m-%d');

다른 DBMS에서 날짜 타입을 비교하거나 INSERT하려면 문자열을 DATE 타입으로 변환하는 코드가 필요하지만 MySQL에서는 정해진 형태의 날짜 포맷으로 표기하면 자동으로 DATE나 DATETIME 값으로 변환하기 때문에 복잡하게 STR_TO_DATE() 같은 함수를 사용하지 않아도 된다.

두 쿼리는 차이 없이 첫 번째 쿼리와 같이 비교한다고 해서 from_date 컬럼의 값을 문자열로 변환해서 비교하지 않기 때문에 from_date 컬럼으로 생성된 인덱스를 이용하는 데 문제가 되지 않는다.

 

불리언

BOOL이나 BOOLEAN이라는 타입이 있지만 사실 이것은 TINY 타입에 동의어일 뿐이다. MySQL에서는 FALSE가 정숫값 0이 되고, TRUE는 1만을 의미한다는 점에 주의해야 한다. 그래서 숫자 값이 저장된 컬럼을 TRUE나 FALSE로 조회하면 0이나 1 이외의 숫자 값은 조회되지 않는다.

 

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

MySQL에서는 동등 비교를 위해 "="에 더해 "<=>" 연산자도 제공한다. "<=>" 연산자는 "=" 연산자와 같으며, 부가적으로 NULL 값에 대한 비교까지 수행한다. MySQL에서는 이 연산자를 NULL-safe 비교 연산자라고 하는데 위 쿼리의 차이에서 알 수 있듯이 NULL은 "IS NULL" 연산자 이외에 비교할 방법이 없다. 따라서 첫 번째 쿼리는 한쪽이 NULL이면 비교 결과도 NULL을 반환하지만 NULL-safe 연산자를 사용하면 양쪽 모두 NULL이라면 TRUE, 한쪽만 NULL이라면 FALSE를 반환한다. 즉, "<=>" 연산자는 NULL을 하나의 값으로 인식하고 비교하는 방법이라고 볼 수 있다.

 

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

"같지 않다" 비교를 위해 "<>" 연산자를 일반적으로 많이 사용할 수 있지만, "!=" 연산자도 Not-Equal 연산자로 사용할 수 있다.

 

NOT 연산자(!)

TRUE 또는 FALSE 연산의 결과를 반대로(부정) 만드는 연산자로 "NOT" 또는 "!"을 사용한다.

 

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

MySQL에서는 불리언 표현식의 결과를 결합하기 위해 AND(&&)와 OR(||)을 사용한다. 오라클에서는 "||"는 문자열 결합하는 연산자로 사용하는데 MySQL에서는 sql_mode 시스템 변숫값에 PIPE_AS_CONCAT을 설정하면 "||"는 불리언 표현식을 결합할 때 사용할 수 없고 오라클처럼 문자열 결합 연산자로 사용할 수 있다. SQL의 가독성을 높이기 위해 다른 용도로 사용될 수 있는 "&&" 연산자와 "||" 연산자는 사용을 자제하는 것이 좋다.

또한 AND와 OR 연산자는 어떤 연산자가 우선순위를 가질지 중요한데 위에서 확인할 수 있듯이 AND 연산자가 더 높은 우선순위를 가진다.

 

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

나누기 연산자는 "/", DIV 연산자를 사용하고, 결과 몫이 아닌 나머지를 가져오는 연산자로는 "%", MOD 연산자가 있다.

 

REGEXP 연산자

'abc' 문자열 값이 'x', 'y', 'z' 문자로 시작하는지 검증하는 표현식

문자열 값이 어떤 패턴을 만족하는지 확인하는 연산자이며, RLIKE는 REGEXP와 똑같은 비교를 수행하는 연산자다. REGEXP 연산자를 사용하려면 위 예제처럼 REGEXP 연산자의 좌측에 비교 대상 문자열 값 또는 문자열 컬럼을 우측에 검증하고자 하는 정규 표현식을 사용하면 된다.

 

 LIKE 연산자

REGEXP 연산자는 인덱스를 전혀 사용하지 못한다는 단점이 있지만 LIKE 연산자는 인덱스를 이용해 처리할 수도 있다. LIKE 연산자는 정규 표현식을 검사하는 것이 아니라 어떤 상수 문자열이 있는지 없는지 정도를 판단하는 연산자다.

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

 

와일드카드 문자인 '%'나 '_' 문자 자체를 비교한다면 EXCAPE 절을 LIKE 조건 뒤에 추가해 이스케이프 문자(Escape sequence)를 설정할 수 있다.

 

와일드 카드를 사용해 인덱스 레인지 스캔을 못하는 경우

LIKE 연산자는 와일드 카드 문자가 검색어의 뒤쪽에 있다면 인덱스 레인지 스캔을 사용할 수 있지만 와일드카드가 검색어의 앞쪽에 있다면 인덱스 레인지 스캔을 사용할 수 없으므로 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔 방식으로 쿼리가 처리되기 때문에 주의해서 사용해야 한다.

 

BETWEEN 연산자

BETWEEN 연산자는 "크거나 같다"와 "작거나 같다"라는 두 개의 연산자를 하나로 합친 연산자다. BETWEEN 연산자는 다은 비교 조건과 결합해 하나의 인덱스를 사용할 때 주의해야 할 점이 있다.

 

BETWEEN 연산자를 이용해 부서 번호와 사원 번호로 dept_emp 테이블을 조회하는 쿼리를 작성한다고 가정해보자. BETWEEN은 크다와 작다 비교를 하나로 묶어둔 것에 가깝기 때문에 emp_no=10001 조건은 비교 범위를 줄이는 역할을 하지 못하고 dept_no가 'd003'보다 크거나 같고 'd005'보다 작거나 같은 모든 인덱스의 범위를 검색해야 한다. 따라서 BETWEEN 조건을 사용하는 쿼리는 dept_emp 테이블의 (dept_no, emp_no) 인덱스의 상당히 많은 레코드(전체 레코드의 1/3 = 약 10만 건)를 읽지만 실제로 가져오는 데이터는 1건밖에 안 된다.

 

그런데 이 쿼리를 IN 연산자를 사용하면 emp_10001 조건도 작업 범위를 줄이는 용도로 인덱스를 사용할 수 있게 된다. BETWEEN이 선형으로 인덱스를 검색해야 하는 것과는 달리 IN은 동등(Equal) 비교를 여러 번 수행하는 것과 같은 효과가 있기 때문에 dept_emp 테이블의 인덱스(dept_no, emp_no)를 최적으로 사용할 수 있는 것이다.

 

BETWEEN 연산자 실행 계획

IN 연산자 실행 계획

BETWEEN을 사용한 쿼리와 IN을 사용한 쿼리 둘 다 인덱스 레인지 스캔을 사용하고 있지만 실행 계획의 rows 컬럼에 표시된 레코드 건수는 매우 큰 차이가 있음을 알 수 있다. BETWEEN 비교를 사용한 쿼리에서는 부서 번호가 'd003'인 레코드부터 'd005'인 레코드의 전체 범위를 다 비교해야 하지만 IN을 사용한 쿼리에서는 부서 번호와 사원 번호가 (('d003', 10001), ('d004', 10001), ('d005', 10001)) 조합인 레코드만 비교해 보면 되기 때문이다.

 

IN 연산자

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

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

위 쿼리는 IN 절의 상숫값이 단순 스칼라값이 아니라 튜플이 사용됐다. 실행 계획을 살펴보면 dept_emp 테이블의 프라이머리 키를 이용했는데, key_len 컬럼이 20인 것으로 보아 dept_no 컬럼(4글자X4바이트)과 emp_no 컬럼(4바이트)을 모두 이용해 인덱스 레인지 스캔을 실행한다는 것을 확인할 수 있다.

NOT IN의 실행 계획은 인덱스 풀 스캔으로 표시되는데, 동등이 아닌 부정형 비교여서 인덱스를 이용해 처리 범위를 줄이는 조건으로는 사용할 수 없기 때문이다. NOT IN 연산자가 프라이머리 키와 비교될 때 가끔 쿼리의 실행계획에 인덱스 레인지 스캔이 표시되는 경우가 있다. 하지만 이는 InnoDB 테이블에서 프라이머리 키가 클러스터링 키이기 때문일 뿐 실제 IN과 같이 효율적으로 실행된다는 것을 의미하지 않는다.

 

MySQL 내장 함수

MySQL의 함수는 MySQL에서 기본으로 제공하는 내장 함수와 사용자가 직접 작성해서 추가할 수 있는 사용자 정의 함수(UDF, User Defined Function)로 구분된다. 아래에서 살펴볼 내장 함수나 사용자 정의 함수는 스토어드 프로그램으로 작성되는 프로시저나 스토어드 함수와는 다르므로 혼동하지 않도록 주의하자.

 

NULL 값 비교 및 대체(IFNULL, ISNULL)

IFNULL()은 컬럼이나 표현식의 값이 NULL인지 비교하고, NULL이면 다른 값으로 대체하는 용도로 사용할 수 있는 함수다. IFNULL() 함수에는 두 개의 인자를 전달하는데, 첫 번째 인자는 NULL인지 아닌지 비교하려는 컬럼이나 표현식을, 두 번째 인자로는 첫 번째 인자의 값이 NULL일 경우 대체할 값이나 컬럼을 설정한다. ISNULL() 함수는 이름 그대로 인자로 전달한 표현식이나 컬럼의 값이 NULL이면 TRUE(1), NULL이 아니면 FALSE(0)를 반환한다.

 

현재 시각 조회(NOW, SYSDATE)

두 함수 모두 현재의 시간을 반환하는 함수로서 같은 기능을 수행한다. 하지만 하나의 SQL에서 모든 NOW() 함수는 같은 값을 가지지만 SYSDATE() 함수는 하나의 SQL 내에서도 호출되는 시점에 따라 결괏값이 달라지므로 상수가 아니다. 위 쿼리의 결과를 통해 마지막 SYSDATE() 함수는 2초 동안의 차이가 있음을 알 수 있다.

SYSDATE() 함수는 이러한 특성 탓에 두 가지 큰 잠재적인 문제가 있다.

• 첫 번째로는 SYSDATE() 함수가 사용된 SQL은 레플리카 서버에서 안정적으로 복제되지 못한다.
• 두 번째로는 SYDATE() 함수와 비교되는 컬럼은 인덱스를 효율적으로 사용하지 못한다.

 

NOW() 함수를 사용한 경우

SYSDATE() 함수를 사용한 경우

위 예시에서 첫 번째 쿼리는 emp_no와 from_date 컬럼 모두 적절히 인덱스를 사용했기 때문에 인덱스의 전체 길이인 7바이트를 모두 사용했지만 두 번째 쿼리는 emp_no 컬럼만 인덱스를 사용했기 때문에 4바이트만 레인지 스캔에 이용했다. 또한 인덱스 스캔을 할 때도 매번 비교되는 레코드마다 함수를 실행해야 하기 때문에 꼭 필요한 경우가 아니라면 SYSDATE() 함수는 사용하지 않는 것이 좋다.

 

날짜와 시간의 포맷(DATE_FORMAT, STR_TO_DATE)

DATETIME 타입의 컬럼이나 값을 원하는 형태의 문자열로 변환해야 할 때는 DATE_FORMAT() 이용하면 되고, 날짜의 각 부분을 의미하는 지정자는 다음과 같다.

• %Y: 4자리 연도
• %m: 2자리 숫자 표시의 월 (01 ~ 12)
• %d: 2자리 숫자 표시의 일자 (01 ~ 31)
• %H: 2자리 숫자 표시의 시 (00 ~ 23)
• %i: 2자리 숫자 표시의 분 (00 ~ 59)
• %s: 2자리 숫자 표시의 초 (00 ~ 59)

 

날짜와 시간의 연산(DATE_ADD, DATE_SUB)

특정 날짜에서 연도나 월일 또는 시간 등을 더하거나 뺄 때는 DATE_ADD() 함수나 DATE_SUB() 함수를 사용한다. DATE_ADD()나 DATE_SUB()는 두 개의 인자를 필요로 하는데, 첫 번째 인자는 연산을 수행할 날짜이며, 두 번째 인자는 더하거나 뺴고자 하는 월의 수나 일자의 수 등을 INTERVAL n [YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, ...] 형태로 입력하면 된다.

 

타임스탬프 연산(UNIX_TIMESTAMP, FROM_UNIXTIME)

UNIX_TIMESTAMP() 함수는 '1970-01-01 00:00:00'으로부터 경과된 초의 수를 반환하는 함수다. 다른 운영체제나 프로그래밍 언어에서도 같은 방식으로 타임스탬프를 산출하는 경우에는 상호 호환해서 사용할 수 있다. FROM_UNIXIMTE() 함수는 인자로 전달한 타임스탬프 값을 DATETIME 타입으로 변환하는 함수다.

MySQL의 TIMESTAMP 타입은 4 바이트 숫자 타입으로 저장되기 때문에 실제로 가질 수 있는 값의 범위는 '1970-01-01 00:00:00' ~ '2038-01-09 03:14:07'까지의 날짜 값만 가능하다.

 

문자열 처리(RPAD, LPAD / RTRIM, LTRIM, TRIM)

RPAD()와 LPAD() 함수는 문자열의 좌측 또는 우측에 문자를 덮붙여서 지정된 길이의 문자열로 만드는 함수다. 다음 3개의 인자를 필요로 한다.

• 첫 번째 인자: 패딩 처리를 할 문자열
• 두 번째 인자: 몇 바이트까지 패딩할 것인지(정확히는 패딩 적용 후 결과물로 반환될 문자열의 최대 길이)
• 세 번째 인자: 패딩할 문자

 

RTRIM()과 LTRIM() 함수는 문자열의 우측 또는 좌측에 연속된 공백 문자를 제거하고, TRIM()은 LTRIM()과 RTRIM()을 동시에 수행하는 함수다.

 

문자열 결합(CONCAT)

여러 개의 문자열을 연결해서 하나의 문자열로 반환하는 함수로, 인자의 개수는 제한이 없다.

 

GROUP BY 문자열 결합(GROUP_CONCAT)

COUNT()나 MAX(), MIN(), AVG() 등과 같은 그룹 함수(Aggregate, 여러 레코드의 값을 병합해서 하나의 값을 만들어내는 함수) 중 하나다. 주로 GROUP BY와 함께 사용하며, GROUP BY가 없는 SQL에서 사용하면 단 하나의 결괏값만 만들어낸다. GROUP_CONCAT() 함수는 값들을 먼저 정렬한 후 연결하거나 각 값의 구분자 설정도 가능하며, 여러 값 중에서 중복을 제거하고 연결하는 것도 가능하므로 상당히 유용하게 사용된다.

위 예제 중 첫 번째 쿼리는 departments 테이블의 모든 레코드에서 dept_no 컬럼의 값을 "|" 구분자(기본 구분자: ",")를 사용하여 반환한다.

 

위 예제는 dept_emp 테이블에서 emp_no 컬럼의 역순으로 정렬해서 dept_no 컬럼의 값을 연결해서 가져오는 쿼리다. 이 예제에서 GROUP_CONCAT() 함수 내에서 정의된 ORDER BY는 쿼리 전체적으로 설정된 ORDER BY와 무관하게 처리된다. 

GROUP_CONCAT() 함수는 지정한 컬럼의 값들을 연결하기 위해 제한적인 메모리 버퍼 공간을 사용한다. 어떤 쿼리에서 GROUP_CONCAT() 함수의 결과가 시스템 변수에 지정된 크기를 초과하면 쿼리에서 경고 메시지(Warning)가 발생한다.  JDBC로 실행될 때는 경고가 아니라 에러로 취급되어 쿼리가 실패하므로 지정된 버퍼 크기를 초과하지 않게 주의해야 하는데 group_concat_max_len 시스템 변수로 조정할 수 있다.

 

MySQL 8.0 이전 버전까지는 GROUP BY의 그룹별로 개수를 제한해서 가져올 수 있는 방법이 없었지만 MySQL 8.0 버전부터는 용도에 맞게 위와 같이 래터럴 조인이나 윈도우 함수를 이용할 수 있다.

 

값의 비교와 대체(CASE WHEN ... THEN ... END)

크게 2가지 방법으로 사용할 수 있는데, 위처럼 단순히 코드 값을 실제 값으로 변환하거나 특정 일자를 기준으로 이전인지 이후인지 비교해 설명을 붙이는 용도로 사용할 수 있다. 이 방식은 비교 연산자(=, <, >, ...)로 비교할 수 있을 때 비교하고자 하는 컬럼이나 표현식을 CASE WHEN 키워드 사이에 두고, 비교 기준값을 WHEN 뒤에 입력해서 사용하는 방식이다.

 

위 쿼리는 Marketing('d001') 부서에 소속된 적이 있는 모든 사원의 가장 최근 급여를 조회할 때 성별이 여자인 경우에만 최종 급여 정보가 필요하고, 남자이면 그냥 이름만 필요한 경우에 CASE WHEN을 사용한 쿼리이다.

위처럼 남자인 경우는 salaries 테이블을 조회할 필요가 없는데, 서브쿼리는 실행되므로 불필요한 작업이기 때문에 불필요한 작업을 제거하기 위해 CASE WHEN으로 서브쿼리를 감싸면 필요한 경우에만 서브쿼리를 실행시킬 수 있다.

 

타입 변환(CAST, CONVERT)

Prepared Statement를 제외하면 SQL은 텍스트(문자열) 기반으로 작동하기 때문에 SQL에 포함된 모든 입력값은 문자열처럼 취급된다. 이럴 때 명시적으로 타입의 변환이 필요한다면 CAST() 함수를 이용하면 된다. CAST() 함수를 통해 변환할 수 있는 데이터 타입은 DATE, TIME, DATETIME, BINARY, CHAR, DECIMAL, SIGNED, INTEGER, UNSINGED INTEGER다.

 

이진값과 16진수 문자열(Hex String) 변환(HEX, UNHEX)

HEX() 함수는 이진값을 사람이 읽을 수 있는 형태의 16진수의 문자열(Hex String)로 변환하는 함수이고, UNHEX()는 16진수의 문자열을 읽어서 이진값(BINARY)으로 변환하는 함수다.

 

벤치마크(BENCHMARK)

BENCHMARK() 함수는 디버깅이나 간단한 함수의 성능 테스트용으로 아주 유용한 함수다. BENCHMARK() 함수는 2개의 인자를 필요로 한다. 첫 번째 인자는 반복해서 수행할 횟수이며, 두 번째 인자로는 반복해서 실행할 표현식을 입력하면 된다. 두 번째 인자의 표현식은 반드시 스칼라값(하나의 컬럼을 가진 하나의 레코드)을 반환하는 표현식이어야 한다.

SELECT BENACH(10, expr)와 SELECT expr을 10번 직접 실행하는 것에는 차이가 있다. SQL 클라이언트와 같은 도구로 "SELECT expr"을 10번 실행하는 경우에는 매번 쿼리의 파싱이나 최적화, 테이블 잠금이나 네트워크 비용 등이 소요된다. 하지만 "SELECT BENCHMARK(10, expr)"로 실행하는 경우에는 벤치마크 횟수에 관계없이 단 1번의 네트워크, 쿼리 파싱 및 최적화 비용이 소요되기 때문에 위 예제에서는 생각보다 짧은 시간에 완료된 것이다.

 

JSON 포맷(JSON_PRETTY)

MySQL 클라이언트에서 JSON 데이터의 기본적인 표시 방법은 단순 텍스트 포맷인데, 이 포맷은 JSON 컬럼값에 대한 가독성이 떨어진다. 하지만 위처럼 JSON_PRETTY() 함수를 이용하면 JSON 컬럼의 값을 읽기 쉬운 포맷으로 변환해준다.

 

JSON 필드 크기(JSON_STORAGE_SIZE)

JSON 데이터는 텍스트 기반이지만 MySQL 서버는 디스크의 저장 공간을 절약하기 위해 JSON 데이터를 실제 디스크에 저장할 때 BSON(Binary JSON) 포맷을 사용한다. 하지만 BSON으로 변환됐을 때 저장 공간의 크기가 얼마나 될지 예측하기는 어렵다. 이를 위해 MySQL 서버에서는 JSON_STORAGE_SIZE() 함수를 제공하고, 반환되는 값의 단위는 바이트(Byte)다.

 

JSON 필드 추출(JSON_EXTRACT)

JSON 도큐먼트에서 특정 필드의 값을 가져오는 방법은 여러 가지가 있지만 가장 일반적인 방법은 JSON_EXTRACT() 함수를 이용하는 것이다. JSON_EXTRACT() 함수는 다음 2개의 인자를 필요로 한다. 그리고 JSON_UNQUOTE() 함수를 사용해서 따옴표 없이 값을 가져올 수도 있다.

• 첫 번째 인자: JSON 데이터가 저장된 컬럼 또는 JSON 도큐먼트 자체
• 두 번째 인자: 가져오고자 하는 필드의 JSON 경로(JSON Path)

"->" 연산자는 JSON_EXTRACT() 함수와 동일한 기능이며, "->>" 연산자는 JSON_UNQUOTE() 함수와 JSON_EXTRACT() 함수를 조합한 것과 동일한 기능이다.

 

JSON 오브젝트 포함 여부 확인(JSON_CONTAINS)\

JSON_CONTAINS() 함수의 첫 번째 인자는 JSON 데이터를 저장하고 있는 컬럼이나 JSON 도큐먼트이며, 두 번째 인자는 JSON 오브젝트(도큐먼트 또는 필드값)를 사용한다. 즉, 첫 번째 인자로 주어진 JSON 도큐먼트에서 두 번째 인자의 JSON 오브젝트가 존재하는지를 검사하는 함수다. 세 번째 인자는 선택적으로 부여할 수 있는데, 세 번째 인자로 JSON 경로를 명시하면 해당 경로에 JSON 오브젝트가 존재하는지 여부를 체크한다.

 

JSON 오브젝트 생성(JSON_OBJECT)

RDBMS 컬럼의 값을 이용해 JSON 오브젝트를 생성하는 함수다.

 

JSON 컬럼으로 집계(JSON_OBJECTAGG & JSON_ARRAYAGG)

JSON_OBJECTAGG()와 JSON_ARRAYAGG() 함수는 GROUP BY 절과 함께 사용되는 함수로서, RDBMS 컬럼의 값들을 모아 JSON 배열 또는 도큐먼트를 생성하는 함수다.

 

JSON 데이터를 테이블로 변환(JSON_TABLE)

JSON_TABLE() 함수는 JSON 데이터의 값들을 모아서 RDMBS 테이블을 만들어 반환한다. 이때 JSON_TABLE() 함수가 만들어서 반환하는 테이블의 레코드 건수는 원본 테이블(JSON_TABLE() 함수 직전에 명시된 테이블)과 동일한 레코드 건수를 가진다.

 

WHERE 절과 GROUP 절, ORDER BY 절의 인덱스 사용

WHERE 절의 조건뿐만 아니라 GROUP BY나 ORDER BY 절도 인덱스를 이용해 빠르게 처리할 수 있다.

 

인덱스를 사용하기 위한 기본 규칙

WHERE 절이나 ORDER BY 또는 GROUP BY가 인덱스를 사용하려면 기본적으로 인덱스된 컬럼의 값 자체를 변환하지 않고 그대로 사용한다는 조건을 만족해야 한다.  인덱스는 컬럼의 값을 아무런 변환 없이 B-Tree에 정렬해서 저장하기 때문에 WHERE 조건이나 GROUP BY 또는 ORDER BY에서도 원본값을 검색하거나 정렬할 때만 B-Tree에 정렬된 인덱스를 이용한다.

 

또한, 위 예제의 쿼리를 확인해보면 age라는 컬럼에 인덱스가 준비되어 있어서 실행 계획의 type 컬럼에 "ref"나 "range"가 표시되어야 할 것으로 기대하지만 "index"가 표시되어있다. "index"는 인덱스 풀 스캔을 의미하는데 인덱스 레인지 스캔을 사용하지 못하는 이유는 age 컬럼의 데이터 타입(VARCHAR 타입)과 비교되는 값 2(INTEGER 타입)의 데이터 타입이 다르기 때문이다. 이렇게 비교되는 두 값의 타입이 다를 때 MySQL 옵티마이저가 내부적으로 문자열 타입을 숫자 타입으로 변환한 후 비교 작업을 처리하기 때문에 인덱스 레인지 스캔이 불가능한 것이다.

 

따라서 위 쿼리처럼 변경하면 인덱스 레인지 스캔을 사용하도록 유도할 수 있다.

 

WHERE 절의 인덱스 사용

GROUP BY나 ORDER BY와는 달리 WHERE 조건절에 나열된 순서가 인덱스와 다르더라도 MySQL 서버 옵티마이저는 인덱스를 사용할 수 있는 조건들을 뽑아서 최적화를 수행할 수 있다. 즉, WHERE 절에서의 각 조건이 명시된 순서는 중요치 않고 인덱스를 구성하는 컬럼에 대한 조건이 있는지 없는지가 중요하다.

 

ALTER TABLE ... ADD INDEX ix_col1234 (col_1 ASC, col_2 DESC, col_3 ASC, col_4 ASC);

MySQL 8.0 이전 버전까지는 하나의 인덱스를 구성하는 각 컬럼의 정렬 순서가 혼합되어 사용할 수 없었다. 하지만 MySQL 8.0 버전부터는 위처럼 인덱스를 구성하는 컬럼별로 정순(오른차순)과 역순(내림차순) 정렬을 혼합해서 생성할 수 있게 개선되었다.

 

SELECT *
FROM employees
WHERE first_name = 'Kebin' OR last_name = 'Poly';

위 쿼리에서 first_name = 'Kebin' 조건은 인덱스를 이용할 수 있지만 last_name = 'Poly'는 인덱스를 사용할 수 없다. 두 조건이 AND 연산자로 연결됐다면 first_name의 인덱스를 이용하겠지만 OR 연산자로 연결됐기 때문에 옵티마이저는 풀 테이블 스캔을 선택할 수 밖에 없다. 만약 first_name과 last_name 컬럼에 각각 인덱스가 있다면 index_merge 접근 방법으로 실행하여 풀 테이블 스캔보다는 빠르지만 여전히 제대로 된 인덱스 하나를 레인지 스캔하는 것보다는 느리기 때문에 OR로 연결되면 읽어서 비교해야 할 레코드가 더 늘어나기 때문에 WHERE 조건에 OR 연산자가 있다면 주의해야 한다.

 

GROUP BY 절의 인덱스 사용

SQL에 GROUP BY가 사용되면 인덱스의 사용 여부는 어떻게 결정될까? GROUP BY 절에 명시된 컬럼의 순서가 인덱스를 구성하는 컬럼의 순서가 같으면 GROUP BY 절은 인덱스를 이용할 수 있다.

• GROUP BY 절에 명시된 컬럼이 인덱스 컬럼의 순서와 같아야 한다.
• 인덱스를 구성하는 컬럼 중에서 뒤쪽에 있는 컬럼은 GROUP BY 절에 명시되지 않아도 인덱스를 사용할 수 있지만 인덱스의 앞쪽에 있는 컬럼이 GROUP BY 절에 명시되지 않으면 인덱스를 사용할 수 없다.
• WHERE 조건절과는 달리 GROUP BY 절에 명시된 컬럼이 하나라도 인덱스에 없으면 GROUP BY 절은 전혀 인덱스를 이용하지 못한다.

 

ORDER BY 절의 인덱스 사용

MySQL에서 GROUP BY와 ORDER BY는 처리 방법이 상당히 비슷하다. 그래서 ORDER BY 절의 인덱스 사용 여부는 GROUP BY의 요건과 거의 흡사하다. 하지만 ORDER BY는 조건이 하나 더 있는데, 정렬되는 각 컬럼의 오름차순(ASC) 및 내림차순(DESC) 옵션이 인덱스와 같거나 정반대인 경우에만 사용할 수 있다는 것이다.

 

WHERE 조건과 ORDER BY(또는 GROUP BY) 절의 인덱스 사용

SQL 문장이 WHERE 절과 ORDER BY 절을 가지고 있다고 가정했을 때 WHERE 조건은 A 인덱스를 사용하고 ORDER BY는 B 인덱스를 사용하도록 쿼리가 실행될 수는 없다. WHERE 절과 ORDER BY 절이 같이 사용된 하나의 쿼리 문장은 다음 3가지 중 한 가지 방법으로만 인덱스를 이용한다.

• WHERE 절과 ORDER BY 절이 동시에 같은 인덱스를 이용
  WHERE 절의 비교 조건에서 사용하는 컬럼과 ORDER BY 절의 정렬 대상 컬럼이 모두 하나의 인덱스에서 연속해서 포함되어 있을 때 이 방식으로 인덱스를 처리하는데 아래 두 방법보다 훨씬 빠른 성능을 가진다.
• WHERE 절만 인덱스를 이용
  ORDER BY 절은 인덱스를 이용한 정렬이 불가능하며, 인덱스를 통해 검색된 결과 레코드를 별도의 정렬 처리 과정(Using Filesort)를 거쳐 정렬을 수행한다. 주로 이 방법은 WHERE 절의 조건에 일치하는 레코드의 건수가 많지 않을 때 효율적인 방식이다.
• ORDER BY 절만 인덱스를 이용
  이 방식은 ORDER BY 절의 순서대로 인덱스를 읽으면서 레코드 한 건씩 WHERE 절의 조건에 일치하는지 비교하고, 일치하지 않을 때는 버리는 형태로 처리한다. 주로 아주 많은 레코드를 조회해서 정렬해야 할 때는 이런 형태로 튜닝하기도 한다.

 

SELECT *
FROM tb_test
WHERE COL_1 = 10
ORDER BY COL_2, COL_3;

위 쿼리는 인덱스를 사용하지 않을 것처럼 보이지만 ORDER BY를 COL_2, COL_3에서 COL_1, COL_2, COL_3처럼 COL_1 컬럼을 추가한다고 하더라도 정렬 순서에 변화가 없기 떄문에 추가한 쿼리나 안한 쿼리 모두 같다고 할 수 있다. 하지만 이렇게 변경된 쿼리에서는 WHERE 절과 ORDER BY 절이 동시에 인덱스를 이용할 수 있는지를 더 쉽게 판단할 수 있다.

 

SELECT * FROM tb_test WHERE COL_1 > 10 ORDER BY COL_1, COL_2, COL_3;
SELECT * FROM tb_test WHERE COL_1 > 10 ORDER BY COL_2, COL_3;

위 쿼리에서 첫 번째 쿼리는 COL_1부터 COL_3까지 순서대로 모두 명시됐기 때문에 인덱스를 사용해 WHERE 조건절과 ORDER BY 절을 처리할 수 있지만 두 번째 쿼리는 ORDER BY 절에는 COL_1이 명시되지 않았기 때문에 정렬할 때는 인덱스를 이용할 수 없다.

 

GROUP BY 절과 ORDER BY 절의 인덱스 사용

GROUP BY와 ORDER BY 절이 동시에 사용된 쿼리에서 두 절이 모두 하나의 인덱스를 사용해서 처리되려면 GROUP By 절에 명시된 컬럼과 ORDER BY에 명시된 컬럼의 순서와 내용이 모두 같아야 한다. GROUP BY와 ORDER BY가 같이 사용된 쿼리에서 둘 중 하나라도 인덱스를 이용할 수 없다면 둘 다 인덱스를 사용할 수 없다.

 

WHERE 절의 비교 조건 사용 시 주의사항

WHERE 절에 사용되는 비교 조건의 표현식은 상당히 중요하다.

 

NULL 비교

다른 DBMS와는 조금 다르게 MySQL에서는 NULL 값이 포함된 레코드도 인덱스로 관리된다. 이는 인덱스에서는 NULL을 하나의 값으로 인정해서 관리한다는 것을 의미하는데 어느 한 쪽이라도 NULL이면 그 결과로 NULL이 반환된다. 따라서 NULL을 비교하려면 IS NULL(또는 <=>) 연산자를 사용해야 한다. 그 밖의 방법으로는 컬럼의 값이 NULL인지 알 수 있는 방법이 없다.

 

위 쿼리는 to_date 컬럼이 NULL인 레코드를 조회하는 쿼리지만 to_date 컬럼에 생성된 ix_todate 인덱스를 ref 방식으로 적절히 이용하고 있음을 알 수 있다.

 

그리고 4개의 쿼리 중 첫 번째와 두 번째 쿼리는 titles 테이블의 ix_todate 인덱스를 레인지 스캔으로 사용할 수 있다. 하지만 세 번째 쿼리와 네 번째 쿼리는 인덱스나 테이플을 풀 스캔하는 형태로 처리되기 때문에 NULL을 비교할 때는 가급적 IS NULL 연산자를 사용하는 것을 권장한다.

 

문자열이나 숫자 비교

SELECT * FROM employees WHERE emp_no = 10001;
SELECT * FROM employees WHERE emp_no = '10001';
SELECT * FROM employees WHERE first_name = 'Smith';
SELECT * FROM employees WHERE first_name = 10001;

문자열 컬럼이나 숫자 컬럼을 비교할 때는 반드시 그 타입에 맞는 상숫값을 사용할 것을 권장한다.

• 첫 번째 쿼리와 세 번째 쿼리는 컬럼의 타입과 비교하는 상숫값이 동일한 타입으로 사용됐기 때문에 인덱스를 적절히 이용할 수 있다.
• 두 번째 쿼리는 emp_no 컬럼이 숫자 타입이기 때문에 문자열 상숫값을 숫자로 타입 변환해서 비교를 수행하므로 특별히 성능 저하는 발생하지 않는다.
• 네 번째 쿼리는 first_name이 문자열 컬럼이지만 비교되는 상숫값이 숫자 타입이므로 옵티마이저는 우선순위를 가지는 숫자 타입으로 비교를 수행하려고 실행 계획을 수립한다. 그래서 first_name 컬럼의 문자열을 숫자로 변환해서 비교를 수행한다. 하지만 first_name 컬럼의 타입 변환이 필요하기 때문에 ix_firstname 인덱스를 전혀 사용하지 못한다.

 

날짜 비교

MySQL에서는 날짜만 저장하는 DATE 타입과 날짜와 시간을 함께 저장하는 DATETIME과 TIMESTAMP 타입이 있으며, 시간만 저장하는 TIME이라는 타입도 있기 때문에 상당히 복잡하게 느껴질 수 있다.

 

DATE 또는 DATETIME과 문자열 비교

SELECT COUNT(*)
FROM employees
WHERE hire_date > STR_TO_DATE('2011-07-23', '%Y-%m-%d');

SELECT COUNT(*)
FROM employees
WEHRE hire_date > '2011-07-23';

DATE 또는 DATETIME 타입의 값과 문자열을 비교할 때는 문자열 값을 MySQL이 내부적으로 DATETIME 타입의 값으로 변환해서 비교를 수행한다. 따라서 위 두 쿼리 모두 인덱스를 효율적으로 이용할 수 있기 때문에 성능과 관련된 문제는 고민하지 않아도 된다.

 

SELECT COUNT(*)
FROM employees
WHERE DATE_FORMAT(hire_date, '%Y-%m-%d') > '2011-07-23';

하지만 위 쿼리는 hire_date 타입을 강제적으로 문자열로 변경하기 때문에 인덱스를 효율적으로 사용하지 못한다. 가능하면 DATE나 DATETIME 타입의 컬럼을 변경하지 말고 상수를 변경하는 형태로 조건을 사용하는 것이 좋다.

 

SELECT COUNT(*)
FROM employees
WHERE DATE_ADD(hire_date, INTERVAL 1 YEAR) > '2011-07-23';

날짜 타입의 포맷을 변환하는 형태를 포함해서 날짜 타입 컬럼의 값을 더하거나 빼는 함수로 변형한 후 비교해도 마찬가지로 인덱스를 이용할 수 없다.

 

DATETIME과 TIMESTAMP의 비교

DATE나 DATETIME 타입의 값과 TIMESTAMP의 값을 별도의 타입 변환 없이 비교하면 문제없이 작동하고 실제 실행 계획도 인덱스 레인지 스캔을 사용해서 동작하는 것처럼 보이지만 사실은 그렇지 않다.

UNIX_TIMESTAMP() 함수의 결괏값은 MySQL 내부적으로 단순 숫자 값에 불과할 뿐이므로 두 번째 쿼리와 같이 비교해서는 원하는 결과를 얻을 수 없다. 이때는 반드시 비교 값으로 사용되는 상수 리터럴을 비교 대상 컬럼의 타입에 맞게 변환해서 사용하는 것이 좋다. 

 

SELECT COUNT(*) FROM employees WHERE hire_date < FROM_UNIXTIME(UNIX_TIMESTAMP());
SELECT COUNT(*) FROM employees WHERE hire_date < NOW();

컬럼이 DATETIME 타입이라면 FROM_UNIXTIME 함수를 이용해 TIMESTAMP 값을 DATETIME 타입으로 만들어서 비교하고, 컬럼이 TIMESTAMP 타입이라면 UNIX_TIMESTAMP 함수를 이용해 DATETIME을 TIMESTAMP로 변환해서 비교해야 한다. 또는 간단하게 NOW() 함수를 이용해도 된다.

 

Short-Circuit Evaluation

boolean in_transaction;

if (in_transaction && has_modified()) {
	commit();
}

만약 in_transaction이 false라면 has_modified() 메서드가 호출되지도 않고 다음 코드를 실행하는데 이렇게 여러 개의 표현식이 AND 또는 OR 논리 연산자로 연결된 경우 선행 표현식의 결과에 따라 후행 표현식을 평가할지 말지 결정하는 최적화를 "Short-circuit Evaluation"이라고 한다.

 

위 예제의 쿼리에서 사용된 두 개의 조건은 모두 인덱스를 사용하지 못하기 때문에 쿼리는 풀 테이블 스캔을 하게 된다. 그리고 1번과 2번 조건을 AND로 연결한 3번째 쿼리의 결과는 0건이 될 것이다. 그리고 1번과 2번 조합을 순서를 바꿔가면서 성능을 확인해 보면 대략 0.5초 차이가 나는 것을 확인할 수 있고, 1번과 2번 조합이 더 많은 CPU를 사용하거나 더 많은 자원을 소모하는 조건이었다면 두 쿼리의 시간 차이는 훨씬 더 컸을 것이다.

이러한 차이가 나는 이유는 위에서 살펴봤듯이 1번과 2번 결합 조건은 CONVERT_TZ() 함수가 2844047번 실행되고, to_date 컬럼 비교 작업이 2442943번 실행되어야 하는 반면, 2번과 1번 결합 조건은 to_date 컬럼 비교 작업 시 만족하는 레코드가 한 건도 없기 떄문에 to_date 비교 작업만 28844047번 실행하면 되고, CONVERT_TZ() 함수는 한 번도 호출되지 않는다.

 

MySQL 서버는 쿼리의 WHERE 절에 나열된 조건을 순서대로 "Short-circuit Evaluation" 방식으로 평가해서 해당 레코드를 반환해야 할지 말지를 결정하는데 WHERE 조건 중에서 인덱스를 사용할 수 있는 조건이 있다면 "Short-circuit Evaluation" 방식과는 무관하게 그 조건을 가장 최우선으로 사용한다. 그래서 WHERE 조건절에 나열된 조건의 순서가 인덱스의 사용 여부를 결정하지는 않는다.

 

SELECT *
FROM employees e
WHERE e.first_name = 'Matt'
  AND EXISTS (SELECT 1 FROM salaries s
  			  WHERE s.emp_no = e.emp_no AND s.to_date > '1995-01-01'
              GROUP BY s.salary HAVING COUNT(*) > 1)
  AND e.last_name = 'Aamodt';

위 예제 쿼리에서 first_name 컬럼의 조건은 인덱스를 사용할 수 있으므로 MySQL 서버 옵티마이저는 최우선으로 ix_firstname (first_name) 인덱스를 사용해 필요한 데이터의 범위를 최소화할 것이다. 그리고 그 결과에서 last_name = 'Aamodt' 조건과 서브쿼리 조건을 만족하는 레코드만 걸러서 결과를 반환한다. 이때 서브쿼리 조건이 먼저 나열됐기 때문에 EXITST (subquery) 조건을 먼저 평가한다. last_name 조건은 이미 가져온 레코드에서 last_name 컬럼의 값이 'Aamodt'인지 단순 비교만 하면 되지만 MySQL 서버 옵티마이저는 WHERE 절에 EXISTS (subquery) 조건이 먼저 나열됐기 때문에 salaries 테이블을 검색하는 서브쿼리를 먼저 실행해서 판단한다.

 

SELECT *
FROM employees e
WHERE e.first_name = 'Matt'
  AND e.last_name = 'Aamodt'
  AND EXISTS (SELECT 1 FROM salaries s
  			  WHERE s.emp_no = e.emp_no AND s.to_date > '1995-01-01'
              GROUP BY s.salary HAVING COUNT(*) > 1);

따라서 last_name = 'Aamodt' 조건을 EXISTS (subquery) 조건보다 먼저 나열시키면 ix_firstname 인덱스를 통해 233건의 레코드를 가져온 다음 last_name = 'Aamodt' 조건을 만족하는지를 먼저 평가한다. 그리고 first_name = 'Matt'이면서 last_name = 'Aamodt'인 레코드 1건에 대해 EXISTS (subquery) 조건의 만족 여부를 평가할 것이다. 결과적으로 233건의 복잡한 EXISTS (subquery) 조건을 평가하면서 상당히 많은 레코드를 읽고 쓰는 작업을 하는 것 보다 서브 쿼리를 뒤쪽으로 배치해서 레코드 1건에 대해 EXIST (subquery)를 처리하도록 하는 것이 성능상 도움이 되는 것을 알 수 있다. 물론 WHERE 조건 중에서 인덱스를 사용할 수 있는 조건은 WHERE 절의 어느 위치에 나열되든디 그 순서에 관계없이 가장 먼저 평가되기 때문에 고려하지 않아도 된다.

 

DISTINCT

특정 컬럼의 유니크한 값을 조회하려면 SELECT 쿼리에 DISTINCT를 사용한다. 많은 사용자가 조인을 하는 경우 레코드가 중복해서 출력되는 것을 막기 위해 DISTINCT를 남용하는 경향이 있는데 성능적인 문제가 발생할 수도 있지만 결과가 의도한 바와 달라질 수 있기 때문에 주의해서 사용해야 한다.

 

LIMIT n

LIMIT 쿼리는 결과에서 지정된 순서에 위치한 레코드만 가져오고자 할 때 사용된다. 위의 쿼리는 다음과 같은 순서로 실행된다.

• employees 테이블에서 WHERE 절의 검색 조건에 일치하는 레코드를 전부 읽어온다.
• 1번에서 읽어온 레코드를 first_name 컬럼값에 따라 정렬한다.
• 정렬된 결과에서 상위 5건만 사용자에게 반환한다.

MySQL의 LIMIT은 위처럼 WHERE 조건이 아니기 때문에 항상 쿼리의 가장 마지막에 실행된다. LIMIT의 중요한 특성은 LIMIT에서 필요한 레코드 건수만 준비되면 즉시 쿼리를 종료한다는 것이다. 즉, 위의 쿼리에서 모든 레코드의 정렬이 완료되지 않았다고 하더라도 상위 5건까지만 정렬되면 작업을 멈춘다.

SELECT DISTINCT first_name FROM employees LIMIT 0, 10;

위 쿼리에서 사용한 DISTINCT는 정렬에 대한 요건이 없이 유니크한 그룹만 만들어 내면 된다. MySQL은 스토리지 엔진을 통해 풀 테이블 스캔 방식을 이용해 employee 테이블 레코드를 읽어 들임과 동시에 DISTINCT를 위한 중복 제거 작업(임시 테이블을 사용)을 진행한다. 이 작업을 반복적으로 처리하다 유니크한 레코드가 LIMIT 건수만큼 채워지면 그 순간 쿼리를 멈춘다. 이렇게 쿼리 문장에 GROUP BY나 ORDER BY 같은 전체 범위 작업이 선행되더라도 LIMIT 절이 있다면 크진 않지만 나름의 성능 향상은 있다고 볼 수 있다.

 

많은 어플리케이션에서 테이블의 데이터를 SELECT할 때 조금씩 잘라서(페이징) 가져가게 되는데 위처럼 LIMIT을 사용하는 경우가 많다. 하지만 특별한 경우 "LIMIT 2000000, 10"처럼 두 인자에 주어지는 수치가 매우 커질 때 위처럼 쿼리 실행에 상당히 오랜 시간이 걸리는 문제가 발생할 수 있다. "LIMIT 2000000, 10" 쿼리는 먼저 salaries 테이블을 처음부터 읽으면서 2000010건의 레코드를 읽은 후, 2000000건은 버리고 마지막 10건만 사용자에게 반환한다. 즉, 실제 사용자의 화면에는 10건만 표시되지만, MySQL 서버는 2000010건의 레코드를 읽어야 하기 때문에 쿼리가 느려지는 것이다.

 

LIMIT 조건의 페이징이 처음 몇 개 페이지 조회로 끝나지 않을 가능성이 높다면 위처럼 WHERE 조건절로 읽어야 할 위치를 찾고 그 위치에서 10개만 읽는 형태의 쿼리를 사용하는 것이 좋다. 위에서 "NOT (salary = 38864 AND emp_no <= 274049)" 조건은 이전 페이지에서 이미 조회했던 건을 제외하기 위해 추가한 조건이다. salaries 테이블의 salary 컬럼에 인덱스가 있는데, 이 인덱스는 중복이 허용되는 인덱스이기 때문에 단순히 이전 페이지의 마지막 salary(이전 페이지에서 가장 큰 salary 값)보다 큰 것을 조회하거나 크거나 같은 경우를 조회하면 중복이나 누락이 발생할 수 있다.

 

COUNT()

COUNT() 함수는 결과 레코드의 건수를 반환하는 함수로, 일반적으로 COUNT(*) 형태로 사용하는데 "*"를 사용한다고 해서 실제로 레코드의 모든 컬럼을 읽는 형태로 처지하지 않는다. 즉, COUNT(1)이나 COUNT(*)나 동일한 처리 성능을 보인다. InnoDB 스토리지 엔진을 사용하는 테이블에서는 WHERE 조건이 없는 COUNT(*) 쿼리라고 하더라도 직접 데이터나 인덱스를 읽어야만 레코드 건수를 가져올 수 있기 때문에 큰 테이블에서 COUNT() 함수를 사용하는 작업은 주의해야 한다.

 

JOIN

 

JOIN의 순서와 인덱스

인덱스 레인지 스캔은 인덱스를 탐색(Index Seek)하는 단계와 인덱스를 스캔(Index Scan)하는 과정으로 구분해 볼 수 있다. 일반적으로 인덱스를 이용해서 쿼리하는 작업에서는 가져오는 레코드의 건수가 소량이기 때문에 인덱스 스캔 작업은 부하가 작지만 특정 인덱스 키를 찾는 인덱스 탐색 과정을 상대적으로 부하가 높은 편이다.

조인 작업에서는 드라이빙 테이블을 읽을 때는 인덱스 탐색 작업을 단 한 번만 수행하고, 그 이후부터는 스캔만 하면 된다. 하지만 드리븐 테이블에서는 인덱스 탐색 작업과 스캔 작업을 드라이빙 테이블에서 읽은 레코드 건수만큼 반복한다. 둘이 1:1로 조인되더라도 드리븐 테이블을 읽는 것이 훨씬 더 큰 부하를 차지한다. 그래서 옵티마이저는 항상 드라이빙 테이블이 아니라 드리븐 테이블을 최적으로 읽을 수 있게 실행 계획을 수립한다.

 

위 두 테이블의 조인 쿼리에서 employees 테이블의 emp_no 컬럼과 dept_emp 테이블의 emp_no 컬럼에 각각 인덱스가 있을 때와 없을 때 조인 순서가 어떻게 달라지는지 살펴보자.

• 두 컬럼 모두 각각 인덱스가 있는 경우: 어느 테이블을 선택하든 인덱스를 이용해 드리븐 테이블의 검색 작업을 빠르게 처리할 수 있다. 이럴 때 옵티마이저가 통계 정보를 이용해 적절히 드라이빙 테이블을 선택하게 된다. 보통의 경우 어느 쪽 테이블이 드라이빙 테이블이 되든 옵티마이저가 선택하는 방법이 최적일 때가 많다.
• employees.emp_no에만 인덱스가 있는 경우: employees.emp_no에만 인덱스가 있을 때 dept_emp 테이블을 드리븐 테이블로 선택하면 employees 테이블의 레코드 건수만큼 dept_emp 테이블을 풀 테이블 스캔해야 한다. 그래서 옵티마이저는 항상 dept_emp 테이블을 드라이빙 테이블로, employees 테이블을 드리븐 테이블로 선택해서 "emp_no=100001"과 같이 employees 테이블을 아주 효율적으로 접근할 수 있도록 드라이빙 테이블로 선택하지 않을 가능성이 높다.
• dept_emp.emp_no에만 인덱스가 있는 경우: 위 경우와 반대로 처리된다고 생각하면 된다.
• 두 칼럼 모두 인덱스가 없는 경우: 두 컬럼 모두 각각 인덱스가 있는 경우와 마찬가지로 어느 테이블을 드라이빙 테이블로 선택하든 드리븐 테이블의 풀 테이블 스캔이 발생하기 때문에 옵티마이저가 레코드 건수가 적은 테이블을 드라이빙 테이블로 선택할 가능성이 높다. MySQL 8.0.18 버전 이전까지는 블록 네스티드 루프 조인을 사용했지만, 이후부터는 블록 네스티드 루프 조인이 없어지고, 해시 조인이 도입되면서 해시 조인으로 처리도니다.

 

JSON 컬럼의 데이터 타입

조인 컬럼간의 비교에서도 WHERE 절에 사용되는 조건처럼 각 컬럼의 데이터 타입이 일치하지 않으면 인덱스를 효율적으로 이용할 수 없다. 위처럼 두 컬럼의 타입이 일치하지 않기 때문에 두 테이블을 모두 풀 테이블 스캔으로 접근하고, Extra 컬럼에 "Using join buffer (hash join)"가 표시된 것으로 봐서 조인 버퍼를 이요해서 해시 조인이 실행된 것을 알 수 있다.

인덱스 사용에 영향을 미치는 데이터 타입 불일치는 CHAR 타입과 VARCHAR 타입, 또는 INT 타입과 BIGINT 타입, 그리고 DATE 타입과 DATETIME 타입 사이에서는 발생하지 않지만 다음과 같은 비교 패턴은 문제가 될 가능성이 높다.

• CHAR 타입과 INT 타입의 비교와 같이 데이터 타입의 종류가 완전히 다른 경우
• 같은 CHAR 타입이더라도 문자 집합이나 콜레이션이 다른 경우
• 같은 INT 타입이더라도 부호(Sign)의 존재 여부가 다른 경우

 

OUTER JOIN의 성능과 주의사항

이너 조인(INNER JOIN)은 조인 대상 테이블에 모두 존재하는 레코드만 결과 집합으로 반환한다. 따라서 위처럼 아우터 조인(OUTER JOIN)으로만 조인을 실행하는 쿼리들도 자주 보인다. 실행 계획을 보면 제일 먼저 employees 테이블을 풀 스캔하면서 dept_emp 테이블과 departments 테이블을 드리븐 테이블로 사용하는데 MySQL 옵티마이저는 절대 아우터로 조인되는 테이블을 드라이빙 테이블로 선택하지 못하기 때문이다.

 

employees 테이블에 존재하는 사원 중에서 dept_emp 테이블에 레코드를 갖지 않는 경우가 있다면 아우터 조인이 필요하지만, 대부분 그런 경우는 없으므로 굳이 아우터 조인을 사용할 필요가 없고 이너 조인을 이용한다면 departmenets 테이블에서 부서명이 "Development" 인 레코드 1건만 찾아서 조인을 실행하는 실행 계획을 선택할 것이다.

 

그리고 OUTER JOIN에서 많이 하는 실수로 아우터로 조인되는 테이블에 대한 조건을 WHERE 조건에 함께 명시하는 것이다. 위 쿼리처럼 dept_no = 'd001' 조건을 WHERE 절에 명시한 것은 잘못된 조인 방법으로 MySQL 옵티마이저는 LEFT JOIN을 INNER JOIN으로 변환해서 실행해버린다. 따라서 그 다음 쿼리처럼 ON 절로 옮겨야 한다.

 

JOIN과 외래키(FOREIGN KEY)

외래키를 생성하는 주목적은 데이터의 무결성을 보장하기 위해서다. 외래키와 연관된 무결성을 참조 무결성이라고 표현한다. 하지만 테이블 간의 조인을 사용하기 위해 외래키는 꼭 필요한 것이 아니며 데이터 모델을 데이터베이스에 생성할 떄는 그 테이블 간의 관계는 외래키로 생성하지 않을 때가 더 많다. 

 

지연된 조인

지연된 조인이란 조인이 실행되기 이전에 GROUP BY나 ORDER BY를 처리하는 방식을 의미한다. 지연된 조인은 주로 LIMIT이 함께 사용된 쿼리에서 더 큰 효과를 얻을 수 있다. 인덱스를 사용하지 못하는 GROUP BY와 ORDER BY 쿼리를 지연된 조인으로 처리하는 방법을 한 번 살펴보자.

 

위 쿼리의 실행 계획을 보면 employees 테이블을 드라이빙 테이블로 선택해서 "emp_no BETWEEN 10001 AND 13000" 조건을 만족하는 레코드 3000건을 읽고, salaries 테이블을 조인했다. 그리고 조인의 결과를 임시 테이블에 저장하고 GROUP BY 처리를 통해 다시 3000건으로 줄였다. 그리고 ORDER BY를 처리해서 상위 10건만 최종적으로 반환한다.

 

위 쿼리는 salaries 테이블에서 가능한 모든 처리(WHERE 조건 및 GROUP BY와 ORDER BY, LIMIT까지)를 수행한 다음, 그 결과를 임시 테이블에 저장했다. 그리고 임시 테이블의 결과를 employees 테이블과 조인하도록 고친 것이다. 즉, 모든 처리를 salaries 테이블에서 수행하고, 최종 10건만 employees 테이블과 조인했다.

 

위에서는 FROM 절의 서브쿼리를 위해 전체 56,844건의 레코드를 읽어야 한다고 나왔지만 COUNT(*)로 확인해보니 실제로는 28,606건의 레코드만 읽으면 되는 쿼리다. 지연된 조인으로 변경된 이 쿼리는 salaries 테이블에 28,606건의 레코드를 읽어 임시 테이블에 저장하고, GROUP BY 처리를 통해 3,000건으로 줄였다. 그리고 ORDER BY를 처리해 상위 10건만 임시 테이블(<derived2>)에 저장하고 최종적으로 임시 테이블의 10건을 읽어서 employees 테이블과 조인을 10번 수행해서 결과를 반환한다. 임시 테이블 때문에 더 느리다고 생각할 수 있지만 저장할 레코드가 10건밖에 되지 않으므로 메모리를 이용해 쿼리의 조인 횟수가 훨씬 적고 빠르게 처리할 수 있다. 실제 테스트해봐도 지연된 조인을 사용한 쿼리가 더 빠르게 조회한 것을 확인할 수 있다.

이러한 지연된 조인은 OUTER JOIn과 INNER JOIN에 대해 다음과 같은 조건이 갖춰져야만 지연된 쿼리로 변경해서 사용할 수 있다.

• LEFT (OUTER) JOIN 경우 드라이빙 테이블과 드리븐 테이블은 1:1 또는 M:1 관계여야 한다.
• INNER JOIN인 경우 드라이빙 테이블과 드리븐 테이블은 1:1 또는 M:1의 관계임과 동시에 드라이빙 테이블에 있는 레코드는 드리븐 테이블에 모두 존재해야 한다.

 

래터럴 조인(Lateral Join)

LATERAL 키워드를 사용한 경우

LATERAL 키워드를 사용하지 않는 경우

MySQL 8.0 버전부터는 래터럴 조인이라는 기능을 이용해 특정 그룹별로 서브쿼리를 실행해서 그 결과와 조인하는 것이 가능해졌다. 위 쿼리는 employees 테이블에서 이름이 'Matt'인 사원에 대해 사원별로 가장 최근 급여 변경 내역을 최대 2건씩만 반환한다. 래터럴 조인에서 가장 중요한 부분은 FROM 절에 사용된 서브쿼리(Derived Table)에서 외부 쿼리의 FROM 절에 정의된 테이블의 컬럼을 참조할 수 있다는 것이다. 위 쿼리는 salaries 테이블을 읽는 서브쿼리에서 employees 테이블의 emp_no를 참조한다. 이렇게 FROM 절에 사용된 서브쿼리가 외부 쿼리의 컬럼을 참조하기 위해서는 LATERAL 키워드가 명시되어야 한다.

LATERAL 키워드 없이 외부 쿼리의 컬럼을 참조하면 위처럼 에러가 발생하고 LATERAL 키워드를 가진 서브쿼리는 조인 순서상 후순위로 밀려 외부 쿼리의 결과 레코드 단위로 임시 테이블이 생성되는 방식이다.

 

실행 계획으로 인한 정렬 흐트러짐

쿼리의 실행 계획에서 네스티드 루프 조인 대신 해시 조인이 사용되거나 블록 네스티드 루프 조인을 사용하더라도 쿼리 결과의 레코드 정렬 순서가 드라이빙 테이블을 읽는 순서와 달라진다. 위 쿼리는 해시 조인이 사용된 것을 확인할 수 있고, 그 결과로 emp_no 컬럼으로 정렬되어 있지 않고, emp_no가 반복적으로 순환되는 결과가 만들어진 것을 확인할 수 있다.

 

GROUP BY

GROUP BY는 특정 컬럼의 값으로 레코드를 그루핑하고, 그룹별로 집계된 결과를 하나의 레코드로 조회할 때 사용한다.

 

WITH ROLLUP

GROUP BY가 사용된 쿼리에서 그루핑된 그룹별로 소계를 가져올 수 롤업(ROLLUP) 기능을 사용할 수 있다. ROLLUP으로 출력되는 소계는 단순히 최종 합만 가져오는 것이 아니라 GROUP BY에 사용된 컬럼의 개수에 따라 소계의 레벨이 달라진다.

위 쿼리를 보면 GROUP BY 컬럼이 2개가 있기 때문에 first_name 그룹별로 소계 레코드가 출력되고, 마지막의 총계는 first_name과 last_name 컬럼이 모두 NULL로 채워진 총합 레코드가 출력되는 것을 확인할 수 있다. 그리고 GROUPING() 함수를 사용해서 NULL을 사용자가 변경할 수 있게 지원한다.

 

레코드를 컬럼으로 변환

 

dept_emp 테이블을 이용해 부서별로 사원의 수를 확인하는 쿼리를 작성했다. 하지만 레포팅 도구나 OLAP 같은 도구에서는 자주 이러한 결과를 반대로 만들어야 할 수도 있다. 즉, 레코드를 컬럼으로 변환해야 하는 경우가 있을 수 있다. 이때는 GROUP BY 쿼리 결과를 SUM(CASE WHEN ...) 구문을 사용해 한 번 더 변환하면 된다.

 

하나의 컬럼을 여러 컬럼으로 분리

위에서 단순히 부서별로 전체 사원의 수만 조회하는 쿼리를 이번에는 소그룹을 특정 조건으로 나눠서 사원의 수를 구하는 용도로도 사용할 수 있다.

 

ORDER BY

ORDER BY는 검색된 레코드를 어떤 순서로 정렬할지 결정한다. ORDER BY 절이 사용되지 않으면 SELECT 쿼리의 결과는 어떤 순서로 정렬될까?

• 인덱스를 사용한 SELECT의 경우에는 인덱스에 정렬된 순서대로 레코드를 가져온다.
• 인덱스를 사용하지 못하고 풀 테이블 스캔을 SELECT 하는 경우에 InnoDB 테이블의 경우 항상 프라이머리 키로 클러스터링되어 있기 때문에 기본적으로 프라이머리 키 순서대로 레코드를 가져온다.
• SELECT 쿼리가 임시 테이블을 거쳐 처리되면 조회되는 레코드의 순서를 예측하기 어렵다.

ORDER BY 절이 없는 SELECT 쿼리 결과의 순서는 처리 절차에 따라 달라질 수 있고, 어떤 DBMS도 ORDER BY 절이 명시되지 않은 쿼리에 대해서는 어떠한 정렬도 보장하지 않는다. 따라서 정렬이 필요한 곳에서는 ORDER BY 절을 사용해야 하고, ORDER BY에서 인덱스를 사용하지 못할 때는 추가 정렬 작업을 수행되며 Extra 컬럼에 "Using filesort"가 표시되는 것을 확인할 수 있다.

 

Sort_merge_passes 상태 값은 메모리의 버퍼(sort_buffer_size 시스템 변수로 설정되는 메모리 공간)와 디스크에 저장된 레코드를 몇 번이나 병합했는지를 보여준다. 이 상태 값이 0보다 크다면 이는 정렬해야 할 데이터가 정렬용 버퍼보다 커서 디스크를 이용했다는 것을 의미한다. Sort_range와 Sort_scan은 인덱스 레인지 스캔을 통해서 읽은 레코드를 정렬한 횟수와 풀 테이블 스캔을 통해서 읽은 레코드를 정렬한 횟수를 누적한 값이다. Sort_rows는 정렬을 수행했던 전체 레코드 건수의 누적된 값을 나타낸ㄴ다.

 

서브쿼리

쿼리를 작성할 때 서브쿼리를 사용하면 단위 처리별로 쿼리를 독립적으로 작성할 수 있다. 조인처럼 여러 테이블을 섞어 두는 형태가 아니어서 쿼리의 가독성도 높아지며, 복잡한 쿼리도 손쉽게 작성할 수 있다. 서브쿼리는 여러 위치에서 사용될 수 있는데, 대표적으로 SELECT 절과 FROM 절, WHERE 절에 사용될 수 있다. 하지만 사용되는 위치에 따라 쿼리의 성능 영향도와 MySQL 서버의 최적화 방법은 완전히 달라진다.

 

SELECT 절에 사용된 서브쿼리

일반적으로 SELECT 절에 서브쿼리를 사용하면 그 서브쿼리는 항상 컬럼과 레코드가 하나인 결과를 반환해야 한다. SELECT 절에서 사용된 서브쿼리의 주의할 점을 다음과 같다.

• 위 쿼리처럼 결과가 0건인 경우에는 에러가 발생하지 않고, 서브쿼리의 결과는 NULL로 채워져서 반환된다.
• 서브쿼리가 2건 이상의 레코드를 반환하는 경우에는 에러가 나면서 쿼리가 종료된다.
• SELECT 절에 사용된 서브쿼리가 2개 이상의 컬럼을 가져오려고 할 때도 에러가 발생한다.

 

위 두 예제는 SELECT 절에 서브쿼리를 사용하는 경우와 래터럴 조인을 사용한 경우이다. 서브쿼리에 LIMIT 1 조건 때문에 salaries 테이블을 조인으로 사용할 수 없었지만 MySQL 8.0 버전부터 도입된 래터럴 조인을 이용하면 동일한 레코드의 각 컬럼을 가져오기 위해서 서브쿼리를 3번씩 남용하지 않아도 된다. 3번의 서브쿼리를 하나의 래터럴 조인으로 변경했기 때문에 salaries 테이블을 한 번만 읽어서 쿼리를 처리할 수 있다.

 

FROM 절에 사용된 서브쿼리

이전 버전의 MySQL 서버에서는 FROM 절에 서브쿼리가 사용되면 항상 서브쿼리의 결과를 임시 테이블로 저장하고 필요할 때 다시 임시 테이블을 읽는 방식으로 처리해서 가능하면 FROM 절의 서브쿼리를 외부 쿼리로 병합하는 형태로 쿼리 튠닝을 했다. 지만 MySQL 5.7 버전부터는 옵티마이저가 FROM 절의 서브쿼리를 외부 쿼리로 병합하는 최적화를 수행하도록 개선됐다. FROM 절에 사용된 서브쿼리를 어떻게 병합했는지는 EXPLAIN 명령을 실행한 후 SHOW WARNINGS 명령을 실행하면 MySQL 서버가 재작성한 쿼리의 내용을 확인할 수 있다.

FROM 절의 모든 서브쿼리를 외부 쿼리로 병합할 수 있는 것응 아니고 대표적으로 다음과 같은 기능이 서브쿼리에 사용되면 FROM 절의 서브쿼리는 외부 쿼리로 병합되지 못한다.

• 집합 함수 사용(SUM(), MIN(), MAX(), COUNT() 등)
• DISTINCT
• GROUP BY 또는 HAVING
• LIMIT
• UNION(UNION DISTINCT) 또는 UNION ALL
• SELECT 절에 서브쿼리가 사용된 경우
• 사용자 변수 사용(사용자 변수에 값이 할당되는 경우)

 

WHERE 절에 사용된 서브쿼리

WHERE 절의 서브쿼리는 SELECT 절이나 FROM 절보다는 다양한 형태(연산자)로 사용될 수 있는데, 크게 다음 3가지로 구분해서 살펴볼 수 있다.

• 동등 또는 크다 작다 비교(= (subquery))
• IN 비교(IN (subquery))
• NOT IN 비교(NOT IN (subquery))

 

동등 또는 크다 작다 비교

MySQL 5.5 버전부터는 쿼리의 실행 계획은 서브쿼리를 먼저 실행한 후 상수로 변환하여 상숫값으로 서브쿼리를 대체해서 나머지 쿼리 부분을 처리한다.

위 쿼리의 실행 계획을 보면 먼저 제일 하단의 제일 안 쪽 "Index lookup on e using ix_lastname_firstname" 라인을 확인할 수 있다. 즉, employees 테이블의 ix_lastname_firstname 인덱스로 서브쿼리를 처리한 후, 그 결과를 이용해 dept_emp 테이블의 ix_empno_fromdate 인덱스를 검색해 쿼리가 완료된다는 것을 확인할 수 있다.

 

단일 값 비교가 아닌 튜플 비교 방식이 사용되면 서브쿼리가 먼저 처리되어 상수화되긴 하지만 외부 쿼리는 인덱스를 사용하지 못하고 풀 테이블 스캔을 실행하게 된다. 따라서 MySQL 8.0 버전이라고 하더라도 튜플 형태의 비교는 아직 주의해서 사용해야 한다.

 

IN 비교(IN (subquery))

실제 조인은 아니지만 위 예제처럼 테이블의 레코드가 다른 테이블의 레코드를 이용한 표현식과 일치하는지를 체크하는 형태를 세미 조인(Semi-Join)이라고 한다. 즉, WHERE 절에 사용된 IN (subquery) 형태의 조건을 조인의 한 방식인 세미 조인이라고 보는 것이다.

MySQL 서버의 세미 조인 최적화는 쿼리 특성이나 조인 관계에 맞게 다음과 같이 5개의 최적화 전략을 선택적으로 사용한다. MySQL 8.0을 사용한다면 세미 조인 최적화에 익숙해져 불필요하게 쿼리를 여러 조각으로 분리해서 실행하는 습관을 버리는 것이 좋다.

• 테이블 풀-아웃(Table Pull-out)
• 퍼스트 매치(Firstmatch)
• 루스 스캔(Loosescan)
• 구체화(Materialization)
• 중복 제거(Duplicated Weed-out)

 

NOT IN 비교(NOT IN (subquery))

IN (subquery)와 비슷한 형태지만 이 경우를 안티 세미 조인(Anti Semi-Join)이라고 명명한다. 일반적으로 RDBMS에서 Not-Equal 비교(<> 연산자)는 인덱스를 제대로 활용할 수 없듯이 안티 세미 조인 또한 최적화할 수 있는 방법이 많지 않다. MySQL 옵티마이저는 안티 세미 조인 쿼리가 사용되면 다음 두 가지 방법으로 최적화를 수행하는데 성능 향상에 도움이 되지 않는 방법이므로 쿼리가 최대한 다른 조건을 활용해서 데이터 검색 범위를 좁힐 수 있게 하는 것이 좋다.

• NOT EXISTS
• 구체화(Materialization)

 

CTE(Common Table Expression)

CTE는 이름을 가지는 임시 테이블로서, SQL 문장 내에서 한 번 이상 사용될 수 있으며 SQL 문장이 종료되면 자동으로 CTE 임시 테이블은 삭제된다. CTE는 재귀적 반복 실행여부를 기준으로 Non-recursive와 Recursive CTE로 구분된다.

 

비 재귀적 CTE(Non-Recursive CTE)

CTE를 이용한 쿼리의 실행 계획

FROM 절의 서브쿼리를 이용한 경우

MySQL 서버에서는 ANSI 표준을 그대로 이용해서 WITH 절을 이용해 CTE를 정의한다. CTE를 이용한 쿼리에서는 salaries 테이블을 이용한 cte1 임시 테이블을 한 번만 생성하지만 FROM 절의 서브쿼리를 이용한 쿼리에서는 2개의 임시 테이블을 생성하기 위해서 각 서브쿼리에서 salaries 테이블을 읽었다는 것을 알 수 있다.

CTE를 재귀적으로 사용하지 않더라도 기존 FROM 절에 사용되던 서브쿼리에 비해 다음의 3가지 장점이 있다.

• CTE 임시 테이블은 재사용 가능하므로 FROM 절의 서브쿼리보다 효율적이다.
• CTE로 선언된 임시 테이블을 다른 CTE 쿼리에서 참조할 수 있다.
• CTE는 임시 테이블의 생성 부분과 사용 부분의 코드를 분리할 수 있으므로 가독성이 높다.

 

재귀적 CTE(Recursive CTE)

MySQL 8.0 버전에서 CTE를 이용한 재귀 쿼리가 가능해졌다. 위 쿼리는 재귀적으로 사용되는 CTE를 활용한 예시이다. 재귀적 CTE 쿼리는 비 재귀적 쿼리 파트와 재귀적 파트로 구분되며, 이 둘을 UNION(UNION DISTINCT) 또는 UNION ALL로 연결하는 형태로 반드시 쿼리를 작성해야 한다. 위 쿼리에서 "SELECT 1"은 비 재귀적 파트이며, UNION ALL 아래의 "SELECT (no + 1) FROM cte WHERE no < 5"는 재귀적 파트다. 비 재귀적 파트는 처음 한 번만 실행되지만 재귀적 파틑 쿼리 결과가 없을 때까지 반복실행한다. 작동하는 방법은 다음과 같다.

1. CTE 쿼리의 비 재귀적 파트의 쿼리 실행
2. 1번의 결과를 이용해 cte라는 이름의 임시 테이블 생성
3. 1번의 결과를 cte라는 임시 테이블에 저장
4. 1번 결과를 입력으로 사용해 CTE 쿼리의 재귀적 파트의 쿼리를 실행
5. 4번의 결과를 cte라는 임시 테이블에 저장(이때 UNION 또는 UNION DISTINCT의 경우 중복 제거를 실행)
6. 전 단계의 결과를 입력으로 사용해 CTE 쿼리의 재귀적 파트 쿼리를 실행
7. 6번 단계에서 쿼리 결과가 없으면 CTE 쿼리를 종료
8. 6번의 결과를 cte라는 임시 테이블에 저장
9. 6번으로 돌아가서 반복 실행

1번 과정에서 매주 중요한 부분이 결정되는데, 바로 CTE 임시 테이블의 구조가 그것이다. CTE 임시 테이블의 구조는 CTE 쿼리의 비 재귀적 쿼리 파트의 결과로 결정된다. 재귀적으로 실행되는 CTE에서 주의해야할 것은 반복 실행의 종료 조건이다. 이 예제에서는 재귀적 파트 쿼리의 WHERE 조건절에 "no < 5"라는 조건이 반복 종료 조건으로 사용됐다. 모든 재귀 쿼리가 이런 조건이 필요한 것이 아니라 재귀 파트 쿼리의 결과가 0건일 때까지이다!

쿼리 작성자의 실수로 재귀적 CTE가 종료 조건을 만족하지 못해서 무한 반복하는 경우도 발생할 수 있다. 이 같은 오류를 막기 위해서 MySQL 서버는 cte_max_resursion_depth 시스템 변수(기본값: 1000)를 이용해 최대 반복 실행 횟수를 제한할 수 있다.

 

윈도우 함수

윈도우 함수는 조회하는 현재 레코드를 기준으로 연관된 레코드 집합의 연산을 수행한다. 집계 함수는 주어진 그룹(GROUP BY 절에 나열된 컬럼의 값에 따른 그룹 또는 GROUP BY 절 없이 전체 그룹)별로 하나의 레코드로 묶어서 출력하지만 윈도우 함수는 조건에 일치하는 레코드 건수는 변하지 않고 그대로 유지한다. 이것이 윈도우 함수와 집계 함수의 가장 큰 차이점이라고 할 수 있다.

 

쿼리 각 절의 실행 순서

윈도우 함수를 사용하는 쿼리의 결과에 보여지는 레코드는 FROM 절과 WHERE 절, GROUP BY와 HAVING 절에 의해 결정되고, 그 이후 윈도우 함수가 실행된다. 그리고 마지막으로 SELECT 절과 ORDER BY 절, LIMIT 절이 실행되어 최종 결과가 반환된다. 윈도우 함수가 사용된 쿼리의 각 절이 처리되는 순서를 다음과 같다.

• 윈도우 함수 이전에 실행: WHERE, FROM, GROUP BY, HAVING
• 윈도우 함수 처리
• 윈도우 함수 이후에 실행: SELECT 절, ORDER BY, LIMIT

위 순서에서도 볼 수 있듯이 윈도우 함수는 GROUP BY 컬럼으로 사용하거나 WHERE 절에 사용할 수 없는 이유는 위 순서로 처리되기 떄문이다. 

 

서브쿼리를 사용하지 않은 윈도우 함수 쿼리

서브쿼리를 사용한 윈도우 함수 쿼리

위 두 쿼리에서 첫 번째 쿼리는 FROM 절의 서브쿼리 없이 "LIMIT 5"를 사용했다. 이는 우선 emp_no=10001 조건에 일치하는 17건의 레코드를 모두 가져온 다음 윈도우 함수(AVG(salary) OVER())를 실행하고, 그 결과에서 5건만 반환했기 때문에 avg_salary 컬럼의 값은 최종 5건의 평균이 아닌 17건의 평균인 것이다. 두 번째 쿼리는 서브 쿼리를 사용하여 5건에 대한 평균이라고 할 수 있다.

 

윈도우 함수 기본 사용법

AGGREGATE_FUNC() OVER(<partition> <order>) AS window_func_column

윈도우 함수는 용도별로 다양한 함수들을 사용할 수 있는데, 집계 함수와는 달리 함수 뒤에 OVER 절을 이용해 연산 대상을 파티션하기 위한 옵션을 명시할 수 있다. 이렇게 OVER 절에 의해 만들어진 그룹을 파티션(Partition) 또는 윈도우(Window)라고 한다.

 

예제 쿼리에서 "RANK() OVER(ORDER BY e.hire_date)"는 소그룹을 별도로 구분하지 않고 전체 결과 집합에서 e.hire_date 컬럼으로 정렬한 후 순위(RANK() 함수)를 매기게 했다.

 

위 쿼리는 부서별로 입사 순위를 매기고자 할 때 부서 코드를 파티션으로 사용하여 소그룹을 정한 쿼리이다. 만약 소그룹 파티션이나 정렬이  필요치 않은 경우 PARTITION이나 ORDER BY 없이 비어 있는 OVER() 절을 사용하면 된다.

 

AGGREGATE_FUNC() OVER(<partition> <order> <frame>) AS window_func_column

frame: {ROWS | RANGE} {frame_start | frame_between}

frame_between: BETWEEN frame_start AND frame_end

frame_start, frame_end: {
    CURRENT ROW
  | UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | expr PRECEDING
  | expr FOLLOWING
}

윈도우 함수의 각 파티션 안에서도 연산 대상 레코드별로 연산을 수행할 소그룹이 사용되는데, 이를 프레임이라고 한다. 윈도우 함수에서 프레임을 명시적으로 지정하지 않아도 MySQL 서버는 상황에 맞게 프레임을 묵시적으로 선택한다. 프레임은 레코드의 순서대로 현재 레코드 기준 앞뒤 몇 건을 연산 범위로 제한하는 역할을 한다.

프레임을 만드는 기준으로 ROWS와 RANGE 중 하나를 선택할 수 있다.

• ROWS: 레코드의 위치를 기준으로 프레임을 생성
• RANGE: ORDER BY 절에 명시된 컬럼을 기준으로 값의 범위로 프레임 생성

프레임의 시작과 끝을 의미하는 키워드들의 의미는 다음과 같다.

• CURRENT ROW: 현재 레코드
• UNBOUNDED PRECEDING: 파티션의 첫 번째 레코드
• UNBOUNDED FOLLOWING: 파티션의 마지막 레코드
• expr PRECEDING: 현재 레코드로부터 n번째 이전 레코드
• expr FOLLOWING: 현재 레코드로부터 n번째 이후 레코드

프레임이 ROWS로 구분되면 expr에는 레코드의 위치를 명시하고, RANGE로 구분하면 expr에는 컬럼과 비교할 값이 설정되어야 한다. 그래서 프레임의 시작과 끝이 expr을 가지는 경우는 다음처럼 사용될 수 있다.

• 10 PRECEDING: 현재 레코드로부터 10건 이전부터
• INTERNAVL 5 DAY PRECEDING: 현재 레코드의 ORDER BY 컬럼값보다 5일 이전 레코드부터
• 5 FOLLOWING: 현재 레코드로부터 5건 이후까지
• INTERVAL '2:30' MINUTE_SECOND FOLLOWING: 현재 레코드의 ORDER BY 컬럼값보다 2분 30초 이후까지

 

위 쿼리는 프레임 절이 있는 윈도우 함수를 사용한 쿼리이다.

 

일부 윈도우 함수들은 프레임이 미리 고정되어 있다. SQL 문장에서 프레임을 별도로 명시하더라도 이러한 윈도우 함수에서는 사용자가 정의한 프레임은 모두 무시된다. 이경우 에러는 발생하지 않기 떄문에 결과가 혼란스러울 수 있기 때문에 주의해야 한다. 다음 윈도우 함수들은 자동으로 프레임이 파티션의 전체 레코드로 설정된다.

• CUME_DIST()
• DENSE_RANK()
• LAG()
• LEAD()
• NTILE()
• PERCENT_RANK()
• RANK()
• ROW_NUMBER()

 

윈도우 함수

MySQL 서버의 윈도우 함수에는 집계 함수와 비 집계 함수를 모두 사용할 수 있다. 집계 함수는 GROUP BY 절과 함께 사용할 수 있는 함수들을 의미하는데, 집계 함수는 OVER() 절 없이 단독으로도 사용될 수 있고 OVER() 절을 가진 윈도우 함수로도 사용될 수 있다. 반면 비 집계 함수는 반드시 OVER() 절을 가지고 있어야 하며 윈도우 함수로만 사용될 수 있다.

 

집계 함수(Aggregate Function)	설명
AVG()	평균값 반환
BIT_AND()	AND 비트 연산 결과 반환
BIT_OR()	OR 비트 연산 결과 반환
BIT_XOR()	XOR 비트 연산 결과 반
COUNT()	건수 반환
JSON_ARRAYAGG()	결과를 JSON 배열로 반환
JSON_OBJECTAGG()	결과를 JSON OBJECT 배열로 반환
MAX()	최댓값 반환
MIN()	최솟값 반환
STDDEV_POP(), STDDEV(), STD()	표준 편차 값 반환
STDDEV_SAMP()	표본 표준 편차 값 반환
SUM()	합계 값 반환
VAR_POP(), VARIANCE()	표준 분산 값 반환
VAR_SAMP()	표본 분산 값 반환

 

비 집계 함수(Non-Aggregate Function)	설명
CUME_DIST()	누적 분포 값 반환 (파티션별 현재 레코드보다 작거나 같은 레코드의 누적 백분율)
DENSE_RANK()	랭킹 값 반환(Gap 없음) (동일한 값에 대해서는 동일 순위를 부여하며, 동일한 순위가 여러 건이어도 한 건으로 취급)
FIRST_VALUE()	파티션의 첫 번째 레코드 값 반환
LAG()	파티션 내에서 파라미터(N)를 이용해 N번째 이전 레코드 값 반환
LAST_VALUE()	파티션의 마지막 레코드 값 반환
LEAD()	파티션 내에서 파라미터(N)를 이용해 N번째 이후 레코드 값 반환
NTH_VALUE()	파티션의 n번째 값 반환
NTILE()	파티션별 전체 건수를 파라미터(N)로 N-등분한 값 반환
PERCENT_RANK()	퍼센트 랭킹 값 반환
RANK()	랭킹 값 반환(Gap 있음)
ROW_NUMBER()	파티션의 레코드 순번 반환

 

LAG()와 LEAD()

LAG() 함수는 파티션 내에서 현재 레코드를 기준으로 n번째 이전 레코드를 반환하며, LEAD() 함수는 반대로 n번째 이후 레코드를 반환한다. LEAD() 함수와 LAG() 함수는 3개의 파라미터를 필요로 하는데, 첫 번째와 두 번째 파라미터는 필수이며, 세 번째 파라미터는 선택 사항이다.

 

윈도우 함수와 성능

MySQL 서버의 윈도우 함수는 8.0 버전에 처음 도입됐으며, 아직 인덱스를 이용한 최적화가 부족한 부분도 있다. 위 두 쿼리는 결과는 차이가 있지만 사용자별로 MAX(from_date) 값을 구하는 쿼리이다. 윈도우 함수와 GROUP BY 쿼리는 근본적으로 차이가 있어서 동등한 비교는 어렵지만 윈도우 함수를 사용하는 쿼린느 인덱스 풀 스캔했으며, "Using filesort"를 보면 레코드 정렬 작업까지 실행한 것을 확인할 수 있다. 반면 GROUP BY 절을 사용한느 쿼리는 별도의 정렬 작업 없이 루스 인덱스 스캔으로 사원별 MAX(from_date) 값을 찾아냈다.

윈도우 함수를 사용한 쿼리는 프라이머리 키(emp_no, from_date)를 충분히 활용할 법한 쿼리였지만 이를 충분히 활용하지 못했다. 물론 인덱스 풀 스캔을 했어도 윈도우 함수 처리를 위한 것이 아니라 ix_salary 인덱스에 쿼리 처리에 필요한 컬럼(from_date와 emp_no 컬럼)이 모두 포함되어 있으면서 프라이머리 키보다 크기가 작기 때문에 이를 활용했을 뿐이다.

 

잠금을 사용하는 SELECT

InnoDB 테이블에 대해서는 레코드를 SELECT할 때 레코드에 아무런 잠금도 걸지 않는데, 이를 잠금없는 읽기(Non Locking Consistent Read)라고 한다. 하지만 SELECT 쿼리를 이용해 읽은 레코드의 컬럼 값을 어플리케이션에서 가공해서 다시 업데이트하고자 할 때는 SELECT가 실행된 후 다른 트랜잭션이 그 컬럼의 값을 변경하지 못하게 해야 한다. 이럴 때 레코드를 읽으면서 강제로 잠금을 걸어 둘 필요가 있는데, 이때 사용하는 옵션이 FOR SHARE와 FOR UPDATE 절이다. FOR SHARE는 읽기 잠금을, FOR UPDATE는 쓰기 잠금을 건다.

 

한 가지 주의할 사항은 FOR UPDATE나 FOR SHARE 절을 가지지 않는 SELECT 쿼리의 작동 방식이다. InnoDB 스토리지 엔진을 사용하는 테이블에서는 잠금 없는 읽기가 지원되기 때문에 특정 레코드가 "SELECT ... FOR UPDATE" 쿼리에 의해 잠겨진 상태라 하더라도 FOR SHARE나 FOR UPDATE 절을 가지지 않은 단순 SELECT 쿼리는 아무런 대기 없이 실행된다.

 

NOWAIT & SKIP LOCKED

MySQL 8.0 버전부터는 NOWAIT과 SKIP LOCKED 옵션을 사용할 수 있게 기능이 추가됐다. 지금까지의 MySQL 잠금은 누군가가 레코드를 잠그고 있다면 다른 트랜잭션은 그 잠금이 해제될 때까지 기다려야 했다. 때로는 일정 시간이 지나면 잠금 획득 실패 에러 메시지를 받을 수도 있었다. 하지만 이런 작동방식은 휴대폰의 화면을 보면서 응답을 기다리고 있을 사용자를 생각하면 때로는 적절한 작동 방식이 아닐 수도 있다.

다시 말해 애플리케이션의 어떤 기능에서 레코드가 이미 잠겨진 상태라면 innodb_lock_wait_timeout 시스템 변수에 설정된 시간 동안 기다렸다가 에러 메시지를 받는 것이 아닌 즉시 에러를 반환받아 응용 프로그램에서 다른 처리를 수행하거나 다시 트랜잭션을 시작하도록 구현해야 할 때도 있다. 이럴 때 SELECT 쿼리의 마지막에 NOWAIT 옵션을 사용하면 된다. FOR UPDATE나 FOR SHARE 절이 없는 SELECT 쿼리는 잠금 대기 자체가 없기 때문에 NOWAIT 옵션을 사용하는 것은 의미가 없다.

SKIP LOCKED 옵션은 SELECT하려는 레코드가 다른 트랜잭션에 의해 이미 잠겨진 상태라면 에러를 반환하지 않고 잠긴 레코드는 무시하고 잠금이 걸리지 않은 레코드만 가져온다. 이런 이유로 SKIP LOCKED 절을 가진 SELECT 절을 가진 SELECT 구문은 확정적이지 않은(NOT-DETEMINISTIC) 쿼리가 된다.

NOWAIT이나 SKIP LOCKED 기능은 큐(Queue)와 같은 기능을 MySQL 서버에서 구현하고자 할 때 매우 유용하다. 다음 요건을 가지는 쿠폰 발급 기능이 있다고 가정해보자.

• 하나의 쿠폰은 한 사용자만 사용 가능하다.
• 쿠폰의 개수는 1000개 제한이며, 선착순으로 요청한 사용자에게 발급한다.

일반적으로 위 요건을 처리하기 위해서 아래와 같은 테이블을 생성해야 한다.

CREATE TABKE coupon (
	coupon_id BIGINT NOT NULL,
    owned_user_id BIGINT NULL DEFAULT 0, /* 쿠폰이 발급되면 소유한 사용자의 id를 저장 */
    coupon_code VARCHAR(15) NOT NULL,
    ...
    PRIMARY KEY (coupon_id),
    INDEX ix_owneduserid (owned_user_id)
);

 

그리고 응용 프로그램에서 다음과 같은 절차를 거쳐 쿠폰을 발급하게 될 것이다. 응용 프로그램 코드에서 우선 아직 주인이 없는 쿠폰(owned_user_id=0) 쿠폰을 검색해서 하나를 가져와 사용자 id로 업데이트할 것이다.

mysql> BEGIN;
mysql> SELECT * FROM coupon
              WHERE owned_user_id = 0 ORDER BY coupon_id ASC LIMIT 1 FOR UPDATE SKIP LOCKED;

... 응용 프로그램 연산 수행 ...

mysql> UPDATE coupon SET owned_user_id = ? WHERE coupon_id = ?;
mysql> COMMIT;

만약 동시에 1000명의 사용자가 쿠폰을 요청하면 애플리케이션 서버는 그 요청만큼 프로세스를 생성해서 위의 트랜잭션을 동시에 실행할 것이다. 하지만 "SELECT ... FOR UPDATE" 쿼리는 coupon 테이블에서 하나의 레코드로 집중해서 잠금 획득을 하려고 할 것이다. 따라서 처음으로 잠금을 획득하는 트랜잭션은 있겠지만 나머지 999개의 트랜잭션은 첫 번째 트랜잭션이 작업을 끝내고 COMMIT할 때까지 대기해야 하고, 만약 대기시간(innodb_lock_wait_timeout 시스템 변수) 동안 잠금을 획득하지 못해서 결국 에러를 반환한다. 아무리 MySQL 서버가 많은 CPU와 메모리를 가지고 있다고 하더라도 이렇게 순차적(Serialization)으로 처리되면 서버의 남는 자원을 제대로 활용하지 못한다.

하지만 위에서 "FOR UPDATE SKIP LOCKED"는 MySQL 서버로 동시에 유입된 트랜잭션들이 대기시간 없이 잠김 레코드를 스킵하고 사용 가능한 레코드를 찾기만 하면 즉시  트랜잭션 처리를 시작할 수 있다. 따라서 위에서 순차적으로 처리하는 문제를 해결할 수 있다.

 

 

출처

Real MuySQL 8.0 2권 : 개발자와 DBA를 위한 MySQL 실전 가이드